Sheet Layout & Printing

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

Baseline date: 2026-10-09. This page was written against Apache POI 5.5.1 (the latest release on that date; 6.0 has not been released), Java 21 and later, Spring Boot 4.x on Spring Framework 7 and Jakarta EE 11. A sheet that holds correct values is only half of a report: the reader of books-inventory.xlsx also needs a header row that stays visible, columns wide enough for the titles, a filter on the catalogue, and a printout that fits the paper with a page number in the footer. All of it is layout and page setup data that lives in the sheet and in the workbook, not in the cells, and all of it is reachable from the Sheet interface of the ss.usermodel API that Spreadsheet Usermodel Basics introduces.

The examples use the Bookshelf scenario described in the section landing page. Cell formatting (fonts, fills, number formats) is in Cell Styles, Fonts & Formats; the validation and conditional-formatting rules that normally accompany a styled catalogue are in Data Validation & Conditional Formatting. This page covers everything that is neither a value nor a cell style.

What lives where

Layout settings are stored in different places of the file. Knowing the place explains which are cheap, which interact with each other and which survive a round trip through another tool.

Setting OOXML (.xlsx) location OLE2 (.xls) record

Column widths, hidden columns, column outline level

<cols> in the worksheet part

COLINFO, DEFCOLWIDTH

Row height, hidden row, row outline level

attributes of each <row>

ROW record

Freeze / split panes, zoom, gridlines, selected cell

<sheetViews> (<pane>, zoomScale)

WINDOW2, PANE, SCL

Outline direction (summary rows)

<sheetPr><outlinePr>

WSBOOL

Auto-filter

<autoFilter> plus a hidden defined name _xlnm._FilterDatabase

AUTOFILTERINFO and the same hidden name

Paper size, orientation, scaling, fit to page

<pageSetup>, <sheetPr><pageSetUpPr fitToPage>

SETUP, WSBOOL

Margins, centring, print gridlines

<pageMargins>, <printOptions>

LEFTMARGIN …​ BOTTOMMARGIN, PRINTGRIDLINES, HCENTER, VCENTER

Headers and footers

<headerFooter> (oddHeader, oddFooter, firstHeader, evenHeader …​)

HEADER, FOOTER

Print area, repeating rows and columns

workbook-level defined names _xlnm.Print_Area, _xlnm.Print_Titles

NAME records with the same built-in names

Manual page breaks

<rowBreaks>, <colBreaks>

HORIZONTALPAGEBREAKS, VERTICALPAGEBREAKS

Active and selected sheet, hidden sheet

<bookViews> in workbook.xml, state of <sheet>

WINDOW1, BOUNDSHEET

Two consequences matter in practice. First, the print area and the repeating rows are defined names (see Tables, Named Ranges & Pivot Tables): POI creates them for you, but a workbook that already defines a name with the same built-in identifier for the same sheet is overwritten. Second, the auto-filter also creates a hidden name, which is why an auto-filter range that is later changed must be set again rather than edited.

Column widths

The unit

Sheet.setColumnWidth(int columnIndex, int width) takes the width in 1/256 of the width of a character of the workbook’s default font (the digit 0 of the Normal style, normally Calibri 11 or Arial 10). A width of 10 * 256 is ten characters. The same unit is returned by getColumnWidth(int); getColumnWidthInPixels(int) returns a float for a pixel-oriented view. The practical limits are:

Setting Limit Notes

Width of one column

255 characters, that is 255 * 256 (65280) units

A larger value throws IllegalArgumentException ("The maximum column width for an individual cell is 255 characters." in a test on XSSF). A width of 0 hides the column.

Default width

Sheet.setDefaultColumnWidth(int) in whole characters

Applies to every column without an explicit width. getDefaultColumnWidth() reads it back.

Columns per sheet

16,384 (.xlsx), 256 (.xls)

SpreadsheetVersion.EXCEL2007.getMaxColumns() and SpreadsheetVersion.EXCEL97.getMaxColumns().

