Skip to Content
Author's profile photo Sri Divya

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&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:

Assigned tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Michelle Crapo
      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

      Author's profile photo Sree katreddy
      Sree katreddy

      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?

      Author's profile photo Sri Divya
      Sri Divya
      Blog Post Author

      Hi Sree,

      May I know what the issue is? I feel it might be a browser compatibility issue.

       

      Thank you,

      Sri Divya.

      Author's profile photo Mangesh Bartakke
      Mangesh Bartakke

      Hi Divya,

       

      I have try it but i got Undefined in my excel sheet.

       

      Can you help out !

       

      Thanks in Advance!

      Mangesh.

      Author's profile photo Aluru Lakshmi Neeraja
      Aluru Lakshmi Neeraja

      Mangesh Bartakke

      Hi Mangesh

      I am facing the same issue too. Were you able to find a solution?