Excel Report Generation with Shiny

[This article was first published on Posts on Tychobra, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

R is great for report generation. Shiny allows us to easily create web apps that generate a variety of reports with R.

This post details a demo Shiny app that generates an Excel report, a PowerPoint report, and a PDF report:

The full Shiny app source code is available here. Also, we included a more basic Shiny app that generates an Excel report at the end of this post. Follow up posts will include similar simple Shiny apps generating PowerPoint and PDF reports.

Here are some screenshots of the Shiny app that generates the reports. You click the button in the left sidebar to select the report type (Excel, PowerPoint, or PDF).

And then you click a the report type button to generate the report. Simple as that!

In this app, the generated reports rely on the user selected “Valuation Date” Shiny input (in the left sidebar). Your report generating Shiny app would, of course, include your data and the Shiny inputs necessary for your custom report.

Excel Report

The generated Excel workbook has 3 sheets:

The “Cover Page” sheet includes an image and some custom text and cell styling.

The other 2 pages include tables with multi part headers, totals rows, and some custom styling.

Follow the link below to download a copy of the actual generated Excel file:

https://github.com/Tychobra/shiny-insurance-examples/blob/master/basic-insurer-dashboard/example%20reports/claims-report-as-of-2019-06-20.xlsx

The process of creating and customizing the Excel workbook is handled by the openxlsx R package. We like openxlsx because it does not require Java (several of the other R packages for working with Excel depend on Java), and it provides functions to highly customize the Excel workbook.

The following is a simple Shiny app that generates an Excel workbook. You can copy and paste this simple app into your R console to run it. Enjoy!

library(shiny)
library(openxlsx)

# create some example data to download
my_table <- data.frame(
  Name = letters[1:4],
  Age = seq(20, 26, 2),
  Occupation = LETTERS[15:18],
  Income = c(50000, 20000, 30000, 45000)
)

# add a totals row
my_table <- rbind(
  my_table,
  data.frame(
    Name = "Total",
    Age = NA_integer_,
    Occupation = "",
    Income = sum(my_table$Income)
  )
)

# minimal Shiny UI
ui <- fluidRow(
  column(
    width = 12,
    align = "center",
    tableOutput("table_out"),
    br(),
    downloadButton(
      "download_excel", 
      "Download Data to Excel"
    )
  )
)

# minimal Shiny server
server <- function(input, output) {
  output$table_out <- renderTable({
    my_table
  })
  
  
  output$download_excel <- downloadHandler(
    filename = function() {
      "employee_data.xlsx"
    },
    content = function(file) {
      my_workbook <- createWorkbook()
      
      addWorksheet(
        wb = my_workbook,
        sheetName = "Employee Data"
      )
      
      setColWidths(
        my_workbook,
        1,
        cols = 1:4,
        widths = c(6, 6, 10, 10)
      )
      
      writeData(
        my_workbook,
        sheet = 1,
        c(
          "Company Name",
          "Employee Data"
        ),
        startRow = 1,
        startCol = 1
      )
      
      addStyle(
        my_workbook,
        sheet = 1,
        style = createStyle(
          fontSize = 24,
          textDecoration = "bold"
        ),
        rows = 1:2,
        cols = 1
      )
      
      writeData(
        my_workbook,
        sheet = 1,
        my_table,
        startRow = 5,
        startCol = 1
      )
      
      addStyle(
        my_workbook,
        sheet = 1,
        style = createStyle(
          fgFill = "#1a5bc4",
          halign = "center",
          fontColour = "#ffffff"
        ),
        rows = 5,
        cols = 1:4,
        gridExpand = TRUE
      )
      
      addStyle(
        my_workbook,
        sheet = 1,
        style = createStyle(
          fgFill = "#7dafff",
          numFmt = "comma"
        ),
        rows = 6:10,
        cols = 1:4,
        gridExpand = TRUE
      )
      
      saveWorkbook(my_workbook, file)
    }
  )

  
}

shinyApp(ui, server)

The above app generates this neat little excel workbook:

To leave a comment for the author, please follow the link and comment on their blog: Posts on Tychobra.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)