SAP ABAP - Generating Excel file for downloading or sending over email

 Creating excel content from data in an internal table? 

Getting the following message when opening an excel created out of SAP - "The file you are trying to open, ‘filename.xls’ is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

Below is the best solution. This solution is based on the approach used by standard SAP to created excel content from ALV. Once any ALV is displayed, you can get the data into excel using the menu bar option LIST -> EXPORT -> SPREADSHEET. 

If there are any questions, put them in the comments. 

Sample working code:

*&---------------------------------------------------------------------*
*& Report Y_EXCEL
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT Y_EXCEL.

DATA: lo_data         TYPE REF TO data,
      lt_fieldcatalog TYPE lvc_t_fcat,
      l_flavour       TYPE string,                              " Flavour for XML conversion
      l_version       TYPE string,                              " XML Version
      lo_result_data  TYPE REF TO cl_salv_ex_result_data_table, " Result data reference
      l_file_type     TYPE salv_bs_constant,
      lt_xml_choice   TYPE if_salv_bs_xml=>t_type_xml_choice,
      ls_xml_choice   TYPE if_salv_bs_xml=>s_type_xml_choice.

DATA: g_xstring  TYPE xstring.

*-- Get data from database into internal table
SELECT * FROM mara INTO TABLE @DATA(lt_mara) UP TO 20 ROWS.

*-- Get object reference of the internal table holding the data
GET REFERENCE OF lt_mara INTO lo_data.

*-- Get the FieldCatalog
CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
  EXPORTING
    i_structure_name       = 'MARA'      " The fields in the structure should match with those of the internal table
  CHANGING
    ct_fieldcat            = lt_fieldcatalog
  EXCEPTIONS
    inconsistent_interface = 1
    program_error          = 2
    OTHERS                 = 3.
.
IF sy-subrc EQ 0.

*-- Prepare result data object using final table data reference
*-- and the fieldcatalog
  CALL METHOD cl_salv_ex_util=>factory_result_data_table
    EXPORTING
      r_data              = lo_data
      t_fieldcatalog      = lt_fieldcatalog
    RECEIVING
      r_result_data_table = lo_result_data.

  IF lo_result_data IS NOT INITIAL.

*--- Get version of XML
    CASE cl_salv_bs_a_xml_base=>get_version( ).
      WHEN if_salv_bs_xml=>version_25.
        l_version = if_salv_bs_xml=>version_25.
      WHEN if_salv_bs_xml=>version_26.
        l_version = if_salv_bs_xml=>version_26.
      WHEN OTHERS.
        " Do nothing
    ENDCASE.

*--- Filetype: XLSX
    l_file_type = if_salv_bs_xml=>c_type_xlsx.
*--- Flavour: Export
    l_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export.

*--- Transformation of data to excel
*--- GXSTRING will be used to send the email
    CALL METHOD cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform
      EXPORTING
        xml_type      = l_file_type
        xml_version   = l_version
        r_result_data = lo_result_data
        xml_flavour   = l_flavour
        gui_type      = if_salv_bs_xml=>c_gui_type_gui
      IMPORTING
        xml           = g_xstring.


*-- Using Function Module "SCMS_XSTRING_TO_BINARY" we can convert G_XSTRING to Binary format and use that to send
*-- email using class CL_BCS


*-- Below is Sample code to download excel file
    lt_xml_choice =  cl_salv_export_xml_dialog=>get_gui_spreadsheet_formats( ).

    READ TABLE lt_xml_choice INTO ls_xml_choice WITH KEY xml_type = l_file_type.
    IF sy-subrc EQ 0.
      cl_salv_export_xml_dialog=>download( EXPORTING s_xml_choice = ls_xml_choice
                                                     xml          = g_xstring ).
    ENDIF.

  ENDIF.

ENDIF.

Ref: https://quick-drop.blogspot.com/2021/07/sap-abap-generating-xlsx-file-for.html