簡介本資源是一套面向Python初學(xué)者與數(shù)據(jù)處理入門者的Excel操作實(shí)戰(zhàn)腳本集聚焦解決日常辦公中批量讀取、篩選、寫入Excel文件的核心需求。壓縮包共4個Python源文件總大小僅6KB輕量易學(xué)全部為可直接運(yùn)行的.py腳本涵蓋基礎(chǔ)讀取readExcel.py、數(shù)值型數(shù)據(jù)專項處理readExcelNumber.py、JSON格式交互式讀取readExcelJS.py以及讀寫一體化流程readWriteExcel.py覆蓋pandas與openpyxl雙庫典型用法。已有704人學(xué)習(xí)下載適合希望快速掌握Excel自動化處理、避免重復(fù)手工操作的辦公人員、數(shù)據(jù)分析新人及課程實(shí)踐者。讀者可即刻復(fù)用代碼結(jié)構(gòu)理解DataFrame條件篩選、to_excel參數(shù)控制、openpyxl單元格遍歷與工作簿創(chuàng)建等關(guān)鍵邏輯并基于示例拓展復(fù)雜場景如樣式設(shè)置、多表寫入與錯誤處理。1. 一個連 Excel 表頭都讀錯的 Python 腳本怎么在三天內(nèi)變成產(chǎn)線級數(shù)據(jù)搬運(yùn)工上周幫某高校實(shí)驗(yàn)室處理一批學(xué)生實(shí)驗(yàn)記錄表32 個 Excel 文件每個含 58 張 sheet字段名不統(tǒng)一“學(xué)號”“student_id”“ID_No”混用、空行隨機(jī)分布、數(shù)值列里夾著“暫缺”“/”“N/A”字符串還要按規(guī)則清洗后寫入 MySQL 和導(dǎo)出 PDF 報告。他們原用的腳本跑一次報錯三次報錯信息是ValueError: invalid literal for int() with base 10: N/A——連最基礎(chǔ)的類型轉(zhuǎn)換都沒兜住。這不是不會寫是沒把 Excel 當(dāng)成有脾氣的數(shù)據(jù)源來對待。這個python讀取Excel并寫入.zip就是把這類真實(shí)場景踩透后沉淀下來的最小可運(yùn)行閉環(huán)它不炫技不堆庫只做三件事——穩(wěn)讀跳過空行/兼容混合類型/識別合并單元格、準(zhǔn)寫保留原始格式/支持多 sheet 寫入/自動適配列寬、可調(diào)試每步輸出日志錯誤定位到具體文件行號。適合剛脫離pandas.read_excel()單行命令、正被實(shí)際業(yè)務(wù)數(shù)據(jù)“毒打”的 Python 開發(fā)者也適合需要快速嵌入 Excel 處理能力的中小型系統(tǒng)后端。它不是教學(xué) demo是能直接扔進(jìn) cron 或 Airflow 里跑半年不出幺蛾子的工具包。2. 環(huán)境準(zhǔn)備與項目結(jié)構(gòu)為什么不用 pandas因?yàn)?openpyxl xlrd 的組合更可控這個壓縮包解壓后是標(biāo)準(zhǔn)的 Python 項目結(jié)構(gòu)沒有 setup.py沒有 pyproject.toml只有 4 個核心文件和 1 個 data 目錄。它刻意避開 pandas原因很實(shí)在pandas 在讀取含合并單元格、空行、混合類型比如一列前 10 行是數(shù)字第 11 行是文字的 Excel 時會靜默丟棄合并單元格信息、把空行當(dāng) NaN 處理、強(qiáng)制將整列轉(zhuǎn)為 object 類型導(dǎo)致后續(xù)計算變慢。而本項目用openpyxl讀寫 .xlsx xlrd讀 .xls已鎖定 v1.2.0 兼容老格式雙引擎所有解析邏輯自己掌控。2.1 依賴安裝精確到小版本避免玄學(xué)兼容問題項目不依賴 pipenv 或 conda只用 requirements.txt 精確控制openpyxl3.1.2 xlrd1.2.0 python-dateutil2.8.2提示xlrd2.0.0已移除對 .xls 格式的支持必須鎖死1.2.0openpyxl3.1.2是最后一個默認(rèn)啟用data_onlyTrue讀取公式的穩(wěn)定版避免公式值被誤讀為公式字符串。安裝命令推薦新建虛擬環(huán)境python -m venv venv_excel source venv_excel/bin/activate # Windows 用 venv_excel\Scripts\activate pip install -r requirements.txt2.2 目錄結(jié)構(gòu)扁平化設(shè)計拒絕過度分層python讀取Excel并寫入/ ├── main.py # 主入口定義流程、調(diào)用模塊、處理異常 ├── reader.py # 核心讀取器openpyxl/xlrd 雙引擎封裝含合并單元格識別 ├── writer.py # 核心寫入器openpyxl 寫入自動列寬適配、樣式繼承 ├── utils.py # 工具函數(shù)類型安全轉(zhuǎn)換、日志記錄、路徑解析 ├── data/ │ ├── input/ # 輸入樣例test_data.xlsx含合并單元格、空行、混合類型 │ └── output/ # 輸出目錄運(yùn)行后自動生成 └── config.json # 配置文件指定輸入路徑、sheet 過濾規(guī)則、字段映射表注意config.json是關(guān)鍵配置中樞不是硬編碼。例如字段映射{ field_mapping: { 學(xué)號: student_id, 姓名: name, 成績: score, 備注: note }, skip_empty_rows: true, preserve_merged_cells: true }這個設(shè)計讓同一套代碼能適配不同客戶的數(shù)據(jù)規(guī)范改配置即可不用動邏輯。2.3 主流程拆解main.py 如何串聯(lián)讀-洗-寫三步main.py不是膠水代碼而是流程控制器。它做了三件關(guān)鍵事加載配置用json.load()讀config.json校驗(yàn)必填字段批量讀取遍歷data/input/下所有 Excel對每個文件調(diào)用reader.read_excel_file()逐 sheet 處理對每個 sheet先調(diào)用utils.safe_convert_types()做類型推斷數(shù)字列嘗試int/float日期列用datetime.strptime()再調(diào)用writer.write_sheet_to_file()寫入新 Excel。核心流程代碼段main.py 第 42–58 行def run_pipeline(): config load_config() input_dir Path(data/input) output_dir Path(data/output) output_dir.mkdir(exist_okTrue) for excel_path in input_dir.glob(*.xls*): logger.info(f開始處理文件: {excel_path.name}) try: # 1. 讀取全部 sheet 數(shù)據(jù)返回 list[SheetData] sheets_data reader.read_excel_file(excel_path, config) # 2. 對每個 sheet 清洗并寫入新文件 output_path output_dir / fcleaned_{excel_path.name} for sheet_data in sheets_data: # 清洗跳空行、類型轉(zhuǎn)換、字段映射 cleaned_df utils.clean_sheet_data(sheet_data, config) # 寫入保留原始樣式自動列寬 writer.write_sheet_to_file(output_path, sheet_data.sheet_name, cleaned_df) logger.info(f? 完成處理: {output_path.name}) except Exception as e: logger.error(f? 處理失敗 {excel_path.name}: {str(e)}, exc_infoTrue)這段代碼的關(guān)鍵在于sheets_data是自定義的SheetData類實(shí)例定義在reader.py它不僅存數(shù)據(jù)還存merged_ranges合并單元格坐標(biāo)列表、original_headers原始表頭行號、sheet_style字體/邊框等。這為后續(xù)寫入時還原樣式打下基礎(chǔ)——很多腳本失敗就敗在“讀出來再寫回去格式全亂了”。3. 核心讀取模塊reader.py 如何馴服合并單元格與空行reader.py是整個項目的“數(shù)據(jù)守門人”。它不返回pandas.DataFrame而是返回SheetData對象確保元信息不丟失。重點(diǎn)解決兩個高頻翻車點(diǎn)合并單元格被讀成空值、空行導(dǎo)致后續(xù)數(shù)據(jù)錯位。3.1 合并單元格識別openpyxl 的merged_cells.ranges是金鑰匙o(hù)penpyxl 的worksheet.merged_cells.ranges返回一個MergedCellRange對象列表每個對象含min_row,max_row,min_col,max_col。但直接用它有個坑worksheet.cell(row, col).value對合并單元格的非首單元格返回None。所以reader.py的read_sheet_data()方法做了兩層處理def read_sheet_data(ws, config): # 步驟1預(yù)掃描所有合并區(qū)域建立坐標(biāo)映射字典 merged_map {} for merged_cell in ws.merged_cells.ranges: # 獲取合并區(qū)域左上角單元格的值即真正值所在位置 top_left ws.cell(merged_cell.min_row, merged_cell.min_col) value top_left.value # 將該合并區(qū)域內(nèi)所有坐標(biāo)映射到該值 for row in range(merged_cell.min_row, merged_cell.max_row 1): for col in range(merged_cell.min_col, merged_cell.max_col 1): merged_map[(row, col)] value # 步驟2逐行讀取優(yōu)先從 merged_map 取值fallback 到 cell.value data [] for row in ws.iter_rows(values_onlyFalse): # values_onlyFalse 才能拿到 cell 對象 row_data [] for cell in row: # 如果 (cell.row, cell.column) 在 merged_map 中取映射值 if (cell.row, cell.column) in merged_map: row_data.append(merged_map[(cell.row, cell.column)]) else: row_data.append(cell.value) data.append(row_data) return data邏輯說明merged_map是內(nèi)存緩存避免每次讀單元格都查合并范圍提升性能iter_rows(values_onlyFalse)是關(guān)鍵若設(shè)為True則拿不到cell.row/cell.column無法做坐標(biāo)映射此方法能 100% 還原合并單元格的顯示效果比如 A1:C1 合并后填“總分”A1、B1、C1 讀出來都是“總分”而非 A1“總分”、B1C1None。3.2 空行過濾不是刪掉而是標(biāo)記“可跳過”空行判斷不能只看all(cell.value is None for cell in row)因?yàn)榭赡苡锌崭?、換行符、零寬度字符。reader.py的is_empty_row()函數(shù)做了三層校驗(yàn)def is_empty_row(row_cells): # 1. 全 None直接判空 if all(cell.value is None for cell in row_cells): return True # 2. 全空白字符串strip 后為空 str_values [str(cell.value).strip() if cell.value is not None else for cell in row_cells] if all(v for v in str_values): return True # 3. 是否只有空格/制表符/換行符 import re for cell in row_cells: if cell.value is not None: s str(cell.value) # 匹配純空白字符包括 Unicode 空格 if not re.fullmatch(r\s*, s): return False return True參數(shù)說明row_cells是ws.iter_rows()返回的一行Cell對象列表第 2 步用str().strip()處理數(shù)字、布爾值等非字符串類型避免AttributeError第 3 步用正則r\s*覆蓋 Unicode 空格如\u200b零寬空格這是很多 Excel 導(dǎo)出工具埋的雷。3.3 雙引擎切換xlrd 讀 .xlsopenpyxl 讀 .xlsxread_excel_file()函數(shù)根據(jù)文件擴(kuò)展名自動路由def read_excel_file(file_path, config): file_path Path(file_path) if file_path.suffix.lower() .xls: return _read_xls_with_xlrd(file_path, config) else: # .xlsx, .xlsm, etc. return _read_xlsx_with_openpyxl(file_path, config)_read_xls_with_xlrd()的關(guān)鍵點(diǎn)xlrd.open_workbook(file_path, formatting_infoTrue)必須加formatting_infoTrue否則讀不到字體、顏色sheet.cell_type(row, col)可判斷單元格類型0empty, 1text, 2number...比cell.value更早暴露數(shù)據(jù)問題。4. 核心寫入模塊writer.py 如何讓輸出 Excel 和輸入長得一模一樣寫入不是df.to_excel()一招鮮。writer.py的目標(biāo)是用戶打開輸出文件肉眼無法分辨是否被程序處理過。這要求保留合并單元格、列寬、字體、邊框、甚至單元格背景色。4.1 合并單元格還原用ws.merge_cells()重建結(jié)構(gòu)write_sheet_to_file()接收SheetData對象其中merged_ranges字段存著原始合并區(qū)域列表如[(A1:C1), (D2:F3)]。寫入時直接調(diào)用def write_sheet_to_file(output_path, sheet_name, sheet_data): wb openpyxl.load_workbook(output_path) if output_path.exists() else openpyxl.Workbook() if sheet_name in wb.sheetnames: ws wb[sheet_name] else: ws wb.create_sheet(titlesheet_name) # 先寫數(shù)據(jù)逐單元格賦值 for r_idx, row in enumerate(sheet_data.data, start1): for c_idx, value in enumerate(row, start1): ws.cell(rowr_idx, columnc_idx, valuevalue) # 再還原合并單元格順序很重要必須在寫數(shù)據(jù)之后 for merged_range in sheet_data.merged_ranges: ws.merge_cells(merged_range) wb.save(output_path)注意merge_cells()必須在所有數(shù)據(jù)寫入之后調(diào)用。如果先合并再寫值openpyxl 會把值只寫到合并區(qū)域左上角其余單元格留空——這是新手最常踩的坑。4.2 自動列寬適配不是估算是實(shí)測字符寬度openpyxl 的column_dimensions設(shè)置列寬單位是“字符寬度”但 Excel 的字符寬度和 Python 字符串長度不等價中英文不同。writer.py的auto_adjust_column_width()方法用openpyxl.utils.units.points_to_pixels()換算并取每列最大內(nèi)容寬度def auto_adjust_column_width(ws): for column in ws.columns: max_length 0 column_letter column[0].column_letter # 如 A for cell in column: if cell.value: # 計算顯示寬度中文算2英文算1空格算0.5 width 0 for char in str(cell.value): if \u4e00 char \u9fff: # 中文 Unicode 范圍 width 2 elif char.isspace(): width 0.5 else: width 1 max_length max(max_length, int(width)) # 設(shè)置列寬openpyxl 最小列寬為 1最大為 255 adjusted_width min(max(8, max_length 2), 255) ws.column_dimensions[column_letter].width adjusted_width邏輯說明column[0].column_letter獲取列字母A/B/C...避免用數(shù)字索引出錯中文字符寬度按 2 計符合 Excel 默認(rèn)微軟雅黑字體渲染效果2是留白緩沖防止文字貼邊min/max限制在合理范圍避免列寬爆炸。4.3 樣式繼承復(fù)制原始單元格的 font/border/fillSheetData在讀取時已緩存每個單元格的font,border,fill屬性通過cell.font等獲取。寫入時逐單元格復(fù)制# 在 write_sheet_to_file() 內(nèi)部寫完數(shù)據(jù)后追加 for r_idx, row in enumerate(sheet_data.data, start1): for c_idx, _ in enumerate(row, start1): src_cell sheet_data.original_sheet.cell(r_idx, c_idx) dst_cell ws.cell(r_idx, c_idx) # 復(fù)制樣式僅非 None 屬性 if src_cell.has_style: dst_cell.font copy(src_cell.font) dst_cell.border copy(src_cell.border) dst_cell.fill copy(src_cell.fill) dst_cell.number_format src_cell.number_format這里copy()來自openpyxl.styles不是copy.deepcopy()。openpyxl的樣式對象是可拷貝的深拷貝反而會破壞樣式引用。5. 避坑指南那些讓我加班到凌晨三點(diǎn)的 Excel 陷阱這個模塊不是理論總結(jié)是血淚經(jīng)驗(yàn)。每一條都對應(yīng)一個真實(shí)報錯截圖和修復(fù) commit。5.1 現(xiàn)象讀取 .xls 文件時報xlrd.biffh.XLRDError: Unsupported format, or corrupt file原因xlrd2.0.0徹底移除了對.xls格式的支持但項目里requirements.txt寫的是xlrd無版本pip 會裝最新版2.0.3。解決立即修改requirements.txt為xlrd1.2.0并運(yùn)行pip install -r requirements.txt --force-reinstall。驗(yàn)證命令python -c import xlrd; print(xlrd.__version__)輸出1.2.0。5.2 現(xiàn)象寫入后 Excel 打開提示“發(fā)現(xiàn)不可讀取的內(nèi)容”點(diǎn)擊“是”后合并單元格消失原因openpyxl在保存時如果工作簿中有未關(guān)閉的openpyxl.workbook.Workbook實(shí)例比如wb openpyxl.Workbook()后沒wb.close()或?qū)懭肓朔欠ㄗ址鏫x00會觸發(fā) Excel 的修復(fù)模式。解決在write_sheet_to_file()結(jié)尾強(qiáng)制wb.close()并在寫入前用re.sub(r[\x00-\x08\x0b\x0c\x0e-\x1f\x7f], , str(value))過濾非法控制字符。utils.py的sanitize_string()函數(shù)已內(nèi)置此邏輯。5.3 現(xiàn)象config.json里skip_empty_rows: true不生效空行依然寫入原因reader.py的read_sheet_data()函數(shù)返回的是二維列表data但main.py調(diào)用utils.clean_sheet_data()時傳入的是SheetData對象而clean_sheet_data()內(nèi)部調(diào)用is_empty_row()時傳入的是row_cellsCell 對象列表但SheetData.data存的是值列表類型不匹配。解決在SheetData類中增加get_row_cells(row_index)方法返回該行的 Cell 對象列表clean_sheet_data()改為調(diào)用此方法判斷空行。已在reader.pyv1.3 版本修復(fù)。5.4 現(xiàn)象日期列讀出來是浮點(diǎn)數(shù)如44197.0而不是datetime對象原因openpyxl默認(rèn)將 Excel 日期存儲為從 1900-01-01 起的天數(shù)xlrd同理需手動轉(zhuǎn)換。reader.py的convert_date_cell()函數(shù)未被read_sheet_data()調(diào)用。解決在read_sheet_data()循環(huán)內(nèi)對每個cell判斷cell.data_type d日期類型然后調(diào)用openpyxl.utils.datetime.from_excel(cell.value)。已在reader.py第 89 行補(bǔ)全。5.5 現(xiàn)象輸出 Excel 的中文列寬太窄文字被截斷原因auto_adjust_column_width()中文字符寬度按 2 計算但 Excel 的默認(rèn)字體如微軟雅黑下中文實(shí)際占 1.21.5 個字符寬度2 是保守值但若用戶設(shè)置了等寬字體如 Consolas中文會撐得更開。解決在config.json中增加font_width_ratio配置項默認(rèn)1.3允許用戶根據(jù)實(shí)際字體調(diào)整。auto_adjust_column_width()中改為width font_width_ratio。此配置已在utils.py的load_config()中解析。6. 二次開發(fā)實(shí)戰(zhàn)如何把 Excel 處理模塊嵌入你的 Flask/Django 項目這個 zip 包不是獨(dú)立玩具而是可拆卸的“數(shù)據(jù)處理引擎”。我把它集成進(jìn)三個不同項目某公司內(nèi)部 OA 的附件解析服務(wù)、某高校教務(wù)系統(tǒng)的成績導(dǎo)入后臺、某 IoT 平臺的設(shè)備配置 Excel 批量下發(fā)。核心改造就三步接口化、異步化、錯誤隔離。6.1 接口化封裝為可調(diào)用函數(shù)屏蔽文件路徑細(xì)節(jié)main.py的run_pipeline()是面向 CLI 的要嵌入 Web 項目需提供process_excel_bytes()函數(shù)接收bytes和config_dict返回bytes處理后的 Excel和dict日志摘要# 新增 api.py from io import BytesIO from openpyxl import load_workbook from reader import read_excel_file from writer import write_sheet_to_file from utils import load_config_from_dict def process_excel_bytes(excel_bytes: bytes, config_dict: dict) - tuple[bytes, dict]: 處理 Excel 字節(jié)流返回處理后的字節(jié)流和結(jié)果摘要 :param excel_bytes: 原始 Excel 文件字節(jié) :param config_dict: 運(yùn)行時配置覆蓋 config.json :return: (processed_bytes, summary_dict) # 1. 從字節(jié)流加載 workbook臨時文件 input_wb load_workbook(filenameBytesIO(excel_bytes), data_onlyTrue) # 2. 保存為臨時 .xlsx 文件因 xlrd 不支持字節(jié)流讀 .xls temp_input Path(temp_input.xlsx) input_wb.save(temp_input) # 3. 調(diào)用原邏輯處理 config load_config_from_dict(config_dict) # 新增函數(shù)從 dict 加載配置 sheets_data reader.read_excel_file(temp_input, config) # 4. 寫入內(nèi)存 BytesIO output_buffer BytesIO() for sheet_data in sheets_data: cleaned_df utils.clean_sheet_data(sheet_data, config) # 注意此處需修改 writer.write_sheet_to_file 支持 BytesIO # 已在 writer.py v1.5 中新增 write_to_buffer() 函數(shù) writer.write_to_buffer(output_buffer, sheet_data.sheet_name, cleaned_df) # 5. 清理臨時文件 temp_input.unlink(missing_okTrue) return output_buffer.getvalue(), { status: success, processed_sheets: len(sheets_data), error_count: 0 }提示write_to_buffer()是writer.py的新增方法它用openpyxl.Workbook()創(chuàng)建新工作簿寫入后save()到BytesIO避免磁盤 IO。6.2 異步化用 Celery 處理大文件避免 Web 請求超時Flask 視圖中不能直接調(diào)用耗時函數(shù)。用 Celery 做異步任務(wù)# tasks.py from celery import Celery from api import process_excel_bytes celery Celery(excel_tasks) celery.conf.broker_url redis://localhost:6379/0 celery.task(bindTrue, max_retries3) def async_process_excel(self, excel_bytes: bytes, config_dict: dict): try: result_bytes, summary process_excel_bytes(excel_bytes, config_dict) return {status: completed, result_bytes: result_bytes, summary: summary} except Exception as exc: # 重試機(jī)制 raise self.retry(excexc, countdown60 * (2 ** self.request.retries))Flask 視圖調(diào)用# app.py app.route(/api/process-excel, methods[POST]) def process_excel(): if file not in request.files: return jsonify({error: no file}), 400 file request.files[file] config request.get_json().get(config, {}) # 異步提交任務(wù) task async_process_excel.delay(file.read(), config) return jsonify({task_id: task.id}), 2026.3 錯誤隔離為每個 Excel 文件創(chuàng)建獨(dú)立日志上下文Web 項目中多個用戶并發(fā)上傳日志不能混在一起。utils.py的setup_logger_for_task(task_id: str)函數(shù)創(chuàng)建帶 task_id 前綴的 loggerdef setup_logger_for_task(task_id: str): logger logging.getLogger(fexcel_processor.{task_id}) logger.setLevel(logging.INFO) if not logger.handlers: handler logging.StreamHandler() formatter logging.Formatter(f%(asctime)s - {task_id} - %(levelname)s - %(message)s) handler.setFormatter(formatter) logger.addHandler(handler) return logger # 在 process_excel_bytes() 開頭調(diào)用 logger setup_logger_for_task(str(uuid.uuid4())) logger.info(開始處理 Excel 字節(jié)流)這樣每條日志都帶唯一 task_idKibana 或 ELK 里可直接篩選。從那以后我每次給新項目加 Excel 處理功能都強(qiáng)制走一遍這三步先寫process_excel_bytes()封裝再套 Celery 異步殼最后用 task_id 隔離日志。省去一半 debug 時間上線后監(jiān)控告警也精準(zhǔn)到具體哪個用戶的哪個文件出了問題。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取