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:
