Xlsx issue, cell.getCellStyle().getDataFormat() changing value after adding or removing date

Question

I am using this block of code to get cell.getCellStyle().getDataFormat() . It changes it's values after adding or removing content from xlsx. What should be done in this case?

First cell 21/01/2016 returns cell.getCellStyle().getDataFormat() retuns 14 when I add some extra rows then cell.getCellStyle().getDataFormat() returns 165

if (DateUtil.isCellDateFormatted(cell)) {
    double val = cell.getNumericCellValue();
    Date date = DateUtil.getJavaDate(val);

    String dateFmt = null;
    System.out.println(cell.getCellStyle().getDataFormat());
    XSSFCellStyle style = (XSSFCellStyle) cell.getCellStyle();
    System.out.println("In INT: "+style.getDataFormat());

    if (cell.getCellStyle().getDataFormat() == 14) { 
        System.out.println("14");
        dateFmt = "dd/mm/yyyy"; 
    } else if(cell.getCellStyle().getDataFormat() == 165) { 
        System.out.println("165");
        dateFmt = "m/d/yy";
    } else if(cell.getCellStyle().getDataFormat() == 166) {
        System.out.println("166");
        dateFmt = "d-mmm-yy"; 
    } else if(cell.getCellStyle().getDataFormat() == 167) { 
        System.out.println("167");
        dateFmt = "mmmm d yyyy ";
    } else if(cell.getCellStyle().getDataFormat() == 168) { 
        System.out.println("168");
        dateFmt = "m/d/yyyy";
    } else if(cell.getCellStyle().getDataFormat() == 169) { 
        System.out.println("169");
        dateFmt = "d-mmm-yyyy";
    } else {
        dateFmt = cell.getCellStyle().getDataFormatString();
    }

    System.out.println("dateFmt "+dateFmt);
    value = new CellDateFormatter(dateFmt).format(date);
    System.out.println("Date "+value);
}

Really difficult to help. This code does not demonstrate the problem, we can't see what happens, and it even seems what you describe is expected behavior. You should rethink your question: the problem might not be where you think — Raffaele, Jan 21 '16 at 08:50
value of cell.getCellStyle().getDataFormat() is getting changed. for 21/01/2016, cell.getCellStyle().getDataFormat() gives value as 14 but when i add extra rows then cell.getCellStyle().getDataFormat() for same date,date pattern returns 165. Why is it returning different values? — Sonal, Jan 21 '16 at 08:56
please refer to this question [http://stackoverflow.com/questions/34900605/excell-cell-style-issue/34902174#34902174] — Sonal, Jan 21 '16 at 08:57
You are moving things around. Likely you are not reading the same cell the second time — Raffaele, Jan 21 '16 at 09:21
The date pattern for `cell.getCellStyle().getDataFormat() == 14` is system dependent. It is *not* always `dd/mm/yyyy`. So if you set the number format of a cell to `dd/mm/yyyy`, it will no more be `numFmtId="14"`. Furthermore the `numFmtId`s 165... are not built-in. So you should not rely on them. Simpl read `cell.getCellStyle().getDataFormatString()` for all `numFmtId`s except 14. — Axel Richter, Jan 21 '16 at 09:24

score 0 · Accepted Answer · answered Jan 21 '16 at 09:50

I am posting this answer in case anyone needs in future.

          if (DateUtil.isCellDateFormatted(cell)) {
                double val = cell.getNumericCellValue();
                Date date = HSSFDateUtil.getJavaDate(val);
                String dateFmt = null;

                if(cell.getCellStyle().getDataFormat()==14){
                    dateFmt = "dd/mm/yyyy";
                    value = new CellDateFormatter(dateFmt).format(date);
                }
                else{
                    DataFormatter fmt = new DataFormatter();
                    String valueAsInExcel = fmt.formatCellValue(cell);
                    value = valueAsInExcel;
                }
           }

Xlsx issue, cell.getCellStyle().getDataFormat() changing value after adding or removing date

1 Answers1

Linked