JExcel / POI: Creating a Cell as a Text Type

Question

I have a requirement that includes reading values ​​from an Excel spreadsheet and populating a spreadsheet for users to modify and reload into our application. One of these cells contains a 5-character text string, which can be letters, numbers, or a combination of both. Some of these lines contain only numbers and start from zero. Because of this, the cell type is Text; however, when I use the Apache POI or JExcel to populate the spreadsheet so that users can modify it, it is always set as the General cell type.

Is there a way to use any of these libraries or some other excel api that I haven't seen yet to indicate that the cell is of type Text?

Answer 1

My colleague just found a way to do this. In JExcel, this can be accomplished using WritableCellFormat, for example:

WritableCellFormat numberAsTextFormat = new WritableCellFormat(NumberFormats.TEXT); 

Then, when you create your cell to add to the sheet, you simply pass in the format as usual:

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

If you are using the Apache POI, you must create an HSSFCellStyle and then set its data format as follows:

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

Answer 2

Many times, when a user enters a number in a cell, the type (formatting) of text (string), spreadsheet software (openoffice or msoffice) automatically changes its formatting. I use apache poi, and so 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: }