使用Apache POI将日期格式设置为Excel单元格不起作用 [英] Setting date format to excel cell with apache POI is not working

问题描述

我正在使用下面的代码将日期值设置为excel单元格，但它在excel单元格中显示了双精度值.要求是在打开excel时查看日期值.

I am using below code to set date value to excel cell but its showing double value in excel cell. Requirement is to see date value when we open excel.

row = sheet.createRow(rowNum++);
cell = row.createCell(0);

XSSFCellStyle cellStyle = (XSSFCellStyle)cell.getCellStyle();       
cellStyle.setDataFormat(workbook.createDataFormat().getFormat("mm/dd/yyyy"));
Date loginDate = formatStringToDate(entry.getKey(),"yyyy-MM-dd");
cell.setCellValue(loginDate);
cell.setCellStyle(cellStyle);

当我打开并看到单元格格式时，它显示为常规"，如下所示

When I open and see the cell format it is showing as General as shown below

如果我将格式更改为Excel中的日期，则它在单元格中显示日期值，但我希望该单元格默认显示日期值

If I change the format to date in excel then it is showing Date value in cell but I want the cell to show Date value by default

推荐答案

首先，您需要了解 Excel 如何管理其单元格样式.它在工作簿级别执行此操作，因此并非每个单元格都有其自己的单元格样式.取而代之的是，需要的单元格样式不尽相同，并且这些单元格使用的是这些单元格样式之一.

At first you need to know how Excel does managing it's cell styles. It does this on workbook level so that not each cell has it's own cell style. Instead there are as much different cell styles as needed and the cells are using one of those cell styles.

新创建的单元格具有默认的单元格样式，如果使用 Cell.getCellStyle 在此新创建的单元格上，您将获得此默认单元格样式.因此，您的代码尝试将默认单元格样式设置为日期格式的单元格样式.这不是要走的路.

A new created cell has the default cell style and if using Cell.getCellStyle on this new created cell, you will get this default cell style. So your code tries to make the default cell style a date formatted cell style. This is not the way to go.

因此，在工作簿级别上，我们首先创建所需的单元格样式.例如，一种日期格式的单元格样式和一种货币格式的单元格样式.

So at first on workbook level we are creating as much cell styles as needed. For example one date formatted cell style and one currency formatted cell style.

然后，我们创建工作表和单元格，并将数据放入单元格中.如果单元格需要特殊的单元格样式，那么我们将使用先前创建的一种单元格样式.

Then we are creating the sheet and the cells and are putting the data into the cells. If the cell needs a special cell style, then we are using one of the cell styles we have previous created.

示例:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Map;
import java.util.TreeMap;
import java.util.List;
import java.util.Arrays;

public class CreateExcelNumberFormats {

 public static void main(String[] args) throws Exception {

  Map<Date, List<Object>> data = new TreeMap<Date, List<Object>>();

  data.put(new GregorianCalendar(2017, 9, 29, 6, 0).getTime(), Arrays.asList("user 1", 1234.56));
  data.put(new GregorianCalendar(2017, 9, 30, 6, 0).getTime(), Arrays.asList("user 2", 789.12));
  data.put(new GregorianCalendar(2017, 9, 31, 6, 0).getTime(), Arrays.asList("user 3", 131415.16));
  data.put(new GregorianCalendar(2017, 9, 29, 15, 45).getTime(), Arrays.asList("user 4", 1234567.89));
  data.put(new GregorianCalendar(2017, 9, 30, 9, 45).getTime(), Arrays.asList("user 5", 123.45));

  Workbook wb = new XSSFWorkbook();
  CreationHelper creationHelper = wb.getCreationHelper();

  //on workbook level we are creating as much cell styles as needed:
  CellStyle datestyle = wb.createCellStyle();
  datestyle.setDataFormat(creationHelper.createDataFormat().getFormat("mm/dd/yyyy"));
  CellStyle currencystyle = wb.createCellStyle();
  currencystyle.setDataFormat(creationHelper.createDataFormat().getFormat("$#,##0.00"));

  //now we are creating the sheet and the cells and are putting the data into the cells
  Sheet sheet = wb.createSheet();
  Row row = sheet.createRow(0);
  Cell cell = row.createCell(0);
  cell.setCellValue("Date");
  cell = row.createCell(1);
  cell.setCellValue("Logged in User");
  cell = row.createCell(2);
  cell.setCellValue("Amount");

  int rowNum = 1;

  for (Map.Entry<Date, List<Object>> entry : data.entrySet()) {

   row = sheet.createRow(rowNum++);
   cell = row.createCell(0);
   Date loginDate = entry.getKey();
   cell.setCellValue(loginDate);
   //if the cell needs a special cell style, then we are using one of the ones we have previous created
   cell.setCellStyle(datestyle);

   List<Object> userdatas = entry.getValue();

   int cellNum = 1;
   for (Object userdata : userdatas) {
    cell = row.createCell(cellNum);
    if (cellNum == 1) {
     cell.setCellValue((String)userdata);
    } else if (cellNum == 2) {
     cell.setCellValue((Double)userdata);
     //if the cell needs a special cell style, then we are using one of the ones we have previous created
     cell.setCellStyle(currencystyle);   
    }
    cellNum++;
   }

  }

  wb.write(new FileOutputStream("CreateExcelNumberFormats.xlsx"));
  wb.close();

 }

}

这篇关于使用Apache POI将日期格式设置为Excel单元格不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！