I have created the following app to read an excel file from the temp folder in R. I would like to retain the file format in R shiny
library(XLConnect)
library(shiny)
library(openxlsx)
library(readxl)
ui <- fluidPage(
titlePanel("Writer App"),
sidebarLayout(sidebarPanel(fileInput(inputId = "file", label = "Read File Here", accept = c(".xlsx")), downloadLink("downloadData", "Download")), 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({
return(datasetInput())})
output$downloadData <- downloadHandler(
filename = function() {
paste("data-", Sys.Date(), ".xlsx", sep="")},
content = function(file) {
tf<-tempdir()
Files=list.files(path=tf, pattern=".xlsx", recursive = TRUE)[1]
file.copy(from =paste0(tf, "/",Files ), to= "temp_1.xlsx")
wb2 <- loadWorkbook(file = "temp_1.xlsx")
df_1<-data.frame("DF"= c(1:3))
addWorksheet(wb = wb2,sheetName = "Parameters1")
writeData(wb2, "Parameters1", df_1, startCol = 1, startRow = 2, rowNames = TRUE)
saveWorkbook(wb2, file)})
}
shinyApp(ui = ui, server = server)
The Download button(output$downloadData) has a command to copy the file including the format from a temporary location to a new file temp.xlsx. this file is next loaded as workbook. a New sheet Parameters1 is added to the workbook wb2. Next, data frame(df_1) is written to sheet Parameters1 and the file is downloaded.
Now, the code within the output$downloadData works in the R console but doesnt work in the reactive environment of R shiny.
There seems to be an error in loadworkbook command. I am able to get the file by means of read_excel and then inserting the data. That however doesnt prserve the format of the loaded excel sheet. I request someone to guide me in this.
This works for me without any error.
library(shiny)
library(openxlsx)
library(readxl)
ui <- fluidPage(
titlePanel("Writer App"),
sidebarLayout(sidebarPanel(fileInput(inputId = "file", label = "Read File Here", accept = c(".xlsx")), downloadLink("downloadData", "Download")),
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$datapath, pattern = ".xlsx" )){data=read_excel(infile$datapath)} else
if(grepl(infile$datapath , pattern = ".csv" )){data=read.csv(infile$datapath )}
#RENAME DATAFRAME WITH UNDERSCORES
names(data)<-gsub(pattern = " ", replacement = "_", x = names(data))
return(data)
})
output$table1 <- renderDataTable({
return(datasetInput())})
output$downloadData <- downloadHandler(
filename = function() {
paste("data-", Sys.Date(), ".xlsx", sep="")},
content = function(file) {
tf<-tempdir()
Files=list.files(path=tf, pattern=".xlsx", recursive = TRUE)[1]
file.copy(from =paste0(tf, "/",Files ), to= "temp_1.xlsx")
wb2 <- loadWorkbook(file = "temp_1.xlsx")
df_1<-data.frame("DF"= c(1:3))
addWorksheet(wb = wb2,sheetName = "Parameters1")
writeData(wb2, "Parameters1", df_1, startCol = 1, startRow = 2, rowNames = TRUE)
saveWorkbook(wb2, file)
})
}
shinyApp(ui = ui, server = server)