import win32com.client as win32
defselect_and_color_cells(file_path, sheet_name, search_text):# Create a new Excel application
excel = win32.Dispatch("Excel.Application")# Open the workbook
workbook = excel.Workbooks.Open(file_path)# Select the active sheet
sheet = workbook.Sheets(sheet_name)# 获取第一列的范围
first_column_range = sheet.Range(sheet.Cells(1,1), sheet.Cells(sheet.UsedRange.Rows.Count,1))# 将第一列的值读取到一个 Python 列表中
first_column_list =[str(cell.Value)for cell in first_column_range]print('first_column_list',first_column_list)
new_list =[]for index, value inenumerate(first_column_list):if value and search_text in value:# 将单元格的背景色设为绿色
new_list.append(index)#
new_list =[f"A{index +1}"for index in new_list]print('new_list',new_list)
color =65535# 背景色,这里使用颜色的索引,65535代表黄色# 将所有要设置背景色的单元格地址拼接成一个字符串
cell_range =",".join(new_list)# 使用 Range 对象的 Union 方法获取所有要设置背景色的单元格范围
cells = sheet.Range(cell_range)# 设置背景色
cells.Interior.Color = color
# Example usage
file_path =r"C:\Users\Administrator\Documents\Book1 - 副本.xlsx"
sheet_name ="Sheet1"# Replace with the name of your sheet
search_text ="11"
select_and_color_cells(file_path, sheet_name, search_text)