您现在的位置：程式碼之家 >> 編程語言 > >> 更多程式語言 >> 更多程式設計知識

Python xlrd/xlwt/openpyxl 讀寫 Excel 去除指定欄位重複值完整教學

編輯：更多程式設計知識

Excel 處理庫選擇指南

庫	支援格式	讀取	寫入	現狀	推薦場景
xlrd	.xls	優	不支援	僅維護安全性更新	舊版 .xls 唯讀
xlwt	.xls	不支援	優	停止更新	產出 .xls 相容檔案
openpyxl	.xlsx	優	優	活躍維護	現代 .xlsx 首選
pandas	.xls/.xlsx/.csv	極優	極優	活躍維護	資料分析、批次處理

注意：xlrd 2.0+ 不再支援 .xlsx，讀取 .xlsx 請用 openpyxl 或 pandas。

核心需求：去除指定欄位重複值

場景：Excel 某欄位（如 ID、Email、手機號）有重複資料，需保留首次出現的列，移除後續重複列。

方案一：pandas 一行搞定（推薦）


import pandas as pd

def deduplicate_excel_pandas(input_file, output_file, subset_col, keep='first'):
    """
    :param input_file: 輸入檔案路徑
    :param output_file: 輸出檔案路徑
    :param subset_col: 去重依據的欄位名稱或索引
    :param keep: 'first' 保留首次, 'last' 保留最後, False 全刪除
    """
    df = pd.read_excel(input_file)
    print(f"原始筆數: {len(df)}")
    
    # 去重
    df_dedup = df.drop_duplicates(subset=subset_col, keep=keep)
    print(f"去重後筆數: {len(df_dedup)}")
    print(f"移除重複: {len(df) - len(df_dedup)} 筆")
    
    # 輸出
    df_dedup.to_excel(output_file, index=False)
    print(f"已輸出至: {output_file}")

# 使用範例
# deduplicate_excel_pandas("data.xlsx", "data_dedup.xlsx", "Email")
# deduplicate_excel_pandas("data.xlsx", "data_dedup.xlsx", 0)  # 第 0 欄（第一欄）

進階：多欄位組合去重、保留特定條件


def advanced_deduplicate(input_file, output_file, subset_cols, sort_by=None, keep='first'):
    df = pd.read_excel(input_file)
    
    # 先排序，再去重可控制保留哪一列
    if sort_by:
        df = df.sort_values(by=sort_by, ascending=False)
    
    df_dedup = df.drop_duplicates(subset=subset_cols, keep=keep)
    
    # 重設索引
    df_dedup.reset_index(drop=True, inplace=True)
    df_dedup.to_excel(output_file, index=False)
    return df_dedup

# 範例：依「部門+職稱」去重，保留薪資最高的
# advanced_deduplicate("employees.xlsx", "unique_emps.xlsx", 
#                      subset_cols=["Department", "Title"], 
#                      sort_by="Salary", keep='first')

方案二：openpyxl 純 Python 實作（無 pandas 依賴）


from openpyxl import load_workbook

def deduplicate_openpyxl(input_file, output_file, col_index, keep_first=True):
    """
    :param col_index: 0-based 欄位索引
    :param keep_first: True 保留首次出現, False 保留最後出現
    """
    wb = load_workbook(input_file)
    ws = wb.active
    
    seen = set()
    rows_to_delete = []
    
    # 從第 2 列開始（假設第 1 列為標題）
    for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=False), start=2):
        cell_value = row[col_index].value
        if cell_value in seen:
            rows_to_delete.append(row_idx)
        else:
            seen.add(cell_value)
    
    # 從後往前刪除，避免索引位移
    for row_idx in reversed(rows_to_delete):
        ws.delete_rows(row_idx)
    
    wb.save(output_file)
    print(f"完成：原始 {ws.max_row} 列，刪除 {len(rows_to_delete)} 列，剩餘 {ws.max_row} 列")

# 使用
# deduplicate_openpyxl("data.xlsx", "data_dedup.xlsx", col_index=0)

方案三：xlrd 讀取 + xlwt 寫入（僅 .xls 老檔案）


import xlrd
import xlwt

def deduplicate_xls(input_file, output_file, col_index):
    # 讀取
    rb = xlrd.open_workbook(input_file)
    rs = rb.sheet_by_index(0)
    
    # 寫入
    wb = xlwt.Workbook()
    ws = wb.add_sheet('Sheet1')
    
    seen = set()
    write_row = 0
    
    for row_idx in range(rs.nrows):
        row_values = rs.row_values(row_idx)
        key_value = row_values[col_index]
        
        if key_value not in seen:
            seen.add(key_value)
            for col_idx, val in enumerate(row_values):
                ws.write(write_row, col_idx, val)
            write_row += 1
    
    wb.save(output_file)
    print(f"完成：讀取 {rs.nrows} 列，寫入 {write_row} 列")

實戰進階：大檔案分塊處理（記憶體優化）

當 Excel 超過 100MB，pandas 讀取會佔用大量記憶體，改用 openpyxl 僅讀模式：


from openpyxl import load_workbook

