Apache POI : How to format numeric cell values

3
votes

I am using Apache POI 3.9 for XLS/XLSX file processing.

In the XLS sheet, there is a column with numeric value like "3000053406".

When I read it with POI with..

cell.getNumericCellValue()

It gives me value like "3.00E+08". This create huge problem in my application.

How can I set the number formatting while reading data in Apcahe POI ?

There is a way that I know is to set the column as "text" type. But I want to know if there is any other way at Apache POI side while reading the data. OR can we format it by using simple java DecimalFormatter ?

4
apache-poi

4 Answers

2
votes

This one comes up very often....

Picking one of my past answers to an almost identical question

What you want to do is use the DataFormatter class. You pass this a cell, and it does its best to return you a string containing what Excel would show you for that cell. If you pass it a string cell, you'll get the string back. If you pass it a numeric cell with formatting rules applied, it will format the number based on them and give you the string back.

For your case, I'd assume that the numeric cells have an integer formatting rule applied to them. If you ask DataFormatter to format those cells, it'll give you back a string with the integer string in it.

1
votes

Problem can be strictly Java-related, not POI related, too. Since your call returns a double,

double val = cell.getNumericCellValue();

You may want to get this

DecimalFormat df = new DecimalFormat("#"); int fractionalDigits = 2; // say 2 df.setMaximumFractionDigits(fractionalDigits); double val = df.format(val);

0
votes

Creating a BigDecimal with the double value from the numeric cell and then using the

BigDecimal.toPlainString()

function to convert it to a plain string and then storing it back to the same cell after erasing the value solved the whole problem of exponential representation of numeric values.

The below code solved the issue for me.

    Double dnum = cellContent.getNumericCellValue();
    BigDecimal bd = new BigDecimal(dnum);
    System.out.println(bd.toPlainString());
    cellContent.setBlank();
    cellContent.setCellValue(bd.toPlainString());
    System.out.println(cellContent.getStringCellValue());
-2
votes

long varA = new Double(cellB1.getNumericCellValue()).longValue(); This will bring the exact value in variable varA.