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:
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:
Controller logic:
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:
Our final Output looks as follows:
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:
- SAP Managed Tags:
6 Comments
