Bug 59645

Summary: Add Support for Page Setup Print row and column headings
Product: POI Reporter: Terry Warren <terrywarren>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: enhancement    
Priority: P2    
Version: 3.14-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: differences in hex dumps for 2 reference xlsx files

Description Terry Warren 2016-05-31 14:03:32 UTC
In Page Setup -> Sheet there is a checkbox for Row and Column Headings. Checking this checkbox causes the Row numbers and Column Letters to be printed on each page. There doesn't seem to be any support for this in POI.

There is (as mentioned in resolution of Bug 27084) support for Repeating Rows and Columns; however this only causes designated rows and columns to be repeated on each printed page. It is not related to print Row and column headings. (Which is why there are separate settings for these two features)
Comment 1 Javen O'Neal 2016-06-17 10:38:10 UTC
Do you know where this is saved in the xlsx file? The printer settings (this sounds like one) are saved in a binary printerSettings.bin. [1]

If so, this is something that would take a non-trivial amount of work to add as someone would have to write code that could read the binary file into record objects, mutate one of the record objects, and write the records back out as a binary file.

If you're not sure, you could create two Excel files that differ only by the page setup settings. Rename the .xlsx files to .zip, extract them, and recursively diff the files. Hopefully this is a change in one of the XML files that defines the view (like freeze pane or split pane). *fingers crossed*

[1] http://stackoverflow.com/questions/23139328/where-is-the-definition-of-the-xlsx-printersettings-bin-file-format
Comment 2 Terry Warren 2016-06-18 23:22:48 UTC
Created attachment 33960 [details]
differences in hex dumps for 2 reference xlsx files

comment was added in my latest reply
Comment 3 Terry Warren 2016-06-18 23:24:48 UTC
I don't really have any in depth knowledge of xlsx internal formats. However, I did attempt to create 2 spreadsheets as suggested. I don't have a fancy difference program, but I do have a program that produces a hex dump, so I did that for the 2 files and looked at them. The files were created as follows:

(1) empty excel spreadsheet, then go to Page Setup -> Sheet and checked the Pint Row and Column headings checkbox.
(2) saved this file as reference1.xlsx
(3) unchecked the box and saved again, this time as reference2.xlsx
(4) created the hex dumps for those 2 files.

Attached is a printout of what seemed to be different in the 2 files (not necessarily a thorough examination):

seem to be the same up until offset 48E0 where that 1 byte is different
then different again at offset:
  reference1 48F0 - 4A7E
  reference2 48F0 - 4A8B

(so, reference1 has 11 extra bytes, plus differences)

then same again

I can send complete hex dump output if it would be helpful.
Comment 4 Terry Warren 2016-06-18 23:30:35 UTC
(In reply to Terry Warren from comment #3)
> I don't really have any in depth knowledge of xlsx internal formats.
> However, I did attempt to create 2 spreadsheets as suggested. I don't have a
> fancy difference program, but I do have a program that produces a hex dump,
> so I did that for the 2 files and looked at them. The files were created as
> follows:
> 
> (1) empty excel spreadsheet, then go to Page Setup -> Sheet and checked the
> Pint Row and Column headings checkbox.
> (2) saved this file as reference1.xlsx
> (3) unchecked the box and saved again, this time as reference2.xlsx
> (4) created the hex dumps for those 2 files.
> 
> Attached is a printout of what seemed to be different in the 2 files (not
> necessarily a thorough examination):
> 
> seem to be the same up until offset 48E0 where that 1 byte is different
> then different again at offset:
>   reference1 48F0 - 4A7E
>   reference2 48F0 - 4A8B
> 
> (so, reference1 has 17 extra bytes, plus differences)
> 
> then same again
> 
> I can send complete hex dump output if it would be helpful.
Comment 5 Andreas Beeker 2016-06-19 00:29:56 UTC
I've just repeated that test and printerSettings1.bin is left unchanged.

In the sheet1.xml there is a "printOptions" element with attribute "headings"="1", when the row and column headings are checked. I guess this can be easily changed via the xmlbeans objects ... something like:
sheet.getCTWorksheet().getPrintOptions().setHeadings(true);

Regarding your hex dumps, can it be, that you've compared the xlxs-files and not their content, i.e. .xlsx are .zip files, so you need to unpack them first before comparing ...
Comment 6 Terry Warren 2016-06-19 11:11:19 UTC
yes, I did not "unzip" the .xlsx files; When I look at them using text wrangler app, it does this automatically and I can see the printOptions element.
Comment 7 Terry Warren 2016-06-19 14:09:14 UTC
one additional comment:

if I check "gridlines" checkbox in Page Setup -> Sheet as well as Headings then the xml contains the following:

<printOptions headings="1" gridLines="1"/>

and, currently, there is support for printing gridlines:

sheet.setPrintGridlines(true)

so, it would seem that adding support for print headings should not be too difficult.
Comment 8 Javen O'Neal 2016-06-19 18:20:48 UTC
Applied in r1749191.