Currently, I am delving into the new office js API to transition existing Excel add-ins to utilize this innovative technology.
While it is simple to fetch an array of values from a complete range by queuing a single load on the context, obtaining cell formatting does not seem as straightforward. If cells in the range have different formats, the returned value for the range becomes 'undefined'.
To address this issue, my approach involves queuing a load operation on each individual cell within the range. For instance, the following function retrieves the fill color for every cell in a range:
function readFormats() {
Excel.run(function (ctx) {
var cells = [];
//Firstly, determine the size of the range for utilization in the subsequent loop
var myRange = ctx.workbook.getSelectedRange().load(["rowCount", "columnCount"]);
return ctx.sync()
.then(function () {
//Iterate through each cell and queue a load on the context for fill colour
for (var r = 0; r < myRange.rowCount; ++r)
for (var c = 0; c < myRange.columnCount; ++c)
cells.push(myRange.getCell(r, c).load("format/fill"));
})
.then(ctx.sync)
.then(function () {
//Implement relevant actions with the fill color of cells in the array here
})
})
.then(function () {
console.log("Formats successfully extracted");
})
.catch(function (error) {
console.log("Encountered an error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug details: " + JSON.stringify(error.debugInfo));
}
});
}
The code performs as anticipated but operates at a slow pace. For example, processing a range of 10,000 cells consumes approximately 12 seconds, while a 20k cell range requires around 45 seconds to execute. On tackling a range comprising 50k cells, the async callback failed to trigger at all.
Is there a more efficient and superior method to achieve this task?