使用 POI 在 Excel 中设置时间 [英] Setting Time in Excel using POI

问题描述

我正在尝试使用 Java 中的 POI api 创建 Excel 工作表.在那个 Excel 工作表中，我想要一个只有 TIME 的单元格.通过设置它，我们可以像在数字列中那样将单元格包含在该特定列的总和中.为此，我们需要将单元格格式化为 Time >> 13:30:55.(内部格式为 'h:mm:ss;@' ).我们需要从单元格中删除日期部分.

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.

当我使用 POI 读取单元格的单元格值时，它返回为Sun Dec 31 01:00:00 IST 1899"(当我将值设置为 1:00 时)，单元格格式索引为 166 并且单元格格式字符串为'h:mm:ss;@'.

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;@'.

设置从excel读取的格式和样式以及单元格值为1800-December-31和时间值后，新的excel将单元格显示为'######'(错误)和单元格值设置为-1".下面是我使用过的代码.我错过了什么吗?是否可以根据需要设置该值.

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");

以下是程序的输出.

    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

推荐答案

Excel 中的日期和时间通常存储为浮点数，即自 1900 年或 1904 年以来的完整天数和小数天数.您可以通过在 excel 中键入 0.5 来查看这一点单元格和格式设置为时间 - 它将显示为 12:00:00，或将值设置为 1.5，它将显示为 1900/1904 中的日期作为日期，12:00:00 作为时间，或36:00:00 如果时间允许超过 24 小时(这是不同的格式代码)

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)

您需要做的是启动 Excel 副本，输入您想要显示的时间值，并计算出使其显示为您想要的格式字符串.然后，记下该格式代码，并将其作为单元格的数据格式提供给 POI.现在，在 POI 中使用类似 DateUtil 的东西来构建您的 Date 对象，并将其设置为单元格作为值.使用正确的格式字符串，Excel 将只显示时间部分而不显示日期

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

这篇关于使用 POI 在 Excel 中设置时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！