Using apache poi input Time

Question

I have used Apahe POI to input the time into the excel file like following

Time time = Time.valueOf("19:30:00");
CellStyle cellStyle1 = workbook.createCellStyle();
CreationHelper createHelper1 = workbook.getCreationHelper();
cellStyle1.setDataFormat(
        createHelper.createDataFormat().getFormat("HH:MM AM/PM"));
cell = row.getCell(1);
System.out.println(time.toString());
cell.setCellValue(time);
cell.setCellStyle(cellStyle1);

which resulted into excel as expected however there was following mismatch found that actual value of excel and display value are different- How can i make them same , am i using incorrect way of updating the value in Excel Time format

Answer 1

In Excel dates and times are stored as floating-point numbers, as amount of days since midnight 01/01/1900.

If you will store some value less than 1.0 - it will be interpreted as a time, otherwise as a date, e.g.:

1. 0.5 will be equal to 12:00:00
2. 5.5 will be equal to 05.01.1900 12:00:00

To handle dates and times correctly please use org.apache.poi.ss.usermodel.DateUtil, e.g. your example might look something like this:

    double time = DateUtil.convertTime("19:30:00");
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(
            workbook.createDataFormat().getFormat("HH:MM AM/PM"));
    cell.setCellValue(time);
    cell.setCellStyle(cellStyle);

And the result Excel will look so:

---

Assume question was about the things described above, real date/time value in Excel should be a double and presentation value should be based on the style/pattern you've set; assume that goal was to achieve this kind of similarity, i.e. 19:30:00 in the formula and 07:30 PM in the cell.

If no and the goal was to have 07:30 PM in both cases - then you will just need to store a string value instead, not a date/time.