元セブ島在住のエンジニアの僕「Tommy」が、プログラミング・英語・セブ事情を発信するブログ

Tommy's blog

【Python】Excel(エクセル)を操作する【→業務効率UP】

7月 12, 2019

プログラム女子
会社でデータの集計を任されたんだけど、excelファイルが複数ある場合ってマクロ使えないよね...
ファイルが1つの場合は、excelマクロを組んで処理できるけど、ファイルが複数ある場合は、プログラムで外からファイルを読み込む方法が良いね。今回は、excelをPythonで操作する方法を説明するね。
Tommy

この記事で分かること

  • PythonプログラムでExcelファイルを自動で作成する方法が分かる
  • PythonプログラムでExcelからデータを読み込む方法が分かる

PythonでExcelを操作する概要

現在のオフィス環境では、データの集計やまとめにはExcel形式のファイルでやり取りされることが多くあります。また、企画内容や仕様内容をExcelで考えを纏める人も少なくありません。

なので、社内環境ではまだまだExcel使用者が根強く残っています。このような環境で、あるシステムのアウトプットとしてのExcelを扱う為には、いづれかのプログラムによってExcelファイルを操作できる必要があります。

この記事では、Pythonを使ったExcelファイルの作成方法と、Excelファイルからのデータ読み取りを方法を説明します。

PythonでExcelを操作する前提環境

Excelを操作する前提の環境は、下の記事で説明している私の環境と同じ環境で確認しています。

レンタルサーバなどのLinuxサーバを使っている場合は、下の記事でプログラミングする環境の作り方を説明しています。もし、まだプログラムする環境ができていない方は、先にこちらの記事を読んで、プログラミングできる環境をつくることをおすすめします。

  • openpyxlライブラリをインストール
$ pip freeze | grep openpyxl
openpyxl==2.6.1

PythonでExcelを操作するサンプルプログラム

ここでは、Pythonを使ってexcelを操作するためのサンプルプログラムを説明します。サンプルプログラムでは大きく次の2つの処理を説明します。

  • Excelを作成する
  • Excelからデータを読み込む

Excelファイルを作成する

「Excelファイルの作成」には、具体的には次のような8つの処理が必要になります。

  1. Excelのワークブックオブジェクトを作成する関数の定義
  2. Excelのワークシートを取得する関数の定義
  3. Excelのセルを編集する関数の定義
  4. Excelのシートを追加する関数の定義
  5. Excelのシート名を変更する関数の定義
  6. Excelのワークブックを保存する関数の定義
  7. Excelのセルの幅を調整する関数の定義
  8. 定義した関数の実行

以下に8つの処理内容をプログラムを使って具体的に説明します。

1.Excelのワークブックオブジェクトを作成する関数の定義

下に記載しているcreate_workbook()関数では、Excel操作に必要なExcelワークブックオブジェクトを作成します。返り値(戻り値)にwb(ワークブックオブジェクト)を返すことによって、このcreate_excel()関数の呼び出し元でExcelワークブックを扱えるようにします。

引数の「default_sheet」はシート名が入力されます。もし特に指定がない場合はシート名として「default」という名前のシートが作成されます。

# create excel work book(return work book object)
def create_workbook(default_sheet='default'):

	# Workbookインスタンスを作成する
	wb = openpyxl.Workbook()
	ws = wb.active

	# ワークシートのタイトルは"Sheet1"を指定する
	ws.title = default_sheet

	return wb

  1. 関数「create_workbook」を定義宣言する。
  2. 変数「wb」をライブラリ「openpyxl」のメンバ関数「Workbook」からの戻り値で初期化する。
  3. 変数「ws」を変数「wb」のメンバ変数「active」の値で初期化する。
  4. 変数「ws」のメンバ変数「title」に変数「default_sheet」の値を代入する。
  5. 変数「wb」の値を戻り値として返却する。

2.Excelのワークシートを取得する関数の定義

下のget_worksheet()関数では、Excelのワークシートオブジェクトを取得します。引数にwork_book(ワークブックオブジェクト)と、sheet_name(シート名)をとります。

