Skip to Content
Technical Articles
Author's profile photo Rajesh Salapu

Consume Third party Libraries npm-xlsx in sapui5 to export page data to Excel

Hi Readers!!

Hope Everyone are doing Good and safe.

In this blog post I am going to demonstrate how you can Export exact page data to Excel using npm-xlsx libraries with adding excel Styles.

Why npm-xlsx : This is the third party library generally used for Exporting and data to XLSX file with adding Styles , formatting etc..

Steps includes:

  1. Create a Form and Table in View
  2. Declaring third party library in controller
  3. Export button Functionality.
  4. Output

 

 1.Create a Form and Table in View:

Created Simple form for displaying Employee details and Created a Table for Displaying Employee last 6 years Address.

<mvc:View controllerName="comThird_party_application.controller.View1" xmlns:mvc="sap.ui.core.mvc" displayBlock="true" xmlns="sap.m"
	xmlns:l="sap.ui.layout" xmlns:f="sap.ui.layout.form" xmlns:core="sap.ui.core">
	<App>
		<pages>
			<Page title="{i18n>title}">
				<content>
					<VBox class="sapUiSmallMargin">
						<f:SimpleForm id="Form2" editable="false" layout="ResponsiveGridLayout" title="Employee Details" labelSpanXL="4" labelSpanL="6"
							labelSpanM="6" labelSpanS="12" adjustLabelSpan="false" emptySpanXL="0" emptySpanL="4" emptySpanM="0" emptySpanS="0" columnsXL="2"
							columnsL="2" columnsM="2" singleContainerFullSize="false">
							<f:content>
								<Label text="Name "/>
								<Text text="Rajesh"/>
								<Label text="Id "/>
								<Text text="123456"/>
								<Label text="Company "/>
								<Text text="Mouritech "/>
								<Label text="Phone Number "/>
								<Text text="123456"/>
							</f:content>
						</f:SimpleForm>
					</VBox>
					<Table width="auto" id="imTable" items="{path: 'Model>/results'}" class="sapUiResponsivePadding tableCls" growingScrollToLoad="true"
						alternateRowColors="true" growing="true" growingThreshold="20">
						<headerToolbar>
							<Toolbar>
								<content>
									<ToolbarSpacer/>
									<Label text="Employee Address Data of last 6 years " design="Bold"/>
									<ToolbarSpacer/>
								</content>
							</Toolbar>
						</headerToolbar>
						<columns>
							<Column id="col1"  width="5rem">
								<Label text="House Number" wrapping="true" design="Bold"></Label>
							</Column>
							<Column id="col2"  width="6rem">
								<Label text="Village/City" wrapping="true" design="Bold"></Label>
							</Column>
							<Column id="col3" minScreenWidth="Desktop" demandPopin="true"  width="6rem">
								<Label text="Phone Number" wrapping="true" design="Bold"></Label>
							</Column>
							<Column id="col4" minScreenWidth="Desktop" demandPopin="true"  width="6rem">
								<Label text="Pincode" wrapping="true" design="Bold"></Label>
							</Column>
						</columns>
						<items>
							<ColumnListItem>
								<cells>
									<Text text="{Model>H_no}"/>
									<Text text="{Model>city}"/>
									<Text text="{Model>Phone_number}"/>
									<Text text="{Model>Pincode}"/>
								</cells>
							</ColumnListItem>
						</items>
					</Table>
				</content>
				<footer>
					<Bar>
						<contentRight>
							<Button tooltip="Excel To Excel" text="Excel To Excel" icon="sap-icon://print" press="onPrint" type="Accept"/>
						</contentRight>
					</Bar>
				</footer>
			</Page>
		</pages>
	</App>
</mvc:View>

 

   Page Output :


Output%20of%20view

Output of view

 

2.Declaring third party library in controller:

As per page output we have to Export page data with same design and adding colors to output sheet by using npm-xlsx libraries.

Create libs folder in webapp and Create a .js file I the name of “styleXLSX” and paste the code from given link:

“https://github.com/gitbrent/xlsx-js-style/blob/master/dist/xlsx.bundle.js”

First load the npm-xlsx library data in libs folder and declare that library in controller.

“$.sap.require(“com/Dynamic_CustomExport/libs/styleXLSX”);”

 

3.Export button Functionality:

First, we have to create a Html table as per our Output data. Then convert to Dom element by using DOM Parser. Then convert this Dom element to Work sheet using “XLSX.utils.table_to_sheet” method in npm-xlsx.

 

// Simple form Data 	
var Form2contents = that.getView().byId("Form2").getContent();
//Table Data 
			var data = that.getView().getModel("Model").getData().results;
//creating a html table
			var html = "";
			html += "<tr>" +
				"<th colspan = 4>" + "Employee Details" + "</th>" + "</tr>" +
				"<tr>" + "<td colspan = 3>" + Form2contents[0].getText() + "</td>" +
				"<td colspan = 1>" + Form2contents[1].getText() + "</td>" + "</tr>" +
				"<tr>" + "<td colspan = 3>" + Form2contents[2].getText() + "</td>" +
				"<td colspan = 1>" + Form2contents[3].getText() + "</td>" + "</tr>" +
				"<tr>" + "<td colspan = 3>" + Form2contents[4].getText() + "</td>" +
				"<td colspan = 1>" + Form2contents[5].getText() + "</td>" + "</tr>" +
				"<tr>" + "<td colspan = 3>" + Form2contents[6].getText() + "</td>" +
				"<td colspan = 1>" + Form2contents[7].getText() + "</td>" + "</tr>" +
				"<tr>" + "</tr>" + "<tr>" + "</tr>";

			html += "<tr>" + "<th colspan = 4>" + "Employee Address Data of last 6 years " + "</th>" + "</tr>";

			html += "<tr>" +
				"<td>" + "House Number" + "</td>" +
				"<td>" + "Village/City" + "</td>" +
				"<td>" + "Phone Number" + "</td>" +
				"<td>" + "Pincode" + "</td>" + "</tr>";
