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