もし、「sheet_name」が入力されていない場合は、現在のアクティブなシートオブジェクトを返却し、sheet_nameが入力されている場合は、その名前のシートオブジェクトを返却します。

# get sheet object
def get_worksheet(work_book,sheet_name=None):

	if sheet_name is None:
		ws = work_book.active
	else:
		ws = work_book[sheet_name]

	return ws

  1. 関数「get_worksheet」を定義宣言する。
  2. 変数「sheet_name」が値「None」の場合は次の処理を実行する。
  3. 変数「ws」をワークブックオブジェクト「work_book」のメンバ変数「active」の値で初期化する。
  4. 変数「sheet_name」が値「None」以外の場合は次の処理を実行する。
  5. 変数「ws」を名前「sheet_name」のワークブックオブジェクト「work_book」で初期化する。
  6. 変数「ws」を戻り値として返却する。

3.Excelのセルを編集する関数の定義

下のedit_cell()では、excelのセルを編集します。引数に、次の4つのパラメータを入力します。

  • work_sheet(ワークシートオブジェクト)
  • row_p(列番号)
  • col_p(行番号)
  • contents(セルに入れる文字や値)

目的の行列の番号で指定されたセルの内容をcontentsに書き換えた後、wc(ワークセルオブジェクト)を返り値で返却します。

※関数「adjust_coll」は、セルの幅を調整するための関数です。関数「adjust_coll」の説明は後ほど行います。

# edit cell value
def edit_cell(work_sheet,row_p,col_p,contents):

	wc = work_sheet.cell(row = row_p, column = col_p, value = contents)

	adjust_col(work_sheet)

	return wc

  1. 関数「edit_cell」を定義宣言する。
  2. 変数「wc」を引数から得られるワークシートオブジェクト「work_sheet」のメンバ関数「cell」の戻り値で初期化する。
  3. 関数「adjust_col」を呼び出す。
  4. 変数「wc」の値を戻り値として返却する。

4.Excelのシートを追加する関数の定義

下のadd_sheet()関数では、excelシートを追加します。引数にwork_book(ワークブックオブジェクト)とsheet_name(シート名)を入力します。sheet_nameで指定された名前のシートがwork_bookに追加されます。戻り値として、ワークシートオブジェクトを返却します。

# add sheet(retrurn additinal sheet object)
def add_sheet(work_book,sheet_name):

	work_sheet = work_book.create_sheet(title=sheet_name)
	return work_sheet

  1. 関数「add_sheet」を定義宣言する。
  2. 変数「work_sheet」をワークブックオブジェクト「work_book」のメンバ関数「create_sheet」からの戻り値で初期化する。
  3. 変数「work_sheet」の値を戻り値として返却する。

5.Excelのシート名を変更する関数の定義

関数change_sheet_name()では、excelのシート名を変更します。引数に次の3つのパラメータを入力します。

  • work_book(ワークブックオブジェクト)
  • org_name(現在のシート名)
  • dst_name(変更するシート名)

動作としては、work_bookの中にあるorg_nameのシート名をdst_nameに変更します。この関数の返り値はありません。

# change name of sheet
def change_sheet_name(work_book,org_name,dst_name):

	work_sheet = work_book[org_name]
	work_sheet.title = dst_name

  1. 関数「change_sheet_name」の定義宣言する。
  2. 変数「work_sheet」を名前「org_name」のワークブックオブジェクト「work_book」で初期化する。
  3. 変数「work_sheet」のメンバ変数「title」に変数「dst_name」の値を代入する。

6.Excelのワークブックを保存する関数の定義

次の関数save_workbook()では、excelのワークブック(エクセルファイル)を保存します。

引数に

  • work_book(ワークブックオブジェクト)
  • file_name(保存ファイル名)
  • save_path(保存する場所)

を入力すると、work_bookをfile_nameのファイル名で、save_pathの場所に保存されます。

もし、save_pathが入力されなかった場合は、現在の場所にファイルを保存します。このsave_file()関数には、返り値はありません。

# save excel files
def save_workbook(work_book,file_name,save_path=''):

	work_book.save(file_name)

  1. 関数「save_workbook」を定義宣言する。
  2. 引数で得られたワークブックオブジェクト「work_book」のメンバ関数「save」を呼び出す。

