ccPoiToolsExt icon

ccPoiToolsExt

POI Tools
ccPoiToolsExtForum
< All topics

Registered StackHub users may elect to receive email notifications whenever a new package version is released or a comment is posted on the forum.

There are 3 watchers.


poiReadSheetByNameAsStr() Fails on certain cells
Annie Dehghani7th Oct 2022

Hey there,

First, thanks for developing this fantastic extension! super useful.

I'm running into an error however and hoping you could maybe add some logic to handle this in a future release.

This is the error:

axon::EvalErr: Func failed: poiReadSheetByNameAsStr(POIReader poi,Str sheetname); args: (POIReader,Str)
sys::Err: org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell 'My Sheet name'!Q1190

It seems to be stumbling when it encounters cells that it doesn't know how to evaluate. In my sheet these are cells with super complicated logic. I'm wondering if you could just skip over these cells or add something akin to what excel does when it has logic errors i.e. "#ERROR" or "#NAME" so that the sheet can still be read ?

thanks! Annie

John MacEnri10th Oct 2022

Hi Annie. I'll take a look at that and see if there's an easy fix to handle cell content like that. I'll post here when there's an update. John.

John MacEnri10th Oct 2022

Hi Annie. I've posted a new build of the ccPoiToolsExt pod v1.2.1 which addresses the issue you raised. If it fails to eval the formula in a cell, it will simply put #POI_EVAL_ERROR as the cell value.

I don't know what was specifically failing to eval in your case, but I simulated the issue simply by using an unknown function name in the cell formula.

Let me know if this fix works ok for you.

Regards, John.

Annie Dehghani11th Oct 2022

hi John,

thanks for your response and fix! Unfortunately, I tested it and it's still failing.

I can email you the workbook. My email is my name ( annie dot dehghani ) at buildingsiot.com .

In the meantime, here are 2 cell formulas on which I was noticing failures :

=@IFS(LEN(AE6)=17,LOWER(D4 & "-" & C6), AE6="N/A", "N/A", LEN(AE6)=0,"")

this doesn't actually evaluate to an error in excel. I'm guessing that it's stumbling on the @sign.

=vlokup()

regular #NAME? error where the function is misspelled

Annie


Log in or Sign up to post a reply.