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