search

awalker89 / openxlsx

R package for .xlsx file reading and writing.
Other
364 stars 79 forks source link

Corrupted xlsx when writing to file with multiple sheets #446

Open uvedhe opened 5 years ago

uvedhe commented 5 years ago

UPDATE – example file: filewith2sheets.xlsx

When loading in an existing excel file (which has 2 sheets) using wb <- loadWorkbook(targetFile) everything goes well (print(wb) shows that there are 2 sheets).

However, when saving to the file using saveWorkbook(wb, targetFile, overwrite = TRUE) the resulting file cannot be opened.

Even if I don't do anything to the wb object (like writeData), and just open (loadWorkbook()) and close (saveWorkbook()), the file gets corrupted.

When I manually delete (in Excel) the second sheet from the excel file, save the file, and then run (from R) the open/close commands, all is well.

sessionInfo()

uvedhe commented 5 years ago

When investigating (using zip -l filename) the original input file, and the corrupted file output by openxlsx (generated using my code above), I notice that the former has a folder customXml/ with a number of files, but the latter doesn't.

Could this be the problem? that openxlsx is not importing (and/or exporting) these files?

uvedhe commented 5 years ago

I found the issue: when writing to an existing sheet, if the target cells that we're writing to contain formulas, the resulting XLSX file will be corrupt.

@awalker89 , is there a way to resolve this? Or issue a warning to the user?

MatthewJWhittle commented 5 years ago

I'm also experiencing an issue when loading a workbook and saving it again. The file is corrupted and everything in sheet 1 disappears.