CODEWITHSUNDEEP
rshiny
krish
krish

Reputation: 1438

Download excel with multiple datasets in the same sheet R Shiny

I am creating a shiny app where I want the user to download data from the shiny app into one single excel file with multiple(more than one) datasets in the same sheet. I looked at the other similar questions but I am not able to get my code working with that help.

Datasets Used:

sample <- structure(list(type = structure(c(1L, 5L, 3L, 5L, 2L, 4L), .Label = c("add select multiple prompt using alphaOptions", 
"add select multiple prompt using imageOptions", "add select one prompt using alphaOptions", 
"add select one prompt using imageOptions", "add select one prompt using numOptions"
), class = "factor"), name = structure(c(4L, 5L, 3L, 6L, 1L, 
2L), .Label = c("grid", "grid_two_columns", "quick_advance", 
"select", "select1", "spinner"), class = "factor"), caption = structure(c(4L, 
5L, 3L, 6L, 1L, 2L), .Label = c("grid widget", "grid with a maximum of two columns", 
"quick advance select widget", "select multiple widget", "select one widget", 
"spinner widget"), class = "factor"), hint = structure(c(4L, 
6L, 1L, 3L, 2L, 5L), .Label = c("click a choice to select it and advance to the next question", 
"click an image to select it (you must have the images on your sdcard to see them)", 
"click the button to provide a response", "don't pick c and d together", 
"regardless of screen size this widget will only show two columns of images", 
"scroll down to see default selection"), class = "factor")), .Names = c("type", 
"name", "caption", "hint"), class = "data.frame", row.names = c(NA, 
-6L))



profile <- structure(list(Company.Name = structure(c(1L, 3L, 4L, 2L, 5L), .Label = c("Address", 
"Assigned MB", "Contact Name", "Contact Phone", "Website"), class = "factor"), 
    ABC = structure(c(2L, 5L, 1L, 3L, 4L), .Label = c("(398) 657-8401", 
    "48,S  St, Denver, CO, 80233", "Bob Harris, Active", "www.abc.com", 
    "John Gardner"), class = "factor")), .Names = c("Company.Name", 
"ABC"), class = "data.frame", row.names = c(NA, -5L))

I have this same data as csv files.

Below is my code for excel with two sheets:

ui.R

shinyUI(pageWithSidebar(
  headerPanel('Download'),
  sidebarPanel(
    downloadButton('downloadData', 'Download')
  ),
  mainPanel(
  )
))

server.R

sample <- read.csv("sample.csv")
profile <- read.csv("profile.csv")

shinyServer(function(input, output) {

  output$downloadData <- downloadHandler(
    filename = "test.xlsx",
    content = function(file) {
      write.xlsx2(profile, file, sheetName = "Sheet1")
      write.xlsx2(sample, file, sheetName = "Sheet2", append = TRUE)
    }
  )
})

This is what I am trying for downloading both datasets in the same sheet.

shinyServer(function(input, output) {

  output$downloadData <- downloadHandler(
    filename = "test.xlsx",
    content = function(file) {
      write.xlsx2(profile, file, sheetName = "Sheet1")
      write.xlsx2(sample, file, sheetName = "Sheet1", append = TRUE)
    }
  )
})

This is the error I get:

Error : java.lang.IllegalArgumentException: The workbook already contains a sheet of this name
Warning: Error in .jcall: java.lang.IllegalArgumentException: The workbook already contains a sheet of this name
Stack trace (innermost first):

I am expecting the profile and sample datasets to be one below the other in one sheet with some space between them something like in the image below: enter image description here

Upvotes: 0

Views: 728

Answers (1)

treysp
treysp

Reputation: 713

I don't think append() works within a given sheet, but you can use the addDataFrame() function:

data <- data.frame(a = 1:3, b = 4:6, c = 7:9)  

wb <- createWorkbook()
sheet  <- createSheet(wb, sheetName="addDataFrame1")

addDataFrame(data, sheet, row.names = FALSE)
addDataFrame(data, sheet, startRow = nrow(data) + 3, , row.names = FALSE)

saveWorkbook(wb, file = "test.xlsx")

Upvotes: 1

Related Questions