not able to save the pivot table output as csv / excel from ShinyApp using rpivotTable

Hi guys, I want to export the pivot table data as shown in the image in csv, excel, pdf form using buttons (Note: I don't want to export the actual data). However i'm unable to do so, can't figure out the reason due to my limited knowledge. Please guide me, I shall be really thankful for the help. :slight_smile:
Kind regards

library(rpivotTable)
library(dplyr)
library(readr)
library(shiny)
library(openxlsx)

#ui
ui = fluidPage(
  radioButtons(inputId="format", label="Enter the format to download", 
               choices=c("none", "csv", "excel", "pdf", "copy"), inline = FALSE, selected="none"),


  fluidRow( rpivotTableOutput("pivot")))


#server
server = function(input, output) { 

  output$pivot <- renderRpivotTable( rpivotTable::rpivotTable( rows = c( 
    "vs"),cols=c("carb"),vals =  "mpg", aggregatorName = "Sum",rendererName =  
      "Table",width="50%", height="550px",
    {
      data <- mtcars
     
      if(input$format =="csv") {
        write_csv( data, 'data.csv')
        
      } 
      if (input$format=="excel") {
        write.xlsx(data, "data.xlsx")
      } 
     
      data
     
    }))

}

shinyApp(ui = ui, server = server)

Hey Saad. I'm not sure if this does exactly what you are looking for, but the code below takes your framework and makes some changes to how downloads are handled:

  1. rPivotTable allows you to incorporate some custom javascript functions. In this case, we capture the HTML table that it displays anytime a change is made to the rPivotTable object
  2. We 'scrape' the HTML table using the rvest library and convert it into a reactive. Specifically we use an eventReactive that triggers anytime the rPivotTable object changes
  3. We use shiny's download handler to download the pivoted table.
library(rpivotTable)
# consider using tidyverse library call to get dplyr, readr and rvest
# library(tidyverse)
library(dplyr)
library(readr)
# need rvest to be able to 'scrape' rPivotTable
library(rvest)
library(shiny)
# library(openxlsx)
# I really like how lightweight and versatile writexl is
library(writexl)
# need JS functionality in htmlwidgets
library(htmlwidgets)
library(shinyjs)
library(clipr)

#ui
ui = fluidPage(
    # for the purposes of this exercise, I'm only including csv and xlsx to simplify the download logic
    # but you could certainly add more format options
    radioButtons(inputId = "format", label = "Enter the format to download", 
                 choices = c( "csv", "excel"), inline = FALSE, selected = "csv"),
    downloadButton("download_pivot"),
    actionButton("copy_pivot", "Copy"),
    fluidRow(rpivotTableOutput("pivot")))

#server
server = function (input, output) { 
    
    output$pivot <- renderRpivotTable(
        rpivotTable(mtcars, rows = "vs", cols = "carb", vals =  "mpg", aggregatorName = "Sum",
                    rendererName = "Table", width="50%", height="550px",
                    onRefresh = htmlwidgets::JS(
                        "function(config) {
                            Shiny.onInputChange('pivot', document.getElementById('pivot').innerHTML); 
                        }"))
    )
    
    # create an eventReactive dataframe that regenerates anytime the pivot object changes
    # wrapped in a tryCatch to only return table object. errors out when charts are shown
    pivot_tbl <- eventReactive(input$pivot, {
        tryCatch({
            input$pivot %>%
                read_html %>%
                html_table(fill = TRUE) %>%
                .[[2]]
        }, error = function(e) {
            return()
        })
    })
    
    # allow the user to download once the pivot_tbl object is available
    observe({
        if (is.data.frame(pivot_tbl()) && nrow(pivot_tbl()) > 0) {
            shinyjs::enable("download_pivot")
            shinyjs::enable("copy_pivot")
        } else {
            shinyjs::disable("download_pivot")
            shinyjs::disable("copy_pivot")
        }
    })
    
    # using shiny's download handler to get the data output
    output$download_pivot <- downloadHandler(
        filename = function() {
            if (input$format == "csv") {
                "pivot.csv"
            } else if (input$format == "excel") {
                "pivot.xlsx"
            }
        },
        content = function(file) {
            if (input$format == "csv") {
                write_csv(pivot_tbl(), path = file)
            } else if (input$format == "excel") {
                writexl::write_xlsx(pivot_tbl(), path = file)
            }
        }
    )
    
    # copy pivot table - works natively on Windows/OSX. Requires xclip on Linux
    observeEvent(input$copy_pivot,  {
        clipr::write_clip(pivot_tbl(), object_type = "table")
    })
    
}

shinyApp(ui = ui, server = server)

Let me know if this helps!

4 Likes

Hi guys,
I'm using rpivotTables Shinyapp to export the pivottable data as csv, excel, pdf using buttons (Note: I don't want to export the actual data). The data is shown in the image. However i'm unable to do so, can't figure out the reason due to my limited knowledge. I shall be really thankful for the help :slight_smile:
Kind regards

library(rpivotTable)
library(dplyr)
library(readr)
library(shiny)

#ui
ui = fluidPage(
  radioButtons(inputId="file", label="Enter the format to download", 
               choices=c("none", "csv", "excel", "pdf", "copy"), inline = TRUE, selected="none"),
  
  selectInput(inputId = "vs", label = "vs:", choices = c("All", 
                                                         unique(as.character(mtcars$vs)))),
  
  fluidRow( rpivotTableOutput("pivot")))


#server
server = function(input, output, session) { 
  reactive({
    mtcars %>% select(cyl, carb, vs, mpg) %>% group_by(carb,vs ) %>% 
      summarise(mpg=sum(mpg)) }) 
  
  output$pivot <- renderRpivotTable( rpivotTable::rpivotTable( rows = c( 
    "vs"),cols=c("carb"),vals =  "mpg", aggregatorName = "Sum",rendererName =  
      "Table",width="50%", height="550px",
    {
      data <- mtcars
      
      if(input$vs != 'All') {
        data<-data[data$vs == input$vs, ]}
      
      if(input$file =="csv") {
        write_csv(filename = data,'data.csv')}
      
      else if (input$file=="excel") {
        write_excel_csv(x = data, "data.xlsx")
      }
      else {"copy"}
      data
    }))}

shinyApp(ui = ui, server = server)

Would you try below code?

library(rpivotTable)
library(dplyr)
library(readr)
library(shiny)
library(openxlsx)

#ui
ui = fluidPage(
  radioButtons(inputId = "file", label = "Enter the format to download", 
               choices = c("none", "csv", "excel", "pdf", "copy"), 
               inline = TRUE, selected = "none"),
  
  selectInput(inputId = "vs", label = "vs:", 
              choices = c("All", unique(as.character(mtcars$vs)))), 
  downloadButton("downloadData", "Download"), 
  
  fluidRow( rpivotTableOutput("pivot")))


#server
server = function(input, output, session) {
    
  dataInput <- reactive({
    mtcars %>% select(cyl, carb, vs, mpg) %>% 
          group_by(carb,vs ) %>% 
          summarise(mpg = sum(mpg))
      }) 
  
  output$pivot <- renderRpivotTable(
      rpivotTable::rpivotTable(
          rows = c("vs"), cols = c("carb"), vals =  "mpg", 
          aggregatorName = "Sum", rendererName = "Table", 
          width = "50%", height = "550px",
    {
      data <- dataInput();
      
      data
    }))
  
  # Downloadable csv of selected dataset ----
  output$downloadData <- downloadHandler(
      
      filename = function() {
          paste(dataInput(), ".csv", sep = "")
      },
    content = function(file) {
        
        if(input$vs != 'All') {
            data <- data[data$vs == input$vs, ]
        }
        
        if(input$file =="csv") {
            write_csv(x = data, filename = 'data.csv')
            
        } else if (input$file=="excel") {
            write.xlsx(x = data, filename = "data.xlsx")
        } else {
            "copy"
        }
    })
  }

shinyApp(ui = ui, server = server)

Thanks mate, i actually want to download the output of the data i.e the pivot table part (as i change its rows and columns), not the original dataset.
Would you be able to do that? Thanks

Hi @gmccomas, I don't have words to thank you enough, its hard to find people who are truly helpful and super genius. I'm really thankful to you for your utmost help. If possible can you pls add the copy button as well and make it functional? Secondly is it possible to hide or delete the Totals column & row coming in the pivot table output? Warm regards

Please follow the community guidelines when cross posting.

Here is the link to your stack overflow post if @gmccomas would like to post his answer there, too.

Lastly, if your question has been answered, please mark a solution.

2 Likes

Thanks, i'll keep that in mind. I didn't know my asking question is offending someone

Hi @gmccomas, there's a slight problem coming up. When i put multiple columns & rows in pivot table, and download the data to csv/ excel, there comes a certain column which has wrong values in the header like the "J" column in image (it's got 3 3 3 which is wrong, it should be 3 8 0). Can you please look into it mate? Thanks

I think the problem can be solved if there is repetition of labels in Tablular layout of output (lame as attached in the image).

Hey @Saad. Yeah one of the drawbacks to parsing these tables is that the parsed HTML doesn't show that stacked headers are in fact 'merged cells'. I'm not really sure how to get around that but the layout at least matches up to the table.

As for copying, the download handler doesn't have built in functionality to address copying to clipboard. I use the clipr with an observer to write to the clipboard. Keep in mind though that if you are hosting this on shiny server, you will have to install clipx https://forum.posit.co/t/copy-to-clipboard-and-downloadhandler-not-working-when-shiny-app-deployed-on-ubuntu-server/8127

I've edited my original answer with the updated code.

1 Like

Thanks mate, i was asking of repeating the labels because when i select more than 2 rows or column in rpivotTable, then after downloading the data i get few wrong labels where there are spaces in between.
e.g. in the image attached there are 3 row variables (cyl, vs, gear) and 2 column variable (carb, am), when you download the data, you can see that rows 6, 7, 11 are wrong (as shown in the image).
As far as selecting 2 variables in either row or column is concerned, the table is fine, but when i select more than 2 variables in row or column, then the problem starts and some of the labels show a wrong value.

If you have a new question, please start a new thread.

Thanks

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.