06-11-2021 8:34 PM
Hi,
Excel (XLSX) is repairing when it is opened. This is due to new line character (cr_lf) in table column header.
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
06-16-2021 10:02 AM
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 ).
06-13-2021 8:54 AM
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.
06-14-2021 7:49 PM
Hi Sandra,
Did you ever try with bind_table method instead of set_cell?
Thanks
Prabha
06-14-2021 8:13 PM
When I use the methods zcl_excel_common=>get_fieldcatalog and worksheet->bind_table, I encountered the issue.
06-15-2021 5:49 AM
Please provide a minimal reproducible example. Don't post code as image so that I can reproduce your issue.
06-15-2021 4:39 PM
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........
06-16-2021 10:02 AM
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 ).
06-16-2021 2:24 PM
06-20-2021 7:37 PM
For information, I just proposed a fix in abap2xlsx for issue #474 above. But it could take time before it's merged.