Rumah > Artikel > pembangunan bahagian belakang > Cara menggunakan python untuk mengendalikan artifak Excel openpyxl
Cara menggunakan python untuk mengendalikan artifak Excel openpyxl
- PHPzke hadapan
- 2023-05-12 10:01:052468semak imbas
Excel xlsx
xlsx ialah sambungan fail untuk format fail hamparan XML terbuka yang digunakan oleh Microsoft Excel. Fail xlsm menyokong makro. xlsx ialah format binari proprietari, manakala xlsx adalah berdasarkan format XML Office Open.
$ sudo pip3 install openpyxl
Kami menggunakan pip3alat untuk memasangopenpyxl.
Openpyxl mencipta fail baharu
Dalam contoh pertama, kami menggunakan openpyxl untuk mencipta fail xlsx baharu.
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")Dalam contoh, kami mencipta fail xlsx baharu. Kami menulis data kepada tiga sel.
from openpyxl import Workbook
Daripada modul openpyxl, kami mengimport kelas Workbook. Buku kerja ialah bekas untuk semua bahagian lain dokumen.
book = Workbook()
Jom buat buku kerja baharu. Sentiasa buat buku kerja dengan sekurang-kurangnya satu lembaran kerja.
sheet = book.active
Kami mendapat rujukan kepada helaian aktif.
sheet['A1'] = 56 sheet['A2'] = 43
Kami menulis data berangka ke dalam sel A1 dan A2.
now = time.strftime("%x")
sheet['A3'] = nowKami menulis tarikh semasa ke dalam sel A3.
book.save("sample.xlsx")Kami menggunakan kaedah save() untuk menulis kandungan pada fail sample.xlsx.
Tulis Openpyxl ke sel
Terdapat dua cara asas untuk menulis ke sel: menggunakan kekunci lembaran kerja (seperti A1 atau D3), atau menggunakan perwakilan baris dan lajur melalui cell() kaedah Undang-undang.
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')
Dalam contoh, kita menulis dua nilai kepada dua sel.
sheet['A1'] = 1
Di sini kami menetapkan nilai pada sel A1.
sheet.cell(row=2, column=2).value = 2
Dalam baris ini, kami menulis ke sel B2 menggunakan tatatanda baris dan lajur.
Openpyxl menambahkan nilai
Menggunakan kaedah append(), kita boleh menambahkan set nilai ke bahagian bawah lembaran kerja semasa.
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')Dalam contoh, kami menambahkan tiga lajur data pada lembaran kerja semasa.
rows = (
(88, 46, 57),
(89, 38, 12),
(23, 59, 78),
(56, 21, 98),
(24, 18, 43),
(34, 15, 67)
)Data disimpan dalam tuple tuple.
for row in rows:
sheet.append(row)Kami melalui baris demi baris bekas dan memasukkan baris data menggunakan kaedah append().
Sel Pembacaan OpenPyXL
Dalam contoh di bawah, kami membaca data yang ditulis sebelum ini daripada fail 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)
Contoh ini memuatkan fail xlsx sedia ada dan membaca tiga sel.
book = openpyxl.load_workbook('sample.xlsx')
Buka fail menggunakan kaedah load_workbook().
a1 = sheet['A1'] a2 = sheet['A2'] a3 = sheet.cell(row=3, column=1)
Kami membaca kandungan sel A1, A2 dan A3. Dalam baris ketiga, kami menggunakan kaedah cell() untuk mendapatkan nilai sel A3.
$ ./read_cells.py 56 43 10/26/16
Ini adalah output contoh.
OpenPyXL membaca berbilang sel
Kami mempunyai jadual data berikut:
Kami menggunakan operator julat untuk membaca data.
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))Dalam contoh, kami membaca data daripada dua lajur menggunakan operasi julat.
cells = sheet['A1': 'B6']
Dalam baris ini, kami membaca data daripada sel A1-B6. Fungsi
for c1, c2 in cells:
print("{0:8} {1:8}".format(c1.value, c2.value))format() digunakan untuk mengeluarkan data dengan kemas pada konsol. Kaedah
$ ./read_cells2.py Items Quantity coins 23 chairs 3 pencils 5 bottles 8 books 30
Openpyxl Iterate by Rows
iter_rows() mengembalikan sel dalam lembaran kerja sebagai baris.
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')Contoh ini berulang melalui baris data demi baris.
for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=3):
Kami menyediakan had untuk lelaran. Kaedah
$ ./iterating_by_rows.py 88 46 57 89 38 12 23 59 78 56 21 98 24 18 43 34 15 67
Openpyxl Iterate by Column
iter_cols() mengembalikan sel dalam lembaran kerja sebagai lajur.
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')Contoh ini berulang melalui lajur data demi lajur.
$ ./iterating_by_columns.py 88 89 23 56 24 34 46 38 59 21 18 15 57 12 78 98 43 67
Statistik
Untuk contoh seterusnya kita perlu mencipta fail xlsx yang mengandungi nombor. Sebagai contoh, kami menggunakan fungsi RANDBETWEEN() untuk mencipta 25 baris nombor dalam 10 lajur.
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)))Dalam contoh, kami membaca semua nilai dari lembaran kerja dan mengira beberapa statistik asas.
import statistics as stats
mengimport modul statistics untuk menyediakan beberapa fungsi statistik seperti median dan varians.
book = openpyxl.load_workbook('numbers.xlsx', data_only=True)
Menggunakan pilihan data_only kita mendapat nilai daripada sel dan bukannya formula.
rows = sheet.rows
Kami mendapat semua baris sel yang tidak kosong.
for row in rows:
for cell in row:
values.append(cell.value)Dalam dua gelung, kami membentuk senarai nilai integer daripada sel.
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)))Kami mengira dan mencetak statistik matematik tentang nilai. Sesetengah fungsi terbina dalam, yang lain diimport melalui modul 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 Filter & Isih Data
Lukisan mempunyai auto_filter atribut yang membenarkan menetapkan keadaan penapisan dan isihan.
Sila ambil perhatian bahawa Openpyxl menetapkan syarat, tetapi kami mesti menggunakannya dalam aplikasi hamparan.
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')Dalam contoh, kami mencipta lembaran kerja yang mengandungi item dan warnanya. Kami menetapkan penapis dan syarat pengisihan.
Dimensi Openpyxl
Untuk mendapatkan sel yang sebenarnya mengandungi data, kita boleh menggunakan dimensi.
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')Contoh ini mengira dimensi dua lajur data.
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)Kami menambah data pada lembaran kerja. Ambil perhatian bahawa kami mula menambah pada baris ketiga. Sifat
print(sheet.dimensions)
dimensions mengembalikan sel kiri atas dan kanan bawah julat sel yang tidak kosong.
print("Minimum row: {0}".format(sheet.min_row))
print("Maximum row: {0}".format(sheet.max_row))Menggunakan atribut min_row dan max_row kita boleh mendapatkan baris minimum dan maksimum yang mengandungi data.
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()方法将创建的图表添加到工作表中。
Dalam tutorial ini, kami menggunakan perpustakaan openpyxl. Kami telah membaca data daripada fail Excel dan data bertulis kepada fail Excel.
Atas ialah kandungan terperinci Cara menggunakan python untuk mengendalikan artifak Excel openpyxl. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!
Artikel berkaitan
Lihat lagi- Bagaimana untuk Meratakan Pemahaman Senarai Bersarang dalam Python?
- Bagaimanakah `random.seed()` mengawal rawak dalam modul `rawak` Python?
- Bila hendak Memilih `OneToOneField()` lwn `ForeignKey(unique=True)` dalam Django?
- Bagaimana untuk Memastikan Matplotlib Plotting Interaktif Semasa Pengiraan Berterusan?
- Mengapa Koma Jejak Mencipta Tuple dalam Python?