3

when I read a content of cell for e.g. if it is in date format it casts into some another value like 12/31/2099 -> 46052 and $50.00 -> 50 and 50.00% -> 0.5.

But what I want is to get the exact string value for every cell.

My Code is like this:

cell.setCellType(Cell.CELL_TYPE_STRING);
String str = cell.getStringCellValue();
yonjans
  • 151
  • 3
  • 10

2 Answers2

5

No need for explicit formatting, Apache POI provides for DataFormatter class as utility to leverage the format of the content as it appears on the excel. You can choose custom formats too, a simple example would be (cell is reference to your XSSFCell object):

System.out.println(new DataFormatter().formatCellValue(cell));

Excel sheet looks like:

enter image description here

Using DataFormatter (sop statement above) prints:

50%
$ 1,200
12/21/14

Where your normal formatting would print:

0.5
1200.0
21-Dec-2014
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
StoopidDonut
  • 8,547
  • 2
  • 33
  • 51
0

I would not try and alter the data type while I am reading.

Instead of that, I would try to

  • get the cell type first of all
  • then use the appropiate getter method for each data type

For example:

// Let's say you have prepared a DecimalFormat yourNumberFormatter and a
// DateFormat yourDateFormatter with the format that is appropiate to your
// presentation 
Cell cell = ...;
String value = null;
switch (cell.getCellType()) {
   case Cell.TYPE_NUMERIC:
     // Date format
     if ( DateUtil.isCellDateFormatted(cell) ) {
        value = yourDateFormatter.format(cell.getDateCellValue());
     }
     else {
        value = yourNumberFormatter.format(cell.getNumericCellValue());
     }
     break;
   case Cell.TYPE_STRING:
     value = cell.getStringCellValue();
     break;
  // Etc.
}
Jorge_B
  • 9,712
  • 2
  • 17
  • 22
  • I wanted to handle all cell types like currency, ssn, etc. I think DateUtil is only for handling DateType cells – yonjans Feb 07 '14 at 11:43
  • Yes, you are right. The problem is that things like "currency" are a combination between numeric format and a specific style for the excel cell; you will need to finetune the numeric case entry in order to separe each case you have and produce an adecuate presentation of the number your read – Jorge_B Feb 07 '14 at 11:48