pip install pandas openpyxl xlrd

import pandas as pd

def remove_empty_rows_cols(file_path, output_path):
    # 讀取Excel文件（自動識別擴展名）
    df = pd.read_excel(file_path)
    
    # 刪除全為空的行
    df = df.dropna(how='all')
    
    # 刪除全為空的列
    df = df.dropna(how='all', axis=1)
    
    # 保存結(jié)果
    df.to_excel(output_path, index=False)

# 使用示例
remove_empty_rows_cols('input.xlsx', 'output.xlsx')

def advanced_clean(file_path, output_path, row_threshold=0.7, col_threshold=0.7):
    df = pd.read_excel(file_path)
    
    # 計算每行非空比例
    row_non_null = df.notna().mean(axis=1)
    # 保留非空比例大于閾值的行
    df = df[row_non_null > row_threshold]
    
    # 計算每列非空比例
    col_non_null = df.notna().mean(axis=0)
    # 保留非空比例大于閾值的列
    df = df.loc[:, col_non_null > col_threshold]
    
    df.to_excel(output_path, index=False)

# 使用示例：保留非空率>30%的行列
advanced_clean('input.xlsx', 'output_advanced.xlsx', 0.3, 0.3)

def clean_multi_sheet(file_path, output_path):
    with pd.ExcelWriter(output_path) as writer:
        xls = pd.ExcelFile(file_path)
        for sheet_name in xls.sheet_names:
            df = pd.read_excel(file_path, sheet_name=sheet_name)
            df = df.dropna(how='all').dropna(how='all', axis=1)
            df.to_excel(writer, sheet_name=sheet_name, index=False)

# 使用示例
clean_multi_sheet('multi_sheet.xlsx', 'cleaned_multi.xlsx')

from openpyxl import load_workbook

def remove_empty_with_openpyxl(file_path, output_path):
    wb = load_workbook(file_path)
    for sheet in wb.worksheets:
        # 刪除空白列（從后往前刪除避免索引錯亂）
        for col in range(sheet.max_column, 0, -1):
            if all(cell.value is None for cell in sheet[col]):
                sheet.delete_cols(col)
        
        # 刪除空白行（從下往上刪除）
        for row in range(sheet.max_row, 0, -1):
            if all(sheet.cell(row=row, column=col).value is None 
                  for col in range(1, sheet.max_column + 1)):
                sheet.delete_rows(row)
    
    wb.save(output_path)

# 使用示例
remove_empty_with_openpyxl('format_important.xlsx', 'cleaned_format.xlsx')

def smart_clean(file_path, output_path, key_column=None):
    wb = load_workbook(file_path)
    for sheet in wb.worksheets:
        # 確定關(guān)鍵列（如果指定）
        key_col_index = None
        if key_column:
            for col in range(1, sheet.max_column + 1):
                if sheet.cell(row=1, column=col).value == key_column:
                    key_col_index = col
                    break
        
        # 刪除行邏輯
        rows_to_delete = []
        for row in range(sheet.max_row, 0, -1):
            # 關(guān)鍵列空白或整行空白則標記刪除
            if (key_col_index and sheet.cell(row=row, column=key_col_index).value is None) or \
               all(sheet.cell(row=row, column=col).value is None 
                  for col in range(1, sheet.max_column + 1)):
                rows_to_delete.append(row)
        
        for row in sorted(rows_to_delete, reverse=True):
            sheet.delete_rows(row)
        
        # 類似邏輯處理列...
    
    wb.save(output_path)

# 使用示例：保留"ID"列非空的行
smart_clean('data_with_id.xlsx', 'cleaned_id.xlsx', key_column='ID')

def optimized_clean(file_path, output_path, chunk_size=10000):
    # 判斷文件類型選擇處理方式
    if file_path.endswith('.xlsx'):
        # 對于大文件使用分塊處理策略
        from openpyxl import load_workbook
        wb = load_workbook(file_path, read_only=True)
        new_wb = load_workbook(file_path)  # 創(chuàng)建新對象用于寫入
        
        for i, sheet in enumerate(wb.worksheets):
            new_sheet = new_wb.worksheets[i]
            
            # 獲取非空行列索引（簡化示例）
            rows_to_keep = []
            cols_to_keep = []
            
            # 實際項目中這里需要更高效的掃描算法
            for row in range(1, sheet.max_row + 1):
                if any(sheet.cell(row=row, column=col).value is not None 
                      for col in range(1, sheet.max_column + 1)):
                    rows_to_keep.append(row)
            
            # 類似處理列...
            
            # 實際應(yīng)用中這里需要實現(xiàn)高效的數(shù)據(jù)復(fù)制
            # 此處僅為示意，實際代碼需要優(yōu)化
            for row_idx in rows_to_keep:
                for col_idx in cols_to_keep:
                    new_sheet.cell(row=row_idx, column=col_idx).value = \
                        sheet.cell(row=row_idx, column=col_idx).value
        
        new_wb.save(output_path)
    
    else:  # 處理xls文件
        import pandas as pd
        reader = pd.read_excel(file_path, chunksize=chunk_size)
        # 實際處理邏輯...

# 使用示例（實際使用時需要完善內(nèi)部邏輯）
optimized_clean('large_file.xlsx', 'optimized_output.xlsx')

import argparse

def main():
    parser = argparse.ArgumentParser(description='Excel空白行列清理工具')
    parser.add_argument('input', help='輸入文件路徑')
    parser.add_argument('output', help='輸出文件路徑')
    parser.add_argument('--pandas', action='store_true', help='使用pandas處理')
    parser.add_argument('--threshold', type=float, default=0.7, 
                       help='非空比例閾值(0-1)')
    
    args = parser.parse_args()
    
    if args.pandas:
        advanced_clean(args.input, args.output, args.threshold, args.threshold)
    else:
        remove_empty_with_openpyxl(args.input, args.output)

if __name__ == '__main__':
    main()

import schedule
import time
from datetime import datetime

def scheduled_clean():
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    input_file = f"daily_data_{timestamp}.xlsx"
    output_file = f"cleaned_data_{timestamp}.xlsx"
    
    # 這里調(diào)用之前的清理函數(shù)
    remove_empty_rows_cols(input_file, output_file)
    print(f"處理完成: {output_file}")

# 每天凌晨3點執(zhí)行
schedule.every().day.at("03:00").do(scheduled_clean)

while True:
    schedule.run_pending()
    time.sleep(60)

import unittest
import pandas as pd
import os

class TestExcelClean(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        # 創(chuàng)建測試文件
        data = {
            'A': [1, None, None, 4],
            'B': [None, 'x', None, None],
            'C': [None, None, None, None]
        }
        df = pd.DataFrame(data)
        df.to_excel('test_input.xlsx', index=False)
    
    def test_basic_clean(self):
        remove_empty_rows_cols('test_input.xlsx', 'test_output.xlsx')
        result = pd.read_excel('test_output.xlsx')
        self.assertEqual(result.shape, (2, 2))  # 應(yīng)保留2行2列
    
    @classmethod
    def tearDownClass(cls):
        # 清理測試文件
        for file in ['test_input.xlsx', 'test_output.xlsx']:
            if os.path.exists(file):
                os.remove(file)

if __name__ == '__main__':
    unittest.main()