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

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

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屋！