一、安装与环境
xlsx 是 Microsoft Excel 使用的开放 XML 电子表格文件格式的文件扩展名。 xlsm文件支持宏。 xlsx 是专有的二进制格式,而 xlsx 是基于 Office Open XML 格式的。
使用pip3
工具安装openpyxl。
$ sudo pip3 install openpyxl
二、Openpyxl 创建新文件
使用openpyxl
创建一个新的 xlsx 文件。
#!/usr/bin/env python
#从Openpyxl模块,我们导入Workbook类。工作簿是文档所有其他部分的容器。
from openpyxl import Workbook
book = Workbook() #创建一个新的工作薄,始终使用至少一个工作表创建一个工作簿。
sheet = book.active #获得对活动工作表的引用
三、Openpyxl 写入单元格
写入单元格有两种基本方法:使用工作表的键(例如 A1 或 D3),或通过cell()
方法使用行和列表示法。
#方法一:使用工作表的键(例如 A1 或 D3),
sheet['A1'] = 1
#方法二:通过cell()方法使用行和列表示法
sheet.cell(row=2, column=2).value = 2 #写入单元格 B2
四、Openpyxl 附加值
使用append()
方法,我们可以在当前工作表的底部附加一组值。
#!/usr/bin/env python
from openpyxl import Workbook
book = Workbook()
sheet = book.active
#数据存储在元组的元组中
rows = (
(88, 46, 57),
(89, 38, 12),
(23, 59, 78),
(56, 21, 98),
(24, 18, 43),
(34, 15, 67)
)
#逐行浏览容器,并使用append()方法插入数据行
for row in rows:
sheet.append(row)
五、OpenPyXL 读取表格数据
从sample.xlsx
文件中读取先前写入的数据
#!/usr/bin/env python
import os
from openpyxl import load_workbook
if __name__ == '__main__':
cur_path = os.path.abspath(os.path.dirname(__file__)) #获取当前项目的根目录
Read_Book = load_workbook(cur_path + r'/Document/sample.xlsx') #使用load_workbook()方法打开文件
Read_sheet = Read_Book.active
#读取单元格
#读取 A1,A2 和 A3 单元的内容,
a1 = Read_sheet['A1']
a2 = Read_sheet.cell(row=2, column=1) #使用cell()方法获取 A2 单元格的值
print("A1:", a1.value)
print("A2:", a2.value)
print("A3:", Read_sheet['A3'].value)
#读取多个单元格
#使用范围运算从两列读取数据
cells = Read_sheet['A1': 'B3']
for c1, c2 in cells:
print("{0:8} {1:8}".format(c1.value, c2.value))
六、Openpyxl 迭代
iter_rows()
方法将工作表中的单元格返回为行(按行迭代)
for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=3):
for cell in row:
print(cell.value, end=" ")
print()
iter_cols()
方法将工作表中的单元格作返回为列(按列迭代)
for row in sheet.iter_cols(min_row=1, min_col=1, max_row=6, max_col=3):
for cell in row:
print(cell.value, end=" ")
print()
七、统计
#!/usr/bin/env python
import openpyxl
import statistics as stats
# 导入statistics模块以提供一些统计功能,例如中值和方差。
book = openpyxl.load_workbook('numbers.xlsx', data_only=True) #使用data_only选项,我们从单元格而不是公式中获取值。
sheet = book.active
rows = sheet.rows #得到所有不为空的单元格行。
values = []
# 在两个 for 循环中,我们从单元格中形成一个整数值列表。
for row in rows:
for cell in row:
values.append(cell.value)
# 从工作表中读取所有值并计算一些基本统计信息
# 计算并打印有关值的数学统计信息。 一些功能是内置的,其他功能是通过statistics模块导入的。
print("Number of values: {0}".format(len(values)))
print("Sum of values: {0}".format(sum(values)))
print("Minimum value: {0}".format(min(values)))
print("Maximum value: {0}".format(max(values)))
print("Mean: {0}".format(stats.mean(values)))
print("Median: {0}".format(stats.median(values)))
print("Standard deviation: {0}".format(stats.stdev(values)))
print("Variance: {0}".format(stats.variance(values)))
八、Openpyxl 过滤器&排序数据
图纸具有auto_filter
属性,该属性允许设置过滤条件和排序条件。
请注意,Openpyxl 设置了条件,但是我们必须在电子表格应用中应用它们。
#!/usr/bin/env python
from openpyxl import Workbook
wb = Workbook()
sheet = wb.active
data = [
['Item', 'Colour'],
['pen', 'brown'],
['book', 'black'],
['plate', 'white'],
['chair', 'brown'],
['coin', 'gold'],
['bed', 'brown'],
['notebook', 'white'],
]
for r in data:
sheet.append(r)
sheet.auto_filter.ref = 'A1:B8'
sheet.auto_filter.add_filter_column(1, ['brown', 'white'])
sheet.auto_filter.add_sort_condition('B2:B8')
wb.save('filtered.xlsx')
在示例中,我们创建一个包含项目及其颜色的工作表。 我们设置一个过滤器和一个排序条件
九、Openpyxl 维度
为了获得那些实际包含数据的单元格,我们可以使用维度。
#!/usr/bin/env python
from openpyxl import Workbook
book = Workbook()
sheet = book.active
sheet['A3'] = 39
sheet['B3'] = 19
rows = [
(88, 46),
(89, 38),
(23, 59),
(56, 21),
(24, 18),
(34, 15)
]
for row in rows:
sheet.append(row)
#dimensions属性返回非空单元格区域的左上角和右下角单元格。
print(sheet.dimensions)
#使用min_row和max_row属性,我们可以获得包含数据的最小和最大行。
#通过min_column和max_column属性,我们获得了包含数据的最小和最大列。
print("Minimum row: {0}".format(sheet.min_row))
print("Maximum row: {0}".format(sheet.max_row))
print("Minimum column: {0}".format(sheet.min_column))
print("Maximum column: {0}".format(sheet.max_column))
for c1, c2 in sheet[sheet.dimensions]:
print(c1.value, c2.value)
book.save('dimensions.xlsx')
该示例计算两列数据的维数。
十、工作表
每个工作簿可以有多个工作表
#!/usr/bin/env python
import openpyxl
book = openpyxl.load_workbook('sheets.xlsx')
print(book.get_sheet_names()) #get_sheet_names()方法返回工作簿中可用工作表的名称。
#获取活动表并将其类型打印到终端
active_sheet = book.active
print(type(active_sheet))
sheet = book.get_sheet_by_name("March")#使用get_sheet_by_name()方法获得对工作表的引用。
print(sheet.title) #检索到的工作表的标题将打印到终端
#!/usr/bin/env python
import openpyxl
book = openpyxl.load_workbook('sheets.xlsx')
book.create_sheet("April")#使用create_sheet()方法创建一个新图纸
print(book.sheetnames) #图纸名称也可以使用sheetnames属性显示
sheet1 = book.get_sheet_by_name("January")
book.remove_sheet(sheet1) #可以使用remove_sheet()方法将纸张取出
print(book.sheetnames)
可以在指定位置创建一个新图纸。 在我们的例子中,我们在索引为 0 的位置创建一个新工作表。
book.create_sheet("January", 0)
print(book.sheetnames)
book.save('sheets2.xlsx')
在此示例中,我们创建一个新工作表。
#!/usr/bin/env python
import openpyxl
book = openpyxl.load_workbook('sheets.xlsx')
sheet = book.get_sheet_by_name("March")
sheet.sheet_properties.tabColor = "0072BA"
book.save('sheets3.xlsx')
该示例修改了标题为“ March”的工作表的背景颜色
十一、合并单元格
单元格可以使用merge_cells()
方法合并,而可以不使用unmerge_cells()
方法合并。 当我们合并单元格时,除了左上角的所有单元格都将从工作表中删除。
#!/usr/bin/env python
from openpyxl import Workbook
from openpyxl.styles import Alignment
# 为了使文本在最后一个单元格中居中,我们使用了openpyxl.styles模块中的Alignment类。
book = Workbook()
sheet = book.active
sheet.merge_cells('A1:B2') #我们用merge_cells()方法合并四个单元格
cell = sheet.cell(row=1, column=1) #我们得到了最后一个单元格。
cell.value = 'Sunny day'
cell.alignment = Alignment(horizontal='center', vertical='center') #我们将文本设置为合并的单元格并更新其对齐方式
book.save('merging.xlsx')
该示例中,我们合并了四个单元格:A1,B1,A2 和 B2。 最后一个单元格中的文本居中。
十二、Openpyxl 冻结窗格
冻结窗格时,在滚动到工作表的另一个区域时,我们会保持工作表的某个区域可见。
该示例通过单元格 B2 冻结窗格。
#!/usr/bin/env python
from openpyxl import Workbook
from openpyxl.styles import Alignment
book = Workbook()
sheet = book.active
sheet.freeze_panes = 'B2' #要冻结窗格,我们使用freeze_panes属性。
book.save('freezing.xlsx')
十三、Openpyxl 公式
显示如何使用公式。 openpyxl
不进行计算; 它将公式写入单元格。
在示例中,我们使用SUM()函数计算所有值的总和,并以粗体显示输出样式。
#!/usr/bin/env python
from openpyxl import Workbook
book = Workbook()
sheet = book.active
# 创建两列数据
rows = (
(34, 26),
(88, 36),
(24, 29),
(15, 22),
(56, 13),
(76, 18)
)
for row in rows:
sheet.append(row)
cell = sheet.cell(row=7, column=2) #得到显示计算结果的单元格
cell.value = "=SUM(A1:B6)" #将一个公式写入单元格
cell.font = cell.font.copy(bold=True) #更改字体样式。
book.save('formulas.xlsx')
十四、OpenPyXL 图像
下面的示例中,我们显示了如何将图像插入到工作表中。示例中,我们将图像写到一张纸上。
#!/usr/bin/env python
from openpyxl import Workbook
from openpyxl.drawing.image import Image
# 使用openpyxl.drawing.image模块中的Image类
book = Workbook()
sheet = book.active
img = Image("icesid.png") #创建一个新的Image类。 icesid.png图像位于当前工作目录中
sheet['A1'] = 'This is Sid'
sheet.add_image(img, 'B2') #我们使用add_image()方法添加新图像
book.save("sheet_image.xlsx")
十五、Openpyxl 图表
openpyxl
库支持创建各种图表,包括条形图,折线图,面积图,气泡图,散点图和饼图。
根据文档,openpyxl
仅支持在工作表中创建图表。 现有工作簿中的图表将丢失。
!/usr/bin/env python
from openpyxl import Workbook
from openpyxl.chart import (
Reference,
Series,
BarChart
)
# openpyxl.chart模块具有使用图表的工具
book = Workbook()
sheet = book.active
rows = [
("USA", 46),
("China", 38),
("UK", 29),
("Russia", 22),
("South Korea", 13),
("Germany", 11)
]
for row in rows:
sheet.append(row)
# 创建一些数据并将其添加到活动工作表的单元格中。
data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=6) #对于Reference类,我们引用表中代表数据的行。 在我们的案例中,这些是奥运金牌的数量。
categs = Reference(sheet, min_col=1, min_row=1, max_row=6) #我们创建一个类别轴。 类别轴是将数据视为一系列非数字文本标签的轴。 在我们的案例中,我们有代表国家名称的文本标签。
# 我们创建一个条形图并为其设置数据和类别。
chart = BarChart()
chart.add_data(data=data)
chart.set_categories(categs)
# 使用legend和majorGridlines属性,可以关闭图例和主要网格线。
chart.legend = None
chart.y_axis.majorGridlines = None
# 将varyColors设置为True,每个条形都有不同的颜色。
chart.varyColors = True
chart.title = "Olympic Gold medals in London" #为图表设置标题
sheet.add_chart(chart, "A8") #使用add_chart()方法将创建的图表添加到工作表中
book.save("bar_chart.xlsx")
在此示例中,我们创建了一个条形图,以显示 2012 年伦敦每个国家/地区的奥运金牌数量。
https://geek-docs.com/python/python-tutorial/python-openpyxl.html#Openpyxl-4