R - Autofit Excel column width

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))

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.

Using XLConnect...

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

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

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 )