Hi,
Was wondering if there was a way to create an Excel import functionality that can take a workbook with several sheets, where the first sheet is full of references of the later sheets. Is there a way to grab the VALUE at the refered to sheet and cell and replace the cell with just the value at the refered to sheet and position?
1) Click on the import file button in the spreadsheet control.
2) Select the file.
3) In a spreadsheet handler (excelImport?) allow the sheet to load during e.promise.progress (BTW I am using Kendo version 2023.3.10.10).
4) Once the sheet has loaded in the e.promise.done block...begin the process of
a) replacing the references with a cell with no formula for the reference...just the hard coded value in the value property for that cell. I tried grabbing a workbook object from the event handler:
var workbook = new kendo.ooxml.Workbook(e.sender._workbook);
But I am having issues understanding the structure of the new workbook object and am not able to access the cells in the rows as expected.
5) Prevent the default import by calling e.preventDefault();
6) Load the modified workbook via a call to: this.fromJSON(workbook.toJSON());
7) Bind it to the spreadsheet.
the issues I am having is correctly iterating though the structure of the workbook.
The other issue is .... I do not know the format of the incoming Excel sheet coming in or being imported:
Here is a complete copy of my testbed code, which you should be able to plop into the Kendo Dojo:
Was wondering if there was a way to create an Excel import functionality that can take a workbook with several sheets, where the first sheet is full of references of the later sheets. Is there a way to grab the VALUE at the refered to sheet and cell and replace the cell with just the value at the refered to sheet and position?
1) Click on the import file button in the spreadsheet control.
2) Select the file.
3) In a spreadsheet handler (excelImport?) allow the sheet to load during e.promise.progress (BTW I am using Kendo version 2023.3.10.10).
4) Once the sheet has loaded in the e.promise.done block...begin the process of
a) replacing the references with a cell with no formula for the reference...just the hard coded value in the value property for that cell. I tried grabbing a workbook object from the event handler:
var workbook = new kendo.ooxml.Workbook(e.sender._workbook);
But I am having issues understanding the structure of the new workbook object and am not able to access the cells in the rows as expected.
5) Prevent the default import by calling e.preventDefault();
6) Load the modified workbook via a call to: this.fromJSON(workbook.toJSON());
7) Bind it to the spreadsheet.
the issues I am having is correctly iterating though the structure of the workbook.
The other issue is .... I do not know the format of the incoming Excel sheet coming in or being imported:
Here is a complete copy of my testbed code, which you should be able to plop into the Kendo Dojo:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Excel Import Test</title>
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2023.3.1010/js/jszip.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2023.3.1010/js/kendo.all.min.js"></script>
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/themes/7.0.1/default/default-ocean-blue.css">
</head>
<body>
<div id="spreadsheet"></div>
<script>
$(document).ready(function() {
$("#spreadsheet").kendoSpreadsheet({
excel: {
proxyURL: "https://demos.telerik.com/kendo-ui/service/export"
},
excelImport: function (e) {
console.log("Event: ", e);
//The excelImport event provides a promise (e.promise) that resolves when the import operation completes.
e.promise
.progress(function(e) {
console.log(kendo.format("{0:P} complete", e.progress));
})
.done(function(workbook) {
var workbook = new kendo.ooxml.Workbook(e.sender._workbook);
console.log("Workbook data:", workbook);
var sheet = workbook.options._sheets[0]; // Get the first sheet
console.log("First sheet:", sheet);
sheet.rows.forEach(function(row) {
row.cells.forEach(function(cell) {
if (cell.formula) {
var value = resolveReference(workbook, cell.formula);// takes in the workbook searches for the refered to cell
// in the outside sheet returns the value from that sheet.
cell.value = value;
delete cell.formula; // Remove the formula
}
});
});
e.preventDefault(); // Prevent the default import
this.fromJSON(workbook.toJSON()); // Load the modified workbook
}.bind(this));
}
});
//This is a work in progress, just a guess.. structure of workbook NOT correct.
function resolveReference(workbook, formula) {
// Extract the sheet name and cell reference from the formula
var match = formula.match(/='([^']+)'!([A-Z]+[0-9]+)/);
if (match) {
var sheetName = match[1];
var cellRef = match[2];
var sheet = workbook.sheets.find(s => s.name === sheetName);
if (sheet) {
var cell = sheet.range(cellRef).value();
console.log("Cell value: " + cell);
return cell;
}
}
return null;
}
});
</script>
</body>
</html>
And actually, my first problem is figuring out how to traverse the incoming Spreadsheet that is being imported into the control? Cannot find anything about that yet. How to traverse through the sheets and then the rows durring the event using Kendo 2023.3.1010? Is this possible?
George
Hello, George,
The way I understand the requirement, in the e.promise.progress function you have access to the e.sheet that is loaded. You can then use the range method with arguments (0,0, e.sheet._rows._count, e.sheet._columns._count). You can then iterate each cell with the forEachCell method to check if the cellProperties have formula, and then proceed with additional logic as per your requirement. Here's a small example for reference.
Martin,
Ok I see you have the range function working in the progress function or block... I was originally thinking I need to set the formula to undefined, and I tried but when the sheet renders... it still shows the reference in the formula
<!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>Excel Import Test</title> <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script> <script src="https://kendo.cdn.telerik.com/2023.3.1010/js/jszip.min.js"></script> <script src="https://kendo.cdn.telerik.com/2023.3.1010/js/kendo.all.min.js"></script> <link rel="stylesheet" href="https://kendo.cdn.telerik.com/themes/7.0.1/default/default-ocean-blue.css"> </head> <body> <div id="spreadsheet"></div> <script> $(document).ready(function() { $("#spreadsheet").kendoSpreadsheet({ excel: { proxyURL: "https://demos.telerik.com/kendo-ui/service/export" }, excelImport: function (e) { //The excelImport event provides a promise (e.promise) that resolves when the import operation completes. e.promise .progress(function(e) { var range = e.sheet.range(0,0, e.sheet._rows._count, e.sheet._columns._count); range.forEachCell(function(row, column, cellProperties){ if(cellProperties.formula){ cellProperties.formula = undefined;//I think if I set this to undefined... the value will show in the fx window. // why the formula still shows up in the rendered spreadsheet? console.log(cellProperties.formula);// this shows up as "undefined". hmmm but rows still recognize the reference in formula for some. // reason...missing something maybe. } }) }) .done(function () { //What to do here? }.bind(this)); } }); }); </script> </body> </html>I did discover this... seems to do what I want... but I am worried I am doing something in the wrong place? will this cause performance weirdness?
Condense Into One Sheet Soln I | Kendo UI Dojo
George
Hello, George,
The only performance issues might be coming from the amount of data you are loading in the Spreadsheet, but that issue would arise regardless of the logic in the e.promise.complete block. It seems in order to be. Let me know if you have any further questions.