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 🙂 .

Program output:


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

1.JPG

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:

2.JPG

❗ 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).

3.JPG

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

Result of the exported data in Excel:

5.JPG

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

To report this post you need to login first.

19 Comments

You must be Logged on to comment or reply to a post.

  1. Kurt Ranft

    Hi Niels,

    Nice to see that you dived realy into it. Nice Blog, helpful and handy for almost any project.

    Looking forward to part 2  and more.

    Kind Regards

    Kurt

    (0) 
  2. Clinton Jones

    Niels

    It would also be interesting to see how you would define and propose to outline the security authorizations associated with this. How would you propose to prevent a user from being able to dump any table they like and any amount of data ? Especially given the technical limitations of Excel at 1m rows.

    Clinton

    (0) 
    1. Niels De Greef Post author

      Hello Clinton,

      This is indeed true, for the moment any user can execute this program and get the data from any table. As this is just a test program, you can modify it to your own needs.

      For authorization I would just add another check and querry the authorization table to see of the program executer (sy-uname) is authorized to execute this program.

      To exceed the limitation of Excel, I would build in another check which will hold the amount of records present in a table. If this amount exceeds the Excel limitation, I would create 2 excel files.

      As this is just a basic tutorial, I didn’t go in depth.
      It’s just a starting point for improvement let’s say 🙂

      Regards,

      Niels

      (0) 
  3. Paul Hardy

    As always when I read the latest blog explaining how to do this I can only congratulate you on working this out.

    Last week when someone published a similar blog about doing the same sort of thing I foolishly raised the subject of ABAP2XLS.

    http://ivanfemia.github.io/abap2xlsx/

    On that occasion the guy said that in his current client they weren’t allowed to use open source things like ABAP2XLS.

    Have you heard of ABAP2XLS?

    Cheersy Cheers

    Paul

    (0) 
    1. Niels De Greef Post author

      Hi Paul,

      Thank you for your feedback.

      I’ve never heard of that open source project and will definately check it out, thank you!

      Regards,

      Niels

      (0) 
  4. Selva A

    Niels, Nice post. This is indeed needed for almost all the projects for testing purposes. But we should add more security related things if we decide to use it in PROD.

    (0) 
    1. Niels De Greef Post author

      Yes indeed, its a basic starting program, to give an idea what is possible.

      To get it into PROD a lot of factors have to be taken into account:

      • export limit of excel data -> create multiple files
      • Security checks
      (0) 
  5. Devesh Kapse

    Hello Niels,

    I am new to ABAP. For my scenario I used above code. It creates the excel but when I

    try to open it following error appears :

    The file you are trying to open , ‘XYZ.xls’, is in different format than specified by the file extension.

    Could you help me on this?

    Capture.PNG

    (0) 
    1. Shai Sinai

      Well,

      I’m not Niels De Greef,

      but as far as I can tell, the one and only purpose of this tool is exporting any DB table contents to Excel (what can also be achieved in SE16N, for a matter of fact).

      It uses internal ALV method (cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform) for this purpose, but could use ABAP2XLSX instead (According to Niels comment, he wasn’t aware of ABAL2XLSX when he implemented this solution).

      (0) 
  6. Martin Fischer

    There is also a new tool available which can be used to integrate SAP data with Microsoft Office tools. It’s based on SAP Gateway and it is called SAP Gateway for Microsoft. If you are already working with Gateway, you should have a look on it!

    (0) 

Leave a Reply