Exporting multiple excel sheets in one workbook R shiny

Question

link to data in xlsx file (data is in 4th sheet),link to data in csv file
library(shiny) library(xlsx)

shinyUI(fluidPage(

  titlePanel("Tim O'Leary"),
  sidebarLayout(
    sidebarPanel(
      fileInput('file1', 'Choose File',
             accept=c('text/csv', 
                     'text/comma-separated-values,text/plain', 
                     c(".txt",'.csv'))),
  downloadButton('downloadData', 'Download'),
  tags$hr(),
  checkboxInput('header', 'Header', TRUE),
  radioButtons('sep', 'Separator',
               c(Comma=',',
                 Semicolon=';',
                 Tab='\t'),
               '\t'),
  radioButtons('quote', 'Quote',
               c(None='',
                 'Double Quote'='"',
                 'Single Quote'="'"),
               '"')

),
mainPanel(

  #tableOutput('contents')
  tabsetPanel(
    tabPanel("RawTable", tableOutput('contents')),
    tabPanel("Table1", tableOutput('a')),
    tabPanel("Table2", tableOutput("b")),
    tabPanel("Table3", tableOutput("c"))

  )
 )
  )
 ))

 library(shiny)
  library(xlsx)

 shinyServer(function(input, output) {

 rawData <- reactive({
 filet <- input$file1
   if(is.null(filet)) return()
   data <- read.csv(filet$datapath)
   })

  #dtableInput<- reactive({
       # if(is.null(rawData())) 
  #   return()
  #  data<-rawData()        
   #})

   a <- reactive({
     a <- subset(rawData(), AssertionString == "10046")
    a
 })

  b <- reactive({
   b <- subset(rawData(), AssertionString == "10074")
   b
  })

 c <- reactive({
   c <- subset(rawData(), AssertionString == "10179")
   c
 })

 # workBook <- reactive({
#    processor <- createWorkbook()
 #    page1 <- createSheet(wb=processor, sheetName="iam")
  #   page2 <- createSheet(wb=processor, sheetName="tim")
  #  page3 <- createSheet(wb=processor, sheetName="oleary")
   # page4 <- createSheet(wb=processor, sheetName="J")
   #addDataFrame(x=rawData(), sheet=page1)
#    addDataFrame(x=a(), sheet=page2)
 #   addDataFrame(x=b(), sheet=page3)
  #  addDataFrame(x=c(), sheet=page4)
  # wb <- saveWorkbook(processor,"processorData")
 #  wb
 #})

 output$contents <- renderTable({
       rawData()
      })

     output$a <- renderTable({
          a()
       })

       output$b <- renderTable({
       b()
        })

     output$c <- renderTable({
        c()
           })

        output$downloadData <- downloadHandler(
      filename = function() {paste("file_name", '.cvs')},
       content = function(file){
         write.csv(a(), file="file_name")
          #write.xlsx2(a(), file="file_name.xlsx", sheetName="sheet1")
            #write.xlsx2(b(), file="file_name.xlsx", sheetName="sheet2",                                    append=T)
       })
#rbind allows you to connect dfs in column like manner

     })

These are my ui and server scripts in r shiny and I'm trying to export the dataframes rawData,a,b,and c into an excel workbook with each dataframe having its own sheet. I have tried reading in a csv file and exporting it that way but I cannot find a function that allows me to export these the way I need to using write.csv. I then tried importing as .xlsx and used write.xlsx2 because the rawData is too big for just write.xlsx, and when I used write.xlsx2 upon clicking my download button, it would just load for an endless amount of time but never download anything. Any help or advice will be appreciated thanks

Answer 1

Cannot download your data, but here is an example that works. You can upload any csv files with headers, and specify a column, and you can then download an xlsx file where the csv file is splitted into multiple tabs based on each unique value in the selected column. Note that the write.xlsx function is fairly slow, so you might need to wait a while depending on your csv file size.

library(shiny)

ui <- shinyUI(fluidPage(

   titlePanel("CSV Splitter"),

   sidebarLayout(
      sidebarPanel(
        fileInput("file", "Upload csv file", accept="text/csv"),
        uiOutput("column_ui"),
        downloadButton("download")
      ),

      mainPanel(
      )
   )
))

server <- shinyServer(function(input, output) {

    data <- reactive({
        if (is.null(input$file)) {
            return(NULL)
        } else {
            return(read.csv(input$file$datapath, header=TRUE))
        }
    })

    output$column_ui <- renderUI({
        selectInput("column", "Select a column to split by unique values", unique(names(data())))
    })

    output$download <- downloadHandler(
        filename = "result.xlsx",
        content = function(file) {
            column_data = data()[[input$column]]
            unique_values = unique(column_data)
            write.xlsx(data()[data()[[input$column]] == unique_values[1],], file, as.character(unique_values[1]))
            for (i in 2:length(unique_values)) {
                write.xlsx(data()[data()[[input$column]] == unique_values[i],], file, as.character(unique_values[i]), append = TRUE)
            }
        }
    )

})

# Run the application 
shinyApp(ui = ui, server = server)