How to generate professional Excel from SAPUI5 using ABAP2XLSX

Hello,

If you are developing with SAPUI5 and you want to generate Excel spreadsheets you have different options.

1. Use the class sap.ui.core.util.ExportTypeCSV and export as CSV, so this is not a fancy excel spreadsheet.
2. If you are on SAPUI5 version 1.50 or higher you have the class sap.ui.export.Spreadsheet, it’s ok if you only want a table and filters, right now is not supporting lot of features excel offers.
3. You can also use external libraries like sheetjs but to have all features available you need a license.
4. One great option is to use ABAP2XLSX to generate the excel in the backend and show it in the frontend.

If you want more info about ABAP2XLSX just check the following links ABAP2XLSX Wiki or   ABAP2XLSX Github. Thanks to @ivan.femia and contributors.

To show how this can be done just continue reading.

We can build a sapui5 application with a simple button that executes a function import, the function import implemented in the backend will build the excel and return as binary, back again in the navigator you can offer the file to download. Here a simplified workflow:

First let’s do the odata part using the SEGW Gateway Builder and create a Complex Type ‘excel’ with reference to structure VALSTRUCTX.

Include the VAL property as binary type, in backend the type is rawstring

Then create a function import getExcel, the return type has to be the complex type we created before:

Now let’s do the implementation for the function import getExcel, just enter to the method /IWBEP/IF_MGW_APPL_SRV_RUNTIME~EXECUTE_ACTION and build an excel using ABAP2XLSX ( here we are using the demo12 example ):

  METHOD /iwbep/if_mgw_appl_srv_runtime~execute_action.

    DATA: lo_excel     TYPE REF TO zcl_excel,
          lo_worksheet TYPE REF TO zcl_excel_worksheet,
          lo_column    TYPE REF TO zcl_excel_column,
          lo_row       TYPE REF TO zcl_excel_row,
          cl_writer    TYPE REF TO zif_excel_writer.

    CASE iv_action_name.

      WHEN 'getExcel'.
        DATA ls_excel TYPE valstructx.



        " Creates active sheet
        CREATE OBJECT lo_excel.

        " Get active sheet
        lo_worksheet = lo_excel->get_active_worksheet( ).

        lo_worksheet->set_title( 'Sheet1' ).
        lo_worksheet->set_cell( ip_column = 'B' ip_row = 2 ip_value = 'Hello world in AutoSize column' ).
        lo_worksheet->set_cell( ip_column = 'C' ip_row = 3 ip_value = 'Hello world in a column width size 50' ).
        lo_worksheet->set_cell( ip_column = 'D' ip_row = 4 ip_value = 'Hello world (hidden column)' ).
        lo_worksheet->set_cell( ip_column = 'F' ip_row = 2 ip_value = 'Outline column level 0' ).
        lo_worksheet->set_cell( ip_column = 'G' ip_row = 2 ip_value = 'Outline column level 1' ).
        lo_worksheet->set_cell( ip_column = 'H' ip_row = 2 ip_value = 'Outline column level 2' ).
        lo_worksheet->set_cell( ip_column = 'I' ip_row = 2 ip_value = 'Small' ).



        lo_worksheet->set_cell( ip_column = 'A' ip_row = 1 ip_value = 'Hello world (hidden row)' ).
        lo_worksheet->set_cell( ip_column = 'E' ip_row = 5 ip_value = 'Hello world in a row height size 20' ).
        lo_worksheet->set_cell( ip_column = 'B' ip_row = 9 ip_value = 'Simple outline rows 10-16 ( collapsed )' ).
        lo_worksheet->set_cell( ip_column = 'B' ip_row = 19 ip_value = '3 Outlines - Outlinelevel 1 is collapsed' ).
        lo_worksheet->set_cell( ip_column = 'C' ip_row = 19 ip_value = 'One of the two inner outlines is expanded, one collapsed' ).
        lo_worksheet->set_cell( ip_column = 'B' ip_row = 20 ip_value = 'Inner outline level - expanded' ).
        lo_worksheet->set_cell( ip_column = 'B' ip_row = 24 ip_value = 'Inner outline level - lines 25-28 are collapsed' ).

        lo_worksheet->zif_excel_sheet_properties~summarybelow = zif_excel_sheet_properties=>c_below_off. " By default is on
        lo_worksheet->zif_excel_sheet_properties~summaryright = zif_excel_sheet_properties=>c_right_off. " By default is on

        " Column Settings
        " Auto size
        lo_column = lo_worksheet->get_column( ip_column = 'B' ).
        lo_column->set_auto_size( ip_auto_size = abap_true ).
        lo_column = lo_worksheet->get_column( ip_column = 'I' ).
        lo_column->set_auto_size( ip_auto_size = abap_true ).
        " Manual Width
        lo_column = lo_worksheet->get_column( ip_column = 'C' ).
        lo_column->set_width( ip_width = 50 ).
        lo_column = lo_worksheet->get_column( ip_column = 'D' ).
        lo_column->set_visible( ip_visible = abap_false ).
        " Implementation in the Writer is not working yet ===== TODO =====
        lo_column = lo_worksheet->get_column( ip_column = 'F' ).
        lo_column->set_outline_level( ip_outline_level = 0 ).
        lo_column = lo_worksheet->get_column( ip_column = 'G' ).
        lo_column->set_outline_level( ip_outline_level = 1 ).
        lo_column = lo_worksheet->get_column( ip_column = 'H' ).
        lo_column->set_outline_level( ip_outline_level = 2 ).

        lo_row = lo_worksheet->get_row( ip_row = 1 ).
        lo_row->set_visible( ip_visible = abap_false ).
        lo_row = lo_worksheet->get_row( ip_row = 5 ).
        lo_row->set_row_height( ip_row_height = 20 ).

