?

周一早上九點(diǎn)，你的郵箱被各種報(bào)表塞滿(mǎn)。

打開(kāi)財(cái)務(wù)發(fā)來(lái)的季度銷(xiāo)售數(shù)據(jù)，幾千行數(shù)字?jǐn)D在屏幕上，眼睛掃過(guò)去一片黑壓壓。老板在旁邊等著匯報(bào)，問(wèn)你這個(gè)季度哪個(gè)產(chǎn)品賣(mài)得最好、哪些區(qū)域掉得厲害。你拿著鼠標(biāo)劃來(lái)劃去，半天找不出個(gè)所以然。

這場(chǎng)景，但凡用Excel干過(guò)活的人都懂——數(shù)據(jù)有了，但看不見(jiàn)。

條件格式就是干這個(gè)用的。它能讓數(shù)字“開(kāi)口說(shuō)話(huà)”：高于平均值的自動(dòng)標(biāo)綠，低于警戒線(xiàn)的自動(dòng)飄紅，重復(fù)值、排名、異常點(diǎn)一眼掃過(guò)去清清楚楚。但問(wèn)題來(lái)了，每月、每周甚至每天都要手動(dòng)重復(fù)這些操作，時(shí)間全耗在“格式化”上了。

Python能幫你解決這個(gè)麻煩。

用代碼操作Excel條件格式，本質(zhì)上就是把你手動(dòng)點(diǎn)鼠標(biāo)的步驟寫(xiě)成腳本。數(shù)據(jù)更新了，腳本跑一遍，格式自動(dòng)生成。市面上主流的幾個(gè)庫(kù)——openpyxl、xlsxwriter、Spire.XLS——各有各的脾氣，選對(duì)了工具，事半功倍。

一、openpyxl：最接地氣的全能選手

openpyxl是處理Excel最常用的庫(kù)，讀寫(xiě)都支持，對(duì)條件格式的支持也比較完善。它的設(shè)計(jì)思路很符合直覺(jué)：先定規(guī)則，再上格式。

比如你想把成績(jī)表里不及格（小于60分）的單元格標(biāo)紅，代碼可以這么寫(xiě)：

from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule

wb = load_workbook('成績(jī)表.xlsx')
ws = wb.active

# 定義紅色填充
red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')

# 添加條件格式規(guī)則：?jiǎn)卧裰敌∮?0
ws.conditional_formatting.add('B2:B100', 
                              CellIsRule(operator='lessThan', 
                                        formula=['60'], 
                                        fill=red_fill))

wb.save('成績(jī)表_格式化.xlsx')

這段代碼干的事，和你手動(dòng)點(diǎn)“條件格式>突出顯示單元格規(guī)則>小于”一模一樣，區(qū)別在于——下次再來(lái)100份報(bào)表，它也能一秒干完。

openpyxl支持的規(guī)則類(lèi)型不少：CellIsRule（基于值）、FormulaRule（基于公式）、ColorScaleRule（色階）、IconSetRule（圖標(biāo)集）、DataBarRule（數(shù)據(jù)條）都有。想做復(fù)雜點(diǎn)的邏輯，比如“既要大于平均線(xiàn)，又要屬于前10%”，用公式規(guī)則就能搞定。

有個(gè)坑得提醒你：openpyxl在讀取帶條件格式的現(xiàn)有文件時(shí)，規(guī)則信息可能會(huì)丟失。所以用它創(chuàng)建格式?jīng)]問(wèn)題，但別指望完美讀取別人設(shè)好的規(guī)則。

二、xlsxwriter：寫(xiě)報(bào)表的一把好手

如果你只需要“生成”報(bào)表，不需要修改已有文件，xlsxwriter是更輕量級(jí)的選擇。它專(zhuān)注于寫(xiě)入，功能純粹，性能也不錯(cuò)。

xlsxwriter的語(yǔ)法有點(diǎn)不一樣，用的是字典傳參：

import xlsxwriter

workbook = xlsxwriter.Workbook('銷(xiāo)售報(bào)表.xlsx')
worksheet = workbook.add_worksheet()

# 準(zhǔn)備數(shù)據(jù)
data = [320, 450, 280, 490, 350, 420, 380]
worksheet.write_column('A1', data)

# 定義兩種格式
green_format = workbook.add_format({'bg_color': 'green'})
red_format = workbook.add_format({'bg_color': 'red'})

# 設(shè)置條件格式：大于400標(biāo)綠，小于等于400標(biāo)紅
worksheet.conditional_format('A1:A7', {'type': 'cell',
                                        'criteria': '>',
                                        'value': 400,
                                        'format': green_format})
worksheet.conditional_format('A1:A7', {'type': 'cell',
                                        'criteria': '<=',
                                        'value': 400,
                                        'format': red_format})

workbook.close()

xlsxwriter最實(shí)用的功能之一是配合pandas用。你拿pandas做完數(shù)據(jù)處理，直接用pd.ExcelWriter指定xlsxwriter引擎，然后調(diào)用條件格式方法，數(shù)據(jù)分析+報(bào)表生成一條龍。

import pandas as pd

df = pd.DataFrame({'銷(xiāo)售額': [320, 450, 280, 490, 350]})

