Create XLSX/MHTML file from internal table in background

I think that any of us had meet the situation when we needed to create an Excel output from internal table in background. There is a really nice project called ABAP2XLSX which gives you to possibility to do all you need but in some case you won't be allowed to install ABAP2XLSX at you SAP instance. Don't worry there is a way to do it, but in older SAP version we will be allowed only to save the file as MHTML excel (like in ALV->Export to Spreadsheet). In newest version we're able save the file directly to XLSX also. In a method shown bellow you can see that to create Excel file you need to only pass internal table, the rest of the parameters are option which gives you opurtinity to pass the settings you've made in your program to the output file.

So what am I doing here.... firstly I check if the fieldcatalog was passed. If not then I create it on a base of internal table with usage of cl_salv_table  and cl_salv_controller_metadata=>get_lvc_fieldcatalog. After I finally have the fieldcatalog I create result table with sort and filter criteria if they were passed. To do that  I use class cl_salv_ex_util. At the end result table is transformed to xstring with method cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform. So you're receiving at the end of the method an xstring file so you can do what you want with it, for example send via mail to the user, or save it on local PC (like in the example found at the end). The most important is that you can use this method in a background so you can prepare some jobs for your programs and send results of them to the users!

Importing:

it_fieldcat type lvc_t_fcat optional -> field catalog for list viewer control
it_sort type lvc_t_sort optional -> alv control: table of sort criteria
it_filt type lvc_t_filt optional -> alv control: table of filter conditions
is_layout type lvc_s_layo optional -> alv control: layout structure
i_xlsx type flag optional -> create xlsx file?

Changing:

ct_data type standard table -> our internal table

Exporting:

e_xstring type xstring -> xstring with our excel file

Implementation:

method create_xls_from_itab.
  data: mt_fcat type lvc_t_fcat.
  data: mt_data       type ref to data.
  data: m_flavour type string.
  data: m_version type string.
  data: mo_result_data type ref to cl_salv_ex_result_data_table.
  data: mo_columns  type ref to cl_salv_columns_table.
  data: mo_aggreg   type ref to cl_salv_aggregations.
  data: mo_salv_table  type ref to cl_salv_table.
  data: m_file_type type salv_bs_constant.
  field-symbols <tab> type any table.

  get reference of ct_data into mt_data.

*if we didn't pass fieldcatalog we need to create it
  if it_fieldcat[] is initial.
    assign mt_data->* to <tab>.
    try .
        cl_salv_table=>factory(
        exporting
          list_display = abap_false
        importing
          r_salv_table = mo_salv_table
        changing
          t_table      = <tab> ).
      catch cx_salv_msg.

    endtry.
    "get colums & aggregation infor to create fieldcat
    mo_columns  = mo_salv_table->get_columns( ).
    mo_aggreg   = mo_salv_table->get_aggregations( ).
    mt_fcat     =  cl_salv_controller_metadata=>get_lvc_fieldcatalog(
                                  r_columns      = mo_columns
                                  r_aggregations = mo_aggreg ).
  else.
*else we take the one we passed
    mt_fcat[] = it_fieldcat[].
  endif.


  if cl_salv_bs_a_xml_base=>get_version( ) eq if_salv_bs_xml=>version_25 or
     cl_salv_bs_a_xml_base=>get_version( ) eq if_salv_bs_xml=>version_26.

    mo_result_data = cl_salv_ex_util=>factory_result_data_table(
        r_data                      = mt_data
        s_layout                    = is_layout
        t_fieldcatalog              = mt_fcat
        t_sort                      = it_sort
        t_filter                    = it_filt
    ).

    case cl_salv_bs_a_xml_base=>get_version( ).
      when if_salv_bs_xml=>version_25.
        m_version = if_salv_bs_xml=>version_25.
      when if_salv_bs_xml=>version_26.
        m_version = if_salv_bs_xml=>version_26.
    endcase.

    "if we flag i_XLSX then we'll create XLSX if not then MHTML excel file
    if i_xlsx is not initial.
      m_file_type = if_salv_bs_xml=>c_type_xlsx.
    else.
      m_file_type = if_salv_bs_xml=>c_type_mhtml.
    endif.


    m_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export.
    "transformation of data to excel
    call method cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform
      exporting
        xml_type      = m_file_type
        xml_version   = m_version
        r_result_data = mo_result_data
        xml_flavour   = m_flavour
        gui_type      = if_salv_bs_xml=>c_gui_type_gui
      importing
        xml           = e_xstring.
  endif.
endmethod.

Example of use:

report  zab_mhtml_xls.

data: gt_file type filetable with header line.
data: g_rc type i.
data: gt_spfli type standard table of spfli.
data: g_xstring type xstring.
data: g_size type i.
data: gt_bintab type solix_tab.
data: g_filename type string.
data: g_path type string.
"get path
parameters: p_path type string obligatory.


at selection-screen on value-request for p_path.

  cl_gui_frontend_services=>file_save_dialog(
    exporting
*      window_title         = window_title
      default_extension    = 'XLS'
*      default_file_name    = default_file_name
*      with_encoding        = with_encoding
*      file_filter          = file_filter
*      initial_directory    = initial_directory
*      prompt_on_overwrite  = 'X'
    changing
      filename             = g_filename
      path                 = g_path
      fullpath             = p_path
*      user_action          = user_action
*      file_encoding        = file_encoding
    exceptions
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      others               = 4
         ).
  if sy-subrc <> 0.
*   Implement suitable error handling here
  endif.

start-of-selection.
  "fill table with data
  select * from spfli into corresponding fields of table gt_spfli.

  "call creation of xls
  zcl_abapblog_com=>create_xls_from_itab(
*    exporting
*    it_fieldcat       = it_fieldcat
*    it_sort           = it_sort
*    it_filt           = it_filt
*    is_layout         = is_layout
*    i_xlsx             = 'X'
    importing
      e_xstring         = g_xstring
    changing
      ct_data           =  gt_spfli
         ).

  if g_xstring is not initial.
    "save file
    call function 'SCMS_XSTRING_TO_BINARY'
      exporting
        buffer          = g_xstring
*       APPEND_TO_TABLE = ' '
      importing
        output_length   = g_size
      tables
        binary_tab      = gt_bintab.

    cl_gui_frontend_services=>gui_download(
      exporting
        bin_filesize              = g_size
        filename                  = p_path
        filetype                  = 'BIN'
      changing
        data_tab                  = gt_bintab
      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
        not_supported_by_gui      = 22
        error_no_gui              = 23
        others                    = 24
           ).
    if sy-subrc <> 0.
*     Implement suitable error handling here
    endif.

  endif.