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

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

Answer 2

Look like OP was asking for Apache solution. After some searching I found this answer:

HSSFCellStyle style = book.createCellStyle();
style.setDataFormat(BuiltInFormats.getBuiltInFormat("text"));

Answer 3

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 4

In this case, I'm using Apache-POI 3.15, and I had the same problem, so I validated the data in my style, I need numbers >0 and strings:

try {
    if (Integer.parseInt(field + "") >= 0) {
        int valor = Integer.parseInt(field + "");
        cell.setCellValue(valor); //Int
    }
} catch (NumberFormatException nfe) {
    // no int
    try {
        if (Double.parseDouble(field + "") >= 0) {
            double valor = Double.parseDouble(field + ""); //double
            cell.setCellValue(valor);
        }
    } catch (NumberFormatException nfe2) {
        cell.setCellValue(field + ""); //String
    }
}

Answer 5

For Apache POI 4.0.1 :

XSSFSheet sheet = workbook.createSheet("MySheetName");
sheet.addIgnoredErrors(new CellRangeAddress(0, 9999, 0, 9999), IgnoredErrorType.NUMBER_STORED_AS_TEXT);

Be careful to cast your sheet to org.apache.poi.xssf.usermodel.XSSFSheet and not to org.apache.poi.ss.usermodel.Sheet, otherwise the method addIgnoredErrors wil be unknown.