1

I want to read data in Excel with Java, and data of cell in Excel got 2 types is NUMERIC and STRING. So when I want to read the data as a NUMERIC, it only displays numbers 101125340004, not like this 1.01E+11 because it is a telephone attribute. My code is working, but for some values (1%) still display floating-point number but they are actually integers, and I don't know why.

Data in sheet of Excel 365 enter image description here

DataFormatter fmt = new DataFormatter();
if (cellType.toString().equals("NUMERIC"))
    companyTel = fmt.formatCellValue(currentRow.getCell(8));
else
    companyTel = currentRow.getCell(8).toString();

Output still got floating-point number in database enter image description here

Version of Java Apache POI I'm using is 3.17.

Please tell me what wrong in my code above or how do I solve the problem? Thank you all.

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
Dao Viet Anh
  • 13
  • 5
  • 1
    "Some values (1%) still display floating-point number but they are actually integers": Please show such an example. Btw.: The differentiation between numeric and string is not necessary using `DataFormatter` . – Axel Richter Aug 04 '18 at 06:17
  • I was posted my capture output yet. I know but if not using `if else` like that, if data type is `NUMERIC` they all display floating-point number. LOL. – Dao Viet Anh Aug 04 '18 at 07:48
  • You could earn an upvote on your question if you would editing it and providing all necessary facts. What spreadsheet calculation software is used? How looks the data in the sheet of this software? The resulting output you have provided already. What `apache poi` version is used? – Axel Richter Aug 04 '18 at 10:13
  • @AxelRichter I'm sorry for my bad English, so i hard to describe all information of my problem. I was think if i post all it will make little confusing. So I summed up the problem that I encountered. – Dao Viet Anh Aug 04 '18 at 10:29

3 Answers3

3

Microsoft Excel converts values having more than 11 digits in scientific notation when cell number format General is used. So 842223622111 leads to 8,42224E+11 in Excel cells having number format General. If in Excel the cell shall show 842223622111, then a special number format (0) is needed instead of General.

You can read about available number formats in Excel for Office 365 and the special behavior of the General format for large numbers (12 or more digits).

Libreoffice/OpenOffice Calc will not do so. There 842223622111 stays 842223622111 in cells having number format General.

Now, if apache poi gets a cell containing 842223622111 and having number format General, then DataFormatter will format this like Excel would also do.

If you wants DataFormatter should format this more like Libreoffice/OpenOffice Calc, then you could do:

...
DataFormatter fmt = new DataFormatter();
...
fmt.addFormat("General", new java.text.DecimalFormat("#.###############"));
...

Using this approach, no changes in the Excel sheet are necessary. But of course the default behavior of apache poi's DataFormatter is changed. To avoid this, you could format the affected cells in Excel using number format 0, which is Number without thousands separator and number of decimal decimal places = 0.

But since you mentioned that this column contains telephone numbers, the most common solution would be formatting the whole column using number format Text (@) . This "treats the content of a cell as text and displays the content exactly as you type it, even when you type numbers." So after that formatting was applied nothing will change to scientific notation any more.

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • Yes, You were right, another way to solve my problem is format "`Number` without thousands separator and number of decimal decimal places = 0.", but I just wanna write code in java and not impact input file. – Dao Viet Anh Aug 04 '18 at 12:06
  • @Dao Viet Anh: Yes you can do so as shown. But nevertheless the error is in the sheet and should be fixed there rather than work around it. Else other users of that sheet will also being confused about this. And because column `I` contains telephone numbers, it should be fixed by formatting this whole column using number format `Text`. – Axel Richter Aug 04 '18 at 12:23
0

As documentation says:

Internally, formats will be implemented using subclasses of Format such as DecimalFormat and SimpleDateFormat. Therefore the formats used by this class must obey the same pattern rules as these Format subclasses. This means that only legal number pattern characters ("0", "#", ".", "," etc.) may appear in number formats. Other characters can be inserted before or after the number pattern to form a prefix or suffix.

For example the Excel pattern "$#,##0.00 "USD"_);($#,##0.00 "USD")" will be correctly formatted as "$1,000.00 USD" or "($1,000.00 USD)". However the pattern "00-00-00" is incorrectly formatted by DecimalFormat as "000000--". For Excel formats that are not compatible with DecimalFormat, you can provide your own custom Format implementation via DataFormatter.addFormat(String,Format). The following custom formats are already provided by this class:

  • SSN "000-00-0000"
  • Phone Number "(###) ###-####"
  • Zip plus 4 "00000-0000"

Also you can add your own formats like this:

DataFormatter.addFormat(String, Format)
Andrei Suvorkov
  • 5,559
  • 5
  • 22
  • 48
0

Try this It may help you:-

HSSFCell cellE1 = row1.getCell((short) 4);
cellE1.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
Double e1Val = cellE1.getNumericCellValue();
BigDecimal bd = new BigDecimal(e1Val.toString());
long lonVal = bd.longValue();
System.out.println(lonVal);
Ravindra Kumar
  • 1,842
  • 14
  • 23