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
Also Read: SAP SF Variable Pay Certification Preparation Guide
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.