def deduplicate_large_file(input_file, output_file, col_index, chunk_size=10000):
    # 僅讀模式：不載入格式、樣式，極省記憶體
    wb_read = load_workbook(input_file, read_only=True)
    ws_read = wb_read.active
    
    wb_write = load_workbook(input_file)  # 保留樣式需完整載入，或建新檔
    ws_write = wb_write.active
    
    seen = set()
    rows_to_delete = []
    
    for row_idx, row in enumerate(ws_read.iter_rows(min_row=2, values_only=True), start=2):
        key_value = row[col_index]
        if key_value in seen:
            rows_to_delete.append(row_idx)
        else:
            seen.add(key_value)
    
    # 批次刪除（openpyxl 不支援批次刪除，需從後往前）
    for row_idx in reversed(rows_to_delete):
        ws_write.delete_rows(row_idx)
    
    wb_write.save(output_file)
    wb_read.close()
    wb_write.close()

資料清洗常見前置處理


def clean_before_deduplicate(df, subset_col):
    """去重前清洗：去除空白、統一大小寫、處理 NaN"""
    # 複製避免修改原資料
    df = df.copy()
    
    # 字串欄位去除首尾空白
    if df[subset_col].dtype == 'object':
        df[subset_col] = df[subset_col].astype(str).str.strip()
        # 統一大小寫（視業務需求）
        # df[subset_col] = df[subset_col].str.lower()
    
    # 處理 NaN：視為獨特值或移除
    # df = df.dropna(subset=[subset_col])  # 移除空值列
    # df[subset_col] = df[subset_col].fillna('__NA__')  # 標記空值
    
    return df

驗證與報表產出


def generate_dedup_report(original_file, dedup_file, subset_col):
    import pandas as pd
    
    df_orig = pd.read_excel(original_file)
    df_dedup = pd.read_excel(dedup_file)
    
    dup_count = len(df_orig) - len(df_dedup)
    dup_rate = dup_count / len(df_orig) * 100
    
    # 找出重複值詳情
    dup_values = df_orig[df_orig.duplicated(subset=subset_col, keep=False)]
    dup_summary = dup_values.groupby(subset_col).size().reset_index(name='重複次數')
    dup_summary = dup_summary[dup_summary['重複次數'] > 1].sort_values('重複次數', ascending=False)
    
    print(f"=== 去重報表 ===")
    print(f"原始筆數: {len(df_orig)}")
    print(f"去重後: {len(df_dedup)}")
    print(f"移除重複: {dup_count} ({dup_rate:.1f}%)")
    print(f"\n重複值 Top 10:")
    print(dup_summary.head(10).to_string(index=False))
    
    # 可選：輸出重複明細至獨立檔案
    # dup_summary.to_excel("duplicate_report.xlsx", index=False)

完整 CLI 工具範例


#!/usr/bin/env python3
import argparse
import pandas as pd
import sys

def main():
    parser = argparse.ArgumentParser(description="Excel 指定欄位去重工具")
    parser.add_argument("input", help="輸入 Excel 檔案")
    parser.add_argument("output", help="輸出 Excel 檔案")
    parser.add_argument("-c", "--column", required=True, help="去重欄位名稱或索引(0-based)")
    parser.add_argument("-k", "--keep", choices=['first', 'last'], default='first', help="保留策略")
    parser.add_argument("--no-header", action="store_true", help="無標題列")
    parser.add_argument("--report", action="store_true", help="產出去重報表")
    
    args = parser.parse_args()
    
    try:
        # 讀取
        header = None if args.no_header else 0
        df = pd.read_excel(args.input, header=header)
        
        # 處理欄位指定：數字視為索引，字串視為名稱
        subset = int(args.column) if args.column.isdigit() else args.column
        if isinstance(subset, int):
            subset = df.columns[subset]
        
        # 清洗與去重
        original_len = len(df)
        df = clean_before_deduplicate(df, subset)
        df_dedup = df.drop_duplicates(subset=subset, keep=args.keep)
        
        # 輸出
        df_dedup.to_excel(args.output, index=False)
        
        print(f"完成: {original_len} -> {len(df_dedup)} 列 (移除 {original_len - len(df_dedup)} 筆重複)")
        
        if args.report:
            generate_dedup_report(args.input, args.output, subset)
            
    except Exception as e:
        print(f"錯誤: {e}", file=sys.stderr)
        sys.exit(1)

if __name__ == "__main__":
    main()

常見問題與解決

合併儲存格導致讀取錯誤：openpyxl 讀取合併儲存格只會在左上角有值，其他為 None，需先取消合併或填充值
日期/時間格式變數字：pandas 讀取時指定 parse_dates，或輸出時設定格式
超長字串截斷：Excel 單儲存格上限 32,767 字元，超過會報錯
公式而非值：pd.read_excel(..., engine='openpyxl') 讀取的是計算後的值，非公式

總結

Excel 去重首選 pandas，語法簡潔、功能強大、效能優異。drop_duplicates(subset=..., keep=...) 核心 API 涵蓋 95% 需求。無法安裝 pandas 環境時，openpyxl 為次佳選擇，支援 .xlsx 讀寫與樣式保留。舊版 .xls 檔案才需考慮 xlrd/xlwt。實務上務必加上資料清洗（去空白、統一大小寫、處理 NaN），並產出去重報表供業務確認。大檔案場景使用 read_only=True 或分塊處理避免記憶體溢位。