Insert Header and Footer in Excel using Apache POI for Java

Apache POI is a popular Java library that allows you to work with Microsoft Office formats, including Excel spreadsheets. The Apache POI’s XSSF (XML Spreadsheet Format) module works with Excel files in the newer XML-based format, commonly known as .xlsx.

Headers and footers in Excel are the areas at the top and bottom of each page in a worksheet where you can place content such as titles, page numbers, dates, and other relevant information. Apache POI provides functionalities to work with headers and footers using the XSSF module.

Here’s how you can work with headers and footers using Apache POI XSSF.

Creating Headers and Footers in Excel using Java

To create headers and footers, you first need to obtain an instance of the XSSFSheet class from your workbook. Then, you can use the getHeader() and getFooter() methods to access the header and footer objects respectively. You can set different content for the left, center, and right parts of the header/footer using methods like setLeft(), setCenter(), and setRight().

Apache POI Java Code for Adding Headers and Footers to Excel Files in Java

The following Java code can be used from Apache POI to insert headers and footers to Excel files.

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet) wb.createSheet("new sheet");
// Create a first page header
Header header = sheet.getFirstHeader();
header.setCenter("Center First Page Header");
header.setLeft("Left First Page Header");
header.setRight("Right First Page Header");
// Create an even page header
Header header2 = sheet.getEvenHeader();
header2.setCenter("Center Even Page Header");
header2.setLeft("Left Even Page Header");
header2.setRight("Right Even Page Header");
// Create an odd page header
Header header3 = sheet.getOddHeader();
header3.setCenter("Center Odd Page Header");
header3.setLeft("Left Odd Page Header");
header3.setRight("Right Odd Page Header");
// Set/Remove Header properties
XSSFHeaderFooterProperties prop = sheet.getHeaderFooterProperties();
prop.setAlignWithMargins(true);
prop.setScaleWithDoc(true);
prop.removeDifferentFirst(); // This does not remove first page headers or footers
prop.removeDifferentOddEven(); // This does not remove even headers or footers
//Working with Footer
Footer footer = sheet.getFooter();
footer.setLeft("Left Footer");
footer.setCenter("Center Footer");
footer.setRight("Right Footer");
try (FileOutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
    wb.write(fileOut);
}

The above code sample gets three types of headers from the sheet i.e. Center, Even page, and Odd page headers. The Header class is used for this purpose. Apache POI provides the options to get the Even and Odd Page headers to set them explicitly. This lets you have control over different even and odd page headers. The getEvenHeader and getOddHeader methods of the XSSFSheet class are used to get these header objects for manipulation.

Special Characters and Formatting

Headers and footers can contain special formatting and placeholders for dynamic information. Apache POI supports placeholders like &[Page], &[Date], and &[Time] to automatically insert page numbers, dates, and times.

footer.setCenter("Page &[Page] of &[Pages]");

Different for the First Page

Headers and footers can be different for the first page and the odd/even pages. You can use setFirstHeader(), setFirstFooter(), setOddHeader(), and setOddFooter() methods to set different content for different scenarios.

header.setFirstHeader("First Page Header");
footer.setOddFooter("Odd Page Footer");

Setting Header/Footer Size

You can also control the size of the header/footer using the setHeaderMargin() and setFooterMargin() methods.

sheet.getHeader().setHeaderMargin(0.5); // Set header margin to 0.5 inches
sheet.getFooter().setFooterMargin(0.5); // Set footer margin to 0.5 inches

Clearing Headers and Footers

If you want to remove the header or footer, you can use the clear() method.

header.clear(); // Clear the header content
footer.clear(); // Clear the footer content

Conclusion

Working with headers and footers in Apache POI XSSF allows you to customize the appearance of your Excel worksheets and add important information to the printed or saved documents. By leveraging the features provided by Apache POI, you can easily manage headers and footers to meet your specific requirements. For more examples on working with Apache POI to manipulate your Excel documents in Java, please stay tuned.