Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
This blog will provide detailed steps to generate excel for multiple OData entities in a Fiori application.

In current world of SAP S/4HANA and Fiori application, there is a common scenario where customers mostly wants to download full data for an application from the list view where all the records are visible, however its is always been performance impact on application as if UI will load full data at time to download in an excel.

If there are 5000 records and user wants to download all records on click of download button, the conventional way of doing this is to download it directly from UI with loading all records at once, which will take approximately 30 minutes. Other way to do this is load records with every page scrolling(by sending page scrolling = True from UI) and on click of download button give filter information to backend on the basis of which backend will generate an excel file and returns it in the form of xstring to download it on UI. This will take only 3 seconds.

So lets start with the detailed implementation for this scenario:

The assumption is :

  • You already have a Fiori application with list view for Odata an entity

  • Paging is enables to load the list data

  • Entity type "List" is created under Project "ListProject"

  • Excel adapter class has been created using blog Excel Adapter class


Detailed steps:

  1. Create a structure "ZDownloadList" with same structure fields available in entity type "List"

    1. Add below fields in structure "ZDownloadList"



  2. Create a utility class "zexcel_utility"to generate dynamic excel and other utilities

  3. Create a method "Get_labels" to get the Odata entity property names for all the fields to set the column names in excel

  4. Following should be the parameters for this method: IV_BO Importing Type zBUSINESS_OBJECT( data_element of type char10)
    RT_LABELS Returning Type zTT_PROP_LABELS(table type of below structure :  PROPERTIE Types CHAR40 CHAR 40 0 Character field of length 40
    LABEL Types ITEX132 CHAR 132 0 Text Symbol length 132)

  5. IV_BO is required only if you want to implement this functionality for multiple business objects , then you need to add a case statement for each business object to return entity specific labels

  6. But if you only want to implement it for single entity , this parameter is not required

  7. Now add below code in method get_labels;


1.DATA: lt_text_elements TYPE zcl_list_mpc=>tt_text_elements.
DATA: lt_textpool TYPE /iwbep/cl_mgw_med_model_util=>ty_t_textpool.
DATA: lt_textpool_ext TYPE /iwbep/cl_mgw_med_model_util=>ty_t_textpool.
DATA: ls_labels TYPE zs_prop_labels.DATA: lv_inclname TYPE char32.
DATA: lv_incl_extname TYPE char32.
DATA: lv_entity_name TYPE /iwbep/med_external_name.
TRY.
CASE iv_bo.

WHEN zif_common_constants=>List.
lt_text_elements = NEW zcl_list_mpc_ext( )->load_text( ).
lv_inclname = zif_common_constants=>co_incl_name-list. (MPC class name)
lv_incl_extname = zif_common_constants=>co_incl_ext_name-list (MPC EXt class name).
lv_entity_name = gc_downloadlist.

WHEN OTHERS.
RETURN.
ENDCASE.


READ TEXTPOOL lv_inclname LANGUAGE sy-langu INTO lt_textpool.

READ TEXTPOOL lv_incl_extname LANGUAGE sy-langu INTO lt_textpool_ext.

APPEND LINES OF lt_textpool_ext TO lt_textpool.

SORT lt_textpool BY key.

LOOP AT lt_text_elements ASSIGNING FIELD-SYMBOL(<ls_text_elements>) WHERE parent_artifact_name = lv_entity_name.
ls_labels-propertie = to_upper( val = <ls_text_elements>-artifact_name ).

READ TABLE lt_textpool ASSIGNING FIELD-SYMBOL(<ls_textpool>)
WITH KEY key = <ls_text_elements>-text_symbol BINARY SEARCH.
IF sy-subrc IS INITIAL.
ls_labels-label = <ls_textpool>-entry.
ENDIF.
APPEND ls_labels TO rt_labels.
CLEAR ls_labels.
ENDLOOP.

CATCH /iwbep/cx_mgw_med_exception .
RETURN.
ENDTRY.


9.Create method "GENERATE_DYNAMIC_EXCEL_TAB" with below parameters :IV_BO Importing Type zBUSINESS_OBJECT
IT_DATA Importing Type Ref To DATA
IT_SELECTED_FIELDS Importing Type TABLE
RT_DATA Returning Type Ref To DATA

10. again iv_BO is to handle multiple objects

11. paste below code:
1.METHOD generate_dynamic_excel_tab.DATA lt_fieldcat TYPE lvc_t_fcat.
DATA lo_struct TYPE REF TO cl_abap_structdescr.
DATA lt_dytable TYPE REF TO data.
DATA ls_fieldcat LIKE LINE OF lt_fieldcat.
DATA lv_price_dec31 TYPE zpam_price_out.
DATA lv_date_str TYPE char10.
DATA lv_time_str TYPE char10.
DATA ls_list TYPE ZDownloadList.


