This is the dataframe:
I want at the column endingBalanceLC to add a comment just in the yellow cell. The problem is that I don't know exactly where this account and the aferent total are in my dataframe because the position can always change,depends the excel. The comment which I want to add is "this is a balance amount".
I was thinking to use xlsxwriter but I don't know how.
Here is an example of how you can use openpyxl to read in a file, detect cell(s) with a certain background color, and add a comment to these cells.
import openpyxl
from openpyxl import load_workbook
from openpyxl.comments import Comment
excel_file = 'sample.xlsx'
wb = load_workbook(excel_file, data_only = True)
sh = wb['Sheet1']
# iterate through excel and display data
for i in range(1, sh.max_row+1):
for j in range(1, sh.max_column+1):
## check if the background is yellow
if sh.cell(row=i, column=j).fill.start_color.index == 'FFFFFF00':
sh.cell(row=i, column=j).comment = Comment("This is a balance account", "author name")
wb.save('commented_sample.xlsx')
You can either overwrite the original file, or create a new xlsx file which will look like this:
