从基础到精通详解Pandas操作Excel使用手册大全

目录

• 前言
• 1. 基础环境配置
• 必要库安装
• 导入库
• 2. Excel文件读取
• 2.1 基础读取
• 2.2 高级读取选项
• 2.3 不同引擎对比
• 3. Excel文件写入
• 3.1 基础写入
• 3.2 使用ExcelWriter
• 4. 多工作表操作
• 4.1 读取多工作表
• 4.2 写入多工作表
• 5. 数据格式化与样式
• 5.1 使用Styler进行样式设置
• 5.2 使用xlsxwriter进行高级格式化
• 5.3 使用openpyxl进行单元格样式设置
• 6. 高级功能
• 6.1 添加图表
• 6.2 数据透 视表
• 6.3 公式和计算
• 7. 性能优化
• 7.1 大数据量处理
• 7.2 内存优化
• 7.3 并行处理
• 8. 常见问题解决
• 8.1 编码问题
• 8.2 数据类型问题
• 8.3 内存不足问题
• 8.4 文件锁定问题
• 9. 实战案例
• 9.1 销售数据报表生成
• 9.2 财务报表自动化
• 9.3 数据清洗和验证
• 总结

在数据分析和处理中，Excel文件是最常见的数据格式之一。Pandas作为Python最强大的数据处理库，提供了丰富的Excel操作功能。本手册将全面介绍Pandas操作Excel的各种技巧，从基础读写到高级格式化，助你成为Excel数据处理专家。

# 基础库
pip install pandas

# Excel处理引擎
pip install openpyxl        # 支持.xlsx文件读写
pip install xlsxwriter      # 支持高级格式化功能
pip install xlrd           # 支持旧版.xls文件读取（可选）

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

# 可选：样式相关
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'])

# 指定数据类型
df = pd.read_excel('data.xlsx', dtype={'列名': str, '数值列': float})

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

# openpyxl引擎（默认，功能全面）
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')

# 基础写入
df.to_excel('output.xlsx', index=False)

# 写入指定工作表
df.to_excel('output.xlsx', sheet_name='数据表', 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)

# 创建ExcelWriter对象
with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
    df1.to_excel(writer, sheet_name='数据1', index=False)
    df2.to_excel(writer, sheet_name='数据2', index=False)
    df3.to_excel(writer, sheet_name='数据3', index=False)

# 使用xlsxwriter引擎（支持更多格式化选项）
with pd.ExcelWriter('output.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='数据', index=False)
    
    # 获取工作簿和工作表对象
    workbook = writer.book
    worksheet = writer.sheets['数据']
    
    # 设置列宽
    worksheet.set_column('A:C', 20)
    
    # 添加格式
    header_format = workbook.add_format({
        'bold': True,
        'text_wrap': True,
        'valign': 'top',
        'fg_color': '#D7E4BD',
        'border': 1
    })
    
    # 应用表头格式
    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 = ['销售数据', '库存数据', '客户数据']
dfs = pd.read_excel('data.xlsx', sheet_name=sheets)

# 访问特定工作表
sales_df = dfs['销售数据']
inventory_df = dfs['库存数据']

# 创建多个工作表
with pd.ExcelWriter('report.xlsx', engine='openpyxl') as writer:
    # 写入不同数据到不同工作表
    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)
    
    # 写入汇总数据
    summary_df.to_excel(writer, sheet_name='汇总统计', index=False)

# 添加工作表到现有文件
with pd.ExcelWriter('existing.xlsx', mode='a', engine='openpyxl', 
                   if_sheet_exists='replace') as writer:
    new_df.to_excel(writer, sheet_name='新数据', index=False)

# 创建样式化数据框
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}'

# 应用样式
styled_df = df.style.applymap(color_negative_red, subset=['数值列'])
styled_df = styled_df.apply(highlight_max, subset=['数值列'])

# 导出带样式的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='数据', index=False)
    
    # 获取工作簿和工作表
    workbook = writer.book
    worksheet = writer.sheets['数据']
    
    # 定义格式
    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%'})
    
    # 获取数据维度
    max_row, max_col = df.shape
    
    # 应用表头格式
    for col_num, value in enumerate(df.columns.values):
        worksheet.write(0, col_num, value, header_format)
    
    # 设置列宽和格式
    worksheet.set_column('A:A', 15)  # 设置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'
    })
    
    # 添加数据条
    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

# 创建工作簿和工作表
wb = Workbook()
ws = wb.active
ws.title = "样式示例"

# 添加数据到工作表
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')

# 应用表头样式
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.border = border
    cell.alignment = center_alignment

# 设置列宽
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

