0
# Cell and Range Operations
1

2
Low-level utilities for working with individual cells, cell addresses, and ranges. These functions provide precise control over cell manipulation, address conversion, and value formatting.
3

4
## Capabilities
5

6
### Cell Address Encoding
7

8
Convert cell coordinates to Excel A1 notation for display and reference purposes.
9

10
```typescript { .api }
11
/**
12
 * Convert cell address object to A1 notation string
13
 * @param cell - Cell address with row and column numbers
14
 * @returns A1 notation string (e.g., "A1", "B5", "AA100")
15
 */
16
function encode_cell(cell: CellAddress): string;
17

18
/**
19
 * Convert row number to A1 notation
20
 * @param row - Row number (0-indexed)
21
 * @returns Row string (e.g., "1", "5", "100")
22
 */
23
function encode_row(row: number): string;
24

25
/**
26
 * Convert column number to A1 notation
27
 * @param col - Column number (0-indexed)
28
 * @returns Column string (e.g., "A", "B", "AA")
29
 */
30
function encode_col(col: number): string;
31

32
/**
33
 * Convert range object to A1 notation string
34
 * @param range - Range object with start and end addresses
35
 * @returns A1 range string (e.g., "A1:C5", "B2:D10")
36
 */
37
function encode_range(range: Range): string;
38

39
/**
40
 * Convert range coordinates to A1 notation string
41
 * @param s - Start cell address
42
 * @param e - End cell address  
43
 * @returns A1 range string
44
 */
45
function encode_range(s: CellAddress, e: CellAddress): string;
46
```
47

48
**Usage Examples:**
49

50
```typescript
51
import { utils } from "xlsx";
52

53
// Encode cell addresses
54
const cellA1 = utils.encode_cell({ r: 0, c: 0 });        // "A1"
55
const cellB5 = utils.encode_cell({ r: 4, c: 1 });        // "B5"
56
const cellAA100 = utils.encode_cell({ r: 99, c: 26 });   // "AA100"
57

58
// Encode rows and columns separately
59
const row10 = utils.encode_row(9);    // "10" (0-indexed input)
60
const colZ = utils.encode_col(25);    // "Z" (0-indexed input)
61

62
// Encode ranges
63
const range1 = utils.encode_range(
64
  { r: 0, c: 0 },     // A1
65
  { r: 4, c: 2 }      // C5
66
);  // "A1:C5"
67

68
const rangeObj = {
69
  s: { r: 1, c: 1 },  // B2
70
  e: { r: 9, c: 3 }   // D10
71
};
72
const range2 = utils.encode_range(rangeObj);  // "B2:D10"
73

74
// Dynamic range creation
75
function createDynamicRange(startRow: number, startCol: number, numRows: number, numCols: number) {
76
  return utils.encode_range(
77
    { r: startRow, c: startCol },
78
    { r: startRow + numRows - 1, c: startCol + numCols - 1 }
79
  );
80
}
81
const dynamicRange = createDynamicRange(0, 0, 10, 5);  // "A1:E10"
82
```
83

84
### Cell Address Decoding
85

86
Convert Excel A1 notation strings back to numeric coordinates for programmatic access.
87

88
```typescript { .api }
89
/**
90
 * Convert A1 notation string to cell address object
91
 * @param address - A1 notation string (e.g., "A1", "B5", "AA100")
92
 * @returns CellAddress object with row and column numbers
93
 */
94
function decode_cell(address: string): CellAddress;
95

96
/**
97
 * Convert A1 row string to number
98
 * @param row - Row string (e.g., "1", "5", "100")
99
 * @returns Row number (0-indexed)
100
 */
101
function decode_row(row: string): number;
102

103
/**
104
 * Convert A1 column string to number
105
 * @param col - Column string (e.g., "A", "B", "AA")
106
 * @returns Column number (0-indexed)
107
 */
108
function decode_col(col: string): number;
109

110
/**
111
 * Convert A1 range string to range object
112
 * @param range - A1 range string (e.g., "A1:C5", "B2:D10")
113
 * @returns Range object with start and end addresses
114
 */
115
function decode_range(range: string): Range;
116
```
117

