使用Apache POI写入数据时，单元格格式以删除excel单元格中的小数点 [英] Cell formatting to remove decimal point in excel cell when data Writing Using Apache POI

问题描述

我正在使用 Apache POI 将 Excel 函数写入单元格并评估该函数.我需要做的是从单元格中删除所有小数点.目前它在每个单元格值的末尾获得不必要的两个零.单元格格式如下.但最后总是得到两个零.

I'm using Apache POI to Write a Excel function into a cell and evaluate the function. What i need to do is to remove all decimal points from the cell.Currently it getting unnecessary two Zeros at the end of each cell values.The cell formatting as follows. But it is always getting two zeros at the end.

     XSSFCellStyle cellStyle = cell.getCellStyle();
     DataFormat df = workbook.createDataFormat();
     cellStyle.setDataFormat(df.getFormat("###,##0"));
     cell.setCellStyle(cellStyle);


     if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
          evaluator.evaluateFormulaCell(cell);
      }

我参考了以下网址和其他几个网站，但找不到修复错误的方法.

I have refered following URLs and several other sites and couldn't find a way to fix the bug.

1. link1
2. link2

注意我使用的是 Apache POI 3.14 版和 Spring 4.3.1.RELEASE.

NOTE I'm using Apache POI version 3.14 with Spring 4.3.1.RELEASE.

推荐答案

尝试为工作簿创建单元格样式并将此样式与 "#,##0" 数字格式应用于所有列中的单元格:

Try to create the cell style for the workbook and apply this style with "#,##0" number format for all the cells in the column:

CellStyle style = workbook.createCellStyle();
DataFormat format = workbook.createDataFormat();
style.setDataFormat(format.getFormat("#,##0"));
cell.setCellStyle(style);

从工作簿创建样式是最佳方式，不会超载内存.

Creating the style from the workbook is the optimal way and does not overload memory.

您还可以调整公式以将数字四舍五入至小数点后 0 位:

You can also adjust your formula to round the number with 0 decimal places:

=ROUND(A1,0)

这篇关于使用Apache POI写入数据时，单元格格式以删除excel单元格中的小数点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！