openxlsx writeData() and saveWorkbook() create corrupted Excel file

Question

When I'm using openxlsx to save a dataframe, the resultant file ends up being damaged.

library(openxlsx)

# Making dummy dataframe
Name <- c("Jon", "Bill", "Maria", "Ben", "Tina")
Age <- c(23, 41, 32, 58, 26)

df <- data.frame(Name, Age)

# Creating output file 
output_path <- "check_corruption.xlsx"
wb <- createWorkbook(output_path) 
addWorksheet(wb, "Sheet1")

# Write unformatted data
writeData(wb, sheet=1, df)

# Save fully formatted output
saveWorkbook(wb, output_path, overwrite=TRUE)

When I try to open check_corruption.xlsx I get the error

"We found a problem with some content in 'check_corruption.xlsx'. Do you want us to try to recover as much as we can?".

If I repair the file then the output seems fine but I don't understand what's causing the problem in the first place.

Answer 1

It turns out this was due to an "&" symbol in the output path of the file, which was not supported by openxlsx. I opened an issue here which has been addressed, so if you are having this problem update your installation of openxlsx to the main branch of github.

Answer 2

I also got the same error when opening a workbook created in openxlsx, but the cause was different and - after repairing - some data was actually missing in the first column.

After a good deal of troubleshooting it turned out that I applied a style to column "0":

openxlsx::addStyle(wb, sheet=1, style=sty3, rows=1, cols=c(0:34), gridExpand = TRUE, stack = FALSE)

That's a big no-no. Columns should always start above 0: cols=c(1:34).