EDUCAÇÃO E TECNOLOGIA

Copy Data From Excel and Paste it on Table in SAP UI5

Hi All,

A few weeks back I had a requirement where the users are copying the data from Excel and paste it on the table.This requirement makes me think. So in this blog post i am going to explain how i achieved the requirement

Step-1:

Login to SAP Web IDE.

Step-2:

Now I am going to create a new project by selecting New Project from Template.

Step-3:

Choose SAP UI5 Application template and press Next button.

Step-4:

In the Basic Information tab, give the project details like Project Name, Namespace and press Next button.

Here I given Project Name as “ExcelData_Copy”

Namespace as “com.data”

Step-5:

In the Template Customization tab, give the View Type and View Name and Click on Finish button.

Step-6:

The Application is created as shown below.

Step-7:

First we need to create a table.

<mvc:View controllerName="Table_Binding.controller.View1" xmlns:c="Table_Binding.control" xmlns:core="sap.ui.core" xmlns:html="http://www.w3.org/1999/xhtml" xmlns:mvc="sap.ui.core.mvc" xmlns="sap.m"> <App> <pages> <Page title="{i18n>title}" > <content> <Panel> <c:CopyPasteTable id="tableId" items="{/Data}" class="sapUiSizeCompact"> <c:columns> <Column><Label design="Bold" text="EmpID"/></Column> <Column><Label design="Bold" text="EmpName"/></Column> <Column><Label design="Bold" text="Designation"/></Column> <Column><Label design="Bold" text="PhoneNumber"/></Column> </c:columns> <c:items> <ColumnListItem> <cells> <Text text="{EmpID}"></Text> <Text text="{EmpName}"></Text> <Input value="{Designation}" width="50%"></Input> <Input value="{PhNumber}" width="50%"></Input> </cells> </ColumnListItem> </c:items> </c:CopyPasteTable> </Panel> </content> </Page> </pages> </App> </mvc:View>

In order to bind the table data, we need a sample JSON. So below you can find the sample JSON file in model folder with name as Objects.json file.

Step-8:

Let us have a sample data to bind the table.

Objects.json:

{ "Data": [{ "EmpID": "1", "EmpName": "TestName1", "Designation": "", "PhNumber": "" }, { "EmpID": "2", "EmpName": "TestName2", "Designation": "", "PhNumber": "" }, { "EmpID": "3", "EmpName": "TestName3", "Designation": "", "PhNumber": "" }, { "EmpID": "4", "EmpName": "TestName4", "Designation": "", "PhNumber": "" }, { "EmpID": "5", "EmpName": "TestName5", "Designation": "", "PhNumber": "" } ] }

Step 9:

Now let us first bind the data using above json file.

