0
# Data Import and Export
1

2
Comprehensive utilities for converting between different data formats including JSON, CSV, HTML tables, and arrays. These functions are essential for data pipeline integration and format transformation workflows.
3

4
## Capabilities
5

6
### JSON to Worksheet Import
7

8
Converts JavaScript objects/JSON arrays into worksheet format for spreadsheet generation.
9

10
```typescript { .api }
11
/**
12
 * Convert array of objects to worksheet
13
 * @param data - Array of objects to convert
14
 * @param opts - Conversion options
15
 * @returns WorkSheet object
16
 */
17
function json_to_sheet<T>(data: T[], opts?: JSON2SheetOpts): WorkSheet;
18

19
interface JSON2SheetOpts extends CommonOptions, DateNFOption {
20
  /** Use specified column order */
21
  header?: string[];
22
  /** Skip header row in generated sheet */
23
  skipHeader?: boolean;
24
}
25
```
26

27
**Usage Examples:**
28

29
```typescript
30
import { utils } from "xlsx";
31

32
// Basic JSON to worksheet
33
const data = [
34
  { Name: "Alice", Age: 25, City: "New York" },
35
  { Name: "Bob", Age: 30, City: "Los Angeles" },
36
  { Name: "Charlie", Age: 35, City: "Chicago" }
37
];
38
const worksheet = utils.json_to_sheet(data);
39

40
// With custom column order
41
const worksheet2 = utils.json_to_sheet(data, {
42
  header: ["City", "Name", "Age"]  // Reorder columns
43
});
44

45
// Skip header row and start at specific cell
46
const worksheet3 = utils.json_to_sheet(data, {
47
  skipHeader: true,
48
  origin: "B2"  // Start data at cell B2
49
});
50

51
// With custom date formatting
52
const salesData = [
53
  { Date: new Date("2023-01-15"), Product: "Widget", Sales: 100 },
54
  { Date: new Date("2023-01-16"), Product: "Gadget", Sales: 150 }
55
];
56
const worksheet4 = utils.json_to_sheet(salesData, {
57
  dateNF: "mm/dd/yyyy"  // Custom date format
58
});
59
```
60

61
### Array of Arrays to Worksheet Import
62

63
Converts 2D arrays (rows and columns) into worksheet format, providing maximum control over cell placement.
64

65
```typescript { .api }
66
/**
67
 * Convert 2D array to worksheet
68
 * @param data - 2D array where each sub-array represents a row
69
 * @param opts - Conversion options
70
 * @returns WorkSheet object
71
 */
72
function aoa_to_sheet<T>(data: T[][], opts?: AOA2SheetOpts): WorkSheet;
73

74
interface AOA2SheetOpts extends CommonOptions, DateNFOption {
75
  /** Create cell objects for stub cells */
76
  sheetStubs?: boolean;
77
}
78
```
79

80
**Usage Examples:**
81

82
```typescript
83
import { utils } from "xlsx";
84

85
// Basic 2D array to worksheet
86
const arrayData = [
87
  ["Name", "Age", "City"],           // Header row
88
  ["Alice", 25, "New York"],         // Data rows
89
  ["Bob", 30, "Los Angeles"],
90
  ["Charlie", 35, "Chicago"]
91
];
92
const worksheet = utils.aoa_to_sheet(arrayData);
93

94
// Mixed data types
95
const mixedData = [
96
  ["Product", "Price", "In Stock", "Launch Date"],
97
  ["Laptop", 999.99, true, new Date("2023-01-01")],
98
  ["Mouse", 29.99, false, new Date("2023-02-15")],
99
  ["Keyboard", 79.99, true, new Date("2023-03-01")]
100
];
101
const worksheet2 = utils.aoa_to_sheet(mixedData);
102

103
// Start at specific location
104
const worksheet3 = utils.aoa_to_sheet(arrayData, {
105
  origin: "C3"  // Start array at cell C3
106
});
107

108
// Create sparse worksheets with gaps
109
const sparseData = [
110
  ["Q1 Results"],
111
  [],  // Empty row
112
  ["Revenue", 100000],
113
  ["Expenses", 75000],
114
  [],  // Empty row  
115
  ["Profit", 25000]
116
];
117
const worksheet4 = utils.aoa_to_sheet(sparseData);
118
```
119

