How to set a cell format to Text

user_1313855 0
Viewed 44788

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?

apache-poi excel java
5 Answers

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
    }
}
user_120265630
user_97036210
Related