CODEWITHSUNDEEP
exceloffice-jsexcel-addins
Shailesh Patel
Shailesh Patel

Reputation: 17

How to get address of each individual cells of selected range in excel

I am working with excel office add-in in JavaScript. In this add-in I need to get individual address of each cell of selected range in an array. for example if user selects range of M1 to T31, I need to have array = ["M1", "N1", "O1" … to … "P31", "Q31", "R31", "S31", "T31”]. My program code is working and as follow. You can see that await context.sync() will be executed many times, which makes execution of this code very slower. Can I improve this code?

async function selectDataRange() {

  await Excel.run(async (context) => {
    // Get selected range
    var range = context.workbook.getSelectedRange();
    range.load(['rowCount', 'columnCount', 'cellCount']);
    await context.sync();
    // Get address of each cell
    var arrAddress = [];
    for (let iRow = 0; iRow < range.rowCount; iRow++) {
      for (let iCol = 0; iCol < range.columnCount; iCol++) {
        const addOfCell = range.getCell(iRow, iCol)
        addOfCell.load('address')
        await context.sync();
        arrAddress.push(addOfCell.address.slice(addOfCell.address.lastIndexOf('!') + 1));
      }
    }
  }
}

Upvotes: 0

Views: 930

Answers (1)

qinliuMSFT
qinliuMSFT

Reputation: 155

You could use Range.getCellProperties API. Please try the following snippet code:

async function run() {
  await Excel.run(async (context) => {
    // Get selected range
    var range = context.workbook.getSelectedRange();
    range.load(["rowCount", "columnCount", "cellCount"]);
    const propertiesToGet = range.getCellProperties({
      address: true
    }); 
    await context.sync(); 
    
    var arrAddress = [];
    for (let iRow = 0; iRow < range.rowCount; iRow++) {
      for (let iCol = 0; iCol < range.columnCount; iCol++) {
        const cellAddress = propertiesToGet.value[iRow][iCol];
        arrAddress.push(cellAddress.address.slice(cellAddress.address.lastIndexOf("!") + 1));
      }
    }

    console.log(arrAddress);
  });
}

Upvotes: 1

Related Questions