Excel处理——pandas读写Excel

一、pandas Excel处理概述

pandas是Python数据分析的核心库，提供了强大的Excel读写功能，支持.xlsx和.xls格式。

import pandas as pd# 读取Exceldf = pd.read_excel('data.xlsx')print(df.head())# 写入Exceldf.to_excel('output.xlsx', index=False)

二、读取Excel文件

2.1 基本读取

import pandas as pd# 读取所有数据df = pd.read_excel('data.xlsx')print(df)# 读取指定工作表df = pd.read_excel('data.xlsx', sheet_name='Sheet1')df = pd.read_excel('data.xlsx', sheet_name=0)  # 索引# 读取多个工作表sheets = pd.read_excel('data.xlsx', sheet_name=['Sheet1', 'Sheet2'])sheets = pd.read_excel('data.xlsx', sheet_name=None)  # 所有工作表# 指定列作为索引df = pd.read_excel('data.xlsx', index_col=0)# 指定读取列df = pd.read_excel('data.xlsx', usecols=['姓名', '年龄'])df = pd.read_excel('data.xlsx', usecols='A:C')  # 列范围df = pd.read_excel('data.xlsx', usecols=[0, 1, 2])  # 列索引# 指定行df = pd.read_excel('data.xlsx', skiprows=2)  # 跳过前2行df = pd.read_excel('data.xlsx', nrows=10)  # 读取前10行

2.2 数据类型指定

import pandas as pd# 指定数据类型dtype = {'姓名': str,'年龄': int,'工资': float}df = pd.read_excel('data.xlsx', dtype=dtype)# 解析日期列df = pd.read_excel('data.xlsx', parse_dates=['日期'])df = pd.read_excel('data.xlsx', parse_dates=[1, 2])  # 列索引# 自定义日期解析df = pd.read_excel('data.xlsx',                    parse_dates=['日期'],                   date_parser=lambda x: pd.to_datetime(x, format='%Y-%m-%d'))# 处理缺失值df = pd.read_excel('data.xlsx', na_values=['NA', 'NULL', ''])# 处理千位分隔符df = pd.read_excel('data.xlsx', thousands=',')

2.3 处理大型Excel

import pandas as pd# 分块读取chunk_size = 1000chunks = pd.read_excel('large_data.xlsx', chunksize=chunk_size)for chunk in chunks:    process(chunk)  # 处理每个块# 只读取需要的列（节省内存）df = pd.read_excel('large_data.xlsx', usecols=['col1', 'col2', 'col3'])# 优化数据类型dtype = {'col1': 'int32','col2': 'float32','col3': 'category'}df = pd.read_excel('large_data.xlsx', dtype=dtype)

三、写入Excel文件

3.1 基本写入

import pandas as pd# 创建DataFramedata = {'姓名': ['张三', '李四', '王五'],'年龄': [25, 30, 28],'城市': ['北京', '上海', '广州']}df = pd.DataFrame(data)# 写入单个工作表df.to_excel('output.xlsx', index=False)# 指定工作表名df.to_excel('output.xlsx', sheet_name='人员信息', index=False)# 不包含索引df.to_excel('output.xlsx', index=False)# 指定列df.to_excel('output.xlsx', columns=['姓名', '城市'], index=False)

3.2 写入多个工作表

import pandas as pd# 创建多个DataFramedf1 = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})df2 = pd.DataFrame({'C': [7, 8, 9], 'D': [10, 11, 12]})df3 = pd.DataFrame({'E': [13, 14, 15], 'F': [16, 17, 18]})# 方法1：使用ExcelWriterwith pd.ExcelWriter('multi_sheet.xlsx') as writer:    df1.to_excel(writer, sheet_name='Sheet1', index=False)    df2.to_excel(writer, sheet_name='Sheet2', index=False)    df3.to_excel(writer, sheet_name='Sheet3', index=False)# 方法2：使用字典sheets = {'Sheet1': df1,'Sheet2': df2,'Sheet3': df3}with pd.ExcelWriter('multi_sheet.xlsx') as writer:for sheet_name, df in sheets.items():        df.to_excel(writer, sheet_name=sheet_name, index=False)

3.3 格式化写入

import pandas as pdfrom openpyxl.styles import Font, PatternFill, Alignment# 创建数据df = pd.DataFrame({'姓名': ['张三', '李四', '王五'],'年龄': [25, 30, 28],'工资': [8000, 12000, 9500]})# 写入并格式化with pd.ExcelWriter('formatted.xlsx', engine='openpyxl') as writer:    df.to_excel(writer, sheet_name='Sheet1', index=False)# 获取工作表    workbook = writer.book    worksheet = writer.sheets['Sheet1']# 设置列宽    worksheet.column_dimensions['A'].width = 15    worksheet.column_dimensions['B'].width = 12    worksheet.column_dimensions['C'].width = 15# 设置表头样式    header_font = Font(bold=True, color='FFFFFF')    header_fill = PatternFill(start_color='4472C4', fill_type='solid')    header_align = Alignment(horizontal='center', vertical='center')for cell in worksheet[1]:        cell.font = header_font        cell.fill = header_fill        cell.alignment = header_align

