Internal Tabel to Excel SAPUI5
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();
- html.setContent(“<iframe src=” + url
+ ” visibl=’true’></iframe>”);
// pageid is nothing but Id of Page Control
var pageid = oController.getView().byId(“pageid”);
- pageid.addContent(html);
- 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();
});
});
}
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