python文件操作相关（excel）

在 Python 中，操作 Excel 文件通常使用 openpyxl、pandas 和 xlrd/xlwt 等库

• openpyxl：适合读写 .xlsx 文件，支持高级功能
• pandas：适合数据分析和处理，支持读写 .xlsx 和 .xls 文件
• xlrd/xlwt：适合读写旧版 .xls 文件
• xlsxwriter：适合创建复杂的 .xlsx 文件
• pyxlsb：适合读取 .xlsb 文件

---

1. openpyxl 库

openpyxl 是一个专门用于读写 Excel 2010 xlsx/xlsm/xltx/xltm 文件的库，它支持 Excel的高级功能，如公式、图表、样式等

主要功能

• 读取 Excel 文件：加载 Excel 文件并读取数据
• 写入 Excel 文件：创建或修改 Excel 文件并保存
• 操作工作表：添加、删除、重命名工作表
• 单元格操作：读取、写入、修改单元格内容
• 样式设置：设置单元格的字体、颜色、边框等样式。

from openpyxl import Workbook, load_workbook# 创建一个新的 Excel 文件
wb = Workbook()
ws = wb.active
ws.title = "Sheet1"# 写入数据
ws['A1'] = "Name"
ws['B1'] = "Age"
ws['A2'] = "Alice"
ws['B2'] = 25
ws['A3'] = "Bob"
ws['B3'] = 30# 保存文件
wb.save("example.xlsx")# 读取 Excel 文件
wb = load_workbook("example.xlsx")
ws = wb["Sheet1"]# 读取单元格数据
for row in ws.iter_rows(min_row=1, max_col=2, max_row=3, values_only=True):print(row)

openpyxl其他用法

创建与删除

1. 创建新的工作表:

   wb.create_sheet('NewSheet')
   

2. 删除工作表:

   del wb['SheetName']
   

操作单元格

1. 访问单元格:

   • 通过坐标访问:

     cell = ws['A1']
     

   • 通过行和列索引访问:

     cell = ws.cell(row=1, column=1)
     

2. 获取单元格的值:

   value = ws['A1'].value
   

3. 遍历行和列:

   • 遍历所有行:

     for row in ws.iter_rows():for cell in row:print(cell.value)
     

   • 遍历特定范围的单元格:

     for row in ws['A1':'C3']:for cell in row:print(cell.value)
     

追加数据

1. 在现有工作表末尾追加一行:

   from openpyxl import load_workbookwb = load_workbook('example.xlsx')
   ws = wb.active# 获取当前最后一行的行号
   max_row = ws.max_row# 追加新行
   new_row = ['Value1', 'Value2', 'Value3']
   for col_idx, value in enumerate(new_row, start=1):cell = ws.cell(row=max_row + 1, column=col_idx)cell.value = valuewb.save('example.xlsx')
   

2. 使用 openpyxl.utils 追加数据:

   from openpyxl import load_workbook
   from openpyxl.utils import get_column_letterwb = load_workbook('example.xlsx')
   ws = wb.activemax_row = ws.max_row
   new_data = [['Value1', 'Value2', 'Value3'],['Value4', 'Value5', 'Value6']
   ]for row_idx, row_data in enumerate(new_data, start=max_row + 1):for col_idx, value in enumerate(row_data, start=1):col_letter = get_column_letter(col_idx)cell = ws[f'{col_letter}{row_idx}']cell.value = valuewb.save('example.xlsx')
   

3. 使用 ws.append 追加数据:

ws.append 方法接受一个列表作为参数，列表中的每个元素会被依次写入工作表当前最后一行的每个单元格中

1. 创建一个新的工作簿并追加数据:

   from openpyxl import Workbook# 创建一个新的工作簿
   wb = Workbook()
   ws = wb.active# 追加一行数据
   ws.append(['Value1', 'Value2', 'Value3'])# 保存工作簿
   wb.save('example.xlsx')
   

2. 在现有工作簿中追加数据:

   from openpyxl import load_workbook# 加载现有的工作簿
   wb = load_workbook('example.xlsx')
   ws = wb.active# 追加多行数据
   ws.append(['Value4', 'Value5', 'Value6'])
   ws.append(['Value7', 'Value8', 'Value9'])# 保存工作簿
   wb.save('example.xlsx')
   

3. 追加包含公式的行:

   from openpyxl import load_workbook# 加载现有的工作簿
   wb = load_workbook('example.xlsx')
   ws = wb.active# 追加包含公式的行
   ws.append(['SUM(A1:A10)', 'AVERAGE(B1:B10)'])# 保存工作簿
   wb.save('example.xlsx')
   

格式化单元格

1. 设置字体样式:

   from openpyxl.styles import Fontcell = ws['A1']
   cell.font = Font(size=14, bold=True, color='FF0000')
   

2. 设置单元格填充颜色:

   from openpyxl.styles import PatternFillfill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
   cell.fill = fill
   

3. 设置边框:

   from openpyxl.styles import Border, Sidethin = Side(border_style='thin', color='000000')
   border = Border(left=thin, right=thin, top=thin, bottom=thin)
   cell.border = border
   

合并单元格

1. 合并单元格:

   ws.merge_cells('A1:B1')
   

2. 取消合并单元格:

   ws.unmerge_cells('A1:B1')
   

