Bug 50884

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?

(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.

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());
    }

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?

(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)

(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!)

(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());

As of r1090301, I think this is now correct, and XSSF + HSSF seem to agree

Please shout if there's anything still wrong