pip install openpyxl

from openpyxl import load_workbook

def clean_excel(file_path, output_path):
    """
    清理Excel文件中的空白行列
    :param file_path: 輸入文件路徑
    :param output_path: 輸出文件路徑
    """
    # 加載工作簿
    wb = load_workbook(filename=file_path)
    
    # 遍歷所有工作表
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        clean_sheet(ws)
    
    # 保存結(jié)果
    wb.save(output_path)
    print(f"處理完成，結(jié)果已保存至 {output_path}")

def clean_sheet(ws):
    """
    清理單個(gè)工作表中的空白行列
    :param ws: 工作表對(duì)象
    """
    # 獲取最大行和最大列
    max_row = ws.max_row
    max_col = ws.max_column
    
    # 從下往上檢查行（避免刪除時(shí)索引變化）
    rows_to_delete = []
    for row in range(max_row, 0, -1):
        is_empty = True
        
        # 檢查該行所有單元格
        for col in range(1, max_col + 1):
            cell = ws.cell(row=row, column=col)
            if cell.value is not None:
                is_empty = False
                break
        
        if is_empty:
            rows_to_delete.append(row)
    
    # 批量刪除行（從下往上）
    for row in rows_to_delete:
        ws.delete_rows(row)
    
    # 接下來(lái)處理列（邏輯類似）
    clean_columns(ws)

def clean_columns(ws):
    """
    清理工作表中的空白列
    :param ws: 工作表對(duì)象
    """
    max_row = ws.max_row
    if max_row == 0:
        return  # 空工作表
    
    max_col = ws.max_column
    cols_to_delete = []
    
    # 從右往左檢查列
    for col in range(max_col, 0, -1):
        is_empty = True
        
        # 檢查該列所有單元格
        for row in range(1, max_row + 1):
            cell = ws.cell(row=row, column=col)
            if cell.value is not None:
                is_empty = False
                break
        
        if is_empty:
            cols_to_delete.append(col)
    
    # 批量刪除列（從右往左）
    for col in cols_to_delete:
        ws.delete_cols(col)

from openpyxl import load_workbook

def clean_excel(file_path, output_path):
    """主清理函數(shù)"""
    wb = load_workbook(filename=file_path)
    
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        clean_sheet(ws)
    
    wb.save(output_path)
    print(f"處理完成，結(jié)果已保存至 {output_path}")

def clean_sheet(ws):
    """清理單個(gè)工作表"""
    # 清理空白行
    max_row = ws.max_row
    max_col = ws.max_column
    
    # 從下往上檢查行
    rows_to_delete = []
    for row in range(max_row, 0, -1):
        if all(ws.cell(row=row, column=col).value is None 
               for col in range(1, max_col + 1)):
            rows_to_delete.append(row)
    
    for row in sorted(rows_to_delete, reverse=True):
        ws.delete_rows(row)
    
    # 更新最大列數(shù)（因?yàn)樾袆h除可能影響列判斷）
    max_col = ws.max_column
    if max_col == 0:
        return
    
    # 清理空白列
    cols_to_delete = []
    for col in range(max_col, 0, -1):
        if all(ws.cell(row=row, column=col).value is None 
               for row in range(1, ws.max_row + 1)):
            cols_to_delete.append(col)
    
    for col in sorted(cols_to_delete, reverse=True):
        ws.delete_cols(col)

# 使用示例
if __name__ == "__main__":
    input_file = "input.xlsx"
    output_file = "output.xlsx"
    clean_excel(input_file, output_file)

# 優(yōu)化后的行刪除（示例）
def delete_rows_optimized(ws, rows_to_delete):
    """批量刪除多行"""
    # 按從大到小排序
    rows_sorted = sorted(rows_to_delete, reverse=True)
    offset = 0
    for row in rows_sorted:
        ws.delete_rows(row - offset)
        offset += 1

from concurrent.futures import ThreadPoolExecutor

def parallel_clean(file_path, output_path, max_workers=4):
    wb = load_workbook(filename=file_path)
    
    def process_sheet(sheet_name):
        ws = wb[sheet_name]
        clean_sheet(ws)
        return ws
    
    with ThreadPoolExecutor(max_workers=max_workers) as executor:
        list(executor.map(process_sheet, wb.sheetnames))
    
    wb.save(output_path)

