Differences in inter-sheet references between Microsoft Excel and LibreOffice Calc

Today’s post is about a bit of a niche issue! Recently I was working on a PHP site that generates spreadsheets in Excel 2007+’s .xlsx format. For years we’ve been using PhpSpreadsheet to generate the spreadsheets. Since I don’t use Microsoft Office, I usually test them using LibreOffice. All seemed well, but in user acceptance testing, users told me that they were getting the following warning when they opened one of the spreadsheets in Excel:

We found a problem with some content in '<filename>.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

They weren’t able to spot any corrupt or missing data, and it opened without error in LibreOffice. Searching online, I saw a number of posts confirming the importance of making sure that no stray output from PHP gets added to the file, but that wasn’t the case here. Potential issues with PHP’s locale settings were also flagged, but that turned out not be an issue here either.

By opening one of the problematic spreadsheets in Excel in Microsoft Office 365, I was able to get it tell me which worksheet was triggering the error. That narrowed down the problem, but as it’s a complex spreadsheet, I still had some digging to do.

It turned out that the cause was due to how references to different worksheets are handled in formulae. In Microsoft Excel, if you set the contents of a cell in (for example) Sheet 2 to a cell in Sheet 1, it will look like this:

='Sheet 1'.A1

If you do the same in LibreOffice Calc, it will look like this:

=$'Sheet 1'.A1

Note the $ at the beginning of the sheet name! To make things extra confusing, if you create the spreadsheet in Excel, and then reopen it in Calc, it will show the $, even though there isn't actually one in the file. Since all I did all my testing in Calc, including working out how to structure the formulae, I was adding in the $ and Excel was complaining about it. The formulae actually worked correctly, but the users were understandably concerned that they saw an error every time they opened one of the files. After I removed the $ from my PHP code, the spreadsheet opened without any warnings in both Excel and Calc.

Takeaways:

1. (the obvious) if at all possible, use the exact same software for testing and live.
2. I hate spreadsheets.