python - painting a cell in excel with condition using python

Question

I am creating an excel report that should give me a result of automatic tests. It should say if they failed/ passed. I have created the excel report from csv using this code:

 import pandas as pd
    import string
    writer = pd.ExcelWriter("file.xlsx", engine="xlsxwriter")


    df = pd.read_csv("K:\\results.csv")
    df.to_excel(writer, sheet_name=os.path.basename("K:\\results.csv"))


    # skip 2 rows
    df.to_excel(writer, sheet_name='Sheet1', startrow=2, header=False, index=False)

    workbook = writer.book
    worksheet = writer.sheets['Sheet1']
    # Add a header format.
    header_format = workbook.add_format({
        'bold': True,
        'fg_color': '#ffcccc',
        'border': 1})

    # create dictionary for map length of columns
    d = dict(zip(range(25), string.ascii_uppercase))
    print (d)

    max_len = d[len(df.columns) - 1]
    print(max_len)
    # C
    # dynamically set merged columns in first row
    worksheet.merge_range('A1:' + max_len + '1', 'This Sheet is for Personal Details')

    for col_num, value in enumerate(df.columns.values):
        # write to second row
        worksheet.write(1, col_num, value, header_format)

        column_len = df[value].astype(str).str.len().max()
        column_len = max(column_len, len(value)) + 3
        worksheet.set_column(col_num, col_num, column_len)




    writer.save()

Now, if i have a cell that has the word" success" in it, i want to color it green, and if i have a cell in the excel which says "fail" in it i want to color it red. How can i access a specific cell in the excel file with the condition of whats written in it? Thanks.

标签： pythonexcel

Answer 1

您可以为此使用条件格式：

import pandas as pd


# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Data': ['success', 'bar', 'fail', 'foo', 'success']})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_conditional.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a format for fail. Light red fill with dark red text.
fail_format = workbook.add_format({'bg_color': '#FFC7CE',
                                   'font_color': '#9C0006'})

# Add a format for pass. Green fill with dark green text.
pass_format = workbook.add_format({'bg_color': '#C6EFCE',
                                   'font_color': '#006100'})

# Apply conditional formats to the cell range.
worksheet.conditional_format('B2:B6', {'type':     'text',
                                       'criteria': 'containing',
                                       'value':    'fail',
                                       'format':   fail_format})

worksheet.conditional_format('B2:B6', {'type':     'text',
                                       'criteria': 'containing',
                                       'value':    'success',
                                       'format':   pass_format})

# Close the Pandas Excel writer and output the Excel file.
writer.save()

输出：

请参阅有关使用条件格式的 XlsxWriter 文档。请注意，您还可以使用数字（行，列）范围而不是A1:D4范围，请参阅conditional_format()。