以双重格式获取日期43318.4847916667，而不是06-08-2018 11:38:06

Question

我正在编写java代码来使用HSSFWorkbook（. xls）生成excel文件。我需要以这种格式在一列中写入日期06-08-2018 11:38:06但它是这样生成的43318.4847916667。
这是我使用的代码片段。请帮助我如何才能成功地编写

Workbook workbook = new HSSFWorkbook();
Sheet excelSheet = workbook.createSheet();
Row dataRow = excelSheet.createRow(1);;
Cell dataCell = dataRow.createCell(1);;
CellStyle cStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();         
cStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
dataCell.setCellValue(new Date());
dataCell.setCellStyle(cStyle);

Answer 1

按照以下代码在DD-MM-YYYY HH:MM:ss中打印日期。

String fileName = "myExcel.xls";
WritableWorkbook writableWorkbook = null;
response.setContentType("application/vnd.ms-excel");
writableWorkbook = Workbook.createWorkbook(response.getOutputStream());
WritableSheet excelOutputsheet = writableWorkbook.createSheet("Sheet1", 0);

DateFormat customDateFormatWithTime = new DateFormat("dd-MM-yyyy HH:mm:ss");
WritableCellFormat dateFormatWithTime = new 
WritableCellFormat(customDateFormatWithTime);

int row = 0;
int col = 0;
excelOutputsheet.setColumnView(col, 20);
Label lable1 = new Label(col, row, "Date and Time", cellFormat);
excelOutputsheet.addCell(lable1);

row = row + 1;
col = col + 1;
DateTime myDate = new jxl.write.DateTime(col, row, "Value", dateFormatWithTime);
excelOutputsheet.addCell(myDate);

Answer 2

如果您想要"06-08-2018 11:38:06"，那么您的日期格式应该是"dd-MM-YYYY HH： mm： ss"。否则，您只是缺少需要将工作簿写入的FileOutputStream。

private static final String FILE_NAME = "./out/MyFirstExcel.xls";

public static void main(String ... args) {
    Workbook workbook = new HSSFWorkbook();
    Sheet excelSheet = workbook.createSheet();
    Row dataRow = excelSheet.createRow(1);
    Cell dataCell = dataRow.createCell(1);
    CellStyle cStyle = workbook.createCellStyle();
    CreationHelper createHelper = workbook.getCreationHelper();
    cStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-MM-YYYY HH:mm:ss"));
    dataCell.setCellValue(new Date());
    dataCell.setCellStyle(cStyle);

    try {
        File file = new File(FILE_NAME);
        if(!file.exists()) {
            file.createNewFile();
        }
        FileOutputStream outputStream = new FileOutputStream(file);
        workbook.write(outputStream);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Answer 3

无法重现问题。

下面的完整示例生成了一个Excel工作簿，该工作簿在单元格B2中有一个工作表和一个正确格式的日期。

代码：

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

class CreateExcelDate {

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

  try (Workbook workbook = new HSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("./Excel.xls") ) {

   CellStyle cStyle = workbook.createCellStyle();
   CreationHelper createHelper = workbook.getCreationHelper();         
   cStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));

   Sheet excelSheet = workbook.createSheet();
   Row dataRow = excelSheet.createRow(1);;
   Cell dataCell = dataRow.createCell(1);;
   dataCell.setCellValue(new java.util.Date());
   dataCell.setCellStyle(cStyle);

   excelSheet.setColumnWidth(1, 25 * 256);

   workbook.write(fileout);
  }

 }
}

结果：

从你的评论来看，你的问题并没有显示全部。行有一个循环，每行都应创建一个日期。这只适用于第42排。

这个问题众所周知。Excel对每个工作簿的单元格样式计数有限制。请参见：Excel规范和限制。

因此，如果你在循环中一遍又一遍地创建一个新的单元样式，这个极限有时会达到。但是，您不必总是在循环中创建新的单元样式。单元格样式存储在工作簿级别。只要在循环外创建所需的单元样式即可。然后仅将先前创建的单元样式应用于循环中的单元。

以下内容适用于我，并创建了1000个正确格式的日期：

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

class CreateExcelDate {

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

  try (Workbook workbook = new HSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("./Excel.xls") ) {

   CellStyle cStyle = workbook.createCellStyle();
   CreationHelper createHelper = workbook.getCreationHelper();         
   cStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));

   Sheet excelSheet = workbook.createSheet();

   for (int r = 1; r < 1000; r++) {
    Row dataRow = excelSheet.createRow(r);;
    Cell dataCell = dataRow.createCell(1);;
    dataCell.setCellValue(new java.util.GregorianCalendar(2019, 9, r));
    dataCell.setCellStyle(cStyle);
   }

   excelSheet.setColumnWidth(1, 25 * 256);

   workbook.write(fileout);
  }

 }
}