Home How to apply formatting to the Total row of a dataframe being exported to Excel

Questions

How to apply formatting to the Total row of a dataframe being exported to Excel

November 5, 2024

I’m exporting a few dataframes to an excel document using the openxlsx package and using the createStyle() and addStyle() functions to apply aesthetics. However, my "total" row is not being formatted like the rest of the cells.

I thought it might be an issue of formatting it as a dataframe, since my formatting calls up the rows of the dataframe and R may not consider the total row as a true row in that sense. I tried reformatting the dataframe as a tibble, but it didn’t resolve the issue.

I’m unsure if there’s an argument I need to adjust in my addStyle() lines, if I need to add another addStyle() line and somehow call up the total row in a relative manner (versus using an index value to call up the specific row number of a specific dataframe), or if I need to reformat my original table as something other than a dataframe or a tibble.

Actual Output:

Code:

# Sample Code #

library("tidyverse")
library("openxlsx")
library("openxlsx2")
library("lubridate")
library("zoo")

## Minimum Reproducible Data ##

servicetypes_2024<- structure(list(`Service Type` = c("a", "b", 
"c", "d", "e", 
"f", "g", "h", 
"i", "j", "k", "l", 
"m", "n", "o", "Total"),`Percentage of Interactions` = c("5.8%", "5.7%", "2.9%", "1.4%", 
"1.2%", "1%", "0.7%", "0.7%", "0.6%", "0.4%", "0.4%", "0.1%", 
"0.1%", "0.1%", "0.3%", "21.5%")), row.names = c(NA, -16L), class = c("tbl_df", 
"tbl", "data.frame"))

## Aesthetics ##

cellStyle <- createStyle(wrapText = TRUE,
                         halign = "right",
                         border= "TopBottomLeftRight",
                         borderColour = getOption("openxlsx.borderColour", "black"),
                         borderStyle = getOption("openxlsx.borderStyle", "thin")) #creates a custom cell style

notesStyle<-createStyle(wrapText = TRUE, 
                        border= "TopBottomLeftRight",
                        borderColour = getOption("openxlsx.borderColour", "black"),
                        borderStyle = getOption("openxlsx.borderStyle", "thin"),
                        halign = "center",
                        textDecoration = "italic")

rowlabelStyle<-createStyle(wrapText = TRUE,
                           border= "TopBottomLeftRight",
                           borderColour = getOption("openxlsx.borderColour", "black"),
                           borderStyle = getOption("openxlsx.borderStyle", "thin"),
                           halign = "left",
                           textDecoration = "italic")

columnlabelStyle<-createStyle(wrapText = TRUE,
                              border= "TopBottomLeftRight",
                              borderColour = getOption("openxlsx.borderColour", "black"),
                              borderStyle = getOption("openxlsx.borderStyle", "thin"),
                              halign = "center",
                              textDecoration = "bold")

## Creating Worksheet ##
output<-createWorkbook() #opens an active workbook for binding

addWorksheet(output,"2024 Service Type Analysis")
writeData(output, "2024 Service Type Analysis", servicetypes_2024, 
          startCol = 1, 
          startRow = 1, 
          colNames = TRUE, 
          rowNames = FALSE, 
          keepNA = TRUE)
setHeaderFooter(output, 
                sheet = "2024 Service Type Analysis", 
                header = c(NA, "2024 Service Type Analysis", NA), 
                footer = c("Data Window: January 1st, 2024 - December 31st, 2024 unless otherwise stated", NA, NA))
setColWidths(output, "2024 Service Type Analysis", cols = 1:ncol(servicetypes_2024), widths="auto")
pageSetup(output, "2024 Service Type Analysis", orientation = "landscape", scale = 100, fitToWidth = TRUE)
addStyle(output, sheet = "2024 Service Type Analysis", cellStyle, rows = 1:nrow(servicetypes_2024), cols = 1:ncol(servicetypes_2024), gridExpand = T)
addStyle(output, sheet = "2024 Service Type Analysis", rowlabelStyle, rows = 1:nrow(servicetypes_2024), cols = 1, gridExpand = T)
addStyle(output, sheet = "2024 Service Type Analysis", columnlabelStyle, rows = 1, cols = 1:ncol(servicetypes_2024), gridExpand = T)

saveWorkbook(output, "Reporting.xlsx", overwrite= TRUE)

>Solution :

You have to add "+ 1" to account for the row containing the column headers, i.e. use e.g. rows = seq(nrow(servicetypes_2024)) + 1 to add the styles starting with the second row.

library("openxlsx")

output <- createWorkbook() # opens an active workbook for binding

addWorksheet(output, "2024 Service Type Analysis")
writeData(output, "2024 Service Type Analysis", servicetypes_2024,
  startCol = 1,
  startRow = 1,
  colNames = TRUE,
  rowNames = FALSE,
  keepNA = TRUE
)
setHeaderFooter(output,
  sheet = "2024 Service Type Analysis",
  header = c(NA, "2024 Service Type Analysis", NA),
  footer = c("Data Window: January 1st, 2024 - December 31st, 2024 unless otherwise stated", NA, NA)
)
setColWidths(output, "2024 Service Type Analysis",
  cols = seq(ncol(servicetypes_2024)), widths = "auto"
)
pageSetup(output, "2024 Service Type Analysis", orientation = "landscape", scale = 100, fitToWidth = TRUE)
addStyle(output,
  sheet = "2024 Service Type Analysis", cellStyle,
  rows = seq(nrow(servicetypes_2024)) + 1,
  cols = seq(ncol(servicetypes_2024)), gridExpand = T
)
addStyle(output,
  sheet = "2024 Service Type Analysis", rowlabelStyle,
  rows = seq(nrow(servicetypes_2024)) + 1,
  cols = 1, gridExpand = T
)
addStyle(output,
  sheet = "2024 Service Type Analysis", columnlabelStyle,
  rows = 1, cols = seq(ncol(servicetypes_2024)), gridExpand = T
)

saveWorkbook(output, "Reporting.xlsx", overwrite = TRUE)