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.

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.

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 )

Answer 3

Using XLConnect...

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

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

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