Inconsistent handling of dates before 01.01.1900

[oliver-huetkoeper]

Hi,

currently absp2xlsx handles „invalid“ Excel dates (dates before 01.01.1900) different on the import and on the export side. I want to convert an ALV to a CSV file using abap2xlsx. If the data contains such a date, converting the ALV to Excel works without problem. But exporting it to CSV fails. In my opinion, this is inconsistent. What do you think?

Best regards, Oliver

PARAMETERS p_path TYPE string LOWER CASE.

INITIALIZATION.
  cl_gui_frontend_services=>get_sapgui_workdir( CHANGING sapworkdir = p_path ).
  cl_gui_cfw=>flush( ).

START-OF-SELECTION.
  TYPES tt_sflight TYPE STANDARD TABLE OF sflight WITH EMPTY KEY.

  DATA(gt_sflight) = VALUE tt_sflight(
      ( carrid = 'AA' connid = 1 fldate = '20000101'  )
      ( carrid = 'AA' connid = 1 fldate = '18000101'  ) ).
  cl_salv_table=>factory( IMPORTING r_salv_table = DATA(lo_salv)
                          CHANGING  t_table      = gt_sflight ).
  cl_salv_bs_runtime_info=>set( display  = abap_false
                                metadata = abap_false
                                data     = abap_false ).
  lo_salv->display( ).

  DATA(lo_converter) = NEW zcl_excel_converter( ).
  DATA(lo_excel) = NEW zcl_excel( ).
  lo_converter->convert( EXPORTING io_alv       = lo_salv
                                   it_table     = gt_sflight
                         CHANGING  co_excel     = lo_excel ).

  DATA(lo_excel_writer) = CAST zif_excel_writer( NEW zcl_excel_writer_csv( ) ).
  DATA(lv_file) = lo_excel_writer->write_file( lo_excel ).

  DATA(lt_file_tab) = cl_bcs_convert=>xstring_to_solix( lv_file ).
  cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = xstrlen( lv_file )
                                                    filename     = p_path && '\Sheet1.csv'
                                                    filetype     = 'BIN'
                                          CHANGING  data_tab     = lt_file_tab ).

[gregorwolf]

Do you have a screenshot with the differences?

[oliver-huetkoeper]

Do you have a screenshot with the differences?

The converter creates negative numbers from such a date. The CSV writer raises an exception if the number is negative. Method date_to_excel_string vs. excel_string_to_date in class zcl_excel_common.

[sandraros]

Result of the program above (Sheet1.csv):

(36526 is the Excel date number representing the YYYYMMDD date 20000101, -36523 is for 18000101)

[sandraros]

Obviously, a bug to be fixed. It must have been here for a long time; my 2 cents that it originates from ancient modifications of zcl_excel_worksheet=>set_cell concerning "data type" and "value type" notions (which I never clearly understood). In the program above, zcl_excel_writer_csv receives an empty data type for date and number fields, but the class expects "D" for date fields (there's also a crazy logic which tests the field names to determine the data type).