Creation of Dynamic Table with csv, xlsx Files using File upload in SAP Ui5

Hello Everyone!!!!

In this Blog Post, I am going to explain how to generate Tables dynamically while uploading both CSV and XLSX file formats. Mostly used file format is Comma Separated Value(CSV) reading data from and to CSV file is easy. While coming to XLSX  it is a little bit difficult, while handling the XLSX file reading data from and to XLSX file.

Here I am creating a table Dynamically while reading data from both files CSV, XLSX to generate a sap.m.table.

Step 1:

Create a new UI5 Application  in SAP Cloud Web IDE.

Step 2:

In the View Part we will use the FileUploader Control using namespace called sap.ui.unified. 

<content>
<OverflowToolbar>
<ToolbarSpacer/>
<u:FileUploader class="sapUiTinyMarginBegin" id="FileUploaderid" width="35" placeholder="Choose a file" name="myFileUpload"  change="onhandleUpload" fileType="xlsx,csv"/>
</OverflowToolbar>
<Table showNoData="true" id="Tableid"></Table>
</content>

Step 3:

In the Index.html File for the XLSX File Upload format we need to add these script Tags JS files of the SheetJS as shown as below : 

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.0/jszip.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.0/xlsx.js"></script>

Step 4:

To instantiate the model, use the following code:

var that = this;
var oModel = new JSONModel();
that.getView().setModel(oModel);

var sampleModel = new sap.ui.model.json.JSONModel();
that.getView().setModel(sampleModel, "sampleModel");

var columnModel = new JSONModel();
that.getView().setModel(columnModel, "columnModel");

Step 5: uploadFile.controller.js 

The Function “onhandleUpload” will triggers when we browse the File.

/* Function to Upload the file*/
onhandleUpload: function (oEvent) {
var that = this;
var oFileUploader = that.getView().byId("FileUploaderid");
var oFile = oFileUploader.getFocusDomRef().files[0];
//To check the File type of uploaded File.
if (oFile.type === "application/vnd.ms-excel") {
//To call the CSV File Function
that.typeCsv();
                                               }
else {
//To call the XLSX File Function
that.typeXLSX();
     }
                                   } 

Step 6:

Function “typeCsv” triggers when the file format is csv.

typeCsv: function () {
var that = this;
var oFileUploader = that.getView().byId("FileUploaderid");
var oFile = oFileUploader.getFocusDomRef().files[0];
if (oFile && window.FileReader) {
var reader = new FileReader();
reader.onload = function (evt) {
var strData = evt.target.result; 
that.csvJSON(strData);
that.getView().getModel("sampleModel").refresh(true);
};
reader.onerror = function (exe) {
	console.log(exe);
				};
reader.readAsText(oFile);

			}

		},
csvJSON: function (csv) {
var that = this;
var lines = csv.split("\n");
var result = [];
var colheaders = lines[0].split(",");
for (var i = 1; i < lines.length; i++) {
var obj = {};
var currentline = lines[i].split(",");
for (var j = 0; j < colheaders.length; j++) {
obj[colheaders[j]] = currentline[j];
				}
	result.push(obj);
			}
that.getView().getModel().setProperty("/", result);
that.generateTableCsv();
		},
	/*	Function to create the table dynamically for csv File*/
generateTableCsv: function () {
var that = this;
var oTable = that.getView().byId("Tableid");
var oModel = that.getView().getModel();
var oModelData = oModel.getProperty("/");
var ColumnsData = Object.keys(oModelData[0]);
var oColumnNames = [];
$.each(ColumnsData, function (i, value) {
oColumnNames.push({
Text: ColumnsData[i]
				});
			});
oModel.setProperty("/columnNames", oColumnNames);
var columnmodel = that.getView().getModel("columnModel");
columnmodel.setProperty("/", oColumnNames);
var oTemplate = new Column({
header: new Label({
text: "{Text}"
	})
			});
oTable.bindAggregation("columns", "/columnNames", oTemplate);
var oItemTemplate = new ColumnListItem();
var oTableHeaders = oTable.getColumns();
$.each(oTableHeaders, function (j, value) {
var oHeaderName = oTableHeaders[j].getHeader().getText();
oItemTemplate.addCell(new Text({
text: "{" + oHeaderName + "}"
				}));
			});
oTable.bindAggregation("items", {
path: "/",
template: oItemTemplate

			});

		}

