Headers (rownames/ columnnames) wrongly printed in Excel when RpivotTable data is exported

r4ds
shiny
shinyappsio
r
html

#1

Guys when i export RpivotTable output to Excel, i get wrong repetition of headers (rownames / columnnames).
Either the repetition of headers should be correct or the headers should be in merged form as it is in rpivotTable.
Secondly i want to exclude Totals appearing as column and bottom row.
I would really appreciate if someone could pls guide me.
Also posted on stackoverflow with different dataset, you can cross check the answers.


Thanks

 # 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(ggplot2)  #incase you want to use 'Diamonds' dataset instead of mtcars
library(readr)
# need rvest to be able to 'scrape' rPivotTable
library(rvest)
library(shiny)
# library(openxlsx)
# need JS functionality in htmlwidgets
library(htmlwidgets)
library(shinyjs)

#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"), inline = TRUE, selected = "csv"),
  downloadButton(outputId = "download_pivot"),
  fluidRow(rpivotTableOutput("pivot")))

#server
server = function (input, output) { 
  
  output$pivot <- renderRpivotTable(
    rpivotTable(mtcars, rows = c("vs", "cyl", "hp"), cols = c("carb", "am", "gear"), 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_summary")
    } else {
      shinyjs::disable("download_summary")
    }
  })
  
  # using shiny's download handler to get the data output
  output$download_pivot <- downloadHandler(
    filename = function() {
      if (input$format == "csv") {
        "pivot.csv"
      } 
    },
    content = function(file) {
      if (input$format == "csv") {
        write_csv(pivot_tbl(), path = file,  na ="", append =FALSE, col_names = TRUE)
      } 
    }
  )
  
  }

shinyApp(ui = ui, server = server)