The date value set after cell formatting is incorrect

Aspose.Cells Product Family
1

The date value set after cell formatting is incorrect：
Set the custom format of cell A1 to yyyymmdd, and then set the value of cell to the value of date type “0001-01-01” through Java, but get the cell value again, and the result is “00010103”
image.png (53.1 KB)
image.png (21.8 KB)

this is code：

Workbook workbook = new Workbook("D://Aspose//EPPR_54028//EPPR_54028.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
Date value = format.parse("0001-01-01");
sheet.getCells().get(0,0).setValue(value);
Cell cell = sheet.getCells().get(0,0);
Object v = cell.getValue();
System.out.println("v:" +v);
workbook.save("D://Aspose//EPPR_54028//output//save.xlsx");

this is excel file：
EPPR_54028.zip (23.1 KB)

2

@xhaixia，
In ms excel, all date time before 1900-01-01 is invalid value for date time type. When you setting such kind of date value to a cell, we will try to convert it to plain string value for the cell. However, for java’s Date object, there are some issue for the conversion between it and the internal data of cells model. We have logged a ticket(CELLSJAVA-44740) for it and will try to fix it(make the string value of the cell correspond to the correct date) in later fix/versions. Currently for date before 1900, we think you may convert it to the expected string by yourself and then set to the cell.

3

@xhaixia,

This is to inform you that your issue has been resolved. The fix will be included in the next official release (Aspose.Cells v22.8) which is scheduled to be released in the second week of August 2022. You will also be notified when the next version is published.

4

The issues you have found earlier (filed as CELLSJAVA-44740) have been fixed in this update. This message was posted using Bugs notification tool by Peyton.Xu