asked on

Problem with Poi ForumulaRecord.getValue()

We have a problem with ForumulaRecord.getValue() returning a 0 value when we know there should be a valid value. The problem appears to be somehow related to a "corruption" in the excel file. If we have the problem and then we save the file or open & repair the file, the problem does not occur (ie getValue() returns the expected value).

The formula is something like: =f4+g4+h4+i4

Any ideas about what is going wrong or what to do?

In our class that implements HSSFListener, we have:

	public void processRecord(Record record) {

		int row = 0;
		int column = 0;

		HSSFRow hssfRow = null;
		HSSFCell hssfCell = null;

		switch (record.getSid()) {
...
		case FormulaRecord.sid:

			FormulaRecord formulaRecord = (FormulaRecord) record;
			row = formulaRecord.getRow();
			column = formulaRecord.getColumn();
			// =f4+g4+h4+i4
			double doubleValue = formulaRecord.getValue(); // sometimes unexpectedly 0
...
}

Open in new window

for_yan

But when you open "corrupted" file in Excel itslef - it works fine? So that it affects only POI operation?

allelopath

ASKER

Correct.

for_yan

SO, it just happens from time to time with the files when you are reading them with POI?
And the files were created by normal Excel not the ones created by POI?
Neverthless you need to do some re-writing of the file to get away from it - once it happens
POI will not read the foormula again until you rewrite the file?

Sorry, just curiosity, frankly dont have any idea.
But if it happens suddenly - then after some changes were amde in the file? It cannot be that you are reding file 5 times and on the 6th
it generates this error.

So maybe when Excel rewrites file it aloows itself some variations of the format
which POI does not tolerate?

allelopath

ASKER

No apologies necessary, it is good to ask questions.

The problem files are created by POI, elsewhere in the application.
If the problem occurs, it happens until the file is saved or open & repaired.
If the problem doesn't occur the first time using the file, it never happens.

It seems that when Excel saves a file, it fixes some format problem.

for_yan

I see, so the problem is that POI does not seem to write it consistently - sometimes it writes it OK and sometimes
not quite OK - still OK enough so that Excel sees it but not OK enough to see it back by the same POI

What if you write it, immediately close it, open and read again - at least for the sake of the experiment

allelopath

ASKER

>>What if you write it...
When you save it, the problem goes away.

for_yan

Sorry, I'm missing that,
so it is only observed if you write it and then try to read it even before you save it?

If you write it save it close the file, open again within the same application and read it - then it is always OK ?

allelopath

ASKER

Oh, I see our misunderstanding. There are 2 kinds of save: save from our application and save in excel.
The save that fixed the problem is the one from excel.
The file is written and saved from our application. Then it is read in. The problem then occurs. Then if you open the file with excel and save from excel, this will cause the problem to go away.

for_yan

Thanks, now I undersatnd better.

>The file is written and saved from our application. Then it is read in. The problem then occurs

Does that happens in 100% of cases?

allelopath

ASKER

When the file is created by our application, using POI to write, it always happens.
We are using POI 3.8 beta3, going to try beta4.

As I originally posted, there is this:

FormulaRecord formulaRecord = (FormulaRecord) record;

Open in new window

I was thinking the cast might not be the best, so I was looking at the FormulaRecord source for a better way to do this, statring with:

FormulaRecord formulaRecord = new FormulaRecord();
<then somehow transform record to formula record>
double doubleValue = formulaRecord.getValue();

Open in new window

but I don't see a way to do that.

for_yan

getValue() should return the result of formula calculation,
but are your referenced cells populated in the same run of the POI ?
Myabe the real order in which they are populating them relative to populating formula is not excatly the order in which
you do it in the program and at the time you want to get the result those values in the cells are not yet settled?

for_yan

By the way if I put

=c3+c4

in the brand new worksheet or normal Excel with nothing populated in the worksheet
and of course nothing in c3 and c4
then it returns zero to me

allelopath

ASKER

Interesting. The data is such that f4 and g4 have numbers, but the other 2 are blank. I will experiment.

for_yan

In normal excel it still is summing the first two in this case, but who knows if in POI it is the same?

allelopath

ASKER

The blanks cells are not the problem. I tried an equation using only the populated cells, e.g. =f4+g4 and the problem still occurs.

Also tried 3.8 beta 4, no difference.

Next, I am going to run with the POI source code and see what I can see.

ASKER CERTIFIED SOLUTION

for_yan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

allelopath

ASKER

Correct.

Can't post the code. I'll close this as we've tried various things and nothing yet.
I have another, specific question, which I will post outside of this.

allelopath

ASKER

Thanks for your help. We've eliminated a lot of what the problem is not.