JExcel/POI: Creating a cell as type Text

Question

I have a requirement that involves reading values from an excel spreadsheet, and populating a spreadsheet for users to modify and re-upload to our application. One of these cells contains a text string of 5 characters that may be letters, numbers, or a combination of both. Some of these strings contain only numbers, and begin with a zero. Because of this, the cell type is Text; however, when I use Apache POI or JExcel to populate a spreadsheet for the users to modify it is always set as cell type General.

Is there a way using either of these libraries, or some other excel api that I have not seen yet, to specify that a cell have type Text?

With POI, are you setting the cell contents with a String or with a Number? — Gagravarr, May 23 '12 at 09:21
I've tried to do it with a normal Java string, and with POI's CreationHelper.createRichTextString methods. I then set the cell type to CELL_TYPE_STRING. — user1411138, May 23 '12 at 12:43
Both of those should work fine, and will tell excel to render the string as-is. (Some versions of excel may give a warning about numbers in a text cell though). What isn't working when you do this? — Gagravarr, May 23 '12 at 13:41
The format of the cell is not set as text, and is instead set as General. When I write the string out it is displayed correctly, but the user will be modifying the sheet. A possibility exists that the user might want to change a value to something along the lines of 06524, where the 0 is actually significant to the string. When POI and JExcel create general type cells this leading 0 will be trimmed. Of course the user could right-click and modify the cell format back to text, but I'd rather not put that burden on them if possible. — user1411138, May 23 '12 at 13:55
Isn't that always the case with Excel though? If you really needed to, could you not set the cell as a number, with a format string applied to zero pad it to the appropriate size? — Gagravarr, May 23 '12 at 15:16
I can't use a Number cell, because the data could contain letters as well as numbers. Also, in Excel 2007, if you right click on a cell and select cell format the very first tab displays a list of cell types. General, Date, Number, Text, etc. If the format is set to Text it will do exactly what I want. The issue is that I can't figure out a way to have POI or JExcel set this value, the cell is always written as a General cell. — user1411138, May 23 '12 at 15:37
If the cell format is forced like this in POI: style.setDataFormat(BuiltInFormats.getBuiltInFormat("text")); Excel keeps the cell as text even when user touches it (ie. it stops the insane format autorecognition). — Mr. Napik, Oct 15 '14 at 16:34

score 8 · Answer 1 · answered May 23 '12 at 16:54

8

My co-worker just found a way to accomplish this. In JExcel, it can be accomplished by using a WritableCellFormat such as:

WritableCellFormat numberAsTextFormat = new WritableCellFormat(NumberFormats.TEXT);

Then, when you are creating your cell to add to a sheet you just pass in the format as normal:

Label l = new Label(0, 0, stringVal, numberAsTextFormat);

If you are using Apache POI, you would create a HSSFCellStyle, and then set it's data format like this:

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

answered May 23 '12 at 16:54

user1411138

121
1
4

1

This is the line that matters: style.setDataFormat(BuiltInFormats.getBuiltInFormat("text")); and forces Excel to keep the cell format as text even when user touches it - cell.setCellType(Cell.CELL_TYPE_STRING) is not enough. – Mr. Napik Oct 15 '14 at 16:30
Upvotes for the Apache POI information. It was exactly what I needed. Note: minor typo in the above code. In Apache POI (version 3.11) the syntax is: BuiltinFormats.getBuiltinFormat – Phil Apr 24 '15 at 15:18
Thanks 1000% for the POI pointer. It worked fine for me, and was the only thing that did. Was struggling for 3 days until I found this. Thanks again! – Matt Campbell Oct 21 '15 at 19:39

score 2 · Answer 2 · answered May 29 '12 at 07:05

Many times when user enters number in cell which type(formatting) is text(string), spreadsheet software (openoffice or msoffice) changes it's formatting automatically. I am using apache poi and this is the way I wrote my code :

cell = row.getCell(); 
switch (cell.getCellType()) {

case HSSFCell.CELL_TYPE_STRING:
    value = cell.getRichStringCellValue().getString();
    break;
case HSSFCell.CELL_TYPE_NUMERIC:
    // if cell fomratting or cell data is Numeric
    // Read numeric value
    // suppose user enters 0123456 (which is string), in numeric way it is read as 123456.0
    // Now I want ot read string and I got number...Problem?????

    //well this is the solution
    cell.setCellType(Cell.CELL_TYPE_STRING);   // set cell type string, so it will retain original value "0123456"
    value = cell.getRichStringCellValue().getString(); // value read is now "0123456"    
    break;
default:
}

You're much better off using DataFormatter rather than converting the cell type, it has much higher fidelity in rendering numbers based on their formatting rules — Gagravarr, May 29 '12 at 13:51

JExcel/POI: Creating a cell as type Text

2 Answers2