PYTHON OPENPYXL COMMENTS

PYTHON PROVIDES THE OPENPYXL MODULE, WHICH IS USED TO DEAL WITH EXCEL FILES WITHOUT INVOLVING THIRD-PARTY MICROSOFT APPLICATION SOFTWARE. BY USING THIS MODULE, WE CAN HAVE CONTROL OVER EXCEL WITHOUT OPEN THE APPLICATION. IT IS USED TO PERFORM EXCEL TASKS SUCH AS READ DATA FROM EXCEL FILE, OR WRITE DATA TO THE EXCEL FILE, DRAW SOME CHARTS, ACCESSING EXCEL SHEET, RENAMING SHEET, MODIFICATION (ADDING AND DELETING) IN EXCEL SHEET, FORMATTING, STYLING IN THE SHEET, AND ANY OTHER TASK. OPENPYXL IS VERY EFFICIENT TO PERFORM THESE TASKS FOR YOU. DATA SCIENTISTS OFTEN USE THE OPENPYXL TO PERFORM DIFFERENT OPERATIONS SUCH AS DATA COPYING TO DATA MINING AS WELL AS DATA ANALYSIS

COMMENTS:

OPENPYXL CURRENTLY SUPPORTS THE READING AND WRITING OF COMMENT TEXT ONLY. FORMATTING INFORMATION IS LOST. COMMENT DIMENSIONS ARE LOST UPON READING, BUT CAN BE WRITTEN. COMMENTS ARE NOT CURRENTLY SUPPORTED IF READ_ONLY=TRUE IS USED.

Adding a comment to a cell:

Comments have a text attribute and an author attribute, which must both be set

>>> from openpyxl import Workbook

>>> from openpyxl.comments import Comment

>>> wb = Workbook()

>>> ws = wb.active

>>> comment = ws["A1"].comment

>>> comment = Comment('This is the comment text', 'Comment Author')

>>> comment.text

>>> comment.author

Output:

This is the comment text

Comment Author

 

If you assign the same comment to multiple cells then openpyxl will automatically create copies

>>> from openpyxl import Workbook

>>> from openpyxl.comments import Comment

>>> wb=Workbook()

>>> ws=wb.active

>>> comment = Comment("Text", "Author")

>>> ws["A1"].comment = comment

>>> ws["B2"].comment = comment

>>> ws["A1"].comment is comment

True

>>> ws["B2"].comment is comment

False

Output:

True

False

Loading and saving comments:

Comments present in a workbook when loaded are stored in the comment attribute of their respective cells automatically. Formatting information such as font size, bold and italics are lost, as are the original dimensions and position of the comment%u2019s container box.

Comments remaining in a workbook when it is saved are automatically saved to the workbook file.

Comment dimensions can be specified for write-only. Comment dimension are in pixels.

>>> from openpyxl import Workbook

>>> from openpyxl.comments import Comment

>>> from openpyxl.utils import units

>>> 

>>> wb=Workbook()

>>> ws=wb.active

>>> 

>>> comment = Comment("Text", "Author")

>>> comment.width = 300

>>> comment.height = 50

>>> 

>>> ws["A1"].comment = comment

>>> 

>>> wb.save('commented_book.xlsx')

If needed, openpyxl.utils.units contains helper functions for converting from other measurements such as mm or points to pixels:

>>> from openpyxl import Workbook

>>> from openpyxl.comments import Comment

>>> from openpyxl.utils import units

>>> 

>>> wb=Workbook()

>>> ws=wb.active

>>> 

>>> comment = Comment("Text", "Author")

>>> comment.width = units.points_to_pixels(300)

>>> comment.height = units.points_to_pixels(50)

>>> 

>>> ws["A1"].comment = comment