Semicolon in formulas replaced with |

[nieejl]

trafficstars

Semi-colon does not seem to work in formulas. Added a fix in #26 - Thank you for your efforts with this library!

[rabanti-github]

Hello Sorry for the late response. Actually, I wrote an answer the evening you filed the issue and provided the PR. But it looks as I never submitted this comment. Then I had to deal with moving to another city for some days until now. Thank you for the feature and the PR. Only some minor changes. I will publish a minor version then ASAP. The function (and test cases will be taken into account too in PicoXLSX, nanoXLSX4j and the new major version of NanoXLSX, currently under development in the dev branch.

Thank you in advance

[rabanti-github]

Hello Sorry to say, but it looks, like the PR cannot be merged yet. I stumbled into issues when I tried to implement 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>

output: a;b

However, a general string sanitizing would lead to:

<c r="B1" t="str">
        <f>CONCATENATE(A1,",",A2)</f>
</c>

Output: a,b

So, we cannot go with 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.

[nieejl]

Thank you for your time. That seems reasonable, and perhaps trying to fix this is the wrong way to go about it. There's a number of edge cases, eg. triple quotes, that make this hard to solve this in a correct and simple way.

I found that the easy workaround for was to just use commas in the formula passed to AddCellFormula. I guess the primary issue here was, that I was not aware that would work. So perhaps adding a formula such as the one below, would be a good way to avoid users running into the problem.

workbook.WS.AddCellFormula("=CONCATENATE(A1,B1)", "C1") // Formula arguments separated with comma

I definitely spent a bit of time trying to figure out why the formulas I wrote did not translate as I expected, even though this it may seem obvious in hindsight.

[rabanti-github]

Hello Unfortunately I still can't take as much time for the project as I would like to. I think the best way would be to provide methods similar to that ones in the class BasicFormulas.cs. This should work, since there is a finite number of supported formula functions. However, it's still not clear how extensive such an implementation would be. Maybe a abstraction would be necessary (e.g. wrapper functions to handle formulas with two numeric parameters). I'm still working on the dev branch for the new major version. Maybe I can incorporate this solution instead of BasicFormulas.