Skip to Content
Business Trends

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

 

 

 

 

 

 

 

 

 

4 Comments
You must be Logged on to comment or reply to a post.
  • 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

  • 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

  • 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