Fixed widths are the cheapest and the most predictable way to lay out a report: they cost nothing at write time, work in any environment and stay identical on every machine. For a catalogue whose columns you know (ISBN, title, author, price, stock), a constant per column is usually the right answer.

autoSizeColumn

Sheet.autoSizeColumn(int column) sets the width to fit the widest rendered value of the column; the overload autoSizeColumn(int column, boolean useMergedCells) decides whether cells that belong to merged regions take part (the default is false, because a merged title that spans six columns would otherwise widen the first one). POI measures the text with java.awt font metrics, a fact with consequences:

  • It needs fonts and AWT classes on the server. Set -Djava.awt.headless=true (or System.setProperty("java.awt.headless", "true") before the first call) so the JVM does not look for a display. On a minimal container image also install a font set and fontconfig/freetype; without them the call can fail with a java.lang.Error or an UnsatisfiedLinkError from the font code, or silently measure with a fallback font. A distroless image is the usual place to find out.

  • The font must match. The width is computed with the font of each cell; if the workbook specifies Calibri and the server only has DejaVu, the result differs from what Excel would compute and a few characters may be cut off.

  • It costs time. XSSF and HSSF walk every cell of the column and lay out its text, so the cost is proportional to the number of rows. For a sheet with 100,000 rows it is noticeable (often more than the cost of writing the sheet); call it once per column, after the data is written and never inside the row loop, and prefer fixed widths for large exports.

  • Formulas have no value yet. A formula cell of a workbook that was just created has no cached result, so there is little to measure; size the columns of computed values by hand or evaluate the formulas first (see Formulas & Evaluation).

  • It ignores the header filter button. An auto-filter drop-down arrow covers about two characters of the first row; add a small margin (sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 2 * 256)) so the first title is not hidden.

  • It is a snapshot. The width is stored; later edits do not re-measure.

The SXSSFSheet variant is different and is described in Autosizing in SXSSF.

import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class BooksInventoryColumns {

    public static void main(String[] args) throws IOException {
        System.setProperty("java.awt.headless", "true");

        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Books");

            Font bold = workbook.createFont();
            bold.setBold(true);
            CellStyle header = workbook.createCellStyle();
            header.setFont(bold);

            String[] titles = {"ISBN", "Title", "Author", "Price", "Stock"};
            Row headerRow = sheet.createRow(0);
            for (int column = 0; column < titles.length; column++) {
                Cell cell = headerRow.createCell(column);
                cell.setCellValue(titles[column]);
                cell.setCellStyle(header);
            }
            for (int index = 1; index <= 120; index++) {
                Row row = sheet.createRow(index);
                row.createCell(0).setCellValue("978014044" + (9000 + index));
                row.createCell(1).setCellValue("A long book title number " + index);
                row.createCell(2).setCellValue("Author " + index);
                row.createCell(3).setCellValue(10 + index * 0.5);
                row.createCell(4).setCellValue(index % 7);
            }

            sheet.setDefaultColumnWidth(12);          // 12 characters unless overridden
            sheet.setColumnWidth(0, 15 * 256);        // fixed: ISBN is always 13 digits
            sheet.autoSizeColumn(1);                  // measured: titles vary
            sheet.autoSizeColumn(2, true);            // measured, merged cells included
            sheet.setColumnWidth(3, 10 * 256);        // fixed: price

            // make room for the filter arrow in the header cell of the measured columns
            sheet.setColumnWidth(1, sheet.getColumnWidth(1) + 2 * 256);
            sheet.setColumnWidth(2, sheet.getColumnWidth(2) + 2 * 256);

            sheet.setAutoFilter(new CellRangeAddress(0, 120, 0, 4));

            try (OutputStream out = Files.newOutputStream(Path.of("books-inventory.xlsx"))) {
                workbook.write(out);
            }
        }
    }
}

Source: POI Quick Guide, Adjusting column width

Autosizing in SXSSF

