Pythonで大量のExcelファイルをいじった話

最近「100個のテキストファイルから一部を取り出して100個のExcelファイルを作る」とか、「100個のExcelファイルの一部のセルを1つのExcelファイルにコピペする」とかいう感じの作業をする必要があって、手作業では面倒すぎるのでPythonのopenpyxlを使ってみました。今回はその時のメモです。

openpyxlは、Pythonからxlsxファイルを読み書きするためのパッケージです（xlsファイルは読み書きできないはず）。Excelを操作するわけではないので注意しないといけない点があるものの、逆にExcelなしでExcelファイルをいじることができます。

準備

Pythonとopenpyxlのインストール

Linuxだと標準でPythonが入っていることが多いのですが、今回はWindows環境で作業する必要がありました。WindowsでPythonを動かす方法はいくつかあるみたいですが、自分はAnacondaをインストールしました。この場合、普通にインストールするとopenpyxlも一緒にインストールされます。

基本的な使い方

ファイルの読み込みと書き込み

インポートは次のようにしています。

import openpyxl as px

ファイルの読み込みは

wb = px.load_workbook("src.xlsx")

書き込みは

wb.save("dst.xlsx")

みたいな感じで行います。新規ファイルを作成する場合は

wb = px.Workbook()

らしいです。

シートの操作

シートを作成するときは

ws = wb.create_sheet(title="シート名")

とします。既存のシートを名前で選択して操作するときは

ws = wb["シート名"]

現在アクティブなシートを操作するときは

ws = wb.active

とします。シートの名前は

ws.title = "シート名"

で変更できます。

セルの読み込みと書き込み

セルの中身の読み込みは

data = ws['A1'].value

書き込みは

ws['A2'] = data

でできます。数式も書けます。

ws['A3'] = "=1+2"

簡単！

または

data = ws.cell(row=1, column=1).value
ws.cell(row=2, column=1).value = data

でも読み書きできます。ループではこちらの方が便利かも。

その他

他にもフォントや罫線を変えたり、セルを結合したりできるみたいなのですが、自分の用途ではExcelであらかじめ見た目だけを整えたxlsxファイルを作って、 shutil.copy() でコピーしてから値を変える方が楽だったので試していません。

はまったこと

罫線が壊れる！

結合されたセルに罫線があるExcelファイルを読み込んだ場合、openpyxlでいじってから保存すると罫線が壊れます。既知の問題らしく、海外でMonkeyPatchが紹介されていました。下記はそのままのコピペです。この記述をソースのどこかに書いておきます。 追記：最新版では、_merged_cellsをmerged_cellsに、self._merged_cells.appendをself.merged_cells.addに変更しないといけないようです。

from itertools import product
import types
import openpyxl
from openpyxl import worksheet
from openpyxl.utils import range_boundaries

def patch_worksheet():
    """This monkeypatches Worksheet.merge_cells to remove cell deletion bug
    https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working
    Thank you to Sergey Pikhovkin for the fix
    """

    def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
        """ Set merge on a cell range.  Range is a cell range (e.g. A1:E1)
        This is monkeypatched to remove cell deletion bug
        https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working
        """
        if not range_string and not all((start_row, start_column, end_row, end_column)):
            msg = "You have to provide a value either for 'coordinate' or for\
            'start_row', 'start_column', 'end_row' *and* 'end_column'"
            raise ValueError(msg)
        elif not range_string:
            range_string = '%s%s:%s%s' % (get_column_letter(start_column),
                                          start_row,
                                          get_column_letter(end_column),
                                          end_row)
        elif ":" not in range_string:
            if COORD_RE.match(range_string):
                return  # Single cell, do nothing
            raise ValueError("Range must be a cell range (e.g. A1:E1)")
        else:
            range_string = range_string.replace('$', '')

        if range_string not in self._merged_cells:
            self._merged_cells.append(range_string)


        # The following is removed by this monkeypatch:

        # min_col, min_row, max_col, max_row = range_boundaries(range_string)
        # rows = range(min_row, max_row+1)
        # cols = range(min_col, max_col+1)
        # cells = product(rows, cols)

        # all but the top-left cell are removed
        #for c in islice(cells, 1, None):
            #if c in self._cells:
                #del self._cells[c]

    # Apply monkey patch
    worksheet.Worksheet.merge_cells = merge_cells
patch_worksheet()

値を読みたいのに数式が読まれる！

openpyxlではvalueという属性でデータにアクセスできますが、これでデータを読み出すとExcelでいうところの「値」ではなく「数式」が読み出されます。値を読み出す場合は、ワークブックを開くときに data_only オプションを指定します。

wb = px.load_workbook(filename, data_only=True)

ちなみに、Excelでxlsxファイルを保存すると、数式のセルは数式と値の両方がファイルに書き込まれますが、openpyxlでは数式しか書き込まれません。もしopenpyxlで数式を書き込んだファイルからそのまま値を読み出すとNoneが読み出されます。そのため、openpyxlで数式を書いたファイルから値を読み出すには、一度Excelでファイルを開いて保存する必要があります。