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

Question

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?

Answer 1

When using Sheet.createSplitPane one sets following parameters:

• xSplitPos - Horizontal position of split (in 1/20th of a point).
• ySplitPos - Vertical position of split (in 1/20th of a point).
• leftmostColumn - Left column visible in right pane.
• topRow - Top row visible in bottom pane
• activePane - Active pane. One of: PANE_LOWER_RIGHT, PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT.

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:

And when using

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

the result looks like:

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.