#' export
#' This function helps in converting the tables created through descriptive() to a formatted excel report ()
#' @import openxlsx
#' @param excel_workbook The workbook object created using wb()
#' @param file_name The name of the file to be generated. Make sure this file is not opened in any window.
#' @param index Boolean indicating whether an index page has to be created, default being TRUE
#' @param title Title for the excel file. This title will be added at the top of every sheet
#' @param heading_fill Hex code for fill color for the table headings
#' @param heading_font_color Hex code for font color for the table headings
#' @param font_size Font size of the data, default font size is 10.
#' @param zoom The zoom percentage for the sheets, default being 90.
#' @param logo The address of the image which has to be added as a logo in the report
#' @export export
#' @return A list containing the tables, subtitle and sheetname
export <-
function(excel_workbook,
file_name,
index = T,
title = "Descriptive report",
heading_fill = "#C00000",
heading_font_color = "#A9A9A9",
font_size = 10,
zoom = 90,
logo = NULL) {
if (is.null(file_name)) {
file_name <- title
}
file_name <- gsub("[[:blank:]]", "_", file_name)
if (grepl(".xlsx", file_name) == FALSE) {
file_name <- paste(file_name, ".xlsx", sep = "")
}
isHexColor <- function (hex) {
return(is.character(hex) == TRUE
& nchar(hex) == 7
& !is.nan(base::strtoi(paste(
'0x', sub('#', '', hex), sep = ''
))))
}
########################### Checking the input arguments #########################
if (!isHexColor(heading_fill)) {
message <- paste(heading_fill, "is not a hex color code")
stop(message)
}
if (!isHexColor(heading_font_color)) {
message <- paste(font_size, "is not a hex color code")
stop(message)
}
if (zoom < 10 | zoom > 400) {
stop("Inavlid zoom percentage. Try values between 10-400")
}
# if (!file.exists(logo)) {
# warning("The file path for logo doesn't exist. Hence, logo will not be added")
# }
#defining the formats
hs2 <- openxlsx::createStyle(
fontColour = "#ffffff",
fontSize = font_size,
fgFill = heading_fill,
halign = "center",
valign = "center",
textDecoration = "bold",
border = "TopBottomLeftRight",
borderColour = heading_font_color,
borderStyle = getOption("openxlsx.borderStyle", "thin")
)
titleStyle <- openxlsx::createStyle(
fontSize = 18,
fontColour = "black",
textDecoration = "bold"
)
subtitleStyle <- openxlsx::createStyle(
fontSize = 14,
fontColour = "black",
textDecoration = "bold"
)
bold <- openxlsx::createStyle(textDecoration = "bold")
grey_bg <- openxlsx::createStyle(
fontSize = font_size,
fgFill = "#F2F2F2",
bgFill = NULL,
border = "TopBottomLeftRight",
borderColour = "#A9A9A9",
borderStyle = getOption("openxlsx.borderStyle", "thin")
)
bold_grey <- openxlsx::createStyle(
fontSize = font_size,
fgFill = "#F2F2F2",
bgFill = NULL,
textDecoration = "bold",
border = "TopBottomLeftRight",
borderColour = "#A9A9A9",
borderStyle = getOption("openxlsx.borderStyle", "thin")
)
bold_grey_center <- openxlsx::createStyle(
fontSize = font_size,
fgFill = "#F2F2F2",
bgFill = NULL,
textDecoration = "bold",
border = "TopBottomLeftRight",
borderColour = "#A9A9A9",
halign = "center",
valign = "center",
borderStyle = getOption("openxlsx.borderStyle", "thin")
)
borderStyle <- openxlsx::createStyle(
halign = "CENTER",
border = "Top",
borderColour = "red",
borderStyle = "thick"
)
# % style
percentColStyle <- openxlsx::createStyle(
fontName = "Calibri",
fontSize = font_size,
fontColour = "black",
numFmt = "#,##0.00%",
border = "TopBottomLeftRight",
borderColour = "#A9A9A9",
borderStyle =
getOption("openxlsx.borderStyle", "thin"),
bgFill = NULL,
fgFill = NULL,
halign = NULL,
valign = NULL,
textDecoration = NULL,
wrapText = FALSE,
textRotation = NULL,
indent = NULL
)
# Add N style
NColStyle <- openxlsx::createStyle(
fontName = "Calibri",
fontSize = font_size,
fontColour = "black",
numFmt = "#,##",
border = "TopBottomLeftRight",
borderColour = "#A9A9A9",
borderStyle =
getOption("openxlsx.borderStyle", "thin"),
bgFill = NULL,
fgFill = NULL,
halign = NULL,
valign = NULL,
textDecoration = NULL,
wrapText = FALSE,
textRotation = NULL,
indent = NULL
)
# Add N.00 style
NDecColStyle <- openxlsx::createStyle(
fontName = "Calibri",
fontSize = font_size,
fontColour = "black",
numFmt = "#,##0.00",
border = "TopBottomLeftRight",
borderColour = "#A9A9A9",
borderStyle =
getOption("openxlsx.borderStyle", "thin"),
bgFill = NULL,
fgFill = NULL,
halign = NULL,
valign = NULL,
textDecoration = NULL,
wrapText = FALSE,
textRotation = NULL,
indent = NULL
)
# % style
percentBoldColStyle <- openxlsx::createStyle(
fontName = "Calibri",
fontSize = font_size,
fontColour = "black",
numFmt = "#,##0.00%",
border = "TopBottomLeftRight",
borderColour = "#A9A9A9",
borderStyle =
getOption("openxlsx.borderStyle", "thin"),
bgFill = NULL,
fgFill = NULL,
halign = NULL,
valign = NULL,
textDecoration = "bold",
wrapText = FALSE,
textRotation = NULL,
indent = NULL
)
# Add N style
NBoldColStyle <- openxlsx::createStyle(
fontName = "Calibri",
fontSize = font_size,
fontColour = "black",
numFmt = "#,##",
border = "TopBottomLeftRight",
borderColour = "#A9A9A9",
borderStyle =
getOption("openxlsx.borderStyle", "thin"),
bgFill = NULL,
fgFill = NULL,
halign = NULL,
valign = NULL,
textDecoration = "bold",
wrapText = FALSE,
textRotation = NULL,
indent = NULL
)
# Add N.00 style
NDecBoldColStyle <- openxlsx::createStyle(
fontName = "Calibri",
fontSize = font_size,
fontColour = "black",
numFmt = "#,##0.00",
border = "TopBottomLeftRight",
borderColour = "#A9A9A9",
borderStyle =
getOption("openxlsx.borderStyle", "thin"),
bgFill = NULL,
fgFill = NULL,
halign = NULL,
valign = NULL,
textDecoration = "bold",
wrapText = FALSE,
textRotation = NULL,
indent = NULL
)
#creating the workbook
outwb <- createWorkbook()
if (index) {
#get the name of all the worksheets
sheet_names <- c()
subtitle <- "Index page"
for (sheet in excel_workbook) {
sheet_names <- c(sheet_names, unlist(sheet[3]))
}
index_table <-
as.data.frame(rbind(cbind(Sheet = sheet_names, Link = sheet_names)))
#adding the worksheet
openxlsx::addWorksheet(outwb, "Index", zoom = zoom)
openxlsx::showGridLines(outwb, "Index", showGridLines = FALSE)
#writing the title and the subtitles
if (!is.null(logo)) {
openxlsx::insertImage(
outwb,
"Index",
logo,
startRow = 1,
startCol = 1,
width = 0.7,
height = 0.8,
units = "in"
)
}
openxlsx::writeData(
outwb,
"Index",
title,
startCol = 2,
startRow = 1,
borders = "none"
)
openxlsx::addStyle(
outwb,
sheet = "Index",
titleStyle,
rows = 1,
cols = 2,
gridExpand = TRUE
)
openxlsx::addStyle(
outwb,
sheet = "Index",
borderStyle,
rows = 2,
cols = 2:16384,
gridExpand = TRUE
)
openxlsx::writeData(
outwb,
"Index",
subtitle,
startCol = 2,
headerStyle = subtitleStyle,
startRow = 3,
borders = "none"
)
openxlsx::addStyle(
outwb,
sheet = "Index",
subtitleStyle,
rows = 3,
cols = 2,
gridExpand = TRUE
)
#writing the data to the worksheet
openxlsx::writeData(
outwb,
"Index",
index_table,
startCol = 2,
startRow = 7,
borders = "all",
borderColour = "#bfbfbf",
headerStyle = hs2,
borderStyle = "thin"
)
for (i in 1:nrow(index_table)) {
x <- index_table[i, 1]
writeFormula(
outwb,
"Index",
startRow = 7 + i,
startCol = 3,
x = makeHyperlinkString(
sheet = x,
row = 1,
col = 1,
text = x
)
)
}
width_vec <- apply(index_table, 2,
function(x)
max(nchar(as.character(x)) + 1,
na.rm = TRUE))
width_vec_header <- nchar(colnames(index_table)) + 1
max_vec_header_param <- pmax(width_vec, width_vec_header)
openxlsx::setColWidths(outwb,
"Index",
cols = 2:(ncol(index_table) + 1),
widths = max_vec_header_param)
}
#going through each sheet
for (sheet in excel_workbook) {
#subtitle
subtitle <- unlist(sheet[2])
#sheet name
sheet_name <- unlist(sheet[3])
#sheet name
stack <- unlist(sheet[4])
#tables
tables <- sheet[[1]]
if (!(stack == "sideways" | stack == "below")) {
message <-
paste(stack,
"is not a valid input for stack. Try 'sideways' or 'below'")
stop(message)
}
#adding the worksheet
openxlsx::addWorksheet(outwb, sheet_name, zoom = zoom)
openxlsx::showGridLines(outwb, sheet_name, showGridLines = FALSE)
#writing the title and the subtitles
if (!is.null(logo)) {
openxlsx::insertImage(
outwb,
sheet_name,
logo,
startRow = 1,
startCol = 1,
width = 0.7,
height = 0.8,
units = "in"
)
}
openxlsx::writeData(
outwb,
sheet_name,
title,
startCol = 2,
startRow = 1,
borders = "none"
)
openxlsx::addStyle(
outwb,
sheet = sheet_name,
titleStyle,
rows = 1,
cols = 2,
gridExpand = TRUE
)
openxlsx::addStyle(
outwb,
sheet = sheet_name,
borderStyle,
rows = 2,
cols = 2:16384,
gridExpand = TRUE
)
openxlsx::writeData(
outwb,
sheet_name,
subtitle,
startCol = 2,
headerStyle = subtitleStyle,
startRow = 3,
borders = "none"
)
openxlsx::addStyle(
outwb,
sheet = sheet_name,
subtitleStyle,
rows = 3,
cols = 2,
gridExpand = TRUE
)
if (index) {
openxlsx::writeFormula(
outwb,
sheet_name,
startRow = 4,
startCol = 1,
x = makeHyperlinkString(
sheet = "Index",
row = 1,
col = 1,
text = "<-Index"
)
)
openxlsx::addStyle(
outwb,
sheet_name,
rows = 4,
style = createStyle(textDecoration = NULL, fontColour = "#0000FF"),
cols = 1
)
}
if (length(tables) != 4) {
reference_row <- 5
reference_col <- 2
for (table in tables) {
#sheet type
sheet_type <- as.character(unlist(table$type))
#heading name
table_heading <- table$heading
#table
table <- as.data.frame(table$table)
#writing the data to the worksheet
openxlsx::writeData(
outwb,
sheet_name,
table,
startCol = reference_col,
startRow = reference_row - 1 + nrow(table_heading),
borders = "all",
borderColour = "#bfbfbf",
headerStyle = hs2,
borderStyle = "thin"
)
#writing the heading
openxlsx::writeData(
outwb,
sheet_name,
table_heading,
startCol = reference_col,
startRow = reference_row - 1,
borders = "all",
borderColour = "#bfbfbf",
borderStyle = "thin"
)
if (sheet_type == "categorical" |
sheet_type == "binary") {
#writing the logic to merge
tfdf <-
as.data.frame(matrix(
rep(F, nrow(table_heading) * ncol(table_heading)),
nrow = nrow(table_heading),
ncol = ncol(table_heading)
))
j <- 1
while (j <= ncol(table_heading)) {
merge_column_start <- j
merge_column_end <- j
i <- 1
while (i < nrow(table_heading)) {
if (tfdf[i, j] == T) {
i <- i + 1
next()
}
temp <- table_heading[i, j]
merge_row_start <- i
merge_row_end <- i
merge_column_end <- j
for (k in ((i + 1):(nrow(table_heading) - 1))) {
if (table_heading[k, j] == temp) {
merge_row_end <- k
next()
} else{
break()
}
}
while ((all(unique(table_heading[merge_row_start:merge_row_end, merge_column_end +
1]) ==
rep(temp, length(
unique(table_heading[merge_row_start:merge_row_end, merge_column_end + 1])
)))) &
merge_column_end < ncol(table_heading)) {
merge_column_end <- merge_column_end + 1
}
if (merge_column_end == 1) {
openxlsx::mergeCells(
outwb,
sheet_name,
cols = (
reference_col - 1 + c(merge_column_start:merge_column_end)
),
rows = (reference_row - 1 + c(
merge_row_start:(merge_row_end +
1)
))
)
} else{
openxlsx::mergeCells(
outwb,
sheet_name,
cols = reference_col - 1 + c(merge_column_start:merge_column_end),
rows = reference_row - 1 + c(merge_row_start:merge_row_end)
)
}
tfdf[merge_row_start:merge_row_end, merge_column_start:merge_column_end] <-
T
i <- i + 1
}
j <- j + 1
}
# Add N style to table
for (i in (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) + reference_row -
1)) {
openxlsx::addStyle(
outwb,
sheet_name,
NColStyle,
rows = i,
cols = c(reference_col - 1 + which(grepl(
"All", colnames(table)
))),
gridExpand = TRUE,
stack = FALSE
)
}
# Add % style to table
for (i in (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
reference_row -
1)) {
openxlsx::addStyle(
outwb,
sheet_name,
percentColStyle,
rows = i,
cols = c(reference_col - 1 + which(grepl(
"Percent", colnames(table)
))),
gridExpand = TRUE,
stack = FALSE
)
}
#Adding format style to the table header
openxlsx::addStyle(
outwb,
sheet = sheet_name,
hs2,
rows = reference_row:(nrow(table_heading) + reference_row -
1),
cols = (reference_col:(
ncol(table_heading) + reference_col - 1
)),
gridExpand = TRUE
)
deleteData(
outwb,
sheet = sheet_name,
cols = (reference_col:(
reference_col - 1 + ncol(table_heading)
)),
rows = reference_row - 1,
gridExpand = TRUE
)
if (sum(rowSums(is.na(table))) == 0) {
grey_style <- bold_grey
} else{
grey_style <- grey_bg
}
#adding grey to the labels
addStyle(
outwb,
sheet = sheet_name,
grey_style,
rows = (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
reference_row -
1),
cols = reference_col,
gridExpand = TRUE
)
#adding bold grey to the variables
addStyle(
outwb,
sheet = sheet_name,
bold_grey,
rows = c((
reference_row - 1 + nrow(table_heading) + (which(is.na(table[, 2])))
)),
cols = (reference_col:(ncol(table) + reference_col - 1)),
gridExpand = TRUE
)
# Add N style to table
openxlsx::addStyle(
outwb,
sheet_name,
NBoldColStyle,
rows = (reference_row + nrow(table_heading)),
cols = c(reference_col - 1 + which(grepl(
"All", colnames(table)
))),
gridExpand = TRUE,
stack = FALSE
)
# Add % style to table
openxlsx::addStyle(
outwb,
sheet_name,
percentBoldColStyle,
rows = reference_row + nrow(table_heading),
cols = c(reference_col - 1 + which(grepl(
"Percent", colnames(table)
))),
gridExpand = TRUE,
stack = FALSE
)
openxlsx::addStyle(
outwb,
sheet_name,
bold_grey,
rows = reference_row + nrow(table_heading) ,
cols = reference_col,
gridExpand = TRUE,
stack = FALSE
)
} else{
#writing the logic to merge
tfdf <-
as.data.frame(matrix(
rep(F, nrow(table_heading) * ncol(table_heading)),
nrow = nrow(table_heading),
ncol = ncol(table_heading)
))
j <- 1
while (j <= ncol(table_heading)) {
merge_column_start <- j
merge_column_end <- j
i <- 1
while (i < nrow(table_heading)) {
if (tfdf[i, j] == T) {
i <- i + 1
next()
}
temp <- table_heading[i, j]
merge_row_start <- i
merge_row_end <- i
merge_column_end <- j
for (k in ((i + 1):(nrow(table_heading)))) {
if (table_heading[k, j] == temp) {
merge_row_end <- k
next()
} else{
break()
}
}
while ((all(unique(table_heading[merge_row_start:merge_row_end, merge_column_end + 1]) ==
rep(temp, length(
unique(table_heading[merge_row_start:merge_row_end, merge_column_end + 1])
)))) &
merge_column_end < ncol(table_heading)) {
merge_column_end <- merge_column_end + 1
}
openxlsx::mergeCells(
outwb,
sheet_name,
cols = (reference_col - 1 + c(merge_column_start:merge_column_end)),
rows = (reference_row - 1 + c(merge_row_start:merge_row_end))
)
tfdf[merge_row_start:merge_row_end, merge_column_start:merge_column_end] <-
T
i <- i + 1
}
j <- j + 1
}
#logic to merge the rows
final <- c()
for (i in 2:nrow(table)) {
after <- table[i, 1]
if (!is.na(after)) {
final <- c(final, (i - 1))
}
}
final <- c(final, nrow(table))
final <- final + reference_row + nrow(table_heading)
for (i in (1:(length(final) - 1))) {
openxlsx::mergeCells(outwb,
sheet_name,
rows = c(final[i]:(final[i + 1] - 1)),
cols = reference_col)
}
for (i in (reference_row + 1 + nrow(table_heading)):(reference_row + nrow(table_heading) + nrow(table))) {
}
#Adding format style to the table header
openxlsx::addStyle(
outwb,
sheet = sheet_name,
hs2,
rows = reference_row:(nrow(table_heading) + 7),
cols = (reference_col:(
reference_col - 1 + ncol(table_heading)
)),
gridExpand = TRUE
)
deleteData(
outwb,
sheet = sheet_name,
cols = (reference_col:(
reference_col - 1 + ncol(table_heading)
)),
rows = reference_row - 1,
gridExpand = TRUE
)
#merge the All row
openxlsx::mergeCells(
outwb,
sheet_name,
rows = reference_row + nrow(table_heading),
cols = ((reference_col):(reference_col + 1))
)
# Add N style to table
for (i in (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
reference_row -
1)) {
openxlsx::addStyle(
outwb,
sheet_name,
NDecColStyle,
rows = i,
cols = ((reference_col + 1):(
reference_col - 1 + ncol(table_heading)
)),
gridExpand = TRUE,
stack = FALSE
)
}
#adding grey to the labels
addStyle(
outwb,
sheet = sheet_name,
grey_bg,
rows = (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
reference_row -
1),
cols = reference_col + 1,
gridExpand = TRUE
)
#adding bold grey to the variables
addStyle(
outwb,
sheet = sheet_name,
bold_grey_center,
rows = ((reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) + reference_row -
1)
),
cols = reference_col,
gridExpand = TRUE
)
openxlsx::addStyle(
outwb,
sheet_name,
NColStyle,
rows = reference_row - 1 + nrow(table_heading) + which(grepl("All", table[, 2])),
cols = ((reference_col + 1):(
reference_col - 1 + ncol(table_heading)
)),
gridExpand = TRUE,
stack = FALSE
)
openxlsx::addStyle(
outwb,
sheet_name,
NBoldColStyle,
rows = (reference_row + nrow(table_heading)),
cols = ((reference_col + 1):(
reference_col - 1 + ncol(table_heading)
)),
gridExpand = TRUE,
stack = FALSE
)
}
#setting the width of the column
width_vec <- apply(table, 2,
function(x)
max(nchar(as.character(x)) + 2,
na.rm = TRUE))
width_vec_header <- nchar(colnames(table_heading)) + 2
max_vec_header <- pmax(width_vec, width_vec_header)
openxlsx::setColWidths(outwb,
sheet_name,
cols = (reference_col:(ncol(table) + reference_col -
1)),
widths = max_vec_header)
if (stack == "below") {
reference_row <- reference_row + nrow(table_heading) + nrow(table) + 4
}
if (stack == "sideways") {
reference_col <- reference_col + ncol(table) + 2
}
}
} else{
reference_row <- 1
reference_col <- 0
#sheet type
sheet_type <- as.character(unlist(tables$type))
#heading name
table_heading <- tables$heading
#table
table <- as.data.frame(tables$table)
reference_row <- reference_row + 4
reference_col <- reference_col + 2
#writing the data to the worksheet
openxlsx::writeData(
outwb,
sheet_name,
table,
startCol = reference_col,
startRow = reference_row - 1 + nrow(table_heading),
borders = "all",
borderColour = "#bfbfbf",
headerStyle = hs2,
borderStyle = "thin"
)
#writing the heading
openxlsx::writeData(
outwb,
sheet_name,
table_heading,
startCol = reference_col,
startRow = reference_row - 1,
borders = "all",
borderColour = "#bfbfbf",
borderStyle = "thin"
)
if (sheet_type == "categorical" |
sheet_type == "binary") {
#writing the logic to merge
tfdf <-
as.data.frame(matrix(
rep(F, nrow(table_heading) * ncol(table_heading)),
nrow = nrow(table_heading),
ncol = ncol(table_heading)
))
j <- 1
while (j <= ncol(table_heading)) {
merge_column_start <- j
merge_column_end <- j
i <- 1
while (i < nrow(table_heading)) {
if (tfdf[i, j] == T) {
i <- i + 1
next()
}
temp <- table_heading[i, j]
merge_row_start <- i
merge_row_end <- i
merge_column_end <- j
for (k in ((i + 1):(nrow(table_heading) - 1))) {
if (table_heading[k, j] == temp) {
merge_row_end <- k
next()
} else{
break()
}
}
while ((all(unique(table_heading[merge_row_start:merge_row_end, merge_column_end +
1]) ==
rep(temp, length(
unique(table_heading[merge_row_start:merge_row_end, merge_column_end + 1])
)))) &
merge_column_end < ncol(table_heading)) {
merge_column_end <- merge_column_end + 1
}
if (merge_column_end == 1) {
openxlsx::mergeCells(
outwb,
sheet_name,
cols = reference_col - 1 + c(merge_column_start:merge_column_end),
rows = reference_row - 1 + c(merge_row_start:(merge_row_end +
1))
)
} else{
openxlsx::mergeCells(
outwb,
sheet_name,
cols = reference_col - 1 + c(merge_column_start:merge_column_end),
rows = reference_row - 1 + c(merge_row_start:merge_row_end)
)
}
tfdf[merge_row_start:merge_row_end, merge_column_start:merge_column_end] <-
T
i <- i + 1
}
j <- j + 1
}
# Add N style to table
for (i in (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) + reference_row -
1)) {
openxlsx::addStyle(
outwb,
sheet_name,
NColStyle,
rows = i,
cols = c(reference_col - 1 + which(grepl(
"All", colnames(table)
))),
gridExpand = TRUE,
stack = FALSE
)
}
# Add % style to table
for (i in (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
reference_row -
1)) {
openxlsx::addStyle(
outwb,
sheet_name,
percentColStyle,
rows = i,
cols = c(reference_col - 1 + which(grepl(
"Percent", colnames(table)
))),
gridExpand = TRUE,
stack = FALSE
)
}
#Adding format style to the table header
openxlsx::addStyle(
outwb,
sheet = sheet_name,
hs2,
rows = reference_row:(nrow(table_heading) + reference_row -
1),
cols = reference_col:(ncol(table_heading) + 1),
gridExpand = TRUE
)
deleteData(
outwb,
sheet = sheet_name,
cols = reference_col:(1 + ncol(table_heading)),
rows = reference_row - 1,
gridExpand = TRUE
)
if (sum(rowSums(is.na(table))) == 0) {
grey_style <- bold_grey
} else{
grey_style <- grey_bg
}
#adding grey to the labels
addStyle(
outwb,
sheet = sheet_name,
grey_style,
rows = (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
reference_row -
1),
cols = reference_col,
gridExpand = TRUE
)
#adding bold grey to the variables
addStyle(
outwb,
sheet = sheet_name,
bold_grey,
rows = c((
reference_row - 1 + nrow(table_heading) + (which(is.na(table[, 2])))
)),
cols = reference_col:(ncol(table) + 1),
gridExpand = TRUE
)
# Add N style to table
openxlsx::addStyle(
outwb,
sheet_name,
NBoldColStyle,
rows = (reference_row + nrow(table_heading)),
cols = c(reference_col - 1 + which(grepl(
"All", colnames(table)
))),
gridExpand = TRUE,
stack = FALSE
)
# Add % style to table
openxlsx::addStyle(
outwb,
sheet_name,
percentBoldColStyle,
rows = reference_row + nrow(table_heading),
cols = c(reference_col - 1 + which(grepl(
"Percent", colnames(table)
))),
gridExpand = TRUE,
stack = FALSE
)
openxlsx::addStyle(
outwb,
sheet_name,
bold_grey,
rows = reference_row + nrow(table_heading) ,
cols = reference_col,
gridExpand = TRUE,
stack = FALSE
)
} else{
#writing the logic to merge
tfdf <-
as.data.frame(matrix(
rep(F, nrow(table_heading) * ncol(table_heading)),
nrow = nrow(table_heading),
ncol = ncol(table_heading)
))
j <- 1
while (j <= ncol(table_heading)) {
merge_column_start <- j
merge_column_end <- j
i <- 1
while (i < nrow(table_heading)) {
if (tfdf[i, j] == T) {
i <- i + 1
next()
}
temp <- table_heading[i, j]
merge_row_start <- i
merge_row_end <- i
merge_column_end <- j
for (k in ((i + 1):(nrow(table_heading)))) {
if (table_heading[k, j] == temp) {
merge_row_end <- k
next()
} else{
break()
}
}
while ((all(unique(table_heading[merge_row_start:merge_row_end, merge_column_end + 1]) ==
rep(temp, length(
unique(table_heading[merge_row_start:merge_row_end, merge_column_end + 1])
)))) &
merge_column_end < ncol(table_heading)) {
merge_column_end <- merge_column_end + 1
}
openxlsx::mergeCells(
outwb,
sheet_name,
cols = reference_col - 1 + c(merge_column_start:merge_column_end),
rows = reference_row + 1 + c(merge_row_start:merge_row_end)
)
tfdf[merge_row_start:merge_row_end, merge_column_start:merge_column_end] <-
T
i <- i + 1
}
j <- j + 1
}
#logic to merge the rows
final <- c()
for (i in 2:nrow(table)) {
after <- table[i, 1]
if (!is.na(after)) {
final <- c(final, (i - 1))
}
}
final <- c(final, nrow(table))
final <- final + reference_row + nrow(table_heading)
for (i in 1:(length(final) - 1)) {
openxlsx::mergeCells(outwb,
sheet_name,
rows = c(final[i]:(final[i + 1] - 1)),
cols = reference_col)
}
for (i in (reference_row + 1 + nrow(table_heading)):(reference_row + nrow(table_heading) + nrow(table))) {
}
#Adding format style to the table header
openxlsx::addStyle(
outwb,
sheet = sheet_name,
hs2,
rows = reference_row:(nrow(table_heading) + 7),
cols = reference_col:(ncol(table_heading) + 1),
gridExpand = TRUE
)
deleteData(
outwb,
sheet = sheet_name,
cols = reference_col:(1 + ncol(table_heading)),
rows = reference_row - 1,
gridExpand = TRUE
)
#merge the All row
openxlsx::mergeCells(
outwb,
sheet_name,
rows = reference_row + nrow(table_heading),
cols = (reference_col):(reference_col + 1)
)
# Add N style to table
for (i in (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
reference_row -
1)) {
openxlsx::addStyle(
outwb,
sheet_name,
NDecColStyle,
rows = i,
cols = (reference_col + 1):(1 + ncol(table_heading)),
gridExpand = TRUE,
stack = FALSE
)
}
#adding grey to the labels
addStyle(
outwb,
sheet = sheet_name,
grey_bg,
rows = (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
reference_row -
1),
cols = reference_col + 1,
gridExpand = TRUE
)
#adding bold grey to the variables
addStyle(
outwb,
sheet = sheet_name,
bold_grey_center,
rows = ((reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) + reference_row -
1)
),
cols = reference_col,
gridExpand = TRUE
)
openxlsx::addStyle(
outwb,
sheet_name,
NColStyle,
rows = reference_row - 1 + nrow(table_heading) + which(grepl("All", table[, 2])),
cols = (reference_col = 1):(1 + ncol(table_heading)),
gridExpand = TRUE,
stack = FALSE
)
openxlsx::addStyle(
outwb,
sheet_name,
NBoldColStyle,
rows = reference_row + nrow(table_heading),
cols = ((reference_col + 1):(
reference_col - 1 + ncol(table_heading)
)),
gridExpand = TRUE,
stack = FALSE
)
}
#setting the width of the column
width_vec <- apply(table, 2,
function(x)
max(nchar(as.character(x)) + 2,
na.rm = TRUE))
width_vec_header <- nchar(colnames(table_heading)) + 2
max_vec_header <- pmax(width_vec, width_vec_header)
openxlsx::setColWidths(outwb,
sheet_name,
cols = reference_col:(ncol(table) + 1),
widths = max_vec_header)
}
}
#saving the workbook
openxlsx::saveWorkbook(outwb, file = file_name, overwrite = TRUE)
}
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.