SXSSFSheet writes rows to a temporary file as soon as they leave the sliding window, so it cannot look at the whole column afterwards. Instead it keeps, per tracked column, the widest value seen so far:

Method Behaviour

trackColumnForAutoSizing(int column)

Start tracking one column.

trackColumnsForAutoSizing(Collection<Integer>)

Start tracking several columns.

trackAllColumnsForAutoSizing()

Track every column. Convenient, but it measures text for every cell of every row, so it is the slowest option.

untrackColumnForAutoSizing(int) / untrackAllColumnsForAutoSizing()

Stop tracking (frees the bookkeeping and the measurement cost).

autoSizeColumn(int column)

Applies the tracked width. Throws IllegalStateException ("Could not auto-size column. Make sure the column was tracked prior to auto-sizing the column.") if the column is not tracked.

The tracking must be enabled before the rows are created: only rows written after the call take part. In a test with 50 rows written, then tracking enabled, then 50 longer rows, the width reflected only the second batch. A sensible order is therefore: create the sheet, declare the columns to track, write the rows, call autoSizeColumn, write the workbook.

import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class LoansReportAutosize {

    public static void main(String[] args) throws IOException {
        System.setProperty("java.awt.headless", "true");

        // keep 100 rows in memory; close() also deletes the temporary files
        try (SXSSFWorkbook workbook = new SXSSFWorkbook(100)) {
            SXSSFSheet sheet = workbook.createSheet("Loans");
            sheet.trackColumnForAutoSizing(0);              // only the borrower column is measured
            sheet.trackColumnForAutoSizing(1);

            for (int index = 0; index < 100_000; index++) {
                Row row = sheet.createRow(index);
                row.createCell(0).setCellValue("Borrower " + index);
                row.createCell(1).setCellValue("The Odyssey, copy " + (index % 12));
                row.createCell(2).setCellValue(index % 30);
            }

            sheet.autoSizeColumn(0);
            sheet.autoSizeColumn(1);
            sheet.setColumnWidth(2, 8 * 256);               // fixed: no need to track

            try (OutputStream out = Files.newOutputStream(Path.of("loans-report.xlsx"))) {
                workbook.write(out);
            }
        }
    }
}

Source: POI How-To, SXSSF and the SXSSFSheet Javadoc

The streaming workbook itself (window size, flushing, close(), temp files) is the topic of Streaming Writes with SXSSF. For a very large export the advice stays the same as for XSSF: fixed widths for all columns whose content you know, and tracking only for the one or two free-text columns.

Row heights

Method Notes

Row.setHeightInPoints(float)

The usual way. Excel’s default is 15 points (20 pixels) for a Calibri 11 sheet.

Row.setHeight(short)

The same value in twips (1/20 of a point), so 15 points is 300. Excel allows at most 409 points.

Sheet.setDefaultRowHeightInPoints(float) / setDefaultRowHeight(short)

The height of rows that do not specify one.

Row.setZeroHeight(boolean)

Hides the row (hidden="1"). Row.getZeroHeight() reads it back.

Row.getHeightInPoints() / Row.isFormatted()

The effective height (the default if none is set).

POI never computes a row height from the content. When a cell has CellStyle.setWrapText(true) and a long value, either leave the height unset and let the spreadsheet application decide (Excel and LibreOffice normally size rows without a custom height when they open the file; simpler viewers may not), or set a height yourself from the number of lines and the font height. Setting a height is a custom height: the application will not shrink or grow that row later.

Freeze and split panes

A freeze pane keeps the first rows and columns visible while the rest scrolls. It is a view setting: it does not change the data and is not printed.

Method Effect

createFreezePane(int colSplit, int rowSplit)

Freezes the colSplit columns on the left and the rowSplit rows on top. createFreezePane(0, 1) freezes the header row only; createFreezePane(2, 1) freezes the header and the ISBN and title columns. The scrollable area starts at the next cell.

createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow)

