Download sap abap internal table to excel with column headings

      Many times abap consultant, get the requirement, to download the internal table data into excel with column headings.

      Here in this blogs, i am sharing the code, which we can use based on our requirement, I have create the program by taking some columns from the FI table BSID, and based on the customer code entered on the selection screen, data get downloaded into excel.

report zkt_internal_table_to_excel.

"types declaration
types : begin of tp_list,
          bukrs type bsid-bukrs,
          kunnr type bsid-kunnr,
          zuonr type bsid-zuonr,
          budat type bsid-budat,
          waers type bsid-waers,
          dmbtr type bsid-dmbtr,
        end of tp_list.

"internal table and work area.
data : gt_list type standard table of tp_list,
       gs_list type tp_list.

"for selection screen select option declaration
data : gv_kunnr type bsid-kunnr.


"for excel functionality
data : lr_excel_structure      type ref to data,
       lo_source_table_descr   type ref to cl_abap_tabledescr,
       lo_table_row_descriptor type ref to cl_abap_structdescr,
       lv_content              type xstring,
       lt_binary_tab           type table of sdokcntasc,
       lv_length               type i,
       lv_filename1 type string,
         lv_path     type string,
         lv_fullpath type string.

selection-screen begin of block b1 with frame title text-001.
select-options : s_kunnr for gv_kunnr.
selection-screen end of block b1.

start-of-selection.
  perform get_data.

  if gt_list[] is not initial.

    perform excel_instantiate.
    perform download_to_excel.

  endif.

end-of-selection.
*&---------------------------------------------------------------------*
*&      Form  GET_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form get_data.

  select bukrs kunnr zuonr budat waers dmbtr from bsid
  into corresponding fields of table gt_list where kunnr in s_kunnr.

endform.
*&---------------------------------------------------------------------*
*&      Form  EXCEL_INSTANTIATE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form excel_instantiate.

  "create data reference
  get reference of gt_list into lr_excel_structure.
  data(lo_itab_services) = cl_salv_itab_services=>create_for_table_ref( lr_excel_structure ).
  lo_source_table_descr ?= cl_abap_tabledescr=>describe_by_data_ref( lr_excel_structure  ).
  lo_table_row_descriptor ?= lo_source_table_descr->get_table_line_type( ).

  "excel instantiate
  data(lo_tool_xls) = cl_salv_export_tool_ats_xls=>create_for_excel(
                            exporting r_data =  lr_excel_structure  ) .

  "Add columns to sheet
  data(lo_config) = lo_tool_xls->configuration( ).

  lo_config->add_column(
      exporting
        header_text          =  'Company Code'
        field_name           =  'BUKRS'
        display_type         =   if_salv_bs_model_column=>uie_text_view ).

  lo_config->add_column(
      exporting
        header_text          =  'Customer ID'
        field_name           =  'KUNNR'
        display_type         =   if_salv_bs_model_column=>uie_text_view ).

  lo_config->add_column(
       exporting
         header_text          =  'Assignment No.'
         field_name           =  'ZUONR'
         display_type         =   if_salv_bs_model_column=>uie_text_view ).


  lo_config->add_column(
        exporting
          header_text          =  'Posting Date'
          field_name           =  'BUDAT'
          display_type         =   if_salv_bs_model_column=>uie_text_view ).

  lo_config->add_column(
        exporting
          header_text          =  'Currency'
          field_name           =  'WAERS'
          display_type         =   if_salv_bs_model_column=>uie_text_view ).

  lo_config->add_column(
        exporting
          header_text          =  'Amount'
          field_name           =  'DMBTR'
          display_type         =   if_salv_bs_model_column=>uie_text_view ).


  "get excel in xstring
  try.
      lo_tool_xls->read_result(  importing content  = lv_content  ).
    catch cx_root.
  endtry.


  call function 'SCMS_XSTRING_TO_BINARY'
    exporting
      buffer        = lv_content
    importing
      output_length = lv_length
    tables
      binary_tab    = lt_binary_tab.

endform.
*&---------------------------------------------------------------------*
*&      Form  DOWNLOAD_TO_EXCEL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form download_to_excel.

    CONCATENATE 'Customer Open Items' sy-datum sy-uzeit into LV_FILENAME1 SEPARATED BY '_'.

  call method cl_gui_frontend_services=>file_save_dialog
    exporting
      window_title      = 'Enter File Name'
      default_extension = 'XLSX'
      default_file_name = LV_FILENAME1
    changing
      filename          = lv_filename1
      path              = lv_path
      fullpath          = lv_fullpath.


  if lv_fullpath is NOT INITIAL.

  call function 'GUI_DOWNLOAD'
    exporting
      bin_filesize            = lv_length
      filename                = lv_fullpath
      filetype                = 'BIN'
    tables
      data_tab                = lt_binary_tab
    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.
* Implement suitable error handling here
  else.

    call method cl_gui_frontend_services=>execute
      exporting
        document               = lv_fullpath
*        application            =
*        parameter              =
*        default_directory      =
*        maximized              =
*        minimized              =
*        synchronous            =
*        operation              = 'OPEN'
      exceptions
        cntl_error             = 1
        error_no_gui           = 2
        bad_parameter          = 3
        file_not_found         = 4
        path_not_found         = 5
        file_extension_unknown = 6
        error_execute_failed   = 7
        synchronous_failed     = 8
        not_supported_by_gui   = 9
        others                 = 10
            .
    if sy-subrc <> 0.
*     Implement suitable error handling here
    endif.

  endif.

  endif.


endform.