Apache POI XSSFWorkbook setSheetOrder not sorting

Question

I'm using Apache POI (4.1.1) to modify an existing workbook. The workbook contains four sheets. I'm using the second sheet as a template by cloning it, then writing data to the new cloned sheets. It's cloned around six times or so. At the end of this process I remove the template sheet, then I try to set a sheet order to all the sheets: the existing sheets (minus the template) and then the sheets I cloned. I'm using XSSFWorkbook::setSheetOrder to do this, and I see it change the index in debug, but when my Excel file actually writes out, the sheets are not in order.

Here is the relevant code:

        // wb is an XSSFWorkbook
        wb.removeSheetAt(wb.getSheetIndex("Original Sheet 2")); // Remove the template sheet
        wb.setSheetOrder("Original Sheet 1", 0);
        for (ReportPresenter presenter : dataMap.values()) {
            String sheetTitle = makeSheetTitleFromPresenter(presenter);
            int oldIndex = wb.getSheetIndex(wb.getSheet(sheetTitle));
            wb.setSheetOrder(sheetTitle, presenter.getAnalysisNumber());
            int newIndex = wb.getSheetIndex(wb.getSheet(sheetTitle));
            System.out.println(oldIndex + " -> " + newIndex);
        }
        wb.setSheetOrder("Original Sheet 3", dataMap.size() + 1);
        wb.setSheetOrder("Original Sheet 4", dataMap.size() + 2);

I included the debug println above to show here how I know that the index is actually being changed to the order I want with the setSheetOrder method. However, when I open the Excel file that is generated by this code, my sheets all exist but they are not in the order I have specified.

I would really appreciate some ideas! Thank you.

Axel Richter Axel Richter · Accepted Answer · 2020-03-28T06:38:21

Cannot reproducing that behavior. Please show a Minimal, Reproducible Example which shows that behavior.

I will provide a such which shows that it works properly:

Start with Execl.xlsx like this:

Then following code:

import java.io.FileOutputStream;
import java.io.FileInputStream;

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

public class ExcelSetSheetOrder {

 public static void main(String[] args) throws Exception {

  java.util.Map<Integer, ReportPresenter> dataMap = new java.util.HashMap<Integer, ReportPresenter>();
  dataMap.put(1, new ReportPresenter("New Sheet 1", 1));
  dataMap.put(2, new ReportPresenter("New Sheet 2", 2));
  dataMap.put(3, new ReportPresenter("New Sheet 3", 3));
  dataMap.put(4, new ReportPresenter("New Sheet 4", 4));
  dataMap.put(5, new ReportPresenter("New Sheet 5", 5));
  dataMap.put(6, new ReportPresenter("New Sheet 6", 6));

  try (XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("./Excel.xlsx"))) {

   workbook.cloneSheet(workbook.getSheetIndex("Original Sheet 2"), dataMap.get(1).getSheetTitle());
   workbook.cloneSheet(workbook.getSheetIndex("Original Sheet 2"), dataMap.get(2).getSheetTitle());
   workbook.cloneSheet(workbook.getSheetIndex("Original Sheet 2"), dataMap.get(3).getSheetTitle());
   workbook.cloneSheet(workbook.getSheetIndex("Original Sheet 2"), dataMap.get(4).getSheetTitle());
   workbook.cloneSheet(workbook.getSheetIndex("Original Sheet 2"), dataMap.get(5).getSheetTitle());
   workbook.cloneSheet(workbook.getSheetIndex("Original Sheet 2"), dataMap.get(6).getSheetTitle());

   workbook.removeSheetAt(workbook.getSheetIndex("Original Sheet 2")); // Remove the template sheet
   workbook.setSheetOrder("Original Sheet 1", 0);
   for (ReportPresenter presenter : dataMap.values()) {
    String sheetTitle = presenter.getSheetTitle();
    int oldIndex = workbook.getSheetIndex(workbook.getSheet(sheetTitle));
    workbook.setSheetOrder(sheetTitle, presenter.getAnalysisNumber());
    int newIndex = workbook.getSheetIndex(workbook.getSheet(sheetTitle));
    System.out.println(oldIndex + " -> " + newIndex);
   }
   workbook.setSheetOrder("Original Sheet 3", dataMap.size() + 1);
   workbook.setSheetOrder("Original Sheet 4", dataMap.size() + 2);

   String filePath = "./ExcelNew.xlsx";
   try (FileOutputStream fileOut = new FileOutputStream(filePath)) {
    workbook.write(fileOut);
   }
  }
 }

 static class ReportPresenter {
  String sheetTitle = "";
  int analysisNumber = -1;

  public ReportPresenter(String sheetTitle, int analysisNumber) {
   this.sheetTitle = sheetTitle;
   this.analysisNumber = analysisNumber;
  }

  public int getAnalysisNumber() {
   return this.analysisNumber;
  }
  public String getSheetTitle() {
   return this.sheetTitle;
  }
 }
}

prints the following on console:

axel@arichter:~/Dokumente/JAVA/poi/poi-4.1.1$ javac -Xlint:deprecation -Xlint:unchecked -cp .:./*:./lib/*:./ooxml-lib/* ExcelSetSheetOrder.java 
axel@arichter:~/Dokumente/JAVA/poi/poi-4.1.1$ java -cp .:./*:./lib/*:./ooxml-lib/* ExcelSetSheetOrder 
3 -> 1
4 -> 2
5 -> 3
6 -> 4
7 -> 5
8 -> 6

and leads to this result ExcelNew.xlsx:

Apache POI XSSFWorkbook setSheetOrder not sorting

2 Answers