Retrieving Date/DateTime value from cell

Aspose.Cells Product Family
1

I am trying to get cell value from my sheet. The value is only date at some places and date with time at others. I want to get the value as it is displayed on the sheet.
Tried using cell.getValue().toString() which does give the same date format as displayed on sheet
also tried cell.getStringValue() and cell.getDisplayStringValue but these seem to ignore the time part.

2

@deaspose,

Could you please zip and attach your template Excel file to evaluate your issue. We will check your issue soon.

3

I need to get cell values as displayed in B, E and F column of the attached file.
sheet.zip (13.6 KB)

4

@deaspose,

Thanks for the template file.

I tested your scenario/case with latest version/fix (Aspose.Cells for Java v22.11) using your sample file and following sample code and it works as expected:
e.g.
Sample code:

Workbook workbook = new Workbook("f:\\files\\file.xlsx ");
Cell cell = workbook.getWorksheets().get(0).getCells().get("B2");
System.out.println(cell.getValue());//2022-10-03T04:35:06
System.out.println(cell.getDateTimeValue());//2022-10-03T04:35:06

Please note, Date/Time values are stored in numeric notations in Excel (XLSX) file formats. If you could unzip your XLSX file via some zip tool and check/browse the source XML data (in the file “\xl\worksheets\sheet1.xml”), you will notice such DateTime values (which are displayed in MS Excel) are stored as “44837.1910491088” for B column cells. This is MS Excel’s behavior which automatically applies formatting to such values to display DateTime values. Even you may specify any cell (in B column) to “General” via Format Cells… dialog, and you will notice the underlying value (44837.19105).

5

Hey, the cell value of B2 in the sheet is 03-10-2022 04:35:07. The date format is dd-mm-yyyy whereas in your output it is in yyyy-mm-dd. This is what I meant when I asked for getting the same value as displayed in the sheet which couldn’t be done using the function you used.

6

@deaspose,

Please note, Aspose.Cells will ignore Time part as displayed in the formula/function bar of MS Excel. It can only get you the value which is actually displayed in cell (e.g., B2) itself. To get Time part also, you need to format the cell involving Time part by yourselves and then you may get the expected results. Plesae note, Time part is not displayed in the cell, so you have to re-format the cell to get Time part involved. See the following sample code for your reference:
e.g.
Sample code:

        Workbook workbook = new Workbook("f:\\files\\file.xlsx ");
        Cell cell = workbook.getWorksheets().get(0).getCells().get("B2");
        Style style = cell.getStyle();
        style.setCustom("d/m/yyyy h:mm:ss AM/PM");
        cell.setStyle(style);

        //Now get the detailed displayed (formatted) DateTime value.
        System.out.println(cell.getStringValue());// 3/10/2022 4:35:07 AM -- same as Excel display
        System.out.println(cell.getDisplayStringValue());// 3/10/2022 4:35:07 AM -- same as Excel display

Alternatively, to get detailed DateTime value as well including minutes, seconds, etc…, you should get them from the DateTime object directly by youselves.

Please note, on my end, after opening the file into MS Excel manually, in the formula bar, the value is 3/10/2022 4:35:07 AM.