Setting Time in Excel using POI

Question

I am trying to create an Excel Work sheet using POI api in Java. In that Excel Work Sheet I want to have a cell with TIME alone. By setting this we can include the cell in summation of that particuluar column as we do in number columns. For this we need to format the cell as Time >> 13:30:55. (The internal format is 'h:mm:ss;@' ). And we need to remove the date part from the cell.

When I read the cell the cell value using the POI, it is returning as 'Sun Dec 31 01:00:00 IST 1899' ( When i set the value as 1:00 ), the cell format index is 166 and the cell format string is 'h:mm:ss;@'.

After setting the formats and style which were read from the excel and the cell value as 1800-December-31 and with the time value, the new excel shows cell as '######' (error) and cell value is setted as '-1'. Below is the code I have used. Did I miss anything ? Is it possible to set the value as I required.

    InputStream is = new BufferedInputStream(new FileInputStream("<FileName>"));
    XSSFWorkbook wb = new XSSFWorkbook(is);
    is.close();

    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFRow row = sheet.getRow(2);

    XSSFCell cell = row.getCell(18);
    System.out.println("ExcelFileReader main cell.getDateCellValue() : '" + cell.getDateCellValue() + "'");
    System.out.println("ExcelFileReader main cell.getCellStyle().getDataFormat() : '" + cell.getCellStyle().getDataFormat() + "'");
    System.out.println("ExcelFileReader main cell.getCellStyle().getDataFormat() : '" + cell.getCellStyle().getDataFormatString() + "'");

    XSSFRow row1 = sheet.createRow(21);
    XSSFCell cell1 = row1.createCell(2);

    cell1.setCellStyle(cell.getCellStyle());
    cell1.setCellValue(cell.getDateCellValue());

    Calendar dummy = Calendar.getInstance();
    dummy.setLenient(false);
    dummy.set(Calendar.YEAR, 1899);
    dummy.set(Calendar.MONTH, Calendar.DECEMBER);
    dummy.set(Calendar.DATE, 31);

    dummy.set(Calendar.HOUR, 00);
    dummy.set(Calendar.MINUTE, 00);
    dummy.set(Calendar.SECOND, 00);
    dummy.set(Calendar.MILLISECOND, 00);

    Calendar cc = Calendar.getInstance();
    XSSFRow row2 = sheet.createRow(25);
    XSSFCell cell2 = row2.createCell(2);

    dummy.set(Calendar.HOUR, cc.get(Calendar.HOUR));
    dummy.set(Calendar.MINUTE, cc.get(Calendar.MINUTE));
    dummy.set(Calendar.SECOND, cc.get(Calendar.SECOND));
    dummy.set(Calendar.MILLISECOND, cc.get(Calendar.MILLISECOND));

    System.out.println("ExcelFileReader main dummy : '" + dummy.getTime() + "'");
    cell2.setCellValue(dummy.getTime());

    CellStyle style = wb.createCellStyle();
    DataFormat df = wb.createDataFormat();
    style.setDataFormat(df.getFormat("[h]:mm:ss;@"));
    cell2.setCellStyle(style);

    FileOutputStream fos = new FileOutputStream(new File("<New Excel file>"));
    wb.write(fos);
    fos.close();
    System.out.println("ExcelFileReader DONE");

The following is the output of the program.

    ExcelFileReader main cell.getDateCellValue() : 'Sun Dec 31 00:15:00 IST 1899'
    ExcelFileReader main cell.getCellStyle().getDataFormat() : '166'
    ExcelFileReader main cell.getCellStyle().getDataFormat() : 'h:mm:ss;@'
    ExcelFileReader main dummy : 'Sun Dec 31 11:32:24 IST 1899'
    ExcelFileReader DONE

Answer 1

I have used the following code to generate the time cell, in Excel using POI. And I am able to use the cell in

        XSSFRow row2 = sheet.createRow(25);
        XSSFCell cell1 = row2.createCell(4);
        XSSFCell cell2 = row2.createCell(5);

        CellStyle style = wb.createCellStyle();
        DataFormat df = wb.createDataFormat();
        style.setDataFormat(df.getFormat("[h]:mm:ss;@"));

        cell1.setCellFormula("TIME(0,15,00)"); // 00:15:00
        cell1.setCellType(Cell.CELL_TYPE_FORMULA);
        cell1.setCellStyle(style);
        evaluator.evaluateFormulaCell(cell1);

        cell2.setCellFormula("TIME(0,30,00)"); //00:30:00
        cell2.setCellType(Cell.CELL_TYPE_FORMULA);
        evaluator.evaluateFormulaCell(cell2);
        cell2.setCellStyle(style);

Answer 2

Dates and Times in Excel are normally stored as floating point numbers, as full and fractional days since 1900 or 1904. You can see this by typing 0.5 into an excel cell and formatting as a time - it'll show as 12:00:00, or set a value of 1.5 and it'll show as a date in 1900/1904 as a date, 12:00:00 as a time, or 36:00:00 if a time that allows >24 hours (it's a different format code)

What you'll want to do is fire up a copy of Excel, type in the time value you want to have displayed, and work out the format string that makes it show up as you want it to. Then, make a note of that format code, and give that to POI as the data format for the cell. Now, use something like DateUtil in POI to build up your Date object, and set that to the cell as the value. With the right format string in, Excel will show only the time part and not the date