RAP - Load Excel File
In this practical example we look at processing Excel in ABAP with the new XCO classes and how we get the file into our RAP object.
Table of contents
In this article we want to integrate the processing of the Excel file into our application and implement various other steps.
Introduction
SAP provides its own XCO API for processing Excel files, which you can use to read and now also to write. In today's example, we are extending our Report Pattern App with an action to read the uploaded Excel file, parse it and thus update the data. We then want to remove the file again if the processing has been successful so far.
Extension
Before we start with the implementation, we need to extend the app with an action that we can then use for development. The action should display a popup where we can make further settings. To do this, we create an abstract entity.
@EndUserText.label: 'Excel Popup'
define abstract entity ZBS_S_DRPExcelPopup
{
@EndUserText.label: 'Test run'
TestRun : abap_boolean;
}
In the next step, we add the action in the behavior definition "ZBS_R_DRPCurrency". A data record must be marked for the action, we would like to display a popup beforehand (parameter) and the action should return an instance of itself to update the data after processing.
action LoadExcelContent parameter ZBS_S_DRPExcelPopup result [1] $self;
So that we can then use the action in the app, we have to declare it in the projection "ZBS_C_DRPCurrency".
use action LoadExcelContent;
Finally, we extend the metadata "ZBS_C_DRPCurrency" to find a place for the button on the UI. We would like to display the button on the detail screen of the data record, so we bind it to the IDENTIFICATION.
@UI:{
lineItem: [{ position: 10 }],
selectionField: [{ position: 10 }],
identification: [{ type: #FOR_ACTION, dataAction: 'LoadExcelContent', label: 'Load Excel' }]
}
Currency;
The button should now be visible on the object page of each data record. If you click on it, a pop-up should appear before processing, asking for the test run. We are now finished with the extension and can define the action.
Excel
In this section we now want to read in and process the uploaded document.
File
In this tutorial we will use the following file for testing. It contains more countries than are actually available in a data record. In addition, we have another column that we could use for evaluations. The file only has one worksheet if we want to read it in.
We upload the Excel file to our entity. For our example, we can load the file in EUR or AED, since we have data sets for both currencies.
Implementation
In order to be able to process the file, we must implement the action in RAP. In the first step, let's start by reading the current data set and all associated countries in order to compare them later. Since we are only processing one data set, we read the first key and the first data set, but also implement appropriate error handling.
READ ENTITIES OF ZBS_R_DRPCurrency IN LOCAL MODE
ENTITY Currency FIELDS ( Currency ExcelAttachement ) WITH CORRESPONDING #( keys )
RESULT DATA(lt_attachement)
ENTITY Currency BY \_Country ALL FIELDS WITH CORRESPONDING #( keys )
RESULT DATA(lt_countries).
TRY.
DATA(ls_key) = keys[ 1 ].
DATA(ls_attachement) = lt_attachement[ 1 ].
CATCH cx_sy_itab_line_not_found.
INSERT new_message( id = 'ZBS_DEMO_RAP_PATTERN'
number = '003'
severity = if_abap_behv_message=>severity-error )
INTO TABLE reported-%other.
RETURN.
ENDTRY.
In the next step, we parse the Excel file to get the data that we want to process. To do this, we outsource the logic to a separate method and create our own exception that we can attach directly to the log in the event of an error. Finally, we create a message for the user telling us how many data records we found in the Excel file.
TRY.
DATA(lt_excel) = convert_excel_file_to_table( ls_attachement-excelattachement ).
CATCH zcx_drp_excel_error INTO DATA(lo_excel_error).
INSERT lo_excel_error INTO TABLE reported-%other.
RETURN.
ENDTRY.
INSERT new_message( id = 'ZBS_DEMO_RAP_PATTERN'
number = '005'
severity = if_abap_behv_message=>severity-success
v1 = lines( lt_excel ) )
INTO TABLE reported-%other.
Here you can find the method for parsing the file. First of all, we check whether a stream, i.e. an upload, is available. In the next step, we open the file and load the first worksheet. If this is not available, we generate an error message. We then try to read the Excel file from line two and save the headings.
IF id_stream IS INITIAL.
RAISE EXCEPTION NEW zcx_drp_excel_error( textid = VALUE #( msgid = 'ZBS_DEMO_RAP_PATTERN'
msgno = '001' ) ).
ENDIF.
DATA(lo_sheet) = xco_cp_xlsx=>document->for_file_content( id_stream
)->read_access( )->get_workbook(
)->worksheet->at_position( 1 ).
IF NOT lo_sheet->exists( ).
RAISE EXCEPTION NEW zcx_drp_excel_error( textid = VALUE #( msgid = 'ZBS_DEMO_RAP_PATTERN'
msgno = '002' ) ).
ENDIF.
DATA(lo_pattern) = xco_cp_xlsx_selection=>pattern_builder->simple_from_to(
)->from_column( xco_cp_xlsx=>coordinate->for_alphabetic_value( 'A' )
)->from_row( xco_cp_xlsx=>coordinate->for_numeric_value( 2 )
)->get_pattern( ).
lo_sheet->select( lo_pattern
)->row_stream(
)->operation->write_to( REF #( rt_result )
)->set_value_transformation( xco_cp_xlsx_read_access=>value_transformation->string_value
)->execute( ).
If we are in the test run, then the processing ends here and we leave the logic. We give the user a corresponding warning.
IF ls_key-%param-TestRun = abap_true.
INSERT new_message( id = 'ZBS_DEMO_RAP_PATTERN'
number = '004'
severity = if_abap_behv_message=>severity-warning )
INTO TABLE reported-%other.
RETURN.
ENDIF.
In order to be able to make the changes, we prepare three tables that will later pass the various changes to the Entity Manipulation Language (EML) statement.
DATA lt_currency_modify TYPE TABLE FOR UPDATE ZBS_R_DRPCurrencyCurrency.
DATA lt_countries_create TYPE TABLE FOR CREATE ZBS_R_DRPCurrency\_Country.
DATA lt_countries_modify TYPE TABLE FOR UPDATE ZBS_R_DRPCurrencyCountry.
To remove the attachment, we set the fields to empty. You should also not forget to activate the corresponding %CONTROL structures so that the changes can be made.
INSERT VALUE #( %tky = ls_attachement-%tky
excelattachement = ''
excelmimetype = ''
excelfilename = ''
%control-excelattachement = if_abap_behv=>mk-on
%control-excelmimetype = if_abap_behv=>mk-on
%control-excelfilename = if_abap_behv=>mk-on )
INTO TABLE lt_currency_modify.
In the next section of code, we process all records in the Excel file that have the same currency, i.e. that match our data set. Then we check against the country table that we read before. If a country already exists, we update the additional fields, in this case the ranking. If a store does not yet exist, we adopt the new data set.
INSERT VALUE #( currency = ls_attachement-Currency )
INTO TABLE lt_countries_create REFERENCE INTO DATA(lr_new).
LOOP AT lt_excel INTO DATA(ls_excel) WHERE currency = ls_attachement-Currency.
TRY.
DATA(ls_country) = lt_countries[ Country = ls_excel-country ].
INSERT VALUE #( currency = ls_country-Currency
country = ls_country-Country
countryranking = ls_excel-ranking
%control-countryranking = if_abap_behv=>mk-on )
INTO TABLE lt_countries_modify.
CATCH cx_sy_itab_line_not_found.
INSERT VALUE #( %cid = xco_cp=>uuid( )->value
currency = ls_excel-currency
country = ls_excel-country
countryranking = ls_excel-ranking
%control-currency = if_abap_behv=>mk-on
%control-country = if_abap_behv=>mk-on
%control-countryranking = if_abap_behv=>mk-on )
INTO TABLE lr_new->%target.
ENDTRY.
ENDLOOP.
After processing, we can pass our three tables to the EML statement and register the changes in the transactional buffer of the RAP object. The CREATE for Country is only possible via Currency, because we defined it that way in the RAP object in the behavior definition. There, the Create is activated on the association.
MODIFY ENTITIES OF ZBS_R_DRPCurrency IN LOCAL MODE
ENTITY Currency UPDATE FROM lt_currency_modify
ENTITY Country UPDATE FROM lt_countries_modify
ENTITY Currency CREATE BY \_Country FROM lt_countries_create.
At the end of the processing, we give the user a message about how many new data records have been added and how many have changed. This completes the implementation of the method.
INSERT new_message( id = 'ZBS_DEMO_RAP_PATTERN'
number = '007'
severity = if_abap_behv_message=>severity-success
v1 = lines( lt_countries_create[ 1 ]-%target ) )
INTO TABLE reported-%other.
INSERT new_message( id = 'ZBS_DEMO_RAP_PATTERN'
number = '006'
severity = if_abap_behv_message=>severity-success
v1 = lines( lt_countries_modify ) )
INTO TABLE reported-%other.
Test
In this section we want to test the new feature. To do this we load the Excel file in the currency EUR. Currently we have also created Germany and Denmark with a bad ranking here.
After we have carried out the action, we receive a success message that two new countries have been created and one country has been changed.
After updating the interface the changes should now be active. The file has now disappeared and the country list is up to date again.
Complete example
As always, you can find all changes in the corresponding commit of the GitHub repository. You can also find the behavior implementation here so that you can understand the current status of the implementation.
CLASS lhc_Currency DEFINITION INHERITING FROM cl_abap_behavior_handler.
PRIVATE SECTION.
TYPES: BEGIN OF ts_excel,
currency TYPE string,
country TYPE string,
ranking TYPE string,
flag TYPE string,
END OF ts_excel.
TYPES tt_excel TYPE STANDARD TABLE OF ts_excel WITH EMPTY KEY.
METHODS get_instance_authorizations FOR INSTANCE AUTHORIZATION
IMPORTING keys REQUEST requested_authorizations FOR Currency RESULT result.
METHODS loadexcelcontent FOR MODIFY
IMPORTING keys FOR ACTION currency~loadexcelcontent.
METHODS convert_excel_file_to_table
IMPORTING id_stream TYPE xstring
RETURNING VALUE(rt_result) TYPE tt_excel
RAISING zcx_drp_excel_error.
ENDCLASS.
CLASS lhc_Currency IMPLEMENTATION.
METHOD get_instance_authorizations.
ENDMETHOD.
METHOD LoadExcelContent.
DATA lt_currency_modify TYPE TABLE FOR UPDATE ZBS_R_DRPCurrencyCurrency.
DATA lt_countries_create TYPE TABLE FOR CREATE ZBS_R_DRPCurrency\_Country.
DATA lt_countries_modify TYPE TABLE FOR UPDATE ZBS_R_DRPCurrencyCountry.
READ ENTITIES OF ZBS_R_DRPCurrency IN LOCAL MODE
ENTITY Currency FIELDS ( Currency ExcelAttachement ) WITH CORRESPONDING #( keys )
RESULT DATA(lt_attachement)
ENTITY Currency BY \_Country ALL FIELDS WITH CORRESPONDING #( keys )
RESULT DATA(lt_countries).
TRY.
DATA(ls_key) = keys[ 1 ].
DATA(ls_attachement) = lt_attachement[ 1 ].
CATCH cx_sy_itab_line_not_found.
INSERT new_message( id = 'ZBS_DEMO_RAP_PATTERN'
number = '003'
severity = if_abap_behv_message=>severity-error )
INTO TABLE reported-%other.
RETURN.
ENDTRY.
TRY.
DATA(lt_excel) = convert_excel_file_to_table( ls_attachement-excelattachement ).
CATCH zcx_drp_excel_error INTO DATA(lo_excel_error).
INSERT lo_excel_error INTO TABLE reported-%other.
RETURN.
ENDTRY.
INSERT new_message( id = 'ZBS_DEMO_RAP_PATTERN'
number = '005'
severity = if_abap_behv_message=>severity-success
v1 = lines( lt_excel ) )
INTO TABLE reported-%other.
IF ls_key-%param-TestRun = abap_true.
INSERT new_message( id = 'ZBS_DEMO_RAP_PATTERN'
number = '004'
severity = if_abap_behv_message=>severity-warning )
INTO TABLE reported-%other.
RETURN.
ENDIF.
INSERT VALUE #( %tky = ls_attachement-%tky
excelattachement = ''
excelmimetype = ''
excelfilename = ''
%control-excelattachement = if_abap_behv=>mk-on
%control-excelmimetype = if_abap_behv=>mk-on
%control-excelfilename = if_abap_behv=>mk-on )
INTO TABLE lt_currency_modify.
INSERT VALUE #( currency = ls_attachement-Currency )
INTO TABLE lt_countries_create REFERENCE INTO DATA(lr_new).
LOOP AT lt_excel INTO DATA(ls_excel) WHERE currency = ls_attachement-Currency.
TRY.
DATA(ls_country) = lt_countries[ Country = ls_excel-country ].
INSERT VALUE #( currency = ls_country-Currency
country = ls_country-Country
countryranking = ls_excel-ranking
%control-countryranking = if_abap_behv=>mk-on )
INTO TABLE lt_countries_modify.
CATCH cx_sy_itab_line_not_found.
INSERT VALUE #( %cid = xco_cp=>uuid( )->value
currency = ls_excel-currency
country = ls_excel-country
countryranking = ls_excel-ranking
%control-currency = if_abap_behv=>mk-on
%control-country = if_abap_behv=>mk-on
%control-countryranking = if_abap_behv=>mk-on )
INTO TABLE lr_new->%target.
ENDTRY.
ENDLOOP.
MODIFY ENTITIES OF ZBS_R_DRPCurrency IN LOCAL MODE
ENTITY Currency UPDATE FROM lt_currency_modify
ENTITY Country UPDATE FROM lt_countries_modify
ENTITY Currency CREATE BY \_Country FROM lt_countries_create.
INSERT new_message( id = 'ZBS_DEMO_RAP_PATTERN'
number = '007'
severity = if_abap_behv_message=>severity-success
v1 = lines( lt_countries_create[ 1 ]-%target ) )
INTO TABLE reported-%other.
INSERT new_message( id = 'ZBS_DEMO_RAP_PATTERN'
number = '006'
severity = if_abap_behv_message=>severity-success
v1 = lines( lt_countries_modify ) )
INTO TABLE reported-%other.
ENDMETHOD.
METHOD convert_excel_file_to_table.
IF id_stream IS INITIAL.
RAISE EXCEPTION NEW zcx_drp_excel_error( textid = VALUE #( msgid = 'ZBS_DEMO_RAP_PATTERN'
msgno = '001' ) ).
ENDIF.
DATA(lo_sheet) = xco_cp_xlsx=>document->for_file_content( id_stream
)->read_access( )->get_workbook(
)->worksheet->at_position( 1 ).
IF NOT lo_sheet->exists( ).
RAISE EXCEPTION NEW zcx_drp_excel_error( textid = VALUE #( msgid = 'ZBS_DEMO_RAP_PATTERN'
msgno = '002' ) ).
ENDIF.
DATA(lo_pattern) = xco_cp_xlsx_selection=>pattern_builder->simple_from_to(
)->from_column( xco_cp_xlsx=>coordinate->for_alphabetic_value( 'A' )
)->from_row( xco_cp_xlsx=>coordinate->for_numeric_value( 2 )
)->get_pattern( ).
lo_sheet->select( lo_pattern
)->row_stream(
)->operation->write_to( REF #( rt_result )
)->set_value_transformation( xco_cp_xlsx_read_access=>value_transformation->string_value
)->execute( ).
ENDMETHOD.
ENDCLASS.
CLASS lsc_ZBS_R_DRPCURRENCY DEFINITION INHERITING FROM cl_abap_behavior_saver.
PROTECTED SECTION.
METHODS
save_modified REDEFINITION.
METHODS
cleanup_finalize REDEFINITION.
ENDCLASS.
CLASS lsc_ZBS_R_DRPCURRENCY IMPLEMENTATION.
METHOD save_modified.
LOOP AT update-currency INTO DATA(ls_new_currency).
DATA(ls_modify_currency) = CORRESPONDING zbs_drp_addcurr( ls_new_currency MAPPING FROM ENTITY ).
ls_modify_currency-last_editor = cl_abap_context_info=>get_user_technical_name( ).
MODIFY zbs_drp_addcurr FROM @ls_modify_currency.
ENDLOOP.
LOOP AT create-country INTO DATA(ls_create_country).
INSERT zbs_drp_country FROM @( CORRESPONDING zbs_drp_country( ls_create_country MAPPING FROM ENTITY ) ).
ENDLOOP.
LOOP AT update-country INTO DATA(ls_update_country).
UPDATE zbs_drp_country FROM @( CORRESPONDING zbs_drp_country( ls_update_country MAPPING FROM ENTITY ) ).
ENDLOOP.
LOOP AT delete-country INTO DATA(ls_delete_country).
DELETE zbs_drp_country FROM @( CORRESPONDING zbs_drp_country( ls_delete_country MAPPING FROM ENTITY ) ).
ENDLOOP.
ENDMETHOD.
METHOD cleanup_finalize.
ENDMETHOD.
ENDCLASS.
Uploading files
The attachment method is currently well suited for storing small files that should be available directly to the user. However, if you only want to use the upload to supply the current entity and you don't actually need the file anymore afterwards, then an action in which you can pass the file directly is recommended. There are already open source projects, such as the Spreadsheet Importer, to implement such a function.
Conclusion
With the new API you can also read and process simple Excel documents. We have explained to you how to use the API correctly in this article.
