I have a dataframe
df = pd.DataFrame(columns = ['id', 'name', 'age'])
df.loc[len(df), :] = [1, 'John', 21]
df.loc[len(df), :] = [2, 'Mary', 19]
df.loc[len(df), :] = [3, 'Ann', 27]
df.loc[len(df), :] = [4, 'Ben', 18]
I want to save it to excel file using xlsxwriter.
However, I want the age larger than 20 to be in red.
In other words, 21 and 27 should appear red in the excel file.
How to do it?
You could use a conditional format with xlsxwriter like this:
import pandas as pd
# Create a Pandas dataframe from some data.
df = pd.DataFrame(columns = ['id', 'name', 'age'])
df.loc[len(df), :] = [1, 'John', 21]
df.loc[len(df), :] = [2, 'Mary', 19]
df.loc[len(df), :] = [3, 'Ann', 27]
df.loc[len(df), :] = [4, 'Ben', 18]
# 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. Light red fill with dark red text.
format1 = workbook.add_format({'bg_color': '#FFC7CE',
'font_color': '#9C0006'})
# Set the conditional format range.
start_row = 1
start_col = 3
end_row = len(df)
end_cold = start_col
# Apply a conditional format to the cell range.
worksheet.conditional_format(start_row, start_col, end_row, end_cold,
{'type': 'cell',
'criteria': '>',
'value': 20,
'format': format1})
# Close the Pandas Excel writer and output the Excel file.
writer.save()
Output:
This did not work for strings for me.
writer = pd.ExcelWriter('ProjectSummaryStatusScriptOutput.xlsx', engine='xlsxwriter')
df_to_csv.to_excel(writer, sheet_name='Sheet1')
# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets['Sheet1']
# Add a format. Light red fill with dark red text.
format1 = workbook.add_format({'bg_color': '#ffff00'})
format2 = workbook.add_format({'bg_color': '##ff0000'})
format3 = workbook.add_format({'bg_color': '#13bd00'})
# Set the conditional format range.
start_row = 2
start_col = 2
end_row = len(df)
end_cold = len(df.columns)
# Apply a conditional format to the cell range.
worksheet.conditional_format('E2:K56',
{'type': 'cell',
'criteria': '=',
'value': 'Red',
'format': format1})
worksheet.conditional_format('E2:K56',
{'type': 'cell',
'criteria': '=',
'value': 'Yellow',
'format': format2})
worksheet.conditional_format('E2:K56',
{'type': 'cell',
'criteria': '=',
'value': 'Green',
'format': format3})
# Close the Pandas Excel writer and output the Excel file.
writer.save()
My output did not have any formatting
