CODEWITHSUNDEEP
apache-poijexcelapi
user1411138
user1411138

Reputation: 121

JExcel/POI: Creating a cell as type Text

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?

Upvotes: 4

Views: 9786

Answers (2)

Nandkumar Tekale
Nandkumar Tekale

Reputation: 16158

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:
}

Upvotes: 2

user1411138
user1411138

Reputation: 121

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

Upvotes: 8

Related Questions