如何将单元格值设置为“日期"并应用默认的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屋！