FIELD-SYMBOLS <lt_data_str> TYPE ANY TABLE.
FIELD-SYMBOLS <lt_data> TYPE ANY TABLE.
FIELD-SYMBOLS <lv_field> TYPE any.
FIELD-SYMBOLS <lv_field_out> TYPE any.
FIELD-SYMBOLS <lv_field_str> TYPE any.
FIELD-SYMBOLS <lv_field_currency> TYPE any.
FIELD-SYMBOLS <lv_field_uom> TYPE any.

TRY.

* We need to get field descriptions from dictionary structure
* and can't get it from dynamic reference variable.
CASE iv_bo.
WHEN List.
lo_struct ?= cl_abap_typedescr=>describe_by_data( ls_list ).
WHEN OTHERS.
EXIT.
ENDCASE.

DATA(lt_field_list) = lo_struct->get_ddic_field_list( ).

* Create dynamic table (columns are ordered by selected fields order)
LOOP AT it_selected_fields ASSIGNING FIELD-SYMBOL(<ls_selected_fields>).
READ TABLE lt_field_list ASSIGNING FIELD-SYMBOL(<ls_field_list>) WITH KEY fieldname = <ls_selected_fields>."<ls_properties>-technical_name.
IF sy-subrc IS INITIAL.

MOVE-CORRESPONDING <ls_field_list> TO ls_fieldcat.

CASE <ls_field_list>-datatype.
WHEN 'CHAR'.
ls_fieldcat-datatype = 'CHAR'.
ls_fieldcat-intlen = <ls_field_list>-outputlen.
IF ls_fieldcat-intlen < 10.
ls_fieldcat-intlen = 10.
ENDIF.
WHEN 'NUMC'.
ls_fieldcat-datatype = 'CHAR'.
WHEN 'DATS' OR 'TIMS'.
ls_fieldcat-datatype = 'CHAR'.
ls_fieldcat-inttype = 'C'.
ls_fieldcat-intlen = '10'.
WHEN 'CURR'.
ls_fieldcat-datatype = 'CHAR'.
ls_fieldcat-inttype = 'C'.
ls_fieldcat-intlen = <ls_field_list>-outputlen.
WHEN 'QUAN'.
ls_fieldcat-datatype = 'CHAR'.
ls_fieldcat-inttype = 'C'.
ls_fieldcat-intlen = <ls_field_list>-outputlen.
WHEN 'CUKY' OR 'UNIT'.
ls_fieldcat-datatype = 'CHAR'.
ls_fieldcat-inttype = 'C'.
ls_fieldcat-intlen = <ls_field_list>-outputlen.
WHEN 'D34D'.
ls_fieldcat-datatype = 'CHAR'.
ls_fieldcat-inttype = 'C'.
ls_fieldcat-intlen = '32'.
WHEN 'RAW'.
ls_fieldcat-datatype = 'CHAR'.
ls_fieldcat-inttype = 'C'.
ls_fieldcat-intlen = '32'.
WHEN 'DEC'.
ls_fieldcat-datatype = 'CHAR'.
ls_fieldcat-inttype = 'C'.
ls_fieldcat-intlen = <ls_field_list>-outputlen.
WHEN 'INT4'.
ls_fieldcat-datatype = 'CHAR'.
ls_fieldcat-inttype = 'C'.
ls_fieldcat-intlen = '20'.
WHEN OTHERS.
ls_fieldcat-datatype = 'CHAR'.
ls_fieldcat-inttype = 'C'.
ls_fieldcat-intlen = <ls_field_list>-outputlen.

ENDCASE.
APPEND ls_fieldcat TO lt_fieldcat.
ENDIF.
ENDLOOP.
CALL METHOD cl_alv_table_create=>create_dynamic_table
EXPORTING
it_fieldcatalog = lt_fieldcat
IMPORTING
ep_table = lt_dytable
EXCEPTIONS
generate_subpool_dir_full = 1
OTHERS = 2.
IF sy-subrc <> 0.
RETURN.
ENDIF.

ASSIGN lt_dytable->* TO <lt_data_str>.
ASSIGN it_data->* TO <lt_data>.

* Fill generated table with applying conversion rules
IF <lt_data_str> IS ASSIGNED AND <lt_data> IS ASSIGNED.

LOOP AT <lt_data> ASSIGNING FIELD-SYMBOL(<ls_data>).
INSERT INITIAL LINE INTO TABLE <lt_data_str> ASSIGNING FIELD-SYMBOL(<ls_data_str>).