120
### DOM Table to Worksheet Import (Browser Only)
121

122
Converts HTML table elements directly into worksheet format for browser-based applications.
123

124
```typescript { .api }
125
/**
126
 * Convert DOM table element to worksheet (browser only)
127
 * @param data - HTML table element or table selector
128
 * @param opts - Conversion options
129
 * @returns WorkSheet object
130
 */
131
function table_to_sheet(data: any, opts?: Table2SheetOpts): WorkSheet;
132

133
/**
134
 * Convert DOM table element to workbook (browser only)
135
 * @param data - HTML table element or table selector
136
 * @param opts - Conversion options
137
 * @returns WorkBook object with single sheet
138
 */
139
function table_to_book(data: any, opts?: Table2SheetOpts): WorkBook;
140

141
interface Table2SheetOpts extends CommonOptions, DateNFOption, OriginOption, SheetOption {
142
  /** If true, plaintext parsing will not parse values */
143
  raw?: boolean;
144
  /** If >0, read the first sheetRows rows */
145
  sheetRows?: number;
146
  /** If true, hidden rows and cells will not be parsed */
147
  display?: boolean;
148
}
149
```
150

151
**Usage Examples:**
152

153
```typescript
154
// Browser environment only
155
import { utils } from "xlsx";
156

157
// Convert table by ID
158
const tableElement = document.getElementById("data-table");
159
const worksheet = utils.table_to_sheet(tableElement);
160

161
// Convert table with options
162
const worksheet2 = utils.table_to_sheet(tableElement, {
163
  raw: true,     // Get raw cell values instead of displayed text
164
  origin: "A1"   // Start at specific cell
165
});
166

167
// Convert directly to workbook
168
const workbook = utils.table_to_book(tableElement);
169

170
// Process multiple tables
171
const tables = document.querySelectorAll(".data-table");
172
const workbook2 = utils.book_new();
173
tables.forEach((table, index) => {
174
  const worksheet = utils.table_to_sheet(table);
175
  utils.book_append_sheet(workbook2, worksheet, `Table${index + 1}`);
176
});
177
```
178

179
### Worksheet to JSON Export
180

181
Converts worksheet data into JavaScript objects/JSON arrays for easy data consumption.
182

183
```typescript { .api }
184
/**
185
 * Convert worksheet to array of objects
186
 * @param worksheet - WorkSheet to convert
187
 * @param opts - Export options
188
 * @returns Array of objects representing worksheet rows
189
 */
190
function sheet_to_json<T>(worksheet: WorkSheet, opts?: Sheet2JSONOpts): T[];
191

192
interface Sheet2JSONOpts extends DateNFOption {
193
  /** Output format */
194
  header?: "A" | number | string[];
195
  /** Override worksheet range */
196
  range?: any;
197
  /** Include or omit blank lines in the output */
198
  blankrows?: boolean;
199
  /** Default value for null/undefined values */
200
  defval?: any;
201
  /** if true, return raw data; if false, return formatted text */
202
  raw?: boolean;
203
  /** if true, skip hidden rows and columns */
204
  skipHidden?: boolean;
205
  /** if true, return raw numbers; if false, return formatted numbers */
206
  rawNumbers?: boolean;
207
}
208
```
209

210
**Usage Examples:**
211

212
```typescript
213
import { utils, readFile } from "xlsx";
214

215
const workbook = readFile("data.xlsx");
216
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
217

218
// Basic JSON export
219
const jsonData = utils.sheet_to_json(worksheet);
220
console.log(jsonData);
221
// [{ Name: "Alice", Age: 25 }, { Name: "Bob", Age: 30 }]
222

223
// Use first row as headers
224
const jsonData2 = utils.sheet_to_json(worksheet, { header: 1 });
225

226
// Use column letters as keys
227
const jsonData3 = utils.sheet_to_json(worksheet, { header: "A" });
228
// [{ A: "Alice", B: 25 }, { A: "Bob", B: 30 }]
229

230
// Custom headers
231
const jsonData4 = utils.sheet_to_json(worksheet, {
232
  header: ["FullName", "Years", "Location"]
233
});
234

235
// Include empty rows and cells
236
const jsonData5 = utils.sheet_to_json(worksheet, {
237
  blankrows: true,  // Include empty rows
238
  defval: ""        // Default value for empty cells
239
});
240

241
// Get raw values without formatting
242
const jsonData6 = utils.sheet_to_json(worksheet, {
243
  raw: true,        // Get raw numeric values
244
  dateNF: "mm/dd/yyyy"  // Date format for display
245
});
246

247
// Specific range only
248
const jsonData7 = utils.sheet_to_json(worksheet, {
249
  range: "A1:C10"   // Only process first 10 rows, columns A-C
250
});
251
```
252

