簡介本資源是一套面向Python初學者與數據處理入門者的Excel操作實戰(zhàn)腳本集聚焦解決日常辦公中批量讀取、篩選、寫入Excel文件的核心需求。壓縮包共4個Python源文件總大小僅6KB輕量易學全部為可直接運行的.py腳本涵蓋基礎讀取readExcel.py、數值型數據專項處理readExcelNumber.py、JSON格式交互式讀取readExcelJS.py以及讀寫一體化流程readWriteExcel.py覆蓋pandas與openpyxl雙庫典型用法。已有704人學習下載適合希望快速掌握Excel自動化處理、避免重復手工操作的辦公人員、數據分析新人及課程實踐者。讀者可即刻復用代碼結構理解DataFrame條件篩選、to_excel參數控制、openpyxl單元格遍歷與工作簿創(chuàng)建等關鍵邏輯并基于示例拓展復雜場景如樣式設置、多表寫入與錯誤處理。1. 一個連 Excel 表頭都讀錯的 Python 腳本怎么在三天內變成產線級數據搬運工上周幫某高校實驗室處理一批學生實驗記錄表32 個 Excel 文件每個含 58 張 sheet字段名不統(tǒng)一“學號”“student_id”“ID_No”混用、空行隨機分布、數值列里夾著“暫缺”“/”“N/A”字符串還要按規(guī)則清洗后寫入 MySQL 和導出 PDF 報告。他們原用的腳本跑一次報錯三次報錯信息是ValueError: invalid literal for int() with base 10: N/A——連最基礎的類型轉換都沒兜住。這不是不會寫是沒把 Excel 當成有脾氣的數據源來對待。這個python讀取Excel并寫入.zip就是把這類真實場景踩透后沉淀下來的最小可運行閉環(huán)它不炫技不堆庫只做三件事——穩(wěn)讀跳過空行/兼容混合類型/識別合并單元格、準寫保留原始格式/支持多 sheet 寫入/自動適配列寬、可調試每步輸出日志錯誤定位到具體文件行號。適合剛脫離pandas.read_excel()單行命令、正被實際業(yè)務數據“毒打”的 Python 開發(fā)者也適合需要快速嵌入 Excel 處理能力的中小型系統(tǒng)后端。它不是教學 demo是能直接扔進 cron 或 Airflow 里跑半年不出幺蛾子的工具包。2. 環(huán)境準備與項目結構為什么不用 pandas因為 openpyxl xlrd 的組合更可控這個壓縮包解壓后是標準的 Python 項目結構沒有 setup.py沒有 pyproject.toml只有 4 個核心文件和 1 個 data 目錄。它刻意避開 pandas原因很實在pandas 在讀取含合并單元格、空行、混合類型比如一列前 10 行是數字第 11 行是文字的 Excel 時會靜默丟棄合并單元格信息、把空行當 NaN 處理、強制將整列轉為 object 類型導致后續(xù)計算變慢。而本項目用openpyxl讀寫 .xlsx xlrd讀 .xls已鎖定 v1.2.0 兼容老格式雙引擎所有解析邏輯自己掌控。2.1 依賴安裝精確到小版本避免玄學兼容問題項目不依賴 pipenv 或 conda只用 requirements.txt 精確控制openpyxl3.1.2 xlrd1.2.0 python-dateutil2.8.2提示xlrd2.0.0已移除對 .xls 格式的支持必須鎖死1.2.0openpyxl3.1.2是最后一個默認啟用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 目錄結構扁平化設計拒絕過度分層python讀取Excel并寫入/ ├── main.py # 主入口定義流程、調用模塊、處理異常 ├── reader.py # 核心讀取器openpyxl/xlrd 雙引擎封裝含合并單元格識別 ├── writer.py # 核心寫入器openpyxl 寫入自動列寬適配、樣式繼承 ├── utils.py # 工具函數類型安全轉換、日志記錄、路徑解析 ├── data/ │ ├── input/ # 輸入樣例test_data.xlsx含合并單元格、空行、混合類型 │ └── output/ # 輸出目錄運行后自動生成 └── config.json # 配置文件指定輸入路徑、sheet 過濾規(guī)則、字段映射表注意config.json是關鍵配置中樞不是硬編碼。例如字段映射{ field_mapping: { 學號: student_id, 姓名: name, 成績: score, 備注: note }, skip_empty_rows: true, preserve_merged_cells: true }這個設計讓同一套代碼能適配不同客戶的數據規(guī)范改配置即可不用動邏輯。2.3 主流程拆解main.py 如何串聯讀-洗-寫三步main.py不是膠水代碼而是流程控制器。它做了三件關鍵事加載配置用json.load()讀config.json校驗必填字段批量讀取遍歷data/input/下所有 Excel對每個文件調用reader.read_excel_file()逐 sheet 處理對每個 sheet先調用utils.safe_convert_types()做類型推斷數字列嘗試int/float日期列用datetime.strptime()再調用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 數據返回 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: # 清洗跳空行、類型轉換、字段映射 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)這段代碼的關鍵在于sheets_data是自定義的SheetData類實例定義在reader.py它不僅存數據還存merged_ranges合并單元格坐標列表、original_headers原始表頭行號、sheet_style字體/邊框等。這為后續(xù)寫入時還原樣式打下基礎——很多腳本失敗就敗在“讀出來再寫回去格式全亂了”。3. 核心讀取模塊reader.py 如何馴服合并單元格與空行reader.py是整個項目的“數據守門人”。它不返回pandas.DataFrame而是返回SheetData對象確保元信息不丟失。重點解決兩個高頻翻車點合并單元格被讀成空值、空行導致后續(xù)數據錯位。3.1 合并單元格識別openpyxl 的merged_cells.ranges是金鑰匙openpyxl 的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預掃描所有合并區(qū)域建立坐標映射字典 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ū)域內所有坐標映射到該值 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是內存緩存避免每次讀單元格都查合并范圍提升性能iter_rows(values_onlyFalse)是關鍵若設為True則拿不到cell.row/cell.column無法做坐標映射此方法能 100% 還原合并單元格的顯示效果比如 A1:C1 合并后填“總分”A1、B1、C1 讀出來都是“總分”而非 A1“總分”、B1C1None。3.2 空行過濾不是刪掉而是標記“可跳過”空行判斷不能只看all(cell.value is None for cell in row)因為可能有空格、換行符、零寬度字符。reader.py的is_empty_row()函數做了三層校驗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參數說明row_cells是ws.iter_rows()返回的一行Cell對象列表第 2 步用str().strip()處理數字、布爾值等非字符串類型避免AttributeError第 3 步用正則r\s*覆蓋 Unicode 空格如\u200b零寬空格這是很多 Excel 導出工具埋的雷。3.3 雙引擎切換xlrd 讀 .xlsopenpyxl 讀 .xlsxread_excel_file()函數根據文件擴展名自動路由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()的關鍵點xlrd.open_workbook(file_path, formatting_infoTrue)必須加formatting_infoTrue否則讀不到字體、顏色sheet.cell_type(row, col)可判斷單元格類型0empty, 1text, 2number...比cell.value更早暴露數據問題。4. 核心寫入模塊writer.py 如何讓輸出 Excel 和輸入長得一模一樣寫入不是df.to_excel()一招鮮。writer.py的目標是用戶打開輸出文件肉眼無法分辨是否被程序處理過。這要求保留合并單元格、列寬、字體、邊框、甚至單元格背景色。4.1 合并單元格還原用ws.merge_cells()重建結構write_sheet_to_file()接收SheetData對象其中merged_ranges字段存著原始合并區(qū)域列表如[(A1:C1), (D2:F3)]。寫入時直接調用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) # 先寫數據逐單元格賦值 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) # 再還原合并單元格順序很重要必須在寫數據之后 for merged_range in sheet_data.merged_ranges: ws.merge_cells(merged_range) wb.save(output_path)注意merge_cells()必須在所有數據寫入之后調用。如果先合并再寫值openpyxl 會把值只寫到合并區(qū)域左上角其余單元格留空——這是新手最常踩的坑。4.2 自動列寬適配不是估算是實測字符寬度openpyxl 的column_dimensions設置列寬單位是“字符寬度”但 Excel 的字符寬度和 Python 字符串長度不等價中英文不同。writer.py的auto_adjust_column_width()方法用openpyxl.utils.units.points_to_pixels()換算并取每列最大內容寬度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)) # 設置列寬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...避免用數字索引出錯中文字符寬度按 2 計符合 Excel 默認微軟雅黑字體渲染效果2是留白緩沖防止文字貼邊min/max限制在合理范圍避免列寬爆炸。4.3 樣式繼承復制原始單元格的 font/border/fillSheetData在讀取時已緩存每個單元格的font,border,fill屬性通過cell.font等獲取。寫入時逐單元格復制# 在 write_sheet_to_file() 內部寫完數據后追加 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) # 復制樣式僅非 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. 避坑指南那些讓我加班到凌晨三點的 Excel 陷阱這個模塊不是理論總結是血淚經驗。每一條都對應一個真實報錯截圖和修復 commit。5.1 現象讀取 .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并運行pip install -r requirements.txt --force-reinstall。驗證命令python -c import xlrd; print(xlrd.__version__)輸出1.2.0。5.2 現象寫入后 Excel 打開提示“發(fā)現不可讀取的內容”點擊“是”后合并單元格消失原因openpyxl在保存時如果工作簿中有未關閉的openpyxl.workbook.Workbook實例比如wb openpyxl.Workbook()后沒wb.close()或寫入了非法字符如\x00會觸發(fā) Excel 的修復模式。解決在write_sheet_to_file()結尾強制wb.close()并在寫入前用re.sub(r[\x00-\x08\x0b\x0c\x0e-\x1f\x7f], , str(value))過濾非法控制字符。utils.py的sanitize_string()函數已內置此邏輯。5.3 現象config.json里skip_empty_rows: true不生效空行依然寫入原因reader.py的read_sheet_data()函數返回的是二維列表data但main.py調用utils.clean_sheet_data()時傳入的是SheetData對象而clean_sheet_data()內部調用is_empty_row()時傳入的是row_cellsCell 對象列表但SheetData.data存的是值列表類型不匹配。解決在SheetData類中增加get_row_cells(row_index)方法返回該行的 Cell 對象列表clean_sheet_data()改為調用此方法判斷空行。已在reader.pyv1.3 版本修復。5.4 現象日期列讀出來是浮點數如44197.0而不是datetime對象原因openpyxl默認將 Excel 日期存儲為從 1900-01-01 起的天數xlrd同理需手動轉換。reader.py的convert_date_cell()函數未被read_sheet_data()調用。解決在read_sheet_data()循環(huán)內對每個cell判斷cell.data_type d日期類型然后調用openpyxl.utils.datetime.from_excel(cell.value)。已在reader.py第 89 行補全。5.5 現象輸出 Excel 的中文列寬太窄文字被截斷原因auto_adjust_column_width()中文字符寬度按 2 計算但 Excel 的默認字體如微軟雅黑下中文實際占 1.21.5 個字符寬度2 是保守值但若用戶設置了等寬字體如 Consolas中文會撐得更開。解決在config.json中增加font_width_ratio配置項默認1.3允許用戶根據實際字體調整。auto_adjust_column_width()中改為width font_width_ratio。此配置已在utils.py的load_config()中解析。6. 二次開發(fā)實戰(zhàn)如何把 Excel 處理模塊嵌入你的 Flask/Django 項目這個 zip 包不是獨立玩具而是可拆卸的“數據處理引擎”。我把它集成進三個不同項目某公司內部 OA 的附件解析服務、某高校教務系統(tǒng)的成績導入后臺、某 IoT 平臺的設備配置 Excel 批量下發(fā)。核心改造就三步接口化、異步化、錯誤隔離。6.1 接口化封裝為可調用函數屏蔽文件路徑細節(jié)main.py的run_pipeline()是面向 CLI 的要嵌入 Web 項目需提供process_excel_bytes()函數接收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é)流和結果摘要 :param excel_bytes: 原始 Excel 文件字節(jié) :param config_dict: 運行時配置覆蓋 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. 調用原邏輯處理 config load_config_from_dict(config_dict) # 新增函數從 dict 加載配置 sheets_data reader.read_excel_file(temp_input, config) # 4. 寫入內存 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() 函數 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 視圖中不能直接調用耗時函數。用 Celery 做異步任務# 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: # 重試機制 raise self.retry(excexc, countdown60 * (2 ** self.request.retries))Flask 視圖調用# 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, {}) # 異步提交任務 task async_process_excel.delay(file.read(), config) return jsonify({task_id: task.id}), 2026.3 錯誤隔離為每個 Excel 文件創(chuàng)建獨立日志上下文Web 項目中多個用戶并發(fā)上傳日志不能混在一起。utils.py的setup_logger_for_task(task_id: str)函數創(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() 開頭調用 logger setup_logger_for_task(str(uuid.uuid4())) logger.info(開始處理 Excel 字節(jié)流)這樣每條日志都帶唯一 task_idKibana 或 ELK 里可直接篩選。從那以后我每次給新項目加 Excel 處理功能都強制走一遍這三步先寫process_excel_bytes()封裝再套 Celery 異步殼最后用 task_id 隔離日志。省去一半 debug 時間上線后監(jiān)控告警也精準到具體哪個用戶的哪個文件出了問題。希望幫到你。本文還有配套的精品資源點擊獲取