0
# Workbook and Worksheet Management
1

2
Functions for creating, modifying, and managing workbooks and worksheets. These utilities provide comprehensive control over spreadsheet structure, data insertion, and workbook organization.
3

4
## Capabilities
5

6
### Workbook Creation and Management
7

8
Create new workbooks and manage their structure and properties.
9

10
```typescript { .api }
11
/**
12
 * Create a new empty workbook
13
 * @returns Empty WorkBook object ready for sheet addition
14
 */
15
function book_new(): WorkBook;
16

17
/**
18
 * Add worksheet to existing workbook
19
 * @param workbook - Target workbook
20
 * @param worksheet - Worksheet to add
21
 * @param name - Optional sheet name (auto-generated if not provided)
22
 * @param roll - If true, replace existing sheet with same name
23
 */
24
function book_append_sheet(
25
  workbook: WorkBook, 
26
  worksheet: WorkSheet, 
27
  name?: string, 
28
  roll?: boolean
29
): void;
30

31
/**
32
 * Set worksheet visibility in workbook
33
 * @param workbook - Target workbook
34
 * @param sheet - Sheet index or name
35
 * @param visibility - Visibility level (0=visible, 1=hidden, 2=very hidden)
36
 */
37
function book_set_sheet_visibility(
38
  workbook: WorkBook, 
39
  sheet: number | string, 
40
  visibility: number
41
): void;
42
```
43

44
**Usage Examples:**
45

46
```typescript
47
import { utils, writeFile } from "xlsx";
48

49
// Create new workbook
50
const workbook = utils.book_new();
51

52
// Create multiple worksheets
53
const salesData = [
54
  ["Month", "Revenue", "Expenses"],
55
  ["Jan", 10000, 7500],
56
  ["Feb", 12000, 8000],
57
  ["Mar", 11500, 7800]
58
];
59

60
const employeeData = [
61
  ["Name", "Department", "Salary"],
62
  ["Alice", "Sales", 50000],
63
  ["Bob", "Engineering", 75000],
64
  ["Charlie", "Marketing", 55000]
65
];
66

67
// Create worksheets
68
const salesSheet = utils.aoa_to_sheet(salesData);
69
const employeeSheet = utils.aoa_to_sheet(employeeData);
70
const summarySheet = utils.aoa_to_sheet([
71
  ["Company Summary"],
72
  ["Total Employees", 3],
73
  ["Total Revenue", 33500]
74
]);
75

76
// Add sheets to workbook with custom names
77
utils.book_append_sheet(workbook, salesSheet, "Sales Data");
78
utils.book_append_sheet(workbook, employeeSheet, "Employees");
79
utils.book_append_sheet(workbook, summarySheet, "Summary");
80

81
// Set sheet visibility
82
utils.book_set_sheet_visibility(workbook, "Summary", 0);     // Visible
83
utils.book_set_sheet_visibility(workbook, "Employees", 1);   // Hidden
84
utils.book_set_sheet_visibility(workbook, 0, 0);            // Sales Data visible (by index)
85

86
// Replace existing sheet
87
const updatedSalesSheet = utils.aoa_to_sheet([
88
  ["Month", "Revenue", "Expenses", "Profit"],
89
  ["Jan", 10000, 7500, 2500],
90
  ["Feb", 12000, 8000, 4000]
91
]);
92
utils.book_append_sheet(workbook, updatedSalesSheet, "Sales Data", true); // roll=true replaces
93

94
// Save workbook
95
writeFile(workbook, "company-data.xlsx");
96

97
// Access workbook structure
98
console.log(workbook.SheetNames);  // ["Sales Data", "Employees", "Summary"]
99
console.log(Object.keys(workbook.Sheets));  // Sheet objects
100
```
101

102
### Sheet Constants for Visibility
103

104
Predefined constants for sheet visibility levels.
105

106
```typescript { .api }
107
const consts: {
108
  /** Visible sheet (default) */
109
  SHEET_VISIBLE: 0;
110
  /** Hidden sheet (can be unhidden by user) */
111
  SHEET_HIDDEN: 1;
112
  /** Very hidden sheet (requires programmatic access to unhide) */
113
  SHEET_VERYHIDDEN: 2;
114
};
115
```
116

117
**Usage Examples:**
118

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

