R - Autofit Excel column width
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
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.