search

jmcnamara / XlsxWriter

A Python module for creating Excel XLSX files.
https://xlsxwriter.readthedocs.io
BSD 2-Clause "Simplified" License
3.66k stars 632 forks source link

Add support for Excel's new (as of 2016?) comment feature #1060

Closed hepcat72 closed 7 months ago

hepcat72 commented 7 months ago

Feature Request

I just recently started using xlsxwriter and I was confused by my interactions with other people online when I was trying to find a way to "autosize" the comment window, and I ended up going down a rabbit hole. Eventually, I learned that what xlsxwriter refers to as comments, excel (which I installed/updated to the latest version today) now treats as "Notes". Old style comments (which did have an autosize feature pre-2016), have simple legacy support as Excel's new "Notes" feature. However, once a note has been added to a cell, you can no longer add the new style of "comments" to that cell, (the "new comment" menu item is grayed out).

Here is an example. The yellow note was added by xlsxwriter and the comment (on the cell below it), I added manually:

noteandcomment

Excel will allow you to select "Format Comment..." associated with the xlsxwriter-note from the system menu bar:

formatcommentpanel

which it doesn't give you from the contextual menu from right-clicking the "comment" border:

commentedgecontxtmenu

But all it gives you is a "format shape" interface:

formatshape

If you try to add a comment to the cell with the existing "comment"/note, the option is grayed out:

ncgray

But you can add comments to cells without xlsxwriter comments, even if a comment has already been added:

secondcmnt
hepcat72 commented 7 months ago

Don't know what happened to the rest of my issue description, but luckily the back button worked to recover the excluded text:

So basically, it would be nice if xlsxwriter could support the new comment interface.

I also explored the options of adding shapes and text boxes, which have the option to automatically fit the text that's added (which was my original goal), but even though I could associate those things with a cell (or cells), I did not quickly figure out a way to make them only appear on hover...

My ultimate goal is to be able to add on-hover "comments" to cells in generated excel docs that always fit the text entered so that processing of user-submitted data could attach things like, errors and instructions to cells.

jmcnamara commented 7 months ago

I just recently started using xlsxwriter and I was confused by my interactions with other people online

I saw your question on StackOverflow but didn't get a chance to respond.

Old style comments (which did have an autosize feature pre-2016),

I don't think that is correct. I have Excel 2007 (amongst others) and it doesn't have an autosize feature. However, even if it did that probably wouldn't help you. Excel's autosizing features like autofit are usually something that the UI does at runtime and isn't part of the file format. Instead the Excel runtime calculates the required size and writes that size to the file format.

You could simulate that by doing a calculation on the text in the text box. It would need to be approximate because you won't have access to the font metrics and other Windows shape/size calculations but it would probably be sufficient for most cases.

I can try create a sample program and post it on StackOverflow.

jmcnamara commented 7 months ago

Added StackOverflow answer: https://stackoverflow.com/a/78301613/10238

Closing this as won't fix/not planned.

jmcnamara commented 7 months ago

Old style comments (which did have an autosize feature pre-2016),

I don't think that is correct.

I was wrong on this. There is a "Automatic size" option in older versions of Excel. Which give output like this:

screenshot 4

However, as I anticipated the actual size is calculated by the Excel runtime and stored in the file. So it would still be necessary to manually calculate the required sizes for the comment box.

hepcat72 commented 7 months ago

That makes sense. I'd realized that Moken's suggestions required a running instance of Excel to be able to autosize the comment boxes. So, reading between the lines and making some inferences, and putting what you said in my own words: I'm guessing that xlsxwriter is using a library's feature set to generate or edit an excel file and that that library doesn't contain the autosize function for comments? It's only available in the running instance of excel and the result is static dimensions that are added to the file? Am I understanding that correctly?