Skip to content

Commit 9e2b088

Browse files
ldefelici-mitobitldefelici-mitobit
committed
Added quote prefix feature
1 parent ac96f9a commit 9e2b088

File tree

10 files changed

+134
-13
lines changed

10 files changed

+134
-13
lines changed

‎.prettier

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,5 @@
22
"bracketSpacing": false,
33
"printWidth": 100,
44
"trailingComma": "all",
5-
"bracketSpacing": false,
65
"arrowParens": "avoid"
76
}

‎README.md

Lines changed: 12 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -116,6 +116,7 @@ To be clear, all contributions added to this library will be included in the lib
116116
<li><a href="#alignment">Alignment</a></li>
117117
<li><a href="#borders">Borders</a></li>
118118
<li><a href="#fills">Fills</a></li>
119+
<li><a href="#quote-prefix">Quote Prefix</a></li>
119120
<li><a href="#rich-text">Rich Text</a></li>
120121
</ul>
121122
</li>
@@ -1704,11 +1705,20 @@ For example, Excel only supports angle gradients of 0, 45, 90 and 135.
17041705
Similarly the sequence of stops may also be limited by the UI with positions [0,1] or [0,0.5,1] as the only options.
17051706
Take care with this fill to be sure it is supported by the target XLSX viewers.
17061707

1708+
### Quote Prefix[⬆](#contents)<!-- Link generated with jump2header -->
1709+
1710+
Individual cells now support the quote prefix style to escape cell content (es. formula that starts with '=' will treat as simple string)
1711+
1712+
```javascript
1713+
// fill A1 with red darkVertical stripes
1714+
ws.getCell('A1').quotePrefix = true;
1715+
```
1716+
17071717
### Rich Text[⬆](#contents)<!-- Link generated with jump2header -->
17081718

17091719
Individual cells now support rich text or in-cell formatting.
1710-
Rich text values can control the font properties of any number of sub-strings within the text value.
1711-
See <a href="font">Fonts</a> for a complete list of details on what font properties are supported.
1720+
Rich text values can control the font properties of any number of sub-strings within the text value.
1721+
See <a href="font">Fonts</a> for a complete list of details on what font properties are supported.
17121722