sap.ui.define([ "sap/ui/core/mvc/Controller" ], function(Controller) { "use strict"; return Controller.extend("Table_Binding.controller.View1", { onInit: function() { //Binding using JSON Model var oModel2 = new sap.ui.model.json.JSONModel(jQuery.sap.getModulePath("Table_Binding", "/model/Objects.json")); this.getView().setModel(oModel2); } }); });

Step-10:

After binding the data to the table the output is shown below.

Here my requirement is copying the data from Excel and paste in the above UI5 table . To achieve this, I have taken a Js file(CopyPaste Table.js) in the control folder, which will be loaded when the app is initialized.I defined that control in our view (xmlns:c=”Table_Binding.control“, Here Table_Binding is my name space). Below is the js code for copying the Excel data to UI5 Application.

Step-11:

Step-12:

Control Folder JS File(CopyPasteTable.js):

var currentRowIndex; sap.ui.define([ "sap/m/Table" ], function(Table) { return Table.extend("Table_Binding.CopyPasteTable", { onInit: function() { }, insertRows: function(value, table, model, startRowIndex, startProperty) { var oTableLength = table.getModel(model).getData().Data.length; var rows = value.split(/\n/); var sNewCopiedData; if (currentRowIndex !== 0) { sNewCopiedData = rows.slice(0, currentRowIndex-1); } else { sNewCopiedData = rows.slice(0, oTableLength+1); } var cells = table.getBindingInfo('items').template.getCells(); var templateItem = []; var itemsPath = table.getBindingPath('items'); var itemsArray = table.getModel(model).getProperty(itemsPath); var startPropertyIndex = 0; var model = table.getModel(model); var fData = model.oData.Data; if (startPropertyIndex === 2) { for (var i = 0; i < fData.length; i++) { for (var int = 0; int < sNewCopiedData.length - 1; int++) { var rows_element = sNewCopiedData[int]; fData[i].Number = rows_element; } } } else if (startPropertyIndex === 3) { for (var q = 0; q < fData.length; q++) { for (var w = 0; w < sNewCopiedData.length - 1; w++) { var row = sNewCopiedData[w]; fData[q].Email = row; } } } if (startRowIndex === undefined) { startRowIndex = 0; } for (var int = 0; int < cells.length; int++) { var cell_element = cells[int]; var path = cell_element.getBindingPath('value'); templateItem.push(path); if (path === startProperty) { startPropertyIndex = int; } } for (var int = 0; int < sNewCopiedData.length - 1; int++) { var rows_element = sNewCopiedData[int]; var cells = rows_element.split(/\t/); var originalObject = model.getProperty(itemsPath + "/" + startRowIndex++); if (originalObject === undefined) { originalObject = {}; for (var k = 0; k < templateItem.length; k++) { originalObject[templateItem[k]] = undefined; } itemsArray.push(originalObject); } var lesserLength = Math.min(templateItem.length, (cells.length + startPropertyIndex)); for (int2 = startPropertyIndex, intValue = 0; int2 < lesserLength; int2++, intValue++) { var name = templateItem[int2]; originalObject[name] = cells[intValue]; } } model.refresh(); }, onAfterRendering: function() { var that = this; sap.m.Table.prototype.onAfterRendering.apply(this, arguments); this.attachBrowserEvent('paste', function(e) { e.preventDefault(); var text = (e.originalEvent || e).clipboardData.getData('text/plain'); console.log(text); that.insertRows(text, this, undefined); }); this.getAggregation('items').forEach(function(row) { row.getCells().forEach(function(cell) { cell.attachBrowserEvent('paste', function(e) { e.stopPropagation(); e.preventDefault(); var text = (e.originalEvent || e).clipboardData.getData('text/plain'); console.log(text); var domCell = jQuery.sap.domById(e.currentTarget.id); var insertCell = jQuery('#' + domCell.id).control()[0]; var itemsPath = that.getBindingPath('items'); var pathRow = insertCell.getBindingContext().sPath; currentRowIndex = parseInt(pathRow.substring(pathRow.lastIndexOf('/') + 1)); //Selected row index var startRowIndex = pathRow.split(itemsPath + "/")[1]; var startProperty = insertCell.getBindingPath('value'); that.insertRows(text, that, undefined, startRowIndex, startProperty); }); }); }); }, renderer: sap.m.Table.prototype.getRenderer() }); });

In the onAfterRendering method, we have the events for Copy, Paste.

	var domCell = jQuery.sap.domById(e.currentTarget.id); var insertCell = jQuery('#' + domCell.id).control()[0];

By using the above code we are identifying the cell where we want to paste the copied Data.

Below is the data I want to copy from Excel and Paste on Table.

For Pasting on the table we need to place the cursor on the inputs where we want to paste.Here I am pasting the above data to the two inputs(Designation and Phone Number)

Therefore we can have the following output.

Here I have copied the data for 4 rows only. So that 4 records pasted on the table.

So this is how I have achieved the functionality. According to my skill set, I have written this blog post and make others get some knowledge.

Hope this will help.

Thank you