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:
- Create a Form and Table in View
- Declaring third party library in controller
- Export button Functionality.
- 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.
