Spreadsheet
abap2UI5 works with the XLSX APIs on your ABAP system to upload and download spreadsheets, converting between XLSX files and internal tables as needed.
Upload
Convert uploaded XLSX files into an internal table:
abap
CLASS zcl_sample_upload DEFINITION PUBLIC.
PUBLIC SECTION.
INTERFACES z2ui5_if_app.
DATA mv_path TYPE string.
DATA mv_value TYPE string.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_sample_upload IMPLEMENTATION.
METHOD z2ui5_if_app~main.
IF client->check_on_navigated( ).
DATA(view) = z2ui5_cl_ui5_view_builder=>factory(
)->ele( n = `View` ns = `mvc`
)->a( n = `xmlns` v = `sap.m`
)->a( n = `xmlns:mvc` v = `sap.ui.core.mvc`
)->a( n = `xmlns:z2ui5` v = `z2ui5.cc`
)->ele( `Page`
)->tag( n = `FileUploader` ns = `z2ui5`
)->a( n = `value` v = client->_bind( mv_value )
)->a( n = `path` v = client->_bind( mv_path )
)->a( n = `fileType` v = `xlsx`
)->a( n = `placeholder` v = `filepath here...`
)->a( n = `upload` v = client->_event( `UPLOAD` ) ).
client->view_display( view->stringify( ) ).
ELSEIF client->check_on_event( `UPLOAD` ).
DATA(lr_itab) = lcl_help=>itab_get_by_xlsx( mv_value ).
"further process with itab...
client->message_box_display( `xlsx uploaded` ).
ENDIF.
ENDMETHOD.
ENDCLASS.abap
"The helper class converts the uploaded file into an internal table.
"The file uploader returns a data URI string (e.g. 'data:application/...;base64,<payload>'),
"so we first split at ';' and ',' to extract the raw Base64 payload.
"cl_fdt_xl_spreadsheet (from the BRFplus framework) then parses the XLSX binary
"and returns the first worksheet as a dynamic internal table.
CLASS lcl_help DEFINITION.
PUBLIC SECTION.
CLASS-METHODS itab_get_by_xlsx
IMPORTING
VALUE(val) TYPE string
RETURNING
VALUE(result) TYPE REF TO data.
ENDCLASS.
CLASS lcl_help IMPLEMENTATION.
METHOD itab_get_by_xlsx.
SPLIT val AT `;` INTO DATA(lv_dummy) DATA(lv_data).
SPLIT lv_data AT `,` INTO lv_dummy lv_data.
DATA(lv_xdata) = cl_web_http_utility=>decode_x_base64( lv_data ).
DATA(lo_excel) = NEW cl_fdt_xl_spreadsheet(
document_name = `test`
xdocument = lv_xdata ).
lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING worksheet_names = DATA(lt_worksheets) ).
result = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).
ENDMETHOD.
ENDCLASS.Download
Convert an internal table to an XLSX file and download it to the frontend:
abap
METHOD z2ui5_if_app~main.
IF client->check_on_navigated( ).
DATA(view) = z2ui5_cl_ui5_view_builder=>factory(
)->ele( n = `View` ns = `mvc`
)->a( n = `xmlns` v = `sap.m`
)->a( n = `xmlns:mvc` v = `sap.ui.core.mvc`
)->ele( `Page`
)->tag( `Button`
)->a( n = `text` v = `Download`
)->a( n = `press` v = client->_event( `DOWNLOAD` ) ).
client->view_display( view->stringify( ) ).
ELSEIF client->check_on_event( `DOWNLOAD` ).
TYPES:
BEGIN OF ty_row,
count TYPE i,
value TYPE string,
descr TYPE string,
END OF ty_row.
TYPES ty_tab TYPE STANDARD TABLE OF ty_row WITH EMPTY KEY.
DATA(lt_tab) = VALUE ty_tab(
( count = `1` value = `red` descr = `this is a description` )
( count = `2` value = `red` descr = `this is a description` )
( count = `3` value = `red` descr = `this is a description` ) ).
DATA(lv_file) = lcl_help=>xlsx_get_by_itab( lt_tab ).
client->follow_up_action(
val = client->cs_event-download_b64_file
t_arg = VALUE #( ( lv_file ) ( `test.xlsx` ) ) ).
ENDIF.
ENDMETHOD.abap
"The helper class converts an internal table to a downloadable XLSX file.
"It uses cl_salv_table to derive a field catalog from the table structure,
"then cl_salv_bs_lex exports the data as XLSX into an xstring.
"Finally the xstring is Base64-encoded and wrapped in a data URI
"so the browser can trigger the file download.
CLASS lcl_help DEFINITION.
PUBLIC SECTION.
CLASS-METHODS xlsx_get_by_itab
IMPORTING
VALUE(val) TYPE STANDARD TABLE
RETURNING
VALUE(result) TYPE string.
ENDCLASS.
CLASS lcl_help IMPLEMENTATION.
METHOD xlsx_get_by_itab.
DATA(lt_data) = REF #( val ).
FIELD-SYMBOLS: <tab> TYPE STANDARD TABLE.
ASSIGN lt_data->* TO <tab>.
TRY.
cl_salv_table=>factory(
EXPORTING
list_display = abap_false
IMPORTING
r_salv_table = DATA(lo_salv)
CHANGING
t_table = <tab> ).
DATA(lt_fcat) = cl_salv_controller_metadata=>get_lvc_fieldcatalog(
r_columns = lo_salv->get_columns( )
r_aggregations = lo_salv->get_aggregations( ) ).
CATCH cx_salv_msg.
RETURN.
ENDTRY.
cl_salv_bs_lex=>export_from_result_data_table(
EXPORTING
is_format = if_salv_bs_lex_format=>mc_format_xlsx
ir_result_data_table = cl_salv_ex_util=>factory_result_data_table(
r_data = lt_data
t_fieldcatalog = lt_fcat )
IMPORTING
er_result_file = DATA(lv_xstring) ).
result = cl_web_http_utility=>encode_x_base64( lv_xstring ).
result = `data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,` && result.
ENDMETHOD.
ENDCLASS.Into a business object
To create the uploaded rows in a RAP business object - with a preview, one MODIFY ENTITIES for all rows and every refusal shown on its row - see the guide Excel Import into RAP.
ABAP Cloud
The code snippets above aren't ABAP Cloud compatible. To make them cloud-compatible, replace the code in the lcl_help class with functions from the XCO_CP_XLSX APIs.
abap2xlsx
Instead of the XLSX API above (which can change between releases), consider the open-source project abap2xlsx. It offers reusable APIs for common XLSX operations and runs entirely within the ABAP stack. The example below uses abap2xlsx in the lcl_help class:
abap
METHOD z2ui5_if_app~main.
IF client->check_on_navigated( ).
DATA(view) = z2ui5_cl_ui5_view_builder=>factory(
)->ele( n = `View` ns = `mvc`
)->a( n = `xmlns` v = `sap.m`
)->a( n = `xmlns:mvc` v = `sap.ui.core.mvc`
)->ele( `Page`
)->tag( `Button`
)->a( n = `text` v = `Download`
)->a( n = `press` v = client->_event( `DOWNLOAD` ) ).
client->view_display( view->stringify( ) ).
ELSEIF client->check_on_event( `DOWNLOAD` ).
TYPES:
BEGIN OF ty_row,
count TYPE i,
value TYPE string,
descr TYPE string,
END OF ty_row.
TYPES ty_tab TYPE STANDARD TABLE OF ty_row WITH EMPTY KEY.
DATA(lt_tab) = VALUE ty_tab(
( count = `1` value = `red` descr = `this is a description` )
( count = `2` value = `red` descr = `this is a description` )
( count = `3` value = `red` descr = `this is a description` ) ).
TRY.
DATA(lv_file) = lcl_help=>xlsx_get_by_itab( lt_tab ).
client->follow_up_action(
val = client->cs_event-download_b64_file
t_arg = VALUE #( ( lv_file ) ( `test.xlsx` ) ) ).
CATCH zcx_excel INTO DATA(x).
client->message_box_display( text = x->get_text( )
type = `error` ).
ENDTRY.
ENDIF.
ENDMETHOD.abap
CLASS lcl_help DEFINITION FINAL CREATE PUBLIC.
PUBLIC SECTION.
CLASS-METHODS xlsx_get_by_itab
IMPORTING
val TYPE any
RETURNING
VALUE(result) TYPE string
RAISING
zcx_excel.
ENDCLASS.
CLASS lcl_help IMPLEMENTATION.
METHOD xlsx_get_by_itab.
DATA: lo_excel TYPE REF TO zcl_excel,
lo_writer TYPE REF TO zif_excel_writer,
lo_worksheet TYPE REF TO zcl_excel_worksheet.
DATA: lt_field_catalog TYPE zexcel_t_fieldcatalog,
ls_table_settings TYPE zexcel_s_table_settings.
" Creates active sheet
CREATE OBJECT lo_excel.
" Get active sheet
lo_worksheet = lo_excel->get_active_worksheet( ).
lo_worksheet->set_title( `Internal table` ).
lt_field_catalog = zcl_excel_common=>get_fieldcatalog( ip_table = val ).
ls_table_settings-table_style = zcl_excel_table=>builtinstyle_medium5.
lo_worksheet->bind_table( ip_table = val
is_table_settings = ls_table_settings
it_field_catalog = lt_field_catalog ).
lo_worksheet->freeze_panes( ip_num_rows = 1 ).
CREATE OBJECT lo_writer TYPE zcl_excel_writer_2007.
DATA(lv_file) = lo_writer->write_file( lo_excel ).
result = cl_web_http_utility=>encode_x_base64( lv_file ).
result = `data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,` && result.
ENDMETHOD.
ENDCLASS.Export in the Browser
SAPUI5 ships sap.ui.export.Spreadsheet, which writes a table's rows to an .xlsx in the browser without a roundtrip. The ExportSpreadsheet control of the custom-controls add-on (builder class z2ui5_cl_cci_spreadsheet) wraps it for an abap2UI5 view; it needs SAPUI5, because OpenUI5 does not carry sap.ui.export.