Pages

Buy

Buy

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.
  datamt_fcat type lvc_t_fcat.
  datamt_data       type ref to data.
  datam_flavour type string.
  datam_version type string.
  datamo_result_data type ref to cl_salv_ex_result_data_table.
  datamo_columns  type ref to cl_salv_columns_table.
  datamo_aggreg   type ref to cl_salv_aggregations.
  datamo_salv_table  type ref to cl_salv_table.
  datam_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.

datagt_file type filetable with header line.
datag_rc type i.
datagt_spfli type standard table of spfli.
datag_xstring type xstring.
datag_size type i.
datagt_bintab type solix_tab.
datag_filename type string.
datag_path type string.
"get path
parametersp_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.

1 comment: