Skip to Content
Author's profile photo Arun Krishna

Excel File Upload in SAPUI5(Master data upload using SAPUI5)

Below is the approach that I tried and successfully able to upload File to back-end ECC server and upload data to database.


Frontend (UI part)


In XML view

<file:FileUploader id=“fileupload” name=“File Uploader”

              useMultipart=“false” sendXHR=“true” />

<Button id=“btnUpload” press=“uploadFile” text=“Upload”></Button>


In Controller.js

uploadFile : function(){

var file = jQuery.sap.domById(“__xmlview2–fileupload-fu”).files[0];

                try {

                  if (file) {

                    this._bUploading = true;

                    var that = this;

/****************To Fetch CSRF Token*******************/

                      var a = “/Yourservice URL or Metadata URL “;

                      var f = {

                        headers : {

                          “X-Requested-With” : “XMLHttpRequest”,

                          “Content-Type” : “application/atom+xml”,

                          DataServiceVersion : “2.0”,

                          “X-CSRF-Token” : “Fetch”

                        },

                        requestUri : a,

                        method : “GET”

                        };

                      var oHeaders;

                      var sUrl=oDataModel.sServiceUrl+“/Your Entity Set “;

                      var oModel = new sap.ui.model.odata.ODataModel(sUrl, true);                     

                      sap.ui.getCore().setModel(oModel);

                        OData.request(f, function(data, oSuccess) {

                          oToken = oSuccess.headers[‘x-csrf-token’];

                               oHeaders = {

                                            “x-csrf-token” : oToken,

                                            “slug” : “QF”,

                                     };

/****************To Fetch CSRF Token*******************/

/*******************To Upload File************************/

            

                   var oURL = oDataModel.sServiceUrl+“/Entity Set”;

                  jQuery.ajax({

                    type: ‘POST’,

                    url: oURL,

                    headers: oHeaders,

                    cache: false,

                    contentType: file.type,

                    processData: false,

                    data: file,

                    success: function(data){                      

                       var rec=data.getElementsByTagName(“entry”)[0].children[5].getAttribute(“src”);

                       sap.m.MessageToast.show(“File Uploaded Successfully”+rec);

                    },

                    error:function(data){

                      sap.m.MessageToast.show(“File Uploaded Successfully”);

                    }

                  });

                  });

                }

                } catch(oException) {

              jQuery.sap.log.error(“File upload failed:\n” + oException.message);

                }

}

/*******************To Upload File************************/

Note

For this blog the uploaded file excel file should be saved as .csv comma delimited format. If you upload (.XLSX, .XLS) format this code will not work for you.

In Backend ABAP System

Entity

Entity.PNG

Your entity should have this fields as mentioned above.


Implement Create_Stream Method


You need to implement /IWBEP/IF_MGW_APPL_SRV_RUNTIME~CREATE_STREAM method of DPC_EXT class


DATA lv_output TYPE string,
lv_out_tab
TYPE TABLE OF string,
ls_output
TYPE string.


DATA: gv_spl TYPE c VALUE cl_abap_char_utilities=>cr_lf.


DATA: lv_converter TYPE REF TO cl_abap_conv_in_ce,
lv_xstring  
TYPE xstring.



CASE iv_entity_name.
WHEN ‘YourEntity’.

lv_xstring = is_media_resourcevalue.

lv_converter = cl_abap_conv_in_ce=>create( input       = lv_xstring
encoding    = ‘UTF-8’
replacement
= ‘?’
ignore_cerr
= abap_true ).

TRY.
CALL METHOD lv_converter->read( IMPORTING data = lv_output ).
CATCH cx_sy_conversion_codepage.
*– Should ignore errors in code conversions
CATCH cx_sy_codepage_converter_init.
*– Should ignore errors in code conversions
CATCH cx_parameter_invalid_type.
CATCH cx_parameter_invalid_range.
ENDTRY.

REPLACE gv_spl IN lv_output WITH .
      
SPLIT lv_output AT gv_spl INTO TABLE lv_out_tab.


LOOP AT lv_out_tab INTO ls_output.


REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>cr_lf(1) IN ls_output WITH .
SPLIT ls_output AT ‘,’ INTO lv_marmatnr ls_char lv_marernam ls_char1 lv_maraenam lv_marvpsta lv_marpstat lv_marmtart lv_marmatkl lv_marmeins.

lv_marmatnr(1) = .
CONDENSE lv_marmatnr.


IF ls_char IS NOT INITIAL.
CALL FUNCTION ‘CONVERT_DATE_TO_INTERNAL’
EXPORTING
date_external                 
= ls_char
*             ACCEPT_INITIAL_DATE            =
IMPORTING
date_internal                 
= lv_marersda
*           EXCEPTIONS
*             DATE_EXTERNAL_IS_INVALID       = 1
*             OTHERS                         = 2
.
IF sysubrc <> 0.
* Implement suitable error handling here
ENDIF.
ENDIF.

IF ls_char1 IS NOT INITIAL.
CALL FUNCTION ‘CONVERT_DATE_TO_INTERNAL’
EXPORTING
date_external                 
= ls_char1
*             ACCEPT_INITIAL_DATE            =
IMPORTING
date_internal                 
= lv_marlaeda
*           EXCEPTIONS
*             DATE_EXTERNAL_IS_INVALID       = 1
*             OTHERS                         = 2
.
IF sysubrc <> 0.
* Implement suitable error handling here
ENDIF.
ENDIF.

IF lv_marmatnr IS NOT INITIAL.
APPEND lv_mar TO it_mara.
ENDIF.
CLEAR lv_mar.

ENDLOOP.

MODIFY zar_mara FROM TABLE it_mara.

ENDCASE.

