# 基礎(chǔ)庫
pip install pandas

# Excel處理引擎
pip install openpyxl        # 支持.xlsx文件讀寫
pip install xlsxwriter      # 支持高級格式化功能
pip install xlrd           # 支持舊版.xls文件讀?。蛇x）

import pandas as pd
import numpy as np
from datetime import datetime, date

# 可選：樣式相關(guān)
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Alignment
from openpyxl.utils.dataframe import dataframe_to_rows

# 讀取Excel文件
 df = pd.read_excel('data.xlsx')

# 讀取指定工作表
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# 讀取多個工作表
dfs = pd.read_excel('data.xlsx', sheet_name=['Sheet1', 'Sheet2'])

# 讀取所有工作表
dfs = pd.read_excel('data.xlsx', sheet_name=None)  # 返回字典

# 指定讀取范圍
df = pd.read_excel('data.xlsx', usecols='A:C')  # 讀取A到C列
df = pd.read_excel('data.xlsx', usecols=[0, 2, 4])  # 讀取指定列索引
df = pd.read_excel('data.xlsx', nrows=100)  # 只讀取前100行
df = pd.read_excel('data.xlsx', skiprows=3)  # 跳過前3行

# 指定索引列
df = pd.read_excel('data.xlsx', index_col=0)  # 第一列作為索引
df = pd.read_excel('data.xlsx', index_col='ID')  # 指定列名作為索引

# 處理缺失值
df = pd.read_excel('data.xlsx', na_values=['NA', 'N/A', 'null'])

# 指定數(shù)據(jù)類型
df = pd.read_excel('data.xlsx', dtype={'列名': str, '數(shù)值列': float})

# 解析日期列
df = pd.read_excel('data.xlsx', parse_dates=['日期列'])
df = pd.read_excel('data.xlsx', parse_dates={'日期時間': ['日期列', '時間列']})

# openpyxl引擎（默認(rèn)，功能全面）
df = pd.read_excel('data.xlsx', engine='openpyxl')

# xlrd引擎（適合舊版.xls文件）
df = pd.read_excel('data.xls', engine='xlrd')

# odf引擎（支持.ods文件）
df = pd.read_excel('data.ods', engine='odf')

# 基礎(chǔ)寫入
df.to_excel('output.xlsx', index=False)

# 寫入指定工作表
df.to_excel('output.xlsx', sheet_name='數(shù)據(jù)表', index=False)

# 不寫入列名
df.to_excel('output.xlsx', header=False, index=False)

# 追加模式（需要openpyxl引擎）
with pd.ExcelWriter('output.xlsx', mode='a', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='新工作表', index=False)

# 創(chuàng)建ExcelWriter對象
with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
    df1.to_excel(writer, sheet_name='數(shù)據(jù)1', index=False)
    df2.to_excel(writer, sheet_name='數(shù)據(jù)2', index=False)
    df3.to_excel(writer, sheet_name='數(shù)據(jù)3', index=False)

