0

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.

Community
  • 1
  • 1
Lakmal Vithanage
  • 2,767
  • 7
  • 42
  • 58
  • 1
    Cell Styles are workbook scoped, and Excel has a pretty low maximum. What happens if you create the style only once at the start and re-use it? – Gagravarr Apr 23 '17 at 21:47
  • @Gagravarr, Thanks your idea.But I have posted the required code only. I'm using this style for every cells in the Column. Anyway, Do you have any idea to resolve my issue ? – Lakmal Vithanage Apr 24 '17 at 05:12

2 Answers2

1

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)
alex.pulver
  • 2,107
  • 2
  • 31
  • 31
  • By doing so my previous cellstyles were gone. So what i did was getting previous cellstyle as follows, `XSSFCellStyle cellStyle = cell.getCellStyle();` and then merge them as follows, `style .cloneStyleFrom(cellStyle );` – Lakmal Vithanage May 03 '17 at 12:11
1

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;
    }
}
Umesh Sonawane
  • 517
  • 6
  • 17