2

I am trying to read an excel file using Java POI HSSF. Everything was working fine, except that when the value is 001001 the HSSFCell will return 1001.0

Is there any way I can use HSSFCell to get the value 001001 ?

I am not supposed to do any modification to the excel file.

Thank in advance for any help and suggestion.

Edit

I have been using the following code:

cell.setCellType(Cell.CELL_TYPE_STRING); cell.toString();

I also ran a debug mode and checked on the cell value the moment the HSSF grabs it. It truncate the leading zeros and converted it to double therefore I do not see a way to retrieve the truncated zeros. There is a link that stated it could be a bug from HSSF: http://osdir.com/ml/jakarta.poi.user/2003-02/msg00007.html

By the way, I solved it by hard coded it. The number of digits are know in advanced. The link for the code that I used: How to format a Java string with leading zero?

Community
  • 1
  • 1
Amblll
  • 88
  • 1
  • 11
  • Try reading it as a string – ngrashia Jun 12 '14 at 07:57
  • I have tried the cell.setCellType(Cell.CELL_TYPE_STRING); line previously. It helps to save the need of differentiating whether is a numeric value or string value. I also just tried the other 2 lines of code, I still get 1001. – Amblll Jun 12 '14 at 08:05
  • I ran on debug mode previously. From the HssfCell value, I can see that the value is 1001.0 then how does it know to get back the 2 leading zero if it is already truncated ? – Amblll Jun 12 '14 at 08:07
  • That question is to distinguish the data value of 2.0 and 2. To read the it in string format. Therefore the code: cell.setCellType(Cell.CELL_TYPE_STRING) will work. For my case is that I would to keep the leading zeros: 001001, but I keep getting 1001 or 1001.0 – Amblll Jun 12 '14 at 09:26
  • I have used DataFormatter df=new DataFormatter(); for(int i=3;i<=lastRowNum;i++) { hrow = sheet.getRow(i); hcell = hrow.getCell(2); cellVal1=df.formatCellValue(hcell); System.out.println(cellVal1); } and it worked perfectly. cellVal1 is a String object. rest all understood i reckon. – Raja Nov 20 '21 at 17:12

1 Answers1

0

Can you try reading it as a string instead of Numeric,

cell.setCellType(Cell.CELL_TYPE_STRING);

Changing the cell type usually will not modify the contents of the cell. it can be retrieved with either of the following approaches:

cell.getStringCellValue();

cell.getRichStringCellValue().getString();
ngrashia
  • 9,869
  • 5
  • 43
  • 58