Python:openpyxl这一篇就够了
后台-插件-广告管理-内容页头部广告(手机) |
各位好,我是轩哥啊哈~ 本文展示如何使用 openpyxl 库在 Python 中使用 Excel 文件。openpyxl 是用于读取和写入 Excel 2010 xlsx / xlsm / xltx / xltm 文件的 Python 库。
目录
Excel xlsx
Openpyxl 创建新文件
Openpyxl 写入单元格
Openpyxl 附加值
OpenPyXL 读取单元格
OpenPyXL 读取多个单元格
Openpyxl 按行迭代
Openpyxl 按列迭代
统计
Openpyxl 过滤器&排序数据
Openpyxl 维度
工作表
合并单元格
Openpyxl 冻结窗格
Openpyxl 公式
OpenPyXL 图像
Openpyxl 图表
Excel xlsx
在本教程中,我们使用 xlsx 文件。 xlsx 是 Microsoft Excel 使用的开放 XML 电子表格文件格式的文件扩展名。 xlsm 文件支持宏。 xlsx 是专有的二进制格式,而 xlsx 是基于 Office Open XML 格式的。
$ sudo pip3 install openpyxl我们使用pip3工具安装openpyxl。
Openpyxl 创建新文件
在第一个示例中,我们使用openpyxl创建一个新的 xlsx 文件。
write_xlsx.py
- #!/usr/bin/env python
- from openpyxl import Workbook
- import time
- book = Workbook()
- sheet = book.active
- sheet['A1'] = 56
- sheet['A2'] = 43
- now = time.strftime("%x")
- sheet['A3'] = now
- book.save("sample.xlsx")
在示例中,我们创建一个新的 xlsx 文件。 我们将数据写入三个单元格。
from openpyxl import Workbook从openpyxl模块,我们导入Workbook类。 工作簿是文档所有其他部分的容器。
book = Workbook()我们创建一个新的工作簿。 始终使用至少一个工作表创建一个工作簿。
sheet = book.active我们获得对活动工作表的引用。
- sheet['A1'] = 56
- sheet['A2'] = 43
我们将数值数据写入单元格 A1 和 A2。
- now = time.strftime("%x")
- sheet['A3'] = now
我们将当前日期写入单元格 A3。
book.save("sample.xlsx")我们使用save()方法将内容写入sample.xlsx文件。
Openpyxl 写入单元格
写入单元格有两种基本方法:使用工作表的键(例如 A1 或 D3),或通过cell()方法使用行和列表示法。
write2cell.py
- #!/usr/bin/env python
- from openpyxl import Workbook
- book = Workbook()
- sheet = book.active
- sheet['A1'] = 1
- sheet.cell(row=2, column=2).value = 2
- book.save('write2cell.xlsx')
在示例中,我们将两个值写入两个单元格。
sheet['A1'] = 1在这里,我们将数值分配给 A1 单元。
sheet.cell(row=2, column=2).value = 2在这一行中,我们用行和列表示法写入单元格 B2。
Openpyxl 附加值
使用append()方法,我们可以在当前工作表的底部附加一组值。
appending_values.py
- #!/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)
- )
- for row in rows:
- sheet.append(row)
- book.save('appending.xlsx')
在示例中,我们将三列数据附加到当前工作表中。
- rows = (
- (88, 46, 57),
- (89, 38, 12),
- (23, 59, 78),
- (56, 21, 98),
- (24, 18, 43),
- (34, 15, 67)
- )
数据存储在元组的元组中。
- for row in rows:
- sheet.append(row)
我们逐行浏览容器,并使用append()方法插入数据行。
OpenPyXL 读取单元格
在下面的示例中,我们从sample.xlsx文件中读取先前写入的数据。
read_cells.py
- #!/usr/bin/env python
- import openpyxl
- book = openpyxl.load_workbook('sample.xlsx')
- sheet = book.active
- a1 = sheet['A1']
- a2 = sheet['A2']
- a3 = sheet.cell(row=3, column=1)
- print(a1.value)
- print(a2.value)
- print(a3.value)
该示例加载一个现有的 xlsx 文件并读取三个单元格。
book = openpyxl.load_workbook('sample.xlsx')使用load_workbook()方法打开文件。
- a1 = sheet['A1']
- a2 = sheet['A2']
- a3 = sheet.cell(row=3, column=1)
我们读取 A1,A2 和 A3 单元的内容。 在第三行中,我们使用cell()方法获取 A3 单元格的值。
- $ ./read_cells.py
- 56
- 43
- 10/26/16
这是示例的输出。
OpenPyXL 读取多个单元格
我们有以下数据表:
我们使用范围运算符读取数据。
read_cells2.py
- #!/usr/bin/env python
- import openpyxl
- book = openpyxl.load_workbook('items.xlsx')
- sheet = book.active
- cells = sheet['A1': 'B6']
- for c1, c2 in cells:
- print("{0:8} {1:8}".format(c1.value, c2.value))
在示例中,我们使用范围运算从两列读取数据。
cells = sheet['A1': 'B6']在这一行中,我们从单元格 A1-B6 中读取数据。
- for c1, c2 in cells:
- print("{0:8} {1:8}".format(c1.value, c2.value))
format()功能用于在控制台上整洁地输出数据。
- $ ./read_cells2.py
- Items Quantity
- coins 23
- chairs 3
- pencils 5
- bottles 8
- books 30
Openpyxl 按行迭代
iter_rows()方法将工作表中的单元格返回为行。
iterating_by_rows.py
- #!/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)
- )
- for row in rows:
- sheet.append(row)
- 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()
- book.save('iterbyrows.xlsx')
该示例逐行遍历数据。
for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=3):我们提供了迭代的边界。
- $ ./iterating_by_rows.py
- 88 46 57
- 89 38 12
- 23 59 78
- 56 21 98
- 24 18 43
- 34 15 67
Openpyxl 按列迭代
iter_cols()方法将工作表中的单元格作为列返回。
iterating_by_columns.py
- #!/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)
- )
- for row in rows:
- sheet.append(row)
- 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()
- book.save('iterbycols.xlsx')
该示例逐列遍历数据。
- $ ./iterating_by_columns.py
- 88 89 23 56 24 34
- 46 38 59 21 18 15
- 57 12 78 98 43 67
统计
对于下一个示例,我们需要创建一个包含数字的 xlsx 文件。 例如,我们使用RANDBETWEEN()函数在 10 列中创建了 25 行数字。
mystats.py
- #!/usr/bin/env python
- import openpyxl
- import statistics as stats
- book = openpyxl.load_workbook('numbers.xlsx', data_only=True)
- sheet = book.active
- rows = sheet.rows
- values = []
- for row in rows:
- for cell in row:
- values.append(cell.value)
- 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)))
在示例中,我们从工作表中读取所有值并计算一些基本统计信息。
import statistics as stats导入statistics模块以提供一些统计功能,例如中值和方差。
book = openpyxl.load_workbook('numbers.xlsx', data_only=True)使用data_only选项,我们从单元格而不是公式中获取值。
rows = sheet.rows我们得到所有不为空的单元格行。
- for row in rows:
- for cell in row:
- values.append(cell.value)
在两个 for 循环中,我们从单元格中形成一个整数值列表。
- 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)))
我们计算并打印有关值的数学统计信息。 一些功能是内置的,其他功能是通过statistics模块导入的。
- $ ./mystats.py
- Number of values: 312
- Sum of values: 15877
- Minimum value: 0
- Maximum value: 100
- Mean: 50.88782051282051
- Median: 54.0
- Standard deviation: 28.459203819700967
- Variance: 809.9262820512821
Openpyxl 过滤器&排序数据
图纸具有auto_filter属性,该属性允许设置过滤条件和排序条件。
请注意,Openpyxl 设置了条件,但是我们必须在电子表格应用中应用它们。
filter_sort.py
- #!/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 维度
为了获得那些实际包含数据的单元格,我们可以使用维度。
dimensions.py
- #!/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)
- print(sheet.dimensions)
- 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')
该示例计算两列数据的维数。
- 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)
我们将数据添加到工作表。 请注意,我们从第三行开始添加。
print(sheet.dimensions)dimensions属性返回非空单元格区域的左上角和右下角单元格。
- print("Minimum row: {0}".format(sheet.min_row))
- print("Maximum row: {0}".format(sheet.max_row))
使用min_row和max_row属性,我们可以获得包含数据的最小和最大行。
- print("Minimum column: {0}".format(sheet.min_column))
- print("Maximum column: {0}".format(sheet.max_column))
通过min_column和max_column属性,我们获得了包含数据的最小和最大列。
- for c1, c2 in sheet[sheet.dimensions]:
- print(c1.value, c2.value)
我们遍历数据并将其打印到控制台。
- $ ./dimensions.py
- A3:B9
- Minimum row: 3
- Maximum row: 9
- Minimum column: 1
- Maximum column: 2
- 39 19
- 88 46
- 89 38
- 23 59
- 56 21
- 24 18
- 34 15
工作表
每个工作簿可以有多个工作表。
Figure: Sheets
让我们有一张包含这三张纸的工作簿。
sheets.py
- #!/usr/bin/env python
- import openpyxl
- book = openpyxl.load_workbook('sheets.xlsx')
- print(book.get_sheet_names())
- active_sheet = book.active
- print(type(active_sheet))
- sheet = book.get_sheet_by_name("March")
- print(sheet.title)
该程序可用于 Excel 工作表。
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)检索到的工作表的标题将打印到终端。
- $ ./sheets.py
- ['January', 'February', 'March']
- <class 'openpyxl.worksheet.worksheet.Worksheet'>
- March
这是程序的输出。
sheets2.py
- #!/usr/bin/env python
- import openpyxl
- book = openpyxl.load_workbook('sheets.xlsx')
- book.create_sheet("April")
- print(book.sheetnames)
- sheet1 = book.get_sheet_by_name("January")
- book.remove_sheet(sheet1)
- print(book.sheetnames)
- book.create_sheet("January", 0)
- print(book.sheetnames)
- book.save('sheets2.xlsx')
在此示例中,我们创建一个新工作表。
book.create_sheet("April")使用create_sheet()方法创建一个新图纸。
print(book.sheetnames)图纸名称也可以使用sheetnames属性显示。
book.remove_sheet(sheet1)可以使用remove_sheet()方法将纸张取出。
book.create_sheet("January", 0)可以在指定位置创建一个新图纸。 在我们的例子中,我们在索引为 0 的位置创建一个新工作表。
- $ ./sheets2.py
- ['January', 'February', 'March', 'April']
- ['February', 'March', 'April']
- ['January', 'February', 'March', 'April']
可以更改工作表的背景颜色。
sheets3.py
- #!/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”的工作表的背景颜色。
sheet.sheet_properties.tabColor = "0072BA"我们将tabColor属性更改为新颜色。
第三工作表的背景色已更改为某种蓝色。
合并单元格
单元格可以使用merge_cells()方法合并,而可以不使用unmerge_cells()方法合并。 当我们合并单元格时,除了左上角的所有单元格都将从工作表中删除。
merging_cells.py
- #!/usr/bin/env python
- from openpyxl import Workbook
- from openpyxl.styles import Alignment
- book = Workbook()
- sheet = book.active
- sheet.merge_cells('A1:B2')
- 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。 最后一个单元格中的文本居中。
from openpyxl.styles import Alignment为了使文本在最后一个单元格中居中,我们使用了openpyxl.styles模块中的Alignment类。
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')
我们将文本设置为合并的单元格并更新其对齐方式。
Openpyxl 冻结窗格
冻结窗格时,在滚动到工作表的另一个区域时,我们会保持工作表的某个区域可见。
freezing.py
- #!/usr/bin/env python
- from openpyxl import Workbook
- from openpyxl.styles import Alignment
- book = Workbook()
- sheet = book.active
- sheet.freeze_panes = 'B2'
- book.save('freezing.xlsx')
该示例通过单元格 B2 冻结窗格。
sheet.freeze_panes = 'B2'要冻结窗格,我们使用freeze_panes属性。
Openpyxl 公式
下一个示例显示如何使用公式。 openpyxl不进行计算; 它将公式写入单元格。
formulas.py
- #!/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')
在示例中,我们使用SUM()函数计算所有值的总和,并以粗体显示输出样式。
- 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)我们更改字体样式。
OpenPyXL 图像
在下面的示例中,我们显示了如何将图像插入到工作表中。
write_image.py
- #!/usr/bin/env python
- from openpyxl import Workbook
- from openpyxl.drawing.image import Image
- book = Workbook()
- sheet = book.active
- img = Image("icesid.png")
- sheet['A1'] = 'This is Sid'
- sheet.add_image(img, 'B2')
- book.save("sheet_image.xlsx")
在示例中,我们将图像写到一张纸上。
from openpyxl.drawing.image import Image我们使用openpyxl.drawing.image模块中的Image类。
img = Image("icesid.png")创建一个新的Image类。 icesid.png图像位于当前工作目录中。
sheet.add_image(img, 'B2')我们使用add_image()方法添加新图像。
Openpyxl 图表
openpyxl库支持创建各种图表,包括条形图,折线图,面积图,气泡图,散点图和饼图。
根据文档,openpyxl仅支持在工作表中创建图表。 现有工作簿中的图表将丢失。
create_bar_chart.py
- #!/usr/bin/env python
- from openpyxl import Workbook
- from openpyxl.chart import (
- Reference,
- Series,
- BarChart
- )
- 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)
- categs = Reference(sheet, min_col=1, min_row=1, max_row=6)
- chart = BarChart()
- chart.add_data(data=data)
- chart.set_categories(categs)
- chart.legend = None
- chart.y_axis.majorGridlines = None
- chart.varyColors = True
- chart.title = "Olympic Gold medals in London"
- sheet.add_chart(chart, "A8")
- book.save("bar_chart.xlsx")
在此示例中,我们创建了一个条形图,以显示 2012 年伦敦每个国家/地区的奥运金牌数量。
- 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)
我们创建一个条形图并为其设置数据和类别。
- chart.legend = None
- chart.y_axis.majorGridlines = None
使用legend和majorGridlines属性,可以关闭图例和主要网格线。
chart.varyColors = True将varyColors设置为True,每个条形都有不同的颜色。
chart.title = "Olympic Gold medals in London"为图表设置标题。
sheet.add_chart(chart, "A8")使用add_chart()方法将创建的图表添加到工作表中。
在本教程中,我们使用了 openpyxl 库。 我们已经从 Excel 文件中读取数据,并将数据写入 Excel 文件中。
1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。
在线投稿:投稿 站长QQ:1888636
后台-插件-广告管理-内容页尾部广告(手机) |