search

tfussell / xlnt

:bar_chart: Cross-platform user-friendly xlsx library for C++11+
Other
1.45k stars 406 forks source link

Implement shared and array formulas #647

Closed tfussell closed 2 years ago

tfussell commented 2 years ago

Currently cells with formulas like <f t="array" ref="G1:G3">PI()</f> or <f t="shared" ref="F2:F4" si="0">1+1</f> will be lost during parsing. This PR adds partial support for these formula types. Based on this StackOverflow question it's not feasible to write documents using shared formulas as there are Excel-specific details. The best approach therefore is to convert shared formulas into normal formulas during parsing. Unfortunately, this means that the file won't be identical if it is read and then written, but it's better than losing the formulas entirely.

Array formulas should be able to be supported fully but will require some storage in the worksheet and an interface for adding/reading/deleting them. For now they are also converted into normal formulas.

Addresses https://github.com/tfussell/xlnt/issues/639 and https://github.com/tfussell/xlnt/issues/436.

SimonSchroeder commented 2 years ago

This is a huge step forward for our use case as xlnt does not throw an exception (like with v1.5.0) anymore in our shared formulas when opening the Excel file.

However, our formulas reference other cells. I am not entirely sure of the syntax, but taking your example from above with small modifications, f< t="shared" ref="B1:B3" si="0">A1</f> still does not work as expected. Loading and saving an Excel file will write the formula A1 into all three cells. The expected behavior from Excel is that we write A1 into B1, A2 into B2 and A3 into B3.

I have one software where we either read or write from/to Excel files and never both. So, I am going to use the current version for this software. Other software that modifies Excel files unfortunately destroys the original data. So, I am going to stick to v1.5.0 in that software.