How to set a cell format to Text

Question

I am using Apache-POI 3.14. I have a need to lock-down a cell to a "Text" format. The data in my cell might be all digits, but it is still considered a string. When I write the cell, I do it like this:

cell.setCellValue("001");
cell.setCellType(Cell.CELL_TYPE_STRING);

When I open the output workbook in Excel, the cell contains the correct value ("001") and it displays with a small green triangle in the corner. Hovering over the exclamation point displays the hover text The number in this cell is formatted as text or preceded by an apostrophe. When I look at the cell formatting (Right-click -> Format cells), the "Category" is displayed as "General". I expected this to be "Text".

The problem arises when a user modifies the value in the cell by entering only digits. Because the "Category" is "General", the value is entered and displayed as a number, removing leading zeroes and right-justified.

How can I achieve the same result as Excel's "Format cells" dialog?

Answer 1

For HSSF,

    DataFormat fmt = workbook.createDataFormat();
    CellStyle textStyle = workbook.createCellStyle();
    textStyle.setDataFormat(fmt.getFormat("@"));
    sheet.setDefaultColumnStyle(0, textStyle);

It just sets the whole column style as Text and set category as Text .

However, if you are using XSSF format, it doesn't work(I am using Apache Poi 3.15 and didn't work for me). In this case you have set style to each cell you want to treat as text in addition to above code using:

cell.setCellStyle(textStyle);

Regarding error, you could use

sheet.addIgnoredErrors(new CellRangeAddress(0,9999,0,9999),IgnoredErrorType.NUMBER_STORED_AS_TEXT );

It ignores the NUMBER_STORED_AS_TEXT error for row 0 till 9999 and column 0 till 9999 and you wont see it.

Answer 2

You can try to set the cell-format to text via

DataFormat fmt = wb.createDataFormat();
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(
    fmt.getFormat("@"));
cell.setCellStyle(cellStyle);

Note: CellStyles shoudl be re-used for all applicable cells, do not create new ones for every cell.

You could also try to use the "Ignore errors" feature in the .xlsx format, however support for it is not fully done yet, see Issue 46136 and Issue 58641 for some ongoing discussion.

See also this MSDN page for some additional information