shiny: Saving rpivotTableOutput as Excel

Question

I could not figured out how to save the output of rpivottable in shiny. My minimum working example is below for reference.

library(tidyverse)
library(shiny)
library(shinydashboard)

ui <-
  dashboardPage(
    skin = "green",
    dashboardHeader(
      title      = "Test",
      titleWidth = 280
      ),
    dashboardSidebar(
      width = 280,
      sidebarMenu(
        menuItem(text = "Output", tabName = "Out1",     icon = icon("file-upload"))
      )
    ),
    dashboardBody(
      tabItems(
        tabItem(
                 tabName = "Out1",
                 fluidRow(column(width = 10, strong("Data")), align = "center"),
                 br(),
                 fluidRow(rpivotTableOutput("Data1"))
                 ) 
      )
    )
  )

  
server <- 
  function(input, output){
    library(rpivotTable)
      
    output$Data1 <- 
      renderRpivotTable(
        rpivotTable(
            data = mtcars
          , rows = "cyl"
          , cols = "gear"
          , height = "780px"
          )
        )
          }

runApp(
    list(ui = ui, server = server)
  , launch.browser = TRUE
  )

The R object of `rpivotTable` output does not contain the processed pivot table. I think this will be calculated in the JavaScript on the client's browser. So this should not be possible. Do you really need `rpivottable`? Why not simply do `xtabs(~ cyl + class, data = mpg) |> as.data.frame() |> writexl::write_xlsx()` instead? — danlooo, Mar 24 '22 at 09:36

R.S. · Accepted Answer · 2022-03-25T07:08:12.130

Edit/Update: with the help of rvest and xlsx packages, we can save the extracted html pivot table as xls file. Updated code below

I agree with @danlooo 's comment that trying to extract data from the render of rpivotTable might not be the best way forward. However, I can offer a way of extracting the HTML code of generated pivot table.

Below code extracts the html code of the generated pivot table, uses rvest to extract a dataframe from this html , finally using xlsx for export. More info on interaction between shiny and js can be found in this article

The code generates an xls file containing the pivot table generated by rpivotTable

library(tidyverse)
library(shiny)
library(shinydashboard)
library(rpivotTable)

library(shinyjs)
library(dplyr)
library(rvest)
library(xlsx)

ui <-
  dashboardPage(
    skin = "green",
    
    dashboardHeader(
      title      = "Test",
      titleWidth = 280
    ),
    
    dashboardSidebar(
      width = 280,
      sidebarMenu(
        menuItem(text = "Output", tabName = "Out1")
      ),
      
      hr(),
      useShinyjs(),
      actionButton(inputId = "btnExport", "Export Table")
      
      
    ),
    dashboardBody(
      tabItems(
        tabItem(
          tabName = "Out1",
          fluidRow(column(width = 10, strong("Data")), align = "center"),
          br(),
          fluidRow(rpivotTableOutput("Data1"))
        ) 
      )
    )
  )


server <- 
  function(input, output){
    #library(rpivotTable)
    
    output$Data1 <- 
      renderRpivotTable(
        rpivotTable(
          data = mtcars
          , rows = "cyl"
          , cols = "gear"
          , height = "780px"
        )
      )
    
    
    observeEvent(input$btnExport,{
      
      runjs(
        "
        var tblhtml=document.getElementsByClassName('pvtRendererArea')[0].innerHTML;
        console.log(tblhtml)
        //set shiny Input value to read reactively from R 
        Shiny.setInputValue('tblvar_shiny', tblhtml);
        "
      )
      
    } )
    
    #save pivot table to html file
    # observeEvent(input$tblvar_shiny, 
    #              { write_file(sprintf("<html><body> %s </body></html>", 
    #                                   input$tblvar_shiny), 
    #                           file = "000pivothtml.html" )})
    
    #save pivot table to xls file 
    observeEvent(input$tblvar_shiny,
                 { 
                     minimal_html(input$tblvar_shiny) %>% 
                     html_element("table")   %>% 
                     html_table() %>% 
                     as.data.frame() %>% 
                     write.xlsx2(file="000pivot_final.xls")
                   })
  } 

runApp(
  list(ui = ui, server = server)  , launch.browser = TRUE
)

Thanks for your answer. Output very close to required. – MYaseen208 Mar 25 '22 at 01:57 — MYaseen208, Mar 25 '22 at 01:57

shiny: Saving rpivotTableOutput as Excel

1 Answers1