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

[rajesh_salapu]

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