LOOP AT lt_field_list ASSIGNING <ls_field_list>.
UNASSIGN <lv_field>.
UNASSIGN <lv_field_str>.
ASSIGN COMPONENT <ls_field_list>-fieldname OF STRUCTURE <ls_data> TO <lv_field>.
IF sy-subrc <> 0 OR <lv_field> IS NOT ASSIGNED.
CONTINUE.
ENDIF.
ASSIGN COMPONENT <ls_field_list>-fieldname OF STRUCTURE <ls_data> TO <lv_field_out>.
IF sy-subrc <> 0 OR <lv_field_out> IS NOT ASSIGNED.
CONTINUE.
ENDIF.
ASSIGN COMPONENT <ls_field_list>-fieldname OF STRUCTURE <ls_data_str> TO <lv_field_str>.
IF sy-subrc <> 0 OR <lv_field_str> IS NOT ASSIGNED.
CONTINUE.
ENDIF.

* Convert using conversion exit

IF <ls_field_list>-convexit IS NOT INITIAL.
DATA(lv_conv_fm) = 'CONVERSION_EXIT_' && <ls_field_list>-convexit && '_OUTPUT'.
CALL FUNCTION lv_conv_fm
EXPORTING
input = <lv_field>
IMPORTING
output = <lv_field_out>.
<lv_field_str> = <lv_field_out>.

ELSE.
WRITE <lv_field> TO <lv_field_str>.
ENDIF.

* Apply special conversion rules
CASE <ls_field_list>-datatype.
WHEN 'DATS'.
IF <lv_field> IS NOT INITIAL.
CALL FUNCTION 'CONVERSION_EXIT_PDATE_OUTPUT'
EXPORTING
input = <lv_field>
IMPORTING
output = <lv_field_str>.
ELSE.
CLEAR <lv_field_str>.
ENDIF.

WHEN 'TIMS'.
IF <lv_field> IS NOT INITIAL.
WRITE <lv_field> TO <lv_field_str>.
ELSE.
CLEAR <lv_field_str>.
ENDIF.
WHEN 'DEC'.
IF <ls_field_list>-domname = 'TZNTSTMPS'.
IF <lv_field> IS NOT INITIAL.
* Convert Timestamp
CONVERT TIME STAMP <lv_field> TIME ZONE ''
INTO DATE DATA(lv_date)
TIME DATA(lv_time).

CALL FUNCTION 'CONVERSION_EXIT_PDATE_OUTPUT'
EXPORTING
input = lv_date
IMPORTING
output = lv_date_str.

WRITE lv_time TO lv_time_str.<lv_field_str>

= lv_date_str && ','.
CONCATENATE <lv_field_str> lv_time_str+0(5) INTO <lv_field_str> SEPARATED BY space.
ELSE.
CLEAR <lv_field_str>.
ENDIF.

ENDIF.
ENDCASE.
ENDLOOP.
ENDLOOP.
* Send table to output
GET REFERENCE OF <lt_data_str> INTO rt_data.
ENDIF.

CATCH cx_root.
RETURN.
ENDTRY.

ENDMETHOD.


12. use class "ZTEST_ADPATER" to create dynamic excel in following steps

13. Now create method "DOWNLOAD_LIST_DATA" with following parameter :

14. IT_DATA Importing Type Ref To DATA
IT_HEADER Importing Type zTT_ODATA_PROPERTIES ( table type with following structure fields :  )
IT_SELECTED_FIELDS Importing Type STRING_TABLE
IV_SHEET_NAME Importing Type STRING
IV_DISP_HEADER Importing Type BOOLE_D
IV_BO Importing Type zBUSINESS_OBJECT (type char10)
ES_FILECONTENT Exporting Type zS_DOWNL_LIST_OUT ( structure with following fields: )

15. paste following code in this method :
  METHOD download_list_data.
DATA lt_excel_header TYPE ZTEST_ADPATER=>tt_header_table.
DATA lt_data_desc TYPE ZTEST_ADPATER=>tt_data_desc.
DATA lo_excel TYPE REF TO ZTEST_ADPATER.
DATA lt_dyn_data TYPE REF TO data.
FIELD-SYMBOLS <lt_dyn_data> TYPE STANDARD TABLE.


DATA(lt_selected_fields) = it_selected_fields.

CREATE OBJECT lo_excel.

* Delete technical fields
DELETE lt_selected_fields WHERE table_line EQ 'GENERATED_ID'
OR table_line EQ 'FILE_NAME'
OR table_line EQ 'MIMETYPE'
OR table_line EQ 'FILE_SIZE'
OR table_line EQ 'CONTENT'
OR table_line CP '*GUID'.