7.Excelのセルの幅を調整する関数の定義

下のadjust_col()関数では、セルの幅を自動調整します。引数にws(ワークシートオブジェクト)を取り、wsで指定されたワークシートのcellに入力されている文字や数字の長さに合わせてCellの幅を調節します。

# adjust cell width
def adjust_col(ws):

	for col in ws.columns:
		 max_length = 0
		 column = col[0].column # Get the column name
		 column = chr(ord("A") - 1 + column)

		 for cell in col:
			 try: # Necessary to avoid error on empty cells
				 if len(str(cell.value)) > max_length:
					 max_length = len(cell.value)
			 except:
				 pass

		 adjusted_width = (max_length + 2) * 1.1
		 ws.column_dimensions[column].width = adjusted_width

  1. 関数「adjust_col」を定義宣言する。
  2. ワークシートオブジェクト「ws」のメンバ変数「columns」を1つずつ変数「col」に格納しながら、次の処理を繰り返す。
    1. 変数「max_length」を値「0」で初期化する。
    2. 変数「column」を0番目の変数「col」のメンバ変数「column」で初期化する。
    3. 変数「column」に変数「column」で指定されている列番号のアスキー番号を格納する。
    4. 「col」を1つずつ変数「cell」に格納しながら次の処理を実行する。
      1. 次の処理にエラーが発生するまで実行する。
        1. セルオブジェクト「cell」のメンバ変数「value」 が変数「max_length」よりも大きい場合は次の処理を実行する。
        2. 変数「max_length」 セルオブジェクト「cell」のメンバ変数「value」の値を代入する。
    5. 変数「adjusted_width」 を値「max_length」に2を足したあとに1.1倍した値で初期化する。
    6. 変数「column」番目のオブジェクト「ws.column_dimensions」のメンバ変数「width」 に変数「adjusted_width」の値を代入する。

8.定義した関数の実行

ここでは、上記で定義した関数の呼び出しを行います。ソースコードの処理の流れは、下記の通りです。

  • Excelワークシートオブジェクトを作成
  • ワークシートオブジェクトを取得(はじめのシート名は「default」になります)
  • 1行1列目のセルから2行2列目のセルに文字列を書き込み
  • Excelシートを追加
  • セルに文字列を書き込み
  • シート名を変更
  • ファイルを保存
# 使用するライブラリをインポートする

import openpyxl
from openpyxl.styles.fonts import Font
from openpyxl.styles.alignment import Alignment
from openpyxl.styles.borders import Border, Side
from openpyxl.chart import BarChart

if __name__ == '__main__':

	# set file name as 'test.xlsx'
	filename = 'test.xlsx'

	# create excel work book for writing
	w_wb = create_workbook()

	# get work sheet
	w_ws = get_worksheet(work_book=w_wb,sheet_name=None)

	# edit cell
	edit_cell(work_sheet=w_ws,row_p=1,col_p=1,contents='A1')
	edit_cell(work_sheet=w_ws,row_p=1,col_p=2,contents='B1')
	edit_cell(work_sheet=w_ws,row_p=2,col_p=1,contents='A2')
	edit_cell(work_sheet=w_ws,row_p=2,col_p=2,contents='B2')

	# add work sheet
	n_ws = add_sheet(work_book=w_wb,sheet_name='new sheet')

	# edit cell
	edit_cell(work_sheet=n_ws,row_p=1,col_p=1,contents='BBBB')

	# change sheet name
	change_sheet_name(work_book=w_wb,org_name='default',dst_name='changed sheet')

	# save work book
	save_workbook(work_book=w_wb,file_name=filename,save_path='')

  1. ライブラリ「openpyxl」をインポートする。
  2. ライブラリ「openpyxl.styles.fonts」からモジュール「Font」をインポートする。
  3. ライブラリ「openpyxl.styles.alignment」から「Alignment」をインポートする。
  4. ライブラリ「openpyxl.styles.borders」からモジュール「Border」「Side」をインポートする。
  5. ライブラリ「openpyxl.chart」から「BarChart」をインポートする。
  6. 変数「filename」を文字列「test.xlsx」で初期化する。
  7. 変数「w_wb」を関数「create_workbook」からの戻り値で初期化する。
  8. 変数「w_ws」を関数「get_worksheet」からの戻り値で初期化する。
  9. 関数「edit_cell」を呼び出す。
  10. 関数「edit_cell」を呼び出す。
  11. 関数「edit_cell」を呼び出す。
  12. 関数「edit_cell」を呼び出す。
  13. 変数「n_ws」を関数「add_sheet」からの戻り値で初期化する。
  14. 関数「edit_cell」を呼び出す
  15. 関数「change_sheet_name」を呼び出す。
  16. 関数「save_workbook」を呼び出す

