Determine Excel column width with R

Question

The end product is an Excel CSV spreadsheet that has over 250 columns. I was wondering if it is possible to determine column width in Excel from R?

I am using write.csv2 which creates an excel column width of 8.43.

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

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

Thank you for your help!

Answer 1

Please check the xlsx package. I use it to generate excel files, and that is pretty good. There is a setColumnWidth method that can help you. here for a more detailed example about the functionality of the xlsx package.

---

So here is a working example using the xlsx package.

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

Answer 2

A slight improvement in the accepted answer: writing a read-only file and changing it is not very elegant yet. Moreover, I had the feeling that overwriting xls files with saveWorkbook could lead to “damaged” files (ie Excel would have to restore the file when it was opened).

To avoid this, you 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")