For Each Cell Loop - Excel JavaScript API - Stack Overflow

admin管理员组

文章数量:1026989

In the following VBA code, I debug.print each cell value. Using the data in the picture, my answer appears like this.

Sub loopAndDebugPrintEachCell()
Dim cl As Object
With Sheets("Sheet1")
    For Each cl In .Range("A1:D2")
        Debug.Print cl.Value
    Next cl
End With
End Sub

I am trying to reiterate this code in JavaScript and I figured out a solution, but I am not sure that this is the most efficient way. I am getting the exact same answer, but is there a more advantageous way to loop through a range?

  loopAndConsoleLogEachCell = async () => {
    try {
      await Excel.run(async context => {
        const range = context.workbook.worksheets.getItem("Sheet1").getRange("A1:D2");
        range.load(["columnCount", "rowCount"]);
        await context.sync();
        let i = 0;
        let rowC = 0;
        do {
          let loopRng = range.getAbsoluteResizedRange(1, 1).getOffsetRange(rowC, i).load(["values"]);
          await context.sync();
          console.log(`${loopRng.values} `);
          let rangeColCount = Math.floor(((range.columnCount) / 2) + 1);
          if (rowC < (range.rowCount) && i == (rangeColCount)) {
            rowC++;
            i = 0;
          } else {
            i++;
          }
        }
        while (rowC < range.rowCount && i < range.columnCount);
      });
    } catch (error) {
      console.error(error);
    }
  };

In the following VBA code, I debug.print each cell value. Using the data in the picture, my answer appears like this.

Sub loopAndDebugPrintEachCell()
Dim cl As Object
With Sheets("Sheet1")
    For Each cl In .Range("A1:D2")
        Debug.Print cl.Value
    Next cl
End With
End Sub

I am trying to reiterate this code in JavaScript and I figured out a solution, but I am not sure that this is the most efficient way. I am getting the exact same answer, but is there a more advantageous way to loop through a range?

  loopAndConsoleLogEachCell = async () => {
    try {
      await Excel.run(async context => {
        const range = context.workbook.worksheets.getItem("Sheet1").getRange("A1:D2");
        range.load(["columnCount", "rowCount"]);
        await context.sync();
        let i = 0;
        let rowC = 0;
        do {
          let loopRng = range.getAbsoluteResizedRange(1, 1).getOffsetRange(rowC, i).load(["values"]);
          await context.sync();
          console.log(`${loopRng.values} `);
          let rangeColCount = Math.floor(((range.columnCount) / 2) + 1);
          if (rowC < (range.rowCount) && i == (rangeColCount)) {
            rowC++;
            i = 0;
          } else {
            i++;
          }
        }
        while (rowC < range.rowCount && i < range.columnCount);
      });
    } catch (error) {
      console.error(error);
    }
  };

• javascript
• excel
• office-js
• office-addins

Share Improve this question Follow edited Jan 26, 2022 at 19:36 FreeSoftwareServers 2,85266 gold badges4040 silver badges7474 bronze badges asked Apr 22, 2020 at 16:34 EthanEthan 84866 silver badges2323 bronze badges 1

• 3 In addition to @RaymondLu answer, don't have a context.sync in a loop. For details about how to avoid this, see learn.microsoft./en-us/office/dev/add-ins/concepts/…. – Rick Kirkham Commented Apr 23, 2020 at 1:08

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 3

If you want to print the value of each cell in the range, you could use range.values API

Here is the sample code for getting the values for the range

  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B2:E6");
    range.load("text");
    range.load("values");

    await context.sync();
    console.log(range.values);
    //console.log(JSON.stringify(range.text, null, 4));
  });

The document can be found https://learn.microsoft./en-us/javascript/api/excel/excel.range?view=excel-js-preview#values

Update:

My original answer searched the entire range starting at row/col 0/0, but I quickly realized a few "gotchas" based on the code (copied from MS somewhere...).

Here is an example where I'm only searching in a column and I'm not starting at row 0.

A few notes:

• I changed r to i because r makes you think row but really, its the index of the range which is zero indexed, but could start at row 42.

• So, when accessing values for B2 inside of say Range("B2:B5") your value starts with .values[0][0].

• To get the value of Cell C2 inside Range("B2:C5") your value would be .values[0][1]. (0 = first row of RANGE and 1 = second col in range).

• Using foo.length inside loop declaration re-calculates the variable each iteration of the loop, for readability and efficiency, declare your start end variables before loop.

        var ws = context.workbook.worksheets.getActiveWorksheet();
        var rng = ws.getRange("B2:B5");
  
        rng .load(["rowCount", "values"]);
        return context.sync()
            .then(function () {
                var RowCount = rng.rowCount  
                for (var i = 0; i < RowCount; ++i) {
                    var rcellval = rng .values[i][0]
                    console.log(rcellval)
                }
            })
  

---

Here is an example I used to iterate over a range, only using one context.sync() to find a cell match via RegEx which should illustrate how to use a For Each Cell loop. Keep in mind I am new to JS as well, but I believe this is proper method.

