Apache POI Focus on a particular cell in generated excel file

Question

Hi I am using apache poi to generate excel files. I am using XSSFWorkbook format. I am trying to make focus on the first cell of the generated excel file.

I have tried the following code snippet.

        try {
                Sheet sheet = workbook.getSheetAt(0);
                workbook.setActiveSheet(0);
                Cell cell = sheet.getRow(0).getCell(0);
                cell.setAsActiveCell();
                sheet.setActiveCell(cell.getAddress());
                sheet.showInPane(0, 0);
            } catch (IllegalArgumentException e) {
                LOGGER.error("Failed to set active sheet and cell.", e);
            }

I have looked at this SO question as well. The solution doesn't seem to work for me. Can anyone please help me here?

P.S: I am using apache poi version 3.15.

Update 1:

I also have a freeze pane with top left cell as C1. The freeze was not being shown properly.

I have tried the following code

public void setActiveCell(Workbook workbook, int sheetIndex, int row, int column) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        CellAddress cellAddress = new CellAddress(row, column);

        sheet.createFreezePane(2, 0);
        ((XSSFSheet) sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).getPane()
            .setTopLeftCell("C1");
        ((XSSFSheet) sheet).setActiveCell(cellAddress);

//        ((XSSFSheet) sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0)
//            .setTopLeftCell(cellAddress.formatAsString());
//        ((XSSFSheet) sheet).setActiveCell(cellAddress);
}

I am calling the above code as setActiveCell(workbook, 0, 0, 0);. It is assured that the workbook is not null and contains atleast one sheet. The above code shows neither the C1 cell (the top left cell of the created pane) nor the A1 cell (the active cell set).

Update 2:

Based on the answer from @AlexRichter, the following code works for me:

public void setActiveCell(Workbook workbook, int sheetIndex, int row, int column) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        CellAddress cellAddress = new CellAddress(row, column);

        sheet.createFreezePane(2, 0);
        ((XSSFSheet) sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).getPane()
            .setTopLeftCell("C1");

        ((XSSFSheet) sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0)
            .setTopLeftCell(cellAddress.formatAsString());
}

Answer 1

Unfortunately XSSFSheet.showInPane is buggy.

The following works for me:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;

import java.io.FileOutputStream;

class TopLeftCell {

 public static void main(String[] args) throws Exception{
  Workbook wb = new XSSFWorkbook();

  Sheet sheet = wb.createSheet("new sheet");

  ((XSSFSheet)sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).setTopLeftCell("D10");
  ((XSSFSheet)sheet).setActiveCell(new CellAddress("E11"));

  wb.write(new FileOutputStream("TopLeftCell.xlsx"));
  wb.close();
 }
}

It uses setTopLeftCell from the basic low level objects.

If you have panes, then you must set the TopLeftCell for the pane you need. Example:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;

import java.io.FileOutputStream;

class TopLeftCell {

 public static void main(String[] args) throws Exception{
  Workbook wb = new XSSFWorkbook();

  Sheet sheet = wb.createSheet("new sheet");
/*
  ((XSSFSheet)sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).setTopLeftCell("D10");
  ((XSSFSheet)sheet).setActiveCell(new CellAddress("E11"));
*/

  sheet.createFreezePane(2, 2); //C3 is top left cell of the scrollable pane
  ((XSSFSheet)sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).getPane().setTopLeftCell("C3");
  ((XSSFSheet)sheet).setActiveCell(new CellAddress("A1"));

  wb.write(new FileOutputStream("TopLeftCell.xlsx"));
  wb.close();
 }
}

There seems to be an exception if the fixed pane contains no rows. Then setting the row in .getPane().setTopLeftCell is meaningless. Then the top row must be set directly in TopLeftCell in the SheetView.

Example:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;

import java.io.FileOutputStream;

class TopLeftCell {

 public static void main(String[] args) throws Exception{
  Workbook wb = new XSSFWorkbook();

  Sheet sheet = wb.createSheet("new sheet");

  sheet.createFreezePane(2, 0); //C1 is top left cell of the scrollable pane.
  //But if the fixed pane contains no rows, as in this example, then setting the row in 
  //getPane().setTopLeftCell is meaningless. Then the top row must be set in the SheetView.
  //Example: Row 6 shall be the top row:
  ((XSSFSheet)sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).setTopLeftCell("A6");
  ((XSSFSheet)sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).getPane().setTopLeftCell("C1");
  ((XSSFSheet)sheet).setActiveCell(new CellAddress("C6"));

  wb.write(new FileOutputStream("TopLeftCell.xlsx"));
  wb.close();
 }
}