How to set Page Breaks View Mode for .xls using hssfwork book ApachePOI

Question

I am creating an .xls excel file using apache poi. I need to set the Page Breaks View by default. But I did look at a related question on .xlsx file . I didn't find anything "how to set Page Breaks View Mode" to using HSSF of ApachePOI

enter image description here

Answer 1

The binary BIFF file system of *.xls and the Office Open XML file system of *.xlsx are two totally different file systems. You can't mix them. In apache poi HSSF ist for the one and XSSF for the other. The high level classes of apache poi try providing methods for both the file systems. This gets done using interfaces in SS. But outside the high level classes one needs strictly differentiate the both file systems.

Setting page break preview for a sheet is not provided by the high level classes up to now. So we need the underlyinf low lewel classes. For XSSF this are the org.openxmlformats.schemas.spreadsheetml.x2006.main.* classes, which are the XML representation of the XSSF internals. But for HSSF this are the org.apache.poi.hssf.record.* classes, which are the binary representation of the HSSF internals.

Setting page break preview for a sheet could be done like so for both the file systems:

import java.io.*;

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

import org.apache.poi.hssf.model.InternalSheet;
import org.apache.poi.hssf.record.WindowTwoRecord;

public class ExcelPageBreakPreview {
  
 public static void main(String[] args) throws Exception {
  //Workbook workbook = WorkbookFactory.create(new FileInputStream("./ExcelTemplate.xlsx")); String filePath = "./ExcelInPageBreakPreview.xlsx";
  Workbook workbook = WorkbookFactory.create(new FileInputStream("./ExcelTemplate.xls")); String filePath = "./ExcelInPageBreakPreview.xls";

  Sheet sheet = workbook.getSheetAt(0);

  //set sheet in PageBreakPreview
  if (sheet instanceof XSSFSheet) {
   XSSFSheet xssfSheet= (XSSFSheet)sheet;   
   xssfSheet.lockSelectLockedCells(true);
   xssfSheet.getCTWorksheet().getSheetViews().getSheetViewArray(0).setView(org.openxmlformats.schemas.spreadsheetml.x2006.main.STSheetViewType.PAGE_BREAK_PREVIEW);   
  } else if (sheet instanceof HSSFSheet) {
   HSSFSheet hssfSheet= (HSSFSheet)sheet; 
   InternalSheet internalSheet = hssfSheet.getSheet(); 
   WindowTwoRecord record = internalSheet.getWindowTwo();
   record.setSavedInPageBreakPreview(true);
  }

  FileOutputStream fileOut = new FileOutputStream(filePath);
  workbook.write(fileOut);
  fileOut.close();
  workbook.close();
 }
}

Previous apache poi versions might not have InternalSheet HSSFSheet.getSheet public. Then one needs using reflection to get the InternalSheet:

   //InternalSheet internalSheet = hssfSheet.getSheet(); 
   java.lang.reflect.Field _sheet = HSSFSheet.class.getDeclaredField("_sheet");
   _sheet.setAccessible(true); 
   InternalSheet internalSheet = (InternalSheet)_sheet.get(hssfSheet);