118
**Usage Examples:**
119

120
```typescript
121
import { utils } from "xlsx";
122

123
// Decode cell addresses
124
const addr1 = utils.decode_cell("A1");        // { r: 0, c: 0 }
125
const addr2 = utils.decode_cell("B5");        // { r: 4, c: 1 }
126
const addr3 = utils.decode_cell("AA100");     // { r: 99, c: 26 }
127

128
// Decode rows and columns
129
const rowNum = utils.decode_row("10");        // 9 (0-indexed output)
130
const colNum = utils.decode_col("Z");         // 25 (0-indexed output)
131

132
// Decode ranges
133
const range1 = utils.decode_range("A1:C5");
134
// { s: { r: 0, c: 0 }, e: { r: 4, c: 2 } }
135

136
const range2 = utils.decode_range("B2:D10");
137
// { s: { r: 1, c: 1 }, e: { r: 9, c: 3 } }
138

139
// Use decoded addresses for cell access
140
const workbook = utils.book_new();
141
const worksheet = utils.aoa_to_sheet([["A", "B", "C"], [1, 2, 3]]);
142

143
const cellAddr = utils.decode_cell("B2");
144
const cellValue = worksheet[utils.encode_cell(cellAddr)];
145
console.log(cellValue?.v);  // 2
146

147
// Range iteration
148
const range = utils.decode_range("A1:C2");
149
for (let r = range.s.r; r <= range.e.r; r++) {
150
  for (let c = range.s.c; c <= range.e.c; c++) {
151
    const cellRef = utils.encode_cell({ r, c });
152
    const cell = worksheet[cellRef];
153
    console.log(`${cellRef}: ${cell?.v}`);
154
  }
155
}
156
```
157

158
### Cell Formatting
159

160
Format and display cell values according to number formats and display preferences.
161

162
```typescript { .api }
163
/**
164
 * Format cell value for display
165
 * @param cell - Cell object to format
166
 * @param v - Optional value override
167
 * @param opts - Formatting options
168
 * @returns Formatted string representation
169
 */
170
function format_cell(cell: CellObject, v?: any, opts?: any): string;
171

172
/**
173
 * Set number format for a cell
174
 * @param cell - Cell object to modify
175
 * @param fmt - Number format string or format index
176
 * @returns Modified cell object
177
 */
178
function cell_set_number_format(cell: CellObject, fmt: string | number): CellObject;
179
```
180

181
**Usage Examples:**
182

183
```typescript
184
import { utils } from "xlsx";
185

186
// Create sample cells
187
const numberCell: CellObject = { v: 1234.567, t: "n" };
188
const dateCell: CellObject = { v: new Date("2023-06-15"), t: "d" };
189
const percentCell: CellObject = { v: 0.1234, t: "n" };
190

191
// Basic formatting
192
const formatted1 = utils.format_cell(numberCell);
193
console.log(formatted1);  // "1234.567"
194

195
// Set number formats
196
const currencyCell = utils.cell_set_number_format(numberCell, "$#,##0.00");
197
const formattedCurrency = utils.format_cell(currencyCell);
198
console.log(formattedCurrency);  // "$1,234.57"
199

200
const percentFormatted = utils.cell_set_number_format(percentCell, "0.00%");
201
const formattedPercent = utils.format_cell(percentFormatted);
202
console.log(formattedPercent);  // "12.34%"
203

204
const dateFormatted = utils.cell_set_number_format(dateCell, "mm/dd/yyyy");
205
const formattedDate = utils.format_cell(dateFormatted);
206
console.log(formattedDate);  // "06/15/2023"
207

208
// Custom formatting with built-in format codes
209
const scientificCell = utils.cell_set_number_format(numberCell, "0.00E+00");
210
const timeCell = utils.cell_set_number_format(dateCell, "hh:mm:ss");
211
const fractionCell = utils.cell_set_number_format(numberCell, "# ?/?");
212
```
213

214
### Cell Links and Hyperlinks
215