* Build Dynamic Excel Heaer
DATA(lt_labels) = zexcel_utility=>get_labels( iv_bo = iv_bo ).
LOOP AT lt_selected_fields ASSIGNING FIELD-SYMBOL(<ls_selected_fields>).
INSERT INITIAL LINE INTO TABLE lt_excel_header ASSIGNING FIELD-SYMBOL(<ls_excel_header>).
READ TABLE it_header ASSIGNING FIELD-SYMBOL(<ls_properties>) WITH KEY technical_name = <ls_selected_fields>.
IF sy-subrc IS INITIAL.
<ls_excel_header>-field_name = <ls_properties>-property->get_name( ).
ENDIF.
TRANSLATE <ls_excel_header>-field_name TO UPPER CASE.
READ TABLE lt_labels ASSIGNING FIELD-SYMBOL(<ls_labels>) WITH KEY propertie = <ls_excel_header>-field_name.
IF sy-subrc = 0.
<ls_excel_header>-field_name = <ls_labels>-label.
ENDIF.
ENDLOOP.
* Build Dynamic table data
lt_dyn_data = zexcel_utility=>generate_dynamic_excel_tab( iv_bo = iv_bo
it_data = it_data
it_selected_fields = lt_selected_fields ).
ASSIGN lt_dyn_data->* TO <lt_dyn_data>.
* Create Excel sheet with dynamic data
lo_excel->create_sheet(
EXPORTING
iv_sheet_name = iv_sheet_name
it_header = lt_excel_header
iv_disp_header = iv_disp_header
it_table_data = <lt_dyn_data> ).

* Final excel sheet
CLEAR es_filecontent.

lo_excel->prepare_for_download(
RECEIVING
rv_xlsx_xml = es_filecontent-content ).


es_filecontent-mimetype = zif_common_constants=>co_mime_type-xlsx.
es_filecontent-file_name = iv_sheet_name && zif_common_constants=>co_file_extension-xlsx.
es_filecontent-file_size = xstrlen( es_filecontent-content ).

ENDMETHOD.​

16. Go to transaction SEGW and copy Entity Type "List" under  project "ListProject", you can name  it "DownloadList"

17. Now add below properties in your entity type in alignment with structure fields added in step 2 :

18. Go to DPC Ext class of project  "ListProject" and redefine method DOWNLOADLIST_GET_ENTITYSET and paste following code  :
  DATA:
lo_facade TYPE REF TO /iwbep/cl_mgw_dp_facade,
lo_model TYPE REF TO /iwbep/if_mgw_odata_re_model,
lo_entity_type TYPE REF TO /iwbep/if_mgw_odata_re_etype,
lt_properties TYPE /iwbep/if_mgw_odata_re_prop=>ty_t_mgw_odata_properties.

DATA:
ls_downl_list_inp TYPE zs_downl_list_inp.

CALL METHOD super->downloadlist_get_entityset
EXPORTING
iv_entity_name = iv_entity_name
iv_entity_set_name = iv_entity_set_name
iv_source_name = iv_source_name
it_filter_select_options = it_filter_select_options
is_paging = is_paging
it_key_tab = it_key_tab
it_navigation_path = it_navigation_path
it_order = it_order
iv_filter_string = iv_filter_string
iv_search_string = iv_search_string
io_tech_request_context = io_tech_request_context
IMPORTING
et_entityset = et_entityset
es_response_context = es_response_context.


DATA(lt_selected_fields) = io_tech_request_context->get_select_entity_properties( ).

* determine name of OData property assigned to ABAP field
lo_facade ?= me->/iwbep/if_mgw_conv_srv_runtime~get_dp_facade( ).
lo_model = lo_facade->/iwbep/if_mgw_dp_int_facade~get_model( ).
lo_entity_type = lo_model->get_entity_type( |{ iv_entity_name }| ).
lt_properties = lo_entity_type->get_properties( ).

GET REFERENCE OF et_entityset INTO ls_downl_list_inp-entityset.

ls_downl_list_inp-header_flds = lt_properties.

ztest_adapter=>download_list_data( EXPORTING it_data = ls_downl_list_inp-entityset
it_header = ls_downl_list_inp-header_flds
it_selected_fields = lt_selected_fields
iv_sheet_name = <sheet name>
iv_bo = < BO name>
iv_disp_header = abap_true
IMPORTING es_filecontent = DATA(ls_action_reslt)
).

CLEAR et_entityset.
APPEND INITIAL LINE TO et_entityset ASSIGNING FIELD-SYMBOL(<fs_entity_set>).
MOVE-CORRESPONDING ls_action_reslt TO <fs_entity_set>.

19. And that's it you are done !!

20. Backend implementation is completed now a exposed OData service will be able to provide excel content on entity call.                                                                                                          As mentioned in my previous blog , any i.e. Fiori app can call this OData service and download this generated excel