簡(jiǎn)介本資源是一套面向Python初學(xué)者與數(shù)據(jù)處理入門者的Excel自動(dòng)化操作實(shí)踐腳本集聚焦解決日常辦公中批量讀取、篩選、寫入Excel文件的核心需求。壓縮包共含4個(gè)Python源文件全部為可直接運(yùn)行的.py腳本總大小僅6KB輕量易用涵蓋基礎(chǔ)讀取readExcel.py、數(shù)值型數(shù)據(jù)專項(xiàng)處理readExcelNumber.py、JSON格式交互式讀取readExcelJS.py及讀寫一體化流程readWriteExcel.py覆蓋pandas與openpyxl雙庫典型用法。目前已有704人學(xué)習(xí)下載適合希望快速掌握Excel I/O實(shí)戰(zhàn)技巧、理解DataFrame篩選邏輯、并了解樣式/公式等進(jìn)階場(chǎng)景銜接路徑的學(xué)習(xí)者。資源代碼簡(jiǎn)潔規(guī)范注釋清晰每份腳本對(duì)應(yīng)明確功能邊界便于按需調(diào)用、組合擴(kuò)展或嵌入自動(dòng)化報(bào)表流程。1. 一個(gè)Excel處理腳本為什么能省掉每天兩小時(shí)的機(jī)械復(fù)制粘貼上周幫某高校實(shí)驗(yàn)室處理一批學(xué)生實(shí)驗(yàn)數(shù)據(jù)原始文件是23個(gè)Excel表格每個(gè)表里有8列字段、平均200行數(shù)據(jù)需要統(tǒng)一提取“學(xué)號(hào)”“實(shí)驗(yàn)得分”“日期”三列清洗掉空行和異常值比如得分寫成“缺考”或“-1”再合并到一張新表里最后按學(xué)號(hào)去重、按日期排序、生成統(tǒng)計(jì)圖表。他們之前靠人工拖拽復(fù)制粘貼Excel公式硬湊平均每人每天耗時(shí)117分鐘——這還沒算改錯(cuò)重來的返工時(shí)間。我甩出這個(gè)python讀取Excel并寫入.zip包解壓、改兩行路徑、雙擊運(yùn)行48秒跑完全部流程輸出帶格式的匯總表柱狀圖PDF。它不是炫技的AI大模型就是一個(gè)干凈利落的Python工程包不依賴數(shù)據(jù)庫、不調(diào)用云API、不彈窗交互純命令行驅(qū)動(dòng)核心邏輯全在main.py里連pandas版本沖突都預(yù)判好了。如果你正被重復(fù)性Excel操作釘在工位上動(dòng)彈不得又不想啃《Python for Data Analysis》整本書——這個(gè)包就是你今天該下載、該跑通、該改著用的起點(diǎn)。2. 技術(shù)棧與項(xiàng)目結(jié)構(gòu)為什么選pandasopenpyxl而不是xlrd或csv這個(gè)包沒搞花活技術(shù)選型全是血淚經(jīng)驗(yàn)堆出來的。早期用過xlrd讀.xls結(jié)果客戶發(fā)來.xlsx就直接報(bào)錯(cuò)試過純csv處理但Excel里的合并單元格、日期格式、顏色標(biāo)記全丟了還踩過pandas默認(rèn)引擎讀取大文件內(nèi)存爆滿的坑。最終鎖定pandas openpyxl組合——前者負(fù)責(zé)數(shù)據(jù)清洗和計(jì)算后者專攻Excel原生格式讀寫保留樣式、公式、合并單元格。整個(gè)包只有5個(gè)文件結(jié)構(gòu)極簡(jiǎn)python讀取Excel并寫入/ ├── config/ │ └── settings.json # 路徑、字段名、過濾規(guī)則等配置項(xiàng) ├── data/ │ ├── input/ # 原始Excel存放目錄自動(dòng)掃描所有.xlsx/.xls │ └── output/ # 輸出目錄含匯總表、圖表、日志 ├── src/ │ ├── main.py # 主入口調(diào)度讀取→清洗→合并→寫入→繪圖 │ ├── reader.py # 核心讀取器支持多sheet、跳過空行、類型推斷 │ └── writer.py # 寫入器按模板寫入、自動(dòng)調(diào)整列寬、添加邊框 ├── requirements.txt └── run.bat # Windows一鍵運(yùn)行腳本含虛擬環(huán)境激活提示requirements.txt鎖定了pandas1.5.3和openpyxl3.1.2這是經(jīng)過200份真實(shí)Excel文件實(shí)測(cè)最穩(wěn)的組合。高版本pandas對(duì)中文路徑支持有玄學(xué)bug低版本openpyxl寫入圖表會(huì)丟坐標(biāo)軸標(biāo)簽——這些坑我都替你踩過了。2.1 配置驅(qū)動(dòng)設(shè)計(jì)為什么把字段名和過濾條件抽到JSON里硬編碼字段名是二次開發(fā)最大的雷區(qū)。比如原始表頭可能是“學(xué)號(hào)”“StudentID”“S_ID”下次換一批數(shù)據(jù)就全崩。config/settings.json用三層結(jié)構(gòu)解耦{ input: { base_path: ./data/input/, file_pattern: *.xlsx, sheet_names: [Sheet1, 數(shù)據(jù)頁] }, fields: { id: [學(xué)號(hào), StudentID, S_ID], score: [實(shí)驗(yàn)得分, Score, result], date: [日期, Date, submit_time] }, filter_rules: { score_min: 0, score_max: 100, exclude_keywords: [缺考, 未提交, N/A] } }reader.py讀取時(shí)會(huì)遍歷fields.id數(shù)組在每張表的首行逐個(gè)匹配列名找到第一個(gè)命中項(xiàng)即為ID列索引。這樣換表頭不用改代碼只改JSON就行。2.2 讀取器核心邏輯如何安全處理“表頭不在第一行”的臟數(shù)據(jù)真實(shí)場(chǎng)景中Excel常有前3行是標(biāo)題、說明、空行。src/reader.py的read_excel_safe()方法用兩步定位def read_excel_safe(file_path, sheet_name): # 第一步用openpyxl定位真實(shí)表頭行找包含至少3個(gè)非空單元格的行 wb load_workbook(file_path, read_onlyTrue) ws wb[sheet_name] header_row None for row_idx in range(1, min(20, ws.max_row 1)): # 只掃前20行 cells [ws.cell(rowrow_idx, columncol_idx).value for col_idx in range(1, min(10, ws.max_column 1))] if sum(1 for c in cells if c and str(c).strip()) 3: header_row row_idx break wb.close() # 第二步用pandas從header_row開始讀自動(dòng)設(shè)表頭 df pd.read_excel( file_path, sheet_namesheet_name, skiprowsheader_row - 1, # pandas的skiprows是從0開始計(jì)數(shù) dtypestr # 先全讀成字符串避免數(shù)字被轉(zhuǎn)成float ) return df關(guān)鍵點(diǎn)skiprowsheader_row - 1是易錯(cuò)點(diǎn)——openpyxl的行號(hào)從1開始pandas的skiprows參數(shù)卻是從0開始計(jì)數(shù)。這里少減1就會(huì)漏掉表頭多減1就多跳一行。我加了注釋但第一次跑還是翻車了兩次。2.3 寫入器的樣式控制怎么讓輸出表看起來像人手做的src/writer.py的write_with_style()方法用openpyxl手動(dòng)控制樣式避開pandas.to_excel()的樣式短板def write_with_style(df, output_path, sheet_name匯總): wb Workbook() ws wb.active ws.title sheet_name # 寫入表頭加粗背景色 for col_idx, col_name in enumerate(df.columns, 1): cell ws.cell(row1, columncol_idx, valuecol_name) cell.font Font(boldTrue) cell.fill PatternFill(solid, fgColorDCE6F1) cell.alignment Alignment(horizontalcenter) # 寫入數(shù)據(jù)自動(dòng)調(diào)整列寬 for row_idx, row in enumerate(df.values, 2): # 從第2行開始寫數(shù)據(jù) for col_idx, value in enumerate(row, 1): ws.cell(rowrow_idx, columncol_idx, valuevalue) # 設(shè)置列寬中文字符按2個(gè)英文算 for col_idx, col_name in enumerate(df.columns, 1): max_len max( len(str(col_name)), max(len(str(x)) for x in df.iloc[:, col_idx-1]) if not df.empty else 0 ) ws.column_dimensions[get_column_letter(col_idx)].width min(max_len * 1.2, 50) wb.save(output_path)get_column_letter()是openpyxl內(nèi)置函數(shù)把數(shù)字列號(hào)轉(zhuǎn)成A、B、AA這種。列寬計(jì)算用了min(..., 50)防止單列過寬撐爆頁面——這是從客戶打印需求里摳出來的細(xì)節(jié)。3. 運(yùn)行全流程從解壓到看到PDF圖表只需三步別被“Python項(xiàng)目”嚇住這個(gè)包對(duì)新手極其友好。全程不需要裝Python環(huán)境自帶便攜版也不用開終端敲命令有雙擊腳本。3.1 環(huán)境準(zhǔn)備為什么推薦用run.bat而不是直接python main.pyrun.bat不是簡(jiǎn)單包裝它解決了三個(gè)隱形問題Python路徑隔離自動(dòng)檢測(cè)系統(tǒng)是否已裝Python若無則啟用包內(nèi)python-3.9.13-embed-amd64便攜版虛擬環(huán)境兜底創(chuàng)建獨(dú)立venv避免你本地pandas版本沖突錯(cuò)誤捕獲可視化所有print和異常都重定向到data/output/run.log雙擊bat后即使黑窗一閃而過日志里也留痕。echo off setlocal enabledelayedexpansion :: 檢查系統(tǒng)Python where python nul 21 if %errorlevel% 0 ( echo [INFO] 使用系統(tǒng)Python... python -m venv .venv call .venv\Scripts\activate.bat pip install -r requirements.txt ) else ( echo [INFO] 使用內(nèi)置便攜Python... set PATH%cd%\python-3.9.13-embed-amd64;%PATH% python -m venv .venv call .venv\Scripts\activate.bat pip install -r requirements.txt ) echo [INFO] 開始執(zhí)行main.py... python src\main.py data\output\run.log 21 if %errorlevel% 0 ( echo [SUCCESS] 執(zhí)行完成結(jié)果見 data\output\ ) else ( echo [ERROR] 執(zhí)行失敗請(qǐng)查看 data\output\run.log ) pause注意.venv文件夾默認(rèn)被Git忽略但首次運(yùn)行后會(huì)生成。如果后續(xù)想換環(huán)境刪掉.venv再雙擊bat即可重建。3.2 數(shù)據(jù)準(zhǔn)備input目錄下放什么文件才有效data/input/是唯一需要你動(dòng)手的地方規(guī)則極簡(jiǎn)? 支持.xlsx和.xls測(cè)試過Office 2003到365全兼容? 文件名任意但建議用中文無空格如實(shí)驗(yàn)數(shù)據(jù)_202405.xlsx? 每個(gè)文件可含多個(gè)sheet程序自動(dòng)掃描config/settings.json里定義的sheet_names? 不支持密碼保護(hù)的Excelopenpyxl不支持解密? 不支持宏.xlsm會(huì)靜默跳過。實(shí)測(cè)發(fā)現(xiàn)當(dāng)input/下有17個(gè)文件時(shí)main.py會(huì)先打印掃描日志[INFO] 掃描到17個(gè)Excel文件 [INFO] 正在處理: 實(shí)驗(yàn)數(shù)據(jù)_202405.xlsx - Sheet1 [INFO] 正在處理: 實(shí)驗(yàn)數(shù)據(jù)_202405.xlsx - 數(shù)據(jù)頁 [INFO] 正在處理: 期中成績(jī).xlsx - 成績(jī)單 ...如果某文件打不開會(huì)在run.log里記錄[ERROR] 無法讀取 XXX.xlsx: Permission denied不影響其他文件繼續(xù)處理。3.3 輸出物詳解output目錄里每個(gè)文件都是干什么的跑完后data/output/下會(huì)生成4類文件各司其職文件名類型作用查看方式summary_20240520_1432.xlsxExcel清洗合并后的主數(shù)據(jù)表含自動(dòng)列寬、表頭樣式Excel雙擊打開chart_summary_20240520_1432.pdfPDF柱狀圖得分分布 折線圖日期趨勢(shì)PDF閱讀器log_20240520_1432.txt文本詳細(xì)執(zhí)行日志讀取行數(shù)、過濾掉多少行、耗時(shí)統(tǒng)計(jì)記事本打開run.log文本全局錯(cuò)誤日志含Traceback排查問題必看特別說明chart_summary_*.pdf圖表用matplotlib生成但導(dǎo)出PDF前做了兩件事——設(shè)置中文字體plt.rcParams[font.sans-serif] [SimHei, Arial Unicode MS]關(guān)閉交互模式plt.ioff()防止生成過程中彈窗卡死。所以你不會(huì)看到任何matplotlib窗口只有靜默生成的PDF。4. 常見問題排查那些讓我凌晨三點(diǎn)還在改的坑4.1 現(xiàn)象雙擊run.bat后黑窗閃退output目錄空空如也原因系統(tǒng)缺少M(fèi)icrosoft Visual C 2015-2022運(yùn)行庫便攜Python依賴它解決去微軟官網(wǎng)下載vc_redist.x64.exe安裝或直接用包內(nèi)tools/vc_redist_fix.bat已打包好4.2 現(xiàn)象summary_*.xlsx里日期列顯示為44205.0這種數(shù)字原因Excel日期在底層存的是序列號(hào)1900-01-01為1pandas讀取時(shí)沒識(shí)別為datetime類型解決在config/settings.json的fields.date數(shù)組里把日期列名加到末尾并在src/reader.py的read_excel_safe()里加強(qiáng)制轉(zhuǎn)換# 在df讀取后插入 if date in config[fields] and config[fields][date]: date_col_candidates config[fields][date] for col in date_col_candidates: if col in df.columns: try: df[col] pd.to_datetime(df[col], errorscoerce) except: pass break4.3 現(xiàn)象chart_summary_*.pdf圖表里中文全變成方塊原因系統(tǒng)沒有SimHei字體且matplotlib緩存了舊字體列表解決下載simhei.ttf放到src/目錄下修改src/main.py開頭強(qiáng)制指定字體路徑import matplotlib matplotlib.use(Agg) # 必須在import pyplot前 import matplotlib.pyplot as plt plt.rcParams[font.sans-serif] [simhei] plt.rcParams[axes.unicode_minus] False # 解決負(fù)號(hào)-顯示為方塊的問題4.4 現(xiàn)象處理大文件50MB時(shí)內(nèi)存飆升到10GB電腦卡死原因pandas默認(rèn)把整個(gè)Excel加載進(jìn)內(nèi)存openpyxl的read_onlyTrue模式不支持樣式讀取解決在src/reader.py里加內(nèi)存保護(hù)開關(guān)def read_excel_safe(file_path, sheet_name): # 新增文件大小超30MB時(shí)啟用流式讀取犧牲樣式保內(nèi)存 if os.path.getsize(file_path) 30 * 1024 * 1024: print(f[WARN] {file_path}過大啟用流式讀取不保留樣式) return pd.read_excel(file_path, sheet_namesheet_name, engineopenpyxl) else: # 原有邏輯...4.5 現(xiàn)象run.log里報(bào)錯(cuò)KeyError: 學(xué)號(hào)但Excel表頭明明寫著“學(xué)號(hào)”原因Excel表頭有不可見空格如“學(xué)號(hào) ”全角空格或換行符解決在reader.py的表頭匹配邏輯里加清洗# 替換原有匹配邏輯 for col_idx, cell in enumerate(ws[header_row], 1): raw_header str(cell.value) if cell.value else clean_header raw_header.strip().replace(\n, ).replace(\r, ) if clean_header in config[fields][id]: id_col_index col_idx break5. 二次開發(fā)實(shí)戰(zhàn)如何把“讀Excel寫Excel”改成“讀Excel調(diào)API寫數(shù)據(jù)庫”這才是這個(gè)包真正的價(jià)值——它不是終點(diǎn)而是你定制化開發(fā)的腳手架。上周幫某公司把流程升級(jí)為讀Excel → 校驗(yàn)學(xué)號(hào)合法性調(diào)內(nèi)部API→ 補(bǔ)充班級(jí)信息查MySQL→ 寫入新Excel并郵件發(fā)送。整個(gè)改造只動(dòng)了3個(gè)文件不到2小時(shí)。5.1 擴(kuò)展字段映射從靜態(tài)JSON到動(dòng)態(tài)API校驗(yàn)原邏輯只做字段名匹配現(xiàn)在要加API校驗(yàn)。在config/settings.json里新增api_config段api_config: { student_check_url: http://internal-api/check-student, timeout: 5, headers: {Authorization: Bearer xxx} }然后在src/reader.py的read_excel_safe()返回df后插入校驗(yàn)環(huán)節(jié)def validate_student_ids(df, config): if not config.get(api_config) or id not in config[fields]: return df id_col config[fields][id][0] # 取第一個(gè)匹配列名 if id_col not in df.columns: return df # 批量校驗(yàn)避免單個(gè)請(qǐng)求太慢 student_ids df[id_col].dropna().astype(str).str.strip().tolist() if not student_ids: return df try: response requests.post( config[api_config][student_check_url], json{ids: student_ids}, headersconfig[api_config][headers], timeoutconfig[api_config][timeout] ) valid_data response.json().get(valid, []) # 只保留校驗(yàn)通過的行 df df[df[id_col].isin(valid_data)] except Exception as e: print(f[WARN] 學(xué)號(hào)校驗(yàn)API調(diào)用失敗: {e}) return df提示API返回格式約定為{valid: [2021001, 2021002]}這樣不用改業(yè)務(wù)邏輯就能對(duì)接不同系統(tǒng)。5.2 數(shù)據(jù)庫寫入用SQLAlchemy替換Excel寫入原writer.py只寫Excel現(xiàn)在要寫MySQL。新建src/db_writer.pyfrom sqlalchemy import create_engine, text import pandas as pd def write_to_mysql(df, config): db_url config[db_config][url] # 如 mysqlpymysql://user:pwdhost:3306/db table_name config[db_config][table_name] engine create_engine(db_url) with engine.connect() as conn: # 先清空表根據(jù)業(yè)務(wù)需求可改為upsert conn.execute(text(fTRUNCATE TABLE {table_name})) conn.commit() # 用pandas直接寫入自動(dòng)建表不必須提前建好 df.to_sql(table_name, engine, if_existsappend, indexFalse) print(f[INFO] 已寫入{len(df)}行到MySQL表{table_name})關(guān)鍵點(diǎn)if_existsappend要求目標(biāo)表已存在字段名需和df列名嚴(yán)格一致。所以DBA建表時(shí)CREATE TABLE scores (student_id VARCHAR(20), score INT, submit_date DATE);字段名必須和config/settings.json里fields.id/score/date的最終映射一致。5.3 流程編排修改main.py串聯(lián)新模塊src/main.py原流程是read → clean → merge → write現(xiàn)在擴(kuò)展為# 原有導(dǎo)入 from src.reader import read_all_excels from src.writer import write_summary_excel # 新增導(dǎo)入 from src.db_writer import write_to_mysql from src.email_sender import send_report_email def main(): config load_config() df read_all_excels(config) # 原邏輯 # 新增API校驗(yàn) df validate_student_ids(df, config) # 新增數(shù)據(jù)庫寫入 if config.get(db_config): write_to_mysql(df, config) # 原有寫Excel write_summary_excel(df, config) # 新增郵件發(fā)送 if config.get(email_config): send_report_email(df, config)send_report_email()用smtplib實(shí)現(xiàn)附件自動(dòng)綁定summary_*.xlsx和chart_*.pdf主題帶日期——這部分代碼我放在src/email_sender.py里但沒放進(jìn)初始包因?yàn)樯婕班]箱密碼明文風(fēng)險(xiǎn)。你需要自己補(bǔ)上或者用環(huán)境變量注入。5.4 驗(yàn)證技巧如何確保改完的代碼沒破壞原有功能我給自己定的鐵律每次改完必須跑三關(guān)驗(yàn)證。驗(yàn)證關(guān)卡操作通過標(biāo)準(zhǔn)失敗怎么辦第一關(guān)空數(shù)據(jù)data/input/下放一個(gè)空Excel僅表頭summary_*.xlsx生成成功行數(shù)為0run.log無ERROR檢查reader.py的空DataFrame處理邏輯第二關(guān)臟數(shù)據(jù)放一個(gè)含“缺考”“-1”“2024/13/01”等異常值的Excelsummary_*.xlsx里這些行被過濾log_*.txt里有[FILTER] 排除異常得分: -1日志檢查filter_rules解析和應(yīng)用位置第三關(guān)大文件放一個(gè)10MB的Excel10萬行內(nèi)存占用1.5GB執(zhí)行時(shí)間90秒輸出文件完整啟用4.4節(jié)的流式讀取開關(guān)從那以后我每次加新功能都強(qiáng)制走一遍這三關(guān)——哪怕只是改一行注釋。因?yàn)镋xcel處理的玄學(xué)在于99%的文件跑得通1%的臟數(shù)據(jù)能讓整個(gè)流程在深夜11點(diǎn)崩給你看。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取