[Bug] Excel Reader with array references produce 0 instead of missing

[mwiegand]

Hi there,

some time ago the Excel Sheets reader had a problem with array references like:
={A:A}

where only the first row had values. The tables looked like so:

Now this seems to have been partially fixed as values are present but empty values are imported as “0”:

Here is a workflow which replicates the issue:

https://hub.knime.com/mw/spaces/Public/latest/Bugs/Excel%20Sheet%20with%20formula

Installed extensions

KNIME Amazon Cloud Connectors	4.2.0.v202006171540	org.knime.features.cloud.aws.feature.group	KNIME AG, Zurich, Switzerland
KNIME Analytics Platform	4.2.3.v202011031551	org.knime.desktop.product	null
KNIME BigQuery	4.2.0.v202007101231	org.knime.features.database.extensions.bigquery.feature.group	KNIME AG, Zurich, Switzerland
KNIME Connectors for Common Databases	4.2.0.v202006221532	org.knime.features.dbdrivers.feature.group	KNIME AG, Zurich, Switzerland
KNIME Database	4.2.1.v202008251536	org.knime.features.database.feature.group	KNIME AG, Zurich, Switzerland
KNIME Ensemble Learning Wrappers	4.2.0.v202004061016	org.knime.features.ensembles.feature.group	KNIME AG, Zurich, Switzerland
KNIME Excel Support	4.2.1.v202008251158	org.knime.features.ext.poi.feature.group	KNIME AG, Zurich, Switzerland
KNIME Expressions	4.2.2.v202009241054	org.knime.features.expressions.feature.group	KNIME AG, Zurich, Switzerland
KNIME Extension providing the Chromium Browser	83.0.4103.116	org.knime.features.ext.chromium.feature.group	KNIME AG, Zurich, Switzerland
KNIME File Handling Testing Framework	4.2.3.v202011021201	org.knime.features.filehandling.core.testing.feature.group	KNIME AG, Zurich, Switzerland
KNIME Google Cloud Storage Connection	4.2.2.v202009241054	org.knime.features.google.cloud.storage.feature.group	KNIME AG, Zurich, Switzerland
KNIME Google Connectors	4.2.2.v202009241054	org.knime.features.google.api.feature.group	KNIME AG, Zurich, Switzerland
KNIME Image Processing	1.8.3.202010230604	org.knime.knip.feature.feature.group	University of Konstanz / KNIME
KNIME JavaScript Views	4.2.3.v202011021201	org.knime.features.js.views.feature.group	KNIME AG, Zurich, Switzerland
KNIME Javasnippet	4.2.0.v202006150933	org.knime.features.javasnippet.feature.group	KNIME AG, Zurich, Switzerland
KNIME JSON-Processing	4.2.1.v202008251158	org.knime.features.json.feature.group	KNIME AG, Zurich, Switzerland
KNIME Math Expression (JEP)	4.2.2.v202009241054	org.knime.features.ext.jep.feature.group	KNIME AG, Zurich, Switzerland
KNIME Personal Productivity Tools	4.2.0.v202001210919	org.knime.features.personalproductivity.feature.group	KNIME AG, Zurich, Switzerland
KNIME Plotly	4.2.0.v202006071334	org.knime.features.js.plotly.feature.group	KNIME AG, Zurich, Switzerland
KNIME Public Server Access	4.2.3.v202011021202	com.knime.features.enterprise.client.exampleserver.feature.group	KNIME AG, Zurich, Switzerland
KNIME Quick Forms	4.2.3.v202011021201	org.knime.features.js.quickforms.feature.group	KNIME AG, Zurich, Switzerland
KNIME REST Client Extension	4.2.0.v202006301416	org.knime.features.rest.feature.group	KNIME AG, Zurich, Switzerland
KNIME ServerSpace	4.11.3.v202011021202	com.knime.features.explorer.serverspace.feature.group	KNIME AG, Zurich, Switzerland
KNIME Statistics Nodes	4.2.0.v202008251159	org.knime.features.stats.feature.group	KNIME AG, Zurich, Switzerland
KNIME SVG Support	4.2.2.v202009241055	org.knime.features.ext.svg.feature.group	KNIME AG, Zurich, Switzerland
KNIME Testing Framework	4.2.0.v202006171549	org.knime.features.testing.core.feature.group	KNIME AG, Zurich, Switzerland
KNIME Testing Framework - JavaScript support	4.2.1.v202008251343	org.knime.features.testing.js.feature.group	null
KNIME Testing Framework UI	4.2.1.v202008251158	org.knime.features.testing.application.feature.group	KNIME AG, Zurich, Switzerland
KNIME Timeseries nodes	4.2.0.v202003271454	org.knime.features.timeseries.feature.group	KNIME AG, Zurich, Switzerland
KNIME Virtual Nodes	4.2.0.v202004271350	org.knime.features.virtual.feature.group	KNIME AG, Zurich, Switzerland
KNIME XML-Processing	4.2.1.v202008251159	org.knime.features.xml.feature.group	KNIME AG, Zurich, Switzerland
Lhasa public plugin	1.1.500.v202007031132	org.lhasalimited.knime.public.feature.feature.group	Lhasa Limited (http://www.lhasalimited.org)
Palladian for KNIME	2.3.0.202009251618	ws.palladian.nodes.feature.feature.group	palladian.ws; Philipp Katz, Klemens Muthmann, David Urbansky
Vernalis KNIME Nodes	1.27.2.v202010191232	com.vernalis.knime.feature.feature.group	Vernalis Research Ltd, Cambridge, UK

The System Config I could not attach as the forum threw a 502. Possibly too much data. I am using macOS Catalina 10.15.7 with most recent version of Knime.

Best
Mike

[mlauber71]

From what I see the 0 (zeros) are already there in the Excel file.

macOS 11.0.1 (20B29)
KNIME 4.2.3

[SimonS]

Hi @mw,

I was able to reproduce it, thanks for reporting this. It is actually an issue of the underlying library (Apache POI). However, as we are currently revising the Excel reader node and also updating the version of the library, we will hopefully be able to fix the issue. I will let you know in this thread as soon as there are news.

Best,
Simon

[SimonS]

@mlauber71,
I actually can see the zeros in Excel as well, that’s weird. I tried with Google Sheets and LibreOffice, both are showing blank cells which makes more sense I would say. The new version of apache poi also does not return zeros anymore, so this seems to be the correct behavior for me.

[mwiegand]

Thanks all for your feedback. Looking forward to see that little annoying bug get fixed. Enjoy your weekend!

[SimonS]

Hey @mw,

the bug should be fixed in the new Excel Reader in version 4.3. Thanks for your feedback!

Best,
Simon

[hotzm]

Hi @mwiegand,

unfortunately, I have to resurrect this thread. For AP 5.3 we bumped the version of Apache POI we are using to POI.v.5.2.5 (AP and POI versions are currently confusingly similar…). The POI maintainers fixed a bug in POI 5.0, specifically that Array Formulas (they call it Area Formulas) evaluate to empty cells instead of 0 (see History of Changes → “github-191”).

I just wanted to give you (and anyone else stumbling over this thread), a heads up and our reasoning why we will introduce what you could label as a “regression” (where 0 will now be output, again).
From our point of view the node now shows the correct behavior, since:

• Excel does the exact same thing (returning 0 instead of blank cells), so “Re-evaluate formulas” now follows the Excel semantic and is now consistent with cached values.
• There is now (again) no difference whether you use “Re-evaluate formulas” or open-save from Excel.
• If users use single formulas that they “copy down” the behavior is now the same when using array formulas (and as in Excel).
• We cannot intercept/customize the evaluation in the library anyway, so there is no way to provide an option in the dialog to “keep buggy behavior”.
• There is a workaround available (IF(ISBLANK(ref), "", ref)) that yields the desired outcome not only in AP, but also in Excel (no 0 is shown for “blank references”).

[mwiegand]

Thanks for the heads up @hotzm!