如何在 JAVA 中使用 apache poi 在 Excel 中删除警告？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23488221/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to remove warning in Excel using apache poi in JAVA?
提问by Charvee Shah
I am using apache poi apito generate Excel sheet in my application in java. Data that are set in excel are coming dynamically with type string. For column1, values are alphanumeric. When I generate Excel, it will give me green indication with warning "Number Stored as Text"or "Text date with 2-digit year"on cell.
我正在使用apache poi apijava 在我的应用程序中生成 Excel 工作表。excel 中设置的数据以字符串类型动态传入。对于 column1，值为 alphanumeric。当我生成 Excel 时，它会给我绿色指示，并在单元格上显示警告“数字存储为文本”或“带有 2 位数年份的文本日期”。
I want to remove that warning. I found that from excel we can flag a cell as 'ignore error', to ignore the warning.
我想删除那个警告。我发现从 excel 中我们可以将单元格标记为“忽略错误”，以忽略警告。
How to do it programmatically or is there any other alternative to achieve this task ?
如何以编程方式完成此任务或是否有其他替代方法来完成此任务？
I also have attached screenshot that shows warning with green mark.
我还附上了用绿色标记显示警告的屏幕截图。
Code :
代码 ：
if (cellValue != null && (shouldBeRightAlign))
{
cellType = Cell.CELL_TYPE_NUMERIC;
}
else if (cellValue != null)
{
cellType = Cell.CELL_TYPE_STRING;
}
cell.setCellValue(cellValue);
回答by Vishrant
Set the cell typelike :
设置cell type像：
HSSFRow row = sheet.createRow(sheet.getLastRowNum());
HSSFCell cell = row.createCell(0);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
For alphanumeric and Charactercell type will be Cell.CELL_TYPE_STRINGfor number, cell type will be CELL_TYPE_NUMERIC
对于字母数字和Character单元格类型将是Cell.CELL_TYPE_STRING数字，单元格类型将是CELL_TYPE_NUMERIC
You can get more on cell type from Celland HSSFCelldocumentation.
回答by Peru
Old one But Still it This will help Some one from .NET who use NPOI
旧的但仍然如此 这将有助于使用 NPOI 的 .NET 中的一些人
I tried with
我试过
cell.SetCellType(NPOI.SS.UserModel.CellType.NUMERIC);
which didn't helped at all. but the below code will work
这根本没有帮助。但下面的代码将工作
dCell.setCellValue(Convert.ToDouble(112.45)); //.NET syntax
dCell.setCellValue(new Double("123")); // Java Syntax
回答by Shambhavi Sharma
I was facing the same issue. I resolved it through checking the data if it is an Integer/Float and putting the condition(CELL_TYPE) accordingly. Please find the code Snippet:
我面临着同样的问题。我通过检查数据是否是整数/浮点数并相应地放置条件（CELL_TYPE）来解决它。请找到代码片段：
if(!isInteger(data))
{
cell.setCellValue(data);
}
else
{
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Float.parseFloat(data));//.parseInt(data));
}
public static boolean isInteger(String s) {
try {
java.lang.Float.parseFloat(s);
} catch(NumberFormatException e) {
return false;
}
return true;
}
Here data is a String from the array of list from the data source(excel/csv) This resolved the green warning.
这里的数据是来自数据源（excel/csv）列表数组中的一个字符串，这解决了绿色警告。
回答by peterh
Apparently this is not currently possible with Apache POI. There are a number of bug reports / RFEs on the subject:
显然，Apache POI 目前无法做到这一点。有许多关于该主题的错误报告/RFE：
https://issues.apache.org/bugzilla/show_bug.cgi?id=58641
https://issues.apache.org/bugzilla/show_bug.cgi?id=58641
https://issues.apache.org/bugzilla/show_bug.cgi?id=54868
https://issues.apache.org/bugzilla/show_bug.cgi?id=54868
https://issues.apache.org/bugzilla/show_bug.cgi?id=46136
https://issues.apache.org/bugzilla/show_bug.cgi?id=46136
The actual attribute (the attribute that says to ignore such warning) exists in the Excel persistent format and is recorded on a per cell basis. It is just that Apache POI doesn't expose it, as far as I can tell from the bug reports.
实际属性（表示忽略此类警告的属性）存在于 Excel 持久格式中，并以每个单元格为基础进行记录。据我从错误报告中可以看出，只是 Apache POI 没有公开它。
To give you an idea of what this looks like in Excel's xlsx format:
为了让您了解 Excel 的 xlsx 格式的外观：
</worksheet>
...
<ignoredErrors>
<ignoredError sqref="K192 E181 E186" numberStoredAsText="1"/>
</ignoredErrors>
</worksheet>
I've read somewhere that this is persisted only in xlsx(i.e. OOXML), not in xlsformat which means that if and when the Apache POI team implements this it will be in the XSSF package.
我在某处读到这仅在xlsx（即 OOXML）中持久化，而不是在xls格式中，这意味着如果 Apache POI 团队实现它，它将在 XSSF 包中。
回答by Martin
The API now provides a corresponding method
public void addIgnoredErrors(CellRangeAddress region,
IgnoredErrorType... ignoredErrorTypes)
回答by lrnzcig
For me, none of the solutions above worked, but at the end got rid of the warnings with a bit convoluted workaround: writting the values as formulas like this.
对我来说，上面的解决方案都没有奏效，但最后通过一些令人费解的解决方法摆脱了警告：将值写成这样的公式。
String asFormula = "\"" + value + "\"";
cell.setCellType(SXSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(asFormula);
Don't think it is always a good solution, but in my case the values of the output file are never modified. But it might help somebody.
不要认为它总是一个好的解决方案，但在我的情况下，输出文件的值永远不会被修改。但它可能会帮助某人。
I'm using version 3.14 of apache POI, in case it makes a difference (I've tried a few different versions before the workaround, with no effect).
我正在使用 apache POI 的 3.14 版，以防万一（我在解决方法之前尝试了几个不同的版本，但没有效果）。
回答by developer
I know it has been a long time since the question was asked, but still... Did you try this:
我知道这个问题已经很久了，但仍然......你有没有试过这个：
DataFormat df = workbook.createDataFormat();
cellStyle.setDataFormat(df.getFormat("@"));
回答by user13275692
Old but still relevant.
旧但仍然相关。
You should use "." in text as decimal separator and to format cell as NUMERIC.
你应该使用“。” 在文本中作为十进制分隔符并将单元格格式化为数字。
Use also DataFormat with de pattern "#.00" and voila! No warning and everything gones fine.
还使用带有模式“#.00”的数据格式，瞧！没有警告，一切顺利。
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double.parseDouble(data));
DataFormat format = hwb.createDataFormat();
CellStyle numbercellStyle = hwb.createCellStyle();
numbercellStyle.setDataFormat(format.getFormat("#.00"));
cell.setCellStyle(numbercellStyle);
