I am working to create a Shiny app that will allow the user to select the preferred file format and download the data. The code will read in FinalEP & FinalLR and then run a function I created that will filter the data to the user inputted account, and then adds the data to an excel workbook report template. The excel workbook has a company logo and text information in the first several rows so I am unable to simply use read_excel. I have been using "xlsx::loadWorkbook" so that I can load the entire workbook without disrupting the format of the template.
Does anyone know how I can load the workbook and have it downloaded as a pdf? Also, how exactly do I go about implementing the selectInput so the correct file format is being downloaded?
Thank you in advance for any assistance or guidance you are able to provide.
library(shiny)
library(tidyverse)
ui <- fluidPage(title = "Interesting Title",
titlePanel("Interesting Header"),
sidebarLayout(
sidebarPanel(
textInput("Date","Valuation Date",placeholder = "Enter date format yyyy-mm-dd"),
textInput("AffNumber","Account", placeholder = "Enter Number"),
selectInput("format","Download format", c("Pdf" = "pdf","Excel" = "excel")),
actionButton("run","Run"), verbatimTextOutput("default"), verbatimTextOutput("default2"),
downloadButton("download","Download")
),
mainPanel("Text Here")
)
)
server <- function(input, output) {
# Updates the report template with data for user specified account
combined <- eventReactive(input$run, {
FinalEP <- Function_To_Obtain_Data_From_PC(input$Date)
FinalLR <- Function_To_Obtain_Data_From_PC(input$Date)
Function_To_Create_Formatted_Excel_Workbook(df = FinalLR, df2 = FinalEP, choice = "Individual", Acct = input$AffNumber, RunDate = input$Date)
Name <- paste(unique(FinalEP$Name[FinalEP$`Account` %in% input$AffNumber]), "as of", input$Date)
wb <- xlsx::loadWorkbook("file.xlsx")
done <- list("done" = "Complete", "Name" = Name, "wb" = wb)
return(done)
})
output$default <- renderText({print((combined()$done))}) # temporary code, used for debugging
output$default2 <- renderText({print((combined()$Name))}) # temporary code, used for debugging
# Successfully downloads excel document
output$download <- downloadHandler(
filename = function(){
paste0(combined()$Name,".xlsx")
},
content = function(file) {
xlsx::saveWorkbook(combined()$wb, file = file)
}
)
# Issue: How can I design this to download the excel workbook as a pdf.
# Using RDCOMClient I am able to save the workbook as a pdf document, but am unsure how I can integrate this with the downloadHandler formatting.
## RDCOMClient Code
#file_location <- "path to workbook file"
#ex <- COMCreate("Excel.Application")
#book <- ex$workbooks()$Open(file_location)
#sheet <- book$Worksheets()$Item(1)
#sheet$Select()
#ex[["ActiveSheet"]]$ExportAsFixedFormat(Type=0,Filename="example.pdf", IgnorePrintAreas=FALSE)
#ex$Quit()
# Trying to download as pdf document
# output$download <- downloadHandler(
# filename = function(){
# paste0(combined()$Name,".pdf")
# },
# content = function(file) {
# xlsx::saveWorkbook(combined()$wb, file = file)
# }
# )
}
# Run the application
shinyApp(ui = ui, server = server)