216
Add hyperlinks and internal links to cells for navigation and external references.
217

218
```typescript { .api }
219
/**
220
 * Set external hyperlink for a cell
221
 * @param cell - Cell object to modify
222
 * @param target - URL or external reference
223
 * @param tooltip - Optional tooltip text
224
 * @returns Modified cell object with hyperlink
225
 */
226
function cell_set_hyperlink(cell: CellObject, target: string, tooltip?: string): CellObject;
227

228
/**
229
 * Set internal workbook link for a cell
230
 * @param cell - Cell object to modify
231
 * @param target - Internal reference (sheet name, cell address)
232
 * @param tooltip - Optional tooltip text
233
 * @returns Modified cell object with internal link
234
 */
235
function cell_set_internal_link(cell: CellObject, target: string, tooltip?: string): CellObject;
236
```
237

238
**Usage Examples:**
239

240
```typescript
241
import { utils } from "xlsx";
242

243
// Create cells with text
244
const urlCell: CellObject = { v: "Visit Google", t: "s" };
245
const emailCell: CellObject = { v: "Contact Us", t: "s" };
246
const internalCell: CellObject = { v: "Go to Summary", t: "s" };
247

248
// Add external hyperlinks
249
const webLink = utils.cell_set_hyperlink(urlCell, "https://www.google.com", "Google Search");
250
const mailLink = utils.cell_set_hyperlink(emailCell, "mailto:support@example.com", "Send Email");
251

252
// Add internal links
253
const sheetLink = utils.cell_set_internal_link(internalCell, "Summary!A1", "Go to Summary Sheet");
254
const rangeLink = utils.cell_set_internal_link(internalCell, "Data!A1:C10", "View Data Range");
255

256
// Apply to worksheet
257
const worksheet = utils.aoa_to_sheet([
258
  ["Links Example"],
259
  ["Visit Google", "Contact Us", "Go to Summary"]
260
]);
261

262
// Set hyperlinks on specific cells
263
worksheet["A2"] = webLink;
264
worksheet["B2"] = mailLink;
265
worksheet["C2"] = sheetLink;
266

267
// Create workbook with linked worksheet
268
const workbook = utils.book_new();
269
utils.book_append_sheet(workbook, worksheet, "Links");
270

271
// Add summary sheet for internal link
272
const summarySheet = utils.aoa_to_sheet([["Summary Data"], ["Item 1"], ["Item 2"]]);
273
utils.book_append_sheet(workbook, summarySheet, "Summary");
274
```
275

276
### Cell Comments
277

278
Add comments and annotations to cells for documentation and collaboration.
279

280
```typescript { .api }
281
/**
282
 * Add comment to a cell
283
 * @param cell - Cell object to modify
284
 * @param text - Comment text content
285
 * @param author - Optional author name
286
 */
287
function cell_add_comment(cell: CellObject, text: string, author?: string): void;
288
```
289

290
**Usage Examples:**
291

292
```typescript
293
import { utils } from "xlsx";
294

295
// Create cells with data
296
const dataCell: CellObject = { v: 1000, t: "n" };
297
const formulaCell: CellObject = { v: 2000, t: "n", f: "SUM(A1:A10)" };
298

299
// Add comments
300
utils.cell_add_comment(dataCell, "This value was manually entered on 2023-06-15", "Alice");
301
utils.cell_add_comment(formulaCell, "Formula calculates total revenue\nLast updated: Q2 2023", "Bob");
302

303
// Create worksheet with commented cells
304
const worksheet = utils.aoa_to_sheet([
305
  ["Revenue Data"],
306
  ["Q1 Revenue", 1000],
307
  ["Q2 Revenue", 2000]
308
]);
309

310
// Apply commented cells
311
worksheet["B2"] = dataCell;
312
worksheet["B3"] = formulaCell;
313

314
// Comments are preserved in Excel output
315
const workbook = utils.book_new();
316
utils.book_append_sheet(workbook, worksheet, "Revenue");
317

318
// Comments will be visible when opening in Excel/Sheets
319
```
320

321
### Advanced Cell Manipulation
322

