CODEWITHSUNDEEP
javaexcelspringapache-poi
Lakmal Vithanage
Lakmal Vithanage

Reputation: 2777

Cell formatting to remove decimal point in excel cell when data Writing Using Apache POI

I'm using Apache POI to Write a Excel function into a cell and evaluate the function. What i need to do is to remove all decimal points from the cell.Currently it getting unnecessary two Zeros at the end of each cell values.The cell formatting as follows. But it is always getting two zeros at the end.

     XSSFCellStyle cellStyle = cell.getCellStyle();
     DataFormat df = workbook.createDataFormat();
     cellStyle.setDataFormat(df.getFormat("###,##0"));
     cell.setCellStyle(cellStyle);


     if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
          evaluator.evaluateFormulaCell(cell);
      }

I have refered following URLs and several other sites and couldn't find a way to fix the bug.

  1. link1
  2. link2

NOTE I'm using Apache POI version 3.14 with Spring 4.3.1.RELEASE.

Upvotes: 0

Views: 5923

Answers (2)

Umesh Sonawane
Umesh Sonawane

Reputation: 527

So basically if you are using the POI library in java it happens that the Number column cell always returns double format because of poi only support double number format for which I mention link here.

To overcome this if you wish to get original value as shown in the column, here is the simple solution.

When cell.getNumericCellValue() returns a result if it is a normal number without decimal it comers with one decimal 0 for example if it is shown in sheet 410 it returns 410.0

so you need to add the below code to get the exact result when you try to get the value from the cell.

for example String cellValue = format(cell.getNumericCellValue()+""); 

public static String format(String val){
    String stringVal = String.valueOf(val);
    String[] number = stringVal.split( "[.]" );
    if(number.length>1 && number[1].equalsIgnoreCase("0")){
        return number[0];                               
    } else {
        return val;
    }
}

Upvotes: 1

alex.pulver
alex.pulver

Reputation: 2125

Try to create the cell style for the workbook and apply this style with "#,##0" number format for all the cells in the column:

CellStyle style = workbook.createCellStyle();
DataFormat format = workbook.createDataFormat();
style.setDataFormat(format.getFormat("#,##0"));
cell.setCellStyle(style);

Creating the style from the workbook is the optimal way and does not overload memory.

You can also adjust your formula to round the number with 0 decimal places:

=ROUND(A1,0)

Upvotes: 1

Related Questions