Openxlsx column width setting using pmax() and apply() when “auto” truncates (#rstats)

I use the R package openxlsx, by Sydney’s Alexander Walker,  a few times per week. When professional stakeholders love Microsoft Excel, we give them the .XLSX workbooks that they seek.

Using setColWidths (..., widths = "auto")usually works to widen columns based on the values present in the workbook. However, I experienced earlier today a failure of this auto-widening feature. I unfortunately cannot provide the confidential data and failed to generate a reproducible example, however, TLDR: The Figure 5 code in the gist below shows how to use the pmax function to identify the maximum of the header length and body text length, per column.

This is an expounding on my StackOverflow answer here, to which I invite you to respond.

Note: The R code is assembled together as a Gist at the bottom of this post.

Openxlsx will often save an Excel workbook without any width adjustments needed.

Here is the starwars data found in Hadley Wickham’s tidyverse collection of packages.

Figure 1

Looks okay, but columns like E (skin_color) show a truncated header.

We can use widths = “auto” in the setColWidths function to auto-widen the column based on the data. I know of no negatives to doing so and therefore always include this code.

Figure 2

We see that widths = “auto” in the setColWidths function serves us well. All of the column headers can be fully-seen and the same is true for the values in the body. The truncation at the far-right is due to my imperfect screenshotting.

I was hoping to reproduce the problem I experienced earlier by trying the storms dataframe. I am including Figure 3 to further emphasize the power of the openxlsx package (using widths = “auto”) and possibly invite exploration of this “subset of the NOAA Atlantic hurricane database best track data“, which also comes with the tidyverse.

Figure 3

Two-row headers might be valuable for those accustomed to Excel pivot tables. This shows a use of mergecells to create a header for the data frame’s column headers. No problem seen with widths = “auto”.

Figure 4

However, I solved my problem with confidential data with the code associated with figure 5, and it looks a bit better than Figure 4. The height column now has no extraneous space, for example. Depending on your font size and features, you might adjust the width_adjuster object (e.g., increase the number for more space).

Figure 5

R code

	library(tidyverse)
	library(openxlsx)
	# Figure 1
	bldStyle <- createStyle(fontSize = 14, fontColour = "black", textDecoration = c("BOLD"))
	wb <- createWorkbook()
	addWorksheet(wb, "starwars")
	writeData(wb, 1, starwars, colNames=TRUE, headerStyle = bldStyle)
	saveWorkbook(wb, "Starwars_default.xlsx", overwrite = TRUE)
	# Figure 2
	wb <- createWorkbook()
	addWorksheet(wb, "starwars")
	writeData(wb, 1, starwars, colNames=TRUE, headerStyle = bldStyle)
	setColWidths(wb, sheet = 1, cols = 1:ncol(starwars), widths = "auto")
	saveWorkbook(wb, "Starwars_autowide.xlsx", overwrite = TRUE)
	# Figure 3
	wb <- createWorkbook()
	addWorksheet(wb, "storms")
	writeData(wb, 1, storms, colNames=TRUE, headerStyle = bldStyle)
	setColWidths(wb, sheet = 1, cols = 1:ncol(storms), widths = "auto")
	saveWorkbook(wb, "storms_autowide.xlsx", overwrite = TRUE)
	# Figure 4
	wb <- createWorkbook()
	addWorksheet(wb, "starwars")
	hs1 <- createStyle(fgFill = "#f9f9f9", halign = "CENTER", textDecoration = "italic",
	border = "TopBottomLeftRight", fontSize = 13)
	row1 <- t(c("", "UseforBMI", "", "colors", "", "", rep("", 8)))
	addStyle(wb, sheet = 1, hs1, rows = 1, cols = 1:ncol(row1))
	writeData(wb, 1, x = row1, colNames=FALSE, headerStyle = bldStyle)
	writeData(wb, 1, x = starwars, startRow = 2, colNames=TRUE, headerStyle = bldStyle)
	mergeCells(wb, 1, cols = 2:3, rows = 1)
	mergeCells(wb, 1, cols = 4:6, rows = 1)
	setColWidths(wb, sheet = 1, cols = 1:ncol(starwars), widths = "auto")
	saveWorkbook(wb, "starwars_autowide_mergedcols.xlsx", overwrite = TRUE)
	# Figure 5
	width_adjuster <- 1.5
	wb <- createWorkbook()
	addWorksheet(wb, "starwars")
	hs1 <- createStyle(fgFill = "#f9f9f9", halign = "CENTER", textDecoration = "italic",
	border = "TopBottomLeftRight", fontSize = 13)
	row1 <- t(c("", "UseforBMI", "", "colors", "", "", rep("", 8)))
	addStyle(wb, sheet = 1, hs1, rows = 1, cols = 1:ncol(row1))
	writeData(wb, 1, x = row1, colNames=FALSE, headerStyle = bldStyle)
	writeData(wb, 1, x = starwars, startRow = 2, colNames=TRUE, headerStyle = bldStyle)
	mergeCells(wb, 1, cols = 2:3, rows = 1)
	mergeCells(wb, 1, cols = 4:6, rows = 1)
	# column widths based on values in the dataframe
	width_vec <- apply(starwars, 2, function(x) max(nchar(as.character(x)) + width_adjuster, na.rm = TRUE))
	# column widths based on the column header widths
	width_vec_header <- nchar(colnames(starwars)) + width_adjuster
	# now use parallel max (like vectorized max) to capture the lengthiest value per column
	width_vec_max <- pmax(width_vec, width_vec_header)
	setColWidths(wb, sheet = 1, cols = 1:ncol(row1), widths = width_vec_max)
	saveWorkbook(wb, "starwars_manualwide_mergedcols.xlsx", overwrite = TRUE)

view raw

openxlsx_wide.R

hosted with ❤ by GitHub