[SAP & Excel Integration] Part 1: Backup/export data from any SAP table to Excel
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:
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
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
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
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
Nice one Niels. I completely agree with Kurt Ranft on your blog. Really waiting for second part.
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
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
Which is the preferable way of creating an excel? XML or OLE?
If possible go with Office Open XML (OOXML) format .xlsx...
Here is nice wiki on Excel in ABAP:
Excel with SAP - An overview - ABAP Development - SCN Wiki
OOXML is the preferable approach in my opinion.
As suggested have a look to the open source project abap2xlsx
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.
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:
Very nice blog
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?
Try change extension to XLSX. (XLS file type is not the same like XLSX)
Hi Tomas,
Thanks for the suggestion, But I tried it. Not working 🙁
Nice blog Niels!
I really appreciate your work and will be interested to know much more about the advantages of your development over ABAP2XLSX.
Thanks for sharing.
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).
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!