How to Insert Text and Images to Excel Header or Footer in Java

[Jack Du]

Overview

Excel headers or footers are visible in Page Layout view or the printed pages. We can add the headers or footers at the top or bottom of a printed worksheet to display page number, worksheet name, current data, text or a picture. This article will demonstrates how to programmatically add headers or footers to a worksheet by using a free java API for Excel.

Introduction

This solution relies on Free Spire.XLS for Java, which provides a PageSetup class to deal with all page setup settings in Excel. Particularly, it offers setLeftHeader method, setLeftHeaderImage method, etc. to handle text and image in header or footer. In addtion, some special script commands are provided for you to display dynamic content like page number and current date, or to format the text to be showed.

Here is a list of scripts and their usage.  

Script	Description
&P	The current page number
&N	The total number of pages
&D	The current date
&T	The current time
&G	A picture
&A	The worksheet name
&F	The file name
&B	Make text bold
&I	Italicize text
&U	Underline text
&”font name”	Represents a font name, for example, &”Aril”
&font size	Represents font size, for example, &12
&K<HEX color>	Represents font color, for example, &KFF0000

Installation

Download Free Spire.XLS for Java pack, unzip it and you’ll get Spire.Xls.jar file from the “lib” folder. Import the jar file in your project as a dependency. If you are creating a Maven project, you can easily add the jar dependency by adding the following configurations to the pom.xml.

<repositories>
        <repository>
            <id>com.e-iceblue</id>
            <name>e-iceblue</name>
            <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
        </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId> e-iceblue </groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>2.2.0</version>
    </dependency>
</dependencies>

Example 1. Insert image and page number in header

It’s very commen that we add our company logo or another meanful image to a document at the header space. The following code snippets show you how to insert an image as well as page number in the Excel header.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;

public class InsertImagePageNumInHeader {

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

        //Create an object of Workbook class
        Workbook wb = new Workbook();

        //Get the first sheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Insert text in A1
        sheet.getCellRange("A1").setText("header example");

        //Load an image file from disk
        File file = new File("C:\\Users\\Administrator\\Desktop\\company_logo.png"); 
        BufferedImage bufferedImage = (BufferedImage) ImageIO.read(file);

        //Insert the image to left header
        sheet.getPageSetup().setLeftHeaderImage(bufferedImage);
        sheet.getPageSetup().setLeftHeader("&G");

        //Insert page number to center header
        sheet.getPageSetup().setCenterHeader("Page "+"&P");

        //Save the file
        wb.saveToFile("InsertImagePageNumInHeader.xlsx", ExcelVersion.Version2016);
    }
}

Example 2. Insert formatted text in footer

It is easy to insert plain text in header or footer by assigning values to header or footer. To change the look of the text, you can use the script commends listed in the table, mentioned above to format the text.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class InsertTextInFooter {

    public static void main(String[] args) {

        //Create an object of Workbook class
        Workbook wb = new Workbook();

        //Get the first sheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Format a string with script commands and assign it to CenterFooter
        sheet.getPageSetup().setCenterFooter("&\"Arial\"&B&12&KFF0000Copyright © 2020 xxx. All Rights Reserved.");

        //Save the file
        wb.saveToFile("InsertTextInFooter.xlsx", ExcelVersion.Version2016);
    }
}

Example 3. Set different header and footer for the first page

In some cases, the first page will be used as the cover of your printed document. As a result, you may want the header and footer of the fisrt page to be different.

import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class SetDifferentHeaderFooter {

    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();
        
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Insert text in A1 and J1
        sheet.getCellRange("A1").setText("page 1");
        sheet.getCellRange("J1").setText("page 2");

        //Set different first page
        sheet.getPageSetup().setDifferentFirst((byte)1);

        //Set the first header string and footer string
        sheet.getPageSetup().setFirstHeaderString("First header");
        sheet.getPageSetup().setFirstFooterString("First footer");

        //Set header string and footer string for other pages
        sheet.getPageSetup().setCenterHeader("Header of other pages");
        sheet.getPageSetup().setCenterFooter("Footer of other pages");

        //Save the document
        workbook.saveToFile("DifferentFirstPage.xlsx", FileFormat.Version2016);
    }
}