Excel处理——OpenPyXL读写Excel

一、OpenPyXL概述

OpenPyXL是Python中处理Excel文件最常用的库之一，支持.xlsx格式的读写操作。

# 安装pip install openpyxl# 基本使用from openpyxl import Workbook, load_workbook# 创建新工作簿wb = Workbook()ws = wb.activews['A1'] = 'Hello'ws['B1'] = 'World'wb.save('hello.xlsx')

二、读取Excel文件

2.1 基本读取

from openpyxl import load_workbook# 加载工作簿wb = load_workbook('data.xlsx')# 获取所有工作表名print(wb.sheetnames)# 选择工作表ws = wb['Sheet1']# 或 ws = wb.active# 读取单元格cell = ws['A1']print(f"A1: {cell.value}")# 使用行列索引cell = ws.cell(row=1, column=2)print(f"B1: {cell.value}")# 读取多个单元格# 读取A1到C3区域for row in ws['A1:C3']:for cell in row:print(cell.value, end=' ')print()

2.2 读取整行整列

from openpyxl import load_workbookwb = load_workbook('data.xlsx')ws = wb.active# 读取第一行row1 = ws[1]for cell in row1:print(cell.value, end=' ')print()# 读取第一列col_a = ws['A']for cell in col_a:print(cell.value)# 读取行范围rows = ws['1:5']  # 第1到5行for row in rows:for cell in row:print(cell.value, end=' ')print()# 读取列范围cols = ws['A:C']  # A到C列for col in cols:for cell in col:print(cell.value, end=' ')print()

2.3 遍历所有数据

from openpyxl import load_workbookdefread_all_data(filepath):"""读取所有数据"""    wb = load_workbook(filepath)    ws = wb.active    data = []for row in ws.iter_rows(values_only=True):        data.append(list(row))return data# 按行迭代defread_row_by_row(filepath):    wb = load_workbook(filepath)    ws = wb.activefor row in ws.iter_rows(min_row=2, max_row=10, values_only=True):print(row)# 按列迭代defread_col_by_col(filepath):    wb = load_workbook(filepath)    ws = wb.activefor col in ws.iter_cols(min_col=1, max_col=3, values_only=True):print(col)# 使用data = read_all_data('data.xlsx')for row in data:print(row)

2.4 获取单元格属性

from openpyxl import load_workbookwb = load_workbook('data.xlsx')ws = wb.activecell = ws['A1']print(f"值: {cell.value}")print(f"数据类型: {cell.data_type}")print(f"行: {cell.row}")print(f"列: {cell.column}")print(f"坐标: {cell.coordinate}")print(f"样式: {cell.font}, {cell.fill}, {cell.border}, {cell.alignment}")# 合并单元格信息print(f"合并单元格: {cell.coordinate in ws.merged_cells}")

三、写入Excel文件

3.1 基本写入

from openpyxl import Workbook# 创建新工作簿wb = Workbook()ws = wb.active# 写入单元格ws['A1'] = '姓名'ws['B1'] = '年龄'ws['C1'] = '城市'# 写入数据data = [    ['张三', 25, '北京'],    ['李四', 30, '上海'],    ['王五', 28, '广州']]for row_idx, row_data inenumerate(data, start=2):for col_idx, value inenumerate(row_data, start=1):        ws.cell(row=row_idx, column=col_idx, value=value)wb.save('output.xlsx')

3.2 写入多行

from openpyxl import Workbookwb = Workbook()ws = wb.active# 使用append添加行ws.append(['姓名', '年龄', '城市'])ws.append(['张三', 25, '北京'])ws.append(['李四', 30, '上海'])ws.append(['王五', 28, '广州'])# 批量添加数据data = [    ['赵六', 35, '深圳'],    ['孙七', 32, '武汉']]for row in data:    ws.append(row)wb.save('output.xlsx')

3.3 写入多种数据类型

from openpyxl import Workbookfrom datetime import datetimewb = Workbook()ws = wb.active# 写入不同类型数据ws['A1'] = '文本'# 字符串ws['B1'] = 123.45# 数字ws['C1'] = 100# 整数ws['D1'] = True# 布尔值ws['E1'] = datetime.now()          # 日期时间ws['F1'] = '=SUM(B1:C1)'# 公式# 写入列表row_data = ['数据1', 100, 200, 300, '=SUM(B1:D1)']ws.append(row_data)wb.save('types.xlsx')

