openpyxl学习笔记

openpyxl 是一个用于读写 Excel 文件的 Python 库它提供了一种简单而强大的方式来操作 Excel 电子表格下面是 openpyxl 的基本介绍和使用方法

# 1. 安装 openpyxl
# pip install openpyxl

# 2. 引入 openpyxl 模块
from openpyxl import Workbook, load_workbook

# 3. 创建一个新的工作簿(Workbook)
wb = Workbook()

# 4. 获取当前活动的工作表(Worksheet)
ws = wb.active

# 5. 访问和修改单元格的值
ws['A1'] = 'Hello'
ws.cell(row=1, column=2, value='World')

# 6. 保存工作簿到文件
wb.save('example.xlsx')

# 7. 加载已有的 Excel 文件
wb = load_workbook('example.xlsx')

# 8. 选择特定的工作表
ws = wb['Sheet1']

# 9. 遍历工作表的行和列
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
   for cell in row:
       print(cell.value)

# 10. 插入行或列
ws.insert_rows(2)
ws.insert_cols(2)

# 11. 删除行或列
ws.delete_rows(2)
ws.delete_cols(2)

# 12. 合并和拆分单元格
ws.merge_cells('A1:B2')
ws.unmerge_cells('A1:B2')

# 13. 设置单元格样式
from openpyxl.styles import Font, Alignment, Border, Side
cell = ws['A1']
cell.font = Font(bold=True)
cell.alignment = Alignment(horizontal='center')
cell.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))

以下是一些openpyxl的高阶用法:

# 1. 使用公式
ws['C1'] = '=SUM(A1:B1)'

# 2. 引用其他工作表的单元格
ws['C1'] = '=Sheet2!A1'

# 3. 添加数据验证
from openpyxl.worksheet.datavalidation import DataValidation
dv = DataValidation(type="list", formula1='"红色,蓝色,绿色"', allow_blank=True)
ws.add_data_validation(dv)
dv.add('A1:A10')

# 4. 创建图表
from openpyxl.chart import BarChart, Reference
data = Reference(ws, min_col=1, min_row=1, max_col=3, max_row=10)
chart = BarChart()
chart.add_data(data)
ws.add_chart(chart, 'E2')

# 5. 添加超链接
from openpyxl.worksheet.hyperlink import Hyperlink
ws['D1'].hyperlink = Hyperlink(ref='', target='<https://www.example.com>')
ws['D1'].style = 'Hyperlink'

# 6. 条件格式
from openpyxl.styles import PatternFill, Font, Border, Alignment
from openpyxl.formatting.rule import CellIsRule
red_fill = PatternFill(start_color='FFFF0000', end_color='FFFF0000', fill_type='solid')
ws.conditional_formatting.add('A1:A10', CellIsRule(operator='greaterThan', formula=['5'], fill=red_fill))

# 7. 添加注释
from openpyxl.comments import Comment
comment = Comment('This is a comment', 'Author')
ws['A1'].comment = comment

# 8. 设置行高和列宽
ws.row_dimensions[1].height = 50
ws.column_dimensions['A'].width = 20

# 9. 冻结窗口
ws.freeze_panes = 'B2'

# 10. 保护工作表
ws.protection.sheet = True

工作中常常需要提取一个表格中的某几行中的某几列，下面是一个使用openpyxl提取指定行和列的示例代码:

from openpyxl import load_workbook

# 加载工作簿
workbook = load_workbook('example.xlsx')
sheet = workbook.active

# 指定要提取的行和列
rows_to_extract = [2, 4, 6]  # 提取第2、4、6行
columns_to_extract = [1, 3, 5]  # 提取第1、3、5列

# 创建一个新的工作簿和工作表
new_workbook = Workbook()
new_sheet = new_workbook.active

# 提取指定的行和列,并将数据写入新工作表
for row_num in rows_to_extract:
    for col_num in columns_to_extract:
        cell_value = sheet.cell(row=row_num, column=col_num).value
        new_sheet.cell(row=rows_to_extract.index(row_num) + 1, column=columns_to_extract.index(col_num) + 1, value=cell_value)

# 保存新工作簿
new_workbook.save('extracted_data.xlsx')

这个示例代码的步骤如下:

1. 加载一个现有的Excel工作簿(example.xlsx)。
2. 指定要提取的行和列号,分别存储在rows_to_extract和columns_to_extract列表中。这里,我们要提取第2、4、6行和第1、3、5列。
3. 创建一个新的工作簿和工作表,用于存储提取的数据。
4. 使用两个嵌套的循环遍历指定的行和列号:
   • 对于每个要提取的行号row_num,遍历要提取的列号col_num。
   • 使用sheet.cell(row=row_num, column=col_num).value获取指定行和列的单元格值。
   • 将获取到的单元格值写入新工作表的对应位置。这里使用rows_to_extract.index(row_num) + 1和columns_to_extract.index(col_num) + 1计算新工作表中的行号和列号,确保提取的数据按照原始顺序排列。
5. 保存新工作簿为extracted_data.xlsx。