Problem with Date Format

As earlier mentioned, cell.putValue(date) or cell.setValue(date) is not working as expected. Please look into it.

Let me know if you need more information.

Scenario: One of the column in data is of type date (format – ‘yyyy-mm-dd’). I am using cell.putValue(new Datetime(dateObject)) method to save it in excel. Here dateObject is java.util.date object with date formatter of pattern – ‘yyyy-mm-dd’ as I wanted the same in excel as well.

Expectations – value should be stored in excel table maintaining the datatype and pattern.

Existing behaviour – value is getting stored as general datatype, some random number is getting stored. Attaching the excel for your reference, column- ‘OrderDate’ is the one I am referring here which is having date as datatype.

Am I missing anything or can you please suggest me another way to achieve it?

Thanks, &

Regards

Hi Irfan,


First of all, I have split your existing thread to create a new one on your behalf because it is appropriate to post each distinct problem in a new thread. Moreover, this helps us in keeping the track of your requests easily and efficiently.

Regarding the original concerns, I am afraid, I am unable to find a sample spreadsheet with your recent response. There is only one spreadsheet shared in your other thread that was to evaluate the problem related to the blank row between ListObject and Total so if you have forgot to attach the sample then please provide it here along with your code snippet to replicate the said problem.

Please note, Excel stores date and time as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt. This is called a serial date, or serial date-time. When you insert a DateTime object in a cell, it is converted to the Excel’s date-time representation unless you format it using the Aspose.Cells’ Style object. Please check the following piece of code and it’s resultant spreadsheet for your reference.

Java

Workbook book = new Workbook();
Worksheet sheet = book.getWorksheets().get(0);
Cells cells = sheet.getCells();
cells.get(“A1”).putValue(new Date());
cells.get(“A2”).setValue(new Date());
cells.get(“B1”).putValue(new Date());
cells.get(“B2”).setValue(new Date());
Style style = book.createStyle();
style.setCustom(“yyyy-mm-dd”);
cells.get(“B1”).setStyle(style);
cells.get(“B2”).setStyle(style);
book.save(“D:/output.xlsx”, SaveFormat.XLSX);