I have an excel file called testfile.xlsx. the first sheet of this file is called sheet1. I have written appended a new sheet called New_Sheet using xlsx package as follows
library(xlsx)
setwd()##set the file path to where testfile.xlsx is located
write.xlsx('new_data', "testfile.xlsx", sheetName="New_Sheet", append=TRUE)
This adds the required sheet.
I have created the following shiny app to write the sheet to the file
library(shiny)
library(xlsx)
library(openxlsx)
library(readxl)
ui <- fluidPage(
titlePanel("Writer App"),
sidebarLayout(sidebarPanel(fileInput(inputId = "file", label = "Read File Here", accept =
c(".xlsx")),actionButton(inputId = "Run", label = "Write Data to Table")),
mainPanel(dataTableOutput(outputId = "table1"))))
server <- function(input, output) {
datasetInput <- reactive({
infile<- input$file
if (is.null(infile))
return(NULL)
#READ .XLSX AND .CSV FILES
if(grepl(infile, pattern = ".xlsx" )==T){
data=read_excel(infile$datapath)
} else if(grepl(infile , pattern = ".csv" )==T)
{data=read.csv(infile$datapath )}
#RENAME DATAFRAME WITH UNDERSCORES
names(data)<-gsub(pattern = " ", replacement = "_", x = names(data))
return(data) })
output$table1 <- renderDataTable({
datasetInput()})
observeEvent(input$Run,{
infile<-input$file
testfile<-(infile[1])
filepath<-(input$file)
filepath<-gsub(pattern = "0.xlsx", replacement ="" , x = filepath)
# print(infile$datapath[1])
print(filepath)
print(testfile)
setwd(dir = filepath)
write.xlsx('new_data', testfile, sheetName="New_Sheet3", append=TRUE)})
}
shinyApp(ui = ui, server = server)
The app renders the data in the excel sheet as a table without any problems .When we push the run app button, the print commands generate the name of the file and the filepath. The write excel function doesnt work. Is there a way to insert the new_data sheet using the action button. I request someone to guide me here.
I recommend using downloadHandler instead. See here for an example.