I can use conditional format to set the cell background color, but I wish to set the whole line background according to one cell text value.
for example:
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()
#-----------------------------------------#
Current code only set A background if cell is text "completed". but I wish if cell A text is "completed", then set A,B,C background color the same value.
Current output is: