Vsoapmac
文章
107
分类
17
评论
0

使用openpyxl轻松操控Excel数据

| 数据分析 • python •
字数总计 14364 | 阅读时长 35分钟 | 阅读量 52

openpyxl是一个用于读写Excel 2010以上版本产生的xlsx/xlsm/xltx/xltm文件的Python库。它提供了一组简单易用的API，允许开发者创建、修改和读取Excel文件。这个库特别适用于处理大量的数据，且读写速度相对较快。

openpyxl中有三个核心的概念：

  1. Workbook：对工作簿的抽象类，一个Excel文件就对应一个Workbook对象，它包含多个Worksheet对象。

  2. Worksheet：对工作表的抽象类，一个Workbook对象包含多个Worksheet对象，每个Worksheet对象对应Excel文件中的一个sheet。Worksheet对象包含多个Cell对象，并提供了对表格的各种操作，如插入、删除行列等。

  3. Cell：对单元格的抽象类，是Worksheet对象的最小组成单元，存储着Excel表格中的数据。

此外，openpyxl还支持一些高级功能，如合并单元格、插入图像、设置公式、设置数据验证等。它提供了丰富的API，使得开发者可以灵活地处理Excel文件。

注意：openpyxl只支持Excel 2007及以上版本产生的xlsx文件

安装

pip install openpyxl

Excel操作

打开一个现有的Excel

使用load_workbook打开xlsx，例子如下：

from openpyxl import load_workbook

wb = load_workbook("./test.xlsx")
print(wb.sheetnames)

以下是load_workbook一些常用的参数：

  • filename（必需）：这是你要加载的Excel文件的路径。可以是一个相对路径或绝对路径。

  • read_only（可选，默认为False）：如果设置为True，则以只读模式加载Excel。这可以显著提高加载大型文件时的性能，但你将无法对Excel进行修改。

  • keep_vba（可选，默认为False）：如果设置为True，则在加载Excel时保留其中的VBA（Visual Basic for Applications）内容。这可能会增加内存消耗，并且read_only参数必须为False。

  • keep_links（可选，默认为True）：控制是否应保留外部链接。如果设置为False，则外部链接将被删除。

  • data_only（可选，默认为False）：当单元格包含货币或日期格式时，此参数控制是否应仅加载单元格的数据，而忽略格式信息。如果设置为True，则仅加载数据；如果设置为False，则同时加载数据和格式。需要注意的是，这个参数在read_only模式下不起作用。

  • keep_formulas（可选，当data_only=True时有效，默认为True）：如果设置为False，则在data_only模式下不保留公式，只保留公式计算后的值。

创建一个新的Excel表

Workbook对象可以创建一个新的xlsx，例子如下：

from openpyxl import Workbook

wb = Workbook()
wb.save('example.xlsx')

Sheet操作

读取Excel表中的sheet

可以通过sheetnames去获取该excel中所有的sheet，例子如下：

from openpyxl import load_workbook

wb = load_workbook("./test.xlsx")
print(wb.sheetnames)

若需要获取特定的sheet，方法如下：

from openpyxl import load_workbook

wb = load_workbook("./test.xlsx")
sheet = wb['Sheet1']

也可以获取当前活动的sheet

from openpyxl import load_workbook

wb = load_workbook("./test.xlsx")
sheet = wb.active

更改sheet标题

from openpyxl import load_workbook

wb = load_workbook("./test.xlsx")
sheet = wb['Sheet1']
sheet.title = "Sheet2" # 重新命名sheet
wb.save('test.xlsx')

新建sheet

from openpyxl import load_workbook

wb = load_workbook("./test.xlsx")
wb.create_sheet("Sheet2")
wb.save('./test.xlsx')

复制sheet

from openpyxl import load_workbook

wb = load_workbook("./test.xlsx")
copy_sheet = wb.copy_worksheet(wb["Sheet1"])
copy_sheet.title = "Sheet2" # 将复制过来的sheet命名
wb.save('test.xlsx')

注意，openpyxl只能复制当前excel中的sheet

删除sheet

from openpyxl import load_workbook

wb = load_workbook("./test.xlsx")
del wb["Sheet2"]
wb.save('test.xlsx')

单元格数据操作

在操作单元格时，我们首先需要加载或创建workbook：

from openpyxl import load_workbook, Workbook

# wb = Workbook() # 创建workbook
wb = load_workbook("./test.xlsx")
sheet = wb['Sheet1']

