javaapache-poidata-formatters

How do I edit pattern in DataFormatter?

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.

Solution

  • 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.