Also sets the first cell of the scrolling area, useful when the sheet should open scrolled down.

createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, PaneType activePane)

A real split (two to four independently scrolling panes). The positions are in 1/20 of a point, not in cells. activePane is a PaneType (LOWER_RIGHT, UPPER_RIGHT, LOWER_LEFT, UPPER_LEFT); this overload exists since POI 5.2.3.

getPaneInformation()

Returns a PaneInformation for a freeze pane (isFreezePane(), getHorizontalSplitPosition(), getVerticalSplitPosition()); null if the sheet has none.

Calling createFreezePane(0, 0) (both arguments zero) removes the pane (getPaneInformation() then returns null in a test on XSSF). Freeze panes work on SXSSFSheet at any time because they are part of the sheet header, not of the row data: they are written before the rows.

import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;

import org.apache.poi.ss.usermodel.PaneType;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class LoansPanes {

    public static void main(String[] args) throws IOException {
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            // 1. Freeze pane: header row and the first two columns stay visible.
            XSSFSheet books = workbook.createSheet("Books");
            books.createRow(0).createCell(0).setCellValue("ISBN");
            books.createFreezePane(2, 1);

            // 2. Split pane: the positions are in 1/20 of a point (3000 = 150 pt, 1500 = 75 pt).
            XSSFSheet loans = workbook.createSheet("Loans");
            loans.createRow(0).createCell(0).setCellValue("Borrower");
            loans.createSplitPane(3000, 1500, 3, 10, PaneType.LOWER_RIGHT);

            try (OutputStream out = Files.newOutputStream(Path.of("loans-panes.xlsx"))) {
                workbook.write(out);
            }
        }
    }
}

Source: POI Quick Guide, Freeze and Split Panes

Use the PaneType overload. The older createSplitPane(int, int, int, int, int activePane) that takes a Sheet.PANE_* constant is deprecated and marked @Removal(version="7.0.0"): its PANE_* values are the HSSF ones, while XSSF converts them with the 1-based STPane enumeration, so PANE_LOWER_RIGHT (0) wrote an empty activePane (the Javadoc of that overload points at POI bug 66173). In a test on 5.5.1 the PaneType.LOWER_RIGHT call above wrote <pane xSplit="3000.0" ySplit="1500.0" state="split" topLeftCell="D11" activePane="bottomRight"/>, so leftmostColumn and topRow do reach the file as topLeftCell, and no low-level CTPane workaround is needed. Split panes are still less common than freeze panes, so check the result in a real viewer.

Grouping and outlining

Outline groups let the reader collapse detail rows or columns behind a plus/minus button. They are used for subtotals (for example loans grouped per month under a monthly total) and for optional columns.

Method Effect

groupRow(int fromRow, int toRow) / ungroupRow(int, int)

Adds (removes) one outline level to the rows, zero based and inclusive. Calling it again on an overlapping range nests the groups; Excel supports up to seven levels.

groupColumn(int fromColumn, int toColumn) / ungroupColumn(int, int)

The same for columns.

setRowGroupCollapsed(int row, boolean collapsed)

Collapses or expands the group that contains row: the detail rows get hidden="1" and the summary row gets collapsed="1".

setColumnGroupCollapsed(int column, boolean collapsed)

The same for columns.

setRowSumsBelow(boolean) / setRowSumsRight(boolean)

Where the summary row or column sits, which decides which row gets the plus/minus button. The defaults are true (summary below the detail, to the right of the detail), as in Excel.

XSSFRow.getOutlineLevel() / HSSFRow.getOutlineLevel()