Excelからデータを読み込む

次に既に作成されているExcelからデータを読み込む方法を説明します。データを読み込むために、具体的には次の3つの処理が必要になります。

  1. Excelのワークブックを読み込む関数の定義
  2. Excelのセルのデータを読み込む関数の定義
  3. 定義した関数の実行

上記の3つの処理をサンプルプログラムを使って説明します。

1.Excelのワークブックを読み込む関数の定義

下記のload_workbook()では、既存のExcelファイルのワークブックオブジェクトの読み込みを行います。引数には既に作成されているExcelのファイル名(file_name)を入力し、そのファイルのワークブックオブジェクトを返却します。

# load exsiting work book
def load_workbook(file_name):

	r_wb = openpyxl.load_workbook(filename)
	return r_wb

  1. 関数「load_workbook」を定義宣言する。
  2. 変数「r_wb」を関数「openpyxl.load_workbook」の戻り値で初期化する。
  3. 変数「r_wb」の値を戻り値として返却する。

2.Excelのセルのデータを読み込む関数の定義

read_cells()では、セルのデータを読み込みます。引数にワークシートオブジェクト(work_sheet)を取り、指定されたワークシートのセルの値を2次元(シートにある縦と横で)の配列として返却します。

# read cells
def read_cells(work_sheet):

	extracted_cells=[]
	for column in work_sheet.columns:
		temp_array = []
		for cell in column:
			temp_array.append(cell.value)
		extracted_cells.append(temp_array)

	return extracted_cells

  1. 関数「read_cells」を定義宣言する。
  2. 変数「extracted_cells」を空のリストで初期化する。
  3. 変数「work_sheet.columns」の値を1つずつ「column」に格納しながら次の処理を繰り返す。
    1. 変数「temp_array」を空のリストで初期化する。
    2. 「column」のすべての要素を1つずつ「cell」に格納しながら次の処理を繰り返す。
      1. 変数「temp_array」に変数「cell」のメンバ変数「.value」の値を加える。
    3. 変数「extracted_cells」に変数「temp_array」の値を加える。
  4. 変数「extracted_cells」の値を戻り値として返却する。

3.定義した関数の実行

ここでは、上記で定義した関数の呼び出しを行います。ソースコードの処理の流れは、下記の通りです。

  • Excelのワークブックオブジェクトを作成します。
  • データを取得するシートのシートオブジェクトを取得します。
  • セルのデータを取得します。
if __name__ == '__main__':

	# load excel work book for reading
	r_wb = load_workbook(file_name=filename)

	# get work sheet
	r_ws = get_worksheet(work_book=r_wb,sheet_name='changed sheet')

	# read cells
	extracted_cells = read_cells(work_sheet=r_ws)

	print(extracted_cells)

  1. 変数「r_wb」を関数「load_workbook」からの戻り値で初期化する。
  2. 変数「r_ws」を関数「 get_worksheet」からの戻り値で初期化する。
  3. 変数「extracted_cells」を関数「read_cells」からの戻り値で初期化する。
  4. 変数「extracted_cells」の内容を表示する。

Pythonで行うその他のofficeの操作方法

Excel以外にも、officeにはそれぞれ異なる操作方法があります。

下記の記事では、普段の業務で使える、office(パワーポイント、エクセル、ワード)のPythonのプログラムによる操作方法を説明しています。

officeそれぞれの操作方法を、下記の記事を参考に、ご自身のプログラムを作成してみてください。