Download the Excel file in local system or upload on AL11 in .xlsx format without abap2xlsx project

[yashoratna]

Introduction: I recently encountered a case where we needed to download an Excel file in.xlsx format (a compressed, XML-based file format) in the local system and generate it on AL11 (application server). The abap2xlsx project is not installed on our system. So, we attempted to achieve this capability using the steps outlined below.

Step 1: Get a reference for the internal table data that has to be entered into the Excel file.
Step 2: Create a field catalog table (lvc_t_fcat) with three fields particularly.

a. col_pos : The same as what is desired in the Excel file for columns.

b. fieldname: Fieldname (column name) from internal table which holds the data.

c. coltext: Column heading for each column.

Step 3: Get the result data from method cl_salv_ex_util=>factory_result_data_table by passing two of the mandatory parameters r_data and t_fieldcatalog from step 1 and step 2 respectively.

Step 4: Transform the result data from step 3 with the method  cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform by passing version. It will produce XML data which would be used to generate the .xlsx file.

Please refer to the code snippet below for detailed understanding.

DATA: lo_data_ref TYPE REF TO data.
SELECT carrid,
       connid,
       fldate,
       price,
       currency,
       planetype,
       seatsmax,
       seatsocc,
       paymentsum
  FROM sflight
  INTO TABLE (lt_sflight)
  UP TO 10 ROWS.
IF sy-subrc NE 0.
  CLEAR lt_sflight.
ENDIF.

GET REFERENCE OF lt_sflight INTO lo_data_ref.
DATA(lt_fieldcat) = VALUE lvc_t_fcat( ( col_pos = 1 fieldname = 'CARRID' coltext = 'Airline Code' )
                                      ( col_pos = 2 fieldname = 'CONNID' coltext = 'Flight Connection Number' )
                                      ( col_pos = 3 fieldname = 'FLDATE' coltext = 'Flight date' )
                                      ( col_pos = 4 fieldname = 'PRICE' coltext = 'Airfare' )
                                      ( col_pos = 5 fieldname = 'CURRENCY' coltext = 'Local currency of airline' )
                                      ( col_pos = 6 fieldname = 'PLANETYPE' coltext = 'Aircraft Type' )
                                      ( col_pos = 7 fieldname = 'SEATSMAX' coltext = 'Maximum Capacity in Economy Class' )
                                      ( col_pos = 8 fieldname = 'SEATSOCC' coltext = 'Occupied seats in economy class' )
                                      ( col_pos = 9 fieldname = 'PAYMENTSUM' coltext = 'Total of current bookings' ) ).

DATA(lo_result_data) = cl_salv_ex_util=>factory_result_data_table(
                                     r_data         = lo_data_ref
                                     t_fieldcatalog = lt_fieldcat ).

CASE cl_salv_bs_a_xml_base=>get_version( ).
  WHEN if_salv_bs_xml=>version_25.
    DATA(lv_version) = if_salv_bs_xml=>version_25.
  WHEN if_salv_bs_xml=>version_26.
    lv_version = if_salv_bs_xml=>version_26.
ENDCASE.

"Transformation of data to excel
CALL METHOD cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform
  EXPORTING
    xml_type      = if_salv_bs_xml=>c_type_xlsx
    xml_version   = lv_version
    r_result_data = lo_result_data
    xml_flavour   = if_salv_bs_c_tt=>c_tt_xml_flavour_export
    gui_type      = if_salv_bs_xml=>c_gui_type_gui
  IMPORTING
    xml           = DATA(lv_xstring).

If the xstring data needs to be downloaded locally, we can convert it to binary and download it. And to upload xstring data on AL11, we can transfer it in binary format to the application server for further processing.

DATA(lv_fullpath) = CONV fileextern( '/usr/sap/tmp/test.xlsx' ).
OPEN DATASET lv_fullpath FOR OUTPUT IN BINARY MODE. 
IF sy-subrc EQ 0.
  TRANSFER lv_xstring TO lv_fullpath.
  CLOSE DATASET lv_fullpath.
ENDIF.

Though, we wouldn't be able to see the file in AL11 as it is not a text file.

But we can cross-verify, if data is uploaded correctly by running T-code CG3Y and downloading it in the local system, again in binary format.

This brings the functionality up to par.

I appreciate your taking the time to read the article. I hope you found it enjoyable. Please feel free to offer your advice and ideas.