pythonexcelxlsxwriter

Is there a way to autofit the comment window height with Python's xlsxwriter?

I just implemented a way to attach various metadata to cells in the excel files we produce in our data validation interface for our scientific data django app. I'm basically migrating from our usage of google sheets.

The one thing that google sheets was better at was displaying comments. Whatever the content of the comment, the comment hover window automatically fit the size of the comment. I don't use excel much, so I was underwhelmed when I first viewed attached comments on cells. All the comment windows are the same size and depending on the line breaks, there's not even any indication that anything is truncated.

Has anyone figured out a way to at least autofit the height? I looked at the docs, but could not find one. The ways to change the height are via height or y_scale options, but none of that takes into account the content. It just takes arbitrary numeric values.

The reason autofit would be advantageous is that the comment content is programmatically generated and will change from file to file: sometimes very little text, sometimes very large text.

Other annoyances that would be nice to be able to address:

Without manually showing and adjusting comment window dimensions:

enter image description here

After adjusting comment windows:

enter image description here

Solution

  • Has anyone figured out a way to at least autofit the height?

    There isn't anything in the file format to autofit the height of a comment. You will need to make some estimate based on the string length and wrapping. An accurate calculation would need to take into account the font metrics.

    Is there a way to select the color for the triangle that appears in the cell? I'd like to differentiate between errors and innocuous comments.

    I don't believe so. That is a fixed property of Excel.

    Is there a way to make the comment remain when you hover over the comment window itself and make the text selectable without having to click to show the comment first?

    You can either set the visble property for each comment or use the worksheet show_comments() method.

    The example below shows some of these options and workarounds:

    import textwrap
import xlsxwriter

comment1 = """The unique name of the biological sample.

MUST match the sample names in the peak annotation
file, minus any appended suffixes.
"""

comment2 = """The date the sample was collected.

Format: YYYY-MM-DD.
"""


# Approximate esitmate of the number of lines in a wrapped comment.
def count_wrapped_lines(comment):
    num_lines = 0
    segments = comment.splitlines()
    for segment in segments:
        if segment == "":
            num_lines += 1
        else:
            lines = textwrap.wrap(segment, width=30)
            num_lines += len(lines)

    if num_lines == 0:
        num_lines = 1

    return num_lines


# Map the number of lines to a pixel height.
def comment_height_in_pixels(comment):
    num_lines = count_wrapped_lines(comment)
    return num_lines * 16


# Create a new workbook and add a worksheet.
workbook = xlsxwriter.Workbook("comments.xlsx")
worksheet = workbook.add_worksheet()

# Write some comments.
worksheet.write("A1", "Sample")
worksheet.write_comment(
    "A1", comment1, {"height": comment_height_in_pixels(comment1), "y_offset": 150}
)

worksheet.write("B1", "Date")
worksheet.write_comment(
    "B1", comment2, {"height": comment_height_in_pixels(comment2), "y_offset": 50}
)


# Make all comments visible.
worksheet.show_comments()


workbook.close()

    Output:

    enter image description here