How to remove warning in Excel using apache poi in JAVA?

Question

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.

I want to remove that warning. I found that from excel we can flag a cell as 'ignore error', to ignore the warning.

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

Answer 1

The API now provides a corresponding method

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

Answer 2

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

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=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.

To give you an idea of what this looks like in Excel's xlsx format:

</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.

Answer 3

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