ABAP Code To Download Internal Table as Excel File

 ABAP Code To Download Internal Table as Excel File

In some projects we get a requirement to fetch the data from table and download it as an excel file. This can be achieved by displaying ALV report and then exporting excel file on the grid display. But sometimes the user wants to skip the display and may want to directly download the file. In such cases the below code will be very helpful.

To download the internal table data as excel file, the following steps are involved.

1) Fetch the data from DDIC table to internal table

2) Call ALV factory method to create ALV instance

3) Convert the table data to excel format

4) Convert the excel data to binary format

5) Call GUI_DOWNLOAD FM to download the file.

Code:

REPORT ztest_excel.

DATA: lt_bin   TYPE STANDARD TABLE OF solix,
      lv_size     TYPE i,
      lv_filename TYPE string.

START-OF-SELECTION.

  SELECT * FROM sflight INTO TABLE @DATA(lt_flight)
   UP TO 10 ROWS.

  TRY.
      cl_salv_table=>factory(
        IMPORTING
          r_salv_table   = DATA(lo_table)
        CHANGING
          t_table        = lt_flight
      ).
    CATCH cx_salv_msg.
  ENDTRY.

  DATA(lv_xml) = lo_table->to_xml( xml_type = if_salv_bs_xml=>c_type_xlsx ).

  "To convert XSTRING to Binary
  CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
    EXPORTING
      buffer        = lv_xml
    IMPORTING
      output_length = lv_size
    TABLES
      binary_tab    = lt_bin.

  lv_filename = 'C:\temp\flight_data.xlsx'.

  "To download Excel File
  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      bin_filesize            = lv_size
      filename                = lv_filename
      filetype                = 'BIN'
    TABLES
      data_tab                = lt_bin
    EXCEPTIONS
      file_write_error        = 1
      no_batch                = 2
      gui_refuse_filetransfer = 3
      invalid_type            = 4
      no_authority            = 5
      unknown_error           = 6
      header_not_allowed      = 7
      separator_not_allowed   = 8
      filesize_not_allowed    = 9
      header_too_long         = 10
      dp_error_create         = 11
      dp_error_send           = 12
      dp_error_write          = 13
      unknown_dp_error        = 14
      access_denied           = 15
      dp_out_of_memory        = 16
      disk_full               = 17
      dp_timeout              = 18
      file_not_found          = 19
      dataprovider_exception  = 20
      control_flush_error     = 21
      OTHERS                  = 22.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
      WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

Output:

I have gone through some blogs where they have used only GUI_DOWNLOAD to download the file, but it requires header fields in separate internal table as input. The advantage of CL_SALV_TABLE- factory method is we don’t need to explicitly prepare the header fields for GUI_DOWNLOAD.

Also, when we download the excel file the header will contain table field descriptions from DDIC. There are ALV methods to hide, optimize the width or change the descriptions as well using this method.

So, using the above the code you can convert internal table to excel format (xlsx) and download it.