四、样式设置

4.1 字体和颜色

from openpyxl import Workbookfrom openpyxl.styles import Font, PatternFill, Border, Side, Alignmentwb = Workbook()ws = wb.active# 字体样式cell = ws['A1']cell.value = '标题'cell.font = Font(    name='微软雅黑',    size=14,    bold=True,    italic=False,    color='FF0000')# 背景颜色cell.fill = PatternFill(    start_color='FFFF00',    end_color='FFFF00',    fill_type='solid')# 边框border = Border(    left=Side(style='thin', color='000000'),    right=Side(style='thin', color='000000'),    top=Side(style='thin', color='000000'),    bottom=Side(style='thin', color='000000'))cell.border = border# 对齐cell.alignment = Alignment(    horizontal='center',    vertical='center',    wrap_text=True)wb.save('styled.xlsx')

4.2 批量样式设置

from openpyxl import Workbookfrom openpyxl.styles import Font, PatternFill, Alignmentdefapply_header_style(worksheet):"""应用表头样式"""    header_font = Font(bold=True, color='FFFFFF')    header_fill = PatternFill(start_color='4472C4', end_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_aligndefapply_data_style(worksheet):"""应用数据样式"""    align = Alignment(horizontal='center', vertical='center')for row in worksheet.iter_rows(min_row=2):for cell in row:            cell.alignment = alignwb = Workbook()ws = wb.active# 添加数据ws.append(['姓名', '年龄', '城市'])ws.append(['张三', 25, '北京'])ws.append(['李四', 30, '上海'])# 应用样式apply_header_style(ws)apply_data_style(ws)wb.save('styled_data.xlsx')

五、高级操作

5.1 合并单元格

from openpyxl import Workbookfrom openpyxl.styles import Alignmentwb = Workbook()ws = wb.active# 合并单元格ws.merge_cells('A1:D1')cell = ws['A1']cell.value = '标题行'cell.alignment = Alignment(horizontal='center', vertical='center')# 合并多个区域ws.merge_cells('A2:A4')ws['A2'] = '分组1'ws.merge_cells('B2:D2')ws['B2'] = '数据行'# 取消合并# ws.unmerge_cells('A1:D1')wb.save('merged.xlsx')

5.2 列宽行高调整

from openpyxl import Workbookwb = Workbook()ws = wb.active# 设置列宽ws.column_dimensions['A'].width = 20ws.column_dimensions['B'].width = 15ws.column_dimensions['C'].width = 25# 设置行高ws.row_dimensions[1].height = 30ws.row_dimensions[2].height = 25# 添加数据ws.append(['姓名', '年龄', '城市'])ws.append(['张三', 25, '北京'])wb.save('dimensions.xlsx')

5.3 冻结窗格

from openpyxl import Workbookwb = Workbook()ws = wb.active# 冻结首行ws.freeze_panes = 'A2'# 冻结首列ws.freeze_panes = 'B1'# 冻结首行首列ws.freeze_panes = 'B2'# 添加数据for i inrange(1, 11):    ws.append([f'列{i}', i*10, i*20, i*30])wb.save('frozen.xlsx')

5.4 添加图表

from openpyxl import Workbookfrom openpyxl.chart import BarChart, Reference, LineChartwb = Workbook()ws = wb.active# 添加数据data = [    ['月份', '销售额', '利润'],    ['1月', 100, 20],    ['2月', 120, 25],    ['3月', 140, 30],    ['4月', 160, 35]]for row in data:    ws.append(row)# 创建柱状图chart = BarChart()chart.title = '销售数据'chart.x_axis.title = '月份'chart.y_axis.title = '金额'# 数据范围data_ref = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=5)categories = Reference(ws, min_col=1, min_row=2, max_row=5)chart.add_data(data_ref, titles_from_data=True)chart.set_categories(categories)# 添加图表到工作表ws.add_chart(chart, 'E2')# 创建折线图line_chart = LineChart()line_chart.title = '趋势图'line_chart.add_data(data_ref, titles_from_data=True)line_chart.set_categories(categories)ws.add_chart(line_chart, 'E20')wb.save('chart.xlsx')

六、实战案例

6.1 Excel数据导入导出

import openpyxlfrom openpyxl import load_workbook, WorkbookclassExcelHandler:"""Excel处理器"""def__init__(self, filepath=None):self.filepath = filepathif filepath:self.wb = load_workbook(filepath)self.ws = self.wb.activeelse:self.wb = Workbook()self.ws = self.wb.activedefread_all(self):"""读取所有数据"""        data = []for row inself.ws.iter_rows(values_only=True):            data.append(list(row))return datadefread_headers(self):"""读取表头"""return [cell.value for cell inself.ws[1]]defwrite_data(self, data, headers=None):"""写入数据"""if headers:self.ws.append(headers)for row in data:self.ws.append(row)defappend_row(self, row):"""追加行"""self.ws.append(row)defset_cell(self, row, col, value):"""设置单元格"""self.ws.cell(row=row, column=col, value=value)defget_cell(self, row, col):"""获取单元格"""returnself.ws.cell(row=row, column=col).valuedefsave(self, filepath=None):"""保存文件"""if filepath:self.filepath = filepathself.wb.save(self.filepath)defset_column_width(self, column, width):"""设置列宽"""self.ws.column_dimensions[column].width = widthdefset_row_height(self, row, height):"""设置行高"""self.ws.row_dimensions[row].height = heightdefadd_style_header(self):"""添加表头样式"""from openpyxl.styles import Font, PatternFill, Alignment        header_font = Font(bold=True, color='FFFFFF')        header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')        header_align = Alignment(horizontal='center', vertical='center')for cell inself.ws[1]:            cell.font = header_font            cell.fill = header_fill            cell.alignment = header_align# 使用# handler = ExcelHandler()# handler.write_data(#     [['张三', 25, '北京'], ['李四', 30, '上海']],#     headers=['姓名', '年龄', '城市']# )# handler.set_column_width('A', 20)# handler.set_column_width('B', 15)# handler.add_style_header()# handler.save('output.xlsx')

6.2 数据清洗与格式化

import openpyxlfrom openpyxl import load_workbookclassDataCleaner:"""数据清洗"""def__init__(self, filepath):self.wb = load_workbook(filepath)self.ws = self.wb.activedefclean_numeric(self):"""清理数字列"""for row inself.ws.iter_rows(min_row=2):for cell in row:ifisinstance(cell.value, str):# 去除空格                    cell.value = cell.value.strip()# 尝试转换为数字try:if cell.value.isdigit():                            cell.value = int(cell.value)elif cell.value.replace('.', '').isdigit():                            cell.value = float(cell.value)except:passdefformat_headers(self):"""格式化表头"""from openpyxl.styles import Font, PatternFill, Alignmentfor cell inself.ws[1]:            cell.font = Font(bold=True, color='FFFFFF')            cell.fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')            cell.alignment = Alignment(horizontal='center', vertical='center')defremove_empty_rows(self):"""删除空行"""        rows_to_delete = []for idx, row inenumerate(self.ws.iter_rows(min_row=2), start=2):ifall(cell.value isNonefor cell in row):                rows_to_delete.append(idx)for idx inreversed(rows_to_delete):self.ws.delete_rows(idx)defconvert_dates(self):"""转换日期格式"""from datetime import datetimeimport refor row inself.ws.iter_rows(min_row=2):for cell in row:ifisinstance(cell.value, str):# 匹配日期格式                    date_patterns = [r'\d{4}-\d{2}-\d{2}',r'\d{2}/\d{2}/\d{4}',r'\d{2}-\d{2}-\d{4}'                    ]for pattern in date_patterns:if re.match(pattern, cell.value):try:                                cell.value = datetime.strptime(cell.value, '%Y-%m-%d')except:passdefsave(self, filepath):"""保存文件"""self.wb.save(filepath)# 使用# cleaner = DataCleaner('raw_data.xlsx')# cleaner.clean_numeric()# cleaner.format_headers()# cleaner.remove_empty_rows()# cleaner.save('cleaned_data.xlsx')

6.3 Excel报表生成

import openpyxlfrom openpyxl import Workbookfrom openpyxl.styles import Font, PatternFill, Alignment, Border, Sidefrom openpyxl.chart import BarChart, Referencefrom datetime import datetimeclassReportGenerator:"""报表生成器"""def__init__(self):self.wb = Workbook()self.ws = self.wb.activeself.ws.title = '报表'defadd_title(self, title, row=1):"""添加标题"""self.ws.merge_cells(f'A{row}:H{row}')        cell = self.ws[f'A{row}']        cell.value = title        cell.font = Font(size=16, bold=True)        cell.alignment = Alignment(horizontal='center', vertical='center')self.ws.row_dimensions[row].height = 30return row + 1defadd_subtitle(self, subtitle, row):"""添加副标题"""self.ws.merge_cells(f'A{row}:H{row}')        cell = self.ws[f'A{row}']        cell.value = f'生成时间: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")} - {subtitle}'        cell.font = Font(size=10, italic=True)        cell.alignment = Alignment(horizontal='center', vertical='center')return row + 1defadd_table(self, data, start_row, headers=None):"""添加表格"""if headers:self.ws.append(headers)            current_row = start_row + 1else:            current_row = start_row# 添加数据for row in data:self.ws.append(row)            current_row += 1# 样式化表头if headers:for cell inself.ws[start_row]:                cell.font = Font(bold=True, color='FFFFFF')                cell.fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')                cell.alignment = Alignment(horizontal='center', vertical='center')# 设置列宽for col inrange(1, len(headers) + 1if headers elselen(data[0]) + 1):            column_letter = openpyxl.utils.get_column_letter(col)self.ws.column_dimensions[column_letter].width = 15return current_rowdefadd_summary(self, start_row, summary_data):"""添加汇总信息"""        row = start_rowfor key, value in summary_data.items():self.ws[f'A{row}'] = keyself.ws[f'B{row}'] = valueself.ws[f'A{row}'].font = Font(bold=True)            row += 1return rowdefadd_chart(self, data_range, categories_range, start_cell, title='图表'):"""添加图表"""        chart = BarChart()        chart.title = title        chart.x_axis.title = '类别'        chart.y_axis.title = '数值'        data = Reference(self.ws, min_col=data_range['min_col'],                         min_row=data_range['min_row'],                         max_col=data_range['max_col'],                         max_row=data_range['max_row'])        categories = Reference(self.ws, min_col=categories_range['min_col'],                              min_row=categories_range['min_row'],                              max_row=categories_range['max_row'])        chart.add_data(data, titles_from_data=True)        chart.set_categories(categories)self.ws.add_chart(chart, start_cell)defsave(self, filepath):"""保存文件"""self.wb.save(filepath)# 使用generator = ReportGenerator()generator.add_title('销售报表')generator.add_subtitle('2024年度', 3)data = [    ['1月', 100, 120, 220],    ['2月', 120, 140, 260],    ['3月', 130, 160, 290]]generator.add_table(data, start_row=5, headers=['月份', '销售额', '利润', '合计'])summary = {'总计': 770,'平均': 256.67,'最高': 290}generator.add_summary(10, summary)generator.save('report.xlsx')

七、总结

# 快速参考# 1. 读取Excelfrom openpyxl import load_workbookwb = load_workbook('file.xlsx')ws = wb.active# 2. 读取单元格value = ws['A1'].valuevalue = ws.cell(row=1, column=1).value# 3. 写入单元格ws['A1'] = 'value'ws.cell(row=1, column=1, value='value')# 4. 保存wb.save('output.xlsx')# 5. 创建新文件from openpyxl import Workbookwb = Workbook()ws = wb.active# 6. 样式from openpyxl.styles import Font, PatternFill, Alignmentcell.font = Font(bold=True)cell.fill = PatternFill(start_color='FFFF00', fill_type='solid')cell.alignment = Alignment(horizontal='center')# 7. 合并单元格ws.merge_cells('A1:D1')# 8. 列宽ws.column_dimensions['A'].width = 20# 9. 添加行ws.append(['data1', 'data2', 'data3'])

OpenPyXL是处理Excel文件的强大工具，支持完整的读写操作和样式设置。掌握基本的读写、样式、图表和高级操作可以满足大部分Excel数据处理需求。对于大文件处理，建议使用read_only和write_only模式以提高性能。