生信技能20 - 生信分析结果文件Excel表的单元格自动化着色-优快云博客

通过Python实现excel表指定内容单元格着色，在输出文件中某列值超过标准范围或内容需要重点关注时，可以使用如下代码实现excel表内容的自动着色。

python代码

import openpyxl
import openpyxl.styles
from openpyxl.styles import PatternFill

# .xlsx文件列名称
list_columns = ['Chr',
                'Start',
                'End',
                'Ref',
                'Alt',
                'Func.refGene',
                'Gene.refGene',
                'GeneDetail.refGene',
                'ExonicFunc.refGene',
                'AAChange.refGene',
                'cytoBand',
                'CLNALLELEID',
                'CLNDN',
                'CLNDISDB',
                'CLNREVSTAT']
col_index = dict(zip(list_columns, list(range(1, len(list_columns) + 1))))

# 添加颜色函数
def add_color(file_path, output_path):
	  # 读取xlsx工作薄
    wb = openpyxl.load_workbook(file_path)
    # 读取sheet工作表
    sheet_name = wb.sheetnames
    sheet = wb[sheet_name[0]]
		
		# 定义着色颜色，可以自行修改颜色十六进制号
    fille_red = PatternFill("solid", fgColor="FF0000")
    fille_orange_low = PatternFill("solid", fgColor="FAD7A0")
    fille_orange_median = PatternFill("solid", fgColor="F39C12")
    fille_orange_high = PatternFill("solid", fgColor="D35400")
    fille_green_low = PatternFill("solid", fgColor="52BE80")
    for row in range(2, sheet.max_row + 1):
        # Alt
        col = col_index['Alt']
        alt_base = sheet.cell(row, col).value
        # 将Alt列全部着色为红色
        sheet.cell(row, col).fill = fille_red

        # Ref
        col = col_index['Ref']
        base = sheet.cell(row, col).value
        if base == 'A':
            sheet.cell(row, col).fill = fille_red
        elif base == 'T':
            sheet.cell(row, col).fill = fille_orange_high
        elif base == 'G':
            sheet.cell(row, col).fill = fille_orange_median
        elif base == 'C':
            sheet.cell(row, col).fill = fille_orange_low

        # ExonicFunc.refGene
        col = col_index['ExonicFunc.refGene']
        refGene = sheet.cell(row, col).value
        # 如果为exonic， 则将单元格着色为红色
        if refGene == 'exonic':
            sheet.cell(row, col).fill = fille_red

        # CLNDN
        col = col_index['CLNDN']
        result = sheet.cell(row, col).value
        # 如果not_provided在单元格中，则着色为绿色
        if 'not_provided' in result:
            sheet.cell(row, col).fill = fille_green_low
            
	  # 保存为新的xlxs表
    wb.save(output_path)


if __name__ == '__main__':
    input_file_path = "snpanno.hg19_multianno.xlsx"
    output_file_path = "snpanno.hg19_multianno_addcolor.xlsx"
    
		# 执行add_color函数
    add_color(input_file_path, output_file_path)