读取单元格数据

openpyxl读取单元格数据的方法有两个，分别如下：

# 读取指定单元格的数据
cell_value = sheet['A1'].value  # 读取A1（A列，第一行）单元格的数据
print(cell_value)

# 另一种方法：使用cell函数通过行列索引来获取单元格数据
another_cell_value = sheet.cell(row=1, column=1).value  # 同样读取A1单元格的数据
print(another_cell_value)

cell中的row和column数字最小是1

批量读取单元格数据

操作特定的列与行：

# 获取单列数据
for cell in sheet["A"]:
    print(cell.value)

# 获取单行数据
for cell in sheet["1"]:
    print(cell.value)

# 获取多列数据
for column in sheet['A:B']:
    for cell in column:
        print(cell.value)

# 获取多行数据
for row in sheet['1:2']:
    for cell in row:
        print(cell.value)

# 指定范围单元格范围
for row in sheet['A1:B2']:
    for cell in row:
        print(cell.value)

不知道特定的行和列，想要获取全部的行或者列的数据，可以用iter_rows()或iter_cols()方法来实现这一点。这些方法允许你按行或按列迭代单元格，并可以选择性地指定要迭代的行或列的范围。

# 使用iter_rows()方法批量读取每行的单元格数据
for row in sheet.iter_rows(min_row=1, max_row=5, min_col=1, max_col=3):
    for cell in row:
        print(cell.value)  # 打印单元格的值

# 使用iter_cols()方法批量读取每列的单元格数据
for col in sheet.iter_cols(min_row=1, max_row=5, min_col=1, max_col=3):
    for cell in col:
        print(cell.value)  # 打印单元格的值

你可以根据需要调整min_row、max_row、min_col和max_col参数来指定迭代范围（也可以不填写）。内层的循环则遍历每行中的单元格，并修改它们的值。

修改单元格数据

# 修改指定单元格的数据
sheet['A1'] = 'New Value' # 将A1单元格的数据修改为'New Value'
cell_b2 = sheet.cell(row=1, column=1).value = 'Another New Value' # 将A1单元格的数据修改为'Another New Value'

# 保存工作簿，以将更改写入文件
wb.save(filename='test.xlsx')

批量修改单元格数据

操作特定的列与行：

# 修改单列数据
for cell in sheet["A"]:
    cell.value = 'New Value'

# 修改单行数据
for cell in sheet["1"]:
    cell.value = 'New Value'

# 修改多列数据
for column in sheet['A:B']:
    for cell in column:
        cell.value = 'New Value'

# 修改多行数据
for row in sheet['1:2']:
    for cell in row:
        cell.value = 'New Value'

# 指定范围单元格范围
for row in sheet['A1:B2']:
    for cell in row:
        print(cell.value)

# 保存工作簿，以将更改写入文件
wb.save(filename='test.xlsx')

不知道特定的行和列，想要修改全部的行或者列的数据，可以用iter_rows()或iter_cols()方法来实现这一点。这些方法允许你按行或按列迭代单元格，并可以选择性地指定要迭代的行或列的范围。

# 使用iter_rows()方法批量修改每行的单元格数据
for row in sheet.iter_rows(min_row=1, max_row=5, min_col=1, max_col=3):
    for cell in row:
        cell.value = 'New Value' # 修改单元格的值

# 使用iter_cols()方法批量修改每列的单元格数据
for col in sheet.iter_cols(min_row=1, max_row=5, min_col=1, max_col=3):
    for cell in col:
        cell.value = 'New Value' # 修改单元格的值

# 保存工作簿，以将更改写入文件
wb.save(filename='test.xlsx')

你可以根据需要调整min_row、max_row、min_col和max_col参数来指定迭代范围（也可以不填写）。内层的循环则遍历每行中的单元格，并打印它们的值。

在sheet末尾增加整行数据

# 使用append方法添加一行数据
sheet.append([1, 2, 3])  # 使用列表添加一行数据
sheet.append((1, 2, 3))  # 使用元组添加一行数据

# 保存工作簿，以将更改写入文件
wb.save(filename='test.xlsx')

删除单元格数据

del sheet["A1"]
sheet.cell(1, 1).value = ""

# 保存工作簿，以将更改写入文件
wb.save(filename='test.xlsx')

合并单元格

# 合并
sheet.merge_cells('A1:B2')
# 解除合并
sheet.unmerge_cells('A1:B2')

sheet.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
sheet.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)

