ホームページ >バックエンド開発 >Python チュートリアル >Pythonを使用してExcelアーティファクトを操作する方法openpyxl
Pythonを使用してExcelアーティファクトを操作する方法openpyxl
- PHPz転載
- 2023-05-12 10:01:052493ブラウズ
Excel xlsx
xlsx は、Microsoft Excel で使用される Open 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 ファイルを作成します。 3 つのセルにデータを書き込みます。
from openpyxl import Workbook
openpyxl モジュールから、Workbook クラスをインポートします。ワークブックは、ドキュメントの他のすべての部分のコンテナーです。
book = Workbook()
新しいワークブックを作成します。常に少なくとも 1 つのワークシートを含むワークブックを作成します。
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 のセルへの書き込み
セルに書き込むには 2 つの基本的な方法があります。ワークシートのキー (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')
この例では、2 つのセルに 2 つの値を書き込みます。
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')この例では、3 列のデータを現在のワークシートに追加します。
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 ファイルをロードし、3 つのセルを読み取ります。
book = openpyxl.load_workbook('sample.xlsx')
load_workbook() メソッドを使用してファイルを開きます。
a1 = sheet['A1'] a2 = sheet['A2'] a3 = sheet.cell(row=3, column=1)
セル A1、A2、A3 の内容を読み取ります。 3 行目では、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))この例では、範囲操作を使用して 2 つの列からデータを読み取ります。
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 Iterate by rows
iter_rows()このメソッドは、ワークシート内のセルを行として返します。
iterated_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()このメソッドは、ワークシート内のセルを列として返します。
iterated_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
Statistics
次の例では、数値を含む 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)2 つの 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')この例では、2 つのデータ列の次元を計算します。
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)データをワークシートに追加します。 3 行目から追加を開始していることに注意してください。
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 ファイルにデータを書き込みました。
以上がPythonを使用してExcelアーティファクトを操作する方法openpyxlの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。