Customize output of excel export of button extension

Question

Is it possible to adjust the output of the Excel output? I would like to be able to do the following things in descending urgency.

Add a Header to the table that includes some text "This table is based on the iris dataset and uses input$width as minimum"
Add an Thick bottom border to the column names header
Add a left border after the first column
Add an empty row above the header
Where I could write stuff into some merged cells, i.e. I would like to write "Petal Sizes" above the four columns of length, width,...

Thats a MWE using the button extension. I found some information for the original javascrtip DT here, but that is a bit too hard for me to transfer into R.

    rm(list=ls())   
    library(shiny)
    library(datasets)
    library(DT)
    library(data.table)
    DT<-data.table(iris)
    server<-shinyServer(function(input, output) {
      output$view <- DT::renderDataTable(
        DT[Sepal.Width<=input$width,.SD],extensions = c( 'FixedHeader','Buttons'),
          options=list(pageLength=60,fixedHeader = TRUE,dom = 'Bfrtip',buttons = c( 'csv', 'excel'  )))
    }) 

    ui<-shinyUI(fluidPage(
      titlePanel("Shiny MWE"),
         sidebarLayout(
            sidebarPanel(
            sliderInput("width", label = h3("Min width"),
                        min=min(DT$Sepal.Width), max=max(DT$Sepal.Width), value=mean(DT$Sepal.Width),
                        )),

        mainPanel(
          DT::dataTableOutput("view")
        )
      )
    ))

    runApp(list(ui=ui,server=server))

You can create downloadbutton and write xlsx using one of r library ( for example xlsx ) — Batanichek, Aug 25 '16 at 10:27

score 1 · Answer 1 · edited May 23 '17 at 12:15

I also realized that I had to abandon the 'button' extension, for other reasons as well. For instance, the excel download button only exports the view on the app, not the whole data set. (which can be fixed with the option server=FALSE, which is too slow for larger data sets)

I opted for the openxlsx package, which needs Rtools to be installed, which I had some difficulties with (found a solution to add it to the windows path ([Error: zipping up workbook failed when trying to write.xlsx)

So my posted code mostly does what I wanted or I can continue using the openxlsx commands. There are alternatives with the xlsx package or others, which I also had trouble installing.

rm(list=ls())   
    library(shiny)
    library(datasets)
    library(DT)
    library(data.table)
    library(openxlsx)

    DT<-data.table(iris)
    # Style created for openxlsx see help
    hs <- createStyle(textDecoration = "BOLD", fontColour = "#FFFFFF", fontSize=12,
    fgFill = "#177B57",border="Bottom",borderStyle=c("thick"))
    #Server
    server<-shinyServer(function(input, output) {
      output$view <- DT::renderDataTable(
        DT[Sepal.Width<=input$width,.SD],extensions = c( 'FixedHeader'),
          options=list(pageLength=20,fixedHeader = TRUE,dom = 'frtip'))
    #Include DownloadHandler
      output$downloadData <- downloadHandler(
    filename = function() { paste0("test.xlsx") }, 
    content = function(file) {
        wb<-createWorkbook() # Create wb in R
        addWorksheet(wb,sheetName="Output") #create sheet
        #Creates a Data Table in Excel if you want, otherwhise only use write Data
        writeDataTable(wb,1, DT[Sepal.Width<=input$width,.SD], colNames = TRUE, headerStyle = hs,startRow=2,tableStyle = "TableStyleLight1")
        mergeCells(wb,sheet = "Output", cols=1:5, rows=1)
        writeData(wb,1, "Include text also based on reactive function and in merged cells" )
        saveWorkbook(wb, file = file, overwrite = TRUE)
        },
       contentType= "excel/xlsx")
       }) 

    ui<-shinyUI(fluidPage(
      titlePanel("Shiny MWE"),
         sidebarLayout(
            sidebarPanel(
            sliderInput("width", label = h3("Min width"),
                        min=min(DT$Sepal.Width), max=max(DT$Sepal.Width), value=mean(DT$Sepal.Width),
                        ),
                downloadButton('downloadData', 'Download')),

        mainPanel(
          DT::dataTableOutput("view")
        )
      )
    ))

    runApp(list(ui=ui,server=server),launch.browser=T) # Download button only works in browser

Customize output of excel export of button extension

1 Answers1