Copy Columns from Excel and Paste Them on Selected Grid Columns
Environment
Product Version | 2018.1 221 |
Product | Progress® Kendo UI® Grid for jQuery |
Description
How can I copy Excel columns and paste them on the selected columns in the Kendo UI Grid?
Solution
Handle the keydown
event of the Grid—in the event handler and if the user is pasting:
- Programmatically
focus
an invisible text-area to paste the content on it. - Use the value of the text-area to create an array of single cell values.
- Get the selected cells of the Grid.
- For every selected cell,
shift
a value from the array.
<div id="grid"></div>
<textarea id="ta" style="opacity: 0"></textarea>
<script>
$("#grid").kendoGrid({
selectable: "multiple cell",
allowCopy: true,
columns: [
{ field: "productName0" },
{ field: "productName1" },
{ field: "productName2" },
{ field: "productName3" },
{ field: "productName4" }
],
dataSource: [
{ productName0: "Tea", productName1: "Tea", productName2: "Tea", productName3: "Tea", productName4: "Tea" },
{ productName0: "Coffee", productName1: "Coffee", productName2: "Coffee", productName3: "Coffee", productName4: "Coffee"},
{ productName0: "Ham", productName1: "Ham", productName2: "Ham", productName3: "Ham", productName4: "Ham"},
{ productName0: "Bread", productName1: "Bread", productName2: "Bread", productName3: "Bread", productName4: "Bread"}
]
});
var grid = $('#grid').data('kendoGrid');
grid.element.on('keydown', function(e) {
if(e.keyCode===86 && e.ctrlKey===true){
var textarea = $("#ta");
textarea.val("");
textarea.focus();
setTimeout(function(e){
var value = $.trim(textarea.val());
var grid = $("#grid").data("kendoGrid");
var rows = value.split('\n');
var data = [];
for (var i = 0; i < rows.length; i++) {
var cells = rows[i].split('\t');
for (var j = 0; j < cells.length; j++) {
data.push(cells[j]);
}
};
var select = grid.select();
select.each(function(i, e){
var cell = $(this);
var row = cell.closest("tr");
var dataItem = grid.dataItem(row);
var dataField = $(grid.thead.find("th")[cell[0].cellIndex]).attr("data-field");
dataItem[dataField] = data.shift()
});
grid.refresh();
},1)
}
})
</script>