OpenPyXLでExcelファイルに書き込む その2

今回はセルに値を記入、追加、削除、移動させる方法を書きます。

前回
OpenPyXLでExcelファイルを読み取る - よちよちpython
ではシートの作成や追加、削除などを行いました。

参考

openpyxl公式

作業環境

Androidスマホ
Qpython(python3.6)
QPyNotebook
Android版のExcel
Googleスプレッドシート

スマホとQpythonを使っている理由は特にありません。 いま手元にこれしかなかった為。 スマホで動くかどうかの確認を兼ねます。

---

目次

---

準備

モジュールでOpenPyXLを使うのでインストールがまだならターミナルから行います。

$ pip install openpyxl

Qpythonの場合は、起動画面から「QPYPI」→「QPYPI CLIENT」の順にタップし、同じように

-->pip install openpyxl

いまからExcelファイルを新規作成しますが、フォルダのパスを指定していません。 従ってExcelファイルはカレントディレクトリに自動的に保存されます。

一応カレントディレクトリを確認します。

import os

print(os.getcwd())

/storage/emulated/0/qpython/notebooks

QpythonのNotebookを使うとデフォルトで上記の場所がカレントディレクトリのようです。  

全然必要ないが、Pythonのバージョンを確認しとこ。

import sys
print(sys.version)

3.6.6 (qpyc:3.6.6, Jul 26 2018, 03:54:22) [BUILD WITH QPY3-TOOLCHAIN (https://github.com/qpython-android) ]

---

セルに値を書き込む

セルA1に文字を記入します。

import openpyxl

# ワークブック作成
wb = openpyxl.Workbook()

# ワークシート選択(1枚しか出来てないけど)
sheet = wb.worksheets[0]

# 新規Excelファイルの保存名(同名があれば上書きされる)
file_name = "sample.xlsx"

# セルA1に値を代入(A1でもa1でもok)
sheet["a1"] = "test"

# ここまでの作業を保存
wb.save(file_name)

実行しました。
Excelアプリで見てみます。

セルA1に文字を入れることが出来ました。   上の画像にあるファイル名の横に「読み取り専用」と表示されているのは、マイクロソフトのアカウントにログインせずにスマホ版Excelを開いた為のようです。 この状態だと直接アプリで編集しようとしても出来ません。 だが何故かPythonからは問題なく書き込めている。

尚、セルの値はExcelと同じように数式を入れることができるようです。

追記

続けてB1とC1に代入してみます。

# セルに文字を追記。
sheet["B1"] = "test2"
sheet["C1"] = "test3"

# こんな書き方もできる。
sheet.cell(column=4, row=1, value="test4")

# または
sheet.cell(row=1, column=5).value = "test5"

# 保存
wb.save(file_name)

アプリで確認します。

バッチリ追記されています。 「追記」としていますが、単にコードを上から順番に実行しているだけです。

column(列)とrow(行)の番号は1から始まります。
行と列の指定順番を入れ換えても大丈夫なようです。

---

ブックを読み込んで追記

複数のセルに値の入った既存のシートに追記する場合は先にブックを読み込み、新たな値を追加します。

A2に「テスト」と追記します。

# ワークブックの読み込み
wb = openpyxl.load_workbook(file_name)

# 読み込んだブックのシート選択
sheet = wb.worksheets[0]

# 読み込んだシートのセルA2に値を代入
sheet["A2"] = "テスト"

# 保存
wb.save(file_name)

セルA2に追記されました。 既存のExcelファイルを書き換える時はこのように一旦ブックを読み込んで値を入れていきます。

---

セルの値の変更

A2の値「テスト」を「書換テスト」に変更してみます。

# 既存ブックの読み込みとシート選択
wb = openpyxl.load_workbook(file_name)
sheet = wb.worksheets[0]

# セルA2に値を代入(変更)
sheet["A2"] = "書換テスト"

# 保存
wb.save(file_name)

上手く書き換えできました。
値が入ったセルに別の値を入れると新しいものに置き換わります。

追記、書換の失敗例

追記か書換をするつもりで、上のコードの二行目の

wb = openpyxl.load_workbook(file_name)

を、

wb = openpyxl.Workbook()

と誤って書いてしまうと一体どうなってしまうでしょうか。

file_name = "sample.xlsx"

# ワークブックの読み込み…のつもりで新規作成してしまうミス
wb = openpyxl.Workbook()

# シート選択
sheet = wb.worksheets[0]

# A2に値を追記または変更…のつもり
sheet["A2"] = "テスト"

# 保存…A2を追記したつもり
wb.save(file_name)

シートのデータが全部消え、セルA2のみ記入されてしまいました。

A2のみ値を入れた新規ブックを既存ファイルに上書き保存した格好。
上書き保存前のバージョンへの戻し方を知らなければ目も当てられません。  

PC用のExcelでは保存後に閉じても元に戻す方法を説明したWebページが沢山見つかりますが、Android版での説明は探しきれませんでした。
触るなキケン…

---

複数セルに行ごと入力

一旦新規ブックを作成し、A1からC5までの3行5列の範囲に値を1度に入れてみます。

appendを使うとできる。

# ブックの読み込みとシート選択
wb = openpyxl.Workbook()
sheet = wb.worksheets[0]

# 行ごとのデータ
data = [
        ["受注日","お客様番号","お客様名","商品番号","商品名","数量"],
        ["20190829","001","八丁堀の政","1873","国芳プリント手拭い","2"],
        ["20190829","794","博多のお銀","9494","長崎鼈甲櫛","1"]
]

# 行ごとに取り出し
for row in data:
    sheet.append(row)
    
# 忘れず保存
wb.save(file_name)

↑はExcelで表示

↓ はGoogleスプレッドシートの表示

appendを使うと最終行の下に行が追加できる。

# ブックの読み込みと先頭シートの読み込み
wb = openpyxl.load_workbook(file_name)
sheet = wb.worksheets[0]

# 行データを追加と保存
sheet.append(["20190829","2929","播磨の猪飼","104","ぼたん","44"])
wb.save(file_name)              

4行目に新しくレコードを追加できた。

---

セルの値の削除

セルの値を削除する場合は、値を''か"",またはNoneとして代入すれば良さそうです。 セルA4の値を消してみます。

sheet["A4"] = ""
wb.save(file_name)

消えました。 今度はセルA2の値を削除してみます。

sheet["a2"] = None
wb.save(file_name)

こちらの方法でも消えました。

---

行や列の挿入

例えば2行目に行を挿入したいなら

sheet.insert_rows(2)
wb.save(file_name)

3列目に列を挿入するなら

sheet.insert_cols(3)
wb.save(file_name)

---

行や列を削除

A列からC列までの3列を削除するなら、

sheet.delete_cols(1,3)
wb.save(file_name)

5行目を削除するなら、

sheet.delete_rows(5)
wb.save(file_name)

---

範囲の移動

いまA1:F4にデータが入っています。
これを下に1行、右に1列だけ移動させてみます。

move_rangeを使います。

sheet.move_range("A1:F4", rows=1, cols=1)
wb.save(file_name)

移動できました。

左と上に動かす場合は数字にマイナスをつければOK。

今回はここまで。

次回につづきます。

目次にもどる

---