Comment (3)
Excel files often contain a wealth of comments that can provide valuable context and insights. These comments may include important text notes, instructions, or even embedded images that can be incredibly useful for various data analysis and reporting tasks. Extracting this information from the comments can be a valuable step in unlocking the full potential of the data. In this article, we will demonstrate how to effectively extract text and images from comments in Excel files in Python using Spire.XLS for Python.
Install Spire.XLS for Python
This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.
pip install Spire.XLS
If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows
Extract Text from Comments in Excel in Python
You can get the text of comments using the ExcelCommentObject.Text property. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Create a list to store the extracted comment text.
- Get the comments in the worksheet using Worksheet.Comments property.
- Traverse through the comments.
- Get the text of each comment using ExcelCommentObject.Text property and append it to the list.
- Save the content of the list to a text file.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Comments.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Create a list to store the comment text
comment_text = []
# Get all the comments in the worksheet
comments = worksheet.Comments
# Extract the text from each comment and add it to the list
for i, comment in enumerate(comments, start=1):
comment_text.append(f"Comment {i}:")
text = comment.Text
comment_text.append(text)
comment_text.append("")
# Write the comment text to a file
with open("comments.txt", "w", encoding="utf-8") as file:
file.write("\n".join(comment_text))
Extract Images from Comments in Excel in Python
To get the images embedded in Excel comments, you can use the ExcelCommentObject.Fill.Picture property. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific comment in the worksheet using Worksheet.Comments[index] property.
- Get the embedded image in the comment using ExcelCommentObject.Fill.Picture property.
- Save the image to an image file.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("ImageComment.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Get a specific comment in the worksheet
comment = worksheet.Comments[0]
# Extract the image from the comment and save it to an image file
image = comment.Fill.Picture
image.Save("CommentImage/Comment.png")
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
Comment in Excel is primarily used to add additional instructions or notes to cells. With this feature, users can add relevant content next to a specific cell to explain the data, provide contextual information, or give instructions. It also helps users to better organize and manage the data in the Excel workbook and improve the understanding and readability of the data. Spire.XLS for Python supports adding comments to Excel files. If necessary, you can also use this library to edit the content of the comments or delete unnecessary comments. In this article, we will show you how to edit or remove existing comments in Excel on Python platforms using Spire.XLS for Python.
Install Spire.XLS for Python
This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip commands.
pip install Spire.XLS
If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows
Edit Existing Comments in Excel Using Python
Spire.XLS for Python allows users to edit existing comments in Excel, including setting new text or changing comment box size. The following are detailed steps.
- Create a Workbook instance.
- Load an Excel file from disk using Workbook.LoadFromFile() method.
- Get the first worksheet of the Excel file using Workbook.Worksheets[] property.
- Set new text for the existing comments using Worksheet.Range[].Comment.Text property.
- Set the height and width of the existing comment by using Worksheet.Range[].Comment.Height and Worksheet.Range[].Comment.Width properties.
- Automatically adapt to the size of the comment by setting the Worksheet.Range.Comment.AutoSize property to "True".
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.common import * inputFile = "C:/Users/Administrator/Desktop/Sample.xlsx" outputFile = "C:/Users/Administrator/Desktop/EditExcelComment.xlsx" # Create a Workbook instance workbook = Workbook() # Load an Excel file from disk workbook.LoadFromFile(inputFile) # Get the first worksheet of this file sheet = workbook.Worksheets[0] # Set new text for the existing comments sheet.Range["A8"].Comment.Text = "Frank has left the company." sheet.Range["F6"].Comment.Text = "Best sales." # Set the height and width of the comment of A8 sheet.Range["A8"].Comment.Height = 60 sheet.Range["A8"].Comment.Width = 100 # Automatically adapt to the size of the comment of F6 sheet.Range["F6"].Comment.AutoSize = True # Save the result file workbook.SaveToFile(outputFile, ExcelVersion.Version2013) workbook.Dispose()
Remove Existing Comments from Excel Using Python
The Worksheet.Range[].Comment.Remove() method offered by Spire.XLS for Python allows users to remove a specified comment easily. The detailed steps are as follows.
- Create a Workbook instance.
- Load an Excel file from disk using Workbook.LoadFromFile() method.
- Get the first worksheet of the Excel file using Workbook.Worksheets[] property.
- Remove the comment by using Worksheet.Range[].Comment.Remove() method.
- Save the document to another file using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.common import * inputFile = "C:/Users/Administrator/Desktop/Sample.xlsx" outputFile = "C:/Users/Administrator/Desktop/RemoveExcelComment.xlsx" # Create a Workbook instance workbook = Workbook() # Load an Excel file from disk workbook.LoadFromFile(inputFile) # Get the first worksheet of this file sheet = workbook.Worksheets[0] # Remove the comment from the sheet sheet.Range["F6"].Comment.Remove() # Save the result file workbook.SaveToFile(outputFile, ExcelVersion.Version2013) workbook.Dispose()
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
Comment in Excel is a function that allows users to add extra details or remarks as explanatory notes. Comments can be in the form of text or images. It enables users to provide additional information to explain or supplement the data in specified cells. After adding a comment, users can view the content of the comment by hovering the mouse over the cell with the comment. This feature enhances the readability and comprehensibility of the document, helping readers better understand and handle the data in Excel. In this article, we will show you how to add comments in Excel by using Spire.XLS for Python.
Install Spire.XLS for Python
This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.
pip install Spire.XLS
If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows
Add Comment with Text in Excel
Spire.XLS for Python allows users to add comment with text in Excel by calling CellRange.AddComment() method. The following are detailed steps.
- Create an object of Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get the first worksheet of this file using Workbook.Worksheets[] property.
- Get the specified cell by using Worksheet.Range[] property.
- Set the author and content of the comment and add them to the obtained cell using CellRange.AddComment() method.
- Set the font of the comment.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
inputFile = "sample.xlsx"
outputFile = "CommentWithAuthor.xlsx"
#Create an object of Workbook class
workbook = Workbook()
#Load the sample file from disk
workbook.LoadFromFile(inputFile)
#Get the first worksheet
sheet = workbook.Worksheets[0]
#Get the specified cell
range = sheet.Range["B4"]
#Set the author and content of the comment
author = "Jhon"
text = "Emergency task."
#Add comment to the obtained cell
comment = range.AddComment()
comment.Width = 200
comment.Visible = True
comment.Text = author + ":\n" + text
#Set the font of the comment
font = workbook.CreateFont()
font.FontName = "Tahoma"
font.KnownColor = ExcelColors.Black
font.IsBold = True
comment.RichText.SetFont(0, len(author), font)
#Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()
Add Comment with Picture in Excel
Additionally, Spire.XLS for Python also enable users to add comment with picture to the specified cell in Excel by using CellRange.AddComment() and ExcelCommentObject.Fill.CustomPicture() methods. The following are detailed steps.
- Create an object of Workbook class.
- Get the first worksheet using Workbook.Worksheets[] property.
- Get the specified cell by using Worksheet.Range[] property and set text for it.
- Add comment to the obtained cell by using CellRange.AddComment() method.
- Load an image and fill the comment with it by calling ExcelCommentObject.Fill.CustomPicture() method.
- Set the height and width of the comment.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.common import * inputFile = "logo.png" outputFile = "CommentWithPicture.xlsx" #Create an object of Workbook class workbook = Workbook() #Get the first worksheet sheet = workbook.Worksheets[0] #Get the specified cell and set text for it range = sheet.Range["C6"] range.Text = "E-iceblue" #Add comment to the obtained cell comment = range["C6"].AddComment() #Load an image file and fill the comment with it image = Image.FromFile(inputFile) comment.Fill.CustomPicture(image, "logo.png") #Set the height and width of the comment comment.Height = image.Height comment.Width = image.Width comment.Visible = True #Save the result file workbook.SaveToFile(outputFile, ExcelVersion.Version2010) workbook.Dispose()
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
