如何根据Excel Dataframe中的内容突出显示行？

问题描述

我有一个包含如左图所示数据的EXCEL文件，我正在尝试将其格式化以获取 数据格式设置为右侧的表格。

使用我当前的代码，我能够格式化包含标题(h1，h2，...)的所有行

这是文件的内容。xlsx：

这是我当前的代码：

import pandas as pd
import numpy as np

from xlsxwriter.utility import xl_rowcol_to_cell

data = {'H1': {0: 'A', 1: '', 2: 'H1', 3: 'A', 4: '', 5: 'H1', 6: 'A', 7: 'A', 8: 'B', 9: 'B', 10: 'B', 11: '', 12: 'H1', 13: 'B', 14: 'B', 15: '', 16: 'H1', 17: 'C', 18: 'C', 19: 'C', 20: 'D', 21: 'D', 22: ''}, 'H2': {0: 'Rty', 1: '', 2: 'H2', 3: 'Rty', 4: '', 5: 'H2', 6: 'Rty', 7: 'Rty', 8: 'Rty', 9: 'Rty', 10: 'Rty', 11: '', 12: 'H2', 13: 'Rty', 14: 'Rty', 15: '', 16: 'H2', 17: 'Rty', 18: 'Rty', 19: 'Rty', 20: 'Rty', 21: 'Rty', 22: ''}, 'H3': {0: '1195', 1: '', 2: 'H3', 3: '1195', 4: '', 5: 'H3', 6: '1195', 7: '1195', 8: '1195', 9: '1195', 10: '1195', 11: '', 12: 'H3', 13: '1195', 14: '1195', 15: '', 16: 'H3', 17: '1195', 18: '1195', 19: '1195', 20: '1195', 21: '1195', 22: ''}, 'H4': {0: '9038', 1: 'H3=9038, 000', 2: 'H4', 3: '1355', 4: 'H3=1355, 363', 5: 'H4', 6: '2022', 7: '2022', 8: '2022', 9: '2022', 10: '2022', 11: 'H3=2022, 234', 12: 'H4', 13: '2564', 14: '2564', 15: 'H3=2564, 726', 16: 'H4', 17: '1501', 18: '1501', 19: '1501', 20: '1501', 21: '1501', 22: 'H3=1501, 143'}, 'H5': {0: '1537', 1: '', 2: 'H5', 3: '8', 4: '', 5: 'H5', 6: '59', 7: '78', 8: '76', 9: '6', 10: '31', 11: '', 12: 'H5', 13: '71', 14: '17', 15: '', 16: 'H5', 17: '72', 18: '89', 19: '47', 20: '32', 21: '233', 22: ''}}
df = pd.DataFrame.from_dict(data)


writer = pd.ExcelWriter('Output.xlsx', engine='xlsxwriter')
df.to_excel(writer, index=False, sheet_name='Output')

workbook = writer.book
worksheet = writer.sheets['Output']
number_rows = len(df.index)

format1 = workbook.add_format({'bg_color': 'black', 'font_color': 'yellow'})

for r in range(0,number_rows):
    if df.iat[r,0] == "H1":
        worksheet.set_row(r+1, None, format1) 

writer.save()

这是我当前的输出：

我陷入了如何将格式从A列限制到E列，以及如何根据需要将绿色、黄色、绿色Yello 当A列中的值更改时。我的意思是，对于列A=A中的所有连续值，当更改突出显示为黄色时，以绿色突出显示 当高亮显示再次更改为绿色时，依此类推。

如何才能做到这一点？提前谢谢。

---

解决方案

您可以使用不同的EXCEL库，如OpenPYXL

您可以分别设置每个单元格的格式 例如：

from openpyxl import Workbook
from openpyxl.styles import Font, Color, colors, fills
from openpyxl.utils.dataframe import dataframe_to_rows
wb = Workbook()
ws = wb.active

for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

a1 = ws['A1']
a1.font = Font(color="FF0000")
a1.fill = fills.PatternFill(patternType='solid', fgColor=Color(rgb='00FF00'))
wb.save("pandas_openpyxl.xlsx")

他们在这里有很好的文档：https://openpyxl.readthedocs.io/en/stable/pandas.html