如何使用JAVA中的apache poi在Excel中删除警告?

I am using apache poi api to 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 api在我的java应用程序中生成Excel表。在excel中设置的数据是使用类型string动态生成的。对于column1，值是字母数字。当我生成Excel时，它会给我一个绿色的指示，提示我“以文本形式存储的数字”或“以两位数年为单位的文本日期”。

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);

6 个解决方案

#1

The API now provides a corresponding method

API现在提供了相应的方法

public void addIgnoredErrors(CellRangeAddress region,
                    IgnoredErrorType... ignoredErrorTypes)

#2

Apparently this is not currently possible with Apache POI. There are a number of bug reports / RFEs on the subject:

显然，这在Apache POI中是不可能实现的。有许多关于这个主题的错误报告/ RFEs:

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没有公开它，就我从bug报告中可以看出这一点。

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 xls format 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包中。

#3

Set the cell type like :

设置单元格类型如下:

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 Character cell type will be Cell.CELL_TYPE_STRING for number, cell type will be CELL_TYPE_NUMERIC

对于字母数字和字符，单元格类型是单元格。CELL_TYPE_STRING为number, cell类型为CELL_TYPE_NUMERIC。

You can get more on cell type from Cell and HSSFCell documentation.

您可以从单元格和HSSFCell文档中获得关于单元格类型的更多信息。

#4

Old one But Still it This will help Some one from .NET who use NPOI

旧的，但这仍然会帮助。net的一些用户使用NPOI

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

#5

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)列表数组的字符串，它解决了绿色警告。

#6

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版本，以备有所不同(在解决方案之前，我尝试了几个不同的版本，但没有效果)。