IGFAE.budget.R

#
library(openxlsx)
library(dplyr)
#
IGFAE_Budget <- openxlsx::read.xlsx("C:/Users/Ricardo Rodríguez/Universidade de Santiago de Compostela/IGFAE-EB - Documents/Strategy/IGFAE.Budget.xlsx")
IGFAE_Budget <- IGFAE_Budget %>% rename(gruposDeCostos = categoryToReport) %>% rename(ano = year) 
#
IGFAE_BudgetSubset <- IGFAE_Budget[which(IGFAE_Budget$ano %in% c(2019,2020,2021)),]
#
print(format(round(addmargins(xtabs(amount ~ substr(concept,1,50) + ano, IGFAE_BudgetSubset[grepl('.*?',  IGFAE_BudgetSubset$gruposDeCostos),c('concept','ano','amount')]), margin = 1)),decimal.mark=",", big.mark=".",small.mark=".", small.interval=3, nsmall=0), quote = FALSE)
#
for (item in sort(unique(IGFAE_BudgetSubset$gruposDeCostos))){
print(item)
print(format(round(addmargins(xtabs(amount ~ substr(concept,1,50) + ano, IGFAE_BudgetSubset[grepl(item,  IGFAE_BudgetSubset$gruposDeCostos),c('concept','ano','amount')]), margin = 1)),decimal.mark=",", big.mark=".",small.mark=".", small.interval=3, nsmall=0), quote = FALSE)
}
#
IGFAE_BudgetSubsetSummary <- addmargins(xtabs(amount ~ gruposDeCostos + ano, IGFAE_BudgetSubset), margin = 1)
print(format(round(IGFAE_BudgetSubsetSummary),decimal.mark=",", big.mark=".",small.mark=".", small.interval=3, nsmall=0), quote = FALSE)
file = paste("C:/Users/Ricardo Rodríguez/Universidade de Santiago de Compostela/IGFAE-EB - Documents/Strategy/IGFAE_BudgetSubsetSummary",".xlsx",sep = '') 
#
openxlsx::write.xlsx(IGFAE_BudgetSubsetSummary, file, sheetName="summary",  col.names=TRUE, row.names=FALSE, append=FALSE, showNA=TRUE)
#
## create a workbook
wb <- createWorkbook()
addWorksheet(wb, "summary")
## modify base font to size 10 Arial Narrow in red
modifyBaseFont(wb, fontSize = 9, fontName = "Calibri")
writeData(wb, "summary", IGFAE_BudgetSubsetSummary)
setColWidths(wb, sheet = 1, cols = 1:4, widths = "auto")
## font colour does not affect tables
saveWorkbook(wb, file, overwrite = TRUE)
#
#
for (item in sort(unique(IGFAE_BudgetSubset$priority20190205Cat))){
print(item)
print(format(round(addmargins(xtabs(amount ~ substr(concept,1,50) + ano, IGFAE_BudgetSubset[grepl(item,  IGFAE_BudgetSubset$priority20190205Cat),c('concept','ano','amount')]), margin = 1)),decimal.mark=",", big.mark=".",small.mark=".", small.interval=3, nsmall=0), quote = FALSE)
}
#