如何将单元格值设置为日期并应用默认的 Excel 日期格式? [英] How do I set cell value to Date and apply default Excel date format?

问题描述

一段时间以来，我一直在使用 Apache POI 以编程方式读取现有的 Excel 2003 文件.现在我有一个新的要求，即在内存中创建整个 .xls 文件(仍然使用 Apache POI)，然后在最后将它们写入一个文件.阻碍我的唯一问题是处理带有日期的单元格.

I've been using Apache POI for some time to read existing Excel 2003 files programmatically. Now I have a new requirement to create entire .xls files in-memory (still using Apache POI) and then write them to a file at the end. The only problem standing in my way is the handling of cells with dates.

考虑以下代码:

Date myDate = new Date();
HSSFCell myCell;
// code that assigns a cell from an HSSFSheet to 'myCell' would go here...
myCell.setCellValue(myDate);

当我将包含此单元格的工作簿写入文件并用 Excel 打开时，该单元格显示为数字.是的，我确实意识到 Excel 将其日期"存储为自 1900 年 1 月 1 日以来的天数，这就是单元格中的数字所代表的内容.

When I write the workbook containing this cell out to a file and open it with Excel, the cell is displayed as a number. Yes, I do realize that Excel stores its 'dates' as the number of days since January 1 1900 and that is what the number in the cell represents.

问题:我可以在 POI 中使用哪些 API 调用来告诉它我希望将默认日期格式应用于我的日期单元格?

理想情况下，如果用户在 Excel 中手动打开电子表格并输入 Excel 识别为日期的单元格值，我希望电子表格单元格以 Excel 为其分配的默认日期格式显示.

Ideally I want the spreadsheet cell to be displayed with the same default date format that Excel would have assigned it if a user had manually opened the spreadsheet in Excel and typed in a cell value that Excel recognized as being a date.

推荐答案

http://poi.apache.org/spreadsheet/quick-guide.html#CreateDateCells

CellStyle cellStyle = wb.createCellStyle();
CreationHelper createHelper = wb.getCreationHelper();
cellStyle.setDataFormat(
    createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);

这篇关于如何将单元格值设置为日期并应用默认的 Excel 日期格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！