四、pandas vs openpyxl对比

4.1 读取性能对比

import pandas as pdimport openpyxlimport timedefpandas_read(filename):"""pandas读取"""    start = time.time()    df = pd.read_excel(filename)print(f"pandas读取: {time.time() - start:.2f}秒")return dfdefopenpyxl_read(filename):"""openpyxl读取"""    start = time.time()    wb = openpyxl.load_workbook(filename)    ws = wb.active    data = []for row in ws.iter_rows(values_only=True):        data.append(list(row))print(f"openpyxl读取: {time.time() - start:.2f}秒")return data# 测试# pandas_read('large_data.xlsx')# openpyxl_read('large_data.xlsx')

4.2 使用场景选择

# 数据分析场景：使用pandasdf = pd.read_excel('data.xlsx')result = df.groupby('部门')['工资'].mean()result.to_excel('result.xlsx')# 复杂格式场景：使用openpyxlfrom openpyxl import load_workbookfrom openpyxl.styles import Font, PatternFillwb = load_workbook('template.xlsx')ws = wb.activews['A1'].font = Font(bold=True, size=14)wb.save('formatted.xlsx')# 混合使用df = pd.read_excel('data.xlsx')with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:    df.to_excel(writer, index=False)# 使用openpyxl添加样式    workbook = writer.book    worksheet = writer.sheets['Sheet1']# ... 样式设置 ...

五、实战案例

5.1 数据清洗与导出

import pandas as pdimport numpy as npclassExcelDataCleaner:"""Excel数据清洗"""def__init__(self, input_file):self.df = pd.read_excel(input_file)defclean_data(self):"""清洗数据"""# 删除空行self.df = self.df.dropna(how='all')# 删除空列self.df = self.df.dropna(axis=1, how='all')# 处理重复值self.df = self.df.drop_duplicates()# 去除首尾空格        str_cols = self.df.select_dtypes(include=['object']).columnsfor col in str_cols:self.df[col] = self.df[col].str.strip()# 处理缺失值        numeric_cols = self.df.select_dtypes(include=[np.number]).columnsfor col in numeric_cols:self.df[col] = self.df[col].fillna(self.df[col].mean())# 转换数据类型for col inself.df.columns:ifself.df[col].dtype == 'object':try:self.df[col] = pd.to_numeric(self.df[col])except:passreturnself.dfdefanalyze_data(self):"""数据分析"""        stats = {}# 数值列统计        numeric_cols = self.df.select_dtypes(include=[np.number]).columnsiflen(numeric_cols) > 0:            stats['数值统计'] = self.df[numeric_cols].describe()# 文本列统计        text_cols = self.df.select_dtypes(include=['object']).columnsiflen(text_cols) > 0:            stats['文本统计'] = {}for col in text_cols:                stats['文本统计'][col] = {'唯一值数': self.df[col].nunique(),'最常见值': self.df[col].mode().iloc[0] ifnotself.df[col].mode().empty elseNone,'缺失值数': self.df[col].isnull().sum()                }return statsdefexport_to_excel(self, output_file):"""导出到Excel"""with pd.ExcelWriter(output_file, engine='openpyxl') as writer:# 清洗后的数据self.df.to_excel(writer, sheet_name='清洗数据', index=False)# 统计信息            stats = self.analyze_data()if'数值统计'in stats:                stats['数值统计'].to_excel(writer, sheet_name='数值统计')# 文本统计if'文本统计'in stats:                text_stats_df = pd.DataFrame(stats['文本统计']).T                text_stats_df.to_excel(writer, sheet_name='文本统计')print(f"数据已导出到 {output_file}")# 使用# cleaner = ExcelDataCleaner('raw_data.xlsx')# cleaner.clean_data()# cleaner.export_to_excel('cleaned_data.xlsx')

5.2 Excel报表生成器

