python excel条件格式_python-xlsxwriter条件格式整行

我可以使用条件格式来设置单元格背景颜色,但是我希望根据一个单元格文本值设置整行背景.

例如：

import pandas as pd

import numpy as np

import xlsxwriter

import os

def main():

xlsfilename = os.path.expanduser("~") + "/test.xls"

writer = pd.ExcelWriter(xlsfilename,engine='xlsxwriter')

create_sheet(writer)

set_format(writer)

writer.save()

return

def create_sheet(writer):

index = np.arange(1)

df = pd.DataFrame(columns=["Col1","Col2","Col3"],index=index)

index = 0

df.ix[index] = ["completed","(1,2)","(1,3)"]

index += 1

df.ix[index] = ["pending","(2,2)","(2,3)"]

index += 1

df.ix[index] = ["discard","(3,2)","(3,3)"]

index += 1

df.to_excel(writer,sheet_name="MySheet",index=False)

return

def set_format(writer):

# Get the xlsxwriter objects from the dataframe writer object.

workbook = writer.book

worksheets = writer.sheets

completed_fmt = workbook.add_format({

'bold': False,

'border': 6,

'align': 'center',

'valign': 'vcenter',

'bg_color': '#D7E4BC',

})

for item in worksheets:

print item

worksheet = worksheets[item]

# Adjust the column width.

worksheet.set_column('A:A',10)

worksheet.set_column('B:B',10)

worksheet.set_column('C:C',10)

worksheet.data_validation('A1', {'validate': 'list',

'source': ['completed', 'pending', 'discard']})

worksheet.conditional_format('A1:A1000', {'type': 'text',

'criteria': 'begins with',

'value': 'completed',

'format': completed_fmt})

workbook.close()

return

#-----------------------------------------#

if __name__ == "__main__":

main()

#-----------------------------------------#

如果单元格为文本“完成”,则当前代码仅设置背景.但我希望如果单元格A文本“已完成”,则将A,B,C背景色设置为相同的值.

当前输出为：

解决方法:

只需将您的条件格式更改为包括整个范围,然后使用“公式”类型而不是“文本”即可.

worksheet.conditional_format('A1:C1000', {'type': 'formula',

'criteria': '=LEFT($A1, 9)="completed"',

'format': completed_fmt})

标签：xlsxwriter,python

来源： https://codeday.me/bug/20191120/2046601.html