insert some cell comments when we write a XLSX file #605

New Issue

2017-03-27T06:33:10Z

ylbweb commented

2017-03-27 06:33:10 +00:00

(Migrated from github.com)

Is it possible to insert some cell comments when we write a XLSX file with js-xlsx ? When I read a file containing some cell comments, they appear when I read the file:

var wb = XLSX.readFile("Book1comments.xlsx")
undefined
wb.Sheets.Sheet1.A1.c
[ { a: 'StÃ©phane Laurent',
t: 'Stéphane Laurent:\r\nhello',
r: '<
t>StÃ©phane Laurent:\r\nhello',
h: 'StÃ©phane Laurent:
hello'
} ]

Now, when I write this JSON workbook to a XLSX file:

XLSX.writeFile(wb, "Book1comments_rewritten.xlsx")

then there are no comments anymore in the created file.

Is it possible to insert some cell comments when we write a XLSX file with js-xlsx ? When I read a file containing some cell comments, they appear when I read the file: > var wb = XLSX.readFile("Book1comments.xlsx") undefined > wb.Sheets.Sheet1.A1.c [ { a: 'StÃ©phane Laurent', t: 'Stéphane Laurent:\r\nhello', r: '<r><rPr><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr>< t>StÃ©phane Laurent:</t></r><r><rPr><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr><t xml:space="preserve">\r\nhello</t></r>', h: 'StÃ©phane Laurent: hello' } ] Now, when I write this JSON workbook to a XLSX file: XLSX.writeFile(wb, "Book1comments_rewritten.xlsx") then there are no comments anymore in the created file.

SheetJSDev commented

2017-03-27 07:09:33 +00:00

(Migrated from github.com)

@ylbweb You can read comments from XLSX but currently cannot write comments. Comment write support is in the roadmap. The main hurdle is settling on the representation of the comments -- the individual formats (XLS, XLSX, XLSB, XLML, ODS) store comments in different ways and we do not yet convert the comments to the same form.

If you just need XLSX write support and you have some time, you can add it in. There are 5 changes you need to make:

collect all of the comments when writing the worksheet. write_ws_xml_cell function gets called on every cell. In that call, check if the cell has a comment and push it to some array (I recommend creating an array ws["!comments"]=[] and keep pushing into it).
generate the comments xml. You have to write a new function for this but it is not too hard to do. The XML is pretty simple here. take a look at comments_stress_test.xlsx -- this is what we use in the read tests. The function should be called in the sheets loop in the main write_zip function
generate a worksheet relationships file that references the comment. You need to define a rels object {} and add a link to the comments file. The workbook relationships file is built up in wbrels -- repeat similar logic for the worksheet, adding the comments file. Feel free to use a relationship id of 1 (second argument to the add_rels function).
add the comments file name to the content types list. it will be something like ct.comments.push(...) right after committing the comments xml.
ensure the comments are written to file. That will be in write_ct function Since comments are binary in XLSB, add the line f1("comments") directly after the themes line.

Like I said, we will be addressing comments at some point, but until that happens if you need write support it isn't too hard to add.

@ylbweb You can read comments from XLSX but currently cannot write comments. Comment write support is in the roadmap. The main hurdle is settling on the representation of the comments -- the individual formats (XLS, XLSX, XLSB, XLML, ODS) store comments in different ways and we do not yet convert the comments to the same form. If you just need XLSX write support and you have some time, you can add it in. There are 5 changes you need to make: 1) collect all of the comments when writing the worksheet. [`write_ws_xml_cell`](https://github.com/SheetJS/js-xlsx/blob/master/bits/67_wsxml.js#L126) function gets called on every cell. In that call, check if the cell has a comment and push it to some array (I recommend creating an array `ws["!comments"]=[]` and keep pushing into it). 2) generate the comments xml. You have to write a new function for this but it is not too hard to do. The XML is pretty simple here. take a look at [`comments_stress_test.xlsx`](https://github.com/SheetJS/test_files/blob/master/comments_stress_test.xlsx) -- this is what we use in the read tests. The function should be called in the sheets loop in [`the main write_zip function`](https://github.com/SheetJS/js-xlsx/blob/master/bits/86_writezip.js#L67) 3) generate a worksheet relationships file that references the comment. You need to define a rels object `{}` and add a link to the comments file. The workbook relationships file is built up in `wbrels` -- repeat similar logic for the worksheet, adding the comments file. Feel free to use a relationship id of `1` (second argument to the `add_rels` function). 4) add the comments file name to the content types list. it will be something like `ct.comments.push(...)` right after committing the comments xml. 5) ensure the comments are written to file. That will be in [`write_ct`](https://github.com/SheetJS/js-xlsx/blob/master/bits/30_ctype.js#L254) function Since comments are binary in XLSB, add the line `f1("comments")` directly after the themes line. Like I said, we will be addressing comments at some point, but until that happens if you need write support it isn't too hard to add.

SheetJSDev commented

2017-04-02 06:54:38 +00:00

(Migrated from github.com)

@ylbweb turns out that merely writing the comment xml/bin isn't enough for XLSX/XLSB! In XLML you can just write the comment and Excel will give a default comment rendering, but in XLSX/XLSB you actually need to set up a VML drawing as well.

We pushed the relevant fix to master and hope to have the next release in the next 48 hours or so

@ylbweb turns out that merely writing the comment xml/bin isn't enough for XLSX/XLSB! In XLML you can just write the comment and Excel will give a default comment rendering, but in XLSX/XLSB you actually need to set up a VML drawing as well. We pushed the relevant fix to master and hope to have the next release in the next 48 hours or so

cmuruganmsc commented

2018-02-16 11:58:39 +00:00

(Migrated from github.com)

Hi, we are using xlsx@v0.12.1 plugin (https://www.npmjs.com/package/xlsx) for writing data in exported excel file also try to add cell comment. After export the data the cell comment added for applicable cells and when we hover on cell the comment window open/close working fine with Read Only mode. When we edit the download excel file from internet/my server. The comment window failed to close when away from cell.

Please let me know if we need to add any new configuration properties in write_comments_vml() method to avoiding comment window close issue and its get works as expected. Also attached screen shot and download file for reference.

Note: Please not down the excel comment working as expected in Read Only mode.
Excel-cell-comment-window-not-close.xlsx

Hi, we are using xlsx@v0.12.1 plugin (https://www.npmjs.com/package/xlsx) for writing data in exported excel file also try to add cell comment. After export the data the cell comment added for applicable cells and when we hover on cell the comment window open/close working fine with Read Only mode. When we edit the download excel file from internet/my server. The comment window failed to close when away from cell. Please let me know if we need to add any new configuration properties in write_comments_vml() method to avoiding comment window close issue and its get works as expected. Also attached screen shot and download file for reference. Note: Please not down the excel comment working as expected in Read Only mode. [Excel-cell-comment-window-not-close.xlsx](https://github.com/SheetJS/js-xlsx/files/1731209/Excel-cell-comment-window-not-close.xlsx) ![excel-comment-window-not-close](https://user-images.githubusercontent.com/5468492/36306657-a4ade9c2-12cd-11e8-828a-be05c872a084.png)

Sign in to join this conversation.