Excel File Upload Download in SAPUI5

Introduction

On many occasions, users need to upload an excel file from the local system and extract the content from the excel file to send to backend SAP. There are multiple ways to achieve the extraction of data from excel file and send the content to SAP system. In this article I am going to explain how one can download the content of an SAP table as a form off an excel file. We will also discuss in detail about how one can upload an excel file filled with table content and send the extracted data to back-end SAP. Finally, we will see how to convert the encoded data in back-end SAP. In this article we are going to use SAP gateway stream methods to extract SAP data and upload presentation server data to SAP.

Development Environment

• SAP UI5 version 1.71.33
• SAP Gateway version 740, SP0026

Gateway Project Building

Let us first create SAP gateway project and create suitable entities to handle the stream methods. In this object we are creating two entities –

1. TableStructure – properties are – (a). TableName, (b). MimeType
2. TableList – properties are – (a). NameOfTable, (b). TableDesc

Gateway_Project_Entities

We have generated the corresponding entity sets. For this project we need table structure set as a media entity. Hence mark the entity as media enabled on the entity set property.

Marking Entity as Media Type

Also do not forget to update the entity structure with correct operations you need, example given – creatable, updateable etc.

Mark Operations for EntitySet

The first entity – TableStructure has two properties – TableName, MIME Type. The table name property will hold the name of the table which we want to download or upload. The MIME Type property will hold the type of the file e.g. – JPEG, XLS, CSV, XLSX, TEXT. You can find the possible MIME type from the standard table – SDOKFEXTT. In this blog we are going to develop a case on the file type XLS. The corresponding MIME type is – application/vnd.ms-excel.

Service Method Implementations

After generating the project go to the MPC extension class and redefine the DEFINE method like shown below –

  DATA: lo_property    TYPE REF TO /iwbep/if_mgw_odata_property,
        lo_entity_type TYPE REF TO /iwbep/if_mgw_odata_entity_typ.

  super->define( ).

  lo_entity_type = model->get_entity_type( iv_entity_name = 'TableStructure' ).

  IF lo_entity_type IS BOUND.
    lo_property = lo_entity_type->get_property('MimeType').
    lo_property->set_as_content_type( ).
  ENDIF.

Now, go to the DPC extension class and redefine method GET_STREAM and CREATE_STREAM to handle download and upload functions, respectively. The relevant implementation of GET_STREAM as shown below.

  DATA: ls_stream      TYPE ty_s_media_resource,
        lv_data_string TYPE string,
        lv_xstring     TYPE xstring,
        ls_header      TYPE ihttpnvp,
        lt_fields      TYPE STANDARD TABLE OF dfies.

* Get Tablename passed from frontend
  DATA(ls_key_tab) = VALUE #( it_key_tab[ name = 'TableName' ] OPTIONAL ).

* Get Column Lists from Table name
  IF ls_key_tab-value IS NOT INITIAL.

    CALL FUNCTION 'DDIF_FIELDINFO_GET'
      EXPORTING
        tabname        = CONV ddobjname( ls_key_tab-value )
      TABLES
        dfies_tab      = lt_fields
      EXCEPTIONS
        not_found      = 1
        internal_error = 2
        OTHERS         = 3.

    IF sy-subrc <> 0.

      "Handle Suitable Error Here

    ELSE.


* Build String with table coulmn names
      LOOP AT lt_fields INTO DATA(lt_fields_line).
        IF sy-tabix = 1.
          lv_data_string = |{ lt_fields_line-fieldname }|.
        ELSE.
          lv_data_string = |{ lv_data_string }\t{ lt_fields_line-fieldname }|.
        ENDIF.
        CLEAR: lt_fields_line.
      ENDLOOP.

* Convert String to xstring
      CALL FUNCTION 'HR_KR_STRING_TO_XSTRING'
        EXPORTING
          unicode_string   = lv_data_string
        IMPORTING
          xstring_stream   = lv_xstring
        EXCEPTIONS
          invalid_codepage = 1
          invalid_string   = 2
          OTHERS           = 3.

* Pass back stream values
      ls_stream-value     = lv_xstring.
      ls_stream-mime_type = 'application/msexcel'.


