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

I think there is one method which you can use for the same sheet.showInPane() . Try this method and also checkout this discussion here http://apache-poi.1045710.n5.nabble.com/RE-How-to-mandate-showing-the-active-cell-when-the-Excel-file-is-first-opened-td2281035.html — Bhavesh, Jul 13 '17 at 07:04
@yaswanth: `.getPane().xsetTopLeftCell().setTopLeftCell("C1")` is not what I have suggested nor will it compile. — Axel Richter, Jul 13 '17 at 11:12
@yaswanth: OK, but now I cannot reproduce your behavior. Using your code snippet works for me as expected having a default `XSSFSheet`. Columns `A:B` are fixed, scrollable pane starts with column `C` and active cell is `A1` in fixed pane. — Axel Richter, Jul 13 '17 at 11:21

Axel Richter · Accepted Answer · 2017-07-13T12:05:21.960

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

Thanks a lot for the workaround. I have another issue. I have a freeze pane on second column and second row. Although I can see the active cell as (0,0) (which I intended), the freeze pane moves the excel sheet to the right showing some 25th column. Meaning I can see the first row and first column. I can see the excel sheet until second column. After that I see the 25th column. Can you help me get the freeze pane to show the excel sheet as is without moving? — yaswanth, Jul 13 '17 at 09:53
@yaswanth: Thats exactly where `XSSFSheet.showInPane` is buggy also. If you **have** panes, then you must set the TopLeftCell for the pane you need. But you will not always have panes. See my supplement. — Axel Richter, Jul 13 '17 at 10:22
getPane().setTopLeftCell("C1") (My freeze pane freezes till column B) doesn't seem to work. Although the excel sheet opens up on column C, it doesn't show the first row which I intended. — yaswanth, Jul 13 '17 at 10:45
@yaswanth: My example code works as intended. If you have code which not works as intended, then we need to see that code. Please update your question providing a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve). — Axel Richter, Jul 13 '17 at 10:51
@AlexRichter, I have updated my code in the question. Your help is greatly appreciated! — yaswanth, Jul 13 '17 at 11:03
@AlexRichter, I have a working code snippet. Please find the code in Update 2 in the question. — yaswanth, Jul 13 '17 at 11:42
@yaswanth: Yes, if the fixed pane contains no rows, as in this example, then setting the row in `.getPane().setTopLeftCell` seams to be meaningless. Then the top row must be set in the SheetView. See my supplement. — Axel Richter, Jul 13 '17 at 12:01
@AlexRichter Correct. That has solved the issue. Thanks a lot for the answer!! — yaswanth, Jul 13 '17 at 14:03

Apache POI Focus on a particular cell in generated excel file

1 Answers1

Linked