Technical Articles
Excel (xlsx) Table Maintenance
Introduction
In 2007, Microsoft embraced an open standard for Excel with the xlsx format. Google Docs is also interchangeable with this format. Searching for Excel upload and download ABAP programs, which support the open xlsx format can sometimes be time consuming. Here is a program which provides all of the code to upload and download an xlsx file. Most ABAP developers are aware of the ultimate solution, which is the open source abap2xlsx project. However, sometimes that may be difficult to install at a large customer, where the security organization rules. If you simply want to generate an xlsx spreadsheet, and read in a spreadsheet, the abap2xlsx library may be overkill. Standard SAP libraries are used here.
This Blog demonstrates a program that allows you to maintain a Z Table in SAP by entering data in an Excel spreadsheet in the xlsx format. This Blog will be useful for both new ABAPers, as well as experienced ABAPers trying to get familiar with the new ABAP language features. Within the program, code snippets are included which will show old ABAP syntax versus the new ABAP syntax (7.4+).
If you want to simply get the code and run it, then jump to the code at the end of this Blog.
This program was built to support this Blog for a Kotlin Application.
This Blog is organized as follows:
- Program Overview
- End User Walk through
- Code Overview
- Create a Custom Table
- Download – Extract and auto-open in Excel
- Upload – Refresh your Z Table from Excel
- Import a CDS View into Excel
- Complete Program
- References
Software Versions:
- Application Server ABAP 7.54 SP00, SAPGUI Version 760.
- Eclipse Version: Version: 2019-03 (4.11.0), ABAP Development Tools 3.4.4
- Microsoft Excel for Office 365 – latest version
- Google Sheets – latest version
Program Overview
Here is an overview of the program flow, with method names in italics:
If we display our program in the Object List, we see the following methods, referenced in the above diagram:
End User Walk through
Let’s walk through an example of how the program will work.
We’ve created a custom Table named ZTEST_KOTLIN, consisting of the following fields, to simulate maintenance of a memo to a customer:
When you execute the program Z_EXCEL_MAINTENANCE, you will get the following selection screen:
Here is a description of these options:
- Open SAP Table in Excel
- This option will fetch all records from the ZTEST_KOTLIN table, write an Excel file with extension xlsx to the user’s PC, then open the file in Excel.
- Refresh SAP Table from Excel
- This option will read an Excel file from the user’s PC, wipe all records in the ZTEST_KOTLIN table and insert all records from the Excel file into the table. So, this will do a complete refresh.
- Z Table Name
- Here, you can specify any Z Table of your choice to maintain.
- Excel File
- This is the directory and file name specified on the user’s PC to either upload or download to or from. There is a drop-down for this field, to browse the user’s PC for a file.
Let’s assume we’ve just created our new Z Table, and it’s empty. We can start off with a template Excel file, by choosing the “Open SAP Table in Excel” option, which gives us an empty table with a header row. Choose a file name and directory on your PC. If we execute the program, with the above options, it will download the file “ExcelFile.xlsx” to our “c:\1\” directory on our PC, then launch Excel and show the file:
Next, let’s enter some test data. Click “Enable Editing”, and enter some test data:
Leave columns A thru E blank, as we’ll explain those later.
Save the file, then Alt-Tab back to our selection screen. Now click the option, “Refresh SAP Table from Excel”:
Execute the program, and you will get a pop-up with a record count of the refresh (number of records deleted) and the insert count:
Next, we can go look at the records in our ZTEST_KOTLIN table. Execute the SAP Transaction Code SE16, and enter table name ZTEST_KOTLIN and execute:
One nice feature, you can keep your Excel file open, edit the data, run the program and see the results in SAP all together. For example, tile your 3 sessions, as follows:
(1) Update your Excel File – Add a new row, and Save:
(2) Re-Execute the program:
(3) Refresh your SE16 screen , and see the newly added record in SAP:
Refresh Button:
You now have your own Excel SM30-like data maintenance tool!
Note, to upload the Excel data, you may have your Excel file open, but to download the data, you must have the file closed because it will be locked.
To see our Excel download again, wipe the records from your Excel file, and Save:
Close the Excel file, then go back to the program selection screen, and click “Open SAP Table in Excel”:
This will re-populate our Excel File from the table, download to the user’s computer, then open it:
Since we have our key field named “GUID” populated, we can also update the records by changing any non-key fields in our file, and uploading it again:
(1) Update the Excel Record, and Save:
(2) Re-run the program:
(3) See the update in our table:
Our custom method my_custom_table_logic was written for the table ZTEST_KOTLIN, where we evaluate this record key named “GUID”. You can add logic specific to your own custom Z table in this method.
Next, let’s walk through the code, to see how the above may be accomplished.
Code Overview
Create a Custom Table
For our example, we will create the custom table ZTEST_KOTLIN, but you may create any custom Z table you’d like and the program will still work. Simply enter your table name on the Selection Screen for “Z Table Name”:
Create the following Z Table in SAP:
Our data element for ZZ_NOTE is a string:
If you prefer Eclipse, we can see the following DDL for our Table:
@EndUserText.label : 'Kotlin Tester'
@AbapCatalog.enhancementCategory : #EXTENSIBLE_ANY
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #ALLOWED
define table ztest_kotlin {
key mandt : mandt not null;
key guid : /aif/exp_guid not null;
date_updated : sydatum;
time_updated : syuzeit;
last_updated_by : syuname;
order_number : vbeln;
customer_number : kunag;
customer_message : zz_note;
}
We have a special method in our program called “my_custom_table_logic”. This method was written specifically for our ZTEST_KOTLIN table, however, the program will still run if you define your own table, and this method will simply ignore the fields. More on this later.
As our diagram showed earlier, there are 2 main processes of the program, upload or download:
The radio buttons on the selection screen determine which option will run:
Download: Open SAP Table in Excel – Selection variable = r_down
Upload: Refresh SAP Table from Excel – Selection variable = r_up
The “execute” method is called first, and will perform one of the options to upload or download:
START-OF-SELECTION.
TRY.
NEW lcl_excel_maint( )->execute( VALUE #( upload = r_up
download = r_down
tabname = p_table
excel_file = p_file ) ).
CATCH lcl_my_exception INTO DATA(lo_exc).
MESSAGE lo_exc->get_message( ) TYPE 'I'.
ENDTRY.
The above statement, in older ABAP, would have been the following:
"Old ABAP:
DATA: lo_obj TYPE REF TO lcl_excel_maint,
lo_exc TYPE REF TO lcl_my_exception,
lw_params TYPE lcl_excel_maint=>lty_param.
lw_params-upload = r_up.
lw_params-download = r_down.
lw_params-tabname = p_table.
lw_params-excel_file = p_file.
CREATE OBJECT lo_obj.
TRY.
call method lo_obj->execute( lw_params ).
CATCH lcl_my_exception INTO lo_exc.
MESSAGE lo_exc->get_message( ) TYPE 'I'.
ENDTRY.
The select-option parameters are passed to the execute method denoting upload, download, table name and the Excel File name and path.
It’s worth mentioning, throughout the program, we use our custom Class-Based Exception called LCL_MY_EXCEPTION, which will enable our exception messages to easily flow up the stack. Here is the definition and implementation of our Exception class:
CLASS lcl_my_exception DEFINITION INHERITING FROM cx_dynamic_check.
PUBLIC SECTION.
METHODS:
constructor
IMPORTING i_textid LIKE textid OPTIONAL
i_previous LIKE previous OPTIONAL
i_message TYPE string OPTIONAL,
get_message RETURNING VALUE(r_message) TYPE bapi_msg.
PRIVATE SECTION.
DATA: gv_message TYPE string.
ENDCLASS. "lcl_my_exception DEFINITION
CLASS lcl_my_exception IMPLEMENTATION.
METHOD constructor.
CALL METHOD super->constructor
EXPORTING
textid = i_textid
previous = i_previous.
CLEAR gv_message.
gv_message = i_message.
ENDMETHOD.
METHOD get_message.
r_message = gv_message.
ENDMETHOD.
ENDCLASS. "lcl_my_exception IMPLEMENTATION
Also, we restrict this program to only custom tables starting with ‘Z’ or ‘Y’. Never update data directly in standard SAP tables:
IF i_parameters-tabname(1) <> 'Z' AND i_parameters-tabname(1) <> 'Y'.
RAISE EXCEPTION TYPE lcl_my_exception
EXPORTING i_message = |Only Custom Tables are supported (must begin with 'Y' or 'Z').|.
ENDIF.
As is, this program should only be used for tables that do not have too many records. For large datasets, the program would timeout. For large data loads, you would need to load your data directly from the SAP operating system, as a background job. For tables with large datasets, you could adjust this program to update subsets of records, rather than perform a complete refresh.
Since both options (upload or download) will require dynamic creation of an internal table, based on the table name entered on the selection screen, we will do the following for both options:
CREATE DATA lo_table TYPE TABLE OF (i_parameters-tabname).
ASSIGN lo_table->* TO <lt_table>.
The above dynamically creates an internal table <lt_table>, in our case the table we entered on the selection screen was ZTEST_KOTLIN, and is passed in the parameter “i_parameters-tabname”.
Because we will need all of the data in the table for both the upload and download methods, we then select all records into the internal table. If we are doing an upload, we will need this in order to completely refresh the table. If we are downloading, of course we need all of the records.
"Get all records currently in our Z Table...
SELECT * FROM (i_parameters-tabname) INTO TABLE <lt_table>.
Now, we will perform the following, based on the user request to upload or download…
Download – Extract and auto-open in Excel
The following 2 methods are called, when running in download mode, or the select option titled “Open SAP Table in Excel”:
"Write the Excel file, and open Excel and display on the user's PC...
export_excel_data( im_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> )
im_file = CONV string( i_parameters-excel_file ) ).
open_excel_file_on_pc( i_parameters-excel_file ).
Written in older ABAP, the above call to the method “export_excel_data” would need to be the following:
"Old ABAP:
DATA: lv_xstring TYPE xstring,
lv_temp_string TYPE string.
lv_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> ).
lv_temp_string = i_parameters-excel_file.
CALL METHOD export_excel_data(
EXPORTING
im_xstring = lv_xstring
im_file = lv_temp_string ).
With the new ABAP language features in 7.4+, we can now utilize temporary variables and inline method calls. So, we can turn the above into a single line of code:
export_excel_data( im_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> )
im_file = CONV string( i_parameters-excel_file ) ).
Because the method “generate_xlsx_string” has a returning parameter, we can use this as an import parameter, and embed it inline to pass to the parameter im_xstring. Using the “CONV” keyword, we can dynamically convert i_parameters-excel_file into a string at runtime. We are converting it from data type “localfile” into a string, which is required for the im_file parameter for the “export_excel_data” method.
Method generate_xlsx_string:
This method utilizes the cl_salv_table class to generate our table, which may then be generated into an xstring of an xml version which Excel understands:
METHOD generate_xlsx_string.
DATA: lo_data TYPE REF TO data.
DATA(l_version) = cl_salv_bs_a_xml_base=>get_version( ).
IF l_version <> if_salv_bs_xml=>version_25 AND
l_version <> if_salv_bs_xml=>version_26. RETURN.
ENDIF.
TRY.
cl_salv_table=>factory( EXPORTING list_display = abap_false
IMPORTING r_salv_table = DATA(lo_salv_table)
CHANGING t_table = ct_table ).
CATCH cx_salv_msg.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = 'ALV Table create failed.'.
ENDTRY.
GET REFERENCE OF ct_table INTO lo_data.
DATA(lo_result_data) = cl_salv_ex_util=>factory_result_data_table(
r_data = lo_data
t_fieldcatalog = cl_salv_controller_metadata=>get_lvc_fieldcatalog(
r_columns = lo_salv_table->get_columns( )
r_aggregations = lo_salv_table->get_aggregations( ) ) ).
cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform( EXPORTING xml_type = if_salv_bs_xml=>c_type_xlsx
xml_version = l_version
r_result_data = lo_result_data
xml_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export
gui_type = if_salv_bs_xml=>c_gui_type_gui
IMPORTING xml = r_xstring ).
ENDMETHOD. "generate_xlsx_string
Once our xstring is generated, we call the method export_excel_data…
Method export_excel_data:
This method will turn our xstring into binary data, for the standard GUI_DOWNLOAD, which downloads the file to the user’s computer:
METHOD export_excel_data.
TYPES: BEGIN OF lty_line,
data(1024) TYPE x,
END OF lty_line.
DATA: lv_size TYPE i,
lt_bintab TYPE TABLE OF lty_line.
IF im_xstring IS INITIAL. RETURN. ENDIF.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
buffer = im_xstring
IMPORTING
output_length = lv_size
TABLES
binary_tab = lt_bintab.
cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = lv_size
filename = im_file
filetype = 'BIN'
CHANGING data_tab = lt_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
OTHERS = 22 ).
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
INTO DATA(lv_error_text).
IF sy-subrc = 15.
lv_error_text = |{ lv_error_text }. Do you have the file currently open? | &&
|If so, close the Excel file, and re-run.|.
ENDIF.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = lv_error_text.
ENDIF.
ENDMETHOD. "export_excel_data
Finally, SAP provides a method to take the file directory and name, and open the Excel program on the user’s computer…
Method open_excel_file_on_pc:
The following method parses out the directory and file name, then calls the standard SAP class to open a program on the user’s computer (PC):
METHOD open_excel_file_on_pc.
DATA: lv_pathname TYPE string,
lv_filename TYPE string.
CALL FUNCTION 'RPM_DX_PATH_FILE_SPLIT'
EXPORTING
iv_pathfile = im_file
IMPORTING
ev_pathname = lv_pathname
ev_filename = lv_filename.
cl_gui_frontend_services=>execute( EXPORTING application = 'EXCEL'
parameter = lv_filename
default_directory = lv_pathname
operation = 'OPEN'
maximized = 'X'
EXCEPTIONS cntl_error = 1
error_no_gui = 2
bad_parameter = 3
file_not_found = 4
path_not_found = 5
file_extension_unknown = 6
error_execute_failed = 7
synchronous_failed = 8
not_supported_by_gui = 9 ).
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
INTO DATA(lv_error_text).
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Excel Could not open. { lv_error_text }|.
ENDIF.
ENDMETHOD. "open_excel_file_on_pc
That’s it for the download, just 3 simple method calls. Next, let’s see how the upload works…
Upload – Refresh your Z Table from Excel
Because we do a complete refresh of the table, and overwrite with the records in the Excel file, first we will delete all records in our table:
"Upload the Excel file from the user's PC and populate our SAP table...
SELECT COUNT( * ) FROM (i_parameters-tabname) INTO lv_count_before.
IF <lt_table> IS NOT INITIAL.
"Refresh/Delete all existing entries from the SAP table...
DELETE (i_parameters-tabname) FROM TABLE <lt_table>.
CLEAR <lt_table>[].
ENDIF.
We make note of a “before” record count, so we may show the end-user a message with the number of records that were deleted.
To perform the upload, we have the following method call:
convert_excel_to_table( EXPORTING io_table = import_excel_data( CONV string( i_parameters-excel_file ) )
im_tabname = i_parameters-tabname
CHANGING ct_table = <lt_table> ).
Just like our download method earlier, we have an inline method call, which has a returning parameter. The method “import_excel_data” returns a data reference type for the io_table import parameter. We must also convert our excel_file parameter to a string with the CONV keyword. The result will be that our internal table <lt_table> now has all of the records from the Excel file. Let’s see how these methods work…
Method import_excel_data:
Here is the code for our import method:
METHOD import_excel_data.
DATA: lt_xtab TYPE cpt_x255,
lv_size TYPE i.
cl_gui_frontend_services=>gui_upload( EXPORTING filename = im_file
filetype = 'BIN'
IMPORTING filelength = lv_size
CHANGING data_tab = lt_xtab ).
IF sy-subrc NE 0.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Invalid File { im_file }|.
ENDIF.
cl_scp_change_db=>xtab_to_xstr( EXPORTING im_xtab = lt_xtab
im_size = lv_size
IMPORTING ex_xstring = DATA(lv_xstring) ).
DATA(lo_excel) = NEW cl_fdt_xl_spreadsheet( document_name = im_file
xdocument = lv_xstring ).
lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING worksheet_names = DATA(lt_worksheets) ).
rt_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).
IF rt_table IS INITIAL.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = 'No Data found in Excel File'.
ENDIF.
ENDMETHOD. "import_excel_data
This method will use the SAP standard gui_upload method to read the Excel File from the end-user’s computer. It then converts the results into an xstring with the method “cl_scp_change_db=>xtab_to_xstr”. Once we have our xstring, we can call some standard SAP classes to extract the data into an internal table. Finally, we get our table with the following method call:
rt_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).
Our assumption is that we only have a single worksheet within our Excel file. If you have many worksheets, you can loop at lt_worksheets and write code as needed. We will simply get the first worksheet by referenceing “lt_worksheets[ 1 ]”. This is new ABAP syntax for getting record number 1 in the internal table “lt_worksheets”. In old abap, we would do something like the following:
"Old ABAP:
DATA: lt_temp_table TYPE REF TO data,
lt_worksheets TYPE STANDARD TABLE OF string,
lv_worksheet TYPE string.
CALL METHOD lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names
IMPORTING
worksheet_names = lt_worksheets.
READ TABLE lt_worksheets INTO lv_worksheet INDEX 1.
CALL METHOD lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet
EXPORTING
worksheet_name = lv_worksheet
RECEIVING
itab = rt_table.
With our new ABAP language features, the above is greatly simplified:
lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING worksheet_names = DATA(lt_worksheets) ).
rt_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).
Notice that the standard SAP cl_fdt_xl_spreadsheet class returns a generic table, with alphabetic column names:
So, we’ll need to transfer this table into our SAP defined Z table schema. This is done in the following method, after we receive the above results into our io_table parameter…
Method convert_excel_to_table:
After the above method call, we receive an internal table with alphabetic column names. We need to translate this table into our custom ZTEST_KOTLIN table. That is, column “A”, translates to column “MANDT”, column “B” moves into the “GUID” column, etc. As a reminder, our ZTEST_KOTLIN schema has the following 8 columns:
Our code for this method is:
METHOD convert_excel_to_table.
DATA: lo_record TYPE REF TO data.
FIELD-SYMBOLS: <lt_input_table> TYPE table.
ASSIGN io_table->* TO <lt_input_table>.
"Remove the Header row, if one exists (i.e. starts with Client - MANDT)...
ASSIGN COMPONENT 1 OF STRUCTURE <lt_input_table>[ 1 ] TO FIELD-SYMBOL(<lfs_mandt>).
IF <lfs_mandt> IS ASSIGNED.
IF <lfs_mandt> = 'Client'. DELETE <lt_input_table> INDEX 1. ENDIF.
ENDIF.
"Need to move the generic Excel input table into our SAP defined table...
"Get a list of fields in our SAP table...
CREATE DATA lo_record LIKE LINE OF ct_table.
ASSIGN lo_record->* TO FIELD-SYMBOL(<lw_record>).
DATA(components) = CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_name( im_tabname ) )->components.
"Migrate each field into our table with an SAP schema...
LOOP AT <lt_input_table> ASSIGNING FIELD-SYMBOL(<lw_input>).
CLEAR <lw_record>.
LOOP AT components INTO DATA(lw_field).
ASSIGN COMPONENT lw_field-name OF STRUCTURE <lw_record> TO FIELD-SYMBOL(<lfs_target>).
ASSIGN COMPONENT sy-tabix OF STRUCTURE <lw_input> TO FIELD-SYMBOL(<lfs_source>).
IF <lfs_target> IS ASSIGNED AND <lfs_source> IS ASSIGNED.
<lfs_target> = <lfs_source>.
ENDIF.
UNASSIGN: <lfs_source>, <lfs_target>.
ENDLOOP.
IF <lw_record> IS NOT INITIAL.
APPEND <lw_record> TO ct_table.
ENDIF.
ENDLOOP.
ENDMETHOD. "convert_excel_to_table
Because the user typed in the table name, we can simply call the standard SAP structure class to get a list of fields for our table schema:
DATA(components) = CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_name( im_tabname ) )->components.
I must say, compressing the above code into a single line is very cool. In old ABAP, we may have had to do the following:
"Old ABAP:
data: type_ref type ref to cl_abap_typedescr,
struc_descr type ref to cl_abap_structdescr,
components type ABAP_COMPDESCR_TAB.
type_ref = cl_abap_typedescr=>describe_by_name( im_tabname ).
struc_descr ?= type_ref.
components = struc_descr->components.
The internal table “components” now contains information on our table schema. There is a field called “NAME”, which holds the table field name. We loop at all components of the Z table schema, and assign one component at a time from our Excel table, into the target field of ZTEST_KOTLIN.
Source Table:
Source = Target Fields:
A = MANDT
B = GUID
C = DATE_UPDATED
D = TIME_UPDATED
…and so on…
Notice we have a header row in our Excel spreadsheet, so we would want to delete the first row:
"Remove the Header row, if one exists (i.e. starts with Client - MANDT)...
ASSIGN COMPONENT 1 OF STRUCTURE <lt_input_table>[ 1 ] TO FIELD-SYMBOL(<lfs_mandt>).
IF <lfs_mandt> IS ASSIGNED.
IF <lfs_mandt> = 'Client'. DELETE <lt_input_table> INDEX 1. ENDIF.
ENDIF.
The above simply searches for the keyword “Client” in the first field to determine if there is a header row.
Method my_custom_table_logic:
After we perform our upload from Excel, you may want to execute some custom code, specific to your own Z Table. We have the following custom method, specific to our ZTEST_KOTLIN table:
METHOD my_custom_table_logic.
LOOP AT ct_table ASSIGNING FIELD-SYMBOL(<lfs_wa>).
"Auto-generate a unique key...
ASSIGN COMPONENT 'GUID' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_guid>).
IF <lfs_guid> IS ASSIGNED.
IF <lfs_guid> IS INITIAL.
TRY.
<lfs_guid> = cl_system_uuid=>if_system_uuid_static~create_uuid_x16( ).
CATCH cx_uuid_error INTO DATA(lo_guid_error).
MESSAGE 'GUID Generation error.' TYPE 'I'.
ENDTRY.
ENDIF.
ENDIF.
ASSIGN COMPONENT 'DATE_UPDATED' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_udate>).
IF <lfs_udate> IS ASSIGNED.
<lfs_udate> = sy-datum.
ENDIF.
ASSIGN COMPONENT 'TIME_UPDATED' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_utime>).
IF <lfs_utime> IS ASSIGNED.
<lfs_utime> = sy-uzeit.
ENDIF.
ASSIGN COMPONENT 'LAST_UPDATED_BY' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_user>).
IF <lfs_user> IS ASSIGNED.
<lfs_user> = sy-uname.
ENDIF.
UNASSIGN: <lfs_udate>, <lfs_guid>, <lfs_utime>, <lfs_user>.
ENDLOOP.
ENDMETHOD. "my_custom_table_logic
Our key field is named GUID. If the key field is blank, we’ll generate a unique key with the custom class cl_system_uuid. We’ll also default the fields DATE_UPDATED, TIME_UPDATED, LAST_UPDATED_BY with the date, time and SAP user ID whom updated this record for logging purposes.
If you define your own Z table, this method will simply ignore any fields not named GUID, DATE_UPDATED, TIME_UPDATED and LAST_UPDATED_BY and the program could work as-is for your table. If you want special logic for your custom table, simply overwrite the code in this method.
Import a CDS View into Excel
This section sort of wanders off on a tangent, but very cool stuff and relevant for our Excel themed Blog. HANA CDS Views are insanely powerful, and you can even create an entire Fiori app from just a CDS View with some clever annotations. Google it, and read more about it. Show this section to an experienced SAP Functional Analyst, and see the delight on their face, as they realize they can become CDS View ninjas, and totally eliminate all future interaction with any ABAP report writers ?…If you are an ABAP programmer who would like to stay current, then yes, go learn CDS Views, Eclipse ABAP Development Tools, SAP Gateway, BOPF, BRF+, Fiori, etc.
Now that we have our custom Z Table, let’s write a simple CDS View, so that we can easily expose it as a service to the outside world:
For CDS View development, you must use Eclipse. Open Eclipse, and perform the following:
Create a new ABAP Project, and login to your SAP system:
Navigate to “Core Data Services” and create a new Data Definition:
Name your new CDS View:
Click Next, accept defaults until you get to the following screen. Choose the “Define View” template:
Click Finish
Overwrite the template strings with the following:
Replace @AbapCatalog.sqlViewName: ‘sql_view_name’ with @AbapCatalog.sqlViewName: ‘ZCDS_KOTLIN_SE11’.
Replace “as select from data_source_name” with “as select from ztest_kotlin”.
You will now have the following:
@AbapCatalog.sqlViewName: 'ZCDS_KOTLIN_SE11'
define view ZCDS_KOTLIN as select from ZTEST_KOTLIN {
You should have the following:
@AbapCatalog.sqlViewName: 'ZCDS_KOTLIN_SE11'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Kotlin CDS View'
define view ZCDS_KOTLIN as select from ZTEST_KOTLIN {
}
With your cursor on the empty row, between the curly brackets { }, enter the shortcut Ctrl + Space, to get the following Code Completion tips:
Double-click on “Insert all elements (template)”, and it will place all fields into the CDS View for you:
@AbapCatalog.sqlViewName: 'ZCDS_KOTLIN_SE11'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Kotlin CDS View'
define view ZCDS_KOTLIN as select from ztest_kotlin {
//ZTEST_KOTLIN
key guid,
date_updated,
time_updated,
last_updated_by,
order_number,
customer_number,
customer_message
}
Save and activate our new CDS View.
The annotation “AbapCatalog.sqlViewName”, gives you a way to display your CDS view in the SAPGUI in SE11. Go to SE11, and display ZCDS_KOTLIN_SE11:
You can Double-click on the DDL Source “ZCDS_KOTLIN”, to see the same DDL you see in Eclipse:
But of course, now that we have Eclipse, there is no need to use the SAPGUI anymore ?… In all seriousness, SAP is slowly adding features to Eclipse that will include the ability to do everything you can do in SAPGUI, but it’s a work in progress.
Go back to Eclipse, and display the data in the table, by accessing the CDS View. Right-click on your CDS View, then go to “Open With > Data Preview”:
Now, let’s create an SAP Gateway service to expose our CDS view to the world, as a RESTful web service…
Back in the SAPGUI, Execute the TCode SEGW. Click on the “Create Project” button:
Enter the following and continue:
This generates the following tree structure:
Go to the following menu path, which is the latest way to auto-generate a service from a CDS View:
Enter our CDS View:
Click “Next”, then “Finish” to accept all defaults.
We now have the following auto-generated for our project:
Save and click Generate, to create all of the code necessary for the Gateway to utilize our CDS View:
Accept all of the defaults and hit Enter to generate the below classes:
When prompted, save it as a Local Object.
We now have all of our runtime artifacts for the service to execute:
Under the “Service Maintenance” node, if a Gateway has already been configured, you will see something like the following:
Right-click on the “GW_HUB” node, and “Register” the service on the Gateway:
Enter System Alias “LOCAL” (most common), and hit Enter:
Select Local Object, and accept all defaults and hit Enter:
Click “Save” again, on the main screen. Now, let’s test our service. Right-click on our Gateway, and go to menu path “SAP Gateway Client”:
Let’s walk through some cool features of the SAP Gateway Client, to test our Service…
In the Gateway Client, click on the “EntitySets” button:
Double-click to select “ZCDS_KOTLIN”:
Click on the Execute Button:
In the lower-right, you will see the OData results for our Gateway Service:
Notice we can see our first record, above. You can scroll down to see the other records.
Click on the button “Add URI Option”:
Double-click on the entry “$format=xlsx”:
This simply adds a new parameter to the end of our URL:
Execute again, and we see the following message in our HTTP Response:
Click on the “Response in Browser” button, and it will auto-open our data in Excel:
Our service looks good, and is responding with data. Let’s approach it from another angle…
In our first test, you can see the following url in the HTTP Response:
We can simply copy this url, then enter it into a Web Browser, and see the following results:
You may be prompted for your SAP User ID and password:
After you enter your SAP ID and password, you can see your raw data in the browser:
Now we know we can access our service from our workstation (PC). Keep this URL handy, so we may use it, below…
To pull our data from Excel, perform the following steps…
Open Excel and create a new Blank workbook:
Go to the “Data” tab, and the path Get Data > From Other Sources > From OData Feed
Paste in the url, which we just tested in our browser:
Click the OK button to continue. You will either get an authorization error message, or it will show your data in the pop-up if you are already logged in. If you get an authorization error, click on the “Basic” option on the left, then enter your SAP User and password. Then choose the longest URL in the drop-down at the bottom of the list (our URL from above, which we pasted into the browser). Here is an example of the Authorization screen:
Once logged in, your data will appear:
The above steps took very little time, with zero programming. Find your CDS View, expose it via an SAP Gateway service, and hand it off to your Excel experts in the business. They now have a real-time reporting tool hooked into SAP on the backend.
Complete Program
Below is the complete program for this Blog. The old ABAP code reference from this tutorial is included and commented out. Simply delete the “Old ABAP” commented sections, if you’d like to remove it.
REPORT z_excel_maintenance.
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE fil_lbl.
PARAMETERS: r_down RADIOBUTTON GROUP g1,
r_up RADIOBUTTON GROUP g1.
PARAMETERS: p_table TYPE tabname16 DEFAULT 'ZTEST_KOTLIN',
p_file TYPE localfile DEFAULT 'c:\1\ExcelFile.xlsx'.
SELECTION-SCREEN END OF BLOCK b1.
CLASS lcl_my_exception DEFINITION INHERITING FROM cx_dynamic_check.
PUBLIC SECTION.
METHODS:
constructor
IMPORTING i_textid LIKE textid OPTIONAL
i_previous LIKE previous OPTIONAL
i_message TYPE string OPTIONAL,
get_message RETURNING VALUE(r_message) TYPE bapi_msg.
PRIVATE SECTION.
DATA: gv_message TYPE string.
ENDCLASS. "lcl_my_exception DEFINITION
CLASS lcl_my_exception IMPLEMENTATION.
METHOD constructor.
CALL METHOD super->constructor
EXPORTING
textid = i_textid
previous = i_previous.
CLEAR gv_message.
gv_message = i_message.
ENDMETHOD.
METHOD get_message.
r_message = gv_message.
ENDMETHOD.
ENDCLASS. "lcl_my_exception IMPLEMENTATION
CLASS lcl_excel_maint DEFINITION CREATE PUBLIC FINAL.
PUBLIC SECTION.
CONSTANTS: c_excel TYPE string VALUE 'Excel files(*.xlsx)|*.xlsx'.
TYPES: BEGIN OF lty_param,
upload TYPE char1,
download TYPE char1,
tabname TYPE tabname16,
excel_file TYPE localfile,
END OF lty_param.
CLASS-METHODS:
select_file
IMPORTING i_filename TYPE string
i_type TYPE string
RETURNING VALUE(re_file) TYPE localfile.
METHODS:
execute
IMPORTING i_parameters TYPE lty_param
RAISING lcl_my_exception.
PRIVATE SECTION.
METHODS:
import_excel_data
IMPORTING im_file TYPE string
RETURNING VALUE(rt_table) TYPE REF TO data
RAISING lcl_my_exception,
generate_xlsx_string
CHANGING ct_table TYPE table
RETURNING VALUE(r_xstring) TYPE xstring,
export_excel_data
IMPORTING im_xstring TYPE xstring
im_file TYPE string
RAISING lcl_my_exception,
my_custom_table_logic
CHANGING ct_table TYPE table,
open_excel_file_on_pc
IMPORTING im_file TYPE localfile
RAISING lcl_my_exception,
convert_excel_to_table
IMPORTING io_table TYPE REF TO data
im_tabname TYPE tabname16
CHANGING ct_table TYPE table
RAISING lcl_my_exception.
ENDCLASS. "lcl_excel_maint
CLASS lcl_excel_maint IMPLEMENTATION.
METHOD execute.
DATA: lo_table TYPE REF TO data,
lv_count_before TYPE i,
lv_count_after TYPE i.
FIELD-SYMBOLS: <lt_table> TYPE table.
IF i_parameters-tabname(1) <> 'Z' AND i_parameters-tabname(1) <> 'Y'.
RAISE EXCEPTION TYPE lcl_my_exception
EXPORTING
i_message = |Only Custom Tables are supported (must begin with 'Y' or 'Z').|.
ENDIF.
SELECT SINGLE tabname INTO @DATA(lv_tabname) FROM dd03l
WHERE tabname = @i_parameters-tabname.
IF sy-subrc <> 0.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Invalid Table Name '{ i_parameters-tabname }'.|.
ENDIF.
CREATE DATA lo_table TYPE TABLE OF (i_parameters-tabname).
ASSIGN lo_table->* TO <lt_table>.
"Get all records currently in our Z Table...
SELECT * FROM (i_parameters-tabname) INTO TABLE <lt_table>.
IF i_parameters-download = abap_true.
"Old ABAP:
* DATA: lv_xstring TYPE xstring,
* lv_temp_string TYPE string.
* lv_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> ).
* lv_temp_string = i_parameters-excel_file.
* CALL METHOD export_excel_data(
* EXPORTING
* im_xstring = lv_xstring
* im_file = lv_temp_string ).
"Write the Excel file, and open Excel and display on the user's PC...
export_excel_data( im_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> )
im_file = CONV string( i_parameters-excel_file ) ).
open_excel_file_on_pc( i_parameters-excel_file ).
ELSEIF i_parameters-upload = abap_true.
"Upload the Excel file from the user's PC and populate our SAP table...
SELECT COUNT( * ) FROM (i_parameters-tabname) INTO lv_count_before.
IF <lt_table> IS NOT INITIAL.
"Refresh/Delete all existing entries from the SAP table...
DELETE (i_parameters-tabname) FROM TABLE <lt_table>.
CLEAR <lt_table>[].
ENDIF.
convert_excel_to_table( EXPORTING io_table = import_excel_data( CONV string( i_parameters-excel_file ) )
im_tabname = i_parameters-tabname
CHANGING ct_table = <lt_table> ).
"Adjust the table according to our requirements...
my_custom_table_logic( CHANGING ct_table = <lt_table> ).
MODIFY (i_parameters-tabname) FROM TABLE <lt_table>.
COMMIT WORK AND WAIT.
IF sy-subrc = 0.
SELECT COUNT( * ) FROM (i_parameters-tabname) INTO lv_count_after.
MESSAGE |Table { i_parameters-tabname } successfully refreshed. | &&
|{ lv_count_before } records deleted and { lv_count_after } inserted.| TYPE 'I'.
ELSE.
RAISE EXCEPTION TYPE lcl_my_exception
EXPORTING
i_message = |Failed to update table { i_parameters-tabname }.|.
ENDIF.
ENDIF.
ENDMETHOD. "execute
METHOD convert_excel_to_table.
DATA: lo_record TYPE REF TO data.
FIELD-SYMBOLS: <lt_input_table> TYPE table.
ASSIGN io_table->* TO <lt_input_table>.
"Remove the Header row, if one exists (i.e. starts with Client - MANDT)...
ASSIGN COMPONENT 1 OF STRUCTURE <lt_input_table>[ 1 ] TO FIELD-SYMBOL(<lfs_mandt>).
IF <lfs_mandt> IS ASSIGNED.
IF <lfs_mandt> = 'Client'. DELETE <lt_input_table> INDEX 1. ENDIF.
ENDIF.
"Need to move the generic Excel input table into our SAP defined table...
"Get a list of fields in our SAP table...
CREATE DATA lo_record LIKE LINE OF ct_table.
ASSIGN lo_record->* TO FIELD-SYMBOL(<lw_record>).
DATA(components) = CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_name( im_tabname ) )->components.
* "Old ABAP for the above single line of code:
* data: type_ref type ref to cl_abap_typedescr,
* struc_descr type ref to cl_abap_structdescr,
* components type ABAP_COMPDESCR_TAB.
* type_ref = cl_abap_typedescr=>describe_by_name( im_tabname ).
* struc_descr ?= type_ref.
* components = struc_descr->components.
"Migrate each field into our table with an SAP schema...
LOOP AT <lt_input_table> ASSIGNING FIELD-SYMBOL(<lw_input>).
CLEAR <lw_record>.
LOOP AT components INTO DATA(lw_field).
ASSIGN COMPONENT lw_field-name OF STRUCTURE <lw_record> TO FIELD-SYMBOL(<lfs_target>).
ASSIGN COMPONENT sy-tabix OF STRUCTURE <lw_input> TO FIELD-SYMBOL(<lfs_source>).
IF <lfs_target> IS ASSIGNED AND <lfs_source> IS ASSIGNED.
<lfs_target> = <lfs_source>.
ENDIF.
UNASSIGN: <lfs_source>, <lfs_target>.
ENDLOOP.
IF <lw_record> IS NOT INITIAL.
APPEND <lw_record> TO ct_table.
ENDIF.
ENDLOOP.
ENDMETHOD. "convert_excel_to_table
METHOD my_custom_table_logic.
LOOP AT ct_table ASSIGNING FIELD-SYMBOL(<lfs_wa>).
"Auto-generate a unique key...
ASSIGN COMPONENT 'GUID' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_guid>).
IF <lfs_guid> IS ASSIGNED.
IF <lfs_guid> IS INITIAL.
TRY.
<lfs_guid> = cl_system_uuid=>if_system_uuid_static~create_uuid_x16( ).
CATCH cx_uuid_error INTO DATA(lo_guid_error).
MESSAGE 'GUID Generation error.' TYPE 'I'.
ENDTRY.
ENDIF.
ENDIF.
ASSIGN COMPONENT 'DATE_UPDATED' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_udate>).
IF <lfs_udate> IS ASSIGNED.
<lfs_udate> = sy-datum.
ENDIF.
ASSIGN COMPONENT 'TIME_UPDATED' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_utime>).
IF <lfs_utime> IS ASSIGNED.
<lfs_utime> = sy-uzeit.
ENDIF.
ASSIGN COMPONENT 'LAST_UPDATED_BY' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_user>).
IF <lfs_user> IS ASSIGNED.
<lfs_user> = sy-uname.
ENDIF.
UNASSIGN: <lfs_udate>, <lfs_guid>, <lfs_utime>, <lfs_user>.
ENDLOOP.
ENDMETHOD. "my_custom_table_logic
METHOD open_excel_file_on_pc.
DATA: lv_pathname TYPE string,
lv_filename TYPE string.
CALL FUNCTION 'RPM_DX_PATH_FILE_SPLIT'
EXPORTING
iv_pathfile = im_file
IMPORTING
ev_pathname = lv_pathname
ev_filename = lv_filename.
cl_gui_frontend_services=>execute( EXPORTING application = 'EXCEL'
parameter = lv_filename
default_directory = lv_pathname
operation = 'OPEN'
maximized = 'X'
EXCEPTIONS cntl_error = 1
error_no_gui = 2
bad_parameter = 3
file_not_found = 4
path_not_found = 5
file_extension_unknown = 6
error_execute_failed = 7
synchronous_failed = 8
not_supported_by_gui = 9 ).
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
INTO DATA(lv_error_text).
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Excel Could not open. { lv_error_text }|.
ENDIF.
ENDMETHOD. "open_excel_file_on_pc
METHOD select_file.
re_file = cl_openxml_helper=>browse_local_file_open(
iv_title = 'Select File'
iv_filename = i_filename
iv_extpattern = i_type ).
ENDMETHOD. "select_file
METHOD generate_xlsx_string.
DATA: lo_data TYPE REF TO data.
DATA(l_version) = cl_salv_bs_a_xml_base=>get_version( ).
IF l_version <> if_salv_bs_xml=>version_25 AND
l_version <> if_salv_bs_xml=>version_26. RETURN.
ENDIF.
TRY.
cl_salv_table=>factory( EXPORTING list_display = abap_false
IMPORTING r_salv_table = DATA(lo_salv_table)
CHANGING t_table = ct_table ).
CATCH cx_salv_msg.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = 'ALV Table create failed.'.
ENDTRY.
GET REFERENCE OF ct_table INTO lo_data.
DATA(lo_result_data) = cl_salv_ex_util=>factory_result_data_table(
r_data = lo_data
t_fieldcatalog = cl_salv_controller_metadata=>get_lvc_fieldcatalog(
r_columns = lo_salv_table->get_columns( )
r_aggregations = lo_salv_table->get_aggregations( ) ) ).
cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform( EXPORTING xml_type = if_salv_bs_xml=>c_type_xlsx
xml_version = l_version
r_result_data = lo_result_data
xml_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export
gui_type = if_salv_bs_xml=>c_gui_type_gui
IMPORTING xml = r_xstring ).
ENDMETHOD. "generate_xlsx_string
METHOD export_excel_data.
TYPES: BEGIN OF lty_line,
data(1024) TYPE x,
END OF lty_line.
DATA: lv_size TYPE i,
lt_bintab TYPE TABLE OF lty_line.
IF im_xstring IS INITIAL. RETURN. ENDIF.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
buffer = im_xstring
IMPORTING
output_length = lv_size
TABLES
binary_tab = lt_bintab.
cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = lv_size
filename = im_file
filetype = 'BIN'
CHANGING data_tab = lt_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
OTHERS = 22 ).
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
INTO DATA(lv_error_text).
IF sy-subrc = 15.
lv_error_text = |{ lv_error_text }. Do you have the file currently open? | &&
|If so, close the Excel file, and re-run.|.
ENDIF.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = lv_error_text.
ENDIF.
ENDMETHOD. "export_excel_data
METHOD import_excel_data.
DATA: lt_xtab TYPE cpt_x255,
lv_size TYPE i.
cl_gui_frontend_services=>gui_upload( EXPORTING filename = im_file
filetype = 'BIN'
IMPORTING filelength = lv_size
CHANGING data_tab = lt_xtab ).
IF sy-subrc NE 0.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Invalid File { im_file }|.
ENDIF.
cl_scp_change_db=>xtab_to_xstr( EXPORTING im_xtab = lt_xtab
im_size = lv_size
IMPORTING ex_xstring = DATA(lv_xstring) ).
DATA(lo_excel) = NEW cl_fdt_xl_spreadsheet( document_name = im_file
xdocument = lv_xstring ).
lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING worksheet_names = DATA(lt_worksheets) ).
rt_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).
* "Old ABAP for the above 2 lines of code:
* DATA: lt_temp_table TYPE REF TO data,
* lt_worksheets TYPE STANDARD TABLE OF string,
* lv_worksheet TYPE string.
* CALL METHOD lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names
* IMPORTING
* worksheet_names = lt_worksheets.
* READ TABLE lt_worksheets INTO lv_worksheet INDEX 1.
* CALL METHOD lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet
* EXPORTING
* worksheet_name = lv_worksheet
* RECEIVING
* itab = rt_table.
IF rt_table IS INITIAL.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = 'No Data found in Excel File'.
ENDIF.
ENDMETHOD. "import_excel_data
ENDCLASS. "lcl_excel_maint
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
p_file = lcl_excel_maint=>select_file( i_filename = CONV string( p_file )
i_type = lcl_excel_maint=>c_excel ).
INITIALIZATION.
%_p_file_%_app_%-text = 'Excel File'.
%_p_table_%_app_%-text = 'Z Table Name'.
%_r_up_%_app_%-text = 'Refresh SAP Table from Excel'.
%_r_down_%_app_%-text = 'Open SAP Table in Excel'.
fil_lbl = 'Excel Table Maintenance Options'.
START-OF-SELECTION.
"Old ABAP for the below execute method:
* DATA: lo_obj TYPE REF TO lcl_excel_maint,
* lo_exc TYPE REF TO lcl_my_exception,
* lw_params TYPE lcl_excel_maint=>lty_param.
* lw_params-upload = r_up.
* lw_params-download = r_down.
* lw_params-tabname = p_table.
* lw_params-excel_file = p_file.
* CREATE OBJECT lo_obj.
* TRY.
* call method lo_obj->execute( lw_params ).
* CATCH lcl_my_exception INTO lo_exc.
* MESSAGE lo_exc->get_message( ) TYPE 'I'.
* ENDTRY.
"New ABAP:
TRY.
NEW lcl_excel_maint( )->execute( VALUE #( upload = r_up
download = r_down
tabname = p_table
excel_file = p_file ) ).
CATCH lcl_my_exception INTO DATA(lo_exc).
MESSAGE lo_exc->get_message( ) TYPE 'I'.
ENDTRY.
References
Related Blog for a Kotlin Appilcation:
https://blogs.sap.com/2020/02/17/kotlin-application-with-sap-gateway/
Information on the xlsx format:
https://en.wikipedia.org/wiki/Microsoft_Excel#File_formats
https://en.wikipedia.org/wiki/Office_Open_XML
Open Source project abap2xlsx:
https://wiki.scn.sap.com/wiki/display/ABAP/abap2xlsx
Blog to create an xlsx file:
https://abapblog.com/articles/tricks/33-create-xlsx-mhtml-file-from-internal-table-in-background
An Excellent Learning website:
Eclipse Shortcuts:
https://blogs.sap.com/2017/07/12/abap-in-eclipse-keyboard-shortcuts-you-cannot-miss-cheat-sheet/
Some excellent blogs for new ABAP Language features:
https://blogs.sap.com/2015/10/25/abap-740-quick-reference/
https://blogs.sap.com/2016/03/02/old-and-new-abap-syntax-overview-sheet/
Blog to dynamically generate a SAPGUI Report from a CDS View, using Excel for configuration:
Nice work. An alternative to CUTE (alternative to SM30). What about proposing your code in a Git repository via abapGit?
Thanks! I always keep my code sharing as simple as Copy > Paste > Run. You can easily turn these local classes into global classes.
Jonathan,
Great stuff - starts deceptively nice and slow, but then really takes off, and before you know it, it is like drinking from a fire hose!
To git or not to git: Here, I like the simple cut and paste approach as well. Because it is straightforward.
Best,
Mike
Thanks Michael...tried abapGit a couple years ago on a large project, and lost 2 days of my life :-)...but maybe it's improved since then.
Hi Jonathan
Great work explained in perfect followable way... Thanks for sharing good stuffs to community... And for your great effort...
Regards
Abinath
Hi Jonathan,
thanks for this write-up and the code which goes along with it! I’m fairly certain that it won’t take long for us to make use of the XLSX-up and download part.
I do have one suggestion for your blog post: how about moving the section about the CDS-view and OData access from Excel to a separate blog post as it – as you already mention – goes off on a bit of a tangent? My reason for this suggestion is, that while the first part of your blog post is something I can easily make use of, the CDS/OData part doesn’t fall into this category because we don’t have access to transaction SEGW and wouldn’t be able/allowed to set up a connection like this on our own. Not to mention that the Excel-version I have access to, doesn’t even have the OData-option available
. This kind of restriction may apply to others as well I would imagine.
Cheers
Bärbel
Hi:
I found your program use an FM named: RPM_DX_PATH_FILE_SPLIT which belong to S4CORE, it means this program can only be used for S/4 environment, is any alternative for this FM on SAP_ABA layer so that this program can be used on purely NetWeaver environment.
BRs
FengLei(Jason)
Hi Jason, That function module is very simple, you can just write the code yourself....here is the code within that FM:
Thanks for this great blog.
Easier even then re-creating the RPM_DX_PATH_FILE_SPLIT function is to use the function
/SAPDMC/LSM_PATH_FILE_SPLIT instead. Worked like a charm in my case.
Have a nice weekend
hello thanx for sharing,
i'm facing an issue with
because i have a huge number of line and having a dump for lack of memory. BC guys won't allow more memory.
Any advice to export when you have million of records?
Thanx
Many thanks 🙂 Its exactly what I needed.
I have now an easy way to fill customizing tables. Of Course they also have a maintenance view, but with drop-down fields that won't allow copy & paste.
This is the fastest way to get the data in and out :-).
Thanks alot!
I got one problem though, the date/time format.
Ones i download to excel it auto-formats to date with (in my case) to YYYY-MM-DD.
But ones i upload again it gets all scrambled. It shows like YYYY-MM-.
It seems like the format is set to 8 char and carries to "formating" back to backend.
My user is set to YYYY-MM.DD too.
Kristian
Hi Jonathan, thank you so much for this!!
I have a questions regarding CDS definition.
Are you defined the CDS in SAP Gateway system or in the Main SAP system (Backend)?
I have my z table defined in my local SAP system (1) and I have another SAP system as a Gateway (2).
Best regards!
Matías