* Set Header for Response
      DATA(lv_filename) = |{ ls_key_tab-value }-{ sy-datum }-{ sy-uzeit }.xls|.
      lv_filename = escape( val = lv_filename format = cl_abap_format=>e_url ).

      ls_header-name = 'Content-Disposition'.
      ls_header-value = |outline; filename="{ lv_filename }"|.
      me->set_header( ls_header ).

      copy_data_to_ref(
        EXPORTING is_data = ls_stream
        CHANGING cr_data = er_stream ).


    ENDIF.

  ENDIF.

Here, we are receiving a table name and extracting the table structure from SAP. We are creating an excel file of type (.XLS) and trying to send that across to SAP UI5. Only the template of the table will be downloaded and there will be no table content.

Note – We have used MIME type from the media type standard table. The file will be downloaded with name – <tableName>-<Date>-<Time>.XLS.

Implement Download Method in SAP UI5

Let’s implement the corresponding SAP UI5 code for this. I have created an Object Page application with 3 simple sections.

1. Custom Table Selection
2. Browse Data File
3. Execute Upload

Final Designed View

Under custom table selection, created an input field for passing the table name for which we want the template to be downloaded. To trigger the download function a button “Download Template” has been introduced.

Let’s see the XML view code for input field and download button.

<ObjectPageSubSection id="customTableSel">
						<blocks>
							<layout:VerticalLayout>
								<m:Input id="tableInput" width="100%" type="Text" placeholder="Enter Table Name ..." showSuggestion="true" suggestionItems="{/TableListSet}"
									showValueHelp="true" valueHelpRequest="onValueHelpRequest" suggest="onSuggest" liveChange="onLiveChange">
									<m:suggestionItems>
										<core:Item text="{NameOfTable}"/>
									</m:suggestionItems>
								</m:Input>
								<m:Button text="Download Template" press="onDownTempPressed" enabled="true"></m:Button>
							</layout:VerticalLayout>
						</blocks>
					</ObjectPageSubSection>

Now, we implemented the button press function on the corresponding controller. On pressing the Download Template button – “onDownTempPressed” method will be triggered. If the input field has values in it then we are triggering the following URL to download the template.

/sap/opu/odata/SAP/XXX_GENTAB_MAINT_SRV/TableStructureSet(‘<table name>’)/$value

Construct the URI as shown below and run it through window.open function.

		onDownTempPressed: function (oEvent) {
			var inputTableName = this.getView().byId("tableInput").getValue();
			var mainUri = this.getOwnerComponent().getManifestEntry("/sap.app/dataSources/mainService").uri;

			// Check whether table name field is not initial
			if (inputTableName === "") {
				this.getView().byId("tableInput").setValueState("Error");
				MessageToast.show("Please Provide a Table Name");
			} else {
				//Reset the value state for input field in case it was error
				this.getView().byId("tableInput").setValueState("None");
				var wStream = window.open(mainUri + "TableStructureSet('" + inputTableName + "')/$value");
				if (wStream === null) {
					MessageToast.show("Error generating template");
				} else {
					MessageToast.show("Template Downloaded Successfully");
				}

			}
		},

File Uploading in SAPUI5

Let us understand how to upload XLS file and extract the content of the file in SAP UI5 and make a call to SAP with the data, to have it posted on SAP table. To do that let us design a file uploader control on SAP UI5. The file uploader control comes with a browse function which allows us to select a file from the local computer. While designing the control in SAP UI5 we can specify which file types are to be allowed for upload. in this article we will allow only XLS file type. there are several events associated with file uploader control. One can make use of standard events like typeMismatch, onChange etc. for better user experience. For the brevity of this blog, we have only used typeMismatch, onChange events on file uploader.

My view for the file uploader looks like below –

<ObjectPageSection id="sectionBrowseDataFile" title="Upload Data" importance="Medium" titleUppercase="true">
				<subSections>
					<ObjectPageSubSection id="browseDataFile" title="Browse Data File">
						<blocks>
							<u:FileUploader id="fileUploader" name="myFileUpload" tooltip="" fileType="xls" uploadOnChange="false" typeMissmatch="handleTypeMissmatch"
								change="onChange"/>
						</blocks>
					</ObjectPageSubSection>
				</subSections>
			</ObjectPageSection>

After selection is completed, user needs to select what operation he wants to perform by selecting the radio button designed at the end of the application. Once the operation is selected, when the user presses “execute upload” button, file reading should happen, and the extracted data should go to SAP through a stream.

