Dear Readers!!

Hope Everyone are doing Good..!

In this blog post I am going to explain How to display the columns in the table and Export the columns to spreadsheet dynamically based on the oData/JSON.

Steps includes:

1. Create a Json data
2. Create a table from Controller and attach this Json data to the table.
3. Adding a custom Button to the Page.
4. Export functionality.

Introduction:

Why this Blog post:

In regular approach of displaying the columns in the table and exporting the table to spreadsheet, we must write the code statically for the number of columns that has to be displayed in the view and exported in the spreadsheet. This way we are making it the number of columns as hard coded.

Suppose if we get a new requirement where we need to display new columns and as well export the newly added columns to existing table, we must write the code to add the columns in the table to achieve the functionality. It is time consuming and extra efforts are needed again to code for the new columns.

To overcome this static approach and achieve the functionality dynamically, I have come up with this solution and this blog post will explain in detail.

Note: If we make changes to the data in the oData/JSON, then columns will also be adjusted dynamically.

In this blog post, I have used JSON data to explain how to achieve this.

Create a Json data:

Create column data and row data

{
	"results": {
		"ColumnData": [{
			"Column": "Material"
		}, {
			"Column": "Description"
		}, {
			"Column": "Quantity"
		}, {
			"Column": "Value"
		}],

		"rowData": [{
			"Material": "0001",
			"Description": "textails",
			"Quantity": "5",
			"Value": "1000"
		}, {
			"Material": "0002",
			"Description": "Iron",
			"Quantity": "25",
			"Value": "10000"
		}, {
			"Material": "0003",
			"Description": "medical",
			"Quantity": "51",
			"Value": "100000"
		}, {
			"Material": "0004",
			"Description": "plastic",
			"Quantity": "53",
			"Value": "100000"
		}, {
			"Material": "0005",
			"Description": "dispocals",
			"Quantity": "45",
			"Value": "990000"
		}]
	}
}

I have created sample JSON data for the table to achieve this functionality.

Create a table from Controller and attach this Json data to the table:

First get the data which you declare in model into model data and the model data assign to table Columns and Rows.

Below code will fetch the data from the model and it will assign the data to table.

onInit: function () {
	var that = this;
        var modelData = new JSONModel(jQuery.sap.getModulePath("com.TablefromController", 
                        "/model/Data.json"));
	that.getView().setModel(modelData, "TableData");
	var oTable = new Table("Table", {alternateRowColors: true});
			oTable.setModel(modelData, "TableData");
			that.getView().byId("oPage").addContent(oTable);
			oTable.bindAggregation("columns", "TableData>/results/ColumnData", function 
                               (index, context) {
				return new sap.m.Column({
					header: new sap.m.Label({
						text: context.getObject().Column
					})
				});
			});
			oTable.bindItems("TableData>/results/rowData", function (index, context) {
				var obj = context.getObject();
				var row = new ColumnListItem();

				for (var i in obj) {
					row.addCell(new Text({
						text: obj[i]
					}));
				}
				return row;
			});
			this.getView().byId("oPage").addContent(oTable);
		}

Output:

Adding a custom Button to the Page:

Add an Export button explicitly to the view.

//adding custom button to the header content of page 
var oButton = new sap.m.Button("Export", {
icon: "sap-icon://excel-attachment", 
text: "Export", type: "Emphasized", 
press: function () {
that.onExport();
}); 
that.byId("oPage").addHeaderContent(Button);

Output:

Export button is added.

Export functionality:

Create oSheet by using spread sheet library and for to collect column data we Create a function this.createColumConfig().

onExport: function (oEvent) {
//varibles related to spreedsheet 
var acols, Settings, Sheet;
var collection Record = this.getView().getModel("TableData").getData().results.rowData; 
//calling columns event in order to get that colums 
acols = this.createColumnConfig(); 
//geting hole data from model 
oSettings = { 
      workbook : {
          columns: acols, context: {
          sheetName: "Dynamic Data"
          dataSource: collectionRecord, 
          fileName: "Dynamic Data"
//created spreedsheet 
oSheet = new Spreadsheet(osettings); 
OSheet.build()
      .then(function () {
// Message Toast.show("Spreadsheet export has finished);
      .finally(function () {
       Sheet.destroy(); 
     });

this.createColumConfig() function:

Get the data from the model and with in loop get label and property and type and push that data in to an array after stringfy the data because if not done the data vanished by new data .so after completion of loop convert the stringfy data to normal data to a new array  by using MAP function.

Below code snippet will generate the columns dynamically based on the data for us making the job easier and quicker.

createColumnConfig: function() {
	var data = this.getView().getModel("TableData").getData().results;
	var PropertyData = data.ColumnData;
	var arr = [];
	var obj = {};
	for (var i = 0; i < PropertyData.length; i++) {
	        obj.label = PropertyData[i].column;
		obj.property = PropertyData[i].Column;
                obj.type = "string";
		var sobj = JSON.stringify(obj);
                    arr.push(sobj);
		}
	var columnProperties = arr.map(function(value) {
	    return JSON.parse(value);
		});
	return columnProperties;

     }

Obj: After JSON.Stringfy

Column Properties in console: After JSON.parse

View:

<mvc:View controllerName="com.TablefromController.controller.View1" xmlns:html="http://www.w3.org/1999/xhtml" xmlns:mvc="sap.ui.core.mvc"
	displayBlock="true" xmlns="sap.m">
	<App>
		<pages>
			<Page title="{i18n>title}" id="oPage">
				<content></content>
			</Page>
		</pages>
	</App>
</mvc:View>

In the view file, we are creating a page for displaying content of the table.

Output:

When we click on Export button, the data is exported to the local machine in xlsx format.

Conclusion:

Using this approach, we can generate the columns dynamically while exporting the data in spreadsheet. This will reduce the extra efforts and save the time. We need not write the code again and again if we get any new requirement to export for the new columns.

Hope this blog post will give better understanding how to achieve the Export Functionality by creating the columns dynamically.

Please feel free to suggest if any correction is needed:)