Skip to Content
Technical Articles
Author's profile photo Mamatha Majji

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! 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Assigned tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Shai Sinai
      Shai Sinai

      Thanks for sharing the code.

       

      I think that your CSV implementation is too simplified. You should take quote character and double quote into account.

      Author's profile photo Suren Battula
      Suren Battula

      Hi Mamatha,

      Thankyou for sharing the code.

      I have Implemented your code its working fine for the XLSX file, Coming to CSV file when am trying to upload the file data is coming in one column. can you please help me out.

      Thankyou

      Author's profile photo Vignesh M
      Vignesh M

      I got error Please help me.

      ReferenceError: column is not defined