Skip to content

Commit fbbfcf7

Browse files
guyonrocheguyonroche
authored
Merge pull request #911 from brunoargolo/FEATURE/ADD-COMMENTS-TO-STREAM-WRITER
Feature/Add comments/notes to stream writer
2 parents ff813aa + 8c12268 commit fbbfcf7

File tree

5 files changed

+192
-5
lines changed

5 files changed

+192
-5
lines changed

‎lib/stream/xlsx/sheet-comments-writer.js‎

Lines changed: 121 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,121 @@
1+
'use strict';
2+
3+
const XmlStream = require('../../utils/xml-stream');
4+
const RelType = require('../../xlsx/rel-type');
5+
const colCache = require('../../utils/col-cache');
6+
const CommentXform = require('../../xlsx/xform/comment/comment-xform');
7+
const VmlNoteXform = require('../../xlsx/xform/comment/vml-note-xform');
8+
9+
const SheetCommentsWriter = (module.exports = function(worksheet, sheetRelsWriter, options) {
10+
// in a workbook, each sheet will have a number
11+
this.id = options.id;
12+
this.count = 0;
13+
this._worksheet = worksheet;
14+
this._workbook = options.workbook;
15+
this._sheetRelsWriter = sheetRelsWriter;
16+
});
17+
18+
SheetCommentsWriter.prototype = {
19+
get commentsStream() {
20+
if (!this._commentsStream) {
21+
// eslint-disable-next-line no-underscore-dangle
22+
this._commentsStream = this._workbook._openStream(`/xl/comments${this.id}.xml`);
23+
}
24+
return this._commentsStream;
25+
},
26+
get vmlStream() {
27+
if (!this._vmlStream) {
28+
// eslint-disable-next-line no-underscore-dangle
29+
this._vmlStream = this._workbook._openStream(`xl/drawings/vmlDrawing${this.id}.vml`);
30+
}
31+
return this._vmlStream;
32+
},
33+
34+
_addRelationships(){
35+
const commentRel = {
36+
Type: RelType.Comments,
37+
Target: `../comments${this.id}.xml`,
38+
};
39+
this._sheetRelsWriter.addRelationship(commentRel);
40+
41+
const vmlDrawingRel = {
42+
Type: RelType.VmlDrawing,
43+
Target: `../drawings/vmlDrawing${this.id}.vml`,
44+
};
45+
this.vmlRelId = this._sheetRelsWriter.addRelationship(vmlDrawingRel);
46+
},
47+
48+
_addCommentRefs(){
49+
this._workbook.commentRefs.push({
50+
commentName: `comments${this.id}`,
51+
vmlDrawing: `vmlDrawing${this.id}`,
52+
});
53+
},
54+
55+
_writeOpen() {
56+
this.commentsStream.write(
57+
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
58+
'<comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">' +
59+
'<authors><author>Author</author></authors>' +
60+
'<commentList>'
61+
);
62+
this.vmlStream.write(
63+
'<?xml version="1.0" encoding="UTF-8"?>' +
64+
'<xml xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:x="urn:schemas-microsoft-com:office:excel">' +
65+
'<o:shapelayout v:ext="edit">' +
66+
'<o:idmap v:ext="edit" data="1" />' +
67+
'</o:shapelayout>' +
68+
'<v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202" path="m,l,21600r21600,l21600,xe">' +
69+
'<v:stroke joinstyle="miter" />' +
70+
'<v:path gradientshapeok="t" o:connecttype="rect" />' +
71+
'</v:shapetype>'
72+
);
73+
},
74+
75+
_writeComment(comment, index) {
76+
const commentXform = new CommentXform();
77+
const commentsXmlStream = new XmlStream();
78+
commentXform.render(commentsXmlStream, comment);
79+
this.commentsStream.write(commentsXmlStream.xml);
80+
81+
const vmlNoteXform = new VmlNoteXform();
82+
const vmlXmlStream = new XmlStream();
83+
vmlNoteXform.render(vmlXmlStream, comment, index);
84+
this.vmlStream.write(vmlXmlStream.xml);
85+
},
86+
87+
_writeClose() {
88+
this.commentsStream.write('</commentList></comments>');
89+
this.vmlStream.write('</xml>');
90+
},
91+
92+
addComments(comments){
93+
if(comments && comments.length){
94+
if(!this.startedData){
95+
this._worksheet.comments = [];
96+
this._writeOpen();
97+
this._addRelationships();
98+
this._addCommentRefs();
99+
this.startedData = true;
100+
}
101+
102+
comments.forEach(item => {
103+
item.refAddress = colCache.decodeAddress(item.ref);
104+
});
105+
106+
comments.forEach((comment)=>{
107+
this._writeComment(comment, this.count);
108+
this.count += 1;
109+
});
110+
}
111+
},
112+
113+
commit() {
114+
if (this.count) {
115+
this._writeClose();
116+
this.commentsStream.end();
117+
this.vmlStream.end();
118+
}
119+
},
120+
121+
};

