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

Closed
opened 2017-03-27 06:33:10 +00:00 by ylbweb · 3 comments
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><b/><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: '<span style="font-weight: bold;">Stéphane Laurent:</span><span style=""><br/>hello</span>' } ] 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:

  1. 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).

  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 -- this is what we use in the read tests. The function should be called in the sheets loop in the main write_zip function

  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 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
excel-comment-window-not-close

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.
No Milestone
No Assignees
1 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#605
No description provided.