Skip to Content

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&gt;/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:

To report this post you need to login first.

1 Comment

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

  1. Michelle Crapo

    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

    (0) 

Leave a Reply