My view for file selection and executing the operation is designed as shown below –

<ObjectPageSection id="executeUpload" title="Execute Upload" importance="Medium" titleUppercase="true">
				<subSections>
					<ObjectPageSubSection id="executeUploadSubSection" title="">
						<blocks>
							<layout:VerticalLayout>
								<m:RadioButtonGroup id="radioButtonGroup">
									<m:buttons>
										<m:RadioButton id="radioButton1" text="Insert"/>
										<m:RadioButton id="radioButton2" text="Modify"/>
										<m:RadioButton id="radioButton3" text="Delete"/>
									</m:buttons>
								</m:RadioButtonGroup>
								<m:Button text="Execute Upload" press="onExecuteUpload" type="Emphasized" enabled="true"></m:Button>
							</layout:VerticalLayout>
						</blocks>
					</ObjectPageSubSection>
				</subSections>
			</ObjectPageSection>

I am using file reader function and its corresponding events to read the file. In this article we are reading the XLS file as a binary string and then parsing the string with specified REGEX operations and converting into a string array. Remember, send your content from SAP UI5 in such a way so that it becomes easy in back-end SAP to parse and convert the data like a table for further operations. You can also use readAsBinaryURL for reading different media types. In such cases the content becomes base64 encoded and you must decode the base64 encoding at your back-end SAP in order to decipher the data and make use of it.

The corresponding JS function attached to “Execute Upload” button is shown below –

onExecuteUpload: function (oEvent) {
			var oFileUploader = this.byId("fileUploader");
			var xlsDomRef = oFileUploader.getFocusDomRef();
			var xlsFile = xlsDomRef.files[0];
			var that = this;

			this.fileName = xlsFile.name;
			this.fileType = xlsFile.type;

			//Get selected Radio Button
			for (var j = 0; j < this.getView().byId("radioButtonGroup").getButtons().length; j++) {
				if (this.getView().byId("radioButtonGroup").getButtons()[j].getSelected()) {
					this.operation = this.getView().byId("radioButtonGroup").getButtons()[j].getText();
				}
			}

			var oReader = new FileReader();
			oReader.onload = function (oReadStream) {
				//Get the number of columns present in the file uploaded & convert the regex unformatted stream
				//to array. This will be parsed at the backend SAP
				var noOfcolumn = oReadStream.currentTarget.result.match(/[^\r\n]+/g)[0].split("\t").length;
				var binContent = oReadStream.currentTarget.result.match(/[^\r\n\t]+/g);

				//Provide the binary content as payload. This will be received as an XSTRING in
				//SAP under the CREATE_STREAM method of media resource structure
				var payload = {
					"Content": binContent
				};

				//Provide additional details through header. Column No, Filename + File Type + TableName + Operation
				var header = {
					"slug": noOfcolumn + "," + that.fileName + "," + that.fileType + "," + that.operation
				};

				//Call a CREATE_STREAM activity
				that.getModel().create("/TableStructureSet", payload, {
					headers: header,
					success: function (oData, oResponse) {
						MessageToast.show("Data Uploaded Successfully!");
					},
					error: function (oError) {
						MessageToast.show("Data Uploaded Failed!");
					}
				});

			};

			// Read the file as a binary String. Do not read URI, you have to encode before sending
			oReader.readAsBinaryString(xlsFile);

		},

Handle Uploaded Filedata in Gateway

The string that we are sending from SAP UI5 will be received as in XString format in the back end. You need to write necessary code to convert the XString to binary content followed by a binary to string conversion. once you receive the content in string format you need to remove the unnecessary parenthesis, colons, braces from the string. With the parsed string you need to dynamically build the internal table before you insert the data in database table. Practice caution and make required validations both in SAP UI5 and gateway code before uploading data to database table. It is a good idea to implement a busy dialogue in SAP UI5 while the back-end operation is happening so that the user remains informed about an ongoing activity with the file data. Send successful message if the operation is completed successfully or send suitable error messages if there are any difficulties arise while processing or uploading the data.

The GW code for handling the file data is shown below – this has been implemented under CREATE_STREAM method.

* Data Declarations
  DATA: lv_xstring         TYPE xstring,
        lv_string          TYPE string,
        lv_length          TYPE i,
        lv_strcomp_counter TYPE i,
        li_bin_tab         TYPE STANDARD TABLE OF x255,
        lt_dref            TYPE REF TO data,
        ls_dref            TYPE REF TO data.

  FIELD-SYMBOLS: <lfs_dyntab> TYPE STANDARD TABLE,
                 <lfs_dyntab_line>    TYPE any.