# 创建带图表的Excel文件
with pd.ExcelWriter('chart_report.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='数据', index=False)
    
    workbook = writer.book
    worksheet = writer.sheets['数据']
    
    # 创建图表
    chart = workbook.add_chart({'type': 'column'})
    
    # 配置图表数据
    max_row = len(df) + 1
    chart.add_series({
        'name': ['数据', 0, 1],  # 系列名称
        'categories': ['数据', 1, 0, max_row, 0],  # X轴标签
        'values': ['数据', 1, 1, max_row, 1],  # Y轴数据
    })
    
    # 设置图表标题和轴标签
    chart.set_title({'name': '销售数据分析'})
    chart.set_x_axis({'name': '月份'})
    chart.set_y_axis({'name': '销售额'})
    
    # 插入图表
    worksheet.insert_chart('E2', chart)

# 创建数据透 视表
pivot_table = pd.pivot_table(df, 
                           values=['销售额', '数量'], 
                           index=['地区'], 
                           columns=['产品类别'], 
                           aggfunc={'销售额': np.sum, '数量': np.mean},
                           fill_value=0)

# 写入数据透 视表
with pd.ExcelWriter('pivot_report.xlsx', engine='openpyxl') as writer:
    # 写入原始数据
    df.to_excel(writer, sheet_name='原始数据', index=False)
    
    # 写入数据透 视表
    pivot_table.to_excel(writer, sheet_name='数据透 视表')

# 使用xlsxwriter添加公式
with pd.ExcelWriter('formula_report.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='数据', index=False)
    
    workbook = writer.book
    worksheet = writer.sheets['数据']
    
    # 添加公式
    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='处理结果', index=False)

# 指定数据类型减少内存使用
dtype_dict = {
    '整数列': '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

# 写入结果
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')

# 处理混合数据类型
df = pd.read_excel('mixed_types.xlsx', dtype=str)  # 全部读取为字符串

# 指定列的数据类型
dtype_dict = {'ID': str, '日期': str, '数值': 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)

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

# 确保正确关闭文件
try:
    with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='数据', index=False)
        # 其他操作...
except Exception as e:
    print(f"错误: {e}")
finally:
    # 确保文件被正确关闭
    pass

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

# 生成示例数据
np.random.seed(42)
dates = pd.date_range(start='2024-01-01', end='2024-12-31', freq='D')
regions = ['华北', '华东', '华南', '西南', '西北']
products = ['产品A', '产品B', '产品C', '产品D', '产品E']

# 创建销售数据
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,
                '地区': region,
                '产品': product,
                '销售额': sales,
                '数量': 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
        
        # 原始数据
        df.to_excel(writer, sheet_name='原始数据', index=False)
        
        # 月度汇总
        monthly_summary = df.groupby([df['日期'].dt.to_period('M'), '地区']).agg({
            '销售额': 'sum',
            '数量': 'sum'
        }).reset_index()
        monthly_summary['日期'] = monthly_summary['日期'].astype(str)
        monthly_summary.to_excel(writer, sheet_name='月度汇总', index=False)
        
        # 产品分析
        product_analysis = df.groupby('产品').agg({
            '销售额': ['sum', 'mean', 'std'],
            '数量': ['sum', 'mean', 'std']
        }).round(2)
        product_analysis.to_excel(writer, sheet_name='产品分析')
        
        # 地区分析
        region_analysis = df.groupby('地区').agg({
            '销售额': 'sum',
            '数量': 'sum'
        }).reset_index()
        region_analysis.to_excel(writer, sheet_name='地区分析', index=False)
        
        # 格式化工作表
        for sheet_name in ['月度汇总', '地区分析']:
            worksheet = writer.sheets[sheet_name]
            
            # 设置列宽
            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)
            
            # 添加数据条
            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'):
    """生成财务报表"""
    
    with pd.ExcelWriter(output_file, engine='xlsxwriter') as writer:
        workbook = writer.book
        
        # 1. 原始交易数据
        transactions_df.to_excel(writer, sheet_name='交易明细', 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. 资产负债表
        balance_sheet = transactions_df.groupby('账户').agg({
            '金额': 'sum'
        }).reset_index()
        balance_sheet.to_excel(writer, sheet_name='资产负债表', 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'
            })
        }
        
        # 应用格式到各个工作表
        for sheet_name in writer.sheets:
            worksheet = writer.sheets[sheet_name]
            
            # 设置列宽
            worksheet.set_column('A:Z', 15)
            
            # 应用货币格式到金额列
            if sheet_name == '交易明细':
                worksheet.set_column('C:C', 15, formats['货币'])
                worksheet.set_column('A:A', 12, formats['日期'])
            elif sheet_name == '资产负债表':
                worksheet.set_column('B:B', 15, formats['货币'])

# 生成示例财务数据
np.random.seed(42)
dates = pd.date_range('2024-01-01', '2024-12-31', freq='D')
categories = ['工资', '餐饮', '交通', '购物', '娱乐', '医疗', '教育', '其他']
accounts = ['现金', '银行卡', '信用卡', '支付宝', '微信']

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)

# 生成财务报告
generate_financial_report(financial_df)
print("财务报表生成完成！")

