NanoXLSX
NanoXLSX copied to clipboard
Fix semicolon issue for formulas
I found that semicolons in formulas were replaced with '|'. I added it as an illegal character, and replaced the semicolon with a comma where you escape other illegal characters. This seems to be what happens when saving something from google docs, and it seems to have worked. See Demo14. I added an optional boolean parameter to the method, as I don't have the full overview of the other use-cases of the method.
Sorry to respond again, but it looks like the solution is not robust. I stumbled into issues when implementing the feature in the dev branch. If you add a formula, e.g. with CONCATINATE, where a semicolon is part of a text, the correct output (according to Excel) would be:
<c r="B1" t="str">
<f>CONCATENATE(A1,";",A2)</f>
<v>a;b</v>
</c>
However, a general sanitizing would lead to:
<c r="B1" t="str">
<f>CONCATENATE(A1,",",A2)</f>
</c>
So, we cannot go whith this solution (PR cannot be merged, sorry). Currently, there is no good solution for this. We have probably to look directly into the AddCellFormula / AddNextCellFormula functions. However, a general replacement of ; to , is still not possible, since we have to differentiate whether the semicolon is part of a text or part of the formula.
I have to think about this a little bit.