The level of an existing row, for example to inspect an uploaded file (the Row interface does not declare it).

 
import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class LoansGrouping {

    public static void main(String[] args) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Loans by month");

            for (int index = 0; index < 14; index++) {
                Row row = sheet.createRow(index);
                row.createCell(0).setCellValue(index % 7 == 6 ? "Total" : "Loan " + index);
            }

            sheet.setRowSumsBelow(false);              // the total row sits above the detail
            sheet.groupRow(1, 5);                      // January detail: rows 2-6 in Excel numbering
            sheet.groupRow(8, 12);                     // February detail
            sheet.setRowGroupCollapsed(1, true);       // January starts collapsed

            sheet.groupColumn(2, 4);                   // the three optional columns C:E
            sheet.getRow(13).setZeroHeight(true);      // a hidden helper row
            sheet.setColumnHidden(5, true);            // a hidden helper column

            try (OutputStream out = Files.newOutputStream(Path.of("loans-grouped.xlsx"))) {
                workbook.write(out);
            }
        }
    }
}

Source: POI Quick Guide, Grouping

In SXSSFSheet grouping must happen while the rows are still in the sliding window. In a test with a window of 10 rows and 100 written rows, groupRow on rows already flushed to disk did not throw but wrote no outlineLevel, and setRowGroupCollapsed on such a row threw IllegalArgumentException ("Row does not exist"). Grouping rows 92 to 96 while they were still in memory wrote outlineLevel="1", hidden="1" on the detail rows and collapsed="1" on the summary row. The practical rule: call groupRow and setRowGroupCollapsed right after writing the group, before the window moves past it (or use a window as large as the largest group). Column grouping has no such limit because column data is not streamed.

Hidden rows, columns and sheets

Method Notes

Row.setZeroHeight(true)

Hides a row. The data stays in the file and in formulas.

Sheet.setColumnHidden(int, boolean) / isColumnHidden(int)

Hides a column. A hidden column keeps its stored width, which is restored when it is shown again.

Workbook.setSheetVisibility(int, SheetVisibility)

VISIBLE, HIDDEN (the user can unhide it from the tab menu) or VERY_HIDDEN (only reachable from the VBA editor or from code). setSheetHidden(int, boolean) is the older form.

Sheet.setDisplayGridlines(boolean), setDisplayRowColHeadings(boolean), setDisplayZeros(boolean), setDisplayFormulas(boolean)

View options of the sheet. They only affect the screen; gridline printing is setPrintGridlines(boolean).

XSSFSheet.setTabColor(XSSFColor)

The colour of the sheet tab (XSSF only; the ss interface has no tab colour).

Hidden is not secret. A hidden row, column or sheet, even a VERY_HIDDEN one, is plain data in the file and appears in every extraction tool. Do not put values in them that the reader must not see; use a separate document or real encryption (see Introduction & Architecture for the component map and the later pages on encryption and protection).

Zoom

Sheet.setZoom(int percent) sets the zoom of the view, between 10 and 400 (values outside throw IllegalArgumentException). setZoom(120) is a good choice for a dense catalogue opened on a laptop. The setZoom(int numerator, int denominator) form exists only on HSSFSheet (it is not deprecated, but the Sheet interface has only setZoom(int)); use the percentage. The zoom is a view setting and does not influence the printout, which has its own scale (see Page setup).

Auto-filter

Sheet.setAutoFilter(CellRangeAddress range) shows the drop-down arrows in the first row of the range and returns an AutoFilter object. Neither AutoFilter nor XSSFAutoFilter has any public method, so pre-set criteria need the low-level CTAutoFilter bean of the sheet (getCTWorksheet().getAutoFilter()). The range should include the header row and all data rows. POI also creates the hidden defined name _xlnm._FilterDatabase that Excel expects.

Aspect Behaviour

Filtering

POI does not filter anything. The arrows are shown and Excel applies the criteria the user picks. If you set criteria in the file, you must also hide the rows that do not match (Row.setZeroHeight(true)); the file is otherwise inconsistent.

Sorting

Not provided either. Sort the data before writing it.

One per sheet

A sheet has at most one auto-filter. For several independent filter ranges use a table (see Tables, Named Ranges & Pivot Tables), which carries its own filter.

SXSSFSheet

Supported. The range is a sheet property and does not depend on the rows being in memory.