Assigned Tags

      15 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Good blog !!

      Author's profile photo Laxman Chittepu
      Laxman Chittepu

      Hi Arun,

      Thanks a lot for the step by step screenshot.

      I tried the same procedure mentioned above but for me its not working.

      Can you let me know where I'm doing wrong.

      I have converted the excel file to csv and then trying to upload it. Even the break point in the ABAP system is not triggered in the Create_Stream Method

      Attached the screen of the Console log.

      ConsoleLog.png

      Author's profile photo Arun Krishna
      Arun Krishna
      Blog Post Author

      Hi Laxman,

      Mark your Entity as media type in SEGW as shown below,

      Untitled.png

      Regards,

      Arun Krishna

      Author's profile photo Laxman Chittepu
      Laxman Chittepu

      Hi Arun,

      Thanks for the prompt reply. After changing it as the media type there is change in the error message: "Bad Request"

      sap-ui-core.js:71 POST http://server/sap/opu/odata/sap/YNGWBPS_SRV/uploadSet 400 (Bad Request)


      Can you please let me know how do I need to test it in the GatewayClient

      Author's profile photo Arun Krishna
      Arun Krishna
      Blog Post Author

      Hi Laxman,

      Can you explain steps which you have followed. And give me your frontend code.

      Author's profile photo Laxman Chittepu
      Laxman Chittepu

      Hi Arun,

      I have followed the above steps as you have mentioned.

      1.Copied the code to XML view.

      2.Copied the below code to the view.controller.js

      uploadFile: function(e)

        {

        var file = jQuery.sap.domById("idzmsg--fileupload-fu").files[0];

        try {

                      if (file) {

                        this._bUploading = true;

                        var that = this;

      /****************To Fetch CSRF Token*******************/

                          //var a = "/Yourservice URL or Metadata URL ";

                       var a = "/sap/opu/odata/sap/YNGWBPS_SRV";

                          var f = {

                            headers : {

                              "X-Requested-With" : "XMLHttpRequest",

                              "Content-Type" : "application/atom+xml",

                              DataServiceVersion : "2.0",

                              "X-CSRF-Token" : "Fetch",

                            },

                            requestUri : a,

                            method : "GET"

                            };

                          var oHeaders;

                          var sUrl=a+"/FileSet";

                          var oModel = new sap.ui.model.odata.ODataModel(sUrl, true);                    

                          sap.ui.getCore().setModel(oModel);

                            OData.request(f, function(data, oSuccess) {

                              var oToken = oSuccess.headers['x-csrf-token'];

                                   oHeaders = {

                                                "x-csrf-token" : oToken,

                                                "slug" : "QF",

                                         };

      /****************To Fetch CSRF Token*******************/

      /*******************To Upload File************************/

                       //var filetype = "text/csv";           

                       var filetype = file.type;

                       var oURL = a+"/FileSet";

                      jQuery.ajax({

                        type: 'POST',

                        url: oURL,

                        headers: oHeaders,

                        cache: false,

                        contentType: filetype,

                        processData: false,

                        data: file,

                        success: function(data){                     

                           var rec=data.getElementsByTagName("entry")[0].children[5].getAttribute("src");

                           sap.m.MessageToast.show("File Uploaded Successfully"+rec);

                        },

                        error:function(data){

                          sap.m.MessageToast.show("File Uploaded Successfully  111");

                        }

                      });

                      });

                    }

                    } catch(oException) {

                  jQuery.sap.log.error("File upload failed:\n" + oException.message);

                    }

        }

      /*******************To Upload File************************/

      });

      3.Created Entity in backend

      4.Implemented the CREATE_STREAM method in the DPC_EXT.

      Author's profile photo Arun Krishna
      Arun Krishna
      Blog Post Author

      Hi Laxman,

      Redefine your MPC_EXT, Define method as follow,

      super->define( ).

           DATA:

             lo_entity   TYPE REF TO /iwbep/if_mgw_odata_entity_typ,

             lo_property TYPE REF TO /iwbep/if_mgw_odata_property.

           lo_entity = model->get_entity_type( iv_entity_name = 'Entity Name' ).

           IF lo_entity IS BOUND.

             lo_property = lo_entity->get_property( iv_property_name =

      'Mimetype' ).

             lo_property->set_as_content_type( ).


      Regards,

      Arun Krishna

      Author's profile photo Laxman Chittepu
      Laxman Chittepu

      Thanks Arun for the reply.

      I'm able to download the file now. But one issue is file name is getting downloaded as $value.xlsx. Can you please guide me how to pass the save as file name.

      Below is the url for downloading the file.

      http://server:port/sap/opu/odata/sap/service_SRV/FileSet(Pernr='000000XX',FileName='Test.xlsx')/$value


      Regards,

      Laxman

      Author's profile photo Arun Krishna
      Arun Krishna
      Blog Post Author

      Hi Laxman,

      Data : ls_lheader type  ihttpnvp.

             ls_lheader-name = 'Content-Disposition'.

             ls_lheader-value = |inline; filename="Filename.extension"|.

             set_header( is_header = ls_lheader ).

      Regards,

      Arun Krishna

      Author's profile photo Former Member
      Former Member

      Hi Arun Krishna

      i have created entity on SEGW i'm not clear on what attributes it should have

      Author's profile photo Former Member
      Former Member

      and im getting this below errorCapture.PNG

      Author's profile photo Ravi Singhal
      Ravi Singhal

      Hi Hashan,

      For Get call you did not need to pass the entity set you have created just pass this URL-"/sap/opu/odata/sap/ATS_FINAL_SRV/"

      Author's profile photo Mauricio Pinheiro Predolim
      Mauricio Pinheiro Predolim

      Hi Arun. Great Job!

      One question pls:

      You redefined only CREATE_STREAM (DPC_EXT) and DEFINE(MPC_EXT) methods.

      I tested CREATE_STREAM and works great, i've got the uploaded CSV file and inserted data into my custom table.

      How to you treat in UI5 the return of insert data table? For example: the modify returns sy-subrc <> 0 and you have to send a msg to user saying "Error on upload".

      Thanks!

       

       

       

      Author's profile photo Debabrata Behera
      Debabrata Behera

      Hello Arun,

      Great Blog , I am getting these  issue could you please help me .

      [ODataMetadata] initial loading of metadata failed -

      POST http://XX.XX.com:8080/sap/opu/odata/sap/XX_SRV/FileLoadSet/ 403(In Network :CSRF token validation failed).

      Please help me so that I can able to upload the file .

       

      Regards,

      Debabrata.

      Author's profile photo Stephan Heinberg
      Stephan Heinberg

      Dear Vishal,

      can you please rename the title of you blog: to: “SAPUI5: CSV file upload“. It sounds much more professional.

      I though you we a magician and would be able to upload native Excel files (XLSX).

      Thanks,

      Stephan