CODEWITHSUNDEEP
rexcelshinyxlsxrodbc
Mily
Mily

Reputation: 351

Download xls file with multiple sheets as data frames Shiny R

I am using the R package Shiny to develop my own web application.

I have a download button which permits me to export the data into a excel file. In the excel file, there are 4 sheets and in each of them there is a data frame.

For example, in sheet1 there is dataTab1, in sheet2 there is dataTab2, in sheet 3 there is dataTab3 and in sheet4 there is dataTab4.

For doing this, I am using the function downloadHeader() in shiny server.R.

I have used two methods to do this.

Method 1: using the R package xlsx

output$downloadTab <- downloadHandler(

  filename = "dataxls.xlsx",
  content = function(file) {

  #creation of the workbook
   dataxls=createWorkbook(file)
  #creation of the sheets
   dataTabs1=createSheet(wb=dataxls,sheetName="Compartiments-simulation_sans_changement") 
   dataTabs2=createSheet(wb=dataxls,sheetName="Esperance-simulation_sans_changement")         
   dataTabs3=createSheet(wb=dataxls,sheetName="Compartiments-simulation_avec_changement") 
   dataTabs4=createSheet(wb=dataxls,sheetName="Esperance-simulation_avec_changement")
 #add the dataframes to the sheets
   writeWorksheet(dataxls, dataTab1, sheet = "Compartiments-simulation_sans_changement")
   writeWorksheet(dataxls, dataTab2, sheet = "Esperance-simulation_sans_changement")
   writeWorksheet(dataxls, dataTab3, sheet = "Compartiments-simulation_avec_changement")
   writeWorksheet(dataxls, dataTab4, sheet = "Esperance-simulation_avec_changement")

   saveWorkbook(dataxls,file)


}
)

The problem is that I have the following error: Error in createWorkbook(file) : Unknown format C:\Users\Baramova\AppData\Local\Temp\Rtmpmyqyeh\fileafc6d2b5998.xlsx

I have tried to fix it using the following example:

content = function(file) {
  fname = paste(file,"xlsx",sep=".")
  wb = loadWorkbook(fname, create = TRUE)
  createSheet(wb, name = "Sheet1")
  writeWorksheet(wb, c(1:3), sheet = "Sheet1") 
  saveWorkbook(wb)
  file.rename(fname,file)
}

But it doesn't work for me. Maybe I am omitting something. Do you have any idea?

Method 2: using RODBC package

output$downloadTab <- downloadHandler(

  filename ="Tab.xls",
  content = function(fname) {
    tmpdir <- tempdir()


    db <- paste(tmpdir,fname,sep="/")            
    channel <- odbcConnectExcel(xls.file = db,readOnly=FALSE)
    sqlSave(channel, Compartiments_simulation_sans_changement, tablename = "Compartiments_simulation_sans_changement")
    sqlSave(channel,  Esperance_simulation_sans_changement, tablename = "Esperance_simulation_sans_changement")
    sqlSave(channel, Compartiments_simulation_avec_changement, tablename = "Compartiments_simulation_avec_changement")
    sqlSave(channel, Esperance_simulation_avec_changement, tablename = "Esperance_simulation_avec_changement")
    odbcClose(channel)

    },
    contentType="application/csv" 

)

The problem is that the odbcConnectExcel() function is not recognized even if I have called the RODBC package by typing: library(RODBC)

Do you have any idea why is that so?

Upvotes: 1

Views: 1915

Answers (1)

Mily
Mily

Reputation: 351

I have found the solution of my problem.

Firstly you have to call the XLConnect and the RODBC packages. After that, you could use the following example:

    output$downloadTab <- downloadHandler(  
       filename ="Tab.xls",
       content = function(file) {
           write.csv(Compartiments_simulation_sans_changement, file="Compartiments-simulation_sans_changement.csv")
           write.csv(Esperance_simulation_sans_changement, file="Esperance-simulation_sans_changement.csv")
           write.csv(Compartiments_simulation_avec_changement, file="Compartiments-simulation_avec_changement.csv")
           write.csv(Esperance_simulation_avec_changement, file="Esperance-simulation_avec_changement.csv")

       channel <- odbcConnectExcel(xls.file = file,readOnly=FALSE)
           sqlSave(channel, Compartiments_simulation_sans_changement, tablename = "Compartiments_simulation_sans_changement")
           sqlSave(channel,  Esperance_simulation_sans_changement, tablename = "Esperance_simulation_sans_changement")
           sqlSave(channel, Compartiments_simulation_avec_changement, tablename = "Compartiments_simulation_avec_changement")
           sqlSave(channel, Esperance_simulation_avec_changement, tablename = "Esperance_simulation_avec_changement")
       odbcClose(channel)

    },
    contentType="application/xls" 

)

Upvotes: 3

Related Questions