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")
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")
You can define the column width when you write a file with openxlsx:
library(openxlsx)
openxlsx::write.xlsx(df, "C:/myfile.xlsx", sheetName = "Nice Sheet", rowNames = F, colWidths=30)
colWidths = "auto"You can set a specific width for each column of your data set:
for(c in 1:ncol(yourData)){
setColumnWidth(sheetIndex,colIndex=c,colWidth=max(nchar(c(names(yourData[c],yourData[,c])))))
}
*.csvby definition discards all the formatting information. So when you open such a file in Excel, you get Excel's default column widths. If you want to format, you must save as*.xlsor*.xlsx.xlsxmentioned in the accepted answer, you can use theopenxlsxpackage. I prefer this one because it does not depend onrJava.