Removal

The Sheet interface has no method to remove an auto-filter. Do not add one that you may need to take back; with XSSF you would have to edit the CTWorksheet bean.

The first example of this page already uses setAutoFilter: the range new CellRangeAddress(0, 120, 0, 4) covers the header and 120 rows of five columns.

Source: POI Quick Guide, Autofilter

Page setup

The settings in this section decide how the sheet looks on paper or in a PDF produced by Excel or LibreOffice. POI only stores them; nothing in POI paginates, so it cannot tell you how many pages the sheet will print (see What POI does not provide).

PrintSetup

Sheet.getPrintSetup() returns a PrintSetup bound to the sheet:

Method Meaning

setPaperSize(short)

One of the PrintSetup constants such as A4_PAPERSIZE, A3_PAPERSIZE, A5_PAPERSIZE, LETTER_PAPERSIZE, LEGAL_PAPERSIZE. The numbers are Excel’s paperSize codes (A4 is 9, Letter is 1).

setLandscape(boolean)

true for landscape. setNoOrientation(boolean) leaves the choice to the printer.

setScale(short)

Fixed print scale in percent (10 to 400). Ignored when fit to page is on.

setFitWidth(short) / setFitHeight(short)

Number of pages across and down to fit the sheet into. A value of 0 means "as many as needed" in that direction, so (1, 0) is "one page wide, any number tall". They only take effect together with Sheet.setFitToPage(true).

setHeaderMargin(double) / setFooterMargin(double)

Distance of the header and footer from the page edge, in inches.

setCopies(short), setNoColor(boolean), setDraft(boolean), setLeftToRight(boolean)

Number of copies, black-and-white, draft quality, and the page order when the sheet is wider and taller than a page (true is across, then down).

setPageStart(short) with setUsePage(boolean)

First page number, when it should not start at 1.

setHResolution(short), setVResolution(short)

Print resolution in dpi, rarely needed.

Other page-level properties are on the Sheet:

Method Meaning

setFitToPage(boolean)

Enables the fit-to-page scaling. Without it, setFitWidth and setFitHeight are stored but ignored.

setMargin(PageMargin, double) / getMargin(PageMargin)

Page margins in inches. PageMargin is an enum (LEFT, RIGHT, TOP, BOTTOM, HEADER, FOOTER); the older setMargin(short, double) form with Sheet.LeftMargin and its siblings is deprecated.

setHorizontallyCenter(boolean) / setVerticallyCenter(boolean)

Center the printed range on the page.

setPrintGridlines(boolean), setPrintRowAndColumnHeadings(boolean)

Print the gridlines, or the row numbers and column letters.

setAutobreaks(boolean)

Whether Excel adds page breaks on its own (the default) or only the manual ones apply.

setRowBreak(int row) / setColumnBreak(int column)

Manual page break after the row or column; removeRowBreak, removeColumnBreak, getRowBreaks() and getColumnBreaks() manage them. In SXSSFSheet set the breaks before the data is flushed or use a large window.

Margins are defined in inches (A4 landscape with 0.5 inch left and right and 0.75 inch top and bottom is a common report layout).

Print area and repeating rows

The print area and the title rows are workbook-level names stored with the sheet:

  • Workbook.setPrintArea(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow) or setPrintArea(int sheetIndex, String reference) (for example $A$1:$E$121) defines _xlnm.Print_Area. getPrintArea(int) reads it and removePrintArea(int) deletes it. Note that the string form takes the reference without the sheet name ($A$1:$E$121), while getPrintArea returns it with the sheet name (Books!$A$1:$E$121).

  • Sheet.setRepeatingRows(CellRangeAddress) and setRepeatingColumns(CellRangeAddress) define _xlnm.Print_Titles: the rows (or columns) that repeat on every printed page. Use CellRangeAddress.valueOf("1:1") for the first row and CellRangeAddress.valueOf("A:A") for the first column; pass null to remove them.

