Dear all,
can anybody confirm (I mean has really seen) that there is a program which successfully can display multiple sheets - each with an ALV (method bind_table) and a picture (method add_new_drawing)?
It is not working for me - even when I use a template where the pictures are already included and I merely add the ALV data. (Not working means: Excel warning message and formating/display glitches)
Here a Demo with Option for 2ALV's or 2Pic's and Both
REPORT zdemo_2alv2pic.
DATA: lo_excel TYPE REF TO zcl_excel,
lo_worksheet TYPE REF TO zcl_excel_worksheet,
lo_style_cond TYPE REF TO zcl_excel_style_cond,
lo_column TYPE REF TO zcl_excel_column,
lo_drawing TYPE REF TO zcl_excel_drawing.
DATA: lt_field_catalog TYPE zexcel_t_fieldcatalog,
ls_table_settings TYPE zexcel_s_table_settings,
ls_iconset TYPE zexcel_conditional_iconset.
DATA: lt_bin TYPE solix_tab,
lv_len TYPE i,
lv_content TYPE xstring,
ls_key TYPE wwwdatatab.
PARAMETERS: p_2alv RADIOBUTTON GROUP r1.
PARAMETERS: p_2pic RADIOBUTTON GROUP r1.
PARAMETERS: p_err RADIOBUTTON GROUP r1.
INITIALIZATION.
* Fill Dynamic texts
%_p_2alv_%_app_%-text = '2 ALV->OK'.
%_p_2pic_%_app_%-text = '2 Pic->OK'.
%_p_err_%_app_%-text = 'Both->NOT OK'.
CONSTANTS: gc_save_file_name TYPE string VALUE '10_iTabFieldCatalog.xlsx'.
INCLUDE zdemo_excel_outputopt_incl.
START-OF-SELECTION.
FIELD-SYMBOLS: <fs_field_catalog> TYPE zexcel_s_fieldcatalog.
" Creates active sheet
CREATE OBJECT lo_excel.
" Get active sheet
lo_worksheet = lo_excel->get_active_worksheet( ).
lo_worksheet->set_title( 'Internal table' ).
ls_iconset-iconset = zcl_excel_style_cond=>c_iconset_5arrows.
ls_iconset-cfvo1_type = zcl_excel_style_cond=>c_cfvo_type_percent.
ls_iconset-cfvo1_value = '0'.
ls_iconset-cfvo2_type = zcl_excel_style_cond=>c_cfvo_type_percent.
ls_iconset-cfvo2_value = '20'.
ls_iconset-cfvo3_type = zcl_excel_style_cond=>c_cfvo_type_percent.
ls_iconset-cfvo3_value = '40'.
ls_iconset-cfvo4_type = zcl_excel_style_cond=>c_cfvo_type_percent.
ls_iconset-cfvo4_value = '60'.
ls_iconset-cfvo5_type = zcl_excel_style_cond=>c_cfvo_type_percent.
ls_iconset-cfvo5_value = '80'.
ls_iconset-showvalue = zcl_excel_style_cond=>c_showvalue_true.
* "cond style
* lo_style_cond = lo_worksheet->add_new_style_cond( ).
* lo_style_cond->rule = zcl_excel_style_cond=>c_rule_iconset.
* lo_style_cond->mode_iconset = ls_iconset.
* lo_style_cond->priority = 1.
DATA lt_test TYPE TABLE OF sflight.
SELECT * FROM sflight INTO TABLE lt_test. "#EC CI_NOWHERE
lt_field_catalog = zcl_excel_common=>get_fieldcatalog( ip_table = lt_test ).
LOOP AT lt_field_catalog ASSIGNING <fs_field_catalog>.
CASE <fs_field_catalog>-fieldname.
WHEN 'CARRID'.
<fs_field_catalog>-position = 3.
<fs_field_catalog>-dynpfld = abap_true.
<fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_count.
WHEN 'CONNID'.
<fs_field_catalog>-position = 4.
<fs_field_catalog>-dynpfld = abap_true.
<fs_field_catalog>-abap_type = cl_abap_typedescr=>typekind_int.
"This avoid the excel warning that the number is formatted as a text: abap2xlsx is not able to recognize numc as a number so it formats the number as a text with
"the related warning. You can force the type and the framework will correctly format the number as a number
WHEN 'FLDATE'.
<fs_field_catalog>-position = 2.
<fs_field_catalog>-dynpfld = abap_true.
WHEN 'PRICE'.
<fs_field_catalog>-position = 1.
<fs_field_catalog>-dynpfld = abap_true.
<fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_sum.
* <fs_field_catalog>-style_cond = lo_style_cond->get_guid( ).
WHEN OTHERS.
<fs_field_catalog>-dynpfld = abap_false.
ENDCASE.
ENDLOOP.
*--------------------------------------------------------------------*
" First sheet
*--------------------------------------------------------------------*
* " Add Picture
IF p_2pic IS NOT INITIAL
OR p_err IS NOT INITIAL.
PERFORM picture.
ENDIF.
ls_table_settings-table_style = zcl_excel_table=>builtinstyle_medium5.
" ALV Starting below Pricture
ls_table_settings-top_left_row = 10.
IF p_2alv IS NOT INITIAL
OR p_err IS NOT INITIAL.
lo_worksheet->bind_table( ip_table = lt_test
is_table_settings = ls_table_settings
it_field_catalog = lt_field_catalog ).
ENDIF.
* lo_column = lo_worksheet->get_column( ip_column = 'D' ). "make date field a bit wider
* lo_column->set_width( ip_width = 13 ).
*--------------------------------------------------------------------*
" Second sheet
*--------------------------------------------------------------------*
lo_worksheet = lo_excel->add_new_worksheet( ).
" Get active sheet
lo_worksheet = lo_excel->get_active_worksheet( ).
lo_worksheet->set_title( '2 Internal table' ).
* "cond style
* lo_style_cond = lo_worksheet->add_new_style_cond( ).
* lo_style_cond->rule = zcl_excel_style_cond=>c_rule_iconset.
* lo_style_cond->mode_iconset = ls_iconset.
* lo_style_cond->priority = 1.
* " Add Picture
IF p_2pic IS NOT INITIAL
OR p_err IS NOT INITIAL.
PERFORM picture.
ENDIF.
ls_table_settings-table_style = zcl_excel_table=>builtinstyle_medium5.
" ALV Starting below Pricture
ls_table_settings-top_left_row = 10.
IF p_2alv IS NOT INITIAL
OR p_err IS NOT INITIAL.
lo_worksheet->bind_table( ip_table = lt_test
is_table_settings = ls_table_settings
it_field_catalog = lt_field_catalog ).
ENDIF.
* lo_column = lo_worksheet->get_column( ip_column = 'D' ). "make date field a bit wider
* lo_column->set_width( ip_width = 13 ).
*** Create output
lcl_output=>output( lo_excel ).
*&---------------------------------------------------------------------*
*& Form PICTURE
*&---------------------------------------------------------------------*
FORM picture .
" Get active sheet
lo_worksheet = lo_excel->get_active_worksheet( ).
* lo_worksheet->set_title( 'Sheet1' ).
lo_worksheet->set_cell( ip_column = 'B' ip_row = 2 ip_value = 'Image from web repository (SMW0)' ).
" create global drawing, set position and media from web repository
lo_drawing = lo_excel->add_new_drawing( ).
lo_drawing->set_position( ip_from_row = 3
ip_from_col = 'B' ).
ls_key-relid = 'MI'.
ls_key-objid = 'SAPLOGO.GIF'.
lo_drawing->set_media_www( ip_key = ls_key
ip_width = 166
ip_height = 75 ).
" assign drawing to the worksheet
lo_worksheet->add_drawing( lo_drawing ).
ENDFORM.
