R - Autofit Excel column width

Question

How do I autofit the column width using openxlsx?

One of my columns has a date variable (eg. 21-08-2017) and if copied using ctrl+c from Excel, and pasted normally elsewhere, it shows like #######(if column width is increased to show the content in Excel, it pastes normally). I want to integrate that repeatitive task into my code. Here is what I am using right now:

WB <- loadWorkbook(File)
addWorksheet(WB, Sheet)
writeDataTable(WB, Sheet, DF, withFilter=F, bandedRows=F, firstColumn=T)
saveWorkbook(WB, File,  overwrite =TRUE)

I have attached the whole relevant code here, I am also doing conditional formatting based on the table values. Please suggest an way to integrate autofit column width in here.

EDIT: By default, XLSX outputs from R has the default 8.43 columnwidth, I want to either set it to autofit as per cell contents, or set it manually for each column.

To Mod: This is a problem I am trying to solve in R, using openxlsx. Anyway, thanks for the attention.

Stopcock

Answer 1

Ok, I got it after another extensive search in the documentation. It seems very few people actually use this from the dearth of solutions online...

setColWidths(WB, Sheet, cols = 1:ncol(DF), widths = "auto")

However, this still does not give the desired result, the date column is still a bit short and shows ########; while the column headers are not fitting as well (as they are formatted bold).

EDIT:

Finally, chose to add c(7.5, 10, "auto", ...) replacing just "auto", it is not totally dynamic, but solves the issue for now. Hope to see better answers.

Stopcock

The idea under "edit" is a good one. It takes into account that the cell contents can have different font sizes, but with <code>widths = c("auto", "auto", "auto",...)</code> it automatically adjusts the cell width to the string size and length. Good job! – Adna

Answer 2

Given that widths = "auto" did not work as you hoped, a more generalized answer to assign the widths based on the lengthiest value + 2 (to handle emboldening):

width_vec <- apply(DF, 2, function(x) max(nchar(as.character(x)) + 2, na.rm = TRUE)) setColWidths(WB, Sheet, cols = 1:ncol(DF), widths = width_vec)

And to assign the width based on the column headers:

width_vec_header <- nchar(colnames(DF)) + 2
setColWidths(WB, Sheet, cols = 1:ncol(DF), widths = width_vec_header)

And to assign the width based on the lengthiest string per column, whether the header or the cells in the body, use the "parallel" maximum function (like a vectorized maximum function):
width_vec <- apply(DF, 2, function(x) max(nchar(as.character(x)) + 2, na.rm = TRUE))
width_vec_header <- nchar(colnames(DF)) + 2
max_vec_header <- pmax(width_vec, width_vec_header)
setColWidths(WB, Sheet, cols = 1:ncol(DF), widths = max_vec_header )

Rubella

I will check it out tomorrow and let you know. Thanks. – Stopcock

Great input, but does not work if a column is e.g. c(0.001, 1000.000). – Anaesthesia

If "+2" fails to provide enough width, try "+8". That is working well today, maybe because of package changes. – Rubella

The down-most block of code works great! In order to not get any warnings from columns containing only missing values, the function has also take care of this case like charlen_na <- function(x) { if (all(is.na(x))) return(1); max(nchar(as.character(x)) + 2, na.rm=TRUE); } width_vec <- apply(DF, 2, charlen_na); – Oregon

Answer 3

Using XLConnect...

setColumnWidths(WB, Sheet, column = 1:ncol(DF), width = -1)

Reference: https://cran.r-project.org/web/packages/XLConnect/XLConnect.pdf

Scrophulariaceous

Answer 4

I had the same issues as above, but was working with a list of data frames. Using the tidyverse, I modified Rick's answer to account for that. I also didn't want column widths wider than 75. This still didn't fix the date issue described above. I didn't want the timestamp to show with my dates and I found you can set the options for how dates are formatted in Excel. So for that I used options("openxlsx.datetimeFormat" = "mm/dd/yyyy"). More info on formatting here

myList <- list(A = data.frame(ID = c("AAA", "AAA"), 
                          Test = c(1, 1), 
                          Value = 1:2), 
           B = data.frame(ID = c("BBB", "BBB", "BBB"), 
                          Test = c(1, 3, 5), 
                          Value = 1:3),
           C = data.frame(Test = c(1, 3, 5), 
                          Value = 1:3))

data_cols <- myList %>%
  map(~ 1:ncol(.), .depth = 2)

width_vec <- myList %>% 
  map(~ summarise_all(., funs(max(nchar(as.character(.)))), na.rm = TRUE), .depth = 2) %>% 
  map(~ unlist(., use.names = FALSE), .depth = 2) %>% 
  map(~ . + 2, .depth = 2)
width_vec_header <- map(myList, ~ nchar(names(.)) + 2, .depth = 2) 
max_vec <- map2(width_vec, width_vec_header, ~ pmin(75, pmax(.x, .y, 0)), .depth = 2)
pwalk(list(names(myList), data_cols, max_vec), ~ setColWidths(wb, ..1, ..2, widths = ..3))

Orthogenic