Java: Set Number Formats in Excel Worksheets

Formatting numbers in Excel cells is a critical step when working with spreadsheets, especially in professional or data-driven environments. Proper number formatting ensures that data is presented clearly, consistently, and in a way that aligns with its purpose—whether it's financial data, percentages, dates, or scientific values. When automating Excel tasks using Java, applying the correct number format programmatically can save time, reduce errors, and enhance the readability of reports or dashboards. This article explores how to use Spire.XLS for Java to set number formats in Excel cells, enabling you to create polished and well-structured spreadsheets with ease.

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's 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>15.1.3</version>
    </dependency>
</dependencies>
    

How to Set Number Formats in Excel with Java

Spire.XLS for Java provides the CellRange.setNumberFormat() method, enabling developers to set number formats for cells using Excel's number format codes. The table below highlights commonly used symbols in Excel number format codes and their functions:

Symbols Description
0 and # 0 forces display of digit places, padding with zeros if necessary; # shows digits only when needed.
? Placeholder for aligning numbers, leaves space but does not display anything if not used.
, and . , serves as a thousands separator and can also indicate division by 1000; . is the decimal point.
% Multiplies the number by 100 and adds a percent sign.
E+ / E- Scientific notation, for positive and negative exponents respectively.
Currency ($, €, ¥, etc.) Displays the respective currency symbol.
[Color] Sets text color (e.g., [Red], [Blue]).
@ Text placeholder, used to represent text in custom formats.
Date/Time (yyyy, mmmm, mm, dd, hh, ss, AM/PM) Represent year, full month name, month, day, hour, minute, second, and 12-hour clock markers respectively.

The detailed steps for setting number formats of Excel cells with Java are as follows:

  • Create a Workbook object to create a new Excel workbook.
  • Get the first default worksheet using the Workbook.getWorksheets().get() method.
  • Add values using the CellRange.setValue() method or add numeric values using the CellRange.setNumberValue() method.
  • Set the number formats using the CellRange.setNumberFormat() method.
  • Save the workbook using the Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

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

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

        // Add a title
        sheet.getCellRange("B1").setText("NUMBER FORMATTING");
        sheet.getCellRange("B1").getCellStyle().getExcelFont().isBold(true);
        sheet.getCellRange("B1:C1").merge(); // Merge cells B1 and C1
        sheet.getCellRange("B1:C1").setHorizontalAlignment(HorizontalAlignType.Center); // Center align the text

        // Add number format examples and corresponding values
        // Add positive number formats
        addNumberFormatExample(sheet, "B3", "C3", "0", "1234.5678");
        addNumberFormatExample(sheet, "B4", "C4", "0.00", "1234.5678");
        addNumberFormatExample(sheet, "B5", "C5", "#,##0.00", "1234.5678");
        addNumberFormatExample(sheet, "B6", "C6", "$#,##0.00", "1234.5678");

        // Add negative number formats
        addNumberFormatExample(sheet, "B7", "C7", "0;[Red]-0", "-1234.5678");
        addNumberFormatExample(sheet, "B8", "C8", "0.00;[Red]-0.00", "-1234.5678");

        // Add scientific notation and percentage formats
        addNumberFormatExample(sheet, "B9", "C9", "0.00E+00", "1234.5678");
        addNumberFormatExample(sheet, "B10", "C10", "0.00%", "0.5678");

        // Add date and time formats
        addNumberFormatExample(sheet, "B11", "C11", "yyyy-MM-dd", "44930.0"); // Excel date value for 2023-01-01
        addNumberFormatExample(sheet, "B12", "C12", "HH:mm:ss", "0.75"); // Excel time value for 18:00:00

        // Add text format
        addNumberFormatExample(sheet, "B13", "C13", "@", "Text Example");

        // Set the formatting
        sheet.getCellRange("B3:B13").getCellStyle().setKnownColor(ExcelColors.Gray25Percent);
        sheet.getCellRange("C3:C13").getCellStyle().setKnownColor(ExcelColors.Gray50Percent);
        sheet.setColumnWidth(2, 24); // Column B
        sheet.setColumnWidth(3, 24); // Column C

        // Save the workbook to a file
        workbook.saveToFile("output/SetExcelNumberFormat.xlsx", FileFormat.Version2016);
        workbook.dispose();
    }

        /**
        * Adds a number format example to the specified cells in the worksheet.
        *
        * @param sheet        The worksheet to modify.
        * @param textCell     The cell for displaying the number format string.
        * @param valueCell    The cell for displaying the formatted value.
        * @param format       The number format code.
        * @param value        The numeric value to format.
        */
        private static void addNumberFormatExample(Worksheet sheet, String textCell, String valueCell, String format, String value) {
            sheet.getCellRange(textCell).setText(format); // Display the number format code
            sheet.getCellRange(valueCell).setValue(value); // Add the value
            // sheet.getCellRange(valueCell).setNumberValue(Double); //  Or set numeric value with setNumberValue() method
            sheet.getCellRange(valueCell).setNumberFormat(format); // Apply the number format
    }
}

How to Set Number Formats in Excel with Java

Add Values in Specified Number Formats to Excel Cells with Java

Spire.XLS for Java also supports directly adding data with specific number formats to Excel cells with methods under the CellRange class. The following table outlines the methods for adding data with common number formats to cells and their corresponding data types:

Method Description
setText(String text) Sets a text value in a cell or range of cells.
setNumberValue(double numberValue) Sets a numeric value in a cell or range of cells.
setBooleanValue(boolean booleanValue) Sets a boolean value (true/false) in a cell or range of cells.
setDateTimeValue(java.util.Date dateTime) Sets a date and time value in a cell or range of cells.
setHtmlString(String htmlCode) Sets an HTML-formatted string in a cell or range of cells.

The detailed steps for adding values with number formats to Excel cells are as follows:

  • Create an instance of the Workbook class.
  • Get the first worksheet using the Workbook.getWorksheets().get() method.
  • Get a cell or cell range using the Worksheet.getCellRange() method.
  • Add values in specific number formats using the methods under the CellRange class.
  • Set the cell styles as needed.
  • Save the workbook using the Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

import java.util.Calendar;
import java.util.Date;

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

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

        // Add a text value
        sheet.getCellRange("C3").setText("Example Text");
        // Add number value
        sheet.getCellRange("C4").setNumberValue(1234.5678);
        // Add boolean value
        sheet.getCellRange("C5").setBooleanValue(true);
        // Add date time value
        sheet.getCellRange("C6").setDateTimeValue(new Date(2024, Calendar.DECEMBER, 12));
        // Add HTML string
        sheet.getCellRange("C7").setHtmlString("Bold Text");

        // Format the cells
        sheet.getCellRange("C3:C7").setHorizontalAlignment(HorizontalAlignType.Center);
        sheet.getCellRange("C3:C7").setVerticalAlignment(VerticalAlignType.Center);
        sheet.getCellRange("C3:C7").getCellStyle().getExcelFont().setSize(14);
        for (int i = 3; i <= 7; i++) {
            sheet.autoFitColumn(i);
        }

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

Add Values in Specified Number Formats to Excel Cells with Java

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.