Available in the Full Version

Data Grid - Paste from Excel

Select Paste mode:

This sample is designed for a larger screen size.

On mobile, try rotating your screen, view full size, or email to another device.

This sample demonstrates how to implement pasting from Excel into the igGrid control. Open up any Excel spreadsheet (or this one here), copy some rows, and paste it into the grid using the keyboard.
There are two possible modes:
Paste data as new records - when this mode is selected the pasted records will be added as new rows in the grid.
Paste starting from active cell - when this mode is selected the pasted records will replace the data starting from the active grid cell.

Code View

Show In JsFiddle

Copy to Clipboard

HTML
Data

                    <!DOCTYPE html>
<html>
<head>
	<title></title>

	<!-- Ignite UI for jQuery Required Combined CSS Files -->
	<link href="http://cdn-na.infragistics.com/igniteui/2024.1/latest/css/themes/infragistics/infragistics.theme.css" rel="stylesheet" />
	<link href="http://cdn-na.infragistics.com/igniteui/2024.1/latest/css/structure/infragistics.css" rel="stylesheet" />

	<script src="http://ajax.aspnetcdn.com/ajax/modernizr/modernizr-2.8.3.js"></script>
	<script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
	<script src="http://code.jquery.com/ui/1.11.1/jquery-ui.min.js"></script>

	<!-- Ignite UI for jQuery Required Combined JavaScript Files -->
	<script src="http://cdn-na.infragistics.com/igniteui/2024.1/latest/js/infragistics.core.js"></script>
	<script src="http://cdn-na.infragistics.com/igniteui/2024.1/latest/js/infragistics.lob.js"></script>
