We help IT Professionals succeed at work.

Problem with Poi ForumulaRecord.getValue()

allelopath
allelopath asked
on
Medium Priority
452 Views
Last Modified: 2012-05-12
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

                 
Comment
Watch Question

Awarded 2011
Awarded 2011

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

Author

Commented:
Correct.
Awarded 2011
Awarded 2011

Commented:
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?

Author

Commented:
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.
Awarded 2011
Awarded 2011

Commented:
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

Author

Commented:
>>What if you write it...
When you save it, the problem goes away.
Awarded 2011
Awarded 2011

Commented:
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 ?

Author

Commented:
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.
Awarded 2011
Awarded 2011

Commented:

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?

Author

Commented:
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.
Awarded 2011
Awarded 2011

Commented:
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?
Awarded 2011
Awarded 2011

Commented:
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

Author

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

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

Author

Commented:
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.
Awarded 2011
Awarded 2011
Commented:
So, maybe I already askwed something similar, you populate file with values and formula with POI - immediately try to get result from
formula in POI - get zero. Then you open the same file with normal excel save it again. And then again open with POI - and then you get correct result.
If you just close it with POI. Then start again POI program without interveneing Excel - read it, you are still getting zero result. Correct?

Maybe you can post the code, I may also try to play with it.

Author

Commented:
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.

Author

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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.