How do I set cell value to Date and apply default Excel date format?

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.

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.

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

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.

233756 次浏览
小开
最佳答案

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

此示例用于处理。Xlsx 文件类型。此示例来自。用于创建。Xslx 电子表格。

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

此代码示例可用于更改日期格式。在这里，我想将 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();
}}
小开

要设置为默认的 Excel 类型 Date (默认为 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 模板; 所有将填充日期的单元格都已经格式化为日期单元格(使用 Excel)。

我使用 ApachePOI 打开. xlsx 模板，然后将日期写入单元格，就可以了。

在下面的示例中，单元格 A1已经在 Excel 中格式化为 [$-409]mmm yyyy，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);

打开 Excel 时，日期的格式正确。

小开

要知道 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:

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

除了@BlondeCodeanswer之外，下面还列出了您可以使用的所有可用格式 creationHelper.createDataFormat().getFormat((short) index)

0 = “一般”
1 = “0”
2 = "0.00"
3 = “ # ，# 0”
4 = “ # ，# 0.00”
5 = ""$"#,##0_);("$"#,##0)"
红色
7 = “ $”# ，# # 0.00 _) ; (“ $”# ，# # 0.00)
8 = “ $”# ，# # 0.00 _) ; 红色
9 = “0%”
10 = “0.00%”
11 = "0.00E+00"
12 = “ # ?/?”
13 = “ # ? ?/?”
14 = “ m/d/广州欢聚时代”
15 = “ d-mm-yy”
16 = “ d-mm”
17 = “ MM-YY”
18 = "h:mm AM/PM"
19 = “ h: mm: ss AM/PM”
20 = “ h: mm”
21 = “ h: mm: ss”
22 = “ m/d/广州欢聚时代 h: mm”
23-36 = 保留
37 = “ # ，# # 0 _; (# ，# # 0)”
38 = “ # ，# # 0 _) ; 红色”
39 = “ # ，# # 0.00 _) ; (# ，# # 0.00)”
40 = “ # ，# # 0.00 _) ; 红色”
41 = "(* * * * *));(* (# ，# # 0) ;(* "-");(@)"
42 = “ (“ $”* # ，# # 0 _) ; (“ $”* (# ，# 0) ;(“ $”*”-“ );(@< em >)”
43 = “ (* # ，# # 0.00 _) ; (* (# ，# 0.00) ;(*”-“ ? ? );(@< em >)”
44 = “ (“ $”* # ，# # 0.00 _) ; (“ $”* (# ，# 0.00) ;(“ $”*”-“ ? ? );(@_)”< br > 45 = “ mm: ss”
46 = “[ h ] : mm: ss”
47 = "mm:ss.0"
48 = “ # # 0.0 E + 0”
49 = “@”< br >

从索引164开始，这是你的自定义模式


提交答案