Define Excel's column width with R

Question

The final product is an Excel CSV spreadsheet which has more than 250 columns. I was wondering if there was a way to determine the column width in Excel from R?

I am using write.csv2, which produces column width in excel all equal to 8,43.

write.csv2(df, na = "", file= "Final.csv")

If possible I am looking for a trick to vary all of them at once or only specific ones. Is running VBA from R my only option?

Thank you for your help!

Answer 1

A small improvement to the accepted answer: Writing a file just to read and modify it again is not very elegant. Moreover, I had the experience that overwriting xls-files with saveWorkbook may lead to "corrupted" files (i.e. Excel will need to repair the file on opening it).

To avoid this, one can proceed as follows:

df <- data.frame(matrix(rnorm(100), nc=10))
library(xlsx)
wb <- createWorkbook(type = "xlsx")
sheet <- createSheet(wb, sheetName = "rnormdata")
addDataFrame(df, sheet, row.names = FALSE)
setColumnWidth(sheet, colIndex = 1:3, colWidth = 20)
autoSizeColumn(sheet, colIndex = 4:ncol(df))
saveWorkbook(wb, "Final.xlsx")

Answer 2

Please check the package xlsx. I am using it for generating excel files and its pretty good. There is a method setColumnWidth which can help you. Check here for more detailed example about xlsx package functionality.

---

So here is a working example using package xlsx.

df <- data.frame(matrix(rnorm(100),nc=10))
library(xlsx)
# must save as an xls or xlsx file...
write.xlsx(df,"Final.xlsx", row.names=FALSE)
# load it back
wb <- loadWorkbook("Final.xlsx")
sheets <- getSheets(wb)
# set widths to 20
setColumnWidth(sheets[[1]], colIndex=1:ncol(df), colWidth=20)
saveWorkbook(wb,"Final.xlsx")
# autosize column widths
autoSizeColumn(sheets[[1]], colIndex=1:ncol(df))
saveWorkbook(wb,"Final.xlsx")