writer = pd.ExcelWriter('報(bào)表.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='銷(xiāo)售數(shù)據(jù)', index=False)

workbook = writer.book
worksheet = writer.sheets['銷(xiāo)售數(shù)據(jù)']

# 對(duì)B列（銷(xiāo)售額）應(yīng)用三色階
worksheet.conditional_format('B2:B6', {'type': '3_color_scale'})

writer.close()

xlsxwriter不支持讀取已有文件，也不能修改。但如果你是從零開(kāi)始建報(bào)表，它夠快、夠穩(wěn)、夠干凈。

三、Spire.XLS：商業(yè)級(jí)功能的代表

Spire.XLS是個(gè)商業(yè)庫(kù)，功能覆蓋面比前兩個(gè)更廣。它的API設(shè)計(jì)更接近Excel本身的邏輯，支持的操作也更多——比如設(shè)置公式條件、處理跨工作表引用、精細(xì)化控制格式選項(xiàng)。

有些場(chǎng)景下，用前兩個(gè)庫(kù)實(shí)現(xiàn)起來(lái)比較費(fèi)勁，比如“隔行變色”這種需求。用Spire.XLS可以寫(xiě)得很直白：

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("數(shù)據(jù).xlsx")
sheet = workbook.Worksheets[0]

# 添加條件格式
conditionalFormat = sheet.ConditionalFormats.Add()
conditionalFormat.AddRange(sheet.Range[2, 1, sheet.LastRow, sheet.LastColumn])

# 偶數(shù)行設(shè)白色背景
condition1 = conditionalFormat.AddCondition()
condition1.FirstFormula = "=MOD(ROW(),2)=0"
condition1.FormatType = ConditionalFormatType.Formula
condition1.BackColor = Color.get_White()

# 奇數(shù)行設(shè)淺灰背景
condition2 = conditionalFormat.AddCondition()
condition2.FirstFormula = "=MOD(ROW(),2)=1"
condition2.FormatType = ConditionalFormatType.Formula
condition2.BackColor = Color.get_LightGray()

workbook.SaveToFile("隔行變色.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Spire.XLS還支持一些“開(kāi)箱即用”的高級(jí)功能：Top/Bottom規(guī)則、高于/低于平均值、介于兩個(gè)數(shù)之間。這些在Excel里是內(nèi)置選項(xiàng)，用openpyxl得繞個(gè)彎，用Spire.XLS直接調(diào)方法就行。

不過(guò)它是收費(fèi)庫(kù)，有30天試用期。項(xiàng)目預(yù)算夠、追求開(kāi)發(fā)效率的情況下可以考慮。

四、怎么選？看你的場(chǎng)景

三個(gè)庫(kù)沒(méi)有絕對(duì)的好壞，關(guān)鍵看你想干什么。

openpyxl最適合“既要讀又要寫(xiě)”的場(chǎng)景。你需要修改現(xiàn)有文件、保留原有內(nèi)容、同時(shí)添加新格式，用它最穩(wěn)妥。社區(qū)活躍，遇到問(wèn)題好搜答案。

xlsxwriter適合“純生成”的場(chǎng)景。跑數(shù)據(jù)分析腳本，自動(dòng)輸出帶格式的報(bào)表，發(fā)給老板或客戶(hù)。性能好，文件體積控制得也不錯(cuò)。配合pandas用，體驗(yàn)很順。

Spire.XLS適合復(fù)雜格式需求的場(chǎng)景。要做多條件嵌套、大量使用公式規(guī)則、或者嫌自己造輪子太麻煩。商業(yè)環(huán)境、預(yù)算允許的情況下，能省不少開(kāi)發(fā)時(shí)間。

五、幾個(gè)實(shí)戰(zhàn)技巧

• 公式規(guī)則里，引用要寫(xiě)對(duì)。在條件格式里用公式，注意相對(duì)引用和絕對(duì)引用。=A1>100是針對(duì)每個(gè)單元格判斷，=A$1>100就變成全跟第一行比。
• 規(guī)則有順序。Excel按規(guī)則添加順序執(zhí)行，后面的規(guī)則可能覆蓋前面的。代碼里添加規(guī)則的順序，就是最終生效的順序。
• 別貪多。一個(gè)工作表里規(guī)則太多，文件打開(kāi)會(huì)卡。能用一兩條規(guī)則解決的，別繞復(fù)雜邏輯。
• 先清空再添加。如果反復(fù)運(yùn)行腳本，記得先刪掉舊規(guī)則再添新的，不然會(huì)疊加上去，結(jié)果亂套。

回到開(kāi)頭那個(gè)周一早上的場(chǎng)景。

用Python寫(xiě)完條件格式腳本之后，你只需要把新季度數(shù)據(jù)拖進(jìn)文件夾，雙擊運(yùn)行。再打開(kāi)Excel時(shí)，銷(xiāo)售冠軍已經(jīng)標(biāo)成綠色，下滑區(qū)域標(biāo)成橙色，異常值標(biāo)成紅色。老板指著屏幕問(wèn)“這個(gè)月怎么回事”，你三秒就能找到問(wèn)題出在哪。

那多出來(lái)的半小時(shí)，不用再對(duì)著黑壓壓的數(shù)字發(fā)呆。

以上就是Python代碼操作Excel條件格式的實(shí)戰(zhàn)指南的詳細(xì)內(nèi)容，更多關(guān)于Python操作Excel條件格式的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論