CODEWITHSUNDEEP
exceljxls
Christian Raack
Christian Raack

Reputation: 1

Jxls -- With version 2.7.2. jointed cell references do not work anymore with several empty collections

copy from: https://bitbucket.org/leonate/jxls/issues/197/jxls-with-version-272-jointed-cell

First of all, thanks to the jxls team for this wonderful and time-saving tool!

However, there is some functionality loss in jxls 2.7.2, which unfortunately forced us to go back to 2.6.0 (I did not check the intermediate versions though).

We regularly do summations over more than one collection (more than one area). In this case the direct Excel notation "=SUM(ref1;ref2;ref3)" does not work with jxls (independent of versions), where ref* are cell references (to the body of different iterations/areas). In particular, in situations, where one or all of the references point to empty collections. The resulting processed cell references can be corrupted. Same is true when using Excel sums in the form "=SUM(ref1:ref3)" or "=ref1+ref2+ref3". (Use the attached template and try out the different options).

For this reason we have to stick to the formula "$[SUM(U_(ref1,ref2,ref3))]" which is also suggested on the jxls website: http://jxls.sourceforge.net/reference/formulas.html ("Jointed cell references").

This formula did work well with jxls 2.6.0 also if all references in the jointed reference referred to a cell that disappears during processing (an iteration over an empty collection). In this case the the formula had been processed to =0 (default). In this special case jxls 2.7.2 fails. I assume that something goes wrong internally with the default formula value.

I attached example code plus template using the $[SUM(U_())] notation, and the two results of jxls 2.6.0 and 2.7.2. You will see that jxls 2.6.0 correctly processes the sum to 0 in the second sum, while jxls 2.7.2 fails in processesing the second $[SUM(U_())] formula.

Can anyone help here?

All the best Christian

code: https://portal.atesio.de/owncloud/index.php/s/7SiSotMMPXJ4tL5

template: https://portal.atesio.de/owncloud/index.php/s/rkHKjE8wsGRPDNj

output 2.6.0: https://portal.atesio.de/owncloud/index.php/s/yMprwr7rCWYkRWY

output 2.7.2: https://portal.atesio.de/owncloud/index.php/s/BCJwD45kRAzTcJZ

Upvotes: 0

Views: 348

Answers (1)

jxls_mw
jxls_mw

Reputation: 21

Can be closed. Further discussion on https://bitbucket.org/leonate/jxls/issues/197/jxls-with-version-272-jointed-cell

We'll try to fix that for JXLS 2.8.0. If you need a fast bugfix:

Replace StandardFormulaProcessor.java and replace line

if (isFormulaCellRefsEmpty && isFormulaJointedCellRefsEmpty && !formulaCellData.isParameterizedFormulaCell()) {

to

if (isFormulaCellRefsEmpty && isFormulaJointedCellRefsEmpty) {

Upvotes: 1

Related Questions