Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member

Hi SCN,

I'm currently working on SAP integration with Excel and want to share my developments with the SCN Community.
In this first part of the integration project I'll share my development to export the data in any SAP table to an Excel spreadsheet.

I have also plans on working out a second and a third part:

  • Part 2: Automatic daily backup/export data from multiple SAP tables to Excel
  • Part 3: Insert/update data from Excel to an SAP table.

I don't claim that this is the best/fastest way to export data to Excel, but it's a working program which can make dynamic exports of table data.

If you have any questions/remarks about this blog post, feel free to contact me :smile: .

Program output:


Upon running the program, the user gets to see the following startscreen:

In this screen the user can enter a table or press F4 to look for a table with the search help.

After choosing a table, the path input field can be accessed:

:!: A valid path and table need to be entered, else the user will get an error message.

You can press F4 in the Path field to specify a path.

The name of the file will be automatically generated to "name of table + export_to_excel + date" (can be changed).

After saving the path and executing the program the file will be created in the specified location.

Result of the exported data in Excel:

Let's dig into the coding:


Data declarations:


DATA: v_default_file_name TYPE string,


      v_filename          TYPE string,


      v_file_path         TYPE string,


      wa_table            TYPE dd02l,


      check_path          TYPE string,


      v_select            TYPE string,


      t_fieldcat          TYPE lvc_t_fcat,


      v_xml_version       TYPE string,


      v_xml_flavour       TYPE string,


      v_xstring           TYPE xstring,


      v_size              TYPE i,


      gt_bintab           TYPE solix_tab.



DATA: r_data        TYPE REF TO data,


      r_structdescr TYPE REF TO cl_abap_structdescr,


      r_table       TYPE REF TO cl_salv_table,


      r_columns     TYPE REF TO cl_salv_columns_table,


      r_aggreg      TYPE REF TO cl_salv_aggregations,


      r_result_data TYPE REF TO cl_salv_ex_result_data_table.



FIELD-SYMBOLS: <table> TYPE ANY TABLE,


               <fs_component> TYPE abap_compdescr.



PARAMETERS: p_table TYPE dd02l-tabname .


PARAMETERS: p_path  TYPE string        OBLIGATORY.




Initialization:

If the table parameter is not specified, no path may be specified:


INITIALIZATION.


  LOOP AT SCREEN.


    IF screen-name = 'P_PATH'.


      screen-input = '0'.


      MODIFY SCREEN.


      EXIT.


    ENDIF.


  ENDLOOP.




When F4 is pressed in path to select a path:

Generates a pop-up window that generates a default name.


AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_path.



  CONCATENATE p_table '_EXPORT_TO_EXCEL_' sy-datum INTO v_default_file_name.



  cl_gui_frontend_services=>file_save_dialog(


    EXPORTING


      window_title        = 'Navigate to location and enter file name'


      default_extension   = 'XLS'


      default_file_name   = v_default_file_name


      initial_directory   = 'Desktop'


      prompt_on_overwrite = 'X'


    CHANGING


      filename            = v_filename


      path                = v_file_path


      fullpath            = p_path


    EXCEPTIONS


      cntl_error          = 1


      error_no_gui        = 2


      not_supported_by_gui = 3


      OTHERS              = 4


         ).


  IF sy-subrc <> 0.


*   TODO: Error Handling


  ENDIF.




When the program gets executed:

The input parameters gets checked to see if an existing table name and a correct file path are entered.

If this is the case, data of the table will be selected, the excel output data will be build and the excel file will be exported to the path.


START-OF-SELECTION.



  TRANSLATE v_file_path TO UPPER CASE.


  CONCATENATE v_file_path v_default_file_name '.XLS' INTO check_path.



  SELECT SINGLE tabname INTO wa_table FROM dd02l


        WHERE tabname   EQ p_table


          AND tabclass  EQ 'TRANSP'.



  IF sy-subrc NE 0.


    MESSAGE i000(your message class here). "If table does not exist


    EXIT.


  ENDIF.



  IF p_path EQ check_path.



*   Select all data


    PERFORM get_table_data.


*   Build excel output data


    PERFORM build_excel_data.


*   Export excel file


    PERFORM export_excel.



  ELSE.


    MESSAGE i001(your message class here). "Invalid path


    EXIT.


  ENDIF.




Form get_table_data: will get all the data of the table specified as parameter (dynamically)


FORM get_table_data.



  CREATE DATA r_data TYPE STANDARD TABLE OF (p_table).


  ASSIGN r_data->* TO <table>.



* Get all columns for select


  r_structdescr ?= cl_abap_structdescr=>describe_by_name( p_table ).


  IF r_structdescr IS BOUND.


    LOOP AT r_structdescr->components[] ASSIGNING <fs_component>.


      CONCATENATE v_select <fs_component>-name INTO v_select SEPARATED BY space.


    ENDLOOP.


  ENDIF.



* Select all data


  SELECT (v_select) FROM (p_table) INTO TABLE <table>.



ENDFORM.                    "get_table_data




Form build_excel_data:

In this form, the SAP data will be converted to XML (xstring) which is needed for the export.


FORM build_excel_data.



  TRY.


      cl_salv_table=>factory(


      EXPORTING


        list_display = abap_false


      IMPORTING


        r_salv_table = r_table


      CHANGING


        t_table     = <table> ).


    CATCH cx_salv_msg.


  ENDTRY.



* Get columns and aggregation to create fieldcatalog


  r_columns  = r_table->get_columns( ).


  r_aggreg   = r_table->get_aggregations( ).


  t_fieldcat = cl_salv_controller_metadata=>get_lvc_fieldcatalog(


                                r_columns     = r_columns


                                r_aggregations = r_aggreg ).



* Create result data table


  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.



    r_result_data = cl_salv_ex_util=>factory_result_data_table(


        r_data                     = r_data


        t_fieldcatalog             = t_fieldcat


    ).



* Get XML version


    CASE cl_salv_bs_a_xml_base=>get_version( ).


      WHEN if_salv_bs_xml=>version_25.


        v_xml_version = if_salv_bs_xml=>version_25.


      WHEN if_salv_bs_xml=>version_26.


        v_xml_version = if_salv_bs_xml=>version_26.


    ENDCASE.



* Get XML flavour


    v_xml_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export.



* Create excel data


    CALL METHOD cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform


      EXPORTING


        xml_type     = if_salv_bs_xml=>c_type_mhtml


        xml_version  = v_xml_version


        r_result_data = r_result_data


        xml_flavour  = v_xml_flavour


        gui_type     = if_salv_bs_xml=>c_gui_type_gui


      IMPORTING


        xml          = v_xstring.


  ENDIF.


ENDFORM.                    "build_excel_data




Form export_excel:

Last but not least, this form will use the XML data (xstring) to create a binary file (Excel) in the specified path.


FORM export_excel.



  IF v_xstring IS NOT INITIAL.


    CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'


      EXPORTING


        buffer       = v_xstring


      IMPORTING


        output_length = v_size


      TABLES


        binary_tab   = gt_bintab.



    CALL METHOD cl_gui_frontend_services=>gui_download


      EXPORTING


        bin_filesize           = v_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.


*  MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO


*             WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.


    ENDIF.


  ENDIF.



ENDFORM.                    "export_excel




I hope that this blog was useful for some of you and feel free to contact me with suggestions, questions or remarks.

I will post an update here when the second part of the SAP & Excel Integration has been posted.

Have a nice day.

Kind regards,

Niels

19 Comments
Labels in this area