Bug 45126

Summary: setRepeatingRowsAndColumns() generates many NameRecord items with the same name
Product: POI Reporter: Gisella Bronzetti <g.bronzetti>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.0-dev   
Target Milestone: ---   
Hardware: All   
OS: All   
Attachments: Patch
sample excel spreadsheet that causes problem
patch
excel that causes problem
excel without problem generated applying the proposed patch

Description Gisella Bronzetti 2008-06-04 02:00:47 UTC
HSSFWorkBook.setRepeatingRowsAndColumns() generates new NameRecord
items all with empty 'field_12_name_text' and all with the same
'field_12_builtIn_name', that is 'Print_Title'.
This makes Excel asking for the setting of a new name for all the
duplicated NameRecord items before opening the file.
This causes some problems with page breaks, too.
This behavior happens only with the follow versions of Excel:
Office 2007: all languages
Office 03: german
Office XP: german
Comment 1 Gisella Bronzetti 2008-06-04 02:03:40 UTC
Created attachment 22069 [details]
Patch
Comment 2 Gisella Bronzetti 2008-06-04 02:56:11 UTC
Created attachment 22070 [details]
sample excel spreadsheet that causes problem
Comment 3 Josh Micich 2008-06-04 10:48:12 UTC
(In reply to comment #1)
> Created an attachment (id=22069) [details]
> Patch
> 

I have a few suggestions regarding the proposed patch

(1) It seems like the new logic in Workbook.createBuiltInName() checks for a built-in name clash (effectively comparing the byte field field_12_builtIn_name on NameRecord).  If there is a clash, the new built-in name record is replaced with a *non*-built-in name record with nameText "Excel_BuiltIn_Titles_" + <digit> (where digit is chosen to make the name unique).  
What is the justification for creating a non-built-in name record when a built-in name was requested?  I can't find any reference to the name "Excel_BuiltIn_Titles".  
Perhaps the more correct operation would be to not add a name record, if one already exists.

(2) A new junit is required, showing the mistake (as visible in latest POI svn).

(3) The deletion of lines from the existing junit looks like it might be wrong.  From what I can see in the API, the print-area can be set per sheet, and therefore a workbook with more than one sheet can have multiple print areas. The name of the unit test method and the comment also seem to support this.  I made a modification to the test case in svn r663322 - duplicating the checks for the multiple print areas before and after re-serialization.  This should make POI's *current* behaviour clearer.  If POI is wrong (in attempting to support multiple print areas) we'll need to change quite a few things. 

(4) checkNameAlreadyExists() or any method like it belongs on LinkTable.  As mentioned above, the name check might need to take sheetIx into account for built-in names. Should the name comparison be case-insensitive?  Either way add a comment.
Comment 4 Gisella Bronzetti 2008-06-20 06:10:44 UTC
Created attachment 22153 [details]
patch


I create non-built-in name record in order to imitate the same correct behavior of OpenOffice.
In effect if I open an xls file create with POI in OpenOffice and then I save the file, 
I notice that all built-in record generated by POI like "Print_Titles" are changed like
"Excel-BuiltInPrintTitles + Sheet Number + progressive number". 

I attach a reviewed patch to fix the problem and 2 xsl file created 
before and after the pach to show the mistake.
If you try to open the file 45126_old.xls with MSOffice2007 you will prompt to insert a new name.
Instead the file 45126.xls, which is generated with the patched POI, you'll be able to open it 
normally.
Comment 5 Gisella Bronzetti 2008-06-20 06:11:50 UTC
Created attachment 22154 [details]
excel that causes problem
Comment 6 Gisella Bronzetti 2008-06-20 06:13:34 UTC
Created attachment 22155 [details]
excel  without problem generated applying the proposed patch
Comment 7 Nick Burch 2008-07-10 14:49:29 UTC
Thanks for this patch. I've applied something like it to svn trunk (I moved the duplicate name detection logic to LinkTable, but otherwise left it about as was)

I've also run your test case, and it opens fine in excel, no warnings