The repeating header row is the single most useful print setting for a catalogue that runs to several pages.

import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;

import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.PageMargin;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class BooksInventoryPrint {

    public static void main(String[] args) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Books");
            for (int index = 0; index <= 120; index++) {
                Row row = sheet.createRow(index);
                row.createCell(0).setCellValue(index == 0 ? "ISBN" : "978014044" + (9000 + index));
                row.createCell(1).setCellValue(index == 0 ? "Title" : "Book " + index);
            }

            // paper: A4 landscape, one page wide and as many pages tall as needed
            PrintSetup setup = sheet.getPrintSetup();
            setup.setPaperSize(PrintSetup.A4_PAPERSIZE);
            setup.setLandscape(true);
            sheet.setFitToPage(true);
            setup.setFitWidth((short) 1);
            setup.setFitHeight((short) 0);

            // margins in inches, centred horizontally
            sheet.setMargin(PageMargin.LEFT, 0.5);
            sheet.setMargin(PageMargin.RIGHT, 0.5);
            sheet.setMargin(PageMargin.TOP, 0.75);
            sheet.setMargin(PageMargin.BOTTOM, 0.75);
            sheet.setHorizontallyCenter(true);

            // what to print and what repeats
            workbook.setPrintArea(0, 0, 1, 0, 120);                       // A1:B121
            sheet.setRepeatingRows(CellRangeAddress.valueOf("1:1"));       // header row on every page
            sheet.setRowBreak(59);                                         // manual break after row 60

            // header and footer
            Header header = sheet.getHeader();
            header.setLeft("Bookshelf");
            header.setCenter(HeaderFooter.fontSize((short) 14) + "Books inventory");
            header.setRight(HeaderFooter.date());
            Footer footer = sheet.getFooter();
            footer.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

            try (OutputStream out = Files.newOutputStream(Path.of("books-inventory-print.xlsx"))) {
                workbook.write(out);
            }
        }
    }
}

Source: POI Quick Guide, Headers and Footers and PrintSetup Javadoc

The class compiled and produced a file with these elements in a test on 5.5.1: <pageSetup paperSize="9" orientation="landscape" fitToWidth="1" fitToHeight="0"/>, <headerFooter> with &C&14Books inventory and &CPage &P of &N, and the defined names _xlnm.Print_Area and _xlnm.Print_Titles.

HeaderFooter is an interface in org.apache.poi.ss.usermodel and an abstract class with static helper methods in org.apache.poi.hssf.usermodel. The helpers (page(), numPages(), date(), time(), file(), sheetName(), fontSize(short), font(String, String), startBold(), endBold() …​) live only in the HSSF class, but they just return the format codes of the next section, so they work for XSSF too. Import the HSSF class explicitly, as in the example, or write the codes yourself.

Headers and footers

Sheet.getHeader() and getFooter() return the odd-page (the default) header and footer, each with setLeft, setCenter and setRight and the matching getters. An XSSFSheet also offers getOddHeader(), getEvenHeader() and getFirstHeader() (and the matching footers) for a different first page or different odd and even pages; whether Excel uses them depends on the differentFirst and differentOddEven flags of the <headerFooter> element, which you set on the CTHeaderFooter bean (sheet.getCTWorksheet().getHeaderFooter()). HSSFSheet only has the single header and footer.

The text is a string with & codes, the same ones Excel shows in its dialog:

Code Inserts

&P / &N

Current page number / total number of pages.

&D / &T

Print date / print time.

&F / &A

File name / sheet name.

&Z

File path.

&B, &I, &U, &E, &S

Toggle bold, italic, underline, double underline, strikethrough.

&nn

Font size in points, for example &14.

&"Arial,Bold"

Font name and style for the following text.

&G

Picture placeholder (the image itself is not settable through the ss API).

&&

A literal ampersand. A book title such as "Pride & Prejudice" must be written as Pride && Prejudice, or the text after the single ampersand is read as a code.