122
const workbook = utils.book_new();
123
const worksheet = utils.aoa_to_sheet([["Secret Data"], ["Confidential"]]);
124
const publicSheet = utils.aoa_to_sheet([["Public Data"], ["Everyone can see"]]);
125

126
utils.book_append_sheet(workbook, publicSheet, "Public");
127
utils.book_append_sheet(workbook, worksheet, "Secret");
128

129
// Use constants for clarity
130
utils.book_set_sheet_visibility(workbook, "Public", utils.consts.SHEET_VISIBLE);
131
utils.book_set_sheet_visibility(workbook, "Secret", utils.consts.SHEET_VERYHIDDEN);
132

133
// Hidden sheet won't appear in normal Excel interface
134
// Very hidden sheet requires VBA or programmatic access to unhide
135
```
136

137
### Adding Data to Existing Worksheets
138

139
Insert additional data into existing worksheets at specified locations.
140

141
```typescript { .api }
142
/**
143
 * Add JSON data to existing worksheet
144
 * @param ws - Target worksheet
145
 * @param data - Array of objects to add
146
 * @param opts - Addition options
147
 * @returns Modified worksheet
148
 */
149
function sheet_add_json<T>(ws: WorkSheet, data: T[], opts?: SheetJSONOpts): WorkSheet;
150

151
/**
152
 * Add array data to existing worksheet
153
 * @param ws - Target worksheet
154
 * @param data - 2D array to add
155
 * @param opts - Addition options
156
 * @returns Modified worksheet
157
 */
158
function sheet_add_aoa<T>(ws: WorkSheet, data: T[][], opts?: SheetAOAOpts): WorkSheet;
159

160
/**
161
 * Add DOM table data to existing worksheet (browser only)
162
 * @param ws - Target worksheet
163
 * @param data - HTML table element
164
 * @param opts - Addition options
165
 * @returns Modified worksheet
166
 */
167
function sheet_add_dom(ws: WorkSheet, data: any, opts?: Table2SheetOpts): WorkSheet;
168

169
interface SheetJSONOpts extends JSON2SheetOpts, OriginOption {}
170

171
interface SheetAOAOpts extends AOA2SheetOpts, OriginOption {}
172
```
173

174
**Usage Examples:**
175

176
```typescript
177
import { utils } from "xlsx";
178

179
// Start with existing worksheet
180
const initialData = [
181
  ["Product", "Q1 Sales"],
182
  ["Widget", 1000],
183
  ["Gadget", 1500]
184
];
185
const worksheet = utils.aoa_to_sheet(initialData);
186

187
// Add more data as JSON (appending columns)
188
const additionalData = [
189
  { "Q2 Sales": 1200, "Q3 Sales": 1400 },
190
  { "Q2 Sales": 1600, "Q3 Sales": 1800 }
191
];
192

193
utils.sheet_add_json(worksheet, additionalData, {
194
  origin: "C1",      // Start at column C
195
  skipHeader: false  // Include headers
196
});
197

198
// Add more rows as array data
199
const newProducts = [
200
  ["Tool", 800, 900, 1000],
201
  ["Accessory", 400, 450, 500]
202
];
203

204
utils.sheet_add_aoa(worksheet, newProducts, {
205
  origin: "A4"  // Start at row 4
206
});
207

208
// Result worksheet now contains:
209
// A1: Product    B1: Q1 Sales  C1: Q2 Sales  D1: Q3 Sales
210
// A2: Widget     B2: 1000      C2: 1200      D2: 1400
211
// A3: Gadget     B3: 1500      C3: 1600      D3: 1800
212
// A4: Tool       B4: 800       C4: 900       D4: 1000
213
// A5: Accessory  B5: 400       C5: 450       D5: 500
214

215
// Add data at specific locations with gaps
216
const summaryData = [
217
  { "Summary": "Total", "Amount": 8150 }
218
];
219

220
utils.sheet_add_json(worksheet, summaryData, {
221
  origin: "A7"  // Skip row 6, add summary at row 7
222
});
223

224
// Create complex layouts by adding data at different origins
225
const headerData = [["SALES REPORT 2023"]];
226
utils.sheet_add_aoa(worksheet, headerData, {
227
  origin: "A1"  // Insert title above existing data (overwrites)
228
});
229