‎lib/stream/xlsx/sheet-rels-writer.js‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,10 @@ SheetRelsWriter.prototype = {
6464
return this._writeRelationship(media);
6565
},
6666

67+
addRelationship(rel) {
68+
return this._writeRelationship(rel);
69+
},
70+
6771
commit() {
6872
if (this.count) {
6973
// write xml utro

‎lib/stream/xlsx/workbook-writer.js‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,7 @@ const WorkbookWriter = (module.exports = function(options) {
4646
this.zipOptions = options.zip;
4747

4848
this.media = [];
49+
this.commentRefs = [];
4950

5051
this.zip = Archiver('zip', this.zipOptions);
5152
if (options.stream) {
@@ -194,6 +195,7 @@ WorkbookWriter.prototype = {
194195
const model = {
195196
worksheets: this._worksheets.filter(Boolean),
196197
sharedStrings: this.sharedStrings,
198+
commentRefs: this.commentRefs,
197199
};
198200
const xform = new ContentTypesXform();
199201
const xml = xform.toXml(model);

‎lib/stream/xlsx/worksheet-writer.js‎

Lines changed: 30 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,7 @@ const Row = require('../../doc/row');
1313
const Column = require('../../doc/column');
1414

1515
const SheetRelsWriter = require('./sheet-rels-writer');
16+
const SheetCommentsWriter = require('./sheet-comments-writer');
1617
const DataValidations = require('../../doc/data-validations');
1718

1819
const xmlBuffer = new StringBuf();
@@ -38,7 +39,7 @@ const xform = {
3839
dataValidations: new DataValidationsXform(),
3940
sheetProperties: new SheetPropertiesXform(),
4041
sheetFormatProperties: new SheetFormatPropertiesXform(),
41-
columns: new ListXform({ tag: 'cols', length: false, childXform: new ColXform() }),
42+
columns: new ListXform({tag: 'cols', length: false, childXform: new ColXform()}),
4243
row: new RowXform(),
4344
hyperlinks: new ListXform({ tag: 'hyperlinks', length: false, childXform: new HyperlinkXform() }),
4445
sheetViews: new ListXform({ tag: 'sheetViews', length: false, childXform: new SheetViewXform() }),
@@ -78,6 +79,9 @@ const WorksheetWriter = (module.exports = function(options) {
7879
// keep record of all hyperlinks
7980
this._sheetRelsWriter = new SheetRelsWriter(options);
8081

82+
this._sheetCommentsWriter = new SheetCommentsWriter(this, this._sheetRelsWriter, options);
83+
84+
8185
// keep a record of dimensions
8286
this._dimensions = new Dimensions();
8387

@@ -110,7 +114,7 @@ const WorksheetWriter = (module.exports = function(options) {
110114
this.pageSetup = Object.assign(
111115
{},
112116
{
113-
margins: { left: 0.7, right: 0.7, top: 0.75, bottom: 0.75, header: 0.3, footer: 0.3 },
117+
margins: {left: 0.7, right: 0.7, top: 0.75, bottom: 0.75, header: 0.3, footer: 0.3},
114118
orientation: 'portrait',
115119
horizontalDpi: 4294967295,
116120
verticalDpi: 4294967295,
@@ -139,6 +143,8 @@ const WorksheetWriter = (module.exports = function(options) {
139143

140144
this._workbook = options.workbook;
141145

146+
this.hasComments = false;
147+
142148
// views
143149
this._views = options.views || [];
144150

@@ -200,6 +206,7 @@ WorksheetWriter.prototype = {
200206
// we _cannot_ accept new rows from now on
201207
this._rows = null;
202208

209+
203210
if (!this.startedData) {
204211
this._writeOpenSheetData();
205212
}
@@ -216,11 +223,15 @@ WorksheetWriter.prototype = {
216223
this._writePageMargins();
217224
this._writePageSetup();
218225
this._writeBackground();
226+
227+
// Legacy Data tag for comments
228+
this._writeLegacyData();
229+
219230
this._writeCloseWorksheet();
220-
221231
// signal end of stream to workbook
222232
this.stream.end();
223233

234+
this._sheetCommentsWriter.commit();
224235
// also commit the hyperlinks if any
225236
this._sheetRelsWriter.commit();
226237

@@ -471,7 +482,7 @@ WorksheetWriter.prototype = {
471482
_writeColumns() {
472483
const cols = Column.toModel(this.columns);
473484
if (cols) {
474-
xform.columns.prepare(cols, { styles: this._workbook.styles });
485+
xform.columns.prepare(cols, {styles: this._workbook.styles});
475486
this.stream.write(xform.columns.toXml(cols));
476487
}
477488
},
@@ -486,17 +497,24 @@ WorksheetWriter.prototype = {
486497
}
487498

488499
if (row.hasValues || row.height) {
489-
const { model } = row;
500+
const {model} = row;
490501
const options = {
491502
styles: this._workbook.styles,
492503
sharedStrings: this.useSharedStrings ? this._workbook.sharedStrings : undefined,
493504
hyperlinks: this._sheetRelsWriter.hyperlinksProxy,
494505
merges: this._merges,
495506
formulae: this._formulae,
496507
siFormulae: this._siFormulae,
508+
comments: [],
497509
};
498510
xform.row.prepare(model, options);
499511
this.stream.write(xform.row.toXml(model));
512+
513+
if(options.comments.length){
514+
this.hasComments = true;
515+
this._sheetCommentsWriter.addComments(options.comments);
516+
}
517+
500518
}
501519
},
502520
_writeCloseSheetData() {
@@ -538,6 +556,13 @@ WorksheetWriter.prototype = {
538556
this.stream.write(xform.picture.toXml(this._background));
539557
}
540558
},
559+
_writeLegacyData() {
560+
if(this.hasComments){
561+
xmlBuffer.reset();
562+
xmlBuffer.addText(`<legacyDrawing r:id="${this._sheetCommentsWriter.vmlRelId}"/>`);
563+
this.stream.write(xmlBuffer);
564+
}
565+
},
541566
_writeDimensions() {
542567
// for some reason, Excel can't handle dimensions at the bottom of the file
543568
// and we don't know the dimensions until the commit, so don't write them.

‎test/test-comment-stream-writer.js‎

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,35 @@
1+
const Excel = require('../lib/exceljs.nodejs.js');
2+
const HrStopwatch = require('./utils/hr-stopwatch');
3+
4+
const [, , filename] = process.argv;
5+
6+
const wb = new Excel.stream.xlsx.WorkbookWriter({filename});
7+
const ws = wb.addWorksheet('Foo');
8+
ws.getCell('B2').value = 5;
9+
ws.getCell('B2').note = {
10+
texts: [
11+
{'font': {'size': 12, 'color': {'theme': 0}, 'name': 'Calibri', 'family': 2, 'scheme': 'minor'}, 'text': 'This is '},
12+
{'font': {'italic': true, 'size': 12, 'color': {'theme': 0}, 'name': 'Calibri', 'scheme': 'minor'}, 'text': 'a'},
13+
{'font': {'size': 12, 'color': {'theme': 1}, 'name': 'Calibri', 'family': 2, 'scheme': 'minor'}, 'text': ' '},
14+
{'font': {'size': 12, 'color': {'argb': 'FFFF6600'}, 'name': 'Calibri', 'scheme': 'minor'}, 'text': 'colorful'},
15+
{'font': {'size': 12, 'color': {'theme': 1}, 'name': 'Calibri', 'family': 2, 'scheme': 'minor'}, 'text': ' text '},
16+
{'font': {'size': 12, 'color': {'argb': 'FFCCFFCC'}, 'name': 'Calibri', 'scheme': 'minor'}, 'text': 'with'},
17+
{'font': {'size': 12, 'color': {'theme': 1}, 'name': 'Calibri', 'family': 2, 'scheme': 'minor'}, 'text': ' in-cell '},
18+
{'font': {'bold': true, 'size': 12, 'color': {'theme': 1}, 'name': 'Calibri', 'family': 2, 'scheme': 'minor'}, 'text': 'format'},
19+
],
20+
};
21+
22+
ws.getCell('D2').value = 'Zoo';
23+
ws.getCell('D2').note = 'Plain Text Comment';
24+
25+
const stopwatch = new HrStopwatch();
26+
stopwatch.start();
27+
wb.commit()
28+
.then(() => {
29+
const micros = stopwatch.microseconds;
30+
console.log('Done.');
31+
console.log('Time taken:', micros);
32+
})
33+
.catch(error => {
34+
console.log(error.message);
35+
});

0 commit comments

Comments
 (0)