* Get the column count and table name for upload
  SPLIT iv_slug AT ',' INTO TABLE DATA(li_slug_data).
  DATA(lv_colcount)  = VALUE #( li_slug_data[ 1 ] OPTIONAL ).
  DATA(lv_operation) = VALUE #( li_slug_data[ 4 ] OPTIONAL ).
  DATA(lv_fileName)  = CONV string( VALUE #( li_slug_data[ 2 ] OPTIONAL ) ).
  SPLIT lv_fileName AT '-' INTO TABLE DATA(li_fileName).
  DATA(lv_tableName) = VALUE tabname16( li_fileName[ 1 ] OPTIONAL ).

* Read the stream information
  lv_xstring = is_media_resource-value.


* XString to Binary Conversion
  CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
    EXPORTING
      buffer        = lv_xstring
    IMPORTING
      output_length = lv_length
    TABLES
      binary_tab    = li_bin_tab.

* Binary to String Conversion
  CALL FUNCTION 'SCMS_BINARY_TO_STRING'
    EXPORTING
      input_length = lv_length
    IMPORTING
      text_buffer  = lv_string
    TABLES
      binary_tab   = li_bin_tab
    EXCEPTIONS
      failed       = 1
      OTHERS       = 2.
  IF sy-subrc <> 0.
* Implement suitable error handling here
  ENDIF.

* Format String to Readable format to Parse in Table
  REPLACE ALL OCCURRENCES OF '{' IN lv_string WITH ''.
  REPLACE ALL OCCURRENCES OF '}' IN lv_string WITH ''.
  REPLACE ALL OCCURRENCES OF ':' IN lv_string WITH ''.
  REPLACE ALL OCCURRENCES OF '[' IN lv_string WITH ''.
  REPLACE ALL OCCURRENCES OF ']' IN lv_string WITH ''.
  REPLACE ALL OCCURRENCES OF '"' IN lv_string WITH ''.
  REPLACE ALL OCCURRENCES OF 'Content' IN lv_string WITH ''.

  SPLIT lv_string AT ',' INTO TABLE DATA(li_values).


  CREATE DATA lt_dref TYPE TABLE OF (lv_tableName).
  CREATE DATA ls_dref TYPE (lv_tableName).

* Assign field symbol with table type of DDIC
  ASSIGN lt_dref->* TO <lfs_dyntab>.
* Assign field symbol with Structure type of DDIC
  ASSIGN ls_dref->* TO <lfs_dyntab_line>.


* Initialize Counter
  lv_strcomp_counter = 1.

* Dynamically read the values and build internal table
  LOOP AT li_values ASSIGNING FIELD-SYMBOL(<lfs_values>).

    ASSIGN COMPONENT lv_strcomp_counter OF STRUCTURE <lfs_dyntab_line> TO FIELD-SYMBOL(<lfs_tgt_data>).
    <lfs_tgt_data> = <lfs_values>.

    lv_strcomp_counter = lv_strcomp_counter + 1.

*   LineBreak Counter for checking column count
    DATA(lv_linebrk_counter)  = sy-tabix MOD ( lv_colcount ).
    IF lv_linebrk_counter EQ 0.
      lv_strcomp_counter = 1.
      APPEND <lfs_dyntab_line> TO <lfs_dyntab>.
      CLEAR: <lfs_dyntab_line>.
    ENDIF.

  ENDLOOP.

* Delete the header row of the table
  DELETE <lfs_dyntab> INDEX 1.

* Create Entry to Actual Table
  MODIFY (lv_tableName) FROM TABLE <lfs_dyntab>.
  IF sy-subrc <> 0.

  ENDIF.

Summary

As explained above, you can send your frontend media file data to backend and upload and download information as XLS file from SAP. For productive use we have done many other validations in UI5 before sending the data to the backend, e.g. – identifying a blank excel file and stopping the upload, stopping the upload if there is any extraneous entry provided in the excel file apart from the designated columns etc. The backend code has been equipped with many other validations and error handling situations.

I hope this will give some direction to people trying to develop similar requirements. I also encourage readers to implement and let the community know your feedback.