253
### Worksheet to CSV Export
254

255
Converts worksheet data to comma-separated values format with customizable delimiters.
256

257
```typescript { .api }
258
/**
259
 * Convert worksheet to CSV string
260
 * @param worksheet - WorkSheet to convert
261
 * @param options - CSV export options
262
 * @returns CSV string representation
263
 */
264
function sheet_to_csv(worksheet: WorkSheet, options?: Sheet2CSVOpts): string;
265

266
interface Sheet2CSVOpts extends DateNFOption {
267
  /** Field Separator ("delimiter") */
268
  FS?: string;
269
  /** Record Separator ("row separator") */
270
  RS?: string;
271
  /** Remove trailing field separators in each record */
272
  strip?: boolean;
273
  /** Include blank lines in the CSV output */
274
  blankrows?: boolean;
275
  /** Skip hidden rows and columns in the CSV output */
276
  skipHidden?: boolean;
277
  /** Force quotes around fields */
278
  forceQuotes?: boolean;
279
  /** if true, return raw numbers; if false, return formatted numbers */
280
  rawNumbers?: boolean;
281
}
282
```
283

284
**Usage Examples:**
285

286
```typescript
287
import { utils, readFile } from "xlsx";
288

289
const workbook = readFile("data.xlsx");
290
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
291

292
// Basic CSV export
293
const csvString = utils.sheet_to_csv(worksheet);
294
console.log(csvString);
295
// "Name,Age,City\nAlice,25,New York\nBob,30,Los Angeles"
296

297
// Custom separators for European format
298
const csvEuropean = utils.sheet_to_csv(worksheet, {
299
  FS: ";",      // Semicolon field separator
300
  RS: "\r\n"    // Windows line endings
301
});
302

303
// Tab-separated values
304
const tsvString = utils.sheet_to_csv(worksheet, {
305
  FS: "\t"      // Tab field separator
306
});
307

308
// Custom formatting
309
const customCsv = utils.sheet_to_csv(worksheet, {
310
  FS: "|",              // Pipe separator
311
  dateNF: "yyyy-mm-dd", // ISO date format
312
  strip: true           // Remove extra whitespace
313
});
314

315
// Skip empty rows
316
const compactCsv = utils.sheet_to_csv(worksheet, {
317
  skipEmpty: true       // Omit empty rows
318
});
319
```
320

321
### Worksheet to HTML Export
322

323
Converts worksheet data to HTML table format for web display.
324

325
```typescript { .api }
326
/**
327
 * Convert worksheet to HTML table
328
 * @param worksheet - WorkSheet to convert
329
 * @param options - HTML export options
330
 * @returns HTML string representation
331
 */
332
function sheet_to_html(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
333

334
interface Sheet2HTMLOpts {
335
  /** TABLE element id attribute */
336
  id?: string;
337
  /** Add contenteditable to every cell */
338
  editable?: boolean;
339
  /** Header HTML */
340
  header?: string;
341
  /** Footer HTML */
342
  footer?: string;
343
}
344
```
345

346
**Usage Examples:**
347

348
```typescript
349
import { utils, readFile } from "xlsx";
350

351
const workbook = readFile("data.xlsx");
352
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
353

354
// Basic HTML table
355
const htmlString = utils.sheet_to_html(worksheet);
356
console.log(htmlString);
357
// "<table><tr><td>Name</td><td>Age</td></tr>..."
358

359
// Custom table with ID and styling
360
const styledHtml = utils.sheet_to_html(worksheet, {
361
  id: "data-table",     // Add table ID
362
  editable: true        // Make cells editable
363
});
364

365
// Complete HTML document
366
const fullHtml = `
367
<!DOCTYPE html>
368
<html>
369
<head>
370
    <title>Data Report</title>