323
Working with cell ranges, iteration, and bulk operations.
324

325
```typescript
326
import { utils, readFile } from "xlsx";
327

328
// Load existing workbook
329
const workbook = readFile("data.xlsx");
330
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
331

332
// Get worksheet range
333
const wsRange = worksheet["!ref"];
334
console.log(`Worksheet range: ${wsRange}`);  // e.g., "A1:E10"
335

336
if (wsRange) {
337
  const range = utils.decode_range(wsRange);
338
  
339
  // Iterate through all cells in range
340
  for (let r = range.s.r; r <= range.e.r; r++) {
341
    for (let c = range.s.c; c <= range.e.c; c++) {
342
      const cellAddress = utils.encode_cell({ r, c });
343
      const cell = worksheet[cellAddress];
344
      
345
      if (cell) {
346
        // Process cell
347
        console.log(`${cellAddress}: ${cell.v} (type: ${cell.t})`);
348
        
349
        // Apply formatting based on cell type
350
        if (cell.t === "n" && cell.v > 1000) {
351
          utils.cell_set_number_format(cell, "$#,##0.00");
352
        } else if (cell.t === "d") {
353
          utils.cell_set_number_format(cell, "mm/dd/yyyy");
354
        }
355
      }
356
    }
357
  }
358
}
359

360
// Find specific cells
361
function findCellsWithValue(worksheet: any, searchValue: any) {
362
  const results: string[] = [];
363
  const range = worksheet["!ref"];
364
  
365
  if (range) {
366
    const decodedRange = utils.decode_range(range);
367
    for (let r = decodedRange.s.r; r <= decodedRange.e.r; r++) {
368
      for (let c = decodedRange.s.c; c <= decodedRange.e.c; c++) {
369
        const cellAddress = utils.encode_cell({ r, c });
370
        const cell = worksheet[cellAddress];
371
        if (cell && cell.v === searchValue) {
372
          results.push(cellAddress);
373
        }
374
      }
375
    }
376
  }
377
  
378
  return results;
379
}
380

381
// Usage
382
const matchingCells = findCellsWithValue(worksheet, "Total");
383
console.log(`Found "Total" in cells: ${matchingCells.join(", ")}`);
384
```
385

386
## Range Operations
387

388
Working with cell ranges for bulk operations and data analysis:
389

390
```typescript
391
import { utils } from "xlsx";
392

393
// Create range utilities
394
function getCellsInRange(worksheet: any, rangeStr: string) {
395
  const range = utils.decode_range(rangeStr);
396
  const cells: any[] = [];
397
  
398
  for (let r = range.s.r; r <= range.e.r; r++) {
399
    for (let c = range.s.c; c <= range.e.c; c++) {
400
      const cellAddress = utils.encode_cell({ r, c });
401
      const cell = worksheet[cellAddress];
402
      if (cell) {
403
        cells.push({ address: cellAddress, ...cell });
404
      }
405
    }
406
  }
407
  
408
  return cells;
409
}
410

411
function sumRange(worksheet: any, rangeStr: string): number {
412
  const cells = getCellsInRange(worksheet, rangeStr);
413
  return cells
414
    .filter(cell => cell.t === "n")
415
    .reduce((sum, cell) => sum + (cell.v || 0), 0);
416
}
417

418
// Usage with actual worksheet
419
const workbook = utils.book_new();
420
const data = [
421
  ["Product", "Q1", "Q2", "Q3", "Q4"],
422
  ["Widget", 100, 150, 200, 175],
423
  ["Gadget", 80, 120, 160, 140],
424
  ["Tool", 60, 90, 110, 100]
425
];
426
const worksheet = utils.aoa_to_sheet(data);
427

428
// Calculate totals for ranges
429
const q1Total = sumRange(worksheet, "B2:B4");  // Sum Q1 column
430
const widgetTotal = sumRange(worksheet, "B2:E2");  // Sum Widget row
431

432
console.log(`Q1 Total: ${q1Total}`);      // 240
433
console.log(`Widget Total: ${widgetTotal}`); // 625
434
```