17131723
```javascript
17141724

‎index.d.ts

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -299,6 +299,7 @@ export interface Style {
299299
protection: Partial<Protection>;
300300
border: Partial<Borders>;
301301
fill: Fill;
302+
quotePrefix: boolean;
302303
}
303304

304305
export type DataValidationOperator =
@@ -656,6 +657,7 @@ export interface Column {
656657
font?: Partial<Font>;
657658
alignment?: Partial<Alignment>;
658659
protection?: Partial<Protection>;
660+
quotePrefix?: boolean;
659661

660662
toString(): string
661663
equivalentTo(other: Column): boolean

‎lib/doc/cell.js

Lines changed: 12 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -96,6 +96,14 @@ class Cell {
9696
this.style.protection = value;
9797
}
9898

99+
get quotePrefix() {
100+
return this.style.quotePrefix;
101+
}
102+
103+
set quotePrefix(value) {
104+
this.style.quotePrefix = value;
105+
}
106+
99107
_mergeStyle(rowStyle, colStyle, style) {
100108
const numFmt = (rowStyle && rowStyle.numFmt) || (colStyle && colStyle.numFmt);
101109
if (numFmt) style.numFmt = numFmt;
@@ -115,6 +123,9 @@ class Cell {
115123
const protection = (rowStyle && rowStyle.protection) || (colStyle && colStyle.protection);
116124
if (protection) style.protection = protection;
117125

126+
const quotePrefix = (rowStyle && rowStyle.quotePrefix) || (colStyle && colStyle.quotePrefix);
127+
if (quotePrefix) style.quotePrefix = quotePrefix;
128+
118129
return style;
119130
}
120131

@@ -862,8 +873,7 @@ class FormulaValue {
862873
if (!this._translatedFormula && this.model.sharedFormula) {
863874
const {worksheet} = this.cell;
864875
const master = worksheet.findCell(this.model.sharedFormula);
865-
this._translatedFormula =
866-
master && slideFormula(master.formula, master.address, this.model.address);
876+
this._translatedFormula = master && slideFormula(master.formula, master.address, this.model.address);
867877
}
868878
return this._translatedFormula;
869879
}

‎lib/doc/column.js

Lines changed: 11 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -122,9 +122,7 @@ class Column {
122122
}
123123

124124
get collapsed() {
125-
return !!(
126-
this._outlineLevel && this._outlineLevel >= this._worksheet.properties.outlineLevelCol
127-
);
125+
return !!(this._outlineLevel && this._outlineLevel >= this._worksheet.properties.outlineLevelCol);
128126
}
129127

130128
toString() {
@@ -155,7 +153,7 @@ class Column {
155153
return false;
156154
}
157155
const s = this.style;
158-
if (s && (s.font || s.numFmt || s.alignment || s.border || s.fill || s.protection)) {
156+
if (s && (s.font || s.numFmt || s.alignment || s.border || s.fill || s.protection || s.quotePrefix)) {
159157
return false;
160158
}
161159
return true;
@@ -243,6 +241,14 @@ class Column {
243241
this._applyStyle('protection', value);
244242
}
245243

244+
get quotePrefix() {
245+
return this.style.quotePrefix;
246+
}
247+
248+
set quotePrefix(value) {
249+
this._applyStyle('quotePrefix', value);
250+
}
251+
246252
get border() {
247253
return this.style.border;
248254
}
@@ -301,7 +307,7 @@ class Column {
301307
* sort cols by min
302308
* If it is not sorted, the subsequent column configuration will be overwritten
303309
* */
304-
cols = cols.sort(function(pre, next) {
310+
cols = cols.sort(function(pre, next) {
305311
return pre.min - next.min;
306312
});
307313
while (index < cols.length) {

‎lib/doc/row.js

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -291,6 +291,14 @@ class Row {
291291
this._applyStyle('protection', value);
292292
}
293293

294+
get quotePrefix() {
295+
return this.style.quotePrefix;
296+
}
297+
298+
set quotePrefix(value) {
299+
this._applyStyle('quotePrefix', value);
300+
}
301+
294302
get border() {
295303
return this.style.border;
296304
}
@@ -324,9 +332,7 @@ class Row {
324332
}
325333

326334
get collapsed() {
327-
return !!(
328-
this._outlineLevel && this._outlineLevel >= this._worksheet.properties.outlineLevelRow
329-
);
335+
return !!(this._outlineLevel && this._outlineLevel >= this._worksheet.properties.outlineLevelRow);
330336
}
331337

332338
// =========================================================================

‎lib/xlsx/xform/style/style-xform.js

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,4 @@
1+
const {parseBoolean} = require('../../../utils/utils');
12
const BaseXform = require('../base-xform');
23

34
const AlignmentXform = require('./alignment-xform');
@@ -53,6 +54,9 @@ class StyleXform extends BaseXform {
5354
if (model.protection) {
5455
xmlStream.addAttribute('applyProtection', '1');
5556
}
57+
if (model.quotePrefix) {
58+
xmlStream.addAttribute('quotePrefix', '1');
59+
}
5660

5761
/**
5862
* Rendering tags causes close of XML stream.
@@ -86,6 +90,9 @@ class StyleXform extends BaseXform {
8690
if (this.xfId) {
8791
this.model.xfId = parseInt(node.attributes.xfId, 10);
8892
}
93+
if (parseBoolean(node.attributes.quotePrefix)) {
94+
this.model.quotePrefix = true;
95+
}
8996
return true;
9097
case 'alignment':
9198
this.parser = this.map.alignment;

‎lib/xlsx/xform/style/styles-xform.js

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -285,6 +285,10 @@ class StylesXform extends BaseXform {
285285
style.protection = model.protection;
286286
}
287287

288+
if (model.quotePrefix) {
289+
style.quotePrefix = model.quotePrefix;
290+
}
291+
288292
const styleId = this._addStyle(style);
289293
if (this.weakMap) {
290294
this.weakMap.set(model, styleId);

‎package.json

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@
3636
"test:all": "npm run test:native && npm run test:es5",
3737
"test:native": "npm run test:full",
3838
"test:unit": "mocha --require spec/config/setup --require spec/config/setup-unit spec/unit --recursive",
39+
"test:escape": "mocha --require spec/config/setup spec/escape --recursive",
3940
"test:integration": "mocha --require spec/config/setup spec/integration --recursive",
4041
"test:end-to-end": "mocha --require spec/config/setup spec/end-to-end --recursive",
4142
"test:browser": "if [ ! -f .disable-test-browser ]; then npm run build && npm run test:jasmine; fi",

‎spec/escape/escape.js

Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,76 @@
1+
/* eslint-disable no-console */
2+
3+
const ExcelJS = verquire('exceljs');
4+
5+
const FORMULA_FILE_NAME = './spec/out/wb.formula.xlsx';
6+
const FORMULA_ESCAPED_FILE_NAME = './spec/out/wb.formula.escaped.xlsx';
7+
8+
describe('WorkbookWriter', () => {
9+
describe('Serialise', () => {
10+
it('shared formula', () => {
11+
const options = {
12+
filename: FORMULA_FILE_NAME,
13+
useStyles: true,
14+
};
15+
const wb = new ExcelJS.stream.xlsx.WorkbookWriter(options);
16+
const ws = wb.addWorksheet('Hello');
17+
ws.getCell('A1').value = {
18+
formula: 'ROW()+COLUMN()',
19+
ref: 'A1:B2',
20+
result: 2,
21+
};
22+
ws.getCell('B1').value = {sharedFormula: 'A1', result: 3};
23+
ws.getCell('A2').value = {sharedFormula: 'A1', result: 3};
24+
ws.getCell('B2').value = {sharedFormula: 'A1', result: 4};
25+
26+
ws.commit();
27+
return wb
28+
.commit()
29+
.then(() => {
30+
const wb2 = new ExcelJS.Workbook();
31+
return wb2.xlsx.readFile(FORMULA_FILE_NAME);
32+
})
33+
.then(wb2 => {
34+
const ws2 = wb2.getWorksheet('Hello');
35+
expect(ws2.getCell('A1').value).to.deep.equal({
36+
formula: 'ROW()+COLUMN()',
37+
shareType: 'shared',
38+
ref: 'A1:B2',
39+
result: 2,
40+
});
41+
expect(ws2.getCell('B1').value).to.deep.equal({
42+
sharedFormula: 'A1',
43+
result: 3,
44+
});
45+
expect(ws2.getCell('A2').value).to.deep.equal({
46+
sharedFormula: 'A1',
47+
result: 3,
48+
});
49+
expect(ws2.getCell('B2').value).to.deep.equal({
50+
sharedFormula: 'A1',
51+
result: 4,
52+
});
53+
});
54+
});
55+
56+
it('shared formula escaped', () => {
57+
const options = {
58+
filename: FORMULA_ESCAPED_FILE_NAME,
59+
useStyles: true,
60+
};
61+
const wb = new ExcelJS.stream.xlsx.WorkbookWriter(options);
62+
const ws = wb.addWorksheet('Hello');
63+
ws.getCell('A1').value = '=RIF.RIGA()+RIF.COLONNA()';
64+
ws.getCell('A1').quotePrefix = true;
65+
ws.getCell('B1').value = '=RIF.RIGA()+RIF.COLONNA()';
66+
ws.getCell('A2').value = '=RIF.RIGA()+RIF.COLONNA()';
67+
ws.getCell('B2').value = '=RIF.RIGA()+RIF.COLONNA()';
68+
69+
ws.commit();
70+
return wb.commit().then(() => {
71+
const wb2 = new ExcelJS.Workbook();
72+
return wb2.xlsx.readFile(FORMULA_ESCAPED_FILE_NAME);
73+
});
74+
});
75+
});
76+
});

0 commit comments

Comments
 (0)