</head>
<body>
	<fieldset id="pasteOptions" class="explorer">
		<label for="pasteMode">Select Paste mode:</label>
		<input id="pasteMode" />
	</fieldset>
	<fieldset class="explorer">
		<table id="grid"></table>
	</fieldset>

		<fieldset id="editingOptions" class="explorer">
			<input type="button" id="saveChanges" class="button-style" value="Save Changes" />
			<input type="button" id="undo" class="button-style" value="Undo" />
			<input type="button" id="redo" class="button-style" value="Redo" />
		</fieldset>

		<script src="/data-files/nw-employees.js"></script>
		<script>
			$(function () {
				var pasteMode = "Paste data as new records", updates;
				var grid = $("#grid");

				//editors and buttons
				$("#pasteMode").igCombo({
					mode: "dropdown",
					width: "300px",
					dataSource: [
						 "Paste data as new records",
						 "Paste starting from active cell"
					],
					selectionChanged: function (evt, ui) {
						if (ui.items.length == 1) {
							pasteMode = ui.items[0].data.value;
						}
					}
				});

				$("#saveChanges").igButton({ labelText: $("#saveChanges").val(), disabled: true });
				$("#undo").igButton({ labelText: $("#undo").val(), disabled: true });
				$("#redo").igButton({ labelText: $("#redo").val(), disabled: true });

				$("#undo").on('igbuttonclick',
					function (e, args) {
						updates = $.extend({}, grid.data('igGrid').pendingTransactions());
						$.each(updates, function (index, transaction) {
							grid.igGrid("rollback", transaction.rowId, true);
						});
						$("#redo").igButton("option", "disabled", false);
						$("#undo").igButton("disable");
						$("#saveChanges").igButton("disable");
						return false;
					}
				);

				$("#redo").on('igbuttonclick',
					function (e) {
						$.each(updates, function (index, transaction) {
							switch (transaction.type) {
								case "row":
									grid.igGridUpdating('updateRow', transaction.rowId, transaction.row, null, false);
									break;
								case "newrow":
									grid.igGridUpdating('addRow', transaction.row, false);
									break;
								case "deleterow":
									grid.igGridUpdating('deleteRow', transaction.rowId, false);
									break;
							}

						});
						$(this).igButton("disable");
						$("#undo").igButton("option", "disabled", false);
						$("#saveChanges").igButton("option", "disabled", false);
					}
				);

				$("#saveChanges").on('igbuttonclick',
				   function (e) {
				   	grid.igGrid("commit");
				   	updates = null;
				   	$("#undo").igButton("disable");
				   	$(this).igButton("disable");
				   	return false;
				   }
			   );

				grid.on("iggridupdatingdatadirty", function (event, ui) {
					grid.igGrid("commit");
					updates = null;
					$("#undo").igButton("disable");
					$("#saveChanges").igButton("disable");
					//saving local changes to the datasource when sorting and filtering
					return false;
				});

				//grid definition and functionality
				$("#grid").igGrid({
					width: "100%",
					autoGenerateColumns: false,
					dataSource: northwindEmployees,
					responseDataKey: "results",
					dataSourceType: "json",
					primaryKey: "ID",
					columns: [
						{ headerText: "Employee ID", key: "ID", dataType: "number", width: "120px", hidden: true },
						{ headerText: "Name", key: "Name", dataType: "string" },
						{ headerText: "Title", key: "Title", dataType: "string" },
						{ headerText: "Phone", key: "Phone", dataType: "string" },
						{ headerText: "Country", key: "Country", dataType: "string" }
					],
					features: [
						{
							name: "Updating"
						},
						{
							name: "Selection",
							mode: "cell"
						},
						{
							name: "Filtering"
						},
						{
							name: "Sorting"
						}
					]
				}).on('focusin', function (e) {
					if ($("#pasteHelper").length > 0) {
						return;
					}
					var container = $(document.body);
					var containerDiv = $("<div></div>")
					.css({
						"position": "fixed",
						"top": -10000,
						"left": -10000
					});
					//create textarea for handling paste event. Text area will be transparent.
					var textArea = $("<textarea id='pasteHelper'></textarea>")
					.css({
						"opacity": 0,
						"overflow": "hidden"
					})
					.appendTo(containerDiv);
					containerDiv.appendTo(container);

					textArea.on("paste", pasteHandler);
				}).on('keydown', function (evnt) {
					//handle grid's keydown event
					var ctrl = evnt.ctrlKey, key = evnt.keyCode;
					if ((ctrl || evnt.metaKey) && key == 86 || evnt.shiftKey && key == 45) // Ctrl-V || Shift-Ins
					{
						//on paste (Ctrl+V) move focus to textarea
						$("#pasteHelper").focus();
					}
				});


				grid.on("iggridupdatingrowdeleted", function (e, args) {
					$("#undo").igButton("option", "disabled", false);
					$("#saveChanges").igButton("option", "disabled", false);
				});

				grid.on("iggridupdatingrowadded", function (e, args) {
					$("#undo").igButton("option", "disabled", false);
					$("#saveChanges").igButton("option", "disabled", false);
				});
				grid.on("iggridupdatingeditrowended", function (e, args) {
					if (args.update) {
						$("#undo").igButton("option", "disabled", false);
						$("#saveChanges").igButton("option", "disabled", false);
					}
				});

				//paste event handler for the textArea.
				function pasteHandler(event) {
					var data;

					//get clipboard data - from window.cliboardData for IE or from the original event's argumets.
					if (window.clipboardData) {
						window.event.returnValue = false;
						data = window.clipboardData.getData("text");
					} else {
						data = event.originalEvent.clipboardData.getData('text/plain');
					}

					//process the clipboard data
					var processedData = ProcessData(data);

					if (pasteMode === "Paste starting from active cell") {
						//update starting from selected cell
						UpdateRecords(processedData);
					} else {
						//add to the grid
						AddRecords(processedData)
					}

					$("#undo").igButton("option", "disabled", false);
					$("#saveChanges").igButton("option", "disabled", false);
				}

				function ProcessData(data) {
					var pasteData = data.split("\n");
					for (var i = 0; i < pasteData.length; i++) {
						pasteData[i] = pasteData[i].split("\t");

						// Check if last row is a dummy row
						if (pasteData[pasteData.length - 1].length == 1 && pasteData[pasteData.length - 1][0] == "") {
							pasteData.pop();
						}
						//remove empty data
						if (pasteData.length == 1 && pasteData[0].length == 1 && (pasteData[0][0] == "" || pasteData[0][0] == "\r")) {
							pasteData.pop();
						}
					}
					return pasteData;
				}

				function UpdateRecords(processedData) {
					//get active cell. Pasting will start from this cell as its top-right endpoint.
					var cell = $("#grid").igGridSelection("selectedCell");

					if (!cell) {
					    alert("No active cell. Please select a cell from which to begin pasting.");
						return;
					}
					cell.element.focus();

					var cellIndex = cell.index;
					var rowIndex = cell.rowIndex;

					//Get visible columns
					var columns = getVisibleColumns();

					//Get Updating feature
					var updating = grid.data("igGridUpdating");

					var excessColsCount = cellIndex + processedData[0].length - columns.length;
					showNotification(cell.row, excessColsCount);

					//update grid records based on the processed clipboard data
					for (var i = 0; i < processedData.length; i++) {
						var gridRow = grid.igGrid("rowAt", rowIndex + i);
						var curentDataRow = processedData[i];
						var rowData = {};
						for (var j = 0; j < columns.length - cellIndex; j++) {
							var currentCell = curentDataRow[j];
							var colKey = columns[j + cellIndex].key;
							rowData[columns[j + cellIndex].key] = currentCell;
						}
						updating.updateRow(parseInt($(gridRow).attr("data-id")), rowData);
					}
				}

				function AddRecords(processedData) {
					var columns = getVisibleColumns();
					var fRowID;
					for (var i = 0; i < processedData.length; i++) {
						var curentDataRow = processedData[i];
						var rowData = {};
						for (var j = 0; j < columns.length; j++) {
							var currentCell = curentDataRow[j];
							var colKey = columns[j].key;
							if (columns[j].dataType === "number") {
								rowData[colKey] = parseInt(currentCell);
							} else {
								rowData[colKey] = currentCell;
							}
						}
						//generate PK
						rowData[grid.igGrid("option", "primaryKey")] = $("#grid").igGrid("allRows").length + 1;
						if (fRowID === undefined) {
							fRowID = rowData[grid.igGrid("option", "primaryKey")];
						}
						$("#grid").igGridUpdating("addRow", rowData);
					}
					var newRec = $("#grid").igGrid("rowById", fRowID);
					var excessColsCount = processedData[0].length - columns.length;
					showNotification(newRec, excessColsCount);
				}
				function getVisibleColumns() {
					var visibleCols = [];
					var columns = grid.igGrid("option", "columns");
					$(columns).each(function () {
						if (this.hidden !== true) {
							visibleCols.push(this);
						}
					});
					return visibleCols;
				}
				function showNotification(rec, excessColsCount) {
					if (!rec.data("igNotifier")) {
						rec.igNotifier({
							mode: "popover",
							direction: "top",
							showOn: "manual",
							position: "start",
							maxWidth: 50 / 100 * grid.width()
						});
					}

					if (excessColsCount > 0) {
						var warning = "You have pasted more columns than are currently available in the grid. The last {count} column(s) from the pasted data have been disregarded.";
						//PasteWarning
						rec.igNotifier("notify", "warning", warning.replace("{count}", excessColsCount));
					} else {
						//SuccessfullyPasted
						rec.igNotifier("notify", "success", "Successfully pasted data in the grid.");
					}
					setTimeout(function () {
						rec.focus();
						$(rec).on("blur", function () {
							rec.igNotifier("hide");
						});
					}, 0);
				}
			});
		</script>
