如何设置单元格值为Date并应用默认的Excel日期格式?

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.

我已经使用Apache POI以编程方式读取现有的Excel 2003文件有一段时间了。现在我有了一个新的需求，要在内存中创建完整的.xls文件(仍然使用Apache POI)，然后在末尾将它们写到一个文件中。唯一妨碍我的问题是处理带有日期的单元格。

Consider the following code:

考虑下面的代码:

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

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.

当我将包含该单元格的工作簿写到一个文件并使用Excel打开它时，单元格将显示为一个数字。是的，我确实意识到Excel将它的“日期”存储为自1900年1月1日以来的天数，这就是单元格中的数字所表示的天数。

QUESTION: What API calls can I use in POI to tell it that I want a default date format applied to my date cell?

问题:在POI中我可以使用什么API来告诉它我想要一个应用于我的日期单元的默认日期格式?

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.

理想的情况是，我希望电子表格单元显示的默认日期格式与Excel中所指定的相同，如果用户在Excel中手动打开电子表格，并在Excel中键入一个被确认为日期的单元格值。

6 个解决方案

#1

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

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

#2

This example is for working with .xlsx file types. This example comes from a .jsp page used to create a .xslx spreadsheet.

此示例用于处理.xlsx文件类型。这个示例来自用于创建.xslx电子表格的.jsp页面。

import org.apache.poi.xssf.usermodel.*; //import needed

XSSFWorkbook  wb = new XSSFWorkbook ();  // Create workbook
XSSFSheet sheet = wb.createSheet();      // Create spreadsheet in workbook
XSSFRow row = sheet.createRow(rowIndex); // Create the row in the spreadsheet


//1. Create the date cell style
XSSFCreationHelper createHelper = wb.getCreationHelper();
XSSFCellStyle cellStyle         = wb.createCellStyle();
cellStyle.setDataFormat(
createHelper.createDataFormat().getFormat("MMMM dd, yyyy")); 

//2. Apply the Date cell style to a cell

//This example sets the first cell in the row using the date cell style
cell = row.createCell(0);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);

#3

To set to default Excel type Date (defaulted to OS level locale /-> i.e. xlsx will look different when opened by a German or British person/ and flagged with an asterisk if you choose it in Excel's cell format chooser) you should:

要设置为默认的Excel类型日期(默认为OS级别的locale /->，即xlsx在由德国人或英国人打开时看起来会有所不同/如果您在Excel的单元格格式选择器中选择它，则使用星号标记)，您应该:

    CellStyle cellStyle = xssfWorkbook.createCellStyle();
    cellStyle.setDataFormat((short)14);
    cell.setCellStyle(cellStyle);

I did it with xlsx and it worked fine.

我用xlsx做的，效果很好。

#4

I am writing my answer here because it may be helpful to other readers, who might have a slightly different requirement than the questioner here.

我在这里写下我的答案，因为它可能对其他读者有帮助，他们可能有一个稍微不同于这里的提问者的要求。

I prepare an .xlsx template; all the cells which will be populated with dates, are already formatted as date cells (using Excel).

我准备了.xlsx模板;所有使用日期填充的单元格都已经被格式化为日期单元格(使用Excel)。

I open the .xlsx template using Apache POI and then just write the date to the cell, and it works.

我使用Apache POI打开.xlsx模板，然后将日期写到单元，它就可以工作了。

In the example below, cell A1 is already formatted from within Excel with the format [$-409]mmm yyyy, and the Java code is used only to populate the cell.

在下面的示例中，单元格A1已经用格式[$-409]mmm yyyyyyy从Excel中格式化，并且Java代码只用于填充单元格。

FileInputStream inputStream = new FileInputStream(new File("Path to .xlsx template"));
Workbook wb = new XSSFWorkbook(inputStream);
Date date1=new Date();
Sheet xlsMainTable = (Sheet) wb.getSheetAt(0);
Row myRow= CellUtil.getRow(0, xlsMainTable);
CellUtil.getCell(myRow, 0).setCellValue(date1);

WHen the Excel is opened, the date is formatted correctly.

当打开Excel时，日期被正确格式化。

#5

This code sample can be used to change date format. Here I want to change from yyyy-MM-dd to dd-MM-yyyy. Here pos is position of column.

此代码示例可用于更改日期格式。这里我想从yyyy-MM-dd改为dd- mm -yyy。这里pos是列的位置。

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class Test{ 
public static void main( String[] args )
{
String input="D:\\somefolder\\somefile.xlsx";
String output="D:\\somefolder\\someoutfile.xlsx"
FileInputStream file = new FileInputStream(new File(input));
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> iterator = sheet.iterator();
Cell cell = null;
Row row=null;
row=iterator.next();
int pos=5; // 5th column is date.
while(iterator.hasNext())
{
    row=iterator.next();

    cell=row.getCell(pos-1);
    //CellStyle cellStyle = wb.createCellStyle();
    XSSFCellStyle cellStyle = (XSSFCellStyle)cell.getCellStyle();
    CreationHelper createHelper = wb.getCreationHelper();
    cellStyle.setDataFormat(
        createHelper.createDataFormat().getFormat("dd-MM-yyyy"));
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    Date d=null;
    try {
        d= sdf.parse(cell.getStringCellValue());
    } catch (ParseException e) {
        // TODO Auto-generated catch block
        d=null;
        e.printStackTrace();
        continue;
    }
    cell.setCellValue(d);
    cell.setCellStyle(cellStyle);
   }

file.close();
FileOutputStream outFile =new FileOutputStream(new File(output));
workbook.write(outFile);
workbook.close();
outFile.close();
}}

#6

To know the format string used by Excel without having to guess it: create an excel file, write a date in cell A1 and format it as you want. Then run the following lines:

要知道Excel使用的格式字符串，而不需要猜测它:创建一个Excel文件，在单元格A1中编写一个日期，并按自己的需要格式化它。然后运行以下代码行:

FileInputStream fileIn = new FileInputStream("test.xlsx");
Workbook workbook = WorkbookFactory.create(fileIn);
CellStyle cellStyle = workbook.getSheetAt(0).getRow(0).getCell(0).getCellStyle();
String styleString = cellStyle.getDataFormatString();
System.out.println(styleString);

Then copy-paste the resulting string, remove the backslashes (for example d/m/yy\ h\.mm;@ becomes d/m/yy h.mm;@) and use it in the http://poi.apache.org/spreadsheet/quick-guide.html#CreateDateCells code:

然后复制粘贴结果字符串，删除反斜线(例如d/m/yy\ h.mm;@变成d/m/yy h.mm;@)，并在http://poi.apache.org/spreadsheet/quick-guide.html#CreateDateCells代码中使用:

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