13

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!

Community
  • 1
  • 1
Kvasir EnDevenir
  • 907
  • 1
  • 10
  • 25
  • I'm pretty sure that at least one of the R-to-Excel packages will let you build xlsx spreadsheets that have column-level formatting options. – IRTFM Dec 05 '14 at 18:16
  • 3
    There's a conceptual problem with your question. Saving a file as `*.csv` by 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 `*.xls` or `*.xlsx`. – jlhoward Dec 05 '14 at 20:32
  • Alternatively to the package `xlsx` mentioned in the accepted answer, you can use the `openxlsx` package. I prefer this one because it does not depend on `rJava`. – Stéphane Laurent Oct 13 '17 at 11:57

4 Answers4

21

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")
jlhoward
  • 58,004
  • 7
  • 97
  • 140
Dusan Grubjesic
  • 945
  • 2
  • 9
  • 16
  • 2
    Above code autoSizes single sheet depending on data frame we just added in that sheet, but how we can autosize all sheets from imported excel using above functionalities? – indra_patil Mar 01 '16 at 10:05
6

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")
jarauh
  • 1,836
  • 22
  • 30
1

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)
Peter
  • 2,120
  • 2
  • 19
  • 33
0

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])))))
}
Fabio Natalini
  • 197
  • 2
  • 2