Date & time values imported as date only

[kjetil-kilhavn]

Method convert_to_table in class zcl_excel_worksheet identifies the number format correctly as a date & time format.
However, there is then a check for date components in the format, and if there are the value is imported as a date.

Is this by design or would you welcome a pull request to support import of date & time columns to time stamps?

[sandraros]

Different people at different moments have developed different parts of abap2xlsx, it's why you can find discrepancies, hazardous design, etc. Could you provide an example to reproduce please? Of course, you are welcome to complete with a pull request, welcome aboard!

[kjetil-kilhavn]

I have been investigating further and reported another issue in the process. Will try to add test cases and fix the code, and create a pull request. In the mean time, here is a test case file I've used to see how a wide selection of formats are handled:
abap2xlsx-datetime_formats.xlsx

Further reading was done - and the reference documentation is extensive, so today I learned that format code "mmmmm" is a date format code to display the initial letter of the month name - and that format code "mm" is interpreted according to what surrounds it.

Source: ECMA-376 Part 1

Page 1789:
Month versus minutes
If "m" or "mm" code is used immediately after the "h" or "hh" code (for hours) or immediately before the "ss" code (for seconds), the application shall display minutes instead of the month.

... have to "love" the creativity of those Microsoft people!

[sandraros]

Program to show what is expected:

REPORT.
CONSTANTS _20230803_140711 TYPE string VALUE `45141.588321759256`.

DATA(lo_excel) = NEW zcl_excel( ).
DATA(lo_worksheet) = lo_excel->get_active_worksheet( ).

DATA(lo_style_date_time) = lo_excel->add_new_style( ).
lo_style_date_time->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
lo_style_date_time->fill->fgcolor-rgb  = zcl_excel_style_color=>c_gray.
lo_style_date_time->number_format->format_code = zcl_excel_style_number_format=>c_format_date_datetime.

lo_worksheet->set_cell( ip_columnrow = 'A1'
                        ip_value     = _20230803_140711
                        ip_style     = lo_style_date_time
                        ip_data_type = ' ' ).

TYPES:
  BEGIN OF ts_read_itab,
    date_time TYPE tzntimestp,
  END OF ts_read_itab.
TYPES tt_read_itab TYPE STANDARD TABLE OF ts_read_itab WITH EMPTY KEY.
DATA(gt_read_itab) = VALUE tt_read_itab( ).
lo_worksheet->convert_to_table( EXPORTING iv_begin_row = 1
                                IMPORTING et_data      = gt_read_itab ).

ASSERT gt_read_itab[ 1 ]-date_time = '20230803140711'. "<== short dump because actual = '00000020230804'