Skip to Content
Business Trends
Author's profile photo Pachalam Rajasekhar

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

 

 

 

 

 

 

 

 

 

Assigned tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Joseph BERTHE
      Joseph BERTHE

      Hi,

      Nice blog. I wanted to highlight you that this feature already exists in SAPUI5 in some specific case :

      https://ui5.sap.com/#/topic/f6a8fd2812d9442a9bba2f6fb296c42e

      Regards,

      Joseph

      Author's profile photo Roshan Kumar Gupta
      Roshan Kumar Gupta

      Hi

      Nice blog, I think most of the things you have done it from scratch. In UI5 they have provided public events for achieving this feature on smart table.

      Regards

      Roshan

      Author's profile photo Ritesh Kumar
      Ritesh Kumar

      Hello,

       

      Could you kindly let me know if this can be extended to the sap.ui.table.Table. I have a requirement to copy and paste from excel sheet to rows in sap.ui.table.

       

      Regards

      Ritesh

      Author's profile photo Michael C
      Michael C

      Hello,

      I am trying to put a toolbar in the custom table after the below code. When ever i add this there is an issue that resource is not available. Any suggestions are appreciated

      <c:CopyPasteTable id="tableId" items="{/Data}" class="sapUiSizeCompact">

      <headerToolbar>
      <OverflowToolbar>
      <Button text=”{i18n>btnTxtPrintCountSheet}” type=”Emphasized” icon=”sap-icon://print” iconFirst=”true” enabled=”true” visible=”true”
      iconDensityAware=”false” class=”sapUiTinyMargin”/>
      <Button text=” ” type=”Emphasized” icon=”sap-icon://add” iconFirst=”true” width=”auto” enabled=”true” visible=”true” press=”onAddPress”
      iconDensityAware=”false” class=”sapUiTinyMargin”/>
      </OverflowToolbar>
      </headerToolbar>

       

      P.S. Solution : I was committing a simple mistake

      <c:headerToolbar> </c:headerToolbar> Solved the issue