Bug 21027 - [PATCH]Cloning Sheets: Formula Problems
Summary: [PATCH]Cloning Sheets: Formula Problems
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.0-dev
Hardware: PC other
: P3 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2003-06-23 19:55 UTC by Niall Pemberton
Modified: 2004-11-16 19:05 UTC (History)
0 users

Patch to correct cloning issues (6.48 KB, patch)
2003-06-23 19:56 UTC, Niall Pemberton
Details | Diff
Replacement Patch with additional bug fix (7.86 KB, patch)
2003-06-25 22:34 UTC, Niall Pemberton
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Niall Pemberton 2003-06-23 19:55:16 UTC
I've had a couple of problems trying to use HSSFSheet.cloneSheet(int)

I'll attach a patch if I can work out how to.

1) Some of the formula Ptg records can have one of three different id's - this 
is done by setting the ptgClass. The clone() methods of the relevant classes do 
not copy the ptgClass when cloning the object. I had problems with ReferencePtg 
and FuncVarPtg.

From a quick scan the classes affected are Area3DPtg, AreaPtg, FuncPtg, 
FuncVarPtg, NamePtg, NameXPtg, Ref3DPtg and ReferencePtg (I'm ignoring 

2) PasswordRecord and ContinueRecord do not implement the clone() method.
Comment 1 Niall Pemberton 2003-06-23 19:56:49 UTC
Created attachment 6944 [details]
Patch to correct cloning issues
Comment 2 Niall Pemberton 2003-06-25 22:34:14 UTC
Created attachment 6981 [details]
Replacement Patch with additional bug fix
Comment 3 Avik Sengupta 2003-06-25 22:39:18 UTC
Thanks, will add them in soon
Comment 4 Niall Pemberton 2003-06-25 22:51:18 UTC
I probably should have given a better description of the problems this caused.

Because these classes were not cloning the ptgClass attribute, when the 
spreadsheet is opened in Excel the cells with the affected functions have 
#VALUE! errors.

I have also just uploaded another patch, which has all the original changes 
plus the following changes to the FunctionRecord class:

1) data_value is not being copied in FunctionRecord.clone(). This causes a 
problem when the result is a "Empty" cell - openning the file in Excel causes a 
message "File Error: data may have been lost" to be displayed. In actual fact 
Nothing had been lost and saving the file sorted this out - doesn't look good 
to the users though.

2) When the calculated value of a Formula is not a number the 
FormulaRecord.toString() just shows "NaN" - however the 8 bytes contain 
important information. When I used biffviewer, it took me a long time to work 
out the above problem because I couldn't see it. I have changed the toString() 
to display a hex dump if its not a number.

I was just typing this in when you said your going to apply them - thanks much 

Comment 5 Avik Sengupta 2003-07-01 14:50:07 UTC
Whats functionrecord? do you mean formularecord? And why is that a problem with
an empty cell? 

Other than that, the patch itself seems fine, so i am applying now!
Comment 6 Niall Pemberton 2003-07-01 16:39:46 UTC
Sorry, yes it should have been "FormulaRecord" not "FunctionRecord".

The data_value contains the results of the formula which is either 1) a number 
2) an identifier for a String value (and a String record should follow) 3) a 
boolean value or 4) an identifier for an "empty cell".

When the result of the formula is an "empty cell" and the indicator (which is 
in data_value) is not cloned I got the message "File Error: data may have been 
lost" when opening the file using excel.

So my patch (which was simply adding a line in the clone() method to copy the 
data_value contents) sorted this out.

Thanks for applying the patch, much appreciated.

Comment 7 Avik Sengupta 2003-07-01 18:27:14 UTC
Thanks for the explanation... but "empty cell" as a formula result, can you give
an example... will help me cook up a testcase for later!