1. Overview

1.概述

In this quick tutorial, we’ll demonstrate how to format numeric cells in Excel using Apache POI.

在这个快速教程中，我们将演示如何使用Apache POI在Excel中格式化数字单元格。

2. Apache POI

2.阿帕奇POI

Apache POI is an open-source pure Java project. It provides libraries for reading and writing files in Microsoft Office formats, such as Word, PowerPoint, and Excel.

Apache POI是一个开源的纯Java项目。它提供了用于读写Microsoft Office格式文件的库，如Word、PowerPoint和Excel>。

While working with the newer .xlsx file format, we would use the XSSFWorkbook class, and for the .xls format, we use the HSSFWorkbook class.

当使用较新的.xlsx文件格式时，我们将使用XSSFWorkbook类，而对于.xls格式，我们使用HSSFWorkbook类.。

3. Numeric Format

3.数值格式

The setCellValue method of Apache POI accepts only double as an input or an input that may be implicitly cast to double and returns a double as a numeric value. The setCellStyle method is used to add the required style. A # character in Excel Number formatting means placing a digit here if required, whereas a character 0 implies always placing a digit here, even if it’s an unnecessary 0.

Apache POI的setCellValue方法只接受double作为输入，或者可以隐含地投向double的输入，并返回double作为数值。setCellStyle方法用于添加所需的样式。在Excel数字格式中的#字符意味着如果需要的话在这里放置一个数字，而0字符则意味着总是在这里放置一个数字，即使是一个不必要的0。

3.1. Format for Display Value Only

3.1.仅用于显示值的格式

Let’s format a double value using patterns like 0.00 or #.##. First, we’ll create a simple utility method for formatting the cell value:

让我们用0.00或#.##这样的模式来格式化一个double值。首先，我们将创建一个简单的实用方法来格式化单元格值。

public static void applyNumericFormat(Workbook outWorkbook, Row row, Cell cell, Double value, String styleFormat) {
    CellStyle style = outWorkbook.createCellStyle();
    DataFormat format = outWorkbook.createDataFormat();
    style.setDataFormat(format.getFormat(styleFormat));
    cell.setCellValue(value);
    cell.setCellStyle(style);
}

Let’s validate a simple code to validate the mentioned approach:

让我们验证一个简单的代码来验证上述方法。

File file = new File("number_test.xlsx");
try (Workbook outWorkbook = new XSSFWorkbook()) {
    Sheet sheet = outWorkbook.createSheet("Numeric Sheet");
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    ExcelNumericFormat.applyNumericFormat(outWorkbook, row, cell, 10.251, "0.00");
    FileOutputStream fileOut = new FileOutputStream(file);
    outWorkbook.write(fileOut);
    fileOut.close();
}

This will add the numeric cell in the spreadsheet:

这将在电子表格中添加数字单元。

Note: The display value is the formatted value, but the actual value remains the same. If we try to access the same cell, we will still get 10.251.

注意：显示值是格式化后的值，但实际值保持不变。如果我们试图访问同一个单元格，我们仍然会得到10.251。

Let’s validate the actual value:

让我们验证一下实际值。

try (Workbook inWorkbook = new XSSFWorkbook("number_test.xlsx")) {
    Sheet sheet = inWorkbook.cloneSheet(0);
    Row row = sheet.getRow(0);
    Assertions.assertEquals(10.251, row.getCell(0).getNumericCellValue());
}

3.2. Format for Both Actual and Display Value

3.2.实际值和显示值的格式

Let’s format display and actual values using the patterns:

让我们用模式来格式化显示和实际值。

File file = new File("number_test.xlsx");
try (Workbook outWorkbook = new HSSFWorkbook()) {
    Sheet sheet = outWorkbook.createSheet("Numeric Sheet");
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    DecimalFormat df = new DecimalFormat("#,###.##");
    ExcelNumericFormat.applyNumericFormat(outWorkbook, row, cell, Double.valueOf(df.format(10.251)), "#,###.##");
    FileOutputStream fileOut = new FileOutputStream(file);
    outWorkbook.write(fileOut);
    fileOut.close();
}

This will add the numeric cell in the spreadsheet and display the formatted value, and also, it changes the actual value:

这将在电子表格中添加数字单元，并显示格式化的数值，同时，它也会改变实际数值。

Let’s assert the actual value in the above case:

让我们来断言上述情况下的实际价值。

try (Workbook inWorkbook = new XSSFWorkbook("number_test.xlsx")) {
    Sheet sheet = inWorkbook.cloneSheet(0);
    Row row = sheet.getRow(0);
    Assertions.assertEquals(10.25, row.getCell(0).getNumericCellValue());
}

4. Conclusion

4.总结

In this article, we’ve demonstrated how to display formatted values with or without changing the actual values of numeric cells in an Excel sheet.

在这篇文章中，我们演示了如何在改变Excel表格中数字单元格的实际数值的情况下显示格式化的数值。

All the code samples used in this tutorial are available over on GitHub.

本教程中使用的所有代码样本都可以在GitHub上找到。