xlsxwriter conditional format whole line
Asked Answered
M

2

6

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:

enter image description here

Mcnamara answered 1/2, 2015 at 4:3 Comment(0)
L
6

Just change your conditional format to include the entire range, and use a 'formula' type instead of 'text'.

worksheet.conditional_format('A1:C1000', {'type': 'formula',
                                          'criteria': '=LEFT($A1, 9)="completed"',
                                          'format': completed_fmt})

enter image description here

Lexington answered 19/5, 2016 at 14:35 Comment(0)
B
1

This should not be an excel-context condition, condition should be on the entire range.

So pass the actual dataframe (df in create_sheet) by creating it before the functions , and not in create_sheet.

When you have the data in the new set_format (which has df as an argument) you can do the condition in python context rather than in excel context and apply the proper formatting.

Bratislava answered 1/2, 2015 at 4:42 Comment(1)
I wish after created this document, if use change cell A to completed, then it will change the whole line background color dynamically. If I change the color during write data, it should be a static color but can not change after user change the content in excel.Not sure if my understand is correct.Mcnamara

© 2022 - 2024 — McMap. All rights reserved.