CODEWITHSUNDEEP
javatimeformatapache-poi
ShankPossible
ShankPossible

Reputation: 507

Using apache poi input Time

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 Screenshot of output on Excel

Upvotes: 4

Views: 1869

Answers (2)

marme1ad
marme1ad

Reputation: 1383

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:

Excel time output

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.

Upvotes: 7

Stefan
Stefan

Reputation: 2395

I use POI 3.17 and Excel from Office 365 ProPlus with Swedish locale. I added a few lines to your code (to create workbook and sheet, etc). The code below works fine. In the cell I get "07:30 PM" and in the formula bar "1970-01-01 19:30:00". If you don't get something similar when you run my code (using POI 3.17), my guess would be that something is a bit strange with your Excel.

public void createExcelFile() {
    XSSFWorkbook workbook = new XSSFWorkbook();
    Time time = Time.valueOf("19:30:00");
    CellStyle cellStyle1 = workbook.createCellStyle();
    CreationHelper createHelper1 = workbook.getCreationHelper();
    cellStyle1.setDataFormat(createHelper1.createDataFormat().getFormat("HH:MM AM/PM"));
    Sheet sheet = workbook.createSheet("Sheet");
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(1);
    System.out.println(time.toString());
    cell.setCellValue(time);
    cell.setCellStyle(cellStyle1);

    try {
        FileOutputStream outputStream = new FileOutputStream("C:/temp/file.xlsx");
        workbook.write(outputStream);
        workbook.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Upvotes: 0

Related Questions