* Define an outline rows 10-16, collapsed on startup
        lo_worksheet->set_row_outline( iv_row_from = 10
                                       iv_row_to   = 16
                                       iv_collapsed = abap_true ).  " collapsed

* Define an inner outline rows 21-22, expanded when outer outline becomes extended
        lo_worksheet->set_row_outline( iv_row_from = 21
                                       iv_row_to   = 22
                                       iv_collapsed = abap_false ). " expanded

* Define an inner outline rows 25-28, collapsed on startup
        lo_worksheet->set_row_outline( iv_row_from = 25
                                       iv_row_to   = 28
                                       iv_collapsed = abap_true ).  " collapsed

* Define an outer outline rows 20-30, collapsed on startup
        lo_worksheet->set_row_outline( iv_row_from = 20
                                       iv_row_to   = 30
                                       iv_collapsed = abap_true ).  " collapsed

* Hint:  the order you create the outlines can be arbitrary
*        You can start with inner outlines or with outer outlines

*--------------------------------------------------------------------*
* Hide columns right of column M
*--------------------------------------------------------------------*
        lo_worksheet->zif_excel_sheet_properties~hide_columns_from = 'M'.


        cl_writer = NEW zcl_excel_writer_2007( ).

        ls_excel-val = cl_writer->write_file( lo_excel ).

        copy_data_to_ref(
          EXPORTING
            is_data = ls_excel
          CHANGING
            cr_data = er_data ).
    ENDCASE.

  ENDMETHOD.

Second we create a SAPUI5 application with just one view, in that view a button that will call a method in the controller called “getExcel”

<mvc:View xmlns="sap.m" xmlns:mvc="sap.ui.core.mvc" xmlns:semantic="sap.f.semantic" controllerName="ztest_excel.ztest_excel.controller.Worklist">
	<Panel id="__panel0">
	    <content>
	        <Button xmlns="sap.m" text="Export Excel" press="getExcel"/>
	    </content>
	</Panel>
</mvc:View>

In the controller we add the following methods:

		s2ab: function(s) {
			var buf = new ArrayBuffer(s.length);
			var view = new Uint8Array(buf);
			for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
			return buf;
		},
		
		getExcel: function(oEvent) {

			var successGetExcel = function(oData, oResponse) {

				if (oData.getExcel) {

					var a = window.document.createElement('a');

					var blob = new Blob([this.s2ab(atob(oData.getExcel.Val))], {
						type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
					});
					a.href = window.URL.createObjectURL(blob);
					a.download = 'Excel.xlsx';

					// Append anchor to body.
					document.body.appendChild(a);
					a.click();

					// Remove anchor from body
					document.body.removeChild(a);

				}

			};

			this.getModel().callFunction("/getExcel", {
				method: "GET",
				success: jQuery.proxy(successGetExcel, this)
			});

		},

The final result will be a button in the view, when you press it will automatically offer the excel:

You can generate graphics:

And also play with styles, just for fun:

So if you want to offer you users great excels from sapui5 I think with abap2xlsx you can offer a good solution.

Thanks for reading