Excel — Report with Selection and Export
A report as it is written every day - a selection with ranges, the result in a table with a total, and the same rows exported as .xlsx with XCO
- Class
z2ui5_cl_smps_app_031- Repository
- abap2UI5/samples-stack
- UI5 libraries
- sap.m, sap.ui.core, sap.ui.layout
- 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.Column, sap.m.ColumnListItem, sap.m.DatePicker, sap.m.HBox, sap.m.Input, sap.m.Label, sap.m.Page, sap.m.Shell, sap.m.StepInput, sap.m.Table, sap.m.Text, sap.m.Title, sap.m.Toolbar, sap.m.ToolbarSpacer, sap.ui.core.mvc.View, sap.ui.layout.form.SimpleForm
The class
" @keywords report selection screen filter range export excel xlsx download travel sql
" @summary A report as it is written every day - a selection with ranges, the result in a table with a total, and the same rows exported as .xlsx with XCO
"! <p class="shorttext synchronized">Excel - Report with Selection and Export</p>
"! The classic report in three parts, each one a few lines:
"!
"! 1. the selection - agency, customer and a date window, each field turned
"! into a range table, so an empty field means "all" exactly as on a
"! selection screen
"! 2. the result - one SELECT on the CDS view entity with the ranges in its
"! WHERE, shown in a table with the number of rows and a total
"! 3. the export - the rows on the screen written into an .xlsx with the XCO
"! XLSX library and handed to the browser as a download
"!
"! SELECT FROM z2ui5_r_smps_trv FIELDS ...
"! WHERE AgencyId IN @t_agency
"! AND CustomerId IN @t_customer
"! AND BeginDate IN @t_begin
"! INTO TABLE @DATA(t_result).
"!
"! The export writes what the report shows, not a second SELECT, so the file
"! and the screen cannot disagree. Reads the travels of package 03.
CLASS z2ui5_cl_smps_app_031 DEFINITION PUBLIC.
PUBLIC SECTION.
INTERFACES z2ui5_if_app.
TYPES:
BEGIN OF ty_s_selection,
agency_id TYPE string,
customer_id TYPE string,
"! yyyyMMdd, the value format of the DatePicker
begin_from TYPE string,
begin_to TYPE string,
max_rows TYPE i,
END OF ty_s_selection.
DATA s_selection TYPE ty_s_selection.
TYPES:
BEGIN OF ty_s_travel,
travel_id TYPE string,
agency_id TYPE string,
customer_id TYPE string,
begin_date TYPE string,
end_date TYPE string,
total_price TYPE string,
currency TYPE string,
status TYPE string,
END OF ty_s_travel.
TYPES ty_t_travel TYPE STANDARD TABLE OF ty_s_travel WITH EMPTY KEY.
DATA t_travels TYPE ty_t_travel.
"! the line under the table - number of rows and the total per currency
DATA summary TYPE string.
PROTECTED SECTION.
DATA client TYPE REF TO z2ui5_if_client.
METHODS view_display.
METHODS data_search.
METHODS data_export.
METHODS on_event.
PRIVATE SECTION.
ENDCLASS.
CLASS z2ui5_cl_smps_app_031 IMPLEMENTATION.
METHOD z2ui5_if_app~main.
me->client = client.
IF client->check_on_init( ).
s_selection-max_rows = 200.
data_search( ).
view_display( ).
ELSEIF 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:form` v = `sap.ui.layout.form` ).
DATA(page) = view->ele( `Shell`
)->ele( `Page`
)->a( n = `title` v = `abap2UI5 - Excel - 31 Report with Selection and Export`
)->a( n = `showNavButton` b = client->check_app_prev_stack( )
)->a( n = `navButtonPress` v = client->_event_nav_app_leave( ) ).
page->ele( n = `SimpleForm` ns = `form`
)->a( n = `title` v = `Selection`
)->a( n = `editable` b = abap_true
)->ele( n = `content` ns = `form`
)->tag( `Label`
)->a( n = `text` v = `Agency`
)->tag( `Input`
)->a( n = `placeholder` v = `all`
)->a( n = `value` v = client->_bind( s_selection-agency_id )
)->a( n = `submit` v = client->_event( `SEARCH` )
)->tag( `Label`
)->a( n = `text` v = `Customer`
)->tag( `Input`
)->a( n = `placeholder` v = `all`
)->a( n = `value` v = client->_bind( s_selection-customer_id )
)->a( n = `submit` v = client->_event( `SEARCH` )
)->tag( `Label`
)->a( n = `text` v = `Begin date from / to`
)->tag( `DatePicker`
)->a( n = `value` v = client->_bind( s_selection-begin_from )
)->a( n = `valueFormat` v = `yyyyMMdd`
)->tag( `DatePicker`
)->a( n = `value` v = client->_bind( s_selection-begin_to )
)->a( n = `valueFormat` v = `yyyyMMdd`
)->tag( `Label`
)->a( n = `text` v = `Maximum rows`
)->tag( `StepInput`
)->a( n = `value` v = client->_bind( s_selection-max_rows )
)->a( n = `min` v = `1`
)->a( n = `max` v = `5000`
)->a( n = `step` v = `100`
)->tag( `Label`
)->ele( `HBox`
)->tag( `Button`
)->a( n = `press` v = client->_event( `SEARCH` )
)->a( n = `text` v = `Search`
)->a( n = `type` v = `Emphasized`
)->a( n = `icon` v = `sap-icon://search`
)->tag( `Button`
)->a( n = `press` v = client->_event( `RESET` )
)->a( n = `text` v = `Reset`
)->a( n = `class` v = `sapUiTinyMarginBegin` ).
DATA(table) = page->ele( `Table`
)->a( n = `items` v = client->_bind( t_travels )
)->a( n = `noDataText` v = `No travel matches the selection` ).
table->ele( `headerToolbar`
)->ele( `Toolbar`
)->tag( `Title`
)->a( n = `text` v = client->_bind( summary )
)->tag( `ToolbarSpacer`
)->tag( `Button`
)->a( n = `press` v = client->_event( `EXPORT` )
)->a( n = `text` v = `Export to Excel`
)->a( n = `icon` v = `sap-icon://excel-attachment` ).
table->ele( `columns`
)->ele( `Column`
)->tag( `Text`
)->a( n = `text` v = `Travel`
)->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 Date`
)->end(
)->ele( `Column`
)->tag( `Text`
)->a( n = `text` v = `End Date`
)->end(
)->ele( `Column`
)->a( n = `hAlign` v = `End`
)->tag( `Text`
)->a( n = `text` v = `Total Price`
)->end(
)->ele( `Column`
)->tag( `Text`
)->a( n = `text` v = `Status` ).
table->ele( `items`
)->ele( `ColumnListItem`
)->ele( `cells`
)->tag( `Text`
)->a( n = `text` v = `{TRAVEL_ID}`
)->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 = `{TOTAL_PRICE} {CURRENCY}`
)->tag( `Text`
)->a( n = `text` v = `{STATUS}` ).
client->view_display( view->stringify( ) ).
ENDMETHOD.
METHOD on_event.
CASE client->get_event( ).
WHEN `SEARCH`.
data_search( ).
WHEN `RESET`.
s_selection = VALUE #( max_rows = 200 ).
data_search( ).
WHEN `EXPORT`.
data_export( ).
ENDCASE.
ENDMETHOD.
METHOD data_search.
TYPES ty_r_agency TYPE RANGE OF z2ui5_t_smps_trv-agency_id.
TYPES ty_r_customer TYPE RANGE OF z2ui5_t_smps_trv-customer_id.
TYPES ty_r_date TYPE RANGE OF z2ui5_t_smps_trv-begin_date.
TYPES:
BEGIN OF ty_s_total,
currency TYPE z2ui5_t_smps_trv-currency_code,
amount TYPE z2ui5_t_smps_trv-total_price,
END OF ty_s_total.
DATA t_total TYPE SORTED TABLE OF ty_s_total WITH UNIQUE KEY currency.
IF NOT matches( val = s_selection-agency_id && s_selection-customer_id regex = `^\d*$` ).
client->message_box_display( text = `Agency and customer are numbers - leave a field empty to select all` type = `error` ).
RETURN.
ENDIF.
" an empty range selects everything - the selection-screen behavior,
" without a branch per field
DATA(t_agency) = VALUE ty_r_agency( ).
IF s_selection-agency_id IS NOT INITIAL.
t_agency = VALUE #( ( sign = `I` option = `EQ` low = s_selection-agency_id ) ).
ENDIF.
DATA(t_customer) = VALUE ty_r_customer( ).
IF s_selection-customer_id IS NOT INITIAL.
t_customer = VALUE #( ( sign = `I` option = `EQ` low = s_selection-customer_id ) ).
ENDIF.
" a window open on one side is a GE or an LE, open on both no range at all
DATA(t_begin) = VALUE ty_r_date( ).
IF s_selection-begin_from IS NOT INITIAL AND s_selection-begin_to IS NOT INITIAL.
t_begin = VALUE #( ( sign = `I` option = `BT` low = s_selection-begin_from high = s_selection-begin_to ) ).
ELSEIF s_selection-begin_from IS NOT INITIAL.
t_begin = VALUE #( ( sign = `I` option = `GE` low = s_selection-begin_from ) ).
ELSEIF s_selection-begin_to IS NOT INITIAL.
t_begin = VALUE #( ( sign = `I` option = `LE` low = s_selection-begin_to ) ).
ENDIF.
DATA(max_rows) = COND i( WHEN s_selection-max_rows > 0 THEN s_selection-max_rows ELSE 200 ).
SELECT FROM z2ui5_r_smps_trv
FIELDS TravelId, AgencyId, CustomerId, BeginDate, EndDate, TotalPrice, CurrencyCode, OverallStatus
WHERE AgencyId IN @t_agency
AND CustomerId IN @t_customer
AND BeginDate IN @t_begin
ORDER BY BeginDate, TravelId
INTO TABLE @DATA(t_result) UP TO @max_rows ROWS.
t_travels = VALUE #( FOR r IN t_result
( travel_id = |{ r-travelid ALPHA = OUT }|
agency_id = |{ r-agencyid ALPHA = OUT }|
customer_id = |{ r-customerid ALPHA = OUT }|
begin_date = |{ r-begindate DATE = ISO }|
end_date = |{ r-enddate DATE = ISO }|
total_price = |{ r-totalprice }|
currency = |{ r-currencycode }|
status = z2ui5_cl_smps_context=>status_get_text( r-overallstatus ) ) ).
" a total only makes sense per currency - summed in the types of the
" business object, not from the strings on the screen
LOOP AT t_result INTO DATA(result).
DATA(total) = VALUE ty_s_total( currency = result-currencycode amount = result-totalprice ).
COLLECT total INTO t_total.
ENDLOOP.
summary = |{ lines( t_travels ) } travels| &&
COND #( WHEN lines( t_travels ) = max_rows THEN | (the first { max_rows } - narrow the selection)| ELSE `` ) &&
REDUCE string( INIT s = `` FOR t IN t_total NEXT s = |{ s }, { t-amount } { t-currency }| ).
ENDMETHOD.
METHOD data_export.
IF t_travels IS INITIAL.
client->message_toast_display( `Nothing to export - the selection found no travel` ).
RETURN.
ENDIF.
" the rows on the screen, not a second SELECT - the file shows exactly
" what the user looked at. Six columns, A to F, as z2ui5_cl_smps_xlsx
" writes them
DATA(t_cells) = VALUE z2ui5_cl_smps_xlsx=>ty_t_row(
( a = `Travel` b = `Customer` c = `Begin Date` d = `End Date` e = `Total Price` f = `Status` ) ).
LOOP AT t_travels INTO DATA(travel).
APPEND VALUE #( a = travel-travel_id
b = travel-customer_id
c = travel-begin_date
d = travel-end_date
e = |{ travel-total_price } { travel-currency }|
f = travel-status ) TO t_cells.
ENDLOOP.
DATA(xlsx) = z2ui5_cl_smps_xlsx=>write( t_cells ).
DATA(file_name) = |travels_{ cl_abap_context_info=>get_system_date( ) }.xlsx|.
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 ) }| )
( file_name ) ) ).
ENDMETHOD.
ENDCLASS.