Internal Tabel to Excel SAPUI5

[arun_krishna567]

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_mara-ersda
IMPORTING
date_external            = lv_cr_dat
EXCEPTIONS
date_internal_is_invalid = 1
OTHERS                   = 2.
IF sy-subrc <> 0.

ENDIF.

CLEAR lv_temp.

CONCATENATE ls_mara-matnr ls_mara-ernam lv_cr_dat ls_mara-vpsta ls_mara-pstat ls_mara-mtart ls_mara-matkl ls_mara-meins 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_stream-value = lv_output.
ls_stream-mime_type = 'application/vnd.ms-excel'."MIMETYPE.

ls_lheader-name = '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();                                                                 

           });

       });

}