0
# XLSX
1
2
XLSX (SheetJS) is a comprehensive spreadsheet data parser and writer that supports multiple formats including Excel (XLSX, XLSM, XLSB, XLS), OpenDocument (ODS), CSV, and many others. It provides a unified JavaScript API for reading, manipulating, and generating spreadsheet files with support for advanced features like formulae, charts, pivot tables, styles, and cell formatting.
3
4
## Package Information
5
6
- **Package Name**: xlsx
7
- **Package Type**: npm
8
- **Language**: JavaScript/TypeScript
9
- **Installation**: `npm install xlsx`
10
- **Repository**: https://github.com/SheetJS/sheetjs
11
- **Documentation**: https://docs.sheetjs.com/
12
13
## Core Imports
14
15
ES Modules:
16
```typescript
17
import { read, readFile, write, writeFile, utils, WorkBook, WorkSheet } from "xlsx";
18
```
19
20
CommonJS:
21
```javascript
22
const XLSX = require("xlsx");
23
const { read, readFile, write, writeFile, utils } = XLSX;
24
```
25
26
Browser (via CDN):
27
```html
28
<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
29
<!-- XLSX is now available as a global -->
30
```
31
32
## Basic Usage
33
34
```typescript
35
import { readFile, writeFile, utils } from "xlsx";
36
37
// Read a spreadsheet file
38
const workbook = readFile("input.xlsx");
39
40
// Get the first worksheet
41
const sheetName = workbook.SheetNames[0];
42
const worksheet = workbook.Sheets[sheetName];
43
44
// Convert worksheet to JSON
45
const jsonData = utils.sheet_to_json(worksheet);
46
console.log(jsonData);
47
48
// Create a new workbook
49
const newWorkbook = utils.book_new();
50
51
// Convert JSON to worksheet and add to workbook
52
const newWorksheet = utils.json_to_sheet([
53
{ Name: "Alice", Age: 25, City: "New York" },
54
{ Name: "Bob", Age: 30, City: "San Francisco" }
55
]);
56
utils.book_append_sheet(newWorkbook, newWorksheet, "People");
57
58
// Write workbook to file
59
writeFile(newWorkbook, "output.xlsx");
60
```
61
62
## Architecture
63
64
XLSX is built around several key components:
65
66
- **Core I/O**: Primary read/write functions for file operations and data parsing
67
- **Utils Namespace**: Comprehensive utility functions for data conversion, cell manipulation, and workbook management
68
- **Stream Interface**: Node.js streaming support for large datasets (Node.js only)
69
- **Type System**: Full TypeScript support with comprehensive type definitions
70
- **Format Support**: Extensive format compatibility covering 30+ spreadsheet and data formats
71
72
## Capabilities
73
74
### Core I/O Operations
75
76
Primary functions for reading and writing spreadsheet data across multiple formats. Supports both file operations (Node.js) and in-memory data processing (all environments).
77
78
```typescript { .api }
79
function read(data: any, opts?: ParsingOptions): WorkBook;
80
function readFile(filename: string, opts?: ParsingOptions): WorkBook;
81
function write(data: WorkBook, opts: WritingOptions): any;
82
function writeFile(data: WorkBook, filename: string, opts?: WritingOptions): any;
83
function writeFileXLSX(data: WorkBook, filename: string, opts?: WritingOptions): any;
84
function writeXLSX(data: WorkBook, opts: WritingOptions): any;
85
function writeFileAsync(filename: string, data: WorkBook, opts: WritingOptions | (() => void), cb?: () => void): any;
86
function set_fs(fs: any): void;
87
function set_cptable(cptable: any): void;
88
```
89
90
[Core I/O Operations](./core-io.md)
91
92
### Data Import and Export
93
94
Utilities for converting between different data formats including JSON, CSV, HTML tables, and arrays. Essential for data pipeline integration and format transformation.
95
96
```typescript { .api }
97
// Import functions (to WorkSheet)
98
function json_to_sheet<T>(data: T[], opts?: JSON2SheetOpts): WorkSheet;
99
function aoa_to_sheet<T>(data: T[][], opts?: AOA2SheetOpts): WorkSheet;
100
function table_to_sheet(data: any, opts?: Table2SheetOpts): WorkSheet;
101
function table_to_book(data: any, opts?: Table2SheetOpts): WorkBook;
102
103
// Export functions (from WorkSheet)
104
function sheet_to_json<T>(worksheet: WorkSheet, opts?: Sheet2JSONOpts): T[];
105
function sheet_to_csv(worksheet: WorkSheet, options?: Sheet2CSVOpts): string;
106
function sheet_to_html(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
107
function sheet_to_txt(worksheet: WorkSheet, options?: Sheet2CSVOpts): string;
108
function sheet_to_formulae(worksheet: WorkSheet): string[];
109
function sheet_to_dif(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
110
function sheet_to_slk(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
111
function sheet_to_eth(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
112
```
113
114
[Data Import and Export](./data-conversion.md)
115
116
### Cell and Range Operations
117
118
Low-level utilities for working with individual cells, cell addresses, and ranges. Includes address encoding/decoding, cell formatting, and value manipulation.
119
120
```typescript { .api }
121
// Cell address utilities
122
function encode_cell(cell: CellAddress): string;
123
function encode_row(row: number): string;
124
function encode_col(col: number): string;
125
function encode_range(range: Range): string;
126
function encode_range(s: CellAddress, e: CellAddress): string;
127
function decode_cell(address: string): CellAddress;
128
function decode_row(row: string): number;
129
function decode_col(col: string): number;
130
function decode_range(range: string): Range;
131
132
// Cell manipulation
133
function format_cell(cell: CellObject, v?: any, opts?: any): string;
134
function cell_set_number_format(cell: CellObject, fmt: string | number): CellObject;
135
function cell_set_hyperlink(cell: CellObject, target: string, tooltip?: string): CellObject;
136
function cell_set_internal_link(cell: CellObject, target: string, tooltip?: string): CellObject;
137
function cell_add_comment(cell: CellObject, text: string, author?: string): void;
138
```
139
140
[Cell and Range Operations](./cell-operations.md)
141
142
### Workbook and Worksheet Management
143
144
Functions for creating, modifying, and managing workbooks and worksheets. Includes sheet visibility, data insertion, and workbook structure manipulation.
145
146
```typescript { .api }
147
function book_new(): WorkBook;
148
function book_append_sheet(workbook: WorkBook, worksheet: WorkSheet, name?: string, roll?: boolean): void;
149
function book_set_sheet_visibility(workbook: WorkBook, sheet: number | string, visibility: number): void;
150
function sheet_add_json<T>(ws: WorkSheet, data: T[], opts?: SheetJSONOpts): WorkSheet;
151
function sheet_add_aoa<T>(ws: WorkSheet, data: T[][], opts?: SheetAOAOpts): WorkSheet;
152
function sheet_add_dom(ws: WorkSheet, data: any, opts?: Table2SheetOpts): WorkSheet;
153
function sheet_set_array_formula(ws: WorkSheet, range: Range | string, formula: string, dynamic?: boolean): WorkSheet;
154
```
155
156
[Workbook and Worksheet Management](./workbook-management.md)
157
158
### Streaming Operations
159
160
Node.js streaming interface for processing large datasets without loading entire files into memory. Ideal for server-side applications processing large spreadsheets.
161
162
```typescript { .api }
163
function to_csv(sheet: WorkSheet, opts?: Sheet2CSVOpts): Readable;
164
function to_html(sheet: WorkSheet, opts?: Sheet2HTMLOpts): Readable;
165
function to_json(sheet: WorkSheet, opts?: Sheet2JSONOpts): Readable;
166
```
167
168
[Streaming Operations](./streaming.md)
169
170
## Additional Exports
171
172
```typescript { .api }
173
/** Version string */
174
const version: string;
175
176
/** SSF Formatter Library */
177
const SSF: any;
178
179
/** CFB Library */
180
const CFB: any;
181
182
/** Utility constants */
183
const utils: {
184
consts: {
185
SHEET_VISIBLE: 0;
186
SHEET_HIDDEN: 1;
187
SHEET_VERYHIDDEN: 2;
188
};
189
};
190
191
/** Stream utilities (Node.js only) */
192
const stream: {
193
to_csv(sheet: WorkSheet, opts?: Sheet2CSVOpts): Readable;
194
to_html(sheet: WorkSheet, opts?: Sheet2HTMLOpts): Readable;
195
to_json(sheet: WorkSheet, opts?: Sheet2JSONOpts): Readable;
196
set_readable(Readable: any): void;
197
};
198
```
199
200
## Core Types
201
202
```typescript { .api }
203
interface WorkBook {
204
SheetNames: string[];
205
Sheets: { [sheet: string]: WorkSheet };
206
Props?: FullProperties;
207
Custprops?: object;
208
Workbook?: WBProps;
209
vbaraw?: any;
210
}
211
212
interface WorkSheet {
213
[address: string]: CellObject | any;
214
"!ref"?: string;
215
"!margins"?: MarginInfo;
216
"!merges"?: Range[];
217
"!protect"?: ProtectInfo;
218
"!autofilter"?: AutoFilterInfo;
219
"!cols"?: ColInfo[];
220
"!rows"?: RowInfo[];
221
}
222
223
interface CellObject {
224
/** The raw value of the cell */
225
v?: any;
226
/** Formatted text (if applicable) */
227
w?: string;
228
/** Cell data type */
229
t?: ExcelDataType;
230
/** Cell formula (if applicable) */
231
f?: string;
232
/** Range of enclosing array formula (if applicable) */
233
F?: string;
234
/** Rich text encoding (if applicable) */
235
r?: string;
236
/** HTML rendering of the rich text (if applicable) */
237
h?: string;
238
/** Comments associated with the cell */
239
c?: Comments;
240
/** Number format string (if requested) */
241
z?: NumberFormat;
242
/** Cell hyperlink object (if applicable) */
243
l?: Hyperlink;
244
/** Cell style/theme (if applicable) */
245
s?: any;
246
}
247
248
interface CellAddress {
249
/** Row number (0-indexed) */
250
r: number;
251
/** Column number (0-indexed) */
252
c: number;
253
}
254
255
interface Range {
256
/** Start cell */
257
s: CellAddress;
258
/** End cell */
259
e: CellAddress;
260
}
261
262
type ExcelDataType = "b" | "n" | "e" | "s" | "d" | "z";
263
type BookType = "xlsx" | "xlsm" | "xlsb" | "xls" | "xla" | "biff8" | "biff5" | "biff2" | "xlml" | "ods" | "fods" | "csv" | "txt" | "sylk" | "slk" | "html" | "dif" | "rtf" | "prn" | "eth" | "dbf";
264
265
interface CommonOptions {
266
WTF?: boolean;
267
bookVBA?: boolean;
268
cellDates?: boolean;
269
sheetStubs?: boolean;
270
cellStyles?: boolean;
271
password?: string;
272
}
273
274
interface ParsingOptions extends CommonOptions {
275
type?: "base64" | "binary" | "buffer" | "file" | "array" | "string";
276
codepage?: number;
277
cellFormula?: boolean;
278
cellHTML?: boolean;
279
cellNF?: boolean;
280
cellText?: boolean;
281
dateNF?: string;
282
FS?: string;
283
sheetRows?: number;
284
bookDeps?: boolean;
285
bookFiles?: boolean;
286
bookProps?: boolean;
287
bookSheets?: boolean;
288
sheets?: number | string | Array<number | string>;
289
raw?: boolean;
290
xlfn?: boolean;
291
dense?: boolean;
292
PRN?: boolean;
293
}
294
295
interface WritingOptions extends CommonOptions {
296
type?: "base64" | "binary" | "buffer" | "file" | "array" | "string";
297
bookSST?: boolean;
298
bookType?: BookType;
299
compression?: boolean;
300
ignoreEC?: boolean;
301
Props?: Properties;
302
numbers?: string;
303
sheet?: string;
304
}
305
306
interface Properties {
307
Title?: string;
308
Subject?: string;
309
Author?: string;
310
Manager?: string;
311
Company?: string;
312
Category?: string;
313
Keywords?: string;
314
Comments?: string;
315
LastAuthor?: string;
316
CreatedDate?: Date;
317
}
318
319
interface FullProperties extends Properties {
320
ModifiedDate?: Date;
321
Application?: string;
322
AppVersion?: string;
323
DocSecurity?: string;
324
HyperlinksChanged?: boolean;
325
SharedDoc?: boolean;
326
LinksUpToDate?: boolean;
327
ScaleCrop?: boolean;
328
Worksheets?: number;
329
SheetNames?: string[];
330
ContentStatus?: string;
331
LastPrinted?: string;
332
Revision?: string | number;
333
Version?: string;
334
Identifier?: string;
335
Language?: string;
336
}
337
338
interface NumberFormat {
339
[key: string]: string;
340
}
341
342
interface Hyperlink {
343
Target: string;
344
Tooltip?: string;
345
}
346
347
interface Comment {
348
a?: string;
349
t: string;
350
T?: boolean;
351
}
352
353
interface Comments extends Array<Comment> {
354
hidden?: boolean;
355
}
356
357
interface ColInfo {
358
hidden?: boolean;
359
width?: number;
360
wpx?: number;
361
wch?: number;
362
level?: number;
363
MDW?: number;
364
}
365
366
interface RowInfo {
367
hidden?: boolean;
368
hpx?: number;
369
hpt?: number;
370
level?: number;
371
}
372
373
interface MarginInfo {
374
left?: number;
375
right?: number;
376
top?: number;
377
bottom?: number;
378
header?: number;
379
footer?: number;
380
}
381
382
interface ProtectInfo {
383
password?: string;
384
selectLockedCells?: boolean;
385
selectUnlockedCells?: boolean;
386
formatCells?: boolean;
387
formatColumns?: boolean;
388
formatRows?: boolean;
389
insertColumns?: boolean;
390
insertRows?: boolean;
391
insertHyperlinks?: boolean;
392
deleteColumns?: boolean;
393
deleteRows?: boolean;
394
sort?: boolean;
395
autoFilter?: boolean;
396
pivotTables?: boolean;
397
objects?: boolean;
398
scenarios?: boolean;
399
}
400
401
interface AutoFilterInfo {
402
ref: string;
403
}
404
```
405
406
## Supported Formats
407
408
### Read and Write Support
409
- **XLSX** - Excel 2007+ format
410
- **XLSM** - Excel macro-enabled format
411
- **XLSB** - Excel binary format
412
- **XLS** - Excel 97-2003 format
413
- **CSV** - Comma-separated values
414
- **ODS** - OpenDocument Spreadsheet
415
- **HTML** - HTML table format
416
- **DIF** - Data Interchange Format
417
- **SYLK** - Symbolic Link format
418
- **DBF** - dBASE database format
419
- **PRN** - Lotus formatted text
420
- **RTF** - Rich Text Format
421
422
### Read-Only Support
423
- **Numbers** - Apple Numbers format
424
- **XLML** - Excel 2003 XML format
425
- **WK1/WK2/WK3/WK4** - Lotus 1-2-3 formats
426
427
### Write-Only Support
428
- **FODS** - Flat OpenDocument Spreadsheet
429
430
## Platform Compatibility
431
432
- **Node.js** - Full feature support including file I/O and streaming
433
- **Browser** - Complete API except file system operations
434
- **Deno** - Full compatibility with ES modules
435
- **Bun** - Native support for all features
436
- **React Native** - Core functionality available
437
- **Electron** - Complete feature set in both main and renderer processes