230
// Shift existing data down and insert title
231
const reportWorksheet = utils.aoa_to_sheet([["SALES REPORT 2023"], []]);
232
utils.sheet_add_aoa(reportWorksheet, initialData, { origin: "A3" });
233
utils.sheet_add_json(reportWorksheet, additionalData, { origin: "C3", skipHeader: false });
234
```
235

236
### Array Formula Management
237

238
Set and manage array formulas across cell ranges.
239

240
```typescript { .api }
241
/**
242
 * Set array formula on a range of cells
243
 * @param ws - Target worksheet
244
 * @param range - Cell range for the array formula
245
 * @param formula - Formula expression
246
 * @param dynamic - Whether this is a dynamic array formula
247
 * @returns Modified worksheet
248
 */
249
function sheet_set_array_formula(
250
  ws: WorkSheet, 
251
  range: Range | string, 
252
  formula: string, 
253
  dynamic?: boolean
254
): WorkSheet;
255
```
256

257
**Usage Examples:**
258

259
```typescript
260
import { utils } from "xlsx";
261

262
// Create worksheet with data
263
const data = [
264
  ["Values", "Multiplier", "Results"],
265
  [10, 2, ""],
266
  [20, 3, ""],
267
  [30, 4, ""],
268
  [40, 5, ""]
269
];
270
const worksheet = utils.aoa_to_sheet(data);
271

272
// Set array formula to calculate results
273
utils.sheet_set_array_formula(
274
  worksheet,
275
  "C2:C5",                    // Range for results
276
  "A2:A5*B2:B5",             // Formula: multiply values by multipliers
277
  false                       // Traditional array formula
278
);
279

280
// Dynamic array formula (Excel 365)
281
const dynamicSheet = utils.aoa_to_sheet([
282
  ["Source Data"],
283
  [1], [2], [3], [4], [5]
284
]);
285

286
utils.sheet_set_array_formula(
287
  dynamicSheet,
288
  "C1",                       // Single cell (dynamic arrays expand automatically)
289
  "A2:A6*2",                 // Formula: double all values
290
  true                        // Dynamic array formula
291
);
292

293
// Complex array formulas
294
const salesData = [
295
  ["Product", "Price", "Quantity", "Revenue"],
296
  ["Widget", 10, 100, ""],
297
  ["Gadget", 15, 80, ""],
298
  ["Tool", 25, 60, ""]
299
];
300
const salesSheet = utils.aoa_to_sheet(salesData);
301

302
// Array formula for revenue calculation
303
utils.sheet_set_array_formula(
304
  salesSheet,
305
  "D2:D4",
306
  "B2:B4*C2:C4"  // Price * Quantity for each row
307
);
308

309
// Conditional array formula
310
utils.sheet_set_array_formula(
311
  salesSheet,
312
  "E2:E4",
313
  'IF(D2:D4>1000,"High","Low")'  // Mark high/low revenue
314
);
315

