Apache POI Java XSSFCell DataFormat not beeing applied

Question

currently im struggling setting a DataFormat to a specific row.

This is how my Excel Sheet looks like. Im having those 4 Columns. Column 1 should take DataFormat Number Column 2 & 3 should take DataFormat Date Column 4 is ok

public static void createTableRow(Sheet sheet, List<String> EDIROWKeys, int col, CellStyle style , int lastRowNum){
    
    if(sheet == null){
        return;
    }
    int lastRow = lastRowNum;
    int newRow = lastRow + 1;
    Workbook wb = sheet.getWorkbook();
    SimpleDateFormat format = new SimpleDateFormat();
    CreationHelper helper = wb.getCreationHelper();
    org.apache.poi.ss.usermodel.DataFormat date = wb.createDataFormat();
    XSSFRow row = (XSSFRow) sheet.createRow(newRow);
    if(row == null){
        return;
    }
    int startCol = col;
    for(String string : EDIROWKeys){
        XSSFCell cell = row.createCell(startCol);
    
        FOe.getFOPSessionContext().getDbContext().out().println(string);
    
        if(style != null){
        
            cell.setCellStyle(style);
        }else  {
            XSSFWorkbook wbx = (XSSFWorkbook) sheet.getWorkbook();
            XSSFDataFormat dataformat = wbx.createDataFormat();

            XSSFCellStyle cs = wbx.createCellStyle();
            if(startCol == 1){
                cs.setDataFormat(dataformat.getFormat("#"));
            }
            cell.setCellStyle(cs);
            
        }
        cell.setCellValue(string);
        
        startCol++;
    }
    return;
}

This is the Method i am using to Create my rows & Columns https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/BuiltinFormats.html This is the resource i was getting my DataFormats from. I also tried applying those DataFormats with HssfDataformat to my cell - no success either.

I apperciate any helpful input :)

Solved by using setCellValue(Double);...

if(string.matches("\\d+")){
        cell.setCellValue(Double.valueOf(string));
        startCol++;
        continue;
    }

After Cell.setCellValue(java.lang.String value) the cell contains a text value. Text values will not use number formats. You need set the cell values using Cell.setCellValue(double value) or Cell.setCellValue(java.util.Date value) or another set-cell-value-method which sets numeric values. — Axel Richter
Thanks mate, this solved the problem. Added now some condition for numbers and date values :)! — T. Lekar

I.G. I.G. · Accepted Answer · 2020-07-08T07:33:20

I see one possible case. If method argument style is null, new style, XSSFCellStyle cs is not applied to cell. Is cell.setCellStyle(cs) missing?

Apache POI Java XSSFCell DataFormat not beeing applied

1 Answers