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
Top comments (0)