Skip to content

Commit 3178efd

Browse files
hofnarwillieWillem Le RouxSiemienik
authored
1842: New xlsx option for ignoring certain nodes for improved performance (#2132)
* 1842: New xlsx option for ignoring certain nodes for improved performance * Included test file * Added typescript typings * optimizing ifs * new line EOF * Update README.md - available options --------- Co-authored-by: Willem Le Roux <[email protected]> Co-authored-by: Siemienik Pawel <[email protected]>
1 parent 4abccf5 commit 3178efd

File tree

7 files changed

+73
-19
lines changed

7 files changed

+73
-19
lines changed

‎README.md‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2159,6 +2159,12 @@ faster or more resilient.
21592159

21602160
#### Reading XLSX[⬆](#contents)<!-- Link generated with jump2header -->
21612161

2162+
Options supported when reading CSV files.
2163+
2164+
| Field | Required | Type |Description |
2165+
| ---------------- | ----------- | ----------- | ----------- |
2166+
| ignoreNodes | N | Array | A list of node names to ignore while loading the XLSX document. Improves performance in some situations. <br/> Available: `sheetPr`, `dimension`, `sheetViews `, `sheetFormatPr`, `cols `, `sheetData`, `autoFilter `, `mergeCells `, `rowBreaks`, `hyperlinks `, `pageMargins`, `dataValidations`, `pageSetup`, `headerFooter `, `printOptions `, `picture`, `drawing`, `sheetProtection`, `tableParts `, `conditionalFormatting`, `extLst`,|
2167+
21622168
```javascript
21632169
// read from a file
21642170
const workbook = new Excel.Workbook();
@@ -2176,6 +2182,16 @@ await workbook.xlsx.read(stream);
21762182
const workbook = new Excel.Workbook();
21772183
await workbook.xlsx.load(data);
21782184
// ... use workbook
2185+
2186+
2187+
// using additional options
2188+
const workbook = new Excel.Workbook();
2189+
await workbook.xlsx.load(data, {
2190+
ignoreNodes: [
2191+
'dataValidations' // ignores the workbook's Data Validations
2192+
],
2193+
});
2194+
// ... use workbook
21792195
```
21802196

21812197
#### Writing XLSX[⬆](#contents)<!-- Link generated with jump2header -->

‎index.d.ts‎

Lines changed: 10 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1457,6 +1457,13 @@ export interface JSZipGeneratorOptions {
14571457
};
14581458
}
14591459

