簡(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雙庫(kù)典型用法。目前已有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分鐘——這還沒(méi)算改錯(cuò)重來(lái)的返工時(shí)間。我甩出這個(gè)python讀取Excel并寫入.zip包解壓、改兩行路徑、雙擊運(yùn)行48秒跑完全部流程輸出帶格式的匯總表柱狀圖PDF。它不是炫技的AI大模型就是一個(gè)干凈利落的Python工程包不依賴數(shù)據(jù)庫(kù)、不調(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è)包沒(méi)搞花活技術(shù)選型全是血淚經(jīng)驗(yàn)堆出來(lái)的。早期用過(guò)xlrd讀.xls結(jié)果客戶發(fā)來(lái).xlsx就直接報(bào)錯(cuò)試過(guò)純csv處理但Excel里的合并單元格、日期格式、顏色標(biāo)記全丟了還踩過(guò)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ò)濾規(guī)則等配置項(xiàng) ├── data/ │ ├── input/ # 原始Excel存放目錄自動(dòng)掃描所有.xlsx/.xls │ └── output/ # 輸出目錄含匯總表、圖表、日志 ├── src/ │ ├── main.py # 主入口調(diào)度讀取→清洗→合并→寫入→繪圖 │ ├── reader.py # 核心讀取器支持多sheet、跳過(guò)空行、類型推斷 │ └── 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)過(guò)200份真實(shí)Excel文件實(shí)測(cè)最穩(wěn)的組合。高版本pandas對(duì)中文路徑支持有玄學(xué)bug低版本openpyxl寫入圖表會(huì)丟坐標(biāo)軸標(biāo)簽——這些坑我都替你踩過(guò)了。2.1 配置驅(qū)動(dòng)設(shè)計(jì)為什么把字段名和過(guò)濾條件抽到JSON里硬編碼字段名是二次開(kāi)發(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ù)頁(yè)] }, 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)題、說(shuō)明、空行。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開(kāi)始讀自動(dòng)設(shè)表頭 df pd.read_excel( file_path, sheet_namesheet_name, skiprowsheader_row - 1, # pandas的skiprows是從0開(kāi)始計(jì)數(shù) dtypestr # 先全讀成字符串避免數(shù)字被轉(zhuǎn)成float ) return df關(guān)鍵點(diǎn)skiprowsheader_row - 1是易錯(cuò)點(diǎn)——openpyxl的行號(hào)從1開(kāi)始pandas的skiprows參數(shù)卻是從0開(kāi)始計(jì)數(shù)。這里少減1就會(huì)漏掉表頭多減1就多跳一行。我加了注釋但第一次跑還是翻車了兩次。2.3 寫入器的樣式控制怎么讓輸出表看起來(lái)像人手做的src/writer.py的write_with_style()方法用openpyxl手動(dòng)控制樣式避開(kāi)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行開(kāi)始寫數(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)防止單列過(guò)寬撐爆頁(yè)面——這是從客戶打印需求里摳出來(lái)的細(xì)節(jié)。3. 運(yùn)行全流程從解壓到看到PDF圖表只需三步別被“Python項(xiàng)目”嚇住這個(gè)包對(duì)新手極其友好。全程不需要裝Python環(huán)境自帶便攜版也不用開(kāi)終端敲命令有雙擊腳本。3.1 環(huán)境準(zhǔn)備為什么推薦用run.bat而不是直接python main.pyrun.bat不是簡(jiǎn)單包裝它解決了三個(gè)隱形問(wèn)題Python路徑隔離自動(dòng)檢測(cè)系統(tǒng)是否已裝Python若無(wú)則啟用包內(nèi)python-3.9.13-embed-amd64便攜版虛擬環(huán)境兜底創(chuàng)建獨(dú)立venv避免你本地pandas版本沖突錯(cuò)誤捕獲可視化所有print和異常都重定向到data/output/run.log雙擊bat后即使黑窗一閃而過(guò)日志里也留痕。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] 開(kāi)始執(zhí)行main.py... python src\main.py data\output\run.log 21 if %errorlevel% 0 ( echo [SUCCESS] 執(zhí)行完成結(jié)果見(jiàn) 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è)試過(guò)Office 2003到365全兼容? 文件名任意但建議用中文無(wú)空格如實(shí)驗(yàn)數(shù)據(jù)_202405.xlsx? 每個(gè)文件可含多個(gè)sheet程序自動(dòng)掃描config/settings.json里定義的sheet_names? 不支持密碼保護(hù)的Excelopenpyxl不支持解密? 不支持宏.xlsm會(huì)靜默跳過(guò)。實(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ù)頁(yè) [INFO] 正在處理: 期中成績(jī).xlsx - 成績(jī)單 ...如果某文件打不開(kāi)會(huì)在run.log里記錄[ERROR] 無(wú)法讀取 XXX.xlsx: Permission denied不影響其他文件繼續(xù)處理。3.3 輸出物詳解output目錄里每個(gè)文件都是干什么的跑完后data/output/下會(huì)生成4類文件各司其職文件名類型作用查看方式summary_20240520_1432.xlsxExcel清洗合并后的主數(shù)據(jù)表含自動(dòng)列寬、表頭樣式Excel雙擊打開(kāi)chart_summary_20240520_1432.pdfPDF柱狀圖得分分布 折線圖日期趨勢(shì)PDF閱讀器log_20240520_1432.txt文本詳細(xì)執(zhí)行日志讀取行數(shù)、過(guò)濾掉多少行、耗時(shí)統(tǒng)計(jì)記事本打開(kāi)run.log文本全局錯(cuò)誤日志含Traceback排查問(wèn)題必看特別說(shuō)明chart_summary_*.pdf圖表用matplotlib生成但導(dǎo)出PDF前做了兩件事——設(shè)置中文字體plt.rcParams[font.sans-serif] [SimHei, Arial Unicode MS]關(guān)閉交互模式plt.ioff()防止生成過(guò)程中彈窗卡死。所以你不會(huì)看到任何matplotlib窗口只有靜默生成的PDF。4. 常見(jiàn)問(wèn)題排查那些讓我凌晨三點(diǎn)還在改的坑4.1 現(xiàn)象雙擊run.bat后黑窗閃退output目錄空空如也原因系統(tǒng)缺少M(fèi)icrosoft Visual C 2015-2022運(yùn)行庫(kù)便攜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í)沒(méi)識(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)沒(méi)有SimHei字體且matplotlib緩存了舊字體列表解決下載simhei.ttf放到src/目錄下修改src/main.py開(kāi)頭強(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)-顯示為方塊的問(wèn)題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ù)開(kāi)關(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}過(guò)大啟用流式讀取不保留樣式) 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表頭有不可見(jiàn)空格如“學(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. 二次開(kāi)發(fā)實(shí)戰(zhàn)如何把“讀Excel寫Excel”改成“讀Excel調(diào)API寫數(shù)據(jù)庫(kù)”這才是這個(gè)包真正的價(jià)值——它不是終點(diǎn)而是你定制化開(kāi)發(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)通過(guò)的行 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ù)庫(kù)寫入用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ù)庫(kù)寫入 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里但沒(méi)放進(jìn)初始包因?yàn)樯婕班]箱密碼明文風(fēng)險(xiǎn)。你需要自己補(bǔ)上或者用環(huán)境變量注入。5.4 驗(yàn)證技巧如何確保改完的代碼沒(méi)破壞原有功能我給自己定的鐵律每次改完必須跑三關(guān)驗(yàn)證。驗(yàn)證關(guān)卡操作通過(guò)標(biāo)準(zhǔn)失敗怎么辦第一關(guān)空數(shù)據(jù)data/input/下放一個(gè)空Excel僅表頭summary_*.xlsx生成成功行數(shù)為0run.log無(wú)ERROR檢查reader.py的空DataFrame處理邏輯第二關(guān)臟數(shù)據(jù)放一個(gè)含“缺考”“-1”“2024/13/01”等異常值的Excelsummary_*.xlsx里這些行被過(guò)濾log_*.txt里有[FILTER] 排除異常得分: -1日志檢查filter_rules解析和應(yīng)用位置第三關(guān)大文件放一個(gè)10MB的Excel10萬(wàn)行內(nèi)存占用1.5GB執(zhí)行時(shí)間90秒輸出文件完整啟用4.4節(jié)的流式讀取開(kāi)關(guān)從那以后我每次加新功能都強(qiáng)制走一遍這三關(guān)——哪怕只是改一行注釋。因?yàn)镋xcel處理的玄學(xué)在于99%的文件跑得通1%的臟數(shù)據(jù)能讓整個(gè)流程在深夜11點(diǎn)崩給你看。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取