def is_cell_empty(ws, row, col):
    """檢查單元格是否為空，考慮合并單元格"""
    cell = ws.cell(row=row, column=col)
    if cell.value is not None:
        return False
    
    # 檢查是否屬于合并單元格區(qū)域
    for merge_range in ws.merged_cells.ranges:
        if (row, col) in merge_range.cells:
            # 合并區(qū)域中只要有一個(gè)單元格有值就不算空
            for r in range(merge_range.min_row, merge_range.max_row + 1):
                for c in range(merge_range.min_col, merge_range.max_col + 1):
                    if ws.cell(row=r, column=c).value is not None:
                        return False
            return True  # 合并區(qū)域所有單元格都為空
    
    return True

def is_cell_really_empty(ws, row, col):
    """嚴(yán)格檢查單元格是否為空（包括公式）"""
    cell = ws.cell(row=row, column=col)
    return cell.value is None and cell.data_type == 'n'  # 'n'表示無(wú)數(shù)據(jù)類型

from openpyxl.styles import Font, Border, PatternFill

def copy_style(source_cell, target_cell):
    """復(fù)制單元格樣式"""
    if source_cell.has_style:
        target_cell.font = copy(source_cell.font)
        target_cell.border = copy(source_cell.border)
        target_cell.fill = copy(source_cell.fill)
        target_cell.number_format = copy(source_cell.number_format)
        target_cell.protection = copy(source_cell.protection)
        target_cell.alignment = copy(source_cell.alignment)

from openpyxl import load_workbook
from openpyxl.utils import range_boundaries
from copy import copy

def clean_excel_advanced(file_path, output_path, 
                        handle_merged=True, 
                        preserve_formatting=False):
    """
    高級(jí)Excel清理函數(shù)
    :param file_path: 輸入文件路徑
    :param output_path: 輸出文件路徑
    :param handle_merged: 是否處理合并單元格
    :param preserve_formatting: 是否保留格式
    """
    wb = load_workbook(filename=file_path)
    
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        clean_sheet_advanced(ws, handle_merged, preserve_formatting)
    
    wb.save(output_path)
    print(f"高級(jí)處理完成，結(jié)果已保存至 {output_path}")

def clean_sheet_advanced(ws, handle_merged=True, preserve_formatting=False):
    """高級(jí)工作表清理"""
    # 1. 處理空白行
    max_row = ws.max_row
    max_col = ws.max_column
    
    rows_to_delete = []
    for row in range(max_row, 0, -1):
        is_empty = True
        
        for col in range(1, max_col + 1):
            if not is_cell_empty(ws, row, col, handle_merged):
                is_empty = False
                break
        
        if is_empty:
            rows_to_delete.append(row)
    
    # 批量刪除行
    for row in sorted(rows_to_delete, reverse=True):
        ws.delete_rows(row)
    
    # 更新最大列
    max_col = ws.max_column
    if max_col == 0:
        return
    
    # 2. 處理空白列
    cols_to_delete = []
    for col in range(max_col, 0, -1):
        is_empty = True
        
        for row in range(1, ws.max_row + 1):
            if not is_cell_empty(ws, row, col, handle_merged):
                is_empty = False
                break
        
        if is_empty:
            cols_to_delete.append(col)
    
    # 批量刪除列
    for col in sorted(cols_to_delete, reverse=True):
        ws.delete_cols(col)

def is_cell_empty(ws, row, col, handle_merged=True):
    """檢查單元格是否為空"""
    cell = ws.cell(row=row, column=col)
    if cell.value is not None:
        return False
    
    if handle_merged:
        for merge_range in ws.merged_cells.ranges:
            min_col, min_row, max_col, max_row = range_boundaries(str(merge_range))
            if (row >= min_row and row <= max_row and 
                col >= min_col and col <= max_col):
                # 檢查合并區(qū)域是否有值
                for r in range(min_row, max_row + 1):
                    for c in range(min_col, max_col + 1):
                        if ws.cell(row=r, column=c).value is not None:
                            return False
                return True
    
    return True

# 使用示例
if __name__ == "__main__":
    input_file = "complex_input.xlsx"
    output_file = "cleaned_output.xlsx"
    clean_excel_advanced(
        input_file, 
        output_file,
        handle_merged=True,
        preserve_formatting=False
    )