# 保存工作簿，以将更改写入文件
wb.save(filename='test.xlsx')

注意：对于没有合并过单元格的位置调用 unmerge_cells 时会报错

单元格样式操作

在操作单元格时，我们首先需要加载或创建workbook：

from openpyxl import load_workbook, Workbook

# wb = Workbook() # 创建workbook
wb = load_workbook("./test.xlsx")
sheet = wb['Sheet1']

OpenPyXl 用6种类来设置单元格的样式

  • NumberFormat 数字

  • Alignment 对齐

  • Font 字体

  • Border 边框

  • PatternFill 填充

  • Protection 保护

字体

from openpyxl.styles import Font  
  
# 创建一个字体对象  
font = Font(name='Arial', size=12, bold=True, italic=False, color='FF0000')  
  
# 应用字体到单元格  
sheet.cell(row=1, column=1).font = font

# 保存工作簿，以将更改写入文件
wb.save(filename='test.xlsx')

填充

from openpyxl.styles import PatternFill  
  
# 创建一个填充对象  
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')  
  
# 应用填充到单元格  
sheet.cell(row=1, column=1).fill = fill

# 保存工作簿，以将更改写入文件
wb.save(filename='test.xlsx')

边框

from openpyxl.styles import Border, Side  
  
# 创建一个边框对象  
border = Border(left=Side(border_style='thin', color='000000'),  
                right=Side(border_style='thin', color='000000'),  
                top=Side(border_style='thin', color='000000'),  
                bottom=Side(border_style='thin', color='000000'))  
  
# 应用边框到单元格  
sheet.cell(row=1, column=1).border = border

# 保存工作簿，以将更改写入文件
wb.save(filename='test.xlsx')

对齐

from openpyxl.styles import Alignment  
  
# 创建一个对齐对象  
alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)  
  
# 应用对齐到单元格  
sheet.cell(row=1, column=1).alignment = alignment

# 保存工作簿，以将更改写入文件
wb.save(filename='test.xlsx')

数字格式

# 直接设置单元格的数字格式属性  
sheet.cell(row=1, column=1).number_format = '0.00%'  # 设置百分比格式
sheet.cell(row=1, column=1).number_format = '0%'  # 设置为百分比格式，没有小数

# 保存工作簿，以将更改写入文件
wb.save(filename='test.xlsx')

图表操作

柱状图：

from openpyxl import load_workbook, Workbook
from openpyxl.chart import BarChart, Reference

# wb = Workbook() # 创建workbook
wb = load_workbook("./test.xlsx")
sheet = wb['Sheet1']

# 添加一些数据到工作表
rows = [
    ['Country', 'Population'],
    ['China', 1400000000],
    ['India', 1350000000],
    ['United States', 327000000],
    ['Indonesia', 267000000],
    ['Pakistan', 216000000],
    ['Brazil', 211000000],
]

for row in rows:
    sheet.append(row)

# 创建柱形图
chart = BarChart()
data = Reference(sheet, min_col=2, min_row=1, max_row=7)
categories = Reference(sheet, min_col=1, min_row=2, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.title = "Population by Country"
chart.y_axis.title = "Population"

# 将图表添加到工作表
sheet.add_chart(chart, "A9")

# 保存工作簿
wb.save("test.xlsx")

圆饼图：

from openpyxl import load_workbook, Workbook
from openpyxl.chart import PieChart, Reference

# wb = Workbook() # 创建workbook
wb = load_workbook("./test.xlsx")
sheet = wb['Sheet1']
  
# 添加数据到工作表
rows = [
    ['Country', 'Population'],
    ['China', 1400],
    ['India', 1350],
    ['United States', 327],
    ['Indonesia', 267],
    ['Others', 2000]  # 添加一个"Others"类别以完整化示例
]

for row in rows:
    sheet.append(row)

# 创建圆饼图数据系列
data = Reference(sheet, min_col=2, min_row=1, max_row=6)
categories = Reference(sheet, min_col=1, min_row=1, max_row=6)

# 创建圆饼图
chart = PieChart()
chart.add_data(data, titles_from_data=True)
chart.title = "Population by Country"

# 将图表添加到工作表
sheet.add_chart(chart, "A8")

# 保存工作簿
wb.save("test.xlsx")

文章作者： Vsoapmac
本文链接：
版权声明： 本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 soap的会员制餐厅！
数据分析 python 第三方库 个人分享
喜欢就支持一下吧