POI after delete rows Excel opens at bottom-CodePudding

I have a requirement to add data to an excel file containing macros. I am using the following code after adding data using Apache POI(4.1.2) to delete excess rows in the Excel template and create a new file after it. It's deleting the rows correctly but my template has around 300 rows and after creating new file it has around 100 rows. Now after opening the Excel file it opens at the bottom in the empty area i.e row 300 where template previously had data and I have to always scroll up to reach the top of file. How can I correct this behaviour ?

   private void removeExtraRows(Sheet sheet, int rowIndex) {
        while (rowIndex < sheet.getLastRowNum() - 1) {
            removeRow(sheet, rowIndex);
        }
    }

    private void removeRow(Sheet sheet, int rowIndex) {
        sheet.removeRow(sheet.getRow(rowIndex));
        sheet.shiftRows(rowIndex   1, sheet.getLastRowNum(), -1);
    }

CodePudding user response：

Excel stores what cell is the active cell in a sheet. Also it stores what cell is the top left cell in actual sheet view respectively in actual view pane per sheet.

So after removing rows and so eventually removing the formerly visible top left cell and/or active cell one should set new the active cell and the top left cell. So one can determine what gets visible if the sheet gets visible.

Following method can do this:

 void setTopLeftCell(Sheet sheet, int row, int column) {
  CellAddress cellAddress = new CellAddress(row, column);
  sheet.setActiveCell(cellAddress);
  if (sheet instanceof XSSFSheet) {
   // Office Open XML Excel format always has sheet view
   ((XSSFSheet)sheet).getCTWorksheet().getSheetViews().getSheetViewArray(0).setTopLeftCell(cellAddress.formatAsString());
  } else {
   // binary BIFF Excel format always has panes
   sheet.showInPane(row, column);
  }
 }

Usage to set A1 to be the top left cell in sheet view respectively in actual view pane:

setTopLeftCell(sheet, 0, 0);