How to highlight the rows and columns in the output file?

Question

from openpyxl import load_workbook, Workbook

def find(wb, string):
res = []
for ws in wb:
    for row in ws.values:
        for value in row:
            if value is not None and string in 
str(value):
                res.append(value)
return res

if __name__ == '__main__':
wb = load_workbook("C:\Book1.xlsx")
values = find(wb, "NAS019")

wb = Workbook()
ws = wb.active
for value in values:
    ws.append([value])
wb.save(filename="C:\Book2.xlsx")

Basically, I just want the output file to have highlighted rows and columns. So, when you open the output file all the information should be highlighted.

Answer 1

you can use the ws.cell(row+1, 1).fill along with the append to add colors to the cell you are updating. Patternfill in openpyxl allows you to set cell background color. The updated code is below.

from openpyxl import load_workbook, Workbook

def find(wb, string):
    res = []
    for ws in wb:
        for row in ws.values:
            for value in row:
                if value is not None and string in str(value):
                    res.append(value)
    return res

if __name__ == '__main__':
#    wb = load_workbook("C:\Book1.xlsx")
    values = find(wb, "NAS019")
    print(values)
    wb = Workbook()
    ws = wb.active
    from openpyxl.styles import Color, PatternFill ## Import library
    fillColor = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') ##I have set the color to yellow, you can change as you need
    for row, value in enumerate(values):
        ws.append([value])
        ws.cell(row+1, 1).fill = fillColor ##Set the color of cell
    wb.save(filename="C:\Book2.xlsx")