How to set numFmt (styles) by multiple cells in range?

[Aftor]

Hi

I have range B2;B5:C99;E5:D99 and number format '### ### ### ###'

How to add format for cells in range?

addConditionalFormatting not work with numFmt

[Siemienik]

Hi! ExcelJS has powerfull class named Range. For that purpose what you need, this code should work:

  // @ts-ignore
  import Range from 'exceljs/lib/doc/range';

  const style = {numFmt:'### ### ### ###'};

  const range = new Range('B5','C99');

  // There is no `forEachAddress` in a index.d.ts
  // Issue tracked by: @link {https://github.com/exceljs/exceljs/issues/1681}
  // @ts-ignore 
  range.forEachAddress((address) => {worksheet.getCell(address).style = {...style} });

Source code

[skypesky]

@Aftor, It might work for you.

import Range from 'exceljs/lib/doc/range'; // import Range

const style = { numFmt: '### ### ### ###' };

const range = new Range('D1', 'D2');
range.forEachAddress((address) => {
  const cell = worksheet.getCell(address);
  cell.style = { ...style };
});

[Siemienik]

Type definition has no forEachAddress function definition.

exceljs/index.d.ts

Lines 927 to 972 in 2ab468b

	export interface Range extends Location {
	sheetName: string;
	tl: string;
	$t$l: string;
	br: string;
	$b$r: string;
	range: string;
	$range: string;
	shortRange: string;
	$shortRange: string;
	count: number;
	decode(): void;
	decode(v: Range): void;
	decode(v: string): void;
	decode(v: Location): void;
	decode(top: number, left: number, bottom: number, right: number, sheetName?: string): void;
	decode(tl: string, br: string, sheetName?: string): void;
	decode(v: [string, string]): void;
	decode(v: [string, string, string]): void;
	decode(v: [number, number, number, number]): void;
	decode(v: [number, number, number, number, string]): void;
	expand(top: number, left: number, bottom: number, right: number): void;
	expandRow(row: Row): void;
	expandToAddress(addressStr: string): void;
	toString(): string;
	intersects(other: Range): boolean;
	contains(addressStr: string): boolean;
	containsEx(address: Partial<{
	sheetName: string;
	row: number;
	col: number;
	}>): boolean;
	}

exceljs/lib/doc/range.js

Lines 248 to 254 in 2ab468b

	forEachAddress(cb) {
	for (let col = this.left; col <= this.right; col++) {
	for (let row = this.top; row <= this.bottom; row++) {
	cb(colCache.encodeAddress(row, col), row, col);
	}
	}
	}

For now, please add // @ts-ignore above.

I updated the code snippet in the origin comment with worksheet.getCell as @skypesky correctly point.

[Siemienik]

I've added issue to fix typings #1681

[skypesky]

@Siemienik Thank you for updating the file(index.d.ts). It's so cool.

[Aftor]

Thanks a lot!