Corrupt file when dropdown list exists in loaded workbook

[christianbraathen]

• I loaded in an existing file as a workbook with the loadWorkbook command. This file consists of one sheet, and this sheet has one cell that uses a dropdown list. This dropdown list has two choices: Yes and No.
• I then added a sheet with data using addWorksheet and writeData.
• I finally saved the workbook with saveWorkbook.
• When I try to open this workbook, the file is corrupt.
• Attempting to repair the file is unsuccessful.
• Removing the dropdown and repeating the process above does not create such problems.

[JanMarvin]

Hi @christianbraathen , could you please provide an example file. You can attach it to the github issue (it was already requested in the issue template you choose to ignore). Thanks

[christianbraathen]

Hi! Sorry for the late reply here. I discovered the solution, so I'll just give it to you outright. If you still want me to modify by filling out the template and providing an example file, just let me know.

• I'm using Excel for Mac Version 16.54 (21101001)
• When I try to produce a dropdown (or also a formula, I discovered today), it produces a corrupt file when I try to open it in Excel for Mac.
• However, it does not cause a problem if I upload the file to Google Drive and open it with Google Sheets.
• It also does not cause problems if I 1) upload to Google Drive 2) open in Sheets 3) download the file from Google Sheets with File -> Download -> Microsoft Excel (.xlsx), and 4) Open locally in Excel for Mac. However, the formula (I've only tried INDEX-MATCH) is now encapsulated as an array, which it was not in Google Sheet.

However, the solution is actually ridiculously simple: Adding the sheet name to the cell references that are made within the same sheet as the formula. In ordinary Excel, writing in Sheet 1, it would normally be sufficient to write—for example—=A1 in cell B1. However, 'Sheet 1'!$A$1 is needed in B1 to avoid corrupted files in Excel for Mac.

Please note I have tried both with/without absolute references—they can be skipped. So both 'Sheet 1'!$A$1 and 'Sheet 1'!A1 produces a non-corrupt file.

So I would recommend that you modify writeFormula (and writeData if input is of class formula) to check if the reference is missing the sheet name, and to automatically prepend it if is. Alternatively, just write in the documentation that it is recommended to use sheet names in references to avoid corrupted files for Mac users.

Let me know if you need more from me. And thumbs up for an awesome package!

[JanMarvin]

Great that you found a solution! I merely requested an R example, some code to understand what you did or want to achieve. Something minimal like

wb <- createWorkbook()
addWorksheet()
writeData()
... # something that causes the corruption
saveWorkbook ()

Right now I simply got that you have an issue an found a solution. But no worries, if you've solved the issue you can simply close the issue.

[christianbraathen]

I realize now that the error was actually something quite different. It is simply that all formulas must follow the syntax of using commas as argument separators. So there is no need to use the sheet references unless the data occurs in other sheets. Demonstrated within the sheet Bar:

• INDEX(Foo!A:A; MATCH(Bar!$A$1; Foo!B:B; 0)) fails because of semicolon-separated arguments.
• INDEX(Foo!A:A; MATCH($A$1; Foo!B:B; 0)) fails because of semicolon-separated arguments.
• INDEX(Foo!A:A, MATCH(Bar!$A$1, Foo!B:B, 0)) works because of comma-separated arguments.
• INDEX(Foo!A:A, MATCH($A$1, Foo!B:B, 0)) works because of comma-separated arguments.

So @ycphs , there's a need to update the documentation of writeFormula, specifying that the arguments must be comma-separated.

[christianbraathen]

PR here: #322