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:
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(
Size = 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",
Colour = "#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:
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.