371
    <style>
372
        table { border-collapse: collapse; }
373
        td, th { border: 1px solid #ddd; padding: 8px; }
374
    </style>
375
</head>
376
<body>
377
    <h1>Report Data</h1>
378
    ${utils.sheet_to_html(worksheet)}
379
</body>
380
</html>
381
`;
382
```
383

384
### Additional Export Formats
385

386
XLSX supports export to several other specialized formats for compatibility with legacy systems.
387

388
```typescript { .api }
389
/**
390
 * Convert worksheet to tab-delimited text
391
 * @param worksheet - WorkSheet to convert
392
 * @param options - Export options
393
 * @returns Tab-delimited string
394
 */
395
function sheet_to_txt(worksheet: WorkSheet, options?: Sheet2CSVOpts): string;
396

397
/**
398
 * Convert worksheet to array of formulae strings
399
 * @param worksheet - WorkSheet to convert
400
 * @returns Array of formula strings
401
 */
402
function sheet_to_formulae(worksheet: WorkSheet): string[];
403

404
/**
405
 * Convert worksheet to DIF (Data Interchange Format)
406
 * @param worksheet - WorkSheet to convert
407
 * @param options - Export options
408
 * @returns DIF format string
409
 */
410
function sheet_to_dif(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
411

412
/**
413
 * Convert worksheet to SYLK (Symbolic Link) format
414
 * @param worksheet - WorkSheet to convert
415
 * @param options - Export options
416
 * @returns SYLK format string
417
 */
418
function sheet_to_slk(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
419

420
/**
421
 * Convert worksheet to ETH format
422
 * @param worksheet - WorkSheet to convert
423
 * @param options - Export options
424
 * @returns ETH format string
425
 */
426
function sheet_to_eth(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
427
```
428

429
**Usage Examples:**
430

431
```typescript
432
import { utils, readFile } from "xlsx";
433

434
const workbook = readFile("data.xlsx");
435
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
436

437
// Tab-delimited text (TSV)
438
const tsvData = utils.sheet_to_txt(worksheet);
439

440
// Extract all formulae from worksheet
441
const formulae = utils.sheet_to_formulae(worksheet);
442
console.log(formulae);
443
// ["A1=SUM(B1:B10)", "C1=AVERAGE(D1:D10)", ...]
444

445
// Legacy format exports
446
const difData = utils.sheet_to_dif(worksheet);      // DIF format
447
const sylkData = utils.sheet_to_slk(worksheet);     // SYLK format  
448
const ethData = utils.sheet_to_eth(worksheet);      // ETH format
449

450
// Use for system integration
451
const dataForLegacySystem = utils.sheet_to_dif(worksheet);
452
// Send to legacy system that requires DIF format
453
```
454

455
## Data Pipeline Integration
456

457
Combining import and export functions for complete data transformation workflows:
458

459
```typescript
460
import { utils, readFile, writeFile } from "xlsx";
461

462
// Read Excel file → Transform to JSON → Modify → Export to CSV
463
const workbook = readFile("input.xlsx");
464
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
465

466
// Convert to JSON for processing
467
const jsonData = utils.sheet_to_json(worksheet);
468

469
// Transform data (add calculated fields, filter, etc.)
470
const transformedData = jsonData
471
  .filter((row: any) => row.Age >= 21)  
472
  .map((row: any) => ({
473
    ...row,
474
    Category: row.Age < 30 ? "Young" : "Experienced",
475
    FullName: `${row.FirstName} ${row.LastName}`
476
  }));
477

478
// Convert back to worksheet and export
479
const newWorksheet = utils.json_to_sheet(transformedData);
480
const newWorkbook = utils.book_new();
481
utils.book_append_sheet(newWorkbook, newWorksheet, "Processed");
482

483
// Export to multiple formats
484
writeFile(newWorkbook, "output.xlsx");                    // Excel
485
const csvOutput = utils.sheet_to_csv(newWorksheet);       // CSV string
486
const htmlOutput = utils.sheet_to_html(newWorksheet);     // HTML table
487
```