Export xlsx with column width setting funtion

I came across this post while reading rweekly.org. Our team at RTA also used openxlsx as core engine for exporting data in Excel format from a shiny app. IMHO, this is one of the best R packages for creating .xlsx files, which achieves both performance and friendly user interface.

Regarding the question on SO about autofit Excel column width. I had a slightly different version than Rick Pack’s implementation. I think Rick’s use of pmax() is a bit more elegant than mapply() while our’s function count_char() is nicer than apply().

set_column_widths <- function(wb, sheet, x) {
    count_char <- function(s) sapply(s, function(x) {
        x <- as.character(x)
        x[is.na(x)] <- ""
        max(nchar(x), na.rm = TRUE)
    })
    cols_width <- count_char(x)
    colnames_width <- count_char(names(x))
    cols_width <- mapply(max, cols_width, colnames_width,
                         MoreArgs = list(na.rm = TRUE))
    openxlsx::setColWidths(wb = wb, sheet = sheet,
                           cols = seq_along(cols_width),
                           widths = cols_width)
}