How to Add Visual Formatting to Excel Sheets with Java

Visual formatting makes Excel reports easier to read and more professional in appearance. Applying background colors and images to worksheets can highlight important sections, distinguish between different sheets, and reinforce branding in distributed reports.

This article demonstrates how to set background colors and background images in Excel worksheets programmatically using Spire.XLS for Java.

Project Setup

To use Spire.XLS for Java in a Maven project, add the following repository and dependency to your pom.xml file:

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

Refresh your project to download the required JAR files. The library requires Java 8 or later. A free version is available with a limit on the number of worksheets per workbook; a paid license removes this restriction.

Applying a Background Color to an Entire Worksheet

Setting a solid background color for a worksheet is a straightforward way to make it visually distinct. The library provides the getPageSetup().setBackgoundColor() method for this purpose. The following example creates a workbook with two sheets, each with a different background color:

import com.spire.xls.*;
import java.awt.*;

public class WorksheetBackground {
    public static void main(String[] args) {
        // Create a new workbook
        Workbook workbook = new Workbook();

        // Access the first sheet and rename it
        Worksheet sheet1 = workbook.getWorksheets().get(0);
        sheet1.setName("GreenSheet");
        // Set background color to light green using RGB values
        sheet1.getPageSetup().setBackgoundColor(new Color(144, 238, 144));

        // Add a second sheet with a light blue background
        Worksheet sheet2 = workbook.getWorksheets().add("BlueSheet");
        sheet2.getPageSetup().setBackgoundColor(new Color(173, 216, 230));

        // Add sample data to both sheets
        sheet1.getCellRange("A1").setValue("This sheet has a green background");
        sheet2.getCellRange("A1").setValue("This sheet has a blue background");

        // Save the workbook
        workbook.saveToFile("output/ColoredSheets.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

The Color class from java.awt is used to specify RGB color values. The background color is visible behind all cell content and persists when the file is opened in Microsoft Excel or other compatible applications.

Setting Background Colors for Specific Cell Ranges

In addition to full-sheet backgrounds, you can apply fill colors to specific cell ranges. This is done through cell style properties on a CellRange object, which is useful for formatting headers, highlighting rows, or color-coding data:

import com.spire.xls.*;
import java.awt.*;

public class CellRangeBackground {
    public static void main(String[] args) {
        // Create a new workbook
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.getWorksheets().get(0);

        // Populate a simple data table
        sheet.getCellRange("A1").setValue("Product");
        sheet.getCellRange("B1").setValue("Sales");
        sheet.getCellRange("C1").setValue("Status");

        sheet.getCellRange("A2").setValue("Widget A");
        sheet.getCellRange("B2").setValue("$1,200");
        sheet.getCellRange("C2").setValue("Active");

        sheet.getCellRange("A3").setValue("Widget B");
        sheet.getCellRange("B3").setValue("$800");
        sheet.getCellRange("C3").setValue("Discontinued");

        // Apply dark blue background to the header row
        CellRange headerRange = sheet.getCellRange("A1:C1");
        headerRange.getCellStyle().setColor(new Color(0, 51, 102));
        // Set header font to white for contrast
        headerRange.getCellStyle().getFont().setColor(new Color(255, 255, 255));
        headerRange.getCellStyle().getFont().isBold(true);

        // Highlight the discontinued product row in light red
        sheet.getCellRange("A3:C3").getCellStyle().setColor(new Color(255, 204, 203));

        // Center-align all data cells
        sheet.getCellRange("A1:C3").setHorizontalAlignment(HorizontalAlignType.Center);

        // Auto-fit column widths
        sheet.autoFitColumn(1);
        sheet.autoFitColumn(2);
        sheet.autoFitColumn(3);

        // Save the workbook
        workbook.saveToFile("output/CellRangeColors.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

The getCellStyle().setColor() method changes the fill color of the specified cells. Combining this with font styling and alignment settings produces structured, readable data tables with clear visual hierarchy.

Adding a Background Image to a Worksheet

Beyond solid colors, an image can be set as the worksheet background. This is useful for adding watermarks, company logos, or subtle decorative patterns. The method getPageSetup().setBackgoundImage() accepts either a file path string or a BufferedImage object. The image tiles across the sheet to cover the entire background:

import com.spire.xls.*;
import java.io.File;

public class ImageBackground {
    public static void main(String[] args) {
        // Create a new workbook
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.getWorksheets().get(0);

        // Add sample data
        sheet.getCellRange("A1").setValue("This worksheet has a background image");
        sheet.getCellRange("B2").setValue("The image tiles across the entire sheet");

        // Load and set a background image
        File imageFile = new File("background.png");
        if (imageFile.exists()) {
            sheet.getPageSetup().setBackgoundImage(imageFile.getAbsolutePath());
        } else {
            System.out.println("Image file not found. Skipping background image.");
        }

        // Save the workbook
        workbook.saveToFile("output/ImageBackground.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

Using a BufferedImage object instead of a file path:

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;

BufferedImage image = ImageIO.read(new File("background.png"));
sheet.getPageSetup().setBackgoundImage(image);

Key points about worksheet background images:

• The image tiles both horizontally and vertically to fill the entire sheet area, similar to a repeating wallpaper pattern.
• Background images are for on-screen display only. They do not appear in printouts, which is standard Excel behavior across all versions and libraries.
• Supported formats include PNG, JPEG, and BMP. For watermark effects, a semi-transparent PNG can be used.

Applying Different Backgrounds to Multiple Sheets

When a workbook contains several sheets, applying distinct formatting to each one can help users navigate. The following example combines color and image backgrounds across three sheets:

import com.spire.xls.*;
import java.awt.*;
import java.io.File;

public class MultiSheetBackgrounds {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();

        // Cover sheet: light blue solid color
        Worksheet sheet1 = workbook.getWorksheets().get(0);
        sheet1.setName("Cover Sheet");
        sheet1.getCellRange("A1").setValue("Quarterly Report");
        sheet1.getPageSetup().setBackgoundColor(new Color(220, 230, 241));

        // Data sheet: background image (if available)
        Worksheet sheet2 = workbook.getWorksheets().add("Data Sheet");
        sheet2.getCellRange("A1").setValue("Sales Data");
        File bgImage = new File("background.png");
        if (bgImage.exists()) {
            sheet2.getPageSetup().setBackgoundImage(bgImage.getAbsolutePath());
        }

        // Summary sheet: warm beige solid color
        Worksheet sheet3 = workbook.getWorksheets().add("Summary");
        sheet3.getCellRange("A1").setValue("Summary Statistics");
        sheet3.getPageSetup().setBackgoundColor(new Color(255, 250, 240));

        workbook.saveToFile("output/MultiSheetBackgrounds.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

This approach is helpful in reports where different sheets have different roles—cover pages, detailed data, and summary views—and visual distinction aids navigation.

Practical Considerations

When applying backgrounds in Excel, several points are worth keeping in mind:

• Printing behavior: Excel does not print worksheet background images. If a printed background is required, consider inserting an image into the header, footer, or directly into cells instead.
• File size impact: Large background images increase the overall workbook file size. Use appropriately sized and compressed images.
• Readability: Ensure sufficient contrast between background colors and cell text. Dark backgrounds need light font colors, and light backgrounds work best with dark text.
• Performance: Applying background colors and images is a lightweight operation and has minimal impact on processing time, even for larger workbooks.

Alternative Libraries

Several other Java libraries support Excel formatting, each with different capabilities for backgrounds:

Library	License	Notes
Apache POI	Apache 2.0 (open source)	Supports cell fill colors through its style API; worksheet background images require direct manipulation of the underlying XML
Aspose.Cells	Commercial	Provides dedicated methods for both sheet backgrounds and cell formatting
JExcelApi	LGPL (open source)	Limited to the older .xls format; includes basic color support but no background image functionality

Apache POI handles cell fill colors well, but setting a worksheet background image involves working with the low-level sheet XML structure. Aspose.Cells offers comparable features under a commercial license. JExcelApi is constrained by its support only for legacy Excel formats and has fewer formatting options.

Conclusion

Setting background colors and images in Excel worksheets through Spire.XLS for Java involves two main methods: getPageSetup().setBackgoundColor() for solid color fills across an entire sheet, and getPageSetup().setBackgoundImage() for tiled image backgrounds. For more granular formatting, getCellStyle().setColor() applies fill colors to specific cell ranges such as headers and data rows. These techniques can be combined across multiple sheets within a single workbook to create visually organized documents that improve readability and provide clear visual cues for different data sections. When implementing these formatting features, developers should consider the standard Excel behavior of not printing background images, the effect of large image files on workbook size, and the importance of maintaining adequate contrast between backgrounds and text. For additional customization, the library also supports conditional formatting rules, direct image insertion into cells, and chart generation with custom color schemes.