前陣子同事甩給我一堆周報讓我用openpyxl讀取工作簿里的銷售數(shù)據(jù)再按城市匯個總。他以為這活兒至少得折騰一下午結果我花十分鐘寫了個腳本吃頓飯的功夫就跑完了。如果你也經(jīng)常被Excel表格折磨或者想把手動復制粘貼的活兒自動化openpyxl這個庫值得認真了解。它不是什么冷門工具而是Python生態(tài)里處理xlsx文件的主力庫讀取工作簿、讀取單元格、批量遍歷、處理合并單元格一套流程下來基本不會卡殼。這篇文章我就圍繞讀取工作簿展開把加載文件、定位工作表、讀單元格、踩過的坑、大文件優(yōu)化這些內(nèi)容一次講透連著代碼和注釋一并給到你可以直接拿去改。1. 為什么處理Excel我選openpyxl這個庫1.1 對比pandas、xlrd、xlwingsopenpyxl贏在哪遇到Excel處理很多人的第一反應是pandas但我不建議一上來就開pandas。pandas的read_excel確實方便一行代碼能把工作表讀成DataFrame可它是個重型庫底層還要依賴其他解析器而且一旦涉及合并單元格、公式緩存、樣式保留、列格式識別這些細節(jié)pandas的可控性反而不好。尤其是當你只是想“打開工作簿、按行讀取、按條件過濾”的時候pandas屬于殺雞用牛刀還會讓腳本體積變大、啟動變慢。xlrd這個庫曾經(jīng)是讀取xlsx的主流選擇但新版xlrd已經(jīng)轉(zhuǎn)向只支持xls老格式xlsx的讀取被移除所以新項目再用它處理xlsx很容易踩版本坑。xlwings則是另一種思路它本質(zhì)上是調(diào)用本機安裝的Excel程序來操作文件功能強大但依賴GUI環(huán)境在服務器或者沒裝Office的機器上根本跑不起來自動化場景里非常受限。openpyxl的優(yōu)勢在于純Python實現(xiàn)不依賴Excel組件能讀能寫能處理樣式、公式、圖表、合并單元格、批注等細節(jié)而且對xlsx這種基于XML的壓縮格式解析得很穩(wěn)定。對“讀取工作簿”這種需求來說它是最穩(wěn)妥的選擇。我做文件數(shù)據(jù)處理的時候十次里有八次會選openpyxl收尾剩下兩次是因為對方給的是xls老文件才考慮先用工具轉(zhuǎn)個格式。1.2 安裝與版本別小看環(huán)境準備這步安裝很簡單直接pip搞定pip install openpyxl如果你在公司內(nèi)網(wǎng)環(huán)境可能需要換到內(nèi)部鏡像源這個按你們自己的網(wǎng)絡設置來。裝完之后可以在Python里確認一下版本python -c import openpyxl; print(openpyxl.__version__)建議用3.1以上版本早期的2.x版本雖然也能用但某些API表現(xiàn)不一樣比如合并單元格范圍的處理方式就改過。順便提一句openpyxl只認xlsx、xlsm這類基于Office Open XML格式的文件老版xls它是打不開的報錯也不是很友好。你要是收到的是.xls要么讓上游重新導出為.xlsx要么先用其他工具轉(zhuǎn)一下再跑腳本。2. 讀取工作簿的基本盤load_workbook與工作表導航2.1 load_workbook的完整參數(shù)認知讀取工作簿的入口是load_workbook我第一次用的時候只傳了個文件名后來才發(fā)現(xiàn)這函數(shù)的參數(shù)里藏著不少坑from openpyxl import load_workbook wb load_workbook(某銷售匯總.xlsx)默認情況下這個調(diào)用會把整個xlsx解析進內(nèi)存所有工作表、所有單元格對象都會被創(chuàng)建。如果文件不大感覺不到差異等你哪天拿到一個幾十MB的大表格就會明白普通模式到底有多吃內(nèi)存。load_workbook還有幾個關鍵參數(shù)read_only設置為True會進入只讀流式模式一行一行從文件里讀數(shù)據(jù)內(nèi)存占用大幅下降代價是部分高級操作不可用。后面我會專門展開講。data_only這個參數(shù)特別容易誤解它不是“只讀取數(shù)據(jù)”的意思而是“是否只讀取公式的緩存結果”。默認False拿到的是公式字符串比如“SUM(A1:A3)”設為True拿到的才是Excel計算后保存下來的數(shù)值。如果公式從沒被Excel真正計算過data_onlyTrue反而會返回None。keep_vba處理xlsm宏文件時使用如果你只想讀數(shù)據(jù)保持默認False就行。keep_links控制是否保留外部鏈接信息默認True。如果你的工作表里引用了別的工作簿加載時會嘗試解析這些鏈接某些文件會導致加載變慢純粹讀內(nèi)容的話可以設成False。除了傳文件路徑load_workbook也可以接收一個二進制流對象比如從內(nèi)存里讀from io import BytesIO from openpyxl import load_workbook data bytes_io.getvalue() wb load_workbook(BytesIO(data))這個技巧在做接口對接、文件上傳處理的時候很實用不用把臨時文件寫到磁盤上再讀。2.2 從工作簿到工作表再到單元格三步定位數(shù)據(jù)拿到工作簿對象之后第一件事不是急著讀單元格而是先摸清楚里面有幾張表、每張表叫什么。print(wb.sheetnames) # 全部工作表名稱列表 ws wb.active # 默認激活的工作表很多人習慣直接用wb.active但我要提醒一句active不一定永遠是第一個工作表它表示的是“上次打開時激活的那張表”有時候文件處理邏輯變了active也會跟著變。想穩(wěn)定定位某張表最好用表名索引ws wb[月度匯總]如果表名是動態(tài)的可以遍歷sheetnames挨個處理for name in wb.sheetnames: ws wb[name] print(name, ws.max_row, ws.max_column)這里有個常用技巧先打印每個sheet的行列范圍快速判斷表是不是空表、數(shù)據(jù)區(qū)域在哪再決定怎么處理。我做啥腳本都習慣先來這段“偵察代碼”省得后面讀取時對不上數(shù)據(jù)范圍。單元格的定位方式也很直接。你可以按Excel坐標寫cell ws[A1] print(cell.value)也可以按行列索引寫cell ws.cell(row1, column1)注意openpyxl的行列編號是從1開始的不是從0開始跟Python里list的索引習慣不一樣用錯的話讀到的值會整體偏移一行或一列。工作表本身還帶一些元信息偶爾也會用到ws.title # 表名 ws.sheet_state # visible / hidden / veryHidden ws.freeze_panes # 凍結窗格位置讀取工作簿不只是讀值表的隱藏狀態(tài)、凍結窗格這些信息也是可以在自動化流程里利用的比如跳過隱藏表不處理。3. 讀取單元格的幾種實用姿勢索引、切片與批量遍歷3.1 用字母坐標還是行列索引讀取單個單元格最直觀的方式就是字母坐標。value ws[B2].value這種方式適合快速讀取固定位置的字段比如表頭在某一行、匯總值在某個固定單元格。但如果要循環(huán)處理整張表我更推薦用cell(rowrow, columncol)這種索引方式col_index 2 total 0 for i in range(1, ws.max_row 1): value ws.cell(rowi, columncol_index).value if isinstance(value, (int, float)): total value行列索引的好處是可以用變量控制列號比如動態(tài)匹配某個字段在第幾列這在多張表結構不一致的場景里非常管用。有時候你還需要在字母坐標和數(shù)字索引之間轉(zhuǎn)換openpyxl已經(jīng)給了工具函數(shù)from openpyxl.utils import get_column_letter, column_index_from_string print(get_column_letter(2)) # B print(column_index_from_string(B)) # 2比如別人給的Excel里表頭是“銷售額”但不知道它排在AB列還是CD列這時候可以先遍歷表頭用column_index_from_string把列字母轉(zhuǎn)成數(shù)字再統(tǒng)一按數(shù)字取數(shù)據(jù)。如果你想一口氣拿一整列或一整行openpyxl也支持類似切片的方式col_cells ws[A] # A列從第一行到已使用區(qū)域末尾的所有Cell對象 row_cells ws[1] # 第一行的Cell對象拿到的是一組Cell對象用的時候記得取它的.value屬性。這種寫法比較省事但可讀性不如iter_rows清晰我建議新手還是優(yōu)先掌握行列索引和遍歷方法。3.2 批量遍歷iter_rows、rows與values_only讀取工作簿的高頻場景其實是“整張表讀出來”這時候一個單元格一個單元格取太慢了用iter_rows最順手for row in ws.iter_rows(min_row1, max_rowws.max_row, min_col1, max_colws.max_col, values_onlyTrue): print(row)iter_rows返回的是一個生成器每輪迭代給出一行數(shù)據(jù)。如果不寫values_onlyTrue它返回的是這一行所有Cell對象你拿到后還得再取.value設置了values_onlyTrue直接就是單元格的值元組處理起來干凈得多。如果你嫌參數(shù)太長還有個等價捷徑for row in ws.iter_rows(): pass省略參數(shù)時它會按工作表的實際使用范圍遍歷所有行。但在某些情況下Excel里的“使用范圍”可能比你真實數(shù)據(jù)范圍更大比如有人刪除過數(shù)據(jù)但沒保存干凈導致max_row比實際行數(shù)多很多。這種時候最好自己設定min_row和max_row別全依賴默認值。等價的還有ws.rows和ws.valuesrows ws.rows # 生成器每個item是一行的Cell對象元組 values ws.values # 生成器每個item是一行的值元組注意ws.values在普通模式下會一次性把整個工作表的值迭代完適合小表。對大表流式配合iter_rows更安全下面會專門講。還有一個實用組合先把整個表變成值列表再處理。data list(ws.values)這個寫法很直觀但只推薦用在數(shù)據(jù)量不大、內(nèi)存隨便吃的情況下。一個幾萬行的表轉(zhuǎn)成list通常沒多大問題但如果你確定文件有幾十萬行就別這么寫很容易把內(nèi)存撐爆。3.3 區(qū)域切片讀取與合并單元格的連帶處理除了正向遍歷openpyxl支持像Excel公式一樣按區(qū)域切片region ws[A1:C3] print(region) # ((A1, B1, C1), (A2, B2, C2), (A3, B3, C3))返回的是元組套元組每一行是一個元組里面是Cell對象。這種寫法適合讀取表頭區(qū)域或者某個固定報表區(qū)塊。真正麻煩的是合并單元格。openpyxl讀取合并單元格時只有左上角那個單元格有值其他區(qū)域返回的是None。比如A1:B2合并后B1、A2、B2的值都是None只保留A1有值。這在讀報表時是非常容易踩的坑。要識別合并區(qū)域可以這樣for merge_range in list(ws.merged_cells.ranges): print(merge_range.min_row, merge_range.min_col, merge_range.max_row, merge_range.max_col)老版本的openpyxl直接遍歷ws.merged_cells也能得到類似信息新版統(tǒng)一推薦使用merged_cells.ranges。如果你的表格里有合并單元格而且你需要每個單元格都有值可以自己做一個“填充合并區(qū)域”的小函數(shù)def fill_merged(ws): for mr in list(ws.merged_cells.ranges): top_left ws.cell(rowmr.min_row, columnmr.min_col).value for r in range(mr.min_row, mr.max_row 1): for c in range(mr.min_col, mr.max_col 1): ws.cell(rowr, columnc).value top_left這樣處理后原來為None的單元格會被填上左上角的值。注意如果你后面還要保存這個工作簿這個函數(shù)其實改變了單元格格式它會破壞合并單元格結構。所以我一般不是真的去改原表而是把值讀到一個新的數(shù)據(jù)結構里比如所有行都變成統(tǒng)一的dict列表再繼續(xù)做匯總。4. 真實數(shù)據(jù)讀取中的三個經(jīng)典坑與應對姿勢4.1 data_onlyTrue卻取不到公式結果這是我接到求助最多的問題之一。有位同學用openpyxl讀一個帶公式的報表load_workbook時特意加了data_onlyTrue結果某些單元格讀出來還是None他一度懷疑是openpyxl的bug。其實openpyxl本身不會計算公式。當Excel在保存xlsx文件時會把每個公式最近一次計算的結果作為緩存一起存進去。data_onlyTrue只是告訴openpyxl“你去讀那個緩存值”而不是“你幫我重新算一遍”。如果這個xlsx是由openpyxl直接創(chuàng)建的里面公式壓根沒被Excel打開計算過那文件里就沒有緩存值data_onlyTrue自然讀到None。解決辦法有幾種讓上游用Excel打開文件并按一次保存把公式結果緩存刷新到文件里。如果只是臨時用用LibreOffice這類可命令行啟動的辦公軟件在服務器上重新計算并保存文件。自己寫邏輯兜底先以data_onlyTrue讀取遇到None再用data_onlyFalse打開一次拿到公式字符串再做后續(xù)處理。我常用的兜底思路是這樣def get_cell_value(file_path, cell_ref): wb_value load_workbook(file_path, data_onlyTrue) ws_value wb_value.active val ws_value[cell_ref].value if val is None: wb_formula load_workbook(file_path, data_onlyFalse) ws_formula wb_formula.active val ws_formula[cell_ref].value return val注意這種寫法對單個固定單元格是夠用的如果要遍歷整張表最好一次打開兩個workbook同時維護兩套值按單元格判斷別每次都重復加載文件。4.2 合并單元格帶來的空值前面已經(jīng)提過合并單元格會導致其他區(qū)域變成None但在真實數(shù)據(jù)分析里這個問題經(jīng)常結合遍歷出現(xiàn)你的數(shù)據(jù)明明看起來完整打印出來卻全是None。比如有些報表把“部門”列按多行合并只保留第一行的值后面的行都是空。如果你直接用openpyxl讀取不做處理后面這些空值就會被當成缺失數(shù)據(jù)。遇到這種情況我的處理思路是先用區(qū)間識別合并區(qū)域再按上一行的值向下填充。也就是把合并單元格的左上角值“往下傳”。下面是一個處理豎排列合并的示例for mr in list(ws.merged_cells.ranges): if mr.min_col mr.max_col: # 同列上下合并 fill_value ws.cell(rowmr.min_row, columnmr.min_col).value for r in range(mr.min_row 1, mr.max_row 1): ws.cell(rowr, columnmr.min_col).value fill_value這種處理很適合“部門列合并了但明細行需要顯示部門名”的場景。做完之后再按行遍歷數(shù)據(jù)就是完整的。還有一個隱藏問題是合并單元格如果出現(xiàn)在表頭區(qū)域可能會導致表頭字段名讀取不上來。比如第一行和第二行合并過但你用iter_rows默認遍歷第二行的表頭字段就是None。遇到這個建議只讀取合并區(qū)域左上角那一行作為表頭或者先把表頭區(qū)域按前面fill_merged方法鋪平。4.3 數(shù)字、日期與百分比的類型識別openpyxl讀取單元格時會自動映射數(shù)據(jù)類型但有些映射結果跟Excel里“看到的內(nèi)容”不一樣這也是很多人讀出來之后發(fā)現(xiàn)對不上的原因。日期時間類型在工作表里會被讀成datetime對象from datetime import datetime cell_val ws[B2].value if isinstance(cell_val, datetime): print(cell_val.strftime(%Y-%m-%d))如果單元格存的是數(shù)字openpyxl默認會保留數(shù)字類型不會給你轉(zhuǎn)成字符串。但有一種情況要注意Excel里如果單元格是“文本”格式里面的數(shù)字會被存成字符串“123”而不是數(shù)字123。讀出來之后做加法時就會報錯。我一般在拿數(shù)字之前都做一層防御def to_float(value): if value is None: return None if isinstance(value, (int, float)): return float(value) if isinstance(value, str): text value.replace(,, ).replace(, ).strip() try: return float(text) except ValueError: return None return None百分比的坑更深。Excel里顯示“12.5%”的單元格openpyxl讀出來的value其實是0.125不是字符串“12.5%”。如果你直接格式化輸出會變成“0.125”跟原表對不上。要用單元格的number_format來判斷value ws[C3].value fmt ws[C3].number_format # 類似 0.0% if % in str(fmt) and isinstance(value, (int, float)): display_value value * 100同樣的道理還適用于千分位、貨幣符號等格式openpyxl讀取的是原始存儲值不是Excel的顯示值。如果你要復現(xiàn)“用戶看到的數(shù)字”必須結合number_format做二次加工。5. 大文件與異常環(huán)境性能優(yōu)化和安全防護5.1 read_only模式只讀模式到底省了多少內(nèi)存普通模式加載工作簿時openpyxl會把整個文件內(nèi)容解析成內(nèi)存里的對象逐行、逐單元格都創(chuàng)建Cell對象。文件小的時候無所謂一旦遇到幾萬行幾十列甚至更大規(guī)模的表普通模式能讓你的腳本內(nèi)存占用直線上漲。解決辦法是load_workbook時打開read_only模式wb load_workbook(大文件.xlsx, read_onlyTrue) ws wb.active for row in ws.iter_rows(values_onlyTrue): # 處理這一行 passread_only模式采用的是流式讀取思路不會一次性把整張表都加載到內(nèi)存里而是一行一行從底層XML文件里解析出來。配合values_onlyTrue一起使用性能表現(xiàn)會更好因為values_onlyTrue直接返回值不會去創(chuàng)建完整的Cell對象。但只讀模式也有明顯的代價。首先你不能再像普通模式那樣方便地按坐標隨機讀取某個單元格最穩(wěn)妥的方式就是從頭到尾遍歷行其次一些高級操作比如修改后保存、處理合并單元格、調(diào)整樣式都是不可用的第三讀取過程中對工作表的約束邏輯比普通模式松不適合做需要精確解析格式的場景。我自己的選擇標準很簡單文件行數(shù)在幾千行以內(nèi)普通模式隨便用超過幾萬行或者明確知道文件很大毫不猶豫開read_only模式。還有一點read_only模式下千萬不要因為圖方便就寫data list(ws.values)這會把流式讀取的好處全丟掉等于又把整表加載進了內(nèi)存。5.2 防御式代碼文件不存在、權限異常與空表我是越來越認同一個觀點腳本的報錯信息要能說人話。openpyxl如果遇到非法文件拋出來的異常一般比較底層比如BadZipFile新手看到會懵。所以正式腳本里我會包一層異常處理from pathlib import Path from zipfile import BadZipFile from openpyxl import load_workbook from openpyxl.exceptions import InvalidFileException def load_workbook_safe(file_path): path Path(file_path) if not path.exists(): raise FileNotFoundError(f文件不存在: {path.resolve()}) try: wb load_workbook(path, read_onlyTrue, data_onlyTrue) except BadZipFile: raise ValueError(文件不是有效的xlsx格式可能被損壞或不是xlsx文件) except InvalidFileException: raise ValueError(openpyxl無法識別該文件請確認擴展名為.xlsx/.xlsm) except PermissionError: raise PermissionError(文件權限異常請檢查是否被占用或沒有讀取權限) if len(wb.sheetnames) 0: raise ValueError(工作簿里沒有任何工作表) return wb這段代碼里有一個容易被忽略的點報錯信息要盡量明確。我在實際工作中就遇到過一個文件被另一個進程占用導致讀取權限異常的情況如果不去捕獲PermissionError默認報錯看著就像文件壞掉一樣排查方向完全跑偏。除了文件層面的異?？毡硪惨龇烙?。有些自動化流程會生成內(nèi)容為空的xlsx但工作表還在read_only模式遍歷時可能連表頭都取不到。這種場景我一般會在循環(huán)外先加判斷if ws.max_row 2: print(這個工作表只有一行或沒有數(shù)據(jù)跳過) continue還有一個安全細節(jié)openpyxl不支持加密保護的xlsx。如果你拿到一個帶打開密碼的文件load_workbook直接會報錯。這個不是openpyxl的bug是它版本里就沒有實現(xiàn)加密文件的解析。遇到加密文件需要讓上游先解密或者用支持密碼的工具另存一份再處理。6. 實戰(zhàn)把12個月度報表合并成一個結構化結果6.1 需求與數(shù)據(jù)樣張結合上面的知識點我來還原一個真實的讀取工作簿小任務。某數(shù)據(jù)小組每個月會收到一份銷售周報工作簿名是“年度銷售周報.xlsx”里面有12張工作表分別叫“第1周”“第2周”一直到“第12周”。每張表的行數(shù)可能不一樣列也不完全對齊但至少都有“城市”“銷售額”“日期”這三個字段。任務是把所有周報的數(shù)據(jù)讀出來按城市匯總銷售額輸出一個摘要字典。先模擬一下數(shù)據(jù)結構。工作表“第1周”大致長這樣城市銷售額日期A市12800.52025-03-01B市96322025-03-01C市104202025-03-01A市87002025-03-02另一張“第2周”可能城市列不在A列日期列也可能有“2025/03/08”這種格式。這種“表結構不固定”才是真實世界最常見的Excel問題。6.2 核心實現(xiàn)代碼下面這個腳本就是把前面的知識串起來用表名導航、用iter_rows遍歷、按表頭映射列索引、對空行跳過、對字符串數(shù)字做轉(zhuǎn)換。from openpyxl import load_workbook from collections import defaultdict from datetime import datetime def parse_weekly_file(file_path): wb load_workbook(file_path, data_onlyTrue, read_onlyFalse) summary defaultdict(lambda: {amount: 0.0, count: 0}) for sheet_name in wb.sheetnames: ws wb[sheet_name] rows ws.iter_rows(values_onlyTrue) try: header next(rows) except StopIteration: print(f跳過空表: {sheet_name}) continue header_map {} for idx, name in enumerate(header): if name is not None: header_map[str(name).strip().lower()] idx if 城市 not in header_map or 銷售額 not in header_map: print(f跳過 {sheet_name}: 缺少必要列實際列名 {list(header_map.keys())}) continue city_idx header_map[城市] amount_idx header_map[銷售額] date_idx header_map.get(日期) for row in rows: if all(item is None for item in row): continue city str(row[city_idx]).strip() if row[city_idx] is not None else 未填寫 amount_raw row[amount_idx] amount to_float(amount_raw) if amount is None: continue summary[city][amount] amount summary[city][count] 1 if date_idx is not None: date_value row[date_idx] if isinstance(date_value, datetime): pass return summary def to_float(value): if value is None: return None if isinstance(value, (int, float)): return float(value) if isinstance(value, str): text value.replace(,, ).replace(, ).strip() try: return float(text) except ValueError: return None return None if __name__ __main__: result parse_weekly_file(年度銷售周報.xlsx) for city, info in result.items(): print(city, info)這里我特意沒有引入pandas因為如果你只需要讀完數(shù)據(jù)并匯總用openpyxl加基礎數(shù)據(jù)結構就夠了依賴少、部署簡單。代碼里header_map的作用很關鍵它會把每一列的表頭轉(zhuǎn)成小寫再映射索引這樣不同周報表頭順序不一致時也能自動定位到正確的列。再補充一個細節(jié)data_onlyTrue這里是為了讀取銷售額的緩存值。如果銷售額列本身是手工填的數(shù)字不帶公式用data_onlyFalse其實也能得到正確結果。但為了保險我依然寫了data_onlyTrue因為一旦某些行的銷售額是公式算出來的不加的話你會讀到一個公式字符串后面轉(zhuǎn)float就會報錯。6.3 運行結果與小坑回顧跑完腳本后輸出大概是這樣A市 {amount: 158320.0, count: 12} B市 {amount: 147500.0, count: 11} C市 {amount: 129800.0, count: 10}看上去很順利但這個腳本在調(diào)試過程中踩過兩個小坑。第一個坑是某周報表的城市列里出現(xiàn)了合并單元格A市下面的幾行城市字段為None直接被腳本歸類成了“未填寫”。這個問題我用前面說的合并區(qū)域識別思路解決先判斷merged_cells.ranges把合并后的空值按左上角值填充再做后續(xù)處理。如果你遇到類似情況記得在進入iter_rows之前先做一次合并值鋪平操作。第二個坑是某張表里銷售額列有些單元格被存成了文本里面帶了千分位逗號比如“12,800.5”。直接用float會報錯我在to_float函數(shù)里做了replace(,, )這才把數(shù)據(jù)救回來。別覺得這種數(shù)據(jù)規(guī)范問題少見真實業(yè)務里各種手填表格什么格式都有。最后分享一個我的習慣寫讀取腳本時先在主邏輯后面加一段“表結構概覽”輸出打印每張工作表的表名、max_row和字段順序。這樣即使腳本后面跑出奇怪結果第一反應是去看數(shù)據(jù)本身而不是懷疑讀取邏輯。很多bug其實就是源數(shù)據(jù)的結構跟你預想的不一樣先摸清底細再動手解析能省下一大半排查時間。