Set Number Format in Excel using Java

In Excel, number formats can control the display of numbers, dates, times, decimals, percentages, phone numbers, etc. For example, you can use format string '0.00' to format 1234.5678 as 1234.57. Numeric format strings often consist of one or more custom numeric specifiers listed as below:

● "#" - Digit placeholder
● "0" - Zero placeholder
● "," - Decimal point
● "." - Decimal separator
● "[Red]" - Color specifier
● "%" - Percentage placeholder

The following example will introduce how to set number format in Excel using Free Spire.XLS for Java.

1# Installation
Method 1: Download the free API and unzip it, then add the Spire.Xls.jar file to your project as dependency.

Method 2: You can also add the jar dependency to maven project 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>3.9.1</version>
    </dependency>
</dependencies>

2# The relevant code snippet

import com.spire.xls.*;

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

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

        //Add a string to cell “B2” & “C2”
        sheet.getCellRange("B2").setText("Number format");
        sheet.getCellRange("B2").getCellStyle().getExcelFont().isBold(true);
        sheet.getCellRange("C2").setText("Output");
        sheet.getCellRange("C2").getCellStyle().getExcelFont().isBold(true);


        //Add a string to cell “B3”
        sheet.getCellRange("B3").setText("0");
        //Add a number to cell “C3” and set the number format
        sheet.getCellRange("C3").setNumberValue(1234.5678);
        sheet.getCellRange("C3").setNumberFormat("0");

        //Repeat the above step to add string and number to other cells and set the number format
        sheet.getCellRange("B4").setText("0.00");
        sheet.getCellRange("C4").setNumberValue(1234.5678);
        sheet.getCellRange("C4").setNumberFormat("0.00");

        sheet.getCellRange("B5").setText("#,##0.00");
        sheet.getCellRange("C5").setNumberValue(1234.5678);
        sheet.getCellRange("C5").setNumberFormat("#,##0.00");

        sheet.getCellRange("B6").setText("$#,##0.00");
        sheet.getCellRange("C6").setNumberValue(1234.5678);
        sheet.getCellRange("C6").setNumberFormat("$#,##0.00");

        sheet.getCellRange("B7").setText("0;[Red]-0");
        sheet.getCellRange("C7").setNumberValue(-1234.5678);
        sheet.getCellRange("C7").setNumberFormat("0;[Red]-0");

        sheet.getCellRange("B8").setText("0.00;[Red]-0.00");
        sheet.getCellRange("C8").setNumberValue(-1234.5678);
        sheet.getCellRange("C8").setNumberFormat("0.00;[Red]-0.00");

        sheet.getCellRange("B9").setText("#,##0;[Red]-#,##0");
        sheet.getCellRange("C9").setNumberValue(-1234.5678);
        sheet.getCellRange("C9").setNumberFormat("#,##0;[Red]-#,##0");

        sheet.getCellRange("B10").setText("#,##0.00;[Red]-#,##0.000");
        sheet.getCellRange("C10").setNumberValue(-1234.5678);
        sheet.getCellRange("C10").setNumberFormat("#,##0.00;[Red]-#,##0.00");

        sheet.getCellRange("B11").setText("0.00E+00");
        sheet.getCellRange("C11").setNumberValue(1234.5678);
        sheet.getCellRange("C11").setNumberFormat("0.00E+00");

        sheet.getCellRange("B12").setText("0.00%");
        sheet.getCellRange("C12").setNumberValue(1234.5678);
        sheet.getCellRange("C12").setNumberFormat("0.00%");

        //Set background color for specified cells
        sheet.getCellRange("B2:C2").getCellStyle().setKnownColor(ExcelColors.PaleBlue);
        sheet.getCellRange("B3:B12").getCellStyle().setKnownColor(ExcelColors.LightGreen1);
        sheet.getCellRange("C3:C12").getCellStyle().setKnownColor(ExcelColors.SeaGreen);

        //Set column width for specified columns
        sheet.setColumnWidth(2, 24);
        sheet.setColumnWidth(3, 24);

        //Save the result file
        workbook.saveToFile("SetNumberFormat.xlsx", ExcelVersion.Version2013);
    }
}

3# The generated Excel document