Link to home
Create AccountLog in
Avatar of allelopath
allelopath

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

                 
Avatar of for_yan
for_yan
Flag of United States of America image

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

ASKER

Correct.
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?
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.
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
>>What if you write it...
When you save it, the problem goes away.
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 ?
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.

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?
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.
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?
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
Interesting. The data is such that f4 and g4 have numbers, but the other 2 are blank. I will experiment.
In normal excel it still is summing the first two in this case, but who knows if in POI it is the same?
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
Avatar of for_yan
for_yan
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
Thanks for your help. We've eliminated a lot of what the problem is not.