CODEWITHSUNDEEP
javascriptexceloffice-jsoffice-addins
Shailesh Patel
Shailesh Patel

Reputation: 17

Optimization of code while working with range object in excel

I have recently moved for office add-in from vb.net to JavaScript and Office.js. In General, I have observed that JavaScript add-in is way too fast than to vb.net. In one of the operations, I am not able to get benefit of speed in JavaScript add-in. Maybe it is due to inadequate code that I am using!
I have one master sheet which contains a large table, first column of table is name of every other sheets of that workbook and first row of table have address of cells. Rest of data in table are values to be transferred at the sheet name defined in the first column and cell adress defined in the first row.
In coding, I am creating an array of range object for each value indicated in table and then I run context. Sync () function to restore values in every sheets. In my real-life application data in table can be 10K to 50K, I can see time taken for this operation is about approx. one minute (for 10K). In contrast of this, I can create the table (master sheet) within few seconds (5 -6 sec) only. Is there any other workaround or suggestion to reduce time?

/* global Excel, console*/
export default async function restoreData() {
  var allRollback = false;
  await Excel.run(async (context) => {
    var sheets = context.workbook.worksheets.load("items/name");
    var wsdb = context.workbook.worksheets.getItem("db");
    const arryRange = wsdb.getUsedRange();
    var addRow = 0;
    var sheetName = [];
    var rangeObj = [];
    //Get last row/column from used range
    arryRange.load(["rowCount", "columnCount", "values", "address"]);
    await context.sync();
    sheets.items.forEach((sheet) => sheetName.push(sheet.name));
    for (let iRow = 0; iRow < arryRange.rowCount; iRow++) {
      if (arryRange.values[iRow][0] == "SheetName/CellAddress") {
        addRow = iRow;
      }
      if (sheetName.indexOf(arryRange.values[iRow][0]) != -1) {
        for (let iCol = 1; iCol < arryRange.columnCount; iCol++) {
          if (arryRange.values[addRow][iCol]) {
            const sheet = context.workbook.worksheets.getItem(arryRange.values[iRow][0]);
            const range = sheet.getRange(arryRange.values[addRow][iCol]);
            range.values = arryRange.values[iRow][iCol];
            rangeObj.push(range);
          }
        }
      } else {
        // code for higlight Row in db
        console.log("Y");
      }
    }
    console.log("Range object created");

    await context.sync();
    // console.log(arryRange.rowCount);
    // console.log(arryRange.columnCount);
    console.log("done");
    // Copy a range starting at a single cell destination.
  });
  allRollback = true;
  return allRollback;
}

Upvotes: 0

Views: 179

Answers (2)

yuc014
yuc014

Reputation: 36

First, base on your statement, I assume you have a table in master sheet. This table's heading is like ["sheetName","A13","D23",...] ("A13" and "D23" are examples of cell address). In each row of this table, contain sheet's name and some values. The sheet's name may not related to a real sheet's name(not exist), and values may contain some blank. And you want to set values on other sheets based on the information given by master sheet's table.

Then I have some suggestions based on my assumptions and your code.

  1. Move unchanged value out of loops. For example, you called const sheet = context.workbook.worksheets.getItem(arryRange.values[iRow][0]);. We can move context.workbook.worksheets out of loops by define var sheets = context.workbook.worksheets and const sheet = sheets.getItem(arryRange.values[iRow][0]). Which could increase the performance.

Also some reused values like arryRange.values[iRow][0], arryRange.values[0][iCol] can be moved out of loop.

  1. Seems you use arryRange.values[addRow][iCol] only for get the address in table's heading. You can replace it by arryRange.values[0][iCol].

Below is the code I rewrite, just for reference, it may not fully satisfy what you need.

export default async function restoreData() {
  var allRollback = false;
  await Excel.run(async (context) => {
    var sheets = context.workbook.worksheets.load("items/name");
    var wsdb = context.workbook.worksheets.getItem("db");
    const arryRange = wsdb.getUsedRange();
    //var addRow = 0;
    var sheetName = [];
    var rangeObj = [];
    //Get last row/column from used range
    arryRange.load(["rowCount", "columnCount", "values", "address"]);
    await context.sync();
    sheets.items.forEach((sheet) => sheetName.push(sheet.name));

    var cellAddress, curSheetName;
    const mySheets = context.workbook.worksheets;

    for (let iRow = 0; iRow < arryRange.rowCount; iRow++) {
      curSheetName = arryRange.values[iRow][0]
      if (sheetName.indexOf(curSheetName) != -1) {
        for (let iCol = 1; iCol < arryRange.columnCount; iCol++) {
          cellAddress = arryRange.values[0][iCol];
          if (cellAddress) {
            const sheet = mySheets.getItem(curSheetName);
            const range = sheet.getRange(cellAddress);
            range.values = arryRange.values[iRow][iCol];
            rangeObj.push(range);
          }
        }
      } else {
        // code for higlight Row in db
        console.log("Y");
      }
    }
    console.log("Range object created");

    await context.sync();
    // console.log(arryRange.rowCount);
    // console.log(arryRange.columnCount);
    console.log("done");
    // Copy a range starting at a single cell destination.
  });
  allRollback = true;
  return allRollback;
}

More references:

https://learn.microsoft.com/en-us/office/dev/add-ins/excel/performance?view=excel-js-preview

https://learn.microsoft.com/en-us/office/dev/add-ins/concepts/correlated-objects-pattern

Upvotes: 1

Shailesh Patel
Shailesh Patel

Reputation: 17

with refer of you assumption, please note that master sheet was crated based on actual workbook with user selected area + deletion of empty value columns in master sheet (I,e empty cell at the adress at each sheet). sheet's name will be as same as actual sheet name unless user don’t change value in master sheet by accident. With reference to yours,

Suggestions 1) I believe that Move unchanged value out of loops will be the key to my problem. I will refabricate one range and get data comparison for changes. I believe speed will be approved drastically in best case scenario. (I will also have some worst-case scenario (less than 5% case), where I will be required to write every values of master sheet).

Suggestions 2) I am planning to have a new functionality, which may have more rows as address in, that is the reason I have I am keep looking for address rows.

Thanks for your reply.

Upvotes: 0

Related Questions