Set Number Format in Excel in Java

Number formatting is a way to control how a number is displayed using numeric format string. 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 demonstrates how to set number format in Excel using Spire.XLS for Java.

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 “B1”
        sheet.getCellRange("B1").setText("NUMBER FORMATTING");
        sheet.getCellRange("B1").getCellStyle().getExcelFont().isBold(true);
        sheet.getCellRange("B1:C1").merge();
        sheet.getCellRange("B1:C1").setHorizontalAlignment(HorizontalAlignType.Center);

        
        //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("B3:B12").getCellStyle().setKnownColor(ExcelColors.Gray25Percent);
        sheet.getCellRange("C3:C12").getCellStyle().setKnownColor(ExcelColors.Gray50Percent);

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

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

The following screenshot shows the output Excel files after setting number format:

Set Number Format in Excel in Java