</body>
</html>

                    var northwindEmployees = [
    { "ID": 1, "Name": "Davolio, Nancy", "Title": "Sales Representative", "ImageUrl": "../../images/samples/nw/employees/1.png", "Phone": "(206) 555-9857", "PhoneUrl": "tel:(206) 555-9857", "BirthDate":"1948-12-08T00:00:00", "HireDate":"1992-05-01T00:00:00", "Country": "USA", "Languages": [{ name: "English" }, { name: "Russian" }] },
    { "ID": 2, "Name": "Fuller, Andrew", "Title": "Vice President, Sales", "ImageUrl": "../../images/samples/nw/employees/2.png", "Phone": "(206) 555-9482", "PhoneUrl": "tel:(206) 555-9482", "BirthDate":"1952-02-19T00:00:00", "HireDate":"1992-08-14T00:00:00", "Country": "USA", "Languages": [{ name: "English" }, { name: "German" }] },
    { "ID": 3, "Name": "Leverling, Janet", "Title": "Sales Representative", "ImageUrl": "../../images/samples/nw/employees/3.png", "Phone": "(206) 555-3412", "PhoneUrl": "tel:(206) 555-3412", "BirthDate":"1963-08-30T00:00:00Z", "HireDate":"1992-04-01T00:00:00Z", "Country": "USA", "Languages": [{ name: "English" }] },
    { "ID": 4, "Name": "Peacock, Margaret", "Title": "Sales Representative", "ImageUrl": "../../images/samples/nw/employees/4.png", "Phone": "(206) 555-8122", "PhoneUrl": "tel:(206) 555-8122", "BirthDate":"1937-09-19T00:00:00Z","HireDate":"1993-05-03T00:00:00Z", "Country": "USA", "Languages": [{ name: "English" }, { name: "Spanish" }] },
    { "ID": 5, "Name": "Buchanan, Steven", "Title": "Sales Manager", "ImageUrl": "../../images/samples/nw/employees/5.png", "Phone": "(71) 555-4848", "PhoneUrl": "tel:(71) 555-4848", "BirthDate":"1955-03-04T00:00:00Z","HireDate":"1993-10-17T00:00:00Z", "Country": "UK", "Languages": [{ name: "English" }, { name: "Italian" }] },
    { "ID": 6, "Name": "Suyama, Michael", "Title": "Sales Representative", "ImageUrl": "../../images/samples/nw/employees/6.png", "Phone": "(71) 555-7773", "PhoneUrl": "tel:(71) 555-7773","BirthDate":"1963-07-02T00:00:00Z","HireDate":"1993-10-17T00:00:00Z", "Country": "UK", "Languages": [{ name: "English" }, { name: "Portuguese" }] },
    { "ID": 7, "Name": "King, Robert", "Title": "Sales Representative", "ImageUrl": "../../images/samples/nw/employees/7.png", "Phone": "(71) 555-5598", "PhoneUrl": "tel:(71) 555-5598", "BirthDate":"1960-05-29T00:00:00Z","HireDate":"1994-01-02T00:00:00Z", "Country": "UK", "Languages": [{ name: "English" }, { name: "French" }, { name: "Spanish" }] },
    { "ID": 8, "Name": "Callahan, Laura", "Title": "Inside Sales Coordinator", "ImageUrl": "../../images/samples/nw/employees/8.png", "Phone": "(206) 555-1189", "PhoneUrl": "tel:(206) 555-1189","BirthDate":"1958-01-09T00:00:00Z","HireDate":"1994-03-05T00:00:00Z", "Country": "USA", "Languages": [ { name: "English" }, { name: "Mandarin" }] },
    { "ID": 9, "Name": "Dodsworth, Anne", "Title": "Sales Representative", "ImageUrl": "../../images/samples/nw/employees/9.png", "Phone": "(71) 555-4444", "PhoneUrl": "tel:(71) 555-4444", "BirthDate":"1966-01-27T00:00:00Z","HireDate":"1994-11-15T00:00:00Z", "Country": "UK", "Languages": [{ name: "English" }, { name: "Japanese" }] }
]

Ignite UI for jQuery | Samples

Search Results

Data Grid - Paste from Excel

Code View