import pandas as pdfrom datetime import datetimeclassExcelReportGenerator:"""Excel报表生成器"""def__init__(self):self.data = {}defadd_dataframe(self, name, df):"""添加DataFrame"""self.data[name] = dfdefgenerate_report(self, filename):"""生成报表"""with pd.ExcelWriter(filename, engine='openpyxl') as writer:# 写入每个DataFramefor name, df inself.data.items():                df.to_excel(writer, sheet_name=name, index=False)# 创建汇总表            summary_data = {'报表名称': list(self.data.keys()),'数据行数': [len(df) for df inself.data.values()],'数据列数': [len(df.columns) for df inself.data.values()]            }            summary_df = pd.DataFrame(summary_data)            summary_df.to_excel(writer, sheet_name='汇总', index=False)# 格式化self._format_excel(writer)def_format_excel(self, writer):"""格式化Excel"""from openpyxl.styles import Font, PatternFill, Alignmentfor sheet_name in writer.sheets:            worksheet = writer.sheets[sheet_name]# 设置列宽for column in worksheet.columns:                max_length = 0                column_letter = column[0].column_letterfor cell in column:try:iflen(str(cell.value)) > max_length:                            max_length = len(str(cell.value))except:pass                adjusted_width = min(max_length + 2, 50)                worksheet.column_dimensions[column_letter].width = adjusted_width# 设置表头样式if sheet_name != '汇总':                header_font = Font(bold=True, color='FFFFFF')                header_fill = PatternFill(start_color='4472C4', fill_type='solid')                header_align = Alignment(horizontal='center', vertical='center')for cell in worksheet[1]:                    cell.font = header_font                    cell.fill = header_fill                    cell.alignment = header_align# 使用report = ExcelReportGenerator()# 添加数据df1 = pd.DataFrame({'产品': ['A', 'B', 'C'],'销量': [100, 150, 200]})df2 = pd.DataFrame({'地区': ['北京', '上海', '广州'],'销售额': [1000, 1200, 900]})report.add_dataframe('产品数据', df1)report.add_dataframe('地区数据', df2)report.generate_report('report.xlsx')

5.3 批量处理Excel

import pandas as pdimport globimport osclassExcelBatchProcessor:"""批量Excel处理器"""def__init__(self, input_pattern):self.files = glob.glob(input_pattern)self.results = []defprocess_file(self, filepath):"""处理单个文件"""        df = pd.read_excel(filepath)# 添加文件名列        df['文件名'] = os.path.basename(filepath)# 处理数据        result = {'文件名': os.path.basename(filepath),'行数': len(df),'列数': len(df.columns),'列名': ', '.join(df.columns),'空值数': df.isnull().sum().sum()        }return result, dfdefprocess_all(self):"""处理所有文件"""        all_data = []for filepath inself.files:print(f"处理: {filepath}")            result, df = self.process_file(filepath)self.results.append(result)            all_data.append(df)# 合并所有数据if all_data:self.merged_df = pd.concat(all_data, ignore_index=True)return pd.DataFrame(self.results)defsave_results(self, output_file):"""保存结果"""        summary = self.process_all()with pd.ExcelWriter(output_file, engine='openpyxl') as writer:            summary.to_excel(writer, sheet_name='汇总', index=False)self.merged_df.to_excel(writer, sheet_name='所有数据', index=False)print(f"结果已保存到 {output_file}")# 使用# processor = ExcelBatchProcessor('data/*.xlsx')# processor.save_results('batch_result.xlsx')

六、性能优化

6.1 读取优化

import pandas as pd# 指定引擎df = pd.read_excel('data.xlsx', engine='openpyxl')  # .xlsxdf = pd.read_excel('data.xls', engine='xlrd')       # .xls# 指定数据类型（减少内存）dtype = {'col1': 'int32','col2': 'float32','col3': 'category'}df = pd.read_excel('data.xlsx', dtype=dtype)# 只读取需要的列df = pd.read_excel('data.xlsx', usecols=['col1', 'col2'])# 限制行数df = pd.read_excel('data.xlsx', nrows=1000)

6.2 写入优化

import pandas as pd# 使用xlsxwriter引擎（更快）df.to_excel('output.xlsx', engine='xlsxwriter', index=False)# 分批写入大文件chunk_size = 10000with pd.ExcelWriter('output.xlsx') as writer:for i, chunk inenumerate(pd.read_excel('large.xlsx', chunksize=chunk_size)):        chunk.to_excel(writer, sheet_name=f'Sheet{i+1}', index=False)

七、总结

# 快速参考# 1. 读取Exceldf = pd.read_excel('file.xlsx')df = pd.read_excel('file.xlsx', sheet_name='Sheet1')df = pd.read_excel('file.xlsx', usecols=['A', 'C'])df = pd.read_excel('file.xlsx', skiprows=2, nrows=10)# 2. 写入Exceldf.to_excel('output.xlsx', index=False)df.to_excel('output.xlsx', sheet_name='数据', index=False)# 3. 多工作表with pd.ExcelWriter('output.xlsx') as writer:    df1.to_excel(writer, sheet_name='Sheet1', index=False)    df2.to_excel(writer, sheet_name='Sheet2', index=False)# 4. 数据类型dtype = {'col1': str, 'col2': int}df = pd.read_excel('file.xlsx', dtype=dtype)# 5. 日期解析df = pd.read_excel('file.xlsx', parse_dates=['date_col'])# 6. 缺失值处理df = pd.read_excel('file.xlsx', na_values=['NA', 'NULL'])# 7. 读取所有工作表sheets = pd.read_excel('file.xlsx', sheet_name=None)

pandas提供了高效便捷的Excel读写功能，适合数据分析场景。对于需要复杂格式和样式的场景，可以结合openpyxl使用。根据实际需求选择合适的工具和优化策略，可以提升数据处理的效率和灵活性。