This manual aims to demonstrate how to export results to Excel. This time, I will teach you how to do it with a list.
## Create a new workbook
wb <- createWorkbook()
## Add a worksheet
addWorksheet(wb, "Resultados")
## set col widths:
writeData(wb, sheet = 1 ,x = Datos_HPF_ACT)
setColWidths(wb, sheet = 1, cols = c(1:7), widths = c(7,13,13,rep(13,4)))
## create and add a style to the column headers
headerStyle <- createStyle(fontSize = 12, textDecoration = "BOLD" ,fontColour = "white", # fontColour = Color de la letra
halign = "center",
valign = "center",
fgFill = "#16365C",
border=c("top", "bottom", "left", "right"),
borderColour = "#16365C",wrapText = TRUE)
addStyle(wb, sheet = 1, headerStyle, rows = 1, cols = 1:ncol(Datos_HPF_ACT), gridExpand = TRUE)
cuerpo <- createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center",numFmt = "#,##0.00", border=c("top", "bottom", "left", "right"))
addStyle(wb, sheet = 1, style = cuerpo, rows = 2:(nrow(Datos_HPF_ACT)+1),
cols =c(2:7), gridExpand = TRUE)
freezePane(wb, sheet = 1, firstActiveRow = as.integer(2),
firstActiveCol = 2)
addFilter(wb, sheet = 1, row = 1,
cols = c(1:ncol(Datos_HPF_ACT)))
# Applied condition
gap_col <- 6
cond_format <- createStyle(fontColour = 'black', bgFill = 'gray', textDecoration = "bold")
conditionalFormatting(
wb,
sheet = 1,
cols = gap_col,
rows = 2:(nrow(Datos_HPF_ACT)+1), # Adjust row range if necessary
rule = ">=1",
style = cond_format
)
## Save workbook
saveWorkbook(wb, "Act_Contracíclico.xlsx", overwrite = TRUE)borderStyle <- createStyle(border = c(“top”, “bottom”, “left”, “right”), borderColour = “#A6A6A6”, borderStyle=“medium”): This option allows you to apply a border to the entire table without losing the effects applied previously. I recommend using this option after applying the other formats.
Fecha_P <- dmy("31-10-2024")
Fecha_P_Per <- format(Fecha_P, "%d de %B de %Y", locale = "es_ES.UTF-8")
create_and_format_report <- function(workbook_name, data) {
## Create a new workbook
wb <- createWorkbook()
## Add a worksheet
addWorksheet(wb, "FLPR", gridLines = FALSE)
# Fecha
writeData(wb, sheet = 1, startRow = 1, startCol = 6,
x = c(Fecha_P_Per))
# Segundo Título
writeData(wb, sheet = 1, startRow = 2, startCol = 2,
x = c("DATA SET"))
mergeCells(wb, sheet = 1, cols = 2:6, rows = 2)
ti_o <- createStyle(fontSize = 12, textDecoration = "BOLD" ,fontColour = "black", # fontColour = Color de la letra
valign = "center", halign = "left")
addStyle(wb, sheet = 1, ti_o, rows = 2, cols = 2:6)
# Tercer Título
writeData(wb, sheet = 1, startRow = 3, startCol = 2,
x = c("EVA"))
mergeCells(wb, sheet = 1, cols = 2:5, rows = 3)
addStyle(wb, sheet = 1, ti_o, rows = 3, cols = 2:5)
# Cuarto Título
writeData(wb, sheet = 1, startRow = 4, startCol = 2,
x = paste0("Período consultado:"," ",Fecha_P))
mergeCells(wb, sheet = 1, cols = 2:5, rows = 4)
ti_oo <- createStyle(fontSize = 11, fontColour = "black", # fontColour = Color de la letra
valign = "center", border = "TopBottom",halign = "left")
addStyle(wb , sheet = 1, ti_o, rows = 4, cols = 2:5, gridExpand = TRUE)
#mergeCells(wb, sheet = 1, cols = 2:4, rows = 4)
# Encabezados No tabla
writeData(wb, sheet = 1, startRow = 5, startCol = 2,
x = c("INSTRUMENTO "))
mergeCells(wb, sheet = 1, cols = 2:5, rows = 5)
ti_ol <- createStyle(fontSize = 11, textDecoration = "BOLD" ,fontColour = "white", # fontColour = Color de la letra
valign = "center", border = "TopBottom",halign = "left",
fgFill = "black", borderColour = "#A6A6A6",wrapText = TRUE)
addStyle(wb, sheet = 1, ti_ol, rows = 5, cols = 2:12, gridExpand = TRUE)
writeData(wb, sheet = 1, startRow = 6, startCol = 4,
x = c("DATOS "))
mergeCells(wb, sheet = 1, cols = 4:5, rows = 6)
addStyle(wb, sheet = 1, ti_ol, rows = 6, cols = 2:12, gridExpand = TRUE)
writeData(wb, sheet = 1, startRow = 6, startCol = 6,
x = c("VALOR "))
mergeCells(wb, sheet = 1, cols = 6:10, rows = 6)
addStyle(wb, sheet = 1, ti_ol, rows = 6, cols = 2:11, gridExpand = TRUE)
writeData(wb, sheet = 1, startRow = 6, startCol = 12,
x = c("RIESGO "))
writeData(wb, sheet = 1, x = FLPR_Extr, startRow = 7, startCol = 2)
setColWidths(wb, sheet = 1, cols = c(2:12), widths = c(7,13,rep(16,2),15,rep(15,2),13,16,21,16))
#
# Numeric
valores <- createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center",numFmt = "#,##0.00")
addStyle(wb, sheet = 1, style = valores, rows = 8:(nrow(FLPR_Extr) + 6),
cols =c(7,8,10,11,12), gridExpand = TRUE)
#
#Centrar
Centrar <- createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center")
addStyle(wb, sheet = 1, style = Centrar, rows = 7:(nrow(FLPR_Extr) + 6),
cols = c(2,3,4,9), gridExpand = TRUE)
#
#Centrar Fechas
Centrar_Fecha <- createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center", numFmt = "dd/mm/yyyy")
addStyle(wb, sheet = 1, style = Centrar_Fecha, rows = 7:(nrow(FLPR_Extr) + 6),
cols = c(5,6), gridExpand = TRUE)
#Wrap
wrapp <- createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center",numFmt = "#,##0.00", wrapText = TRUE )
addStyle(wb, sheet = 1, style = wrapp, rows = 7,
cols =c(9), gridExpand = TRUE)
# pie
FH <- nrow(FLPR_Extr)+7
writeData(wb, sheet = 1, startRow = FH, startCol = 2,
x = c("TOTAL Fixbi"))
mergeCells(wb, sheet = 1, cols = 2:6, rows = nrow(FLPR_Extr) + 7)
POLZ <- createStyle(fontSize = 11, textDecoration = "BOLD" ,fontColour = "black", # fontColour = Color de la letra
valign = "center", border = "TopBottom",halign = "center",
fgFill = "#A6A6A6", borderColour = "#A6A6A6",wrapText = TRUE,
numFmt = "#,##0.00")
addStyle(wb, sheet = 1, POLZ, rows = nrow(FLPR_Extr) + 7, cols = 2:12,gridExpand = TRUE)
ador <- createStyle(wrapText = TRUE, valign = "center", halign = "left")
addStyle(wb, sheet = 1, ador, rows = nrow(FLPR_Extr) + 20, cols = c(4,8,12),
gridExpand = TRUE)
addStyle(wb, sheet = 1, ador, rows = nrow(FLPR_Extr) + 19, cols = c(4,8,12),
gridExpand = TRUE)
addStyle(wb, sheet = 1, ador, rows = nrow(FLPR_Extr) + 19, cols = c(3,7,11),
gridExpand = TRUE)
#NOTAS
FHA <- nrow(FLPR_Extr)+9
# 1
writeData(wb, sheet = 1, startRow = FHA, startCol = 2,
x = c("NOTAS"))
writeData(wb, sheet = 1, startRow = FHA+1, startCol = 2,
x = c("(1) AA: AA1"))
writeData(wb, sheet = 1, startRow = FHA+2, startCol = 2,
x = c("(2) BB: BB1"))
writeData(wb, sheet = 1, startRow = FHA+3, startCol = 2,
x = c("(3) CC: CC1"))
writeData(wb, sheet = 1, startRow = FHA+4, startCol = 2,
x = c("(4) DD: DD1"))
freezePane(wb, sheet = 1, firstActiveRow = as.integer(7),
firstActiveCol = 2)
oca <- createStyle(fontSize = 11, # fontColour = Color de la letra
valign = "center", wrapText = TRUE,textDecoration = "BOLD")
addStyle(wb, sheet = 1, oca, rows = 7, cols = 2:12, gridExpand = TRUE)
borderStyle <- createStyle(border = c("top", "bottom", "left", "right"), borderColour = "#A6A6A6",
borderStyle="medium")
addStyle(wb, sheet = 1, style = borderStyle, rows = 7:(nrow(FLPR_Extr) + 7),
cols = 2:12, gridExpand = TRUE, stack = TRUE)
# Specify the desired folder path
folder_path <- "D:/Manuales/FL_P/Resultados"
# Save the workbook to the specified folder
saveWorkbook(wb, file = paste0(folder_path, "/FLPR", Fecha_P_Per, ".xlsx"), overwrite = TRUE)
}
create_and_format_report("FLPR.xlsx", AA22)Add rounded formulas for H3, K3, H4, and K4 into J3, K3, J4, and K4
rounded_cols <- c(8, 9) # Columns H (8) and K (11) rounded_rows <- c(3, 4) # Rows 3 and 4
for (row in rounded_rows) { for (col in rounded_cols) { # Determine the target column for the rounded result (e.g., J (10) for H (8), L (12) for K (11)) target_col <- col + 2 # Create the ROUND formula for the cell formula <- paste0(“ROUND(”, LETTERS[col], row, “,0)”) # Write the formula to the target cell writeFormula(wb, sheet = 1, startRow = row, startCol = target_col, x = formula) } }
Add subtotals for specified columns
for (col in 2:11) { # Columns B (2) to I (9)
writeFormula(wb, sheet = 1, startRow = 5, startCol = col, x = paste0(“SUM(”, LETTERS[col], “3:”, LETTERS[col], “4)”)) }
# Style for the subtotal row subtotalStyle <- createStyle(fontSize = 11, fontColour = “black”, halign = “center”, valign = “center”, fgFill = “#FFFF00”, border = c(“top”, “bottom”, “left”,“right”), textDecoration = “BOLD”, numFmt = “#,##0.00”) addStyle(wb, sheet = 1, style = subtotalStyle, rows = 5, cols = 1:11, gridExpand = TRUE)
Fecha_A <- dmy("30-11-2024")
create_and_format_workbook <- function(workbook_name, data) {
## Create a new workbook
wb <- createWorkbook()
## Add a worksheet
addWorksheet(wb, "Sheet 1")
# Método 1 (Distribuciones)
writeData(wb, sheet = 1, startRow = 1, startCol = 2,
x = c("Método 1 (Distribuciones)", "", "", ""))
mergeCells(wb, sheet = 1, cols = 2:3, rows = 1)
writeData(wb, sheet = 1, startRow = 1, startCol = 4,
x = c("Método 2 (Bootstrap)", "", "", ""))
mergeCells(wb, sheet = 1, cols = 4:5, rows = 1)
writeData(wb, sheet = 1, startRow = 1, startCol = 6,
x = c("Método 3 (Valores Extremos)", "", "", ""))
mergeCells(wb, sheet = 1, cols = 6:7, rows = 1)
writeData(wb, sheet = 1, startRow = 1, startCol = 8,
x = c("RESULTADOS", "", "", ""))
mergeCells(wb, sheet = 1, cols = 8:9, rows = 1)
writeData(wb, sheet = 1, startRow = 1, startCol = 10,
x = c("RESULTADO APROXIMADO", "", "", ""))
mergeCells(wb, sheet = 1, cols = 10:11, rows = 1)
## set col widths:
writeData(wb, sheet = 1 ,x = Consolidado, startRow = 2)
setColWidths(wb, sheet = 1, cols = c(1:11), widths = c(20,rep(15,10)))
# Método I
writeData(wb, sheet = 1, startRow = 2, startCol = 2,
x = c("Mínimo"))
writeData(wb, sheet = 1, startRow = 2, startCol = 3,
x = c("Objetivo"))
# create and add a style to the column headers
headerStyle_MET_UNO <- createStyle(fontSize = 12, textDecoration = "BOLD" ,fontColour = "black", halign = "center", # fontColour = Color de la letra
valign = "center",fgFill = "#D9E1F2", border = "TopBottom",
borderColour = "black",
wrapText = TRUE)
addStyle(wb, sheet = 1, headerStyle_MET_UNO, rows = c(1), cols = c(1:3), gridExpand = TRUE)
addStyle(wb, sheet = 1, headerStyle_MET_UNO, rows = c(2), cols = c(1:3), gridExpand = TRUE)
# Método II
writeData(wb, sheet = 1, startRow = 2, startCol = 4,
x = c("Mínimo"))
writeData(wb, sheet = 1, startRow = 2, startCol = 5,
x = c("Objetivo"))
# create and add a style to the column headers
headerStyle_MET_DOS <- createStyle(fontSize = 12, textDecoration = "BOLD" ,fontColour = "black", halign = "center", # fontColour = Color de la letra
valign = "center",fgFill = "#FFF2CC", border = "TopBottom",
borderColour = "black",
wrapText = TRUE)
addStyle(wb, sheet = 1, headerStyle_MET_DOS, rows = 1, cols = c(4:5), gridExpand = TRUE)
addStyle(wb, sheet = 1, headerStyle_MET_DOS, rows = 2, cols = c(4:5), gridExpand = TRUE)
# create and add a style to the column headers
headerStyle_MET_TRE <- createStyle(fontSize = 12, textDecoration = "BOLD" ,fontColour = "black", halign = "center", # fontColour = Color de la letra
valign = "center",fgFill = "#E2EFDA", border = "TopBottom",
borderColour = "black",
wrapText = TRUE)
addStyle(wb, sheet = 1, headerStyle_MET_TRE, rows = 1, cols = c(6:7), gridExpand = TRUE)
# create and add a style to the column headers
headerStyle_MET_CUATRO <- createStyle(fontSize = 12, textDecoration = "BOLD" ,fontColour = "black", halign = "center", # fontColour = Color de la letra
valign = "center",fgFill = "#2F75B5", border = "TopBottom",
borderColour = "black",
wrapText = TRUE)
addStyle(wb, sheet = 1, headerStyle_MET_CUATRO, rows = 1, cols = c(8:9), gridExpand = TRUE)
# create and add a style to the column headers
headerStyle_MET_CINCO <- createStyle(fontSize = 12, textDecoration = "BOLD" ,fontColour = "black", halign = "center", # fontColour = Color de la letra
valign = "center",fgFill = "#00B050", border = "TopBottom",
borderColour = "black",
wrapText = TRUE)
addStyle(wb, sheet = 1, headerStyle_MET_CINCO, rows = 1, cols = c(10:11), gridExpand = TRUE)
# Método III
writeData(wb, sheet = 1, startRow = 2, startCol = 6,
x = c("Mínimo"))
writeData(wb, sheet = 1, startRow = 2, startCol = 7,
x = c("Objetivo"))
# Método IV
writeData(wb, sheet = 1, startRow = 2, startCol = 8,
x = c("Mínimo"))
writeData(wb, sheet = 1, startRow = 2, startCol = 9,
x = c("Objetivo"))
# Método IV
writeData(wb, sheet = 1, startRow = 2, startCol = 10,
x = c("Mínimo"))
writeData(wb, sheet = 1, startRow = 2, startCol = 11,
x = c("Objetivo"))
# Numeric
valores <- createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center",numFmt = "#,##0.00", border=c("top", "bottom", "left", "right"))
addStyle(wb, sheet = 1, style = valores, rows = 2:(nrow(Consolidado)+2),
cols =c(2:11), gridExpand = TRUE)
# Add rounded formulas for H3, K3, H4, and K4 into J3, K3, J4, and K4
rounded_cols <- c(8, 9) # Columns H (8) and K (11)
rounded_rows <- c(3, 4) # Rows 3 and 4
for (row in rounded_rows) {
for (col in rounded_cols) {
# Determine the target column for the rounded result (e.g., J (10) for H (8), L (12) for K (11))
target_col <- col + 2
# Create the ROUND formula for the cell
formula <- paste0("ROUND(", LETTERS[col], row, ",0)")
# Write the formula to the target cell
writeFormula(wb, sheet = 1, startRow = row, startCol = target_col, x = formula)
}
}
# Add subtotals for specified columns
for (col in 2:11) { # Columns B (2) to I (9)
writeFormula(wb, sheet = 1, startRow = 5, startCol = col,
x = paste0("SUM(", LETTERS[col], "3:", LETTERS[col], "4)"))
}
# Style for the subtotal row
subtotalStyle <- createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center", fgFill = "#FFFF00", border = c("top", "bottom", "left",
"right"),
textDecoration = "BOLD", numFmt = "#,##0.00")
addStyle(wb, sheet = 1, style = subtotalStyle, rows = 5, cols = 1:11, gridExpand = TRUE)
# Specify the desired folder path
folder_path <- "D:/Documentos/Estadisticos/R/R_studio/Openxlsx/Resultados"
# Extract the month and year in the desired format (e.g., "Oct_2024")
date_suffix <- format(Fecha_A, "%b %Y") # %b gives abbreviated month (e.g., "Oct"), %Y gives year
date_suffix_A <- gsub("\\.", "", date_suffix)
# Define the file path with the date suffix
file_name <- paste0("Resultados_", date_suffix, ".xlsx")
file_path <- file.path(folder_path, file_name)
# Save the workbook to the specified folder
saveWorkbook(wb, file = file_path, overwrite = TRUE)
}
create_and_format_workbook("Resultados.xlsx", Consolidado)Fecha_A <- dmy("30-11-2024")
create_and_format_workbook <- function(workbook_name, data) {
## Create a new workbook
wb <- createWorkbook()
## Add a worksheet
addWorksheet(wb, "Sheet 1")
# Merge A1:A2 and write content
writeData(wb, sheet = 1, startRow = 1, startCol = 1, x = "Tipo") # Adjust content as needed
mergeCells(wb, sheet = 1, cols = 1, rows = 1:2) # Merge A1:A2
# Titles and column groups
titles <- c(
"Método 1 (Distribuciones)",
"Método 2 (Bootstrap)",
"Método 3 (Valores Extremos)",
"RESULTADOS",
"RESULTADO APROXIMADO"
)
col_groups <- list(2:3, 4:5, 6:7, 8:9, 10:11)
for (i in seq_along(titles)) {
writeData(wb, sheet = 1, startRow = 1, startCol = col_groups[[i]][1], x = titles[i])
mergeCells(wb, sheet = 1, cols = col_groups[[i]], rows = 1)
}
# Write data and set column widths
writeData(wb, sheet = 1, x = data, startRow = 2)
setColWidths(wb, sheet = 1, cols = 1:11, widths = c(20, rep(15, 10)))
# Column-specific labels
methods <- list(
"Mínimo" = c(2, 4, 6, 8, 10),
"Objetivo" = c(3, 5, 7, 9, 11)
)
for (label in names(methods)) {
cols <- methods[[label]]
for (col in cols) {
writeData(wb, sheet = 1, startRow = 2, startCol = col, x = label)
}
}
# Styles for headers
header_colors <- c("#D9E1F2", "#FFF2CC", "#E2EFDA", "#2F75B5", "#00B050")
for (i in seq_along(col_groups)) {
style <- createStyle(
fontSize = 12, textDecoration = "BOLD", fontColour = "black", halign = "center",
valign = "center", fgFill = header_colors[i], border = "TopBottom", borderColour = "black",
wrapText = TRUE
)
addStyle(wb, sheet = 1, style, rows = 1, cols = col_groups[[i]], gridExpand = TRUE)
addStyle(wb, sheet = 1, style, rows = 2, cols = col_groups[[i]], gridExpand = TRUE)
}
# Add "Total pagos" in A5
writeData(wb, sheet = 1, startRow = 5, startCol = 1, x = "Total pagos")
# Add numeric style to the data
numeric_style <- createStyle(
fontSize = 11, fontColour = "black", halign = "center", valign = "center",
numFmt = "#,##0.00", border = c("top", "bottom", "left", "right")
)
addStyle(wb, sheet = 1, numeric_style, rows = 2:(nrow(data) + 2), cols = 2:11, gridExpand = TRUE)
# Add rounded formulas
rounded_cols <- c(8, 9)
rounded_rows <- c(3, 4)
for (row in rounded_rows) {
for (col in rounded_cols) {
target_col <- col + 2
formula <- paste0("ROUND(", LETTERS[col], row, ",0)")
writeFormula(wb, sheet = 1, startRow = row, startCol = target_col, x = formula)
}
}
# Add subtotals
for (col in 2:11) {
writeFormula(wb, sheet = 1, startRow = 5, startCol = col,
x = paste0("SUM(", LETTERS[col], "3:", LETTERS[col], "4)"))
}
# Subtotal row style
subtotal_style <- createStyle(
fontSize = 11, fontColour = "black", halign = "center", valign = "center",
fgFill = "#FFFF00", border = c("top", "bottom", "left", "right"),
textDecoration = "BOLD", numFmt = "#,##0.00"
)
addStyle(wb, sheet = 1, subtotal_style, rows = 5, cols = 1:11, gridExpand = TRUE)
# Save workbook
folder_path <- "D:/Documentos/Estadisticos/R/R_studio/Openxlsx/Resultados"
file_name <- paste0("Resultados_", format(Fecha_A, "%b_%Y"), ".xlsx")
saveWorkbook(wb, file = file.path(folder_path, file_name), overwrite = TRUE)
}
# Example call with "Consolidado" data
create_and_format_workbook("Resultados.xlsx", Consolidado)for (report in reports) { create_and_format_report( workbook_name = report\(file_name, data = report\)data, report_type = tools::file_path_sans_ext(report\(file_name), second_title = report\)second_title ) }
# 3. Date initial
#####
Fecha_P <- ymd("2024-12-31")
Fecha_P_AS <- format(Fecha_P, "%d-%b-%Y")
Fecha_P_Per <- gsub("\\.", "",format(Fecha_P, "%b %Y", locale = "es_ES.UTF-8"))
Fecha_P_GR <- gsub("\\.", "",format(Fecha_P, "%b %Y"))
#####
# 4. Export data
#####
create_and_format_report <- function(workbook_name, data, report_type, second_title) {
# Crear un nuevo libro de trabajo
wb <- createWorkbook()
# Agregar una hoja de trabajo
addWorksheet(wb, report_type, gridLines = FALSE)
# Primer Título
writeData(wb, sheet = 1, startRow = 2, startCol = 2,
x = c("GERENCIA DE INVERSIONES- SUBGERENCIA DE INVERSIONES"))
mergeCells(wb, sheet = 1, cols = 2:6, rows = 2)
ti_o <- createStyle(fontSize = 12, textDecoration = "BOLD", fontColour = "black", valign = "center",
halign = "left")
addStyle(wb, sheet = 1, ti_o, rows = 2, cols = 2:6)
# Segundo Título
writeData(wb, sheet = 1, startRow = 3, startCol = 2, x = c(second_title))
mergeCells(wb, sheet = 1, cols = 2:9, rows = 3)
addStyle(wb, sheet = 1, ti_o, rows = 3, cols = 2:9)
# Tercer Título
writeData(wb, sheet = 1, startRow = 4, startCol = 2, x = paste0("Período consultado: ", Fecha_P_AS))
mergeCells(wb, sheet = 1, cols = 2:5, rows = 4)
ti_oo <- createStyle(fontSize = 11, fontColour = "black", valign = "center", border = "TopBottom",
halign = "left")
addStyle(wb, sheet = 1, ti_oo, rows = 4, cols = 2:5, gridExpand = TRUE)
# Encabezados No tabla
writeData(wb, sheet = 1, startRow = 5, startCol = 2,
x = c("INSTRUMENTO FIXBIS"))
mergeCells(wb, sheet = 1, cols = 2:5, rows = 5)
ti_ol <- createStyle(fontSize = 11, textDecoration = "BOLD" ,fontColour = "white", # fontColour = Color de la letra
valign = "center", border = "TopBottom",halign = "left",
fgFill = "black", borderColour = "#A6A6A6",wrapText = TRUE)
addStyle(wb, sheet = 1, ti_ol, rows = 5, cols = 2:12, gridExpand = TRUE)
writeData(wb, sheet = 1, startRow = 6, startCol = 4,
x = c("DATOS INSTRUMENTOS FINANCIERO"))
mergeCells(wb, sheet = 1, cols = 4:5, rows = 6)
addStyle(wb, sheet = 1, ti_ol, rows = 6, cols = 2:12, gridExpand = TRUE)
writeData(wb, sheet = 1, startRow = 6, startCol = 6,
x = c("VALOR DE LA POSICIÓN EN EL MERCADO (MARK TO MARKET)"))
mergeCells(wb, sheet = 1, cols = 6:10, rows = 6)
addStyle(wb, sheet = 1, ti_ol, rows = 6, cols = 2:11, gridExpand = TRUE)
writeData(wb, sheet = 1, startRow = 6, startCol = 12,
x = c("RIESGO DE TASA DE INTERES"))
writeData(wb, sheet = 1, x = data, startRow = 7, startCol = 2)
setColWidths(wb, sheet = 1, cols = c(2:12), widths = c(7,13,rep(16,2),15,rep(15,2),13,16,21,16))
#
# Numeric
valores <- createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center",numFmt = "#,##0.00")
addStyle(wb, sheet = 1, style = valores, rows = 8:(nrow(data) + 6),
cols =c(7,8,11,12), gridExpand = TRUE)
#
#Centrar
Centrar <- createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center")
addStyle(wb, sheet = 1, style = Centrar, rows = 7:(nrow(data) + 6),
cols = c(2,3,4,9), gridExpand = TRUE)
#
#Centrar Fechas
Centrar_Fecha <- createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center", numFmt = "dd/mm/yyyy")
addStyle(wb, sheet = 1, style = Centrar_Fecha, rows = 7:(nrow(data) + 6),
cols = c(5,6), gridExpand = TRUE)
#Wrap
wrapp <- createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center",numFmt = "#,##0.00", wrapText = TRUE )
addStyle(wb, sheet = 1, style = wrapp, rows = 7,
cols =c(9), gridExpand = TRUE)
# pie
FH <- nrow(data)+7
writeData(wb, sheet = 1, startRow = FH, startCol = 2,
x = c("TOTAL Fixbi"))
mergeCells(wb, sheet = 1, cols = 2:6, rows = nrow(data) + 7)
POLZ <- createStyle(fontSize = 11, textDecoration = "BOLD" ,fontColour = "black", # fontColour = Color de la letra
valign = "center", border = "TopBottom",halign = "center",
fgFill = "#A6A6A6", borderColour = "#A6A6A6",wrapText = TRUE,
numFmt = "#,##0.00")
addStyle(wb, sheet = 1, POLZ, rows = nrow(data) + 7, cols = 2:12,gridExpand = TRUE)
ador <- createStyle(wrapText = TRUE, valign = "center", halign = "left")
addStyle(wb, sheet = 1, ador, rows = nrow(data) + 20, cols = c(4,8,12),
gridExpand = TRUE)
addStyle(wb, sheet = 1, ador, rows = nrow(data) + 19, cols = c(4,8,12),
gridExpand = TRUE)
addStyle(wb, sheet = 1, ador, rows = nrow(data) + 19, cols = c(3,7,11),
gridExpand = TRUE)
#NOTAS
FHA <- nrow(data)+9
# 1
writeData(wb, sheet = 1, startRow = FHA, startCol = 2,
x = c("NOTAS"))
writeData(wb, sheet = 1, startRow = FHA+1, startCol = 2,
x = c("(1) Valor Pagado: Es el valor del título al momento de la negociación"))
writeData(wb, sheet = 1, startRow = FHA+2, startCol = 2,
x = c("(2) Tasa descuento: Mercado Internacional"))
writeData(wb, sheet = 1, startRow = FHA+3, startCol = 2,
x = c("(3) Precio: 100 - (Tasa de descuento * días por vencer /360)"))
writeData(wb, sheet = 1, startRow = FHA+4, startCol = 2,
x = c("(4) Valor de mercado: Valor Nominal * Precio a la fecha de evaluación)"))
writeData(wb, sheet = 1, startRow = FHA+10, startCol = 3,
x = c("Elaborado:"))
writeData(wb, sheet = 1, startRow = FHA+10, startCol = 4,
x = c("René Díaz F."))
writeData(wb, sheet = 1, startRow = FHA+11, startCol = 4,
x = c("Especialista de Operaciones de Liquidez 2"))
writeData(wb, sheet = 1, startRow = FHA+10, startCol = 11,
x = c("Aprobado por:"))
writeData(wb, sheet = 1, startRow = FHA+10, startCol = 12,
x = c("Claudio Panchez"))
writeData(wb, sheet = 1, startRow = FHA+11, startCol = 12,
x = c("Subgerente de Inversiones (e)"))
freezePane(wb, sheet = 1, firstActiveRow = as.integer(7),
firstActiveCol = 2)
oca <- createStyle(fontSize = 11, # fontColour = Color de la letra
valign = "center", wrapText = TRUE,textDecoration = "BOLD")
addStyle(wb, sheet = 1, oca, rows = 7, cols = 2:12, gridExpand = TRUE)
borderStyle <- createStyle(border = c("top", "bottom", "left", "right"), borderColour = "#A6A6A6",
borderStyle="medium")
addStyle(wb, sheet = 1, style = borderStyle, rows = 7:(nrow(data) + 7),
cols = 2:12, gridExpand = TRUE, stack = TRUE)
# Guardar archivo
user_name <- Sys.getenv("USERNAME")
folder_path <- "D:/Documentos/Estadisticos/R/R_studio/Openxlsx/Rresults"
# Create subfolder for the date
final_folder_path <- file.path(folder_path, Fecha_P_GR)
if (!dir.exists(final_folder_path)) {
dir.create(final_folder_path, recursive = TRUE)
}
# saveWorkbook(wb, file = paste0(folder_path, "/", report_type, " ", Fecha_P_Per, ".xlsx"),
# overwrite = TRUE)
#wb <- createWorkbook() # Replace with your actual workbook object
saveWorkbook(wb, file = file.path(final_folder_path, paste0(report_type, " ",
Fecha_P_Per, ".xlsx")), overwrite = TRUE)
}
# Lista de data.frames y sus parámetros específicos
reports <- list(list(data = FLPR, file_name = "FLPR.xlsx",
second_title = "EVALUACIÓN - P. FDO. LIQ. PRIVADO"),
list(data = FLPS, file_name = "FLPS.xlsx",
second_title = "EVALUACIÓN - P. FDO. LIQ. POPULAR Y SOLIDARIO"),
list(data = SDPR, file_name = "SDPR.xlsx",
second_title = "EVALUACIÓN - FIDEICOMISO DEL SEGURO DE DEPÓSITOS DE LAS ENTIDADES DEL SECTOR FINANCIERO PRIVADO"),
list(data = SDPS, file_name = "SDPS.xlsx",
second_title = "EVALUACIÓN - FIDEICOMISO DEL SEGURO DE DEPÓSITOS DE LAS ENTIDADES DEL SECTOR FINANCIERO POPULAR Y SOLIDARIO"))
# Iterar sobre cada reporte
for (report in reports) {
create_and_format_report(
workbook_name = report$file_name,
data = report$data,
report_type = tools::file_path_sans_ext(report$file_name),
second_title = report$second_title
)
}The first step involves generating anonymous functions with two arguments: data_list_ESC_A and Escenarios_II. The first argument is the name of the list, and the second is the name of the file that I will export.
Now, I define all commands in the routine.
createStyle: Create a new style to apply to worksheet cells
wrapText: If TRUE cell contents will wrap to fit in column.
numFmt = “#,##0.00”: It created a number with two decimals and a separator of decimals.
freezePane: Freeze a worksheet pane.
addFilter: Add excel column filters to a worksheet
ampliar_II <- function(data_list_ESC_A, Escenarios_II) {
wb<- createWorkbook()
for (i in 1: length(data_list_ESC_A)) {
addWorksheet(wb,sheetName = names(data_list_ESC_A)[i])
headerStyle <- createStyle(fontSize = 11,
fontColour = "#FFFFFF",
halign = "center",
valign = "center",
fgFill = "#002060",
border=c("top", "bottom", "left", "right"),
borderColour= "#4F81BD",
textDecoration = "bold",
wrapText = TRUE)
addStyle(wb,sheet = names(data_list_ESC_A)[i],headerStyle,
rows=1,cols = c(1:ncol(as.data.frame(data_list_ESC_A[i]))),
gridExpand = TRUE)
cuerpo <- createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center",numFmt = "#,##0.00")
addStyle(wb, names(data_list_ESC_A)[i], style = cuerpo, rows = 2:(nrow(data_list_ESC_A[[i]])+9),
cols =c(5,8:10), gridExpand = TRUE)
cuerpo_II <- createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center")
addStyle(wb, names(data_list_ESC_A)[i], style = cuerpo_II, rows = 2:(nrow(data_list_ESC_A[[i]])+9),
cols =c(1,3,6,7,11,12,13), gridExpand = TRUE)
writeData(wb, sheet = names(data_list_ESC_A)[i], as.data.frame(data_list_ESC_A[[i]]),
sheet, startRow = 1, startCol = 1, rowNames = F)
freezePane(wb, sheet = names(data_list_ESC_A)[i], firstActiveRow = as.integer(2),
firstActiveCol = 5)
addFilter(wb, sheet = names(data_list_ESC_A)[i], row = 1,
cols = c(1:ncol(as.data.frame(data_list_ESC_A[i]))))
# setColWidths(wb, names(data_list_ESC_A)[i], cols = 1:ncol(data_list_ESC_A[[i]]),
# widths = c(rep(20,3),50,rep(20,3),rep(25,3),rep(15,3)))
}
saveWorkbook(wb, Escenarios_II, overwrite = TRUE)
}
ampliar_II(data_list_ESC_A, paste0("Resultados_Bank/Escenarios_II",".xlsx"))switch evaluates EXPR and accordingly chooses one of the further arguments
# Example data
data_list_ESC_B <- list(
Sheet1 = data.frame(A = 1:10, B = 11:20, C = 21:30),
Sheet2 = data.frame(X = 101:110, Y = 111:120, Z = 121:130)
)
ampliar_III <- function(data_list_ESC_B, Escenarios_III) {
wb <- createWorkbook()
for (i in 1:length(data_list_ESC_B)) {
sheet_name <- names(data_list_ESC_B)[i]
addWorksheet(wb, sheetName = sheet_name)
# Customize header style differently for each list
headerStyle <- switch(sheet_name,
"Sheet1" = createStyle(fontSize = 12, fontColour = "#FFFFFF",
halign = "center",
valign = "center",
fgFill = "#4CAF50",
border = c("top", "bottom", "left", "right"),
borderColour= "#388E3C",
textDecoration = "bold", wrapText = TRUE),
"Sheet2" = createStyle(fontSize = 11, fontColour = "#FFFFFF",
halign = "center",
valign = "center", fgFill = "#2196F3",
border=c("top", "bottom", "left", "right"),
borderColour= "#1976D2", textDecoration = "bold",
wrapText = TRUE),
createStyle(fontSize = 11, fontColour = "#FFFFFF", halign = "center",
valign = "center", fgFill = "#002060",
border=c("top", "bottom", "left", "right"),
borderColour= "#4F81BD", textDecoration = "bold",
wrapText = TRUE))
addStyle(wb, sheet = sheet_name, headerStyle,
rows = 1, cols = c(1:ncol(as.data.frame(data_list_ESC_B[[i]]))),
gridExpand = TRUE)
# Customize body style differently for each list
cuerpo <- switch(sheet_name,
"Sheet1" = createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center", numFmt = "#,##0.00"),
"Sheet2" = createStyle(fontSize = 11, fontColour = "red", halign = "center",
valign = "center", numFmt = "#,##0.00"),
createStyle(fontSize = 11, fontColour = "black", halign = "center",
valign = "center", numFmt = "#,##0.00"))
addStyle(wb, sheet_name, style = cuerpo, rows = 2:(nrow(data_list_ESC_B[[i]]) + 1),
cols = c(1:ncol(as.data.frame(data_list_ESC_B[[i]]))), gridExpand = TRUE)
# Write data to worksheet
writeData(wb, sheet = sheet_name, as.data.frame(data_list_ESC_B[[i]]),
startRow = 1, startCol = 1, rowNames = FALSE)
freezePane(wb, sheet = sheet_name, firstActiveRow = 2, firstActiveCol = 1)
addFilter(wb, sheet = sheet_name, row = 1,
cols = c(1:ncol(as.data.frame(data_list_ESC_B[[i]]))))
# Optional: Set column widths differently for each list
setColWidths(wb, sheet_name, cols = 1:ncol(data_list_ESC_B[[i]]),
widths = switch(sheet_name,
"Sheet1" = c(20, 20, 20),
"Sheet2" = c(15, 15, 15)))
}
saveWorkbook(wb, Escenarios_III, overwrite = TRUE)
}
ampliar_III(data_list_ESC_B, paste0("Resultados_Bank/Escenarios_III",".xlsx"))