def clean_and_validate_excel(input_file, output_file='cleaned_data.xlsx'):
    """清洗和验证Excel数据"""
    
    # 读取数据
    df = pd.read_excel(input_file)
    
    # 数据清洗
    print("开始数据清洗...")
    
    # 1. 删除空行
    initial_rows = len(df)
    df = df.dropna(how='all')
    print(f"删除空行: {initial_rows - len(df)} 行")
    
    # 2. 处理重复数据
    duplicates = df.duplicated().sum()
    df = df.drop_duplicates()
    print(f"删除重复数据: {duplicates} 行")
    
    # 3. 处理缺失值
    missing_before = df.isnull().sum().sum()
    
    # 数值列用中位数填充
    numeric_columns = df.select_dtypes(include=[np.number]).columns
    for col in numeric_columns:
        df[col].fillna(df[col].median(), inplace=True)
    
    # 字符串列用众数填充
    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. 数据类型转换
    # 尝试将合适的列转换为数值类型
    for col in df.columns:
        if df[col].dtype == 'object':
            try:
                # 尝试转换为数值
                df[col] = pd.to_numeric(df[col], errors='ignore')
            except:
                pass
            
            # 尝试转换为日期
            if '日期' in col or '时间' in col or 'date' in col.lower():
                try:
                    df[col] = pd.to_datetime(df[col], errors='ignore')
                except:
                    pass
    
    # 5. 数据验证
    validation_results = {}
    
    # 数值范围验证
    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,
                '异常值数量': ((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,
                '空字符串数量': (df[col] == '').sum()
            }
    
    print("数据验证完成")
    
    # 生成清洗报告
    with pd.ExcelWriter(output_file, engine='xlsxwriter') as writer:
        # 清洗后的数据
        df.to_excel(writer, sheet_name='清洗后数据', index=False)
        
        # 数据质量报告
        quality_report = pd.DataFrame({
            '指标': ['总行数', '总列数', '重复行数', '缺失值总数', '数值列数', '字符串列数'],
            '数值': [len(df), len(df.columns), duplicates, missing_after, 
                    len(numeric_columns), len(string_columns)]
        })
        quality_report.to_excel(writer, sheet_name='数据质量报告', index=False)
        
        # 验证结果
        if validation_results:
            validation_df = pd.DataFrame.from_dict(validation_results, orient='index')
            validation_df.to_excel(writer, sheet_name='验证结果')
        
        # 格式化
        workbook = writer.book
        
        # 设置数据格式
        data_worksheet = writer.sheets['清洗后数据']
        data_worksheet.set_column('A:Z', 15)
        
        # 设置报告格式
        report_worksheet = writer.sheets['数据质量报告']
        report_worksheet.set_column('A:B', 20)
        
        # 添加条件格式
        if len(df) > 0:
            # 为数值列添加颜色刻度
            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

# 生成示例脏数据用于测试
def create_dirty_data():
    """创建包含各种数据质量问题的示例数据"""
    np.random.seed(42)
    
    data = []
    for i in range(100):
        # 故意制造一些数据质量问题
        
        # 空值
        if i % 10 == 0:
            name = None
        else:
            name = f'产品{i}'
        
        # 重复数据
        if i % 15 == 0:
            name = '产品1'  # 重复名称
        
        # 异常数值
        price = np.random.randint(10, 1000)
        if i % 20 == 0:
            price = -999  # 异常负值
        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,
            '数量': np.random.randint(1, 100) if i % 7 != 0 else None
        })
    
    # 添加完全重复的行
    data.extend(data[:5])
    
    return pd.DataFrame(data)

# 创建脏数据
dirty_df = create_dirty_data()
dirty_df.to_excel('dirty_data.xlsx', index=False)
print("脏数据文件创建完成: dirty_data.xlsx")

# 清洗数据
cleaned_df = clean_and_validate_excel('dirty_data.xlsx')
print("数据清洗完成！")

本手册涵盖了Pandas操作Excel的方方面面，从基础读写到高级格式化，从性能优化到实际应用。掌握这些技能将大大提升你的数据处理效率。

最佳实践建议：

1.选择合适的引擎：

• openpyxl：通用性强，支持读写
• xlsxwriter：格式化功能强大，只支持写入
• xlrd：适合处理旧版.xls文件

2.注意性能优化：

• 使用chunksize处理大文件
• 指定合适的数据类型
• 只读取需要的列

3.数据质量保证：

• 处理缺失值和异常值
• 验证数据类型和格式
• 添加数据清洗步骤

4.格式化技巧：

• 使用条件格式突出重点数据
• 合理设置列宽和行高
• 添加图表增强可视化效果

到此这篇关于从基础到精通详解Pandas操作Excel使用手册大全的文章就介绍到这了,更多相关Pandas操作Excel内容请搜索风君子博客以前的文章或继续浏览下面的相关文章希望大家以后多多支持风君子博客！

您可能感兴趣的文章:

• Python+pandas实现Excel连续数据分组求平均值
• python使用pandas读取excel文件中数据的四种方法
• Python自动化操作Excel的多种方式(Pandas+openpyxl+xlrd)
• Python Pandas高效处理Excel数据完整指南
• Pandas如何操作Excel
• python使用pandas实现Excel转换为CSV文件