如何正确格式化日期单元格并使用Apache POI 3.7填充内容

Background:

背景：

I need to export a spreadsheet document with one column containing date formatted data.

我需要导出一个电子表格文档，其中一列包含日期格式的数据。

I'm currently setting up the workbook style like so:

我正在设置工作簿样式，如下所示：

...
dateTimeStyle = workbook.createCellStyle();
//dateTimeStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
dateTimeStyle.setDataFormat((short)0x16);
...

and inserting the data into the cell/setting the format of the cell like so:

并将数据插入单元格/设置单元格的格式，如下所示：

...    
if (Date.class.isAssignableFrom(o.getClass())) {

    Calendar cal = Calendar.getInstance();
    cal.setTime((Date) o);
    cell.setCellStyle(dateTimeStyle);
    cell.setCellValue(cal);
}
...

Note: According to BuiltinFormats documentation (http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html) 0x16 refers to the date format I'm trying to achieve.

注意：根据BuiltinFormats文档（http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html)0x16指的是我想要实现的日期格式。

The problem I have is that when I open the exported document in Microsoft Office Excel 2007, when I right-click the cell and choose Format cells... it shows the selected cell as having a custom format of dd/mm/yyyy hh:mm

我遇到的问题是，当我在Microsoft Office Excel 2007中打开导出的文档时，右键单击单元格并选择“设置单元格格式...”时，它会将所选单元格显示为自定义格式为dd / mm / yyyy hh：毫米

Also, VLOOKUP operations do not work on the column (which I may, admittedly, be doing wrong):

此外，VLOOKUP操作不适用于该列（我承认，这可能是错误的）：

I have a feeling this is due to a misunderstanding of how Excel stores and formats content, and would appreciate any help provided.

我有一种感觉，这是因为误解了Excel如何存储和格式化内容，并感谢所提供的任何帮助。

Question:

题：

So, how do I correctly format/populate the cell so that Microsoft Excel treats it as a date and VLOOKUPs work etc?

那么，我如何正确格式化/填充单元格，以便Microsoft Excel将其视为日期和VLOOKUPs工作等？

Update: If I open the resulting file in Open Office Calc and choose Format Cells... the format shows up correctly as being Date. Starting to wonder, then, if this is an issue with the POI library and Excel 2007...

更新：如果我在Open Office Calc中打开生成的文件并选择格式化单元格...格式正确显示为日期。开始怀疑，如果这是POI库和Excel 2007的问题...

Many thanks.

非常感谢。

1 个解决方案

#1

If you want the dates in your excel to "behave" properly (including VLOOKUP, etc), you should write them as numeric and not as calendars.

如果您希望excel中的日期“正常”（包括VLOOKUP等），您应该将它们写为数字而不是日历。

Also when doing the Date -> Excel Double conversion be careful about setting the correct timezone to your Calendar object, otherwise the timezone offset to UTC will be added automatically and you will end up with datetimes in Excel different from the date times you thought you had in Java.

此外，在进行日期 - > Excel双转换时，请注意将正确的时区设置为Calendar对象，否则将自动添加到UTC的时区偏移量，最终Excel中的日期时间与您认为的日期时间不同在Java中。

Finally note that your setDataFormat() uses 0x16 format id, when I think for standard date format it should be just plain 16 (decimal). See this tutorial for a list of valid formats.

最后请注意，你的setDataFormat（）使用0x16格式的id，当我认为标准日期格式时，它应该只是简单的16（十进制）。有关有效格式的列表，请参阅本教程。

See this small example which generates an Excel in which the lookup works just fine:

请参阅这个生成Excel的小示例，其中查找工作正常：

package test;

import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.TimeZone;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Main {

    // note: 2014/6/11 -> 41801 excel
    public static double date2double(Date date)
    {
        return date.getTime() / 1000.0 / 60.0 / 60.0 / 24.0 + 25568.0;
    }

    public static void main(String[] args) 
    {
        try 
        {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet();

            CellStyle csDate = wb.createCellStyle();
            csDate.setDataFormat((short)16);

            Calendar cal = new GregorianCalendar(TimeZone.getTimeZone("UTC"));
            cal.set(2014, 6 - 1, 12, 0, 0, 0);
            cal.set(Calendar.MILLISECOND, 0);

            for(int i = 0; i < 10; ++i)
            {
                Row row = sheet.createRow(i);

                double d = date2double(cal.getTime());
                Cell cell = row.createCell(0);
                cell.setCellValue((int)d);
                cell.setCellStyle(csDate);

                cell = row.createCell(1);
                cell.setCellValue(i);

                cal.add(Calendar.DATE, 1);
            }

            FileOutputStream out = new FileOutputStream("/Users/enicolas/Downloads/test2.xls");
            wb.write(out);
            out.close();
        }
        catch (Throwable e) 
        {
            e.printStackTrace();
        }
    }
}