Bug 50884 - sheet.createFreezePane messes up pane location
Summary: sheet.createFreezePane messes up pane location
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.7-FINAL
Hardware: PC Windows XP
: P2 normal with 1 vote (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-03-07 08:09 UTC by Bancika
Modified: 2011-04-08 11:41 UTC (History)
1 user (show)



Attachments
Output file with messed up display (3.53 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2011-03-07 08:09 UTC, Bancika
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Bancika 2011-03-07 08:09:04 UTC
Created attachment 26739 [details]
Output file with messed up display

I'm having a trouble making a freeze pane with the same code that worked great in HSSF.

Here's my scenario: I write the data and call and call

sheet.createFreezePane(4, 1);

Sheet display looks weird. If I open the file in excel, scroll left right and re-save it seems to be fine.
Attached is the result.

Thanks,
Bane
Comment 1 Nick Burch 2011-03-21 07:55:21 UTC
Can you try creating a simple file with the same freeze pane settings in Excel? Then, unzip both the POI and Excel version (.xlsx is a zip of xml files), and try to spot what was done differently for the freeze panes, page settings etc for them?
Comment 2 mcroci 2011-03-21 09:56:41 UTC
(In reply to comment #1)
> Can you try creating a simple file with the same freeze pane settings in Excel?
> Then, unzip both the POI and Excel version (.xlsx is a zip of xml files), and
> try to spot what was done differently for the freeze panes, page settings etc
> for them?

I have down what you havesuggested and I think I ahve found the problem.

Excel-generated file:

<pane xSplit="2" ySplit="1" state="frozen" topLeftCell="C2" activePane="bottomRight"/>

POI-generated file:

<pane xSplit="2.0" ySplit="1.0" state="frozen" topLeftCell="B3" activePane="bottomRight"/>

The top left cell is wrong. I have substituted 'B3' with 'C2' and zipped the files again creating a new xlsx file and it works.
Comment 3 mcroci 2011-03-21 10:28:00 UTC
I have looked at the source code. The problem is in the method createFreezePane of the XSSFSheet class.

When a new CellReference is created, row and column are inverted in the constructor.


    public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow) {
        CTPane pane = getPane();
        if (colSplit > 0) pane.setXSplit(colSplit);
        if (rowSplit > 0) pane.setYSplit(rowSplit);
        pane.setState(STPaneState.FROZEN);
        if (rowSplit == 0) {
            pane.setTopLeftCell(new CellReference(0, topRow).formatAsString());
            pane.setActivePane(STPane.TOP_RIGHT);
        } else if (colSplit == 0) {
            pane.setTopLeftCell(new CellReference(rowSplit, 0).formatAsString());
            pane.setActivePane(STPane.BOTTOM_LEFT);
        } else {
            pane.setTopLeftCell(new CellReference(leftmostColumn, topRow).formatAsString());
            pane.setActivePane(STPane.BOTTOM_RIGHT);
        }

        CTSheetView ctView = getDefaultSheetView();
        ctView.setSelectionArray(null);
        CTSelection sel = ctView.addNewSelection();
        sel.setPane(pane.getActivePane());
    }
Comment 4 Nick Burch 2011-03-21 10:36:35 UTC
Ah, I think I fixed that a few days ago as part of bug #49381

Can you try with a nightly build / svn checkout from over the weekend and see if that fixes it for you?
Comment 5 mcroci 2011-03-21 11:20:55 UTC
(In reply to comment #4)
> Ah, I think I fixed that a few days ago as part of bug #49381
> 
> Can you try with a nightly build / svn checkout from over the weekend and see
> if that fixes it for you?

Ok, it seems to be fixed in the last nightly build.

But I think there is a Problem in this code snapshot:

if (rowSplit == 0) {
            pane.setTopLeftCell(new CellReference(0, topRow).formatAsString());

(from XSSFSheet.createFreezePane)
If there is only  a column split the file is not correct (topRow is used and this is wrong)
Comment 6 Nick Burch 2011-03-25 11:22:36 UTC
(In reply to comment #5)
> But I think there is a Problem in this code snapshot:
> 
> if (rowSplit == 0) {
>             pane.setTopLeftCell(new CellReference(0, topRow).formatAsString());
> 
> (from XSSFSheet.createFreezePane)
> If there is only  a column split the file is not correct (topRow is used and
> this is wrong)

What do you think it should be?

(We'll probably want to work up another unit test that checks that HSSF and XSSF agree, but it'd be worth ensuring that we end up with the correct same answer!)
Comment 7 mcroci 2011-04-01 07:07:31 UTC
(In reply to comment #6)
> (In reply to comment #5)
> > But I think there is a Problem in this code snapshot:
> > 
> > if (rowSplit == 0) {
> >             pane.setTopLeftCell(new CellReference(0, topRow).formatAsString());
> > 
> > (from XSSFSheet.createFreezePane)
> > If there is only  a column split the file is not correct (topRow is used and
> > this is wrong)
> 
> What do you think it should be?
> 
> (We'll probably want to work up another unit test that checks that HSSF and
> XSSF agree, but it'd be worth ensuring that we end up with the correct same
> answer!)

I think it should be 

      if (rowSplit == 0) {
          pane.setTopLeftCell(new CellReference(0, colSplit).formatAsString());
Comment 8 Nick Burch 2011-04-08 11:41:51 UTC
As of r1090301, I think this is now correct, and XSSF + HSSF seem to agree

Please shout if there's anything still wrong