Export a page to .xls format in SAP UI5
Hello Peeps,
I have come across a scenario where the client requested us to export the data in the page where we are tracking the usage of different systems available to a spread sheet. Initially when I had started working on the scenario I have the only idea of exporting data in the table/model to csv / xlsx format using the libraries:
new sap.ui.core.util.ExportTypeCSV(sId?, mSettings?); new sap.ui.export.Spreadsheet(mSettings); |
Please go through the following demo apps in demo kit for your reference for csv export and excel export:
https://sapui5.hana.ondemand.com/#/sample/sap.m.sample.TableExport/preview
https://sapui5.hana.ondemand.com/#/entity/sap.ui.export.Spreadsheet
Now coming to our requirement where we need to export the data in the page to a spread sheet.
Note: we won’t be able to export images, viz frame charts and icons using the export method I have used so far.
Here I have created a sample application with a page having a header consist of sample text and image, table with header and a sample line-chart.
View logic:
<Page class="ExportPage" title="{i18n>Page Export}">
<content>
<Toolbar>
<content>
<Text text="In this blog I am explaining the concept of exporting a page to xls format" class="SampleText"/>
<ToolbarSpacer/>
<Image class="Image" densityAware="false" id="ImageId" src="images/cubs.jpg" width="5rem">
</Image>
</content>
</Toolbar>
<Table class="tableId" items="{Items>/results}">
<headerToolbar>
<Toolbar>
<content>
<Label text="Date:"/>
<Text text="25.09.2018"/>
<ToolbarSpacer/>
<ToggleButton icon="sap-icon://excel-attachment" id="Export" press="onExportPress"/>
</content>
</Toolbar>
</headerToolbar>
<columns>
<Column>
<Label text="Name"/>
</Column>
<Column>
<Label text="Age"/>
</Column>
<Column>
<Label text="City"/>
</Column>
</columns>
<items>
<ColumnListItem>
<cells>
<Text text="{Items>Name}"/>
<Text text="{Items>Age}"/>
<Text text="{Items>City}"/>
</cells>
</ColumnListItem>
</items>
</Table>
<viz:VizFrame id="barId" width="100%"></viz:VizFrame>
</content>
</Page>
Controller logic:
onInit: function() {
//Model Data
var jSon = {
results: [{
Name: "Allen",
Age: 12,
City: "New Jersy"
}, {
Name: "Rocky",
Age: 13,
City: "Los Angeles"
}, {
Name: "Smith",
Age: 10,
City: "Los Angeles"
}, {
Name: "David",
Age: 15,
City: "Los Angeles"
}]
};
var oModel = new sap.ui.model.json.JSONModel();
oModel.setData(jSon);
this.getView().setModel(oModel, "Items");
//Line chart logic
var oVizFrame = this.getView().byId("barId");
var oDataset = new sap.viz.ui5.data.FlattenedDataset({
dimensions: [{
name: "Name",
value: "{Name}"
}],
measures: [{
name: "Age",
value: "{Age}"
}],
data: {
path: "/results"
}
});
oVizFrame.setDataset(oDataset);
oVizFrame.setModel(oModel);
oVizFrame.setVizType("line");
// 4.Set Viz properties
var properties = {
title: {
visible: true,
text: "",
width: "50%",
height: "50%"
},
plotArea: {
colorPalette: d3.scale.category20().range()
/*drawingEffect: "glossy"*/
}
};
oVizFrame.setVizProperties(properties);
var feedValueAxis = new sap.viz.ui5.controls.common.feeds.FeedItem({
"uid": "valueAxis",
"type": "Measure",
"values": ["Age"]
}),
feedCategoryAxis = new sap.viz.ui5.controls.common.feeds.FeedItem({
"uid": "categoryAxis",
"type": "Dimension",
"values": ["Name"]
});
oVizFrame.addFeed(feedValueAxis);
oVizFrame.addFeed(feedCategoryAxis);
},
onExportPress: function() {
//Logic to export data in the page to xls format
//Read the HTML content Dynamically
var hContent = '<html><head></head><body class="sapUiSizeCompact displayCSS">';
var bodyContent = "";
bodyContent = $(".ExportPage").html();
var closeContent = "</body></html>";
var htmlpage = hContent + bodyContent + closeContent;
var htmls = "";
var uri = 'data:application/vnd.ms-excel;base64,';
var base64 = function(s) {
return window.btoa(unescape(encodeURIComponent(s)));
};
var format = function(s, c) {
return s.replace(/{(\w+)}/g, function(m, p) {
return c[p];
});
};
htmls = "Page Export";
var ctx = {
worksheet: 'Page_Export',
table: htmls
};
var link = document.createElement("a");
link.download = "Page_Export.xls";
link.href = uri + base64(format(htmlpage, ctx));
link.click();
}Our UI looks as follows:
On export button click, the page data is exported as in the attached screenshot:
You could see the broken image and only the measures and values of the line chart. In order to avoid this and format as required the small change to the body content is made as follows:
//Read the HTML content Dynamically
var hContent = '<html><head></head><body class="sapUiSizeCompact displayCSS" >';
var bodyContent = "";
/*bodyContent = $(".ExportPage").html();*/
bodyContent = $(".SampleText").html() + "<br>" + $(".tableId").html();
var closeContent = "</body></html>";
var htmlpage = hContent + bodyContent + closeContent;Our final Output looks as follows:
I wonder if you could have embedded ABAP2XLSX. (GitHub) Yes it would have had to be written in ABAP and pulled into the UI5. It just makes spreadsheets so pretty to look at. Maybe another project is needed. One that starts in UI5 where you can easily create Excel formatting.
Since I don't know UI5 at all - with this comment perhaps someone is/or will start work on it.
Michelle
Hi Sri Divya
I have tried this and it is working fine in chrome but not working in IE and Edge.
Can you please help me to sort of this browser issue?
Hi Sree,
May I know what the issue is? I feel it might be a browser compatibility issue.
Thank you,
Sri Divya.
Hi Divya,
I have try it but i got Undefined in my excel sheet.
Can you help out !
Thanks in Advance!
Mangesh.
Mangesh Bartakke
Hi Mangesh
I am facing the same issue too. Were you able to find a solution?
Dear both,
Sorry for the late reply. Could you please check if the class defined is matching with the class specified while reading the html information in the controller