Failed to run formula

[Daemach]

I get the following error when trying to parse a new Excel file. Excel itself is okay with this sheet. The file is attached.

Walkner Condon Model Delivery Fee Calc Q1 2024.xlsx

I'm using spreadsheet.read() for this, with simple options.

[cfsimplicity]

As the exception says, the problem is with the formula contained in cell K3, which is:

=(Table11[[#This Row],[Start Date AUM]]*Table11[[#This Row],[Fee Schedule]]/4)*(DAYS(Billing_Date,Table11[[#This Row],[Inception Date]])/DAYS(Billing_Date,Previous_Billing))

Apparently this is using "structured references" to a named "table" ("Table11") within your sheet. POI's formula parser should be able to handle these, and in fact it can if you target certain cells. For example G7 has this formula:

=SUM(Table11[Fee])

If I read just that cell, it works fine:

data = spreadsheet.read( src=path, format="query", columns=7, rows=7 );

It seems to be the formulae that contain "current row" references that are problematic, i.e. any with[#This Row] in them.

I'm afraid I've no idea why but I'll let you know if I come up with anything.

[cfsimplicity]

It seems to be the formulae that contain "current row" references that are problematic, i.e. any with[#This Row] in them.

That doesn't seem to be the issue. I can create a simple test sheet using a table and formula with current row references like this and POI calculates it just fine. Must be something else going on in your particular sheet that POI can't deal with.

[Daemach]

Hmm. The same company sent two spreadsheets - one works fine, and this is the other one.

Any chance it's conflicting with local variables or something of that nature? Is the # breaking it? The other sheet does not contain #ThisRow

[cfsimplicity]

Is the # breaking it? The other sheet does not contain #ThisRow

That was my first thought, but the underlying exception is coming from POI, not the CF engine. Also, as I say I've created a test sheet with the same [#This Row] reference syntax and it works fine.

[cfsimplicity]

Any chance it's conflicting with local variables

Actually, that's possible. There's a reference to Billing_Date in the formulae that I couldn't see defined anywhere. Where does that come from?

[Daemach]

Good question - maybe a hidden field? The sheets come from an outside vendor and I just feed them into spreadsheet-cfml without modification.

[cfsimplicity]

If I replace Billing_Date with a reference to a date in a new visible field, it works fine. So that's the problem: POI can't access that variable.

[cfsimplicity]

See #358 for a workaround. Could you give it a try?

[Daemach]

Thanks, Julian!

Is there an updated version of spreadsheet-cfml coming, then, or do I need to do something else? I don't see the "switch" you mentioned.

[cfsimplicity]

Just checkout the develop branch (or download the zip).

There'll be another official release along soon, but I've got another new feature to sort out first. Probably next week.

[Daemach]

Gotcha - how do I enable that switch, or is the default now to check the cached results?

[cfsimplicity]

Making that the default would be a potentially breaking change, so I'm loathe to do that, even though it might make sense.

I added an example of how to call the switch to #358

[Daemach]

I see - thanks for adding that.

It worked!

[cfsimplicity]

There'll be another official release along soon, but I've got another new feature to sort out first. Probably next week.

@Daemach Sorry, I haven't had time lately to get out another 3.x release, but this will be in version 4.x coming very soon I hope.