1460+
export interface XlsxReadOptions {
1461+
/**
1462+
* The list of XML node names to ignore while parsing an XLSX file
1463+
*/
1464+
ignoreNodes: string[];
1465+
}
1466+
14601467
export interface XlsxWriteOptions extends stream.xlsx.WorkbookWriterOptions {
14611468
/**
14621469
* The option passed to JsZip#generateAsync(options)
@@ -1468,19 +1475,19 @@ export interface Xlsx {
14681475
/**
14691476
* read from a file
14701477
*/
1471-
readFile(path: string): Promise<Workbook>;
1478+
readFile(path: string, options?: Partial<XlsxReadOptions>): Promise<Workbook>;
14721479

14731480
/**
14741481
* read from a stream
14751482
* @param stream
14761483
*/
1477-
read(stream: import('stream').Stream): Promise<Workbook>;
1484+
read(stream: import('stream').Stream, options?: Partial<XlsxReadOptions>): Promise<Workbook>;
14781485

14791486
/**
14801487
* load from an array buffer
14811488
* @param buffer
14821489
*/
1483-
load(buffer: Buffer): Promise<Workbook>;
1490+
load(buffer: Buffer, options?: Partial<XlsxReadOptions>): Promise<Workbook>;
14841491

14851492
/**
14861493
* write to a buffer

‎lib/xlsx/xform/sheet/worksheet-xform.js‎

Lines changed: 8 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -92,7 +92,10 @@ class WorkSheetXform extends BaseXform {
9292
constructor(options) {
9393
super();
9494

95-
const {maxRows, maxCols} = options || {};
95+
const {maxRows, maxCols, ignoreNodes} = options || {};
96+
97+
this.ignoreNodes = ignoreNodes || [];
98+
9699
this.map = {
97100
sheetPr: new SheetPropertiesXform(),
98101
dimension: new DimensionXform(),
@@ -221,9 +224,7 @@ class WorkSheetXform extends BaseXform {
221224
});
222225
}
223226
let rIdImage =
224-
this.preImageId === medium.imageId
225-
? drawingRelsHash[medium.imageId]
226-
: drawingRelsHash[drawing.rels.length];
227+
this.preImageId === medium.imageId ? drawingRelsHash[medium.imageId] : drawingRelsHash[drawing.rels.length];
227228
if (!rIdImage) {
228229
rIdImage = nextRid(drawing.rels);
229230
drawingRelsHash[drawing.rels.length] = rIdImage;
@@ -368,8 +369,8 @@ class WorkSheetXform extends BaseXform {
368369
return true;
369370
}
370371

371-
this.parser = this.map[node.name];
372-
if (this.parser) {
372+
if (this.map[node.name] && !this.ignoreNodes.includes(node.name)) {
373+
this.parser = this.map[node.name];
373374
this.parser.parseOpen(node);
374375
}
375376
return true;
@@ -405,11 +406,7 @@ class WorkSheetXform extends BaseXform {
405406
false,
406407
margins: this.map.pageMargins.model,
407408
};
408-
const pageSetup = Object.assign(
409-
sheetProperties,
410-
this.map.pageSetup.model,
411-
this.map.printOptions.model
412-
);
409+
const pageSetup = Object.assign(sheetProperties, this.map.pageSetup.model, this.map.printOptions.model);
413410
const conditionalFormattings = mergeConditionalFormattings(
414411
this.map.conditionalFormatting.model,
415412
this.map.extLst.model && this.map.extLst.model['x14:conditionalFormattings']

‎lib/xlsx/xlsx.js‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@ const TableXform = require('./xform/table/table-xform');
2222
const CommentsXform = require('./xform/comment/comments-xform');
2323
const VmlNotesXform = require('./xform/comment/vml-notes-xform');
2424

25-
const theme1Xml = require('./xml/theme1.js');
25+
const theme1Xml = require('./xml/theme1');
2626

2727
function fsReadFileAsync(filename, options) {
2828
return new Promise((resolve, reject) => {
@@ -285,9 +285,11 @@ class XLSX {
285285
entryName = entryName.substr(1);
286286
}
287287
let stream;
288-
if (entryName.match(/xl\/media\//) ||
288+
if (
289+
entryName.match(/xl\/media\//) ||
289290
// themes are not parsed as stream
290-
entryName.match(/xl\/theme\/([a-zA-Z0-9]+)[.]xml/)) {
291+
entryName.match(/xl\/theme\/([a-zA-Z0-9]+)[.]xml/)
292+
) {
291293
stream = new PassThrough();
292294
stream.write(await entry.async('nodebuffer'));
293295
} else {
@@ -597,8 +599,7 @@ class XLSX {
597599
model.created = model.created || new Date();
598600
model.modified = model.modified || new Date();
599601

600-
model.useSharedStrings =
601-
options.useSharedStrings !== undefined ? options.useSharedStrings : true;
602+
model.useSharedStrings = options.useSharedStrings !== undefined ? options.useSharedStrings : true;
602603
model.useStyles = options.useStyles !== undefined ? options.useStyles : true;
603604

604605
// Manage the shared strings

‎spec/integration/data/.gitignore‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
!*.xlsx

‎spec/integration/data/test-issue-1842.xlsx‎

7.98 KB
Binary file not shown.

‎spec/integration/issues/issue-1842-dataValidations-memory-overload.spec.js‎

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,32 @@
1+
const {join} = require('path');
2+
const {readFileSync} = require('fs');
3+
4+
const ExcelJS = verquire('exceljs');
5+
6+
const fileName = './spec/integration/data/test-issue-1842.xlsx';
7+
8+
describe('github issues', () => {
9+
describe('issue 1842 - Memory overload when unnecessary dataValidations apply', () => {
10+
it('when using readFile', async () => {
11+
const wb = new ExcelJS.Workbook();
12+
await wb.xlsx.readFile(fileName, {
13+
ignoreNodes: ['dataValidations'],
14+
});
15+
16+
// arriving here is success
17+
expect(true).to.equal(true);
18+
});
19+
20+
it('when loading an in memory buffer', async () => {
21+
const filePath = join(process.cwd(), fileName);
22+
const buffer = readFileSync(filePath);
23+
const wb = new ExcelJS.Workbook();
24+
await wb.xlsx.load(buffer, {
25+
ignoreNodes: ['dataValidations'],
26+
});
27+
28+
// arriving here is success
29+
expect(true).to.equal(true);
30+
});
31+
});
32+
});

0 commit comments

Comments
 (0)