Excel — Import Travels into a RAP Business Object
An .xlsx file of travels checked row by row and created in the RAP business object with one MODIFY ENTITIES - every refusal lands on the row it belongs to
- Class
z2ui5_cl_smps_app_028- Repository
- abap2UI5/samples-stack
- UI5 libraries
- sap.m, sap.ui.core, z2ui5.cc
- Lowest UI5 release
- 1.71
- In the browser
- Needs a system - opens in the playground for reading
- Controls it builds
- sap.m.Button, sap.m.CheckBox, sap.m.Column, sap.m.ColumnListItem, sap.m.HBox, sap.m.MessageStrip, sap.m.ObjectStatus, sap.m.Page, sap.m.Shell, sap.m.Table, sap.m.Text, sap.m.Title, sap.m.Toolbar, sap.m.ToolbarSpacer, sap.ui.core.mvc.View, z2ui5.cc.FileUploader
The class
" @keywords excel xlsx import upload mass create rap eml business object travel fileuploader
" @summary An .xlsx file of travels checked row by row and created in the RAP business object with one MODIFY ENTITIES - every refusal lands on the row it belongs to
"! <p class="shorttext synchronized">Excel - Import Travels into a RAP Business Object</p>
"! An Excel import in three steps, each one a roundtrip:
"!
"! 1. Upload - the FileUploader brings the file, the XCO XLSX library reads
"! it (z2ui5_cl_smps_xlsx), and every row is converted and checked in the
"! app. Nothing touches the business object yet.
"! 2. Preview - the table shows each row with its status, so a wrong date or
"! an amount that is no number is seen before anything is created.
"! 3. Import - one MODIFY ENTITIES creates all rows at once. The %cid of an
"! instance is the Excel row number, so every message the business object
"! sends back - from the CREATE or from the validations at COMMIT - is
"! shown on the row it belongs to:
"!
"! MODIFY ENTITIES OF z2ui5_r_smps_trv
"! ENTITY travel
"! CREATE FIELDS ( agencyid customerid begindate enddate ... )
"! WITH VALUE #( FOR r IN rows ( %cid = |ROW_{ r-row }| ... ) )
"! MAPPED DATA(s_mapped) FAILED DATA(s_failed) REPORTED DATA(s_reported).
"!
"! COMMIT ENTITIES RESPONSE OF z2ui5_r_smps_trv
"! FAILED DATA(s_failed_commit) REPORTED DATA(s_reported_commit).
"!
"! A COMMIT is all or nothing: one refused row and no travel is saved. With
"! "Skip refused rows" the app rolls back, drops the refused rows and creates
"! the rest in a second attempt.
"!
"! Needs the travel business object z2ui5_r_smps_trv of package 03, which
"! comes with this package's branch. Download Example File writes a sheet
"! with one row the business object refuses.
CLASS z2ui5_cl_smps_app_028 DEFINITION PUBLIC.
PUBLIC SECTION.
INTERFACES z2ui5_if_app.
TYPES:
BEGIN OF ty_s_row,
"! the row number in the sheet - the first data row is 2
row TYPE i,
agency_id TYPE string,
customer_id TYPE string,
begin_date TYPE string,
end_date TYPE string,
booking_fee TYPE string,
currency TYPE string,
"! None, Success, Warning or Error - the sap.ui.core.ValueState
state TYPE string,
status TYPE string,
travel_id TYPE string,
END OF ty_s_row.
TYPES ty_t_row TYPE STANDARD TABLE OF ty_s_row WITH EMPTY KEY.
"! the picked file - a data URL, data:<mime>;base64,<payload>
DATA file TYPE string.
DATA file_name TYPE string.
DATA t_rows TYPE ty_t_row.
DATA skip_refused TYPE abap_bool.
PROTECTED SECTION.
TYPES:
BEGIN OF ty_s_travel,
row TYPE i,
agency_id TYPE z2ui5_t_smps_trv-agency_id,
customer_id TYPE z2ui5_t_smps_trv-customer_id,
begin_date TYPE d,
end_date TYPE d,
booking_fee TYPE z2ui5_t_smps_trv-booking_fee,
currency TYPE z2ui5_t_smps_trv-currency_code,
END OF ty_s_travel.
TYPES ty_t_travel TYPE STANDARD TABLE OF ty_s_travel WITH EMPTY KEY.
DATA client TYPE REF TO z2ui5_if_client.
METHODS view_display.
METHODS file_read.
METHODS rows_import.
METHODS example_download.
METHODS on_event.
"! one row of the sheet into the types of the business object - the
"! problems found go into the row's status, which stays Error unless
"! travel is complete
METHODS row_convert
EXPORTING
travel TYPE ty_s_travel
CHANGING
row TYPE ty_s_row.
"! a date cell - the serial number Excel stores, or a text 2026-12-31
METHODS date_convert
IMPORTING
val TYPE string
RETURNING
VALUE(result) TYPE d.
"! one attempt: CREATE all travels, COMMIT, and on any refusal ROLLBACK -
"! the refused rows are marked in t_rows and returned
METHODS travels_create
IMPORTING
t_travel TYPE ty_t_travel
RETURNING
VALUE(result) TYPE ty_t_travel.
METHODS row_refuse
IMPORTING
row TYPE i
text TYPE string.
PRIVATE SECTION.
ENDCLASS.
CLASS z2ui5_cl_smps_app_028 IMPLEMENTATION.
METHOD z2ui5_if_app~main.
me->client = client.
IF client->check_on_navigated( ).
view_display( ).
ELSEIF client->check_on_event( ).
on_event( ).
ENDIF.
ENDMETHOD.
METHOD view_display.
DATA(view) = z2ui5_cl_ui5_view_builder=>factory(
)->ele( n = `View` ns = `mvc`
)->a( n = `displayBlock` v = `true`
)->a( n = `height` v = `100%`
)->a( n = `xmlns` v = `sap.m`
)->a( n = `xmlns:mvc` v = `sap.ui.core.mvc`
)->a( n = `xmlns:z2ui5` v = `z2ui5.cc` ).
DATA(page) = view->ele( `Shell`
)->ele( `Page`
)->a( n = `title` v = `abap2UI5 - Excel - 28 Import Travels into a RAP Business Object`
)->a( n = `showNavButton` b = client->check_app_prev_stack( )
)->a( n = `navButtonPress` v = client->_event_nav_app_leave( ) ).
page->tag( `MessageStrip`
)->a( n = `text` v = `Columns A to F: agency, customer, begin date, end date, booking fee, currency - row 1 is the header. ` &&
`Upload checks every row, Import creates the travels in Z2UI5_R_SMPS_TRV. No file at hand? Download Example File first.`
)->a( n = `type` v = `Information`
)->a( n = `showIcon` b = abap_true
)->a( n = `class` v = `sapUiSmallMargin` ).
page->ele( `HBox`
)->a( n = `class` v = `sapUiSmallMargin`
)->a( n = `alignItems` v = `Center`
)->tag( n = `FileUploader` ns = `z2ui5`
)->a( n = `placeholder` v = `pick an .xlsx file...`
)->a( n = `fileType` v = `xlsx`
)->a( n = `uploadButtonText` v = `Upload`
)->a( n = `upload` v = client->_event( `UPLOAD` )
)->a( n = `path` v = client->_bind( file_name )
)->a( n = `value` v = client->_bind( file )
)->tag( `Button`
)->a( n = `press` v = client->_event( `EXAMPLE` )
)->a( n = `text` v = `Download Example File`
)->a( n = `icon` v = `sap-icon://download`
)->a( n = `class` v = `sapUiSmallMarginBegin` ).
DATA(table) = page->ele( `Table`
)->a( n = `items` v = client->_bind( t_rows )
)->a( n = `noDataText` v = `Nothing read yet` ).
table->ele( `headerToolbar`
)->ele( `Toolbar`
)->tag( `Title`
)->a( n = `text` v = `Preview`
)->tag( `ToolbarSpacer`
)->tag( `CheckBox`
)->a( n = `text` v = `Skip refused rows`
)->a( n = `selected` v = client->_bind( skip_refused )
)->tag( `Button`
)->a( n = `press` v = client->_event( `IMPORT` )
)->a( n = `text` v = `Import`
)->a( n = `type` v = `Emphasized`
)->a( n = `enabled` b = xsdbool( t_rows IS NOT INITIAL ) ).
table->ele( `columns`
)->ele( `Column`
)->a( n = `width` v = `4rem`
)->tag( `Text`
)->a( n = `text` v = `Row`
)->end(
)->ele( `Column`
)->tag( `Text`
)->a( n = `text` v = `Agency`
)->end(
)->ele( `Column`
)->tag( `Text`
)->a( n = `text` v = `Customer`
)->end(
)->ele( `Column`
)->tag( `Text`
)->a( n = `text` v = `Begin`
)->end(
)->ele( `Column`
)->tag( `Text`
)->a( n = `text` v = `End`
)->end(
)->ele( `Column`
)->tag( `Text`
)->a( n = `text` v = `Fee`
)->end(
)->ele( `Column`
)->tag( `Text`
)->a( n = `text` v = `Status`
)->end(
)->ele( `Column`
)->tag( `Text`
)->a( n = `text` v = `Travel` ).
table->ele( `items`
)->ele( `ColumnListItem`
)->ele( `cells`
)->tag( `Text`
)->a( n = `text` v = `{ROW}`
)->tag( `Text`
)->a( n = `text` v = `{AGENCY_ID}`
)->tag( `Text`
)->a( n = `text` v = `{CUSTOMER_ID}`
)->tag( `Text`
)->a( n = `text` v = `{BEGIN_DATE}`
)->tag( `Text`
)->a( n = `text` v = `{END_DATE}`
)->tag( `Text`
)->a( n = `text` v = `{BOOKING_FEE} {CURRENCY}`
)->tag( `ObjectStatus`
)->a( n = `text` v = `{STATUS}`
)->a( n = `state` v = `{STATE}`
)->tag( `Text`
)->a( n = `text` v = `{TRAVEL_ID}` ).
client->view_display( view->stringify( ) ).
ENDMETHOD.
METHOD on_event.
CASE client->get_event( ).
WHEN `UPLOAD`.
file_read( ).
WHEN `IMPORT`.
rows_import( ).
WHEN `EXAMPLE`.
example_download( ).
ENDCASE.
ENDMETHOD.
METHOD file_read.
" data:<mime>;base64,<payload> - only the payload is the file
DATA(base64) = substring_after( val = file sub = `,` ).
" the file is in t_rows now - kept in the app it would travel along with
" every roundtrip from here on
CLEAR file.
CLEAR t_rows.
TRY.
DATA(t_cells) = z2ui5_cl_smps_xlsx=>read( cl_web_http_utility=>decode_x_base64( base64 ) ).
CATCH cx_root INTO DATA(error).
" not an .xlsx at all, or a damaged one - XCO says why
client->message_box_display( text = |{ file_name }: { error->get_text( ) }| type = `error` ).
" a rebuild, not only the model push: Import is enabled at render
" time from t_rows, and the FileUploader starts over with it
view_display( ).
RETURN.
ENDTRY.
" row 1 is the header; a row without any value is the end of the data
" for a person, not an error
LOOP AT t_cells INTO DATA(cells) FROM 2.
IF cells IS INITIAL.
CONTINUE.
ENDIF.
DATA(row) = VALUE ty_s_row( row = sy-tabix
agency_id = condense( cells-a )
customer_id = condense( cells-b )
begin_date = condense( cells-c )
end_date = condense( cells-d )
booking_fee = condense( cells-e )
currency = to_upper( condense( cells-f ) ) ).
row_convert( CHANGING row = row ).
APPEND row TO t_rows.
ENDLOOP.
DATA(errors) = REDUCE i( INIT n = 0 FOR r IN t_rows WHERE ( state = `Error` ) NEXT n = n + 1 ).
client->message_toast_display( |{ file_name } - { lines( t_rows ) } rows read, { errors } with errors| ).
" a rebuild, not only the model push: Import is enabled at render time
" from t_rows, and the FileUploader starts over with it
view_display( ).
ENDMETHOD.
METHOD row_convert.
" the checks an import can make without the business object: does every
" cell fit the type of its field. What a travel must satisfy beyond that
" - a customer, an end date not before the begin date - is the business
" object's to decide, and it does at the COMMIT
travel = VALUE #( row = row-row ).
DATA(problems) = VALUE string_table( ).
IF matches( val = row-agency_id regex = `^\d{1,6}$` ).
travel-agency_id = row-agency_id.
ELSE.
APPEND |agency '{ row-agency_id }' is not a number of up to 6 digits| TO problems.
ENDIF.
" an empty customer is passed on on purpose - the validation
" validateCustomer of the business object answers it
IF row-customer_id IS INITIAL OR matches( val = row-customer_id regex = `^\d{1,6}$` ).
travel-customer_id = row-customer_id.
ELSE.
APPEND |customer '{ row-customer_id }' is not a number of up to 6 digits| TO problems.
ENDIF.
travel-begin_date = date_convert( row-begin_date ).
IF travel-begin_date IS INITIAL.
APPEND |begin date '{ row-begin_date }' is no date| TO problems.
ENDIF.
travel-end_date = date_convert( row-end_date ).
IF travel-end_date IS INITIAL.
APPEND |end date '{ row-end_date }' is no date| TO problems.
ENDIF.
TRY.
travel-booking_fee = row-booking_fee.
CATCH cx_sy_conversion_error.
APPEND |booking fee '{ row-booking_fee }' is not a number - write it like 20.00| TO problems.
ENDTRY.
travel-currency = row-currency.
IF problems IS NOT INITIAL.
row-state = `Error`.
row-status = concat_lines_of( table = problems sep = `; ` ).
RETURN.
ENDIF.
row-state = `None`.
row-status = `Ready`.
ENDMETHOD.
METHOD date_convert.
" the string value of a number cell may come with a fraction, 45658.0
IF matches( val = val regex = `^\d{1,5}(\.0+)?$` ).
" a date cell: Excel stores the days since 1899-12-30
result = CONV d( '18991230' ) + CONV i( val ).
ELSEIF matches( val = val regex = `^\d{4}-\d{2}-\d{2}$` ).
result = replace( val = val sub = `-` with = `` occ = 0 ).
ELSE.
RETURN.
ENDIF.
" 2026-02-30 has the right shape and is still no date - a date that is
" not valid converts to the day number 0
IF CONV i( result ) = 0.
CLEAR result.
ENDIF.
ENDMETHOD.
METHOD rows_import.
DATA t_travel TYPE ty_t_travel.
LOOP AT t_rows REFERENCE INTO DATA(row) WHERE travel_id IS INITIAL.
row_convert( IMPORTING travel = DATA(travel) CHANGING row = row->* ).
IF row->state <> `Error`.
APPEND travel TO t_travel.
ENDIF.
ENDLOOP.
IF t_travel IS INITIAL.
client->message_box_display( `Nothing to import - upload a file with at least one row without errors first` ).
RETURN.
ENDIF.
DATA(pending) = REDUCE i( INIT n = 0 FOR r IN t_rows WHERE ( travel_id IS INITIAL ) NEXT n = n + 1 ).
IF skip_refused = abap_false AND lines( t_travel ) < pending.
client->message_box_display( text = `Some rows have errors - fix the file, or switch on Skip refused rows` type = `error` ).
RETURN.
ENDIF.
DATA(created_before) = REDUCE i( INIT n = 0 FOR r IN t_rows WHERE ( travel_id IS NOT INITIAL ) NEXT n = n + 1 ).
DATA(t_refused) = travels_create( t_travel ).
" a COMMIT is all or nothing, so the rows that were fine are not saved
" either. Skipping means: the same CREATE once more, without the rows the
" business object refused
IF t_refused IS NOT INITIAL AND skip_refused = abap_true.
DATA(t_retry) = t_travel.
LOOP AT t_refused INTO DATA(refused).
DELETE t_retry WHERE row = refused-row.
ENDLOOP.
IF t_retry IS NOT INITIAL.
LOOP AT t_retry INTO DATA(retry).
READ TABLE t_rows ASSIGNING FIELD-SYMBOL(<row>) WITH KEY row = retry-row.
IF sy-subrc = 0.
<row>-state = `None`.
<row>-status = `Ready`.
ENDIF.
ENDLOOP.
t_refused = travels_create( t_retry ).
ENDIF.
ENDIF.
DATA(created) = REDUCE i( INIT n = 0 FOR r IN t_rows WHERE ( travel_id IS NOT INITIAL ) NEXT n = n + 1 ) - created_before.
DATA(refused_rows) = REDUCE i( INIT n = 0 FOR r IN t_rows WHERE ( state = `Error` ) NEXT n = n + 1 ).
IF refused_rows = 0.
client->message_toast_display( |{ created } travels created| ).
ELSE.
client->message_box_display( text = |{ created } travels created, { refused_rows } rows not imported - see the status column|
type = COND #( WHEN created = 0 THEN `error` ELSE `warning` ) ).
ENDIF.
ENDMETHOD.
METHOD travels_create.
DATA row TYPE i.
DATA texts TYPE string_table.
" the %cid is the Excel row number: it is the one thing the business
" object hands back with each instance, before a key exists - so it is
" what leads every answer back to its row
MODIFY ENTITIES OF z2ui5_r_smps_trv
ENTITY travel
CREATE FIELDS ( agencyid customerid begindate enddate bookingfee currencycode description )
WITH VALUE #( FOR t IN t_travel
( %cid = |ROW_{ t-row }|
agencyid = t-agency_id
customerid = t-customer_id
begindate = t-begin_date
enddate = t-end_date
bookingfee = t-booking_fee
currencycode = t-currency
description = |{ file_name }, row { t-row }| ) )
MAPPED DATA(s_mapped)
FAILED DATA(s_failed)
REPORTED DATA(s_reported).
IF s_failed-travel IS NOT INITIAL.
ROLLBACK ENTITIES.
LOOP AT s_failed-travel INTO DATA(failed).
row = substring_after( val = failed-%cid sub = `ROW_` ).
texts = VALUE #( FOR r IN s_reported-travel WHERE ( %cid = failed-%cid AND %msg IS BOUND )
( r-%msg->if_message~get_text( ) ) ).
row_refuse( row = row text = concat_lines_of( table = texts sep = `; ` ) ).
APPEND VALUE #( row = row ) TO result.
ENDLOOP.
ELSE.
" the validations run now. Their answers carry the key, not the %cid -
" MAPPED is what translates one into the other
COMMIT ENTITIES RESPONSE OF z2ui5_r_smps_trv
FAILED DATA(s_failed_commit)
REPORTED DATA(s_reported_commit).
IF s_failed_commit IS INITIAL.
LOOP AT s_mapped-travel INTO DATA(created).
row = substring_after( val = created-%cid sub = `ROW_` ).
READ TABLE t_rows ASSIGNING FIELD-SYMBOL(<row>) WITH KEY row = row.
IF sy-subrc = 0.
<row>-travel_id = |{ created-travelid ALPHA = OUT }|.
<row>-state = `Success`.
<row>-status = `Created`.
ENDIF.
ENDLOOP.
RETURN.
ENDIF.
" a failed save keeps the instances in the transactional buffer -
" discard them, or the next attempt and abap2UI5's own COMMIT WORK at
" the end of the roundtrip run into them
ROLLBACK ENTITIES.
LOOP AT s_failed_commit-travel INTO DATA(failed_commit).
DATA(mapped) = VALUE #( s_mapped-travel[ travelid = failed_commit-travelid ] OPTIONAL ).
row = substring_after( val = mapped-%cid sub = `ROW_` ).
texts = VALUE #( FOR r IN s_reported_commit-travel WHERE ( travelid = failed_commit-travelid AND %msg IS BOUND )
( r-%msg->if_message~get_text( ) ) ).
row_refuse( row = row text = concat_lines_of( table = texts sep = `; ` ) ).
APPEND VALUE #( row = row ) TO result.
ENDLOOP.
ENDIF.
" one refusal and nothing is saved - the rows the business object did
" not object to are rolled back with the others
LOOP AT t_travel INTO DATA(travel).
IF NOT line_exists( result[ row = travel-row ] ).
READ TABLE t_rows ASSIGNING <row> WITH KEY row = travel-row.
IF sy-subrc = 0.
<row>-state = `Warning`.
<row>-status = `Not saved - another row was refused`.
ENDIF.
ENDIF.
ENDLOOP.
ENDMETHOD.
METHOD row_refuse.
READ TABLE t_rows ASSIGNING FIELD-SYMBOL(<row>) WITH KEY row = row.
IF sy-subrc <> 0.
RETURN.
ENDIF.
<row>-state = `Error`.
" a business object may refuse without a message - saying so beats an
" empty cell
<row>-status = COND #( WHEN text IS INITIAL THEN `Refused by the business object` ELSE text ).
ENDMETHOD.
METHOD example_download.
DATA(today) = cl_abap_context_info=>get_system_date( ).
DATA(t_cells) = VALUE z2ui5_cl_smps_xlsx=>ty_t_row(
( a = `Agency` b = `Customer` c = `Begin Date` d = `End Date` e = `Booking Fee` f = `Currency` )
( a = `70001` b = `1` c = |{ CONV d( today + 30 ) DATE = ISO }| d = |{ CONV d( today + 37 ) DATE = ISO }| e = `20.00` f = `EUR` )
( a = `70002` b = `2` c = |{ CONV d( today + 45 ) DATE = ISO }| d = |{ CONV d( today + 49 ) DATE = ISO }| e = `35.50` f = `EUR` )
" the business object refuses this one at the COMMIT: the end date
" is before the begin date
( a = `70003` b = `3` c = |{ CONV d( today + 60 ) DATE = ISO }| d = |{ CONV d( today + 55 ) DATE = ISO }| e = `15.00` f = `USD` )
( a = `70001` b = `4` c = |{ CONV d( today + 90 ) DATE = ISO }| d = |{ CONV d( today + 97 ) DATE = ISO }| e = `40.00` f = `EUR` ) ).
DATA(xlsx) = z2ui5_cl_smps_xlsx=>write( t_cells ).
client->follow_up_action(
val = client->cs_event-download_b64_file
t_arg = VALUE #( ( |data:{ z2ui5_cl_smps_xlsx=>mime_type };base64,{ cl_web_http_utility=>encode_x_base64( xlsx ) }| )
( `travels.xlsx` ) ) ).
ENDMETHOD.
ENDCLASS.