Apache POI – Add title and page number in Xlsx file


import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFPrintSetup;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFHeaderFooter;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;


public class ExcelMain {

    public static void main(String[] args) throws IOException {

        //Initialize xlsx and create sheet and row
        FileOutputStream fileOutputStream = new FileOutputStream(new File("/tmp/header.xlsx"));
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet xssfSheet = workbook.createSheet("sheet0");
        XSSFRow xssfRow = xssfSheet.createRow(0);
        XSSFCell cell = xssfRow.createCell(0);
        cell.setCellValue("Test");

        //Set the Page margins
        xssfSheet.setMargin(Sheet.LeftMargin, 0.25);
        xssfSheet.setMargin(Sheet.RightMargin, 0.25);
        xssfSheet.setMargin(Sheet.TopMargin, 0.75);
        xssfSheet.setMargin(Sheet.BottomMargin, 0.75);
        xssfSheet.setAutobreaks(true);
        xssfSheet.setFitToPage(true);
        xssfSheet.setPrintGridlines(true);

        //Set the Header and Footer Margins
        xssfSheet.setMargin(Sheet.HeaderMargin, 0.25);
        xssfSheet.setMargin(Sheet.FooterMargin, 0.25);

        //Setup print layout settings
        XSSFPrintSetup layout = xssfSheet.getPrintSetup();
        layout.setLandscape(true);
        layout.setFitWidth((short) 1);
        layout.setFitHeight((short) 0);
        layout.setPaperSize(PrintSetup.A4_PAPERSIZE);
        layout.setFooterMargin(0.25);

        XSSFHeaderFooter header = (XSSFHeaderFooter) xssfSheet.getHeader();
        header.setCenter(HSSFHeader.font("Calibri", "Bold") + HSSFHeader.fontSize((short) 20) + "TITLE");
        header.setRight(HSSFHeader.font("Stencil-Normal", "Bold") + HSSFHeader.fontSize((short) 15) + "Page Number: " +
                HeaderFooter.page());

        workbook.write(fileOutputStream);

    }
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s