javaapache-poiapache-poi-4

Is there a way to create a split plane horizontally using the Apache POI library in Java? [EXCEL + APACHE POI]

using the createSplitPlane() method in XSSFSheet it is only able to do a 4 pane split. Is there a way to do a 2 plane split instead horizontally?

Solution

  • When using Sheet.createSplitPane one sets following parameters:

    So if you set xSplitPos to 0 and leftmostColumn to 0, then you should get what you want.

    But there is a bug with activePane.

    The constant fields in Sheet are as follows:

    PANE_LOWER_RIGHT  0
PANE_UPPER_RIGHT  1
PANE_LOWER_LEFT   2
PANE_UPPER_LEFT   3

    But the corresponding values in org.openxmlformats.schemas.spreadsheetml.x2006.main.STPane are:

    INT_BOTTOM_RIGHT 1
INT_TOP_RIGHT    2
INT_BOTTOM_LEFT  3
INT_TOP_LEFT     4

    So the first are 0-based while the second are 1-based.

    That's why one needs using +1 to each of the Sheet constants.

    Example:

    import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

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

  try (
       Workbook workbook = new XSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {
       //Workbook workbook = new HSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xls") ) {

   Sheet sheet = workbook.createSheet();
   
   sheet.createSplitPane(0, 100*20, 0, 9, Sheet.PANE_UPPER_LEFT+1);
   
   workbook.write(fileout);
  }
 }
}

    Result:

    enter image description here

    And when using

    ...
sheet.createSplitPane(100*20, 0, 3, 0, Sheet.PANE_UPPER_LEFT+1);
...

    the result looks like:

    enter image description here

    This is tested and works using apache poi 4.1.1 as well as apache poi 5.2.2.

    There are additional issues in HSSF using HSSFSheet.createSplitPane. But that's another question.