function Bool_RegEx(rexp, findstr) {
    return rexp.test(findstr)
}

---

function Make_FakeData(context) {
    var ws = context.workbook.worksheets.getActiveWorksheet();
    var rng = ws.getRange("A1:D5");
    rng.values = "TESTDATA";

    var rng = ws.getRange("A5");
    rng.values = "AB12345678"
    return context;
}

---

export async function helloworld(event) {
    try {
        await Excel.run(async (context) => {
            //Start Func
            Make_FakeData(context);

            var ws = context.workbook.worksheets.getActiveWorksheet();
            var rng = ws.getRange("A1:D5");
            rng.load(["columnCount", "rowCount", "values"]);

            let rexp = new RegExp('[a-z]{2}[0-9]{8}', 'i');
            return context.sync()
                .then(function () {
                    for (var r = 0; r < rng.rowCount; ++r) {
                        for (var c = 0; c < rng.columnCount; ++c) { 
                        var rcellval = rng.values[r][c];
                        console.log("r = " + r);
                        console.log("c = " + c);
                        console.log("rcellval = " + rcellval);
                        if (Bool_RegEx(rexp, rcellval) === true) {
                            console.log("RegEx Match in Row r" + r + ", Col " + c);
                            console.log("RegEx Match rcellval = " + rcellval);
                        }
                    }
                }

                })

            //End Func
            await context.sync();
        });
    } catch (error) {
        console.error(error);
    }
    event.pleted();
}

VBA allows you to access every cell on the iteration using the for loop. What I did in TypeScript to replicate this for a range was loading the properties of columns and rows and storing the values in variables:

await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
const range = context.workbook.getSelectedRange();
range.load(["columnIndex", "columnCount", "rowCount", "rowIndex"]);
let col_1 = range.columnIndex; //first column index
let nc = range.columnCount; //number of columns
let row_1 = range.rowIndex; //first row
let nr = range.rowCount; //number of rows

Then you can access each cell with two for loops:

for (let r = 0; r < nr; r++) {
      for (let c = 0; c < nc; c++) {
         cell = sheet.getCell(r + row_1, c + col_1);
         cell.load(["address", "values", "formulasR1C1", "format"]);
         await context.sync();
         let cell_add = cell.address;
         let cell_val = cell.values[0][0]; //values of a cell/range is always a matrix
         if (cell_val <= 5) {
         //do something
         }
      }
  };

The code might not be the best, but it worked for me.

Hope this helps.

In the following VBA code, I debug.print each cell value. Using the data in the picture, my answer appears like this.

Sub loopAndDebugPrintEachCell()
Dim cl As Object
With Sheets("Sheet1")
    For Each cl In .Range("A1:D2")
        Debug.Print cl.Value
    Next cl
End With
End Sub

I am trying to reiterate this code in JavaScript and I figured out a solution, but I am not sure that this is the most efficient way. I am getting the exact same answer, but is there a more advantageous way to loop through a range?

  loopAndConsoleLogEachCell = async () => {
    try {
      await Excel.run(async context => {
        const range = context.workbook.worksheets.getItem("Sheet1").getRange("A1:D2");
        range.load(["columnCount", "rowCount"]);
        await context.sync();
        let i = 0;
        let rowC = 0;
        do {
          let loopRng = range.getAbsoluteResizedRange(1, 1).getOffsetRange(rowC, i).load(["values"]);
          await context.sync();
          console.log(`${loopRng.values} `);
          let rangeColCount = Math.floor(((range.columnCount) / 2) + 1);
          if (rowC < (range.rowCount) && i == (rangeColCount)) {
            rowC++;
            i = 0;
          } else {
            i++;
          }
        }
        while (rowC < range.rowCount && i < range.columnCount);
      });
    } catch (error) {
      console.error(error);
    }
  };

In the following VBA code, I debug.print each cell value. Using the data in the picture, my answer appears like this.

Sub loopAndDebugPrintEachCell()
Dim cl As Object
With Sheets("Sheet1")
    For Each cl In .Range("A1:D2")
        Debug.Print cl.Value
    Next cl
End With
End Sub

I am trying to reiterate this code in JavaScript and I figured out a solution, but I am not sure that this is the most efficient way. I am getting the exact same answer, but is there a more advantageous way to loop through a range?

  loopAndConsoleLogEachCell = async () => {
    try {
      await Excel.run(async context => {
        const range = context.workbook.worksheets.getItem("Sheet1").getRange("A1:D2");
        range.load(["columnCount", "rowCount"]);
        await context.sync();
        let i = 0;
        let rowC = 0;
        do {
          let loopRng = range.getAbsoluteResizedRange(1, 1).getOffsetRange(rowC, i).load(["values"]);
          await context.sync();
          console.log(`${loopRng.values} `);
          let rangeColCount = Math.floor(((range.columnCount) / 2) + 1);
          if (rowC < (range.rowCount) && i == (rangeColCount)) {
            rowC++;
            i = 0;
          } else {
            i++;
          }
        }
        while (rowC < range.rowCount && i < range.columnCount);
      });
    } catch (error) {
      console.error(error);
    }
  };