//adding table data dynamically

			for (var k = 0; k < data.length; k++) {
				html += "<tr>" +
					"<td>" + (data[k].H_no) + "</td>" +
					"<td>" + data[k].city + "</td>" +
					"<td>" + data[k].Phone_number + "</td>" +
					"<td>" + data[k].Pincode + "</td>" + "</tr>";

			}
			$("#tableHtml").html(html);
			var Table = "<table>" + html + "</table>";

			var Sheet = createElementFromHTML(Table);
//function to Creta Dom element
			function createElementFromHTML(htmlString) {
				var doc = new DOMParser().parseFromString(htmlString, 'text/html');
				return doc.body.childNodes[0]; // return the child nodes
			}
//create a Work Sheet
var worksheet = XLSX.utils.table_to_sheet(Sheet, {
				cellStyles: true
			});

 

Applying Styles to the Worksheet as per our requirement by using .s property in npm-xlsx:

var header_styles = {
				fill: {
					fgColor: {
						rgb: "E9E9E9"
					}
				},
				font: {
					bold: true,
					sz: 14
				},
				alignment: {
					horizontal: "center"
				}
			};
//applying styles to particular cells
	worksheet["A1"].s = header_styles;

Applying Auto fit column length using ‘! cols’:

	var totalSheetrange = XLSX.utils.decode_range(worksheet['!ref']);
			var Tablerange = {
				s: {
					c: 0,
					r: 0
				},
				e: {
					c: totalSheetrange.e.c,
					r: totalSheetrange.e.r
				}
			};

			for (var R1 = Tablerange.s.r; R1 <= Tablerange.e.r; ++R1) {
				for (var C1 = Tablerange.s.c; C1 <= Tablerange.e.c; ++C1) {
					col_length.push({
						wch: 15
					});
				}
			}
			/* for auto fit column Width */
			worksheet['!cols'] = col_length;

Created a Work book by using “XLSX.utils.book_new() “ and append our work sheet to this work

book using “XLSX.utils.book_append_sheet” and finally export this data using    “XLSX.writeFile()”.

	var workbook = XLSX.utils.book_new();
			var worksheet = XLSX.utils.table_to_sheet(Sheet, {
				cellStyles: true
			});

			var header_styles = {
				fill: {
					fgColor: {
						rgb: "E9E9E9"
					}
				},
				font: {
					bold: true,
					sz: 14
				},
				alignment: {
					horizontal: "center"
				}
			};
			var tableHeader = {
				fill: {
					fgColor: {
						rgb: "FFEA00"
					}
				},
				font: {
					bold: true,
					sz: 11
				},
				alignment: {
					horizontal: "center"
				}
			};
			worksheet["A9"].s = tableHeader;
			worksheet["B9"].s = tableHeader;
			worksheet["C9"].s = tableHeader;
			worksheet["D9"].s = tableHeader;
			worksheet["A1"].s = header_styles;
			worksheet["A8"].s = header_styles;

			var totalSheetrange = XLSX.utils.decode_range(worksheet['!ref']);
			var Tablerange = {
				s: {
					c: 0,
					r: 0
				},
				e: {
					c: totalSheetrange.e.c,
					r: totalSheetrange.e.r
				}
			};

			for (var R1 = Tablerange.s.r; R1 <= Tablerange.e.r; ++R1) {
				for (var C1 = Tablerange.s.c; C1 <= Tablerange.e.c; ++C1) {
					col_length.push({
						wch: 15
					});
				}
			}
			/* for auto fit column Width */
			worksheet['!cols'] = col_length;

			/* Append work sheet to work book */
			XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1", {
				widths: "auto"
			});

			/* for Print data to excel */
			XLSX.writeFile(workbook, "Report.xlsx", {
				bookType: 'xlsx',
				bookSST: false,
				type: 'binary',
				cellStyles: true
			});

 

Output:

Excel%20Output

Excel Output

 

Conclusion: Hope this blog post will give better understanding for to consume npm-xlsx libraries in sapui5 to  export exact page data with Excel Styles.

Please feel free while writing any kind of comment. That would be a pleasure for me to see your feedbacks or thoughts in comments. I look forward to hearing from you.

 

Best Regards

Rajesh Salapu

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Marian Zeis
      Marian Zeis

      Hi rajesh,

      Please do not use jQuery. This is more then deprecated.

      You should use ui5-tooling-modules like it's described here: https://blogs.sap.com/2022/05/15/excel-upload-using-rap-part-3/

      Please change your blog article.

      Thanks

       

      Author's profile photo Rajesh Salapu
      Rajesh Salapu
      Blog Post Author

      Hi Marian Zeis,

      Thanks for valuable feedback. If we are using lower version[local webide] or full stack  webide we  use "$" . if we are using higher version like BAS we go with the process as you mentioned in your blog post.

       

      Thanks

      Rajesh salapu

      Author's profile photo Marian Zeis
      Marian Zeis

      Hi rajesh salapu

      what do you mean with "lower/higher version"?

      You can also use ui5-tooling-modules in webide.
      You don't mention the distinction of what to use and how in your blog post.
      Someone who wants to inform here thinks this is the best option, which it is by far not!

      If you don´t want to use this, the better way is to follow this blog post.
      How to include third party libraries / modules in SAPUI5 | SAP Blogs

      This blog entry helps no one but harms more.
      I suggest to delete this one.

      Regards