How do I select a range of cells? #2508

Same need here. Iteration may work, but overcomplicates the code.
Still, I used this, which worked nicely
[1,2,3].map(x=>worksheet.getCell(worksheet.rowCount,x).fill= {... fill object here ...})

0 replies

kendallroth · 2019-12-12T17:08:52Z

kendallroth
Dec 12, 2019

I'm fairly surprised that this hasn't been a more commonly requested feature; is it something that we could see support for in the near future?

Here's what I've come up with in the mean time, not quite fully error-proof (missing a few checks) but close enough:

/**
 * Select a range of cells
 * @param  {string}   startCell - Range start cell (top-left)
 * @param  {string}   endCell   - Range end cell (bottom-right)
 * @return {Object[]}           - Selected cells
 */
const selectRange = (sheet, startCell, endCell) => {
  const [endCellColumn, endRow] = endCell.split(":", 2);
  const [startCellColumn, startRow] = startCell.split(":", 2);

  let endColumn = sheet.getColumn(endCellColumn);
  let startColumn = sheet.getColumn(startCellColumn);

  if (!endColumn) throw new Error("End column not found");
  if (!startColumn) throw new Error("Start column not found");

  endColumn = endColumn._number;
  startColumn = startColumn._number;

  const cells = [];
  for (let y = startRow; y <= endRow; y++) {
    const row = sheet.getRow(y);

    for (let x = startColumn; x <= endColumn; x++) {
      cells.push(row.getCell(x));
    }
  }

  return cells;
};

Sample Usage

const lastRow = worksheet.lastRow._number;
const row = selectRange(worksheet, `startColumnKey:${lastRow}`,  `endColumnKey:${lastRow + 4}`);

row.forEach(cell => {
  // TODO: Do something with each cell
});

0 replies

Siemienik · 2020-02-17T22:40:51Z

Siemienik
Feb 17, 2020
Maintainer

@kendallroth is will be really nice to show PR created by You 👍 If only want you to create one, please add me as a pending reviewer

0 replies

RizkiNfs · 2020-09-27T11:41:52Z

RizkiNfs
Sep 27, 2020