316
const workbook = utils.book_new();
317
utils.book_append_sheet(workbook, worksheet, "Array Formulas");
318
utils.book_append_sheet(workbook, salesSheet, "Sales Calculations");
319
```
320

321
### Advanced Workbook Management
322

323
Working with workbook properties, metadata, and complex structures.
324

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

328
// Create workbook with metadata
329
const workbook = utils.book_new();
330

331
// Set workbook properties
332
workbook.Props = {
333
  Title: "Quarterly Sales Report",
334
  Subject: "Q2 2023 Sales Analysis",
335
  Author: "Sales Department",
336
  Manager: "Sales Manager",
337
  Company: "Example Corp",
338
  Category: "Sales Reports",
339
  Keywords: "sales, quarterly, revenue",
340
  Comments: "Generated from CRM data",
341
  CreatedDate: new Date()
342
};
343

344
// Create summary worksheet with calculations
345
const summaryData = [
346
  ["Quarterly Sales Summary"],
347
  [],
348
  ["Metric", "Q1", "Q2", "Q3", "Q4", "Total"],
349
  ["Revenue", 100000, 120000, 110000, 130000, "=SUM(B4:E4)"],
350
  ["Expenses", 75000, 85000, 80000, 90000, "=SUM(B5:E5)"],
351
  ["Profit", "=B4-B5", "=C4-C5", "=D4-D5", "=E4-E5", "=F4-F5"]
352
];
353

354
const summarySheet = utils.aoa_to_sheet(summaryData);
355
utils.book_append_sheet(workbook, summarySheet, "Summary");
356

357
// Create detailed data sheets
358
const q1Data = [
359
  ["Q1 Sales Detail"],
360
  ["Date", "Product", "Amount"],
361
  ["2023-01-15", "Widget", 25000],
362
  ["2023-02-10", "Gadget", 35000],
363
  ["2023-03-05", "Tool", 40000]
364
];
365

366
const q2Data = [
367
  ["Q2 Sales Detail"], 
368
  ["Date", "Product", "Amount"],
369
  ["2023-04-12", "Widget", 30000],
370
  ["2023-05-18", "Gadget", 40000],
371
  ["2023-06-22", "Tool", 50000]
372
];
373

374
utils.book_append_sheet(workbook, utils.aoa_to_sheet(q1Data), "Q1 Detail");
375
utils.book_append_sheet(workbook, utils.aoa_to_sheet(q2Data), "Q2 Detail");
376

377
// Set sheet visibility - hide detail sheets by default
378
utils.book_set_sheet_visibility(workbook, "Q1 Detail", utils.consts.SHEET_HIDDEN);
379
utils.book_set_sheet_visibility(workbook, "Q2 Detail", utils.consts.SHEET_HIDDEN);
380

381
// Add custom properties
382
workbook.Custprops = {
383
  "Report Period": "Q2 2023",
384
  "Data Source": "CRM System",
385
  "Generated By": "Automated Report System",
386
  "Last Updated": new Date().toISOString()
387
};
388

389
// Save with metadata
390
writeFile(workbook, "quarterly-report.xlsx");
391

392
console.log("Created workbook with:", workbook.SheetNames.length, "sheets");
393
console.log("Sheet names:", workbook.SheetNames);
394
console.log("Properties:", workbook.Props?.Title);
395
```
396

397
### Dynamic Worksheet Management
398

399
Programmatically manage worksheets based on data structures.
400

401
```typescript
402
import { utils } from "xlsx";
403

404
function createMultiSheetReport(dataByCategory: Record<string, any[]>) {
405
  const workbook = utils.book_new();
406
  const summaryData: any[][] = [["Category", "Count", "Total Value"]];
407

408
  // Create a sheet for each category
409
  Object.entries(dataByCategory).forEach(([category, data]) => {
410
    // Create individual category sheet
411
    const worksheet = utils.json_to_sheet(data);
412
    utils.book_append_sheet(workbook, worksheet, category);
413

414
    // Collect summary data
415
    const count = data.length;
416
    const total = data.reduce((sum, item) => sum + (item.value || 0), 0);
417
    summaryData.push([category, count, total]);
418
  });
419

420
  // Create summary sheet
421
  const summarySheet = utils.aoa_to_sheet(summaryData);
422
  utils.book_append_sheet(workbook, summarySheet, "Summary");
423

424
  // Move summary to first position by recreating workbook
425
  const reorderedWorkbook = utils.book_new();
426
  utils.book_append_sheet(reorderedWorkbook, summarySheet, "Summary");
427
  
428
  Object.entries(dataByCategory).forEach(([category]) => {
429
    utils.book_append_sheet(reorderedWorkbook, workbook.Sheets[category], category);
430
  });
431

432
  return reorderedWorkbook;
433
}
434

435
// Usage
436
const salesByRegion = {
437
  "North": [
438
    { product: "Widget", sales: 1000, value: 10000 },
439
    { product: "Gadget", sales: 800, value: 12000 }
440
  ],
441
  "South": [
442
    { product: "Widget", sales: 1200, value: 12000 },
443
    { product: "Tool", sales: 600, value: 15000 }
444
  ],
445
  "East": [
446
    { product: "Gadget", sales: 900, value: 13500 },
447
    { product: "Tool", sales: 700, value: 17500 }
448
  ]
449
};
450

451
const reportWorkbook = createMultiSheetReport(salesByRegion);
452
console.log("Created report with sheets:", reportWorkbook.SheetNames);
453
// Output: ["Summary", "North", "South", "East"]
454
```