Tychobra

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:

Excel Report Generation with Shiny

Richard Hill and Andy Merlino

2019/07/22

Excel Report