Help with Excel Formatting

General
#1

Hi,

I have been saving Excel files using openxlsx package and I really like it. However, saved files are in unclean version (not sure the right word), but it looks like this:
image

This is how I save my file currently:

writeDataTable(wb, "ABC", x = df, startCol = 1, startRow = 1, colNames = TRUE, tableStyle = "None", tableName = "abc", withFilter = FALSE)

But every time, I have to open each excel file manually and set the columns so that each column is seen easily. With increasing number of files, its becoming a time consuming task. Is there a way to save file directly in clean version or in other words, I can see all columns clearly and do not need to do anything.

Thank you!

#2

Hi:
You can use this function to set column width after your WriteDataTable call:

setColWidths(wb, sheet, cols = 1:ncol(dataframe), widths = "auto")

See other examples for this function in package help.

#3

Thanks @yan_lyesin! script doesn't give any errors, but when I try to open the xls file, it says. "We found a problem. Would you like to recover as much as we can" and when I say Yes, then the file is fine, but still it has the same messy format as before. This is what I did based on your suggestion in case I am missing something:

writeDataTable(wb, "ABC", x = df, startCol = 1, startRow = 1, colNames = TRUE, tableStyle = "None", tableName = "abc", withFilter = FALSE)
setColWidths(wb, "ABC", cols = 1:ncol(df), widths = "auto")
#4

2 possible things you can try:

  1. specify columns explicitly: cols = 1:3
  2. use vector of widths: widths = c(25, 20, 25)

Are you writing formulas in this workbook as well? Is there anything in your data that might be causing auto width to fail due to big size (more than 250) or something else in your data?
Also - how wide is your data?

#5

Thank you so much @yan_lyesin! Specifying columns worked!

specify columns explicitly: cols = 1:3

Thank you!

1 Like