How do I edit pattern in DataFormatter?

Question

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

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

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.

Answer 1

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.

Answer 2

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);

Answer 3

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)