I'm developing a Shiny app that processes financial data and generates a formatted Excel report. The data processing part works perfectly as a standalone script, but when integrated into Shiny, the openxlsx package does not seem to apply the formatting and formulas as expected.
Here's the standalone script that prepares the budget_data and dataset frames:
library(dplyr)
library(tidyr)
library(stringi)
library(lubridate)
library(readr)
set.seed(123) # for reproducibility
# Sample size
num_rows <- 18250
# Updated object codes and names
object_codes <- c("A01", "A14", "AA1", "B01", "B02", "B05", "B10", "B11", "B91", "D09", "E01", "E02", "E06", "E12", "E16", "E18", "E90", "EE2", "F05", "F21", "F24", "HH2", "J25", "K03", "K04", "L63", "P01", "PP1", "U03", "U04", "U10")
object_names <- c("Salaries", "Stipends, Bonus Pay, and Awards", "Salaries: Suppl", "Out-Of-State Travel", "In-State Travel", "Conference, Training, Registration and Membership Dues", "Exigent Job-Related Expense", "Employer Refund", "Employee Reimbursement Accounts Payable Non-Tax", "Fringe Benefit Cost Recoupment", "Office & Administrative Supplies", "Printing Expenses & Supplies", "Postage", "Subscriptions, Memberships & Licensing Fees", "Indirect Cost Recoupment", "State Single Audit Chargeback", "DEP INDIRECT", "Conference, Training, Registration and Membership Dues", "Laboratory Supplies", "Navigational & Nautical Supplies", "Motor Vehicle Maintenance and Repair Parts", "Engineering, Research, and Scientific Services", "Laboratory and Pharmaceutical Services", "Programmatic Facility Equipment", "Motor Vehicle Equipment", "Programmatic Equipment Maintenance and Repair", "Grants to Public Entities", "Grants to Non-Public Entities", "SOFTWARE - IT", "Information Technology Chargeback", "IT Equipment Maintenance & Repairs")
# Mapping object codes to names
object_map <- setNames(object_names, object_codes)
# Randomized data
dataset <- data.frame(
Expr1000 = sample(c("03-Apr-20", "16-Apr-20", "20-Apr-20", "25-Apr-20"), num_rows, replace = TRUE),
cash_expense_amount = runif(num_rows, min = 10, max = 10000),
appropriation = sample(10000000:100000000, num_rows, replace = TRUE),
object = sample(object_codes, num_rows, replace = TRUE),
ref_doc_identifier = replicate(num_rows, paste0(sample(c(LETTERS, 0:9), 16, replace = TRUE), collapse = "")),
legal_name = replicate(num_rows, paste0(sample(LETTERS, 20, replace = TRUE), collapse = "")),
program = replicate(num_rows, paste0(sample(LETTERS, 10, replace = TRUE), collapse = "")),
activity = sample(c("AR06", "AR19", NA), num_rows, replace = TRUE, prob = c(1/3, 1/3, 1/3)),
acceptance_date = sample(seq(as.Date('2020-01-01'), as.Date('2024-12-31'), by="day"), num_rows, replace = TRUE)
)
# Replace object codes with corresponding names
dataset$object_name <- object_map[dataset$object]
# Rearrange columns to place object_name after object
dataset <- dataset %>% select(Expr1000, cash_expense_amount, appropriation, object, object_name, everything())
# View the dataset
head(dataset)
write.csv(dataset,"Input_Full.csv", row.names = FALSE)
############################################################################################
############################################################################################
# Load necessary libraries
library(dplyr)
# Define the object codes and names as per the image provided
object_codes <- c("A01", "A14", "AA1", "B01", "B02", "B05", "B10", "B11", "B91", "D09", "E01", "E02", "E06", "E12", "E16", "E18", "E90", "EE2", "F05", "F21", "F24", "HH2", "J25", "K03", "K04", "L63", "P01", "PP1", "U03", "U04", "U10")
subsidiaries <- c("Salaries", "Stipends, Bonus Pay, and Awards", "Salaries: Suppl", "Out-Of-State Travel", "In-State Travel", "Conference, Training, Registration and Membership Dues", "Exigent Job-Related Expense", "Employer Refund", "Employee Reimbursement Accounts Payable Non-Tax", "Fringe Benefit Cost Recoupment", "Office & Administrative Supplies", "Printing Expenses & Supplies", "Postage", "Subscriptions, Memberships & Licensing Fees", "Indirect Cost Recoupment", "State Single Audit Chargeback", "DEP INDIRECT", "Conference, Training, Registration and Membership Dues", "Laboratory Supplies", "Navigational & Nautical Supplies", "Motor Vehicle Maintenance and Repair Parts", "Engineering, Research, and Scientific Services", "Laboratory and Pharmaceutical Services", "Programmatic Facility Equipment", "Motor Vehicle Equipment", "Programmatic Equipment Maintenance and Repair", "Grants to Public Entities", "Grants to Non-Public Entities", "SOFTWARE - IT", "Information Technology Chargeback", "IT Equipment Maintenance & Repairs")
# Create a vector for ApplicationUSD with the amounts provided
application_usd <- c(5408428, NA, NA, NA, 120000, NA, NA, NA, NA, 2050336, 16000, 4000, NA, 236000, 904004, 2800, 1539488, 16000, 153000, NA, NA, NA, 323000, 169000, NA, NA, 1161924, NA, NA, NA, 440000)
# Create placeholders for the other financial figures which are not provided
revisions <- rep(NA, length(object_codes))
ffy_budget <- rep(NA, length(object_codes))
encumbrance <- rep(NA, length(object_codes))
# Generate the budget_data data frame
budget_data <- data.frame(
object = object_codes,
Subsidiary = subsidiaries,
ApplicationUSD = application_usd,
Revisions = revisions,
FFY_Budget = ffy_budget,
Encumbrance = encumbrance
)
# View the budget_data
print(budget_data)
# If you need to write out this dataset, uncomment the following line:
write.csv(budget_data, "budget_data_StackQ.csv", row.names = FALSE)
When I use code for the expenses generation inside the Shiny server function, the Excel report generation doesn't work as intended. Specifically, it loses formatting and doesn't show formulas results in the last row and the last two columns. It was indicated to me that there must be issues within the Shiny reactive context - I'm not sure, quite novice with this. Is there a specific scope or reactive issue that I need to address? Help, SOS.
Here is the code:
library(shiny)
library(readr)
library(dplyr)
library(openxlsx)
library(lubridate)
# UI Definition
ui <- fluidPage(
titlePanel("Budget Analysis Workflow"),
sidebarLayout(
sidebarPanel(
dateRangeInput("dateRange", "Select Date Range:",
start = Sys.Date() - 30, end = Sys.Date()),
selectInput("activityCode", "Select Activity Code:",
choices = c("AR06", "AR19")),
actionButton("generate", "Generate Report")
),
mainPanel(
downloadButton("downloadReport", "Download Report")
)
)
)
# Server Logic
server <- function(input, output) {
observeEvent(input$generate, {
# Reactive expression to handle data processing
processed_data <- reactive({
# Read datasets
input_full <- dataset # created in above code
budget_data <- budget_data # created in above code
# Process data
input_full <- input_full %>%
mutate(acceptance_date = dmy(acceptance_date)) %>%
filter(acceptance_date >= input$dateRange[1] & acceptance_date <= input$dateRange[2],
activity %in% input$activityCode)
expenditure_totals <- input_full %>%
group_by(object) %>%
summarise(Total_Expenditures = sum(cash_expense_amount, na.rm = TRUE)) %>%
ungroup()
final_data <- left_join(budget_data, expenditure_totals, by = "object") %>%
select(object, Subsidiary, ApplicationUSD, Revisions, Total_Expenditures, Encumbrance)
return(final_data)
})
# Generate report
output$downloadReport <- downloadHandler(
filename = function() {
paste0("Summary_Report_", Sys.Date(), ".xlsx")
},
content = function(file) {
# ----- Excel Output Preparation -----
wb <- createWorkbook()
addWorksheet(wb, "Summary Report")
# ----- Add Custom Headers -----
# First row: "FFY2022 MONTHLY ACCOUNT UPDATE"
update_title <- "FFY2022 MONTHLY ACCOUNT UPDATE"
writeData(wb, "Summary Report", x = update_title, startRow = 1, startCol = 1)
mergeCells(wb, "Summary Report", rows = 1, cols = 1:8)
# Second row: Customizable merged cells and text (example below)
# You can customize the merges and the text as needed
second_row_texts <- c("Custom Text 1", "Custom Text 2")
writeData(wb, "Summary Report", x = second_row_texts[1], startRow = 2, startCol = 1)
writeData(wb, "Summary Report", x = second_row_texts[2], startRow = 2, startCol = 5)
mergeCells(wb, "Summary Report", rows = 2, cols = 1:4)
mergeCells(wb, "Summary Report", rows = 2, cols = 5:8)
# Third row: Blank
# Nothing to do since it's blank by default
# Fourth row: Date Range Identifier
date_range_identifier <- paste("Date Range: ", format(start_date, "%Y-%m-%d"), " to ", format(end_date, "%Y-%m-%d"))
writeData(wb, "Summary Report", x = date_range_identifier, startRow = 4, startCol = 1)
mergeCells(wb, "Summary Report", rows = 4, cols = 1:8)
# ----- Data Writing Section -----
# Explicitly write each header in its respective cell
header_titles <- c("Object", "Subsidiary", "Application USD", "Revisions",
"Total Expenditures", "Encumbrance", "Balance", "% Spent")
for (col in 1:length(header_titles)) {
writeData(wb, "Summary Report", x = header_titles[col], startRow = 5, startCol = col)
}
# Write data to the worksheet, starting from the sixth row
writeData(wb, "Summary Report", final_data, startRow = 5)
# headers <- c("Object", "Subsidiary", "Application USD", "Revisions", "Total Expenditures", "Encumbrance", "Balance", "% Spent")
# # Write headers and data to the worksheet, starting from the fifth row
# writeData(wb, "Summary Report", x = headers, startCol = 1, startRow = 5, colNames = T) # Headers now include colNames = TRUE
# writeData(wb, "Summary Report", final_data, startRow = 5) # Data starts from row 6
# ----- Calculate FINAL ROW TOTALS and Write Formulas -----
num_rows <- nrow(final_data)
totals_row <- num_rows + 6 # Adjusted for the shift
# Calculate the TOTALS using R and write them to the worksheet
totals <- c("TOTALS", "", sum(final_data$ApplicationUSD), sum(final_data$Revisions), sum(final_data$Total_Expenditures), sum(final_data$Encumbrance), "", "")
writeData(wb, "Summary Report", x = totals, startRow = totals_row)
# Define formulas for 'Balance' and '% Spent' in R
balance_formula <- sprintf("=C%d-E%d", totals_row, totals_row)
percent_spent_formula <- sprintf("=IF(C%d=0, \"\", (E%d/C%d)*100)", totals_row, totals_row, totals_row)
# Write formulas to the worksheet
writeFormula(wb, "Summary Report", startRow = totals_row, startCol = 7, x = balance_formula) # 'Balance' formula in column 7
writeFormula(wb, "Summary Report", startRow = totals_row, startCol = 8, x = percent_spent_formula) # '% Spent' formula in column 8
# New formulas for columns C through F in the 'TOTAL' row
total_ApplicationUSD_formula <- sprintf("=SUM(C2:C%d)", totals_row - 1)
total_Revisions_formula <- sprintf("=SUM(D2:D%d)", totals_row - 1)
total_Expenditures_formula <- sprintf("=SUM(E2:E%d)", totals_row - 1)
total_Encumbrance_formula <- sprintf("=SUM(F2:F%d)", totals_row - 1)
# Write new formulas to the worksheet
writeFormula(wb, "Summary Report", startRow = totals_row, startCol = 3, x = total_ApplicationUSD_formula)
writeFormula(wb, "Summary Report", startRow = totals_row, startCol = 4, x = total_Revisions_formula)
writeFormula(wb, "Summary Report", startRow = totals_row, startCol = 5, x = total_Expenditures_formula)
writeFormula(wb, "Summary Report", startRow = totals_row, startCol = 6, x = total_Encumbrance_formula)
# ----- Formulas Section -----
# Add formulas for 'Balance' and '% Spent'
num_rows <- nrow(final_data)
for (i in 1:num_rows) {
row_num <- i + 5 # Adjust row number for header and data
# Formulas
balance_formula <- sprintf("=C%d-E%d", row_num, row_num)
# Updated % Spent formula to display values as percentages
percent_spent_formula <- sprintf("=IF(C%d=0, \"\", (E%d/C%d))", row_num, row_num, row_num) #dont have to multiply by 100... why?
# Write formulas
writeFormula(wb, "Summary Report", startRow = row_num, startCol = 7, x = balance_formula) # 'Balance' formula in column 6
writeFormula(wb, "Summary Report", startRow = row_num, startCol = 8, x = percent_spent_formula) # '% Spent' formula in column 7
}
# =============================================================================
# Styling Section
# =============================================================================
# ----- Styling Section -----
# Define combined styles including full borders for each section
generalStyle <- createStyle(fontSize = 12, numFmt = "#,##0.00", border = "TopBottomLeftRight", borderColour = "black", borderStyle = "thin")
firstRowStyle <- createStyle(fontSize = 15, fontColour = "#FF0000", fgFill = "#ADD8E6", textDecoration = "Bold", halign = "center", border = "TopBottomLeftRight", borderColour = "black", borderStyle = "thin")
secondRowStyle <- createStyle(fontSize = 12, fontColour = "black", fgFill = "#ADD8E6", textDecoration = "Bold", halign = "center", border = "TopBottomLeftRight", borderColour = "black", borderStyle = "thin")
lastRowStyle <- createStyle(numFmt = "#,##0.00", fontSize = 12, fontColour = "#008000", textDecoration = "Bold", border = "TopBottomLeftRight", borderColour = "black", borderStyle = "thin")
firstColumnStyle <- createStyle(textDecoration = "Bold", fontSize = 12.5, border = "TopBottomLeftRight", borderColour = "black", borderStyle = "thin")
percentStyle <- createStyle(numFmt = "0.00%", border = "TopBottomLeftRight", borderColour = "black", borderStyle = "thin")
# Apply specific styles first
addStyle(wb, "Summary Report", style = firstRowStyle, rows = 1, cols = 1:8, gridExpand = TRUE)
addStyle(wb, "Summary Report", style = secondRowStyle, rows = 2, cols = 1:8, gridExpand = TRUE)
addStyle(wb, "Summary Report", style = lastRowStyle, rows = totals_row, cols = 1:8, gridExpand = TRUE)
addStyle(wb, "Summary Report", style = firstColumnStyle, rows = 3:(totals_row), cols = 1, gridExpand = TRUE)
# Apply general styles next, ensuring it covers column B
addStyle(wb, "Summary Report", style = generalStyle, rows = 3:(totals_row - 1), cols = 2:7, gridExpand = TRUE)
addStyle(wb, "Summary Report", style = percentStyle, rows = 3:(totals_row - 1), cols = 8, gridExpand = TRUE)
# Apply conditional formatting for negative values in columns G and H
negativeValueStyleRed <- createStyle(fontColour = "#FF0000", numFmt = "#,##0.00")
conditionalFormatting(wb, "Summary Report", cols = 7:8, rows = 3:(totals_row - 1), rule = "<0", style = negativeValueStyleRed)
# Set the height of the first and last rows
setRowHeights(wb, "Summary Report", rows = 1, heights = 30)
setRowHeights(wb, "Summary Report", rows = 2, heights = 30)
setRowHeights(wb, "Summary Report", rows = totals_row, heights = 30)
setRowHeights(wb, "Summary Report", rows = 3:33, heights = 20)
setColWidths(wb, "Summary Report", cols = 1, widths = 15)
setColWidths(wb, "Summary Report", cols = 2, widths = 53)
setColWidths(wb, "Summary Report", cols = 3:8, widths = 15)
saveWorkbook(wb, file, overwrite = T)
}
)
})
}
# Create Shiny App
shinyApp(ui = ui, server = server)
Environment:
R version: 4.1.2
Shiny version: 1.7.1
openxlsx version: 4.2.4
After making the changes in my comment the following appears to work.
start_date to input$dateRange[1], end_date to input$dateRange[2] and pulling final_data from the reactive value storing the data frame (final_data <- dtr()).observeEvent(input$generate, to use a reactive value dtr to store the data frame, and unnesting output$downloadReport.dataset and budget_data are visible to shiny.You may also have to enable editing in Excel to allow the formula to update.
library(shiny)
library(readr)
library(dplyr)
library(openxlsx)
library(lubridate)
library(dplyr)
library(tidyr)
library(stringi)
library(lubridate)
library(readr)
set.seed(123) # for reproducibility
# Sample size
num_rows <- 18250
# Updated object codes and names
object_codes <- c("A01", "A14", "AA1", "B01", "B02", "B05", "B10", "B11", "B91", "D09", "E01", "E02", "E06", "E12", "E16", "E18", "E90", "EE2", "F05", "F21", "F24", "HH2", "J25", "K03", "K04", "L63", "P01", "PP1", "U03", "U04", "U10")
object_names <- c("Salaries", "Stipends, Bonus Pay, and Awards", "Salaries: Suppl", "Out-Of-State Travel", "In-State Travel", "Conference, Training, Registration and Membership Dues", "Exigent Job-Related Expense", "Employer Refund", "Employee Reimbursement Accounts Payable Non-Tax", "Fringe Benefit Cost Recoupment", "Office & Administrative Supplies", "Printing Expenses & Supplies", "Postage", "Subscriptions, Memberships & Licensing Fees", "Indirect Cost Recoupment", "State Single Audit Chargeback", "DEP INDIRECT", "Conference, Training, Registration and Membership Dues", "Laboratory Supplies", "Navigational & Nautical Supplies", "Motor Vehicle Maintenance and Repair Parts", "Engineering, Research, and Scientific Services", "Laboratory and Pharmaceutical Services", "Programmatic Facility Equipment", "Motor Vehicle Equipment", "Programmatic Equipment Maintenance and Repair", "Grants to Public Entities", "Grants to Non-Public Entities", "SOFTWARE - IT", "Information Technology Chargeback", "IT Equipment Maintenance & Repairs")
# Mapping object codes to names
object_map <- setNames(object_names, object_codes)
# Randomized data
dataset <- data.frame(
Expr1000 = sample(c("03-Apr-20", "16-Apr-20", "20-Apr-20", "25-Apr-20"), num_rows, replace = TRUE),
cash_expense_amount = runif(num_rows, min = 10, max = 10000),
appropriation = sample(10000000:100000000, num_rows, replace = TRUE),
object = sample(object_codes, num_rows, replace = TRUE),
ref_doc_identifier = replicate(num_rows, paste0(sample(c(LETTERS, 0:9), 16, replace = TRUE), collapse = "")),
legal_name = replicate(num_rows, paste0(sample(LETTERS, 20, replace = TRUE), collapse = "")),
program = replicate(num_rows, paste0(sample(LETTERS, 10, replace = TRUE), collapse = "")),
activity = sample(c("AR06", "AR19", NA), num_rows, replace = TRUE, prob = c(1/3, 1/3, 1/3)),
acceptance_date = sample(seq(as.Date('2020-01-01'), as.Date('2024-12-31'), by="day"), num_rows, replace = TRUE)
)
# Replace object codes with corresponding names
dataset$object_name <- object_map[dataset$object]
# Rearrange columns to place object_name after object
dataset <- dataset %>% select(Expr1000, cash_expense_amount, appropriation, object, object_name, everything())
# View the dataset
head(dataset)
write.csv(dataset,"Input_Full.csv", row.names = FALSE)
############################################################################################
############################################################################################
# Load necessary libraries
library(dplyr)
# Define the object codes and names as per the image provided
object_codes <- c("A01", "A14", "AA1", "B01", "B02", "B05", "B10", "B11", "B91", "D09", "E01", "E02", "E06", "E12", "E16", "E18", "E90", "EE2", "F05", "F21", "F24", "HH2", "J25", "K03", "K04", "L63", "P01", "PP1", "U03", "U04", "U10")
subsidiaries <- c("Salaries", "Stipends, Bonus Pay, and Awards", "Salaries: Suppl", "Out-Of-State Travel", "In-State Travel", "Conference, Training, Registration and Membership Dues", "Exigent Job-Related Expense", "Employer Refund", "Employee Reimbursement Accounts Payable Non-Tax", "Fringe Benefit Cost Recoupment", "Office & Administrative Supplies", "Printing Expenses & Supplies", "Postage", "Subscriptions, Memberships & Licensing Fees", "Indirect Cost Recoupment", "State Single Audit Chargeback", "DEP INDIRECT", "Conference, Training, Registration and Membership Dues", "Laboratory Supplies", "Navigational & Nautical Supplies", "Motor Vehicle Maintenance and Repair Parts", "Engineering, Research, and Scientific Services", "Laboratory and Pharmaceutical Services", "Programmatic Facility Equipment", "Motor Vehicle Equipment", "Programmatic Equipment Maintenance and Repair", "Grants to Public Entities", "Grants to Non-Public Entities", "SOFTWARE - IT", "Information Technology Chargeback", "IT Equipment Maintenance & Repairs")
# Create a vector for ApplicationUSD with the amounts provided
application_usd <- c(5408428, NA, NA, NA, 120000, NA, NA, NA, NA, 2050336, 16000, 4000, NA, 236000, 904004, 2800, 1539488, 16000, 153000, NA, NA, NA, 323000, 169000, NA, NA, 1161924, NA, NA, NA, 440000)
# Create placeholders for the other financial figures which are not provided
revisions <- rep(NA, length(object_codes))
ffy_budget <- rep(NA, length(object_codes))
encumbrance <- rep(NA, length(object_codes))
# Generate the budget_data data frame
budget_data <- data.frame(
object = object_codes,
Subsidiary = subsidiaries,
ApplicationUSD = application_usd,
Revisions = revisions,
FFY_Budget = ffy_budget,
Encumbrance = encumbrance
)
# View the budget_data
print(budget_data)
# If you need to write out this dataset, uncomment the following line:
write.csv(budget_data, "budget_data_StackQ.csv", row.names = FALSE)
# UI Definition
ui <- fluidPage(
titlePanel("Budget Analysis Workflow"),
sidebarLayout(
sidebarPanel(
dateRangeInput("dateRange", "Select Date Range:",
start = Sys.Date() - 30, end = Sys.Date()),
selectInput("activityCode", "Select Activity Code:",
choices = c("AR06", "AR19")),
actionButton("generate", "Generate Report")
),
mainPanel(
downloadButton("downloadReport", "Download Report")
)
)
)
# Server Logic
server <- function(input, output) {
dtr <- reactiveVal()
observeEvent(input$generate, {
# Reactive expression to handle data processing
# Read datasets
input_full <- dataset # created in above code
budget_data <- budget_data # created in above code
# Process data
input_full <- input_full %>%
mutate(acceptance_date = ymd(acceptance_date)) %>%
filter(acceptance_date >= input$dateRange[1] & acceptance_date <= input$dateRange[2],
activity %in% input$activityCode)
expenditure_totals <- input_full %>%
group_by(object) %>%
summarise(Total_Expenditures = sum(cash_expense_amount, na.rm = TRUE)) %>%
ungroup()
final_data <- left_join(budget_data, expenditure_totals, by = "object") %>%
select(object, Subsidiary, ApplicationUSD, Revisions, Total_Expenditures, Encumbrance)
dtr(final_data)
})
# Generate report
output$downloadReport <- downloadHandler(
filename = function() {
paste0("Summary_Report_", Sys.Date(), ".xlsx")
},
content = function(file) {
# ----- Excel Output Preparation -----
wb <- createWorkbook()
addWorksheet(wb, "Summary Report")
# ----- Add Custom Headers -----
# First row: "FFY2022 MONTHLY ACCOUNT UPDATE"
update_title <- "FFY2022 MONTHLY ACCOUNT UPDATE"
writeData(wb, "Summary Report", x = update_title, startRow = 1, startCol = 1)
mergeCells(wb, "Summary Report", rows = 1, cols = 1:8)
# Second row: Customizable merged cells and text (example below)
# You can customize the merges and the text as needed
second_row_texts <- c("Custom Text 1", "Custom Text 2")
writeData(wb, "Summary Report", x = second_row_texts[1], startRow = 2, startCol = 1)
writeData(wb, "Summary Report", x = second_row_texts[2], startRow = 2, startCol = 5)
mergeCells(wb, "Summary Report", rows = 2, cols = 1:4)
mergeCells(wb, "Summary Report", rows = 2, cols = 5:8)
# Third row: Blank
# Nothing to do since it's blank by default
# Fourth row: Date Range Identifier
date_range_identifier <- paste("Date Range: ", format(input$dateRange[1], "%Y-%m-%d"), " to ", format(input$dateRange[2], "%Y-%m-%d"))
writeData(wb, "Summary Report", x = date_range_identifier, startRow = 4, startCol = 1)
mergeCells(wb, "Summary Report", rows = 4, cols = 1:8)
# ----- Data Writing Section -----
# Explicitly write each header in its respective cell
header_titles <- c("Object", "Subsidiary", "Application USD", "Revisions",
"Total Expenditures", "Encumbrance", "Balance", "% Spent")
for (col in 1:length(header_titles)) {
writeData(wb, "Summary Report", x = header_titles[col], startRow = 5, startCol = col)
}
final_data <- dtr()
# Write data to the worksheet, starting from the sixth row
writeData(wb, "Summary Report", final_data, startRow = 5)
# headers <- c("Object", "Subsidiary", "Application USD", "Revisions", "Total Expenditures", "Encumbrance", "Balance", "% Spent")
# # Write headers and data to the worksheet, starting from the fifth row
# writeData(wb, "Summary Report", x = headers, startCol = 1, startRow = 5, colNames = T) # Headers now include colNames = TRUE
# writeData(wb, "Summary Report", final_data, startRow = 5) # Data starts from row 6
# ----- Calculate FINAL ROW TOTALS and Write Formulas -----
num_rows <- nrow(final_data)
totals_row <- num_rows + 6 # Adjusted for the shift
# Calculate the TOTALS using R and write them to the worksheet
totals <- c("TOTALS", "", sum(final_data$ApplicationUSD), sum(final_data$Revisions), sum(final_data$Total_Expenditures), sum(final_data$Encumbrance), "", "")
writeData(wb, "Summary Report", x = totals, startRow = totals_row)
# Define formulas for 'Balance' and '% Spent' in R
balance_formula <- sprintf("=C%d-E%d", totals_row, totals_row)
percent_spent_formula <- sprintf("=IF(C%d=0, \"\", (E%d/C%d)*100)", totals_row, totals_row, totals_row)
# Write formulas to the worksheet
writeFormula(wb, "Summary Report", startRow = totals_row, startCol = 7, x = balance_formula) # 'Balance' formula in column 7
writeFormula(wb, "Summary Report", startRow = totals_row, startCol = 8, x = percent_spent_formula) # '% Spent' formula in column 8
# New formulas for columns C through F in the 'TOTAL' row
total_ApplicationUSD_formula <- sprintf("=SUM(C2:C%d)", totals_row - 1)
total_Revisions_formula <- sprintf("=SUM(D2:D%d)", totals_row - 1)
total_Expenditures_formula <- sprintf("=SUM(E2:E%d)", totals_row - 1)
total_Encumbrance_formula <- sprintf("=SUM(F2:F%d)", totals_row - 1)
# Write new formulas to the worksheet
writeFormula(wb, "Summary Report", startRow = totals_row, startCol = 3, x = total_ApplicationUSD_formula)
writeFormula(wb, "Summary Report", startRow = totals_row, startCol = 4, x = total_Revisions_formula)
writeFormula(wb, "Summary Report", startRow = totals_row, startCol = 5, x = total_Expenditures_formula)
writeFormula(wb, "Summary Report", startRow = totals_row, startCol = 6, x = total_Encumbrance_formula)
# ----- Formulas Section -----
# Add formulas for 'Balance' and '% Spent'
num_rows <- nrow(final_data)
for (i in 1:num_rows) {
row_num <- i + 5 # Adjust row number for header and data
# Formulas
balance_formula <- sprintf("=C%d-E%d", row_num, row_num)
# Updated % Spent formula to display values as percentages
percent_spent_formula <- sprintf("=IF(C%d=0, \"\", (E%d/C%d))", row_num, row_num, row_num) #dont have to multiply by 100... why?
# Write formulas
writeFormula(wb, "Summary Report", startRow = row_num, startCol = 7, x = balance_formula) # 'Balance' formula in column 6
writeFormula(wb, "Summary Report", startRow = row_num, startCol = 8, x = percent_spent_formula) # '% Spent' formula in column 7
}
# =============================================================================
# Styling Section
# =============================================================================
# ----- Styling Section -----
# Define combined styles including full borders for each section
generalStyle <- createStyle(fontSize = 12, numFmt = "#,##0.00", border = "TopBottomLeftRight", borderColour = "black", borderStyle = "thin")
firstRowStyle <- createStyle(fontSize = 15, fontColour = "#FF0000", fgFill = "#ADD8E6", textDecoration = "Bold", halign = "center", border = "TopBottomLeftRight", borderColour = "black", borderStyle = "thin")
secondRowStyle <- createStyle(fontSize = 12, fontColour = "black", fgFill = "#ADD8E6", textDecoration = "Bold", halign = "center", border = "TopBottomLeftRight", borderColour = "black", borderStyle = "thin")
lastRowStyle <- createStyle(numFmt = "#,##0.00", fontSize = 12, fontColour = "#008000", textDecoration = "Bold", border = "TopBottomLeftRight", borderColour = "black", borderStyle = "thin")
firstColumnStyle <- createStyle(textDecoration = "Bold", fontSize = 12.5, border = "TopBottomLeftRight", borderColour = "black", borderStyle = "thin")
percentStyle <- createStyle(numFmt = "0.00%", border = "TopBottomLeftRight", borderColour = "black", borderStyle = "thin")
# Apply specific styles first
addStyle(wb, "Summary Report", style = firstRowStyle, rows = 1, cols = 1:8, gridExpand = TRUE)
addStyle(wb, "Summary Report", style = secondRowStyle, rows = 2, cols = 1:8, gridExpand = TRUE)
addStyle(wb, "Summary Report", style = lastRowStyle, rows = totals_row, cols = 1:8, gridExpand = TRUE)
addStyle(wb, "Summary Report", style = firstColumnStyle, rows = 3:(totals_row), cols = 1, gridExpand = TRUE)
# Apply general styles next, ensuring it covers column B
addStyle(wb, "Summary Report", style = generalStyle, rows = 3:(totals_row - 1), cols = 2:7, gridExpand = TRUE)
addStyle(wb, "Summary Report", style = percentStyle, rows = 3:(totals_row - 1), cols = 8, gridExpand = TRUE)
# Apply conditional formatting for negative values in columns G and H
negativeValueStyleRed <- createStyle(fontColour = "#FF0000", numFmt = "#,##0.00")
conditionalFormatting(wb, "Summary Report", cols = 7:8, rows = 3:(totals_row - 1), rule = "<0", style = negativeValueStyleRed)
# Set the height of the first and last rows
setRowHeights(wb, "Summary Report", rows = 1, heights = 30)
setRowHeights(wb, "Summary Report", rows = 2, heights = 30)
setRowHeights(wb, "Summary Report", rows = totals_row, heights = 30)
setRowHeights(wb, "Summary Report", rows = 3:33, heights = 20)
setColWidths(wb, "Summary Report", cols = 1, widths = 15)
setColWidths(wb, "Summary Report", cols = 2, widths = 53)
setColWidths(wb, "Summary Report", cols = 3:8, widths = 15)
saveWorkbook(wb, file, overwrite = T)
}
)
}
# Create Shiny App
shinyApp(ui = ui, server = server)