CODEWITHSUNDEEP
javaexcelapache-poi
Madhusudan
Madhusudan

Reputation: 4815

How to set the data format for the row in Excel?

I am trying to set the data type for row in Excel to date format ('yyyy-MM-dd') and for that I have written following piece of code:

....
Row r = null;
    CellStyle cellStyle = workbook.createCellStyle();
    //CreationHelper createHelper = workbook.getCreationHelper();
    cellStyle.setDataFormat(
            workbook.createDataFormat().getFormat("yyyy-MM-dd"));
    for(int i =0;i < 2;i++){
        switch(i){
            case 0:
                r = sheet.getRow(0);
                r.setRowStyle(cellStyle);
                break;
            case 1:
                r = sheet.getRow(1);
                r.setRowStyle(cellStyle);
                break;
            default:
                break;
        }
    }
....

I have posted formatting for only first two rows for reference. But when I run my code and open the Excel sheet and check the format of cells in first two rows then its general only. Change is not getting reflected in Excel sheet. I am not getting what I did wrong here.

Upvotes: 0

Views: 155

Answers (1)

Moh-Aw
Moh-Aw

Reputation: 3018

If you are creating this excel sheet from scratch than it doesn't have rows yet. I get a NullPointerException on getRow(0) in a fresh sheet. Try using createRow(0) instead:

public static void main(String[] args) throws JDOMException, IOException {
        File excelFile = new File("C:/temp/test.xls");
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheetname");

        Row r = null;
        CellStyle cellStyle = workbook.createCellStyle();
        // CreationHelper createHelper = workbook.getCreationHelper();
        cellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd"));
        for (int i = 0; i < 2; i++) {
            switch (i) {
            case 0:
                r = sheet.createRow(0);
                r.setRowStyle(cellStyle);
                break;
            case 1:
                r = sheet.createRow(1);
                r.setRowStyle(cellStyle);
                break;
            default:
                break;
            }
        }

        try (FileOutputStream out = new FileOutputStream(excelFile)) {
            workbook.write(out);
        }
        Desktop.getDesktop().open(excelFile);
    }

Upvotes: 1

Related Questions