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.
Kind regards
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:
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
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
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)
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
Kind regards
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
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
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.
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.
