Ir al contenido principal

ABAP - Crear XLS de fondo (JOB)

Si ejecutamos un programa de fondo y queremos crear un XLS, no se nos permite, entonces, dependiendo de la version de SAP, podemos hacer algo.

Tomamos del codigo que utiliza ALV, para bajar a XLS o mas bien, MHTML, que Excel lo puede abrir.

Paso mi código, copiado y adaptado de abapblog.com:


report  ytest001.


*----------------------------------------------------------------------*

*       CLASS zcl_xls DEFINITION

*----------------------------------------------------------------------*

*

*----------------------------------------------------------------------*

class zcl_xls definition.


  public section.


    methods: create_xls_from_itab

             importing  it_fieldcat type lvc_t_fcat optional

                        it_sort     type lvc_t_sort optional

                        it_filt     type lvc_t_filt optional

                        is_layout   type lvc_s_layo optional

                        i_xlsx      type flag optional

              exporting e_xstring   type xstring

              changing  ct_data     type standard table.


endclass.                    "zcl_xls DEFINITION

*----------------------------------------------------------------------*

*       CLASS zcl_xls IMPLEMENTATION

*----------------------------------------------------------------------*

*

*----------------------------------------------------------------------*

class zcl_xls 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_xxl. "c_type_xlsx. 

      else.

        m_file_type = if_salv_bs_xml=>c_type_mhtml. "c_type_mhtml. 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.                    "create_xls_from_itab

endclass.                    "zcl_xls IMPLEMENTATION


data: gt_file type filetable with header line.

data: g_rc type i.

data: gt_spfli type standard table of spfli,

      gt_zaaprg515 type standard table of zaaprg515.

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.


    field-symbols: <sp> type spfli,

                   <z> type zaaprg515.

  data gr_xls1 type ref to zcl_xls.


at selection-screen on value-request for p_path.


  cl_gui_frontend_services=>file_save_dialog(

    exporting

*      window_title         = window_title

      default_extension    = 'MHTML' "'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.


  create object gr_xls1.


  gr_xls1->create_xls_from_itab(

    exporting

      i_xlsx            = ' '

    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.


Fuentes:

(versiones viejas de SAP)

https://abapblog.com/articles/tricks/33-create-xlsx-mhtml-file-from-internal-table-in-background

(versiones nuevas de SAP)

https://abapblog.com/articles/tricks/120-create-xlsx-file-from-internal-table-in-background-v2

Para indagar un poco más, hay un proyecto desarrollado también:

https://blogs.sap.com/2010/07/12/abap2xlsx-generate-your-professional-excel-spreadsheet-from-abap/

http://ww1.abap2xlsx.org

Comentarios

Publicar un comentario

Entradas más populares de este blog

A to Z of OLE Excel in ABAP 7.4

  SAP users, both business and end users always need to download the output of a report to spreadsheet and do their analytics.   The standard excel output from a report is very simple process but it is old fashioned and the spreadsheet looks quite boring.  There is no default formatting and the users have to do all the hard work of changing the fonts, coloring the texts, marking the borders etc. Updated 16th Aug 2019  –  If you are working in non-ABAP 7.4 (below), there is complete reference program for you too. Go to the end of this article.  Thank you  Legxis  ( LeonievonK ) for the share. I acknowledge, whatever I mentioned above can be achieved in many ways programmatically. We can do it in the old traditional ABAP way but  providing multiple tabs in the spreadsheet and formatting is quite tricky with non  OLE  method. OLE = Object Linking and Embedding The high level agenda of this article is to be the  G.O.A.T.  (please google it if you do not know the full form)  of OLE Excel Gu
Publicar un comentario
Leer más
How to add custom field in Additional B Tab for SAP Sales Order In this article we want to explain step by step how to add custom field in SAP Sales Order transaction VA01/VA02/VA03. This time Mr ABAPGurus will give the tutorial of enhancement to add custom field in SAP Sales order. In this sample we will add new custom field for comments and customer satisfaction, this data will save into separate table from SAP Standard table ( VBAK / VBAP ). SAP provided us with ADDITIONAL TAB in the sales order transaction ( VA01/VA02/VA03 ) which allow customer to add custom fields.  T he different between ADDITIONAL A and ADDITIONAL B is the ADDITIONAL A for field which already predefine in Sales Order Header ( VBAK ) fields and ADDITIONAL B is for field that freely define. This sample we will use ADDITIONAL B because we will store data from customer satisfaction into Z database table. 1. Create one Z table using SE11 SAP Transaction code. 2.Using SE38 Transaction code and ope
Publicar un comentario
Leer más

VOFM

Creating VOFM custom routine, functionality same as SAP standard routine 103 with Additional Functionality 1.    Introduction 1.1   Document Purpose 1.2    This is the document helps you to create custom routine for sales order for below specific requirement: Functional description: In case of sale order with the SD document category ‘K’ or ‘L’ or ‘H’ related to an invoice, we need to fill the fixed value date (VBKD_VALDT) with the billing reference date (VBRK_FKDAT). For example: Invoice 90001111, the billing date is 22.07.2013 If we create a sale order related to this invoice, we want the fixed value date equal to the billing reference date. As per the requirement: Sap standard routine ‘103’ triggers whenever sales order is being created with reference. Sap standard routine is configured in VOFM transaction as: Sap standard routine ‘103’ has the functionality of copying the billing document header data and billing document line item data into the
Publicar un comentario
Leer más