[abap2xlsx] excel (XLSX) repair due to new line character (cr_lf) in table column header

[padchalingam]

Hi,

Excel (XLSX) is repairing when it is opened. This is due to new line character (cr_lf) in table column header.

abap-code.png

excel-opening-error-message-1.png

excel-opening-error-message-2.png

New line characterblinigual-column-header.png is used to show bilingual column header in separate lines.

Could you help me to resolve this issue?

Thanks

Prabha

[abo]

On friday we ran into a similar issue using purely standard methods on a 7.31 system: files generated with cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform would have to be repaired whereas those created with to_xml would be just fine.

We didn't have the time or the mandate to fix it but it does look suspiciously like something a SAP Note would address.

[padchalingam]

Hi Sandra,

Did you ever try with bind_table method instead of set_cell?

Thanks

Prabha

[padchalingam]

When I use the methods zcl_excel_common=>get_fieldcatalog and worksheet->bind_table, I encountered the issue.

[Sandra_Rossi]

Please provide a minimal reproducible example. Don't post code as image so that I can reproduce your issue.

[padchalingam]

lt_field_catalog = zcl_excel_common=>get_fieldcatalog( ip_table = t_actuals ).

LOOP AT lt_field_catalog ASSIGNING <fs_field_catalog>.

CASE <fs_field_catalog>-fieldname.

WHEN 'KOSTL'. <fs_field_catalog>-position = 1.

<fs_field_catalog>-dynpfld = abap_true.

<fs_field_catalog>-style = lo_style_col_heading->get_guid( ).

<fs_field_catalog>-SCRTEXT_S = gc_cc_e && |\r\n| && gc_cc_f.

<fs_field_catalog>-SCRTEXT_M = gc_cc_e && |\r\n| && gc_cc_f.

<fs_field_catalog>-SCRTEXT_L = gc_cc_e && |\r\n| && gc_cc_f.

WHEN 'LTEXT_E'. ..........

lo_worksheet->bind_table( EXPORTING ip_table = t_actuals

it_field_catalog = lt_field_catalog

is_table_settings = ls_table_settings

IMPORTING es_table_settings = ls_table_settings_out ).

Issue is occurring when |\r\n| inserted between English and French column header.

<fs_field_catalog>-SCRTEXT_S = gc_cc_e && |\r\n| && gc_cc_f........

[Sandra_Rossi]

I can't make it work (minimal reproducible code below) with abap2xlsx. There's a popup error when opening the file in Excel.

The abap2xlsx bug is explained by oliver-huetkoeper here: binding an internal table with field catalog produces corrupt excel file Issue #474 sapmentors/abap2...

There's no workaround. So keep only one line. Or replace bind_table with many set_cell.

minimal reproducible code (fails when opening file in Excel):

DATA: gc_save_file_name TYPE string VALUE 'abap2xlsx.xlsx'.
INCLUDE zdemo_excel_outputopt_incl.
START-OF-SELECTION.
  SELECT * FROM scarr INTO TABLE @DATA(scarr).
  DATA(lo_excel) = NEW zcl_excel( ).
  DATA(lo_worksheet) = lo_excel->get_active_worksheet( ).
  DATA(lt_field_catalog) = zcl_excel_common=>get_fieldcatalog( ip_table = scarr ).
  LOOP AT lt_field_catalog ASSIGNING FIELD-SYMBOL(<fs_field_catalog>).
    <fs_field_catalog>-scrtext_s =
    <fs_field_catalog>-scrtext_m =
    <fs_field_catalog>-scrtext_l = |First { sy-tabix }\nSecond { SY-tabix }|.
  ENDLOOP.
  lo_worksheet->bind_table(
    EXPORTING
      ip_table            = scarr
      it_field_catalog    = lt_field_catalog
      is_table_settings   = VALUE zexcel_s_table_settings(
                              table_name       = 'RR'
                              top_left_column  = 'A'
                              top_left_row     = 1
                              show_row_stripes = abap_true ) ).
  lcl_output=>output( cl_excel = lo_excel ).

[padchalingam]

Thanks Sandra. So it is a limitation in abap2xlsx.

[Sandra_Rossi]

For information, I just proposed a fix in abap2xlsx for issue #474 above. But it could take time before it's merged.