Skip to Content

In this blog I have shown the procedure to download Internal Table as Excel using oData and SAPUI5


To upload excel file, please refer my blog Excel File Upload in SAPUI5(Master data upload using SAPUI5)


Method 1


Frontend (UI part)

In Controller.js

downloadFile : function() {

var  oDataModel = this.getView().getModel();

var url = oDataModel.sServiceUrl

+ “/YourEntitySet/$value”;

var html = new sap.ui.core.HTML();

  1. html.setContent(“<iframe src=” + url

+ ” visibl=’true’></iframe>”);

// pageid is nothing but Id of Page Control

var pageid = oController.getView().byId(“pageid”);

  1. pageid.addContent(html);
  2. pageid.setVisible(true);

}

In Backend ABAP System

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


  DATA :         lt_zmara    TYPE TABLE OF zar_mara,
           ls_mara   
TYPE zar_mara,
           ls_lheader 
TYPE ihttpnvp,
           ls_stream  
TYPE ty_s_media_resource.

DATA :         conv TYPE REF TO cl_abap_conv_out_ce,
           lv_text
TYPE string,
           lv_temp
TYPE string,
           lv_output
TYPE xstring,
           lv_cr_dat
TYPE char10.

CONSTANTS: lc_hor_tab type c VALUE cl_abap_char_utilities=>horizontal_tab.

CASE iv_entity_name.
WHEN ‘Your Entity Set’.

SELECT * FROM zar_mara INTO CORRESPONDING FIELDS OF TABLE lt_zmara.

CONCATENATE ‘Material’ ‘Created By’ ‘Created On’ ‘Complete Material’ ‘Maintenance status’ ‘Material Type’ ‘Material Group’ ‘Base Unit’ cl_abap_char_utilities=>cr_lf  INTO lv_text SEPARATED BY lc_hor_tab.

LOOP AT lt_zmara INTO ls_mara.
CALL FUNCTION ‘CONVERT_DATE_TO_EXTERNAL’
EXPORTING
date_internal           
= ls_maraersda
IMPORTING
date_external           
= lv_cr_dat
EXCEPTIONS
date_internal_is_invalid
= 1
OTHERS                   = 2.
IF sysubrc <> 0.

ENDIF.

CLEAR lv_temp.


CONCATENATE ls_maramatnr ls_maraernam lv_cr_dat ls_maravpsta ls_marapstat ls_maramtart ls_maramatkl ls_marameins cl_abap_char_utilities=>cr_lf  INTO lv_temp SEPARATED BY lc_hor_tab.


CONCATENATE lv_text lv_temp INTO lv_text.

ENDLOOP.

conv = cl_abap_conv_out_ce=>create(
encoding = ‘UTF-8’
endian
= ‘L’ ).

conv->convert( EXPORTING data = lv_text
IMPORTING buffer = lv_output ).

ls_streamvalue = lv_output.
ls_stream
mime_type = ‘application/vnd.ms-excel’.“MIMETYPE.

ls_lheadername = ‘Content-Disposition’.
ls_lheader
value = |inline; filename=“ZMARA.XLS”|.
set_header
( is_header = ls_lheader ).

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



Method 2


In this way you just need to implement GET_ENTITYSET method of your entity. And with the help of the following  libraries download of excel is possible in fronend

jQuery.sap.require(“sap.ui.core.util.Export”);

jQuery.sap.require(“sap.ui.core.util.ExportTypeCSV”);

downloadFileUI : function(){                                              

       var oDataModel = oController.getView().getModel(); 

    var url = oDataModel.sServiceUrl + “/zarmara_set”;

    var oDateFormat = ap.ui.core.format.DateFormat.getInstance({pattern: “dd/MM/yyyy”});

                                        

     oDataModel.read(“/zarmara_set”nullnullnullfunction(odata, req) {

              var oModel = new sap.ui.model.json.JSONModel();

              var dataObj = {

                  “tableData” : odata                                                                     

               };

           

          oModel.setData(dataObj);

       var oExport = new sap.ui.core.util.Export({

                                 exportType : new sap.ui.core.util.ExportTypeCSV({separatorChar : “,”}),

                                 models : oModel,

                                 rows : {                                                                                                                             path : “/tableData/results”

                                  },

                                columns :[{                                                                                                                               name : “Material”,                                                                                                             template:{                                                                                                                          content : {                                                                                                                        path : “Matnr”

                                                       }

                                                  }

                                           },

                                           {                                                                                                                               name : “Created On”,                                                                                                           template : {                                                                                                                        content : {

                                                     path : “Ersda”,

                                                     formatter : function(dValue){

return oDateFormat.format(new Date(dValue));

}

                    }

               }

          },

          {

             name : “Created By”,

             template : {

                   content : {

                         path : “Ernam”

                              }

                         }

          },

                                            {                                                                                                                               name : “Complete Material”,                                                                                             template : {

                                                    content : {

                                                        path : “Vpsta”

                                                   }

                                                  }

                                           },                                                                                                                             {

                                             name : “Maintenance status”,

                                             template : {                                                                                                                        content : {  

                                                      path : “Pstat”                                                            }                                                                                                                           }                                                                                                                },                                                                                                                             {                                                                                                                              name : “Material Group”,                                                                                                      template : {   

                                                       content : {                                                                                                             path : “Mtart” 

                                                          }                                                                                                                           }                                                                                                                },                                                                                                                            {                                                                                                                               name : “Material Type”,                                                                                                        template : {                                                                                                                      content : {                                                                                                                       path : “Matkl”                                                                                                                 }                                                                                                                            }                                                                                                                      },                                                                                                                            {                                                                                                                               name : “UOM”,                                                                                                                  template : {                                                                                                                      content : {                                                                                                                          path : “Meins”                                                                                    }                                                                                                                        }                                                                                                                       }]

                                });

       oExport.saveFile().always(function() {                                                                                                  this.destroy();                                                                 

           });

       });

}

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Anooj R Krishnan

    Hi Arun,

    In method 2 you are using a argument called “dValue” in the formatter attached there. May I know what does that argument signifies ?. It is returning null in my case.

     

     

    Thanks,

    Anooj

    (0) 

Leave a Reply