插入图片

1. 插入图片:

   from openpyxl.drawing.image import Imageimg = Image('example.png')
   ws.add_image(img, 'A1')
   

公式

1. 设置公式:

   cell = ws['A1']
   cell.value = '=SUM(B1:B10)'
   

打印设置

1. 设置打印区域:

   ws.print_area = 'A1:D10'
   

2. 设置页眉和页脚:

   ws.header_footer.center_header.text = "&[Date]"
   ws.header_footer.right_footer.text = "&[Page] of &[Pages]"
   

保护工作表

1. 保护工作表:

   ws.protection.password = 'password'
   ws.protection.enable()
   

2. 取消保护工作表:

   ws.protection.disable()
   

其他功能

• 条件格式化:

  from openpyxl.formatting import Rule
  from openpyxl.styles import PatternFill
  from openpyxl.styles.differential import DifferentialStylered_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
  dxf = DifferentialStyle(fill=red_fill)
  rule = Rule(type='expression', dxf=dxf, formula=['A1>10'])
  ws.conditional_formatting.add('A1:A10', rule)
  

• 数据验证:

  from openpyxl.worksheet.datavalidation import DataValidationdv = DataValidation(type='list', formula1='"Item1,Item2,Item3"', allow_blank=True)
  dv.add('A1')
  ws.add_data_validation(dv)
  

• 超链接:

  from openpyxl.cell import Cell
  from openpyxl.worksheet.hyperlink import Hyperlinkcell = ws['A1']
  cell.value = 'Click here'
  cell.hyperlink = Hyperlink(display='Click here', ref='A1', location='https://www.example.com')
  

---

2. pandas 库

pandas 是一个强大的数据分析库，支持读取和写入 Excel 文件。它通常用于处理结构化数据，如表格数据

主要功能

• 读取 Excel 文件：将 Excel 文件加载为 DataFrame
• 写入 Excel 文件：将 DataFrame 保存为 Excel 文件
• 数据处理：支持数据筛选、排序、聚合等操作
• 多工作表操作：支持读取和写入多个工作表

import pandas as pd# 读取 Excel 文件
df = pd.read_excel("example.xlsx", sheet_name="Sheet1", engine='openpyxl')
print(df)# 数据处理
df['Age'] = df['Age'] + 1  # 将年龄加 1
print(df)# 写入 Excel 文件
df.to_excel("modified_example.xlsx", index=False)

---

3. xlrd 和 xlwt 库

xlrd 用于读取 Excel 文件（仅支持旧版 .xls 格式），xlwt 用于写入 Excel 文件（仅支持旧版 .xls 格式）

主要功能

• 读取 Excel 文件：xlrd 可以读取 .xls 文件的内容
• 写入 Excel 文件：xlwt 可以创建或修改 .xls 文件

import xlrd
import xlwt# 读取 Excel 文件
workbook = xlrd.open_workbook("example.xls")
sheet = workbook.sheet_by_index(0)
for row in range(sheet.nrows):print(sheet.row_values(row))# 写入 Excel 文件
workbook = xlwt.Workbook()
sheet = workbook.add_sheet("Sheet1")
sheet.write(0, 0, "Name")
sheet.write(0, 1, "Age")
sheet.write(1, 0, "Alice")
sheet.write(1, 1, 25)
workbook.save("example.xls")

---

4. xlsxwriter 库

xlsxwriter 一个专门用于写入 Excel 文件的库，支持创建复杂的 Excel 文件，如添加图表、公式、条件格式等

主要功能

• 创建 Excel 文件：支持创建 .xlsx 文件
• 高级功能：支持图表、公式、条件格式、数据验证等
• 样式设置：支持设置单元格样式

import xlsxwriter# 创建一个新的 Excel 文件
workbook = xlsxwriter.Workbook('example.xlsx')
worksheet = workbook.add_worksheet('Sheet1')# 写入数据
worksheet.write('A1', 'Name')
worksheet.write('B1', 'Age')
worksheet.write('A2', 'Alice')
worksheet.write('B2', 25)# 添加图表
chart = workbook.add_chart({'type': 'column'})
chart.add_series({'values': '=Sheet1!B2:B2'})
worksheet.insert_chart('D2', chart)# 保存文件
workbook.close()

---

5. pyxlsb 库

pyxlsb 用于读取 Excel 二进制文件（.xlsb 格式）

主要功能

• 读取 .xlsb** 文件**：支持读取 Excel 二进制文件

from pyxlsb import open_workbook# 读取 Excel 二进制文件
with open_workbook('example.xlsb') as wb:with wb.get_sheet(1) as sheet:for row in sheet.rows():print([item.v for item in row])

---

应用场景

• 数据导入导出：将数据库或其他数据源的数据导出为 Excel 文件，或从 Excel 文件中导入数据
• 报表生成：使用 openpyxl 或 xlsxwriter 生成带有图表和样式的报表
• 数据分析：使用 pandas 对 Excel 文件中的数据进行清洗、分析和可视化
• 自动化任务：批量处理多个 Excel 文件，如合并、拆分、格式转换等

---

参考资料

• openpyxl 官方文档
• pandas 官方文档
• xlrd 官方文档
• xlwt 官方文档
• xlsxwriter 官方文档
• pyxlsb 官方文档