Limits and pitfalls: Excel truncates a header or footer at 255 characters, counting the codes; text entered by users (titles, author names) should be escaped for &; the six areas share the width of the page, so long text in the left part overlaps the centre. The header is stored once per sheet and is not a row, so it is not visible in the sheet and not copied by cell-based tools.

Selected and active sheet

When a workbook opens, Excel shows the active sheet and highlights the selected ones:

Method Meaning

Workbook.setActiveSheet(int) / getActiveSheetIndex()

The sheet shown when the file opens.

Workbook.setSelectedTab(int) / Sheet.setSelected(boolean)

Which tabs are selected. More than one selected tab is a group edit in Excel (a change on one sheet applies to all), so select exactly one unless you mean it.

Workbook.setFirstVisibleTab(int)

The first tab shown in the tab bar when there are many sheets.

Sheet.showInPane(int topRow, int leftCol)

Which cell is at the top-left of the scrollable area when the file opens.

Sheet.setActiveCell(CellAddress) (and Sheet.getActiveCell())

The selected cell on that sheet. Use A1 (the default) for a report.

A known trap in workbooks built from a template (see Templates & Modifying Workbooks) is two selected sheets: the template’s tab is still selected after another sheet was activated, so both tabs look selected and the user edits both at once. Call setActiveSheet and then setSelected(false) on every other sheet.

What POI does not provide

  • No pagination. POI stores page-setup values; it cannot compute how many pages a sheet will print or place the manual breaks for you. To know the page count you have to render the file in a spreadsheet application (LibreOffice in headless mode converts .xlsx to PDF; POI itself never converts to PDF, see the PDF conversion alternatives in the PDF Generation section).

  • No layout preview. There is no API to render a sheet to an image; use LibreOffice or a viewer to check the result.

  • Fonts depend on the server. autoSizeColumn needs AWT and installed fonts, and the final widths in the user’s Excel may differ from the server’s measurement.

  • No filter or sort engine. The auto-filter is a view of criteria that Excel evaluates.

  • No portable print layout. Different viewers honour different subsets of page setup (many mail and mobile previews ignore fit-to-page, repeating rows and headers). The settings are written for Excel and LibreOffice.

Memory and cost

Feature Cost Notes

Fixed column widths, freeze pane, zoom, page setup, print area, headers and footers

Negligible

A few bytes in the sheet header or the workbook.

autoSizeColumn on XSSF/HSSF

O(rows) per column, CPU bound

Text layout per cell. Call once per column after filling the data.

trackAllColumnsForAutoSizing on SXSSF

O(rows x columns) while writing

Measures every written cell; track only the columns you need.

Row heights, hidden rows

One attribute per touched row

Negligible.

Grouping

One attribute per grouped row

Outline data is per row; groups of rows in SXSSF must be defined inside the window.

setAutoFilter

Negligible

A range plus a defined name.

Changes between POI versions

Version Change relevant to layout and printing

3.x to 4.0

Enum-based APIs (CellType, SheetVisibility) replace integer constants; the percentage form setZoom(int) is the one to use. Check the Javadoc of the version you target for deprecated overloads before copying a 3.x snippet.

4.x to 5.x

The PageMargin enum overloads of setMargin / getMargin are available and the short-constant forms are deprecated in the current Javadoc. SXSSF autosizing works through the column-tracking methods described above. Since 5.1.0 logging goes through log4j-api only.

5.5.x

The deprecated int overload of createSplitPane described above was still present in 5.5.1 (marked for removal in 7.0.0); the PaneType overload (since 5.2.3) is the one to use.

6.0 (planned, not released)

The POI site announces removal of the APIs deprecated in 5.x and a raise of the baseline to JDK 17. Code that uses the PageMargin enum, setZoom(int) and SheetVisibility is already on the long-term API; code that still calls deprecated overloads is the code to port first. The announcements do not mention the layout model, so expect no change there, but re-check the release notes when 6.0 ships.

References