Step 7: 

Function “typeXLSX” triggers when the file format is xlsx.

	/*	Function to create the table dynamically for csv File*/
generateTableCsv: function () {
var that = this;
var oTable = that.getView().byId("Tableid");
var oModel = that.getView().getModel();
var oModelData = oModel.getProperty("/");
var ColumnsData = Object.keys(oModelData[0]);
var oColumnNames = [];
$.each(ColumnsData, function (i, value) {
oColumnNames.push({
Text: ColumnsData[i]
		});
			                });
oModel.setProperty("/columnNames", oColumnNames);
var columnmodel = that.getView().getModel("columnModel");
columnmodel.setProperty("/", oColumnNames);
var oTemplate = new Column({
header: new Label({
text: "{Text}"
		})
	                  });
oTable.bindAggregation("columns", "/columnNames", oTemplate);
var oItemTemplate = new ColumnListItem();
var oTableHeaders = oTable.getColumns();
$.each(oTableHeaders, function (j, value) {
var oHeaderName = oTableHeaders[j].getHeader().getText();
oItemTemplate.addCell(new Text({
text: "{" + oHeaderName + "}"
				}));
			});
oTable.bindAggregation("items", {
     path: "/",
     template: oItemTemplate

			});

		},
typeXLSX: function() {
var that = this;
var oFileUploader = that.getView().byId("FileUploaderid");
var file = oFileUploader.getFocusDomRef().files[0];
var excelData = {};
if (file && window.FileReader) {
var reader = new FileReader();
reader.onload = function (evt) {
var data = evt.target.result;
var workbook = XLSX.read(data, {
type: 'binary'
		});
workbook.SheetNames.forEach(function (sheetName) {
excelData = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
					});
that.getView().getModel("sampleModel").setData(excelData);
that.getView().getModel("sampleModel").refresh(true);
that.generateTablexlsx();
			};
reader.onerror = function (ex) {
			  console.log(ex);
				};
		    	reader.readAsBinaryString(file);
			}
		},
		/*Function to create the table Dynamically for xlsx file*/
generateTablexlsx: function () {
var that = this;
var oTable = that.getView().byId("Tableid");
var oModel = that.getView().getModel("sampleModel");
var oModelData = oModel.getProperty("/");
var oColumns = Object.keys(oModelData[0]);
var oColumnNames = [];
$.each(oColumns, function (i, value) {
	oColumnNames.push({
	Text: oColumns[i]
		        });
			            });
var columnmodel = that.getView().getModel("columnModel");
columnmodel.setProperty("/", oColumnNames);
var oTemplate = new Column({
				header: new Label({
				text: "{columnModel>Text}"
				})
			});
oTable.bindAggregation("columns", "columnModel>/", oTemplate);
var oItemTemplate = new ColumnListItem();
var oTableHeaders = oTable.getColumns();
$.each(oTableHeaders, function (j, value) {
var oHeaderName = oTableHeaders[j].getHeader().getText();
oItemTemplate.addCell(new Text({
		text: "{sampleModel>" + oHeaderName + "}"
				}));
			});
oTable.bindAggregation("items", {
path: "sampleModel>/",
template: oItemTemplate
			});
		}

Step 8: Below is the output:

DynamicTable.csv: Table is created dynamically by uploading  csv file.

DynamicTable.xlsx: Table is created dynamically by uploading xlsx file.

Hope this blog will give a clear idea to upload both csv,xlsx files to create table dynamically. I hope you will understand the concept of uploading the file formats.

Thanks for Reading. 

Have a Nice day!