shiny: Saving rpivotTableOutput as Excel

Question

shiny: Saving rpivotTableOutput as Excel

328 Views Asked by MYaseen208 At 21 March 2022 at 16:34

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
  )

Original Q&A

There are 1 best solutions below

**R.S.** · Accepted Answer · 2022-03-24T21:06:38.947000

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
)

shiny: Saving rpivotTableOutput as Excel

There are 1 best solutions below

Related Questions in R

Related Questions in EXCEL

Related Questions in SHINY

Related Questions in OUTPUT

Related Questions in RPIVOTTABLE

Trending Questions

Popular # Hahtags

Popular Questions