Linuxでopenpyxlを利用してExcelのワークシートを操作する

投稿日: 2021年12月4日 2022年5月14日
投稿者: 老猫こてつ.f90
カテゴリー: Python

LibreOfficeは、Microsoft Officeと高い互換性を持つオフィスソフトである。Linuxのディストリビューションには、ほとんどインストールされている。Microsoft OfficeからLibreOfficeのWriter、Calc、Impressといったファイルを読み込むことができ、編集も可能になっている。その逆も同じように読み込みこんで編集することが可能である。

何も知らないと、そんなことはないと思うかもしれないが、Office365のオンライン版でやってみるとよくわかるはずだ。Calcで作成した表（.ods）をなんの問題もなく読み込むことができる。ただし、相互の独自の関数などはサポートされないことに注意する。

ただ高い互換性といっても100％ではないが、一般的なビジネス文書では互換性は100％といってもいいほど、レイアウトが崩れることがない。Calcにおいては「Option VBASupport 1」というオプションを先頭に記述すればVBAのコードを動かせるようになった。

今回は、Excelが使えなくてもLibreOffice Calcで、Pythonを使ったワークシートへのアクセスについてやってみることにする。なお、使用する形式は.xlsx形式なので、保存する際は.odsではないことに注意しよう。

openpyxlのインストール

Excelのファイル（xlsx/xlsm/xltx/xltm）ファイルを読み書きする、Pythonのライブラリとしてopenpyxlをインストールする。直接Excelをコントロールするものではなく、ファイルに対してアクセスするものなので、形式さえあっていれば問題がない。

pip3 install openpyxl

ほかにもあるが、とりあえずこれひとつで事足りる。openpyxlについて詳しく知りたければ、ドキュメントがあるので読んでみるといいだろう。英語だが、右クリックで日本語に翻訳してしまえばいい。サンプルコードも掲載されているので、使い方ぐらいは理解できるはずだ。

簡単なデモコード

LibreOffice Calcで空のファイルを作成しておく。その際、ワークシートSheet1の名前を「成績表」に変更する。ファイル名は適当でも構わないが、一応「sample.xlsx」としておこう。フォルダは/home/<ユーザー名>/Documentsとしておく。同じフォルダに、以下のコードを記述して、「demo.py」として保存する。

LibreOffice Calcで空のファイルを作るのが面倒であれば、ファイルの新規作成の箇所をコメントアウトして実行すればよい。

# openpyxlを使った簡単なデモ
import pandas as pd
import openpyxl
from openpyxl.styles.borders import Border, Side
from openpyxl.styles.fonts import Font
from openpyxl.styles.alignment import Alignment


# ワークシートに書き込むデータ
title_txt = 'アニマル・フォレスト中学校 - ３教科成績表'
data = [
    ['氏名', '国語', '数学', '英語'],
    ['うさぎさん', 95, 80, 85],
    ['きりんさん', 75, 100, 92],
    ['わにさん', 100, 74, 50],
    ['らいおんさん', 98, 83, 85],
    ['ねこさん', 100, 100, 100],
    ['平均点', '=average(B3:B7)', '=average(C3:C7)', '=average(D3:D7)']
]

# ファイルの新規作成
# wbook = openpyxl.Workbook()
# sheet = wbook.active
# sheet.title = '成績表'
# wbook.save('./sample.xlsx')

try:
    # ファイルを開く
    wbook = openpyxl.load_workbook('./sample.xlsx')
except FileNotFoundError as e:
    print(e)
    exit()

# ワークシートは国語
sheet = wbook['成績表']

# 書き込む範囲の指定
data_cell_range = sheet['A2':'D8']

# 表タイトルを書き込む
sheet['A1'].value = title_txt
sheet.merge_cells('A1:D1')      # セルの結合
sheet['A1'].alignment = Alignment(horizontal="center")  # 中央揃え

# 表データを書き込む
for i in range(len(data_cell_range)):
    for j in range(len(data_cell_range[0])):
        data_cell_range[i][j].value = data[i][j]

# 罫線を引く
line_style = Side(style='thin', color='000000')
border = Border(top=line_style, bottom=line_style, left=line_style, right=line_style)
for row_number in range(2, 9):
    for col_number in range(1, 5):
        sheet.cell(row=row_number, column=col_number).border = border

# 表のタイトル、教科名、平均点を太字にする
font_style = Font(name='Noto Sans CJK JP', size=10, bold=True)
sheet['A1'].font = font_style
sheet['A8'].font = font_style
sheet['A2'].font = font_style
sheet['B2'].font = font_style
sheet['C2'].font = font_style
sheet['D2'].font = font_style

# 上書き保存
wbook.save('./sample.xlsx')

# ワークシートの読み込みと内容を表示
df = pd.read_excel('./sample.xlsx')
for row in df.values:
    print(row[0], row[1], row[2], row[3])

# ワークブックを閉じる
wbook.close()

保存したsample.xlsxの内容をPandasで読み込んで表示した内容は下記のとおりである。タイトルは表示されていない。nanはデータが欠損つまりデータがないということを表す。平均点については、本来なら数式で計算されたものが入るはずだが、nanとなっている。xlwingsを使ってExcelを直接コントロールすれば表示できるようだが、残念ながらLibreOffice Calcではダメなようである。

最終的にはExcelでの確認なので、そこまでこだわらなければこのままでよいと思うが、平均点の結果を表示させたい場合は、数式ではなくあらかじめ平均点を求めてセルに書き込むか、表示時に平均点を求めるという方法もある。

氏名 国語 数学 英語
うさぎさん 95 80 85
きりんさん 75 100 92
わにさん 100 74 50
らいおんさん 98 83 85
ねこさん 100 100 100
平均点 nan nan nan

コメントをツケておいたので、リストを見れば何をしているか理解できるはずである。どういうふうにすればいいか理解できればOKである。

ためしに平均点を求めるコードを書いてみた。冗長なところもあるが、そこのところは勘弁いただきたい。

import openpyxl


# 各教科の合計
sum = [0, 0, 0]

try:
    wbook = openpyxl.load_workbook('./sample.xlsx')
except FileNotFoundError as e:
    print(e)
    exit()

sheet = wbook['成績表']
maxrow = sheet.max_row

# 表のタイトルと教科名の表示
print(sheet['A1'].value)
print()
print('%-6s        %2s   %2s   %2s' % (
    sheet['A2'].value,
    sheet['B2'].value,
    sheet['C2'].value,
    sheet['D2'].value)
)
print('----------------------------------')

# 各教科ごとの点数の集計
for i in range(3, maxrow):
    # 合計の計算
    sum[0] += sheet['B' + str(i)].value
    sum[1] += sheet['C' + str(i)].value
    sum[2] += sheet['D' + str(i)].value

    # 氏名の長さ調整
    s = sheet['A' + str(i)].value
    name = s.ljust(6, '　')  # '　'は全角空白１文字

    # 氏名と教科点数の表示
    print('%-6s     %3d    %3d    %3d' % (
        name,
        sheet['B' + str(i)].value,
        sheet['C' + str(i)].value,
        sheet['D' + str(i)].value)
    )

print('----------------------------------')

# 各教科の平均点の表示
print(sheet['B2'].value, ':', sum[0] / (maxrow - 3))
print(sheet['C2'].value, ':', sum[1] / (maxrow - 3))
print(sheet['D2'].value, ':', sum[2] / (maxrow - 3))

# ワークブックを閉じる
wbook.close()

(maxrow – 3)の部分については、表の行数が８行であるため、sheet.max_rowで８が返される。このうち、点数が入っている行数は５行なので−３としている。実行結果は以下のとおりである。

アニマル・フォレスト中学校 - ３教科成績表

氏名            国語   数学   英語
----------------------------------
うさぎさん　      95     80     85
きりんさん　      75    100     92
わにさん　　     100     74     50
らいおんさん      98     83     85
ねこさん　　     100    100    100
----------------------------------
国語 : 93.6
数学 : 87.4
英語 : 82.4

openpyxlのインストール

簡単なデモコード

老猫こてつ.f90