10

*Hi, I'm trying to download multiple csv file from a unique excel file. I want to download (using only one downloadbutton) the differents sheets from the excel file. I don't understand why a for() loop doesn't work, and I can't see how can I do? If anyone knows..

The point is to download differents csv files, which are in the "wb" list (wb[1],wb[2]...) Thanks. Here is my code who works with the third sheet for instance (and sorry for my bad english) : ui :

library(readxl)
library(shiny)
library(XLConnect)
fluidPage(
titlePanel("Export onglets en CSV"),
  sidebarLayout(
    sidebarPanel(
      fileInput('fichier1','Choisissez votre fichier excel :',
                accept = ".xlsx"),
      fluidPage(
    fluidRow(
      column(width = 12,
             numericInput("sheet","Indiquez l'onglet à afficher :",min = 1, value = 1),
             tags$hr(),
             textInput('text',"Indiquez le nom des fichiers :"),
             tags$hr(),
             h4("Pour télécharger les fichiers .csv :"),
             downloadButton("download","Télécharger")
             )

    )
  )),
mainPanel(
  tabsetPanel(
    tabPanel('Importation',
             h4("Fichier de base:"),
             dataTableOutput("contents"))
      )
    )
  )
)

Server :

function(input,output){

  #Création data :
  data <- reactive({
    inFile<- input$fichier1
    if (is.null(inFile)){
      return(NULL)
    }else{
      file.rename(inFile$datapath,
              paste(inFile$datapath,".xlsx", sep =""))
      wb = loadWorkbook(paste(inFile$datapath,".xlsx",sep=""))
      lst = readWorksheet(wb,sheet = getSheets(wb))
      list(wb = wb, lst = lst)
    }
  })



  #Sortie de la table :
  output$contents <- renderDataTable({
    data()$wb[input$sheet]
  },options = list(pageLength = 10))


  #Téléchargement :
  output$download <- downloadHandler(

    #for (i in 1:input$sheet){

    filename = function(){
      paste(input$text,"_0",3,".csv",sep = "")
    },
    content = function(file){
      write.table(data()$wb[3],file,
                  sep = ';', row.names = F, col.names = T)
    }
#}
  )
}
MBnnn
  • 308
  • 2
  • 13
  • 1
    i dont think that for loop can work. The way to go would be to zip it i think,.. – Tonio Liebrand May 11 '17 at 13:24
  • Yes the for loop does'nt work that's why I put a # in front of. I'm looking for code or exemple with zip() for this situation since this morning, but I haven't found anything .. and/or I don't understand how to put different files in a zip and download it. Do you have any idea / code / exemple or website to explain the zip function ? – MBnnn May 11 '17 at 13:34

3 Answers3

13

As @BigDataScientist pointed out, you could zip all of your csv file and download the zipped file. Your downloadHandler could look like:

output$download <- downloadHandler(
    filename = function(){
      paste0(input$text,".zip")

    },
    content = function(file){
      #go to a temp dir to avoid permission issues
      owd <- setwd(tempdir())
      on.exit(setwd(owd))
      files <- NULL;

      #loop through the sheets
      for (i in 1:input$sheet){
        #write each sheet to a csv file, save the name
        fileName <- paste(input$text,"_0",i,".csv",sep = "")
        write.table(data()$wb[i],fileName,sep = ';', row.names = F, col.names = T)
        files <- c(fileName,files)
      }
      #create the zip file
      zip(file,files)
    }
  )

This does not download all the sheets from the excel file but the sheets ranging from 1 to whatever the user has as input in input$sheet.

You could also disable the download button if the user has not added an excel file/name.

NicE
  • 21,165
  • 3
  • 51
  • 68
  • Yes, I've already tried this code, but I've this error message : Warning: running command '"zip" -r9X ... had status 127 I don't know how to deal with this. Thank you very much for your help btw – MBnnn May 12 '17 at 14:39
  • Pbly an issue with `zip`, are you on Windows? If yes have you installed RTools? – NicE May 12 '17 at 14:42
  • note that it might not work in the shiny preview. Try to launch it like `runApp(shinyApp(ui, server), launch.browser = TRUE)` – Tonio Liebrand May 12 '17 at 14:53
  • Yes I'm on windows, and I'm trying to install RTools packages, but even with the last update, I can't install it.. – MBnnn May 12 '17 at 15:03
  • I can't speak to it's accuracy, but [this answer](https://stackoverflow.com/a/54194637/8366499) provides another workaround for zipping files in shiny – divibisan Jun 05 '19 at 02:47
0

Hope you've solved this MBnn, but in case anyone else is having similar problems, this case is down to RTools not being installed correctly on windows.

Currently you need to play close attention while running through the install process, and make sure to hit the checkbox to edit the system path.

Based on your code, this is likely to be the same issue preventing you from saving XLSX workbooks too.

James Allison
  • 141
  • 2
  • 4
0

I know this is an old thread but I had the same issue and the top answer did not work for me. However a simple tweak and using the archive package worked.

Reproductible example below:

library(shiny)
library(archive)

shinyApp(
  # ui
  ui = fluidPage(downloadButton("dl")),
  # server
  server = function(input, output, session) {
    # download handler
    output$dl <- downloadHandler(
      filename = function() {"myzipfile.zip"},
      # content: iris and mtcars
      content = function(file) {
        # definition of content to download
        to_dl <- list(
          # names to use in file names
          names = list(a = "iris",
                       b = "mtcars"),
          # data
          data = list(a = iris,
                      b = mtcars)
        )
        
        # temp dir for the csv's as we can only create
        # an archive from existent files and not data from R
        twd <- setwd(tempdir())
        on.exit(setwd(twd))
        files <- NULL
        
        # loop on data to download and write individual csv's
        for (i in c("a", "b")) {
          fileName <- paste0(to_dl[["names"]][[i]], ".csv") # csv file name
          write.csv(to_dl[["data"]][[i]], fileName) # write csv in temp dir
          files <- c(files, fileName) # store written file name
        }
        
        # create archive from written files
        archive_write_files(file, files)
      }
    )
  }
)

This will create the zip file myzipfile.zip which will contain iris.csv and mtcars.csv.

yben
  • 1
  • 1