# 使用xlsxwriter引擎（支持更多格式化選項）
with pd.ExcelWriter('output.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='數(shù)據(jù)', index=False)
    
    # 獲取工作簿和工作表對象
    workbook = writer.book
    worksheet = writer.sheets['數(shù)據(jù)']
    
    # 設(shè)置列寬
    worksheet.set_column('A:C', 20)
    
    # 添加格式
    header_format = workbook.add_format({
        'bold': True,
        'text_wrap': True,
        'valign': 'top',
        'fg_color': '#D7E4BD',
        'border': 1
    })
    
    # 應(yīng)用表頭格式
    for col_num, value in enumerate(df.columns.values):
        worksheet.write(0, col_num, value, header_format)

# 方法1：讀取所有工作表
all_sheets = pd.read_excel('data.xlsx', sheet_name=None)
for sheet_name, df in all_sheets.items():
    print(f"工作表: {sheet_name}")
    print(df.head())

# 方法2：讀取指定工作表列表
sheets = ['銷售數(shù)據(jù)', '庫存數(shù)據(jù)', '客戶數(shù)據(jù)']
dfs = pd.read_excel('data.xlsx', sheet_name=sheets)

# 訪問特定工作表
sales_df = dfs['銷售數(shù)據(jù)']
inventory_df = dfs['庫存數(shù)據(jù)']

# 創(chuàng)建多個工作表
with pd.ExcelWriter('report.xlsx', engine='openpyxl') as writer:
    # 寫入不同數(shù)據(jù)到不同工作表
    sales_df.to_excel(writer, sheet_name='銷售報表', index=False)
    inventory_df.to_excel(writer, sheet_name='庫存報表', index=False)
    customer_df.to_excel(writer, sheet_name='客戶信息', index=False)
    
    # 寫入?yún)R總數(shù)據(jù)
    summary_df.to_excel(writer, sheet_name='匯總統(tǒng)計', index=False)

# 添加工作表到現(xiàn)有文件
with pd.ExcelWriter('existing.xlsx', mode='a', engine='openpyxl', 
                   if_sheet_exists='replace') as writer:
    new_df.to_excel(writer, sheet_name='新數(shù)據(jù)', index=False)

# 創(chuàng)建樣式化數(shù)據(jù)框
def highlight_max(s):
    is_max = s == s.max()
    return ['background-color: yellow' if v else '' for v in is_max]

def color_negative_red(val):
    color = 'red' if val < 0 else 'black'
    return f'color: {color}'

# 應(yīng)用樣式
styled_df = df.style.applymap(color_negative_red, subset=['數(shù)值列'])
styled_df = styled_df.apply(highlight_max, subset=['數(shù)值列'])

# 導(dǎo)出帶樣式的Excel
styled_df.to_excel('styled_output.xlsx', engine='openpyxl', index=False)

# 使用xlsxwriter進行高級格式化
with pd.ExcelWriter('formatted_report.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='數(shù)據(jù)', index=False)
    
    # 獲取工作簿和工作表
    workbook = writer.book
    worksheet = writer.sheets['數(shù)據(jù)']
    
    # 定義格式
    header_format = workbook.add_format({
        'bold': True,
        'text_wrap': True,
        'valign': 'top',
        'fg_color': '#4F81BD',
        'font_color': 'white',
        'border': 1
    })
    
    money_format = workbook.add_format({'num_format': '￥#,##0.00'})
    date_format = workbook.add_format({'num_format': 'yyyy-mm-dd'})
    percent_format = workbook.add_format({'num_format': '0.00%'})
    
    # 獲取數(shù)據(jù)維度
    max_row, max_col = df.shape
    
    # 應(yīng)用表頭格式
    for col_num, value in enumerate(df.columns.values):
        worksheet.write(0, col_num, value, header_format)
    
    # 設(shè)置列寬和格式
    worksheet.set_column('A:A', 15)  # 設(shè)置A列寬度
    worksheet.set_column('B:B', 12, date_format)  # B列使用日期格式
    worksheet.set_column('C:C', 15, money_format)  # C列使用貨幣格式
    worksheet.set_column('D:D', 10, percent_format)  # D列使用百分比格式
    
    # 添加條件格式
    worksheet.conditional_format(1, 2, max_row, 2, {  # C列
        'type': '3_color_scale',
        'min_color': '#F8696B',
        'mid_color': '#FFEB9C',
        'max_color': '#63BE7B'
    })
    
    # 添加數(shù)據(jù)條
    worksheet.conditional_format(1, 3, max_row, 3, {  # D列
        'type': 'data_bar',
        'bar_color': '#63C384'
    })

from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.utils.dataframe import dataframe_to_rows

# 創(chuàng)建工作簿和工作表
wb = Workbook()
ws = wb.active
ws.title = "樣式示例"

# 添加數(shù)據(jù)到工作表
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

# 定義樣式
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid")
border = Border(left=Side(style='thin'), right=Side(style='thin'), 
                top=Side(style='thin'), bottom=Side(style='thin'))
center_alignment = Alignment(horizontal='center', vertical='center')

# 應(yīng)用表頭樣式
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.border = border
    cell.alignment = center_alignment

# 設(shè)置列寬
for column in ws.columns:
    max_length = 0
    column_letter = column[0].column_letter
    for cell in column:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = min(max_length + 2, 50)
    ws.column_dimensions[column_letter].width = adjusted_width

# 保存文件
wb.save('styled_with_openpyxl.xlsx')

import xlsxwriter

# 創(chuàng)建帶圖表的Excel文件
with pd.ExcelWriter('chart_report.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='數(shù)據(jù)', index=False)
    
    workbook = writer.book
    worksheet = writer.sheets['數(shù)據(jù)']
    
    # 創(chuàng)建圖表
    chart = workbook.add_chart({'type': 'column'})
    
    # 配置圖表數(shù)據(jù)
    max_row = len(df) + 1
    chart.add_series({
        'name': ['數(shù)據(jù)', 0, 1],  # 系列名稱
        'categories': ['數(shù)據(jù)', 1, 0, max_row, 0],  # X軸標(biāo)簽
        'values': ['數(shù)據(jù)', 1, 1, max_row, 1],  # Y軸數(shù)據(jù)
    })
    
    # 設(shè)置圖表標(biāo)題和軸標(biāo)簽
    chart.set_title({'name': '銷售數(shù)據(jù)分析'})
    chart.set_x_axis({'name': '月份'})
    chart.set_y_axis({'name': '銷售額'})
    
    # 插入圖表
    worksheet.insert_chart('E2', chart)

# 創(chuàng)建數(shù)據(jù)透 視表
pivot_table = pd.pivot_table(df, 
                           values=['銷售額', '數(shù)量'], 
                           index=['地區(qū)'], 
                           columns=['產(chǎn)品類別'], 
                           aggfunc={'銷售額': np.sum, '數(shù)量': np.mean},
                           fill_value=0)

# 寫入數(shù)據(jù)透 視表
with pd.ExcelWriter('pivot_report.xlsx', engine='openpyxl') as writer:
    # 寫入原始數(shù)據(jù)
    df.to_excel(writer, sheet_name='原始數(shù)據(jù)', index=False)
    
    # 寫入數(shù)據(jù)透 視表
    pivot_table.to_excel(writer, sheet_name='數(shù)據(jù)透 視表')

# 使用xlsxwriter添加公式
with pd.ExcelWriter('formula_report.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='數(shù)據(jù)', index=False)
    
    workbook = writer.book
    worksheet = writer.sheets['數(shù)據(jù)']
    
    # 添加公式
    max_row = len(df) + 1
    worksheet.write(max_row, 1, '總計:', workbook.add_format({'bold': True}))
    worksheet.write(max_row, 2, f'=SUM(C2:C{max_row-1})')
    worksheet.write(max_row, 3, f'=SUM(D2:D{max_row-1})')
    
    # 添加平均值
    worksheet.write(max_row + 1, 1, '平均值:', workbook.add_format({'bold': True}))
    worksheet.write(max_row + 1, 2, f'=AVERAGE(C2:C{max_row-1})')
    worksheet.write(max_row + 1, 3, f'=AVERAGE(D2:D{max_row-1})')

# 分批讀取大文件
chunk_size = 10000
chunks = []

for chunk in pd.read_excel('large_file.xlsx', chunksize=chunk_size):
    # 處理每個chunk
    processed_chunk = chunk[chunk['銷售額'] > 1000]  # 示例處理
    chunks.append(processed_chunk)

# 合并所有chunks
final_df = pd.concat(chunks, ignore_index=True)

# 寫入文件（使用更快的引擎）
with pd.ExcelWriter('processed_large.xlsx', engine='xlsxwriter') as writer:
    final_df.to_excel(writer, sheet_name='處理結(jié)果', index=False)

# 指定數(shù)據(jù)類型減少內(nèi)存使用
dtype_dict = {
    '整數(shù)列': 'int32',
    '浮點列': 'float32',
    '字符串列': 'category',
    '日期列': 'datetime64[ns]'
}

df = pd.read_excel('data.xlsx', dtype=dtype_dict)

# 只讀取需要的列
use_cols = ['需要的列1', '需要的列2', '需要的列3']
df = pd.read_excel('data.xlsx', usecols=use_cols)

import concurrent.futures
import pandas as pd

def process_sheet(sheet_name):
    """處理單個工作表"""
    df = pd.read_excel('multi_sheet_file.xlsx', sheet_name=sheet_name)
    # 進行處理...
    return sheet_name, df

# 并行處理多個工作表
sheet_names = ['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4']
results = {}

with concurrent.futures.ThreadPoolExecutor(max_workers=4) as executor:
    futures = [executor.submit(process_sheet, sheet) for sheet in sheet_names]
    
    for future in concurrent.futures.as_completed(futures):
        sheet_name, processed_df = future.result()
        results[sheet_name] = processed_df

# 寫入結(jié)果
with pd.ExcelWriter('parallel_processed.xlsx', engine='openpyxl') as writer:
    for sheet_name, df in results.items():
        df.to_excel(writer, sheet_name=sheet_name, index=False)

# 處理中文編碼問題
try:
    df = pd.read_excel('chinese_file.xlsx', encoding='utf-8')
except UnicodeDecodeError:
    df = pd.read_excel('chinese_file.xlsx', encoding='gbk')

# 或者讓pandas自動檢測編碼
df = pd.read_excel('file.xlsx')

# 處理混合數(shù)據(jù)類型
df = pd.read_excel('mixed_types.xlsx', dtype=str)  # 全部讀取為字符串

# 指定列的數(shù)據(jù)類型
dtype_dict = {'ID': str, '日期': str, '數(shù)值': float}
df = pd.read_excel('file.xlsx', dtype=dtype_dict)

# 處理日期時間
df = pd.read_excel('file.xlsx', parse_dates=['日期列'])

# 分批處理大文件
chunk_size = 5000
result_chunks = []

for chunk in pd.read_excel('large_file.xlsx', chunksize=chunk_size):
    # 處理chunk
    processed = chunk.groupby('類別').sum()
    result_chunks.append(processed)

# 合并結(jié)果
final_result = pd.concat(result_chunks).groupby(level=0).sum()

# 確保正確關(guān)閉文件
try:
    with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='數(shù)據(jù)', index=False)
        # 其他操作...
except Exception as e:
    print(f"錯誤: {e}")
finally:
    # 確保文件被正確關(guān)閉
    pass

import pandas as pd
import numpy as np
from datetime import datetime, timedelta

# 生成示例數(shù)據(jù)
np.random.seed(42)
dates = pd.date_range(start='2024-01-01', end='2024-12-31', freq='D')
regions = ['華北', '華東', '華南', '西南', '西北']
products = ['產(chǎn)品A', '產(chǎn)品B', '產(chǎn)品C', '產(chǎn)品D', '產(chǎn)品E']

# 創(chuàng)建銷售數(shù)據(jù)
data = []
for date in dates:
    for region in regions:
        for product in products:
            sales = np.random.randint(100, 1000)
            quantity = np.random.randint(10, 100)
            data.append({
                '日期': date,
                '地區(qū)': region,
                '產(chǎn)品': product,
                '銷售額': sales,
                '數(shù)量': quantity
            })

sales_df = pd.DataFrame(data)

# 生成銷售報表
def generate_sales_report(df, output_file='sales_report.xlsx'):
    with pd.ExcelWriter(output_file, engine='xlsxwriter') as writer:
        workbook = writer.book
        
        # 原始數(shù)據(jù)
        df.to_excel(writer, sheet_name='原始數(shù)據(jù)', index=False)
        
        # 月度匯總
        monthly_summary = df.groupby([df['日期'].dt.to_period('M'), '地區(qū)']).agg({
            '銷售額': 'sum',
            '數(shù)量': 'sum'
        }).reset_index()
        monthly_summary['日期'] = monthly_summary['日期'].astype(str)
        monthly_summary.to_excel(writer, sheet_name='月度匯總', index=False)
        
        # 產(chǎn)品分析
        product_analysis = df.groupby('產(chǎn)品').agg({
            '銷售額': ['sum', 'mean', 'std'],
            '數(shù)量': ['sum', 'mean', 'std']
        }).round(2)
        product_analysis.to_excel(writer, sheet_name='產(chǎn)品分析')
        
        # 地區(qū)分析
        region_analysis = df.groupby('地區(qū)').agg({
            '銷售額': 'sum',
            '數(shù)量': 'sum'
        }).reset_index()
        region_analysis.to_excel(writer, sheet_name='地區(qū)分析', index=False)
        
        # 格式化工作表
        for sheet_name in ['月度匯總', '地區(qū)分析']:
            worksheet = writer.sheets[sheet_name]
            
            # 設(shè)置列寬
            worksheet.set_column('A:A', 15)
            worksheet.set_column('B:E', 12)
            
            # 添加貨幣格式
            money_format = workbook.add_format({'num_format': '￥#,##0.00'})
            worksheet.set_column('C:C', 12, money_format)
            
            # 添加數(shù)據(jù)條
            max_row = len(worksheet.tables.get(sheet_name, [])) + 1
            worksheet.conditional_format(1, 2, max_row, 2, {
                'type': 'data_bar',
                'bar_color': '#63C384'
            })

# 生成報表
generate_sales_report(sales_df)
print("銷售報表生成完成！")

def generate_financial_report(transactions_df, output_file='financial_report.xlsx'):
    """生成財務(wù)報表"""
    
    with pd.ExcelWriter(output_file, engine='xlsxwriter') as writer:
        workbook = writer.book
        
        # 1. 原始交易數(shù)據(jù)
        transactions_df.to_excel(writer, sheet_name='交易明細(xì)', index=False)
        
        # 2. 月度收支匯總
        monthly_cashflow = transactions_df.groupby([
            transactions_df['日期'].dt.to_period('M'),
            '類別'
        ])['金額'].sum().unstack(fill_value=0)
        monthly_cashflow.to_excel(writer, sheet_name='月度收支')
        
        # 3. 資產(chǎn)負(fù)債表
        balance_sheet = transactions_df.groupby('賬戶').agg({
            '金額': 'sum'
        }).reset_index()
        balance_sheet.to_excel(writer, sheet_name='資產(chǎn)負(fù)債表', index=False)
        
        # 4. 收支趨勢
        daily_trend = transactions_df.groupby(transactions_df['日期'].dt.date)['金額'].sum()
        daily_trend.to_excel(writer, sheet_name='收支趨勢')
        
        # 格式化
        formats = {
            '貨幣': workbook.add_format({'num_format': '￥#,##0.00'}),
            '百分比': workbook.add_format({'num_format': '0.00%'}),
            '日期': workbook.add_format({'num_format': 'yyyy-mm-dd'}),
            '表頭': workbook.add_format({
                'bold': True,
                'fg_color': '#4F81BD',
                'font_color': 'white'
            })
        }
        
        # 應(yīng)用格式到各個工作表
        for sheet_name in writer.sheets:
            worksheet = writer.sheets[sheet_name]
            
            # 設(shè)置列寬
            worksheet.set_column('A:Z', 15)
            
            # 應(yīng)用貨幣格式到金額列
            if sheet_name == '交易明細(xì)':
                worksheet.set_column('C:C', 15, formats['貨幣'])
                worksheet.set_column('A:A', 12, formats['日期'])
            elif sheet_name == '資產(chǎn)負(fù)債表':
                worksheet.set_column('B:B', 15, formats['貨幣'])

# 生成示例財務(wù)數(shù)據(jù)
np.random.seed(42)
dates = pd.date_range('2024-01-01', '2024-12-31', freq='D')
categories = ['工資', '餐飲', '交通', '購物', '娛樂', '醫(yī)療', '教育', '其他']
accounts = ['現(xiàn)金', '銀行卡', '信用卡', '支付寶', '微信']

financial_data = []
for date in dates[:500]:  # 生成500條記錄
    amount = np.random.randint(-500, 2000)
    category = np.random.choice(categories)
    account = np.random.choice(accounts)
    description = f'{category}支出' if amount < 0 else f'{category}收入'
    
    financial_data.append({
        '日期': date,
        '描述': description,
        '金額': amount,
        '類別': category,
        '賬戶': account
    })

financial_df = pd.DataFrame(financial_data)

# 生成財務(wù)報告
generate_financial_report(financial_df)
print("財務(wù)報表生成完成！")

def clean_and_validate_excel(input_file, output_file='cleaned_data.xlsx'):
    """清洗和驗證Excel數(shù)據(jù)"""
    
    # 讀取數(shù)據(jù)
    df = pd.read_excel(input_file)
    
    # 數(shù)據(jù)清洗
    print("開始數(shù)據(jù)清洗...")
    
    # 1. 刪除空行
    initial_rows = len(df)
    df = df.dropna(how='all')
    print(f"刪除空行: {initial_rows - len(df)} 行")
    
    # 2. 處理重復(fù)數(shù)據(jù)
    duplicates = df.duplicated().sum()
    df = df.drop_duplicates()
    print(f"刪除重復(fù)數(shù)據(jù): {duplicates} 行")
    
    # 3. 處理缺失值
    missing_before = df.isnull().sum().sum()
    
    # 數(shù)值列用中位數(shù)填充
    numeric_columns = df.select_dtypes(include=[np.number]).columns
    for col in numeric_columns:
        df[col].fillna(df[col].median(), inplace=True)
    
    # 字符串列用眾數(shù)填充
    string_columns = df.select_dtypes(include=['object']).columns
    for col in string_columns:
        if not df[col].mode().empty:
            df[col].fillna(df[col].mode()[0], inplace=True)
    
    missing_after = df.isnull().sum().sum()
    print(f"處理缺失值: {missing_before - missing_after} 個")
    
    # 4. 數(shù)據(jù)類型轉(zhuǎn)換
    # 嘗試將合適的列轉(zhuǎn)換為數(shù)值類型
    for col in df.columns:
        if df[col].dtype == 'object':
            try:
                # 嘗試轉(zhuǎn)換為數(shù)值
                df[col] = pd.to_numeric(df[col], errors='ignore')
            except:
                pass
            
            # 嘗試轉(zhuǎn)換為日期
            if '日期' in col or '時間' in col or 'date' in col.lower():
                try:
                    df[col] = pd.to_datetime(df[col], errors='ignore')
                except:
                    pass
    
    # 5. 數(shù)據(jù)驗證
    validation_results = {}
    
    # 數(shù)值范圍驗證
    for col in numeric_columns:
        if col in df.columns:
            min_val = df[col].min()
            max_val = df[col].max()
            validation_results[col] = {
                '最小值': min_val,
                '最大值': max_val,
                '異常值數(shù)量': ((df[col] < 0) | (df[col] > 1000000)).sum()
            }
    
    # 字符串長度驗證
    for col in string_columns:
        if col in df.columns:
            max_length = df[col].astype(str).str.len().max()
            validation_results[col] = {
                '最大長度': max_length,
                '空字符串?dāng)?shù)量': (df[col] == '').sum()
            }
    
    print("數(shù)據(jù)驗證完成")
    
    # 生成清洗報告
    with pd.ExcelWriter(output_file, engine='xlsxwriter') as writer:
        # 清洗后的數(shù)據(jù)
        df.to_excel(writer, sheet_name='清洗后數(shù)據(jù)', index=False)
        
        # 數(shù)據(jù)質(zhì)量報告
        quality_report = pd.DataFrame({
            '指標(biāo)': ['總行數(shù)', '總列數(shù)', '重復(fù)行數(shù)', '缺失值總數(shù)', '數(shù)值列數(shù)', '字符串列數(shù)'],
            '數(shù)值': [len(df), len(df.columns), duplicates, missing_after, 
                    len(numeric_columns), len(string_columns)]
        })
        quality_report.to_excel(writer, sheet_name='數(shù)據(jù)質(zhì)量報告', index=False)
        
        # 驗證結(jié)果
        if validation_results:
            validation_df = pd.DataFrame.from_dict(validation_results, orient='index')
            validation_df.to_excel(writer, sheet_name='驗證結(jié)果')
        
        # 格式化
        workbook = writer.book
        
        # 設(shè)置數(shù)據(jù)格式
        data_worksheet = writer.sheets['清洗后數(shù)據(jù)']
        data_worksheet.set_column('A:Z', 15)
        
        # 設(shè)置報告格式
        report_worksheet = writer.sheets['數(shù)據(jù)質(zhì)量報告']
        report_worksheet.set_column('A:B', 20)
        
        # 添加條件格式
        if len(df) > 0:
            # 為數(shù)值列添加顏色刻度
            for col_num, col_name in enumerate(df.columns):
                if col_name in numeric_columns:
                    data_worksheet.conditional_format(1, col_num, len(df), col_num, {
                        'type': '3_color_scale',
                        'min_color': '#F8696B',
                        'mid_color': '#FFEB9C',
                        'max_color': '#63BE7B'
                    })
    
    print(f"清洗完成！輸出文件: {output_file}")
    return df

# 生成示例臟數(shù)據(jù)用于測試
def create_dirty_data():
    """創(chuàng)建包含各種數(shù)據(jù)質(zhì)量問題的示例數(shù)據(jù)"""
    np.random.seed(42)
    
    data = []
    for i in range(100):
        # 故意制造一些數(shù)據(jù)質(zhì)量問題
        
        # 空值
        if i % 10 == 0:
            name = None
        else:
            name = f'產(chǎn)品{i}'
        
        # 重復(fù)數(shù)據(jù)
        if i % 15 == 0:
            name = '產(chǎn)品1'  # 重復(fù)名稱
        
        # 異常數(shù)值
        price = np.random.randint(10, 1000)
        if i % 20 == 0:
            price = -999  # 異常負(fù)值
        elif i % 25 == 0:
            price = 999999  # 異常大值
        
        # 格式不一致的日期
        if i % 12 == 0:
            date = '2024-13-45'  # 無效日期
        else:
            date = pd.Timestamp('2024-01-01') + pd.Timedelta(days=i)
        
        # 空字符串
        category = '' if i % 8 == 0 else f'類別{i % 5}'
        
        data.append({
            'ID': i if i % 5 != 0 else None,  # 空ID
            '名稱': name,
            '價格': price,
            '日期': date,
            '類別': category,
            '數(shù)量': np.random.randint(1, 100) if i % 7 != 0 else None
        })
    
    # 添加完全重復(fù)的行
    data.extend(data[:5])
    
    return pd.DataFrame(data)

# 創(chuàng)建臟數(shù)據(jù)
dirty_df = create_dirty_data()
dirty_df.to_excel('dirty_data.xlsx', index=False)
print("臟數(shù)據(jù)文件創(chuàng)建完成: dirty_data.xlsx")

# 清洗數(shù)據(jù)
cleaned_df = clean_and_validate_excel('dirty_data.xlsx')
print("數(shù)據(jù)清洗完成！")