@kendallroth post work as expected for me, but i have to parse startRow and endRow to integer like so
for (let y = parseInt(startRow); y <= parseInt(endRow); y++) {

0 replies

kendallroth · 2020-09-28T02:30:54Z

kendallroth
Sep 28, 2020

@Siemienik I have no idea why I never responded to your comment previously (although I was overseas for most of this year...), my apologies for that! Our use case for this was relatively minor so we never expanded upon this; for example, we didn't run into what @RizkiNfs mentioned above (a good observation). I sadly do not have the necessary context to create a PR at this point (or to determine if the approach taken matches the repo, such as throwing errors...). However, I do hope this example can continue to be useful to others in the future!

0 replies

Bvc0326 · 2020-10-30T16:43:33Z

Bvc0326
Oct 30, 2020

This is the first time I am using excelJs and everything looks good but I am stuck with one problem.

I did worksheet protect first that means none of the cells are editable. That’s good.
But now I want to unlock cells from A2 to G9999 that means from second row first column to 9999th row in G column. Can someone help me how to do that?

Is there any function like getRange or something ?

0 replies

darron1217 · 2020-10-31T05:43:00Z

darron1217
Oct 31, 2020

@Bvc0326
I solved this issue by getRows function on worksheet obj.
But you must be sure that your exceljs version should be 4.2.0

Here's example

  ws.getRows(1, 9999).forEach((row) => {
    for(let i = 1; i <= 7; i++) {
      row.getCell(i).value = 'hello';
    }
  });

0 replies

brnyza · 2022-01-10T20:14:39Z

brnyza
Jan 10, 2022

I'm fairly surprised that this hasn't been a more commonly requested feature; is it something that we could see support for in the near future?

Here's what I've come up with in the mean time, not quite fully error-proof (missing a few checks) but close enough:

/**
 * Select a range of cells
 * @param  {string}   startCell - Range start cell (top-left)
 * @param  {string}   endCell   - Range end cell (bottom-right)
 * @return {Object[]}           - Selected cells
 */
const selectRange = (sheet, startCell, endCell) => {
  const [endCellColumn, endRow] = endCell.split(":", 2);
  const [startCellColumn, startRow] = startCell.split(":", 2);

  let endColumn = sheet.getColumn(endCellColumn);
  let startColumn = sheet.getColumn(startCellColumn);

  if (!endColumn) throw new Error("End column not found");
  if (!startColumn) throw new Error("Start column not found");

  endColumn = endColumn._number;
  startColumn = startColumn._number;

  const cells = [];
  for (let y = startRow; y <= endRow; y++) {
    const row = sheet.getRow(y);

    for (let x = startColumn; x <= endColumn; x++) {
      cells.push(row.getCell(x));
    }
  }

  return cells;
};

Sample Usage

const lastRow = worksheet.lastRow._number;
const row = selectRange(worksheet, `startColumnKey:${lastRow}`,  `endColumnKey:${lastRow + 4}`);

row.forEach(cell => {
  // TODO: Do something with each cell
});

Thank you for the code, we just did some change using param like "A1:C4" instead of working with two params

import ExcelJS from "exceljs"

export const selectRange = (sheet: ExcelJS.Worksheet, rangeCell: string) => {
  const [startCell, endCell] = rangeCell.split(":")

  const [endCellColumn, endRow] = endCell.match(/[a-z]+|[^a-z]+/gi) as string[]
  const [startCellColumn, startRow] = startCell.match(
    /[a-z]+|[^a-z]+/gi
  ) as string[]

  let endColumn = sheet.getColumn(endCellColumn)
  let startColumn = sheet.getColumn(startCellColumn)

  if (!endColumn) throw new Error("End column not found")
  if (!startColumn) throw new Error("Start column not found")

  const endColumnNumber = endColumn.number
  const startColumnNumber = startColumn.number

  const cells = []
  for (let y = parseInt(startRow); y <= parseInt(endRow); y++) {
    const row = sheet.getRow(y)

    for (let x = startColumnNumber; x <= endColumnNumber; x++) {
      cells.push(row.getCell(x))
    }
  }

  return cells
}

const lastRow = worksheet.lastRow._number;
const row = selectRange(worksheet, `A${lastRow}:C${lastRow + 4}`);
 
row.forEach(cell => {
   // TODO: Do something with each cell
});

0 replies

Hedgineering · 2022-01-29T03:58:25Z

Hedgineering
Jan 29, 2022

Just a small adjustment to brnyza's code in case unexpected cell endpoints are given (like the bottom left and top right of the range to be selected instead of the top left and bottom right).

This is a bit easier to use with peace of mind in my opinion.

Edit: This only works as long as you're within the columns A through Z, it fails when you try to select something like Z1:AB120 for example because my code here recognizes AB as less than Z alphabetically, and incorrectly recalculates. If someone knows a fix for that I'd appreciate it! If you want something that works for all cells, use brnyza's code but just be sure to only select starting from the top left to the bottom right.

/**
 *
 * @param {Exceljs.Worksheet} sheet
 * @param {String} rangeCell
 * @returns Cell[] References
 */
const selectRange = (sheet, rangeCell) => {
  let [startCell, endCell] = rangeCell.split(':')

  // Recalculate in case bottom left and top right are given
  if (endCell < startCell) {
    let temp = endCell
    endCell = startCell
    startCell = temp
  }

  let [endCellColumn, endRow] = endCell.match(/[a-z]+|[^a-z]+/gi)
  let [startCellColumn, startRow] = startCell.match(/[a-z]+|[^a-z]+/gi)

  // Recalculate in case bottom left and top right are given
  if (endCellColumn < startCellColumn) {
    let temp = endCellColumn
    endCellColumn = startCellColumn
    startCellColumn = temp
  }

  // Recalculate in case bottom left and top right are given
  if (endRow < startRow) {
    let temp = endRow
    endRow = startRow
    startRow = temp
  }

  let endColumn = sheet.getColumn(endCellColumn)
  let startColumn = sheet.getColumn(startCellColumn)

  if (!endColumn) throw new Error('End column not found')
  if (!startColumn) throw new Error('Start column not found')

  const endColumnNumber = endColumn.number
  const startColumnNumber = startColumn.number

  const cells = []
  for (let y = parseInt(startRow); y <= parseInt(endRow); y++) {
    const row = sheet.getRow(y)

    for (let x = startColumnNumber; x <= endColumnNumber; x++) {
      cells.push(row.getCell(x))
    }
  }

  return cells
}

0 replies

BB-BenBridges · 2023-06-13T15:25:45Z

BB-BenBridges
Jun 13, 2023

Any update on this?

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

How do I select a range of cells? #2508

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Replies: 15 comments

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

Select a reply

How do I select a range of cells? #2508

Replies: 15 comments

JacobWeisenburger Oct 23, 2017 Author

JacobWeisenburger Jan 9, 2018 Author

Siemienik Dec 1, 2018 Maintainer

Siemienik Feb 17, 2020 Maintainer

JacobWeisenburger
Oct 23, 2017
Author

JacobWeisenburger
Jan 9, 2018
Author

Siemienik
Dec 1, 2018
Maintainer

Siemienik
Feb 17, 2020
Maintainer