CODEWITHSUNDEEP
javascriptexceloffice-js
CodingAnxiety
CodingAnxiety

Reputation: 206

Getting the width and height of every cell in a selected range

To give you a bit of context, I am trying to retrieve the width and height properties of each cell in a selected range. For this I am using the Office Javascript API and so far the only way I can think of is by looping over every cell in the selected range and retrieving said properties for each.

Assuming selectedRange is the user's selection and the appropriate properties have been loaded beforehand, this is what I would have:

    for (let r = 0; r < selectedRange.rowCount; r++) {
      console.log(selectedRange.getRow(r).height);
      for (let c = 0; c < selectedRange.columnCount; c++) {
        console.log(selectedRange.getColumn(c).width);
      }
    }

The problem however, is that the width and height properties for each column and row are not loaded and correct me if I'm wrong, but they would have to be loaded inside the loop for each column and row. That would in turn mean calling context.sync() quite a few times inside the loop which is a very bad idea according to the official documentation.

So, is there another way of doing this, which wouldn't involve calling context.sync() so often?

I am still quite new to Office Js, so any help is greatly appreciated. Thank you all!

Upvotes: 0

Views: 838

Answers (3)

Rick Kirkham
Rick Kirkham

Reputation: 9769

Yes. There is a way to do this without calling context.sync inside a loop. You need to use the split loop pattern. For details and examples, see Avoid using the context.sync method in loops and the Stack answers that it links to.

Upvotes: 1

MandytMSFT
MandytMSFT

Reputation: 89

One possible way is to keep all the ranges (cell) and load them all in the same context.sync(), then output the height and Width after that. You must call range.untrack to save memory.

https://learn.microsoft.com/en-us/javascript/api/excel/excel.range?view=excel-js-preview#untrack--

Besides, Your code above can do some optimization, It don't need to be n*m, n+m is enough.

let selectedRange = context.workbook.getSelectedRange();

selectedRange.load("rowCount,columnCount");
await context.sync();

for (let r = 0; r < selectedRange.rowCount; r++) {
  let row = selectedRange.getRow(r);
  row.load("height");
  await context.sync();

  console.log(row.height);
  }

for (let c = 0; c < selectedRange.columnCount; c++) {
  let column = selectedRange.getColumn(c);
  column.load("width");
  await context.sync();
  console.log(column.width);
}

Upvotes: 0

Raymond Lu
Raymond Lu

Reputation: 2236

If I understand your scenario correctly you would like to access each cell's hight and width. So you may try range.format.rowHight and range.format.columnWidth

Therefore you could use range.getCellProperties which will return a 2d array for you with width and hight information for each cell of this range.

Here is the document for range.getCellProperties https://learn.microsoft.com/en-us/javascript/api/excel/excel.range?view=excel-js-preview#getcellproperties-cellpropertiesloadoptions-

Upvotes: 0

Related Questions