• javascript
• excel
• office-js
• office-addins

Share Improve this question Follow edited Jan 26, 2022 at 19:36 FreeSoftwareServers 2,85266 gold badges4040 silver badges7474 bronze badges asked Apr 22, 2020 at 16:34 EthanEthan 84866 silver badges2323 bronze badges 1

• 3 In addition to @RaymondLu answer, don't have a context.sync in a loop. For details about how to avoid this, see learn.microsoft./en-us/office/dev/add-ins/concepts/…. – Rick Kirkham Commented Apr 23, 2020 at 1:08

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 3

If you want to print the value of each cell in the range, you could use range.values API

Here is the sample code for getting the values for the range

  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B2:E6");
    range.load("text");
    range.load("values");

    await context.sync();
    console.log(range.values);
    //console.log(JSON.stringify(range.text, null, 4));
  });

The document can be found https://learn.microsoft./en-us/javascript/api/excel/excel.range?view=excel-js-preview#values

Update:

My original answer searched the entire range starting at row/col 0/0, but I quickly realized a few "gotchas" based on the code (copied from MS somewhere...).

Here is an example where I'm only searching in a column and I'm not starting at row 0.

A few notes:

• I changed r to i because r makes you think row but really, its the index of the range which is zero indexed, but could start at row 42.

• So, when accessing values for B2 inside of say Range("B2:B5") your value starts with .values[0][0].

• To get the value of Cell C2 inside Range("B2:C5") your value would be .values[0][1]. (0 = first row of RANGE and 1 = second col in range).

• Using foo.length inside loop declaration re-calculates the variable each iteration of the loop, for readability and efficiency, declare your start end variables before loop.

        var ws = context.workbook.worksheets.getActiveWorksheet();
        var rng = ws.getRange("B2:B5");
  
        rng .load(["rowCount", "values"]);
        return context.sync()
            .then(function () {
                var RowCount = rng.rowCount  
                for (var i = 0; i < RowCount; ++i) {
                    var rcellval = rng .values[i][0]
                    console.log(rcellval)
                }
            })
  

---

Here is an example I used to iterate over a range, only using one context.sync() to find a cell match via RegEx which should illustrate how to use a For Each Cell loop. Keep in mind I am new to JS as well, but I believe this is proper method.

function Bool_RegEx(rexp, findstr) {
    return rexp.test(findstr)
}

---

function Make_FakeData(context) {
    var ws = context.workbook.worksheets.getActiveWorksheet();
    var rng = ws.getRange("A1:D5");
    rng.values = "TESTDATA";

    var rng = ws.getRange("A5");
    rng.values = "AB12345678"
    return context;
}

---

export async function helloworld(event) {
    try {
        await Excel.run(async (context) => {
            //Start Func
            Make_FakeData(context);

            var ws = context.workbook.worksheets.getActiveWorksheet();
            var rng = ws.getRange("A1:D5");
            rng.load(["columnCount", "rowCount", "values"]);

            let rexp = new RegExp('[a-z]{2}[0-9]{8}', 'i');
            return context.sync()
                .then(function () {
                    for (var r = 0; r < rng.rowCount; ++r) {
                        for (var c = 0; c < rng.columnCount; ++c) { 
                        var rcellval = rng.values[r][c];
                        console.log("r = " + r);
                        console.log("c = " + c);
                        console.log("rcellval = " + rcellval);
                        if (Bool_RegEx(rexp, rcellval) === true) {
                            console.log("RegEx Match in Row r" + r + ", Col " + c);
                            console.log("RegEx Match rcellval = " + rcellval);
                        }
                    }
                }

                })

            //End Func
            await context.sync();
        });
    } catch (error) {
        console.error(error);
    }
    event.pleted();
}

VBA allows you to access every cell on the iteration using the for loop. What I did in TypeScript to replicate this for a range was loading the properties of columns and rows and storing the values in variables:

await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
const range = context.workbook.getSelectedRange();
range.load(["columnIndex", "columnCount", "rowCount", "rowIndex"]);
let col_1 = range.columnIndex; //first column index
let nc = range.columnCount; //number of columns
let row_1 = range.rowIndex; //first row
let nr = range.rowCount; //number of rows

Then you can access each cell with two for loops:

for (let r = 0; r < nr; r++) {
      for (let c = 0; c < nc; c++) {
         cell = sheet.getCell(r + row_1, c + col_1);
         cell.load(["address", "values", "formulasR1C1", "format"]);
         await context.sync();
         let cell_add = cell.address;
         let cell_val = cell.values[0][0]; //values of a cell/range is always a matrix
         if (cell_val <= 5) {
         //do something
         }
      }
  };

The code might not be the best, but it worked for me.

Hope this helps.

本文标签： For Each Cell LoopExcel JavaScript APIStack Overflow