【Python】XlsxWriter を用いて整形したデータを Excel ファイルに出力する（逆引き）

先日、Python で簡単なデータ解析をして、解析した結果を Excel ファイルで出力するアプリケーションを作成した。その際に、XlsxWriter を用いて、Excel ファイルの作成・編集などを行ったので、備忘録として残しておく。

XlsxWriter とは
ライブラリ逆引き
まとめ

XlsxWriter とは

xlsxwriter.readthedocs.io

XlsxWriter は Excel 2007+ XLSX ファイルを作成する Python モジュールである。（Python 2.7, 3.4+ に対応している。）

Python で Excel を扱うモジュールとしては、XlsxWriter とは別に、OpenPyXL が有名である。

XlsxWriter と OpenPyXL の違いとしては、以下が挙げられる。

module	新規作成	既存ファイル編集
XlsxWriter	○	×
OpenPyXL	○	○

XlsxWriter では、既存Excelファイルの読み込みができないため、既存Excelファイルを編集する場合、OpenPyPl を使う必要がある。

ただし、今回私は XlsxWriter を選択することにした。

その理由としては、Excel ファイルのヘッダーに画像を挿入したいことが挙げられる。OpenPyXL では、現状の仕様ではヘッダーに画像を挿入できないようであった。XlsxWriter では挿入可能だったため、XlsxWriter を使用することにした。

ライブラリ逆引き

ライブラリの使い方に関して、詳細を公式ドキュメントで確認して欲しい。ただ、実装にあたり、Excel のこの処理ってどのライブラリ使うの？って最初、私もなったので、今回、私が使用したライブラリを用途に応じて紹介するので、参考にして欲しい。

Excelファイル / シート作成

XlsxWriter の基本的な流れは、以下である。

Excel ファイルであるworkbookを作成する
1.で作成したworkbookに、ワークシートworksheetを追加する
ワークシートworksheet内を編集する
Excel ファイルを上書きして、保存する

import xlsxwriter
# Excelファイル作成
workbook = xlsxwriter.Workbook(file_name)
# ワークシート作成
worksheet = workbook.add_worksheet(sheet_name)
# ワークシート編集
worksheet.write('A1', 'Hello world') #A1セルにHello Worldを入力する
# Excelファイル保存
workbook.close()

import xlsxwriter
# Withを使うことで、close()する必要がなくなるのでオススメ
With xlsxwriter.Workbook(file_name) as workbook:
    # ワークシート作成
    worksheet = workbook.add_worksheet(sheet_name)
    # ワークシート編集
    worksheet.write('A1', 'Hello World') #A1セルにHello Worldを入力する

セルへの文字入力

セルに文字や式を入力する場合は、以下を使う。

worksheet.write(row, col, *args)

cell_format = workbook.add_format({'bold': True, 'italic': True})
worksheet.write(A1, 'Hello World', cell_format) 
worksheet.write(A2, '=SIN(PI()/4)', cell_format)

Excel には文字以外にも、数字や数式などがあり、フォーマットを指定した入力方法も存在する write_number(), write_formula()が、write()ではどのフォーマットでも対応できるので、基本的にはこれを使えばいいと思う。

セルのformat（書体、中央寄せ等）は、一度add_formatで Format Object を定義しておき使う必要がある。

セルを結合させて、そこに文字や式を入力する場合は、以下を使う。

worksheet.merge_range(first_row, first_col, last_row, last_col, data[, cell_format])

merge_format = workbook.add_format({
　　'font_name': 'メイリオ',
        'font_size': 8,
        'align': 'center',
        'valign': 'vcenter',
})
worksheet.merge_range('A1:C3', 'Hello World', merge_format)

また、Excel にデータを入力する場合は、基本的に大量の配列データである場合が多いと思う。その場合には、以下を使う。

横方向の場合：worksheet.write_row(row, col, data[, cell_format])
縦方向の場合：worksheet.write_column(row, col, data[, cell_format])

表のヘッダーなどを設定する場合は、下記で簡単に設定できる。

header = ('title1', 'title2', 'title3', 'title4', 'title5')
worksheet.write_row('A1', header)

また、大量のデータを表示する場合は、以下のようにすることで表示が可能となる。

data_list = [
    {'name': 'test1', 'price': 200, 'num': 10, 'desc': 'desc1'},
    {'name': 'test2', 'price': 400, 'num': 20, 'desc': 'desc2'},
    {'name': 'test3', 'price': 600, 'num': 30, 'desc': 'desc3'}
]
table_format = workbook.add_format({
    "align": "center",
    "valign": "vcenter",
    "text_wrap": True,
    "border": 1,
})
for row, data in enumerate(data_list):
    worksheet.write_row(f"A{2+row}", data, table_format)

セルの幅・高さ調整

セルの幅や高さを調整したい場合は、以下を使う。初期設定は、height: 15, width: 8.43となっている。

worksheet.set_row(0, 20)  # Set the height of Row 1 to 20.
worksheet.set_column('A:B', 20)  # Column A-B width set to 20.
worksheet.set_column('C:C', 40)  # Column C width set to 40.

高さに関しては、全体的に変更したい場合が多いと思う。その場合は、set_default_row(height)を使う。

テキストボックス挿入

テキストボックスを挿入したい場合は、以下を使う。

worksheet.insert_textbox(row, col, textbox[, options])

worksheet.insert_textbox(
    "A2",
    "Hello World",
    {
        "width": 400,
        "height": 50,
        "font": {"name": "メイリオ", "size": 14},
        "fill": {"none": True},
        "line": {"none": True},
    },
)

テキストボックスのサイズ、位置、フォントのフォーマットなどを指定することができる。

画像挿入

画像を挿入したい場合は、以下を使う。

worksheet.insert_image(row, col, image[, options])

worksheet.insert_image('B2', 'python.png')
worksheet.insert_image('B2', 'python.png', {'x_offset': 15, 'y_offset': 10})
worksheet.insert_image('B2', 'python.png', {'x_scale': 0.5, 'y_scale': 0.5})

基本的に、座標系は全て左上を原点として考える必要がある。x_offsetやy_offsetを設定しなければ、図形の左上がB2の左上の角と一致するように配置される。また、画像の大きさは、x_scaleやy_scaleで設定することができる。

ヘッダー/フッターの設定

ヘッダー/フッターを設定する場合は、以下を使う。

worksheet.set_header(
    "&L&G&R&G",
    {
        "image_left": "report-img/header_logo_left.png",
        "image_right": "report-img/header_logo_right.png",
    },
)
worksheet.set_footer(
    '&L&"游ゴシック Regular, Regular"Copyright TEST Inc. All Rights Reserved.' + '&R &P'
)

上記の設定をすることで、ヘッダーの両端にロゴ画像を挿入、フッターの左にはCopyright、右にはページ番号を挿入することができる。

印刷範囲設定

Excel では表の一部を選択して印刷したり、見栄えよく1枚に収めたり、印刷したい範囲だけを指定することができる。

worksheet.print_area(first_row, first_col, last_row, last_col)

worksheet1.print_area('A1:H20')     # Cells A1 to H20.
worksheet2.print_area(0, 0, 19, 7)  # The same as above.

範囲指定は、行数と列数での指定も可能であるが、アルファベットを用いた範囲指定の方が容易であるように思う。（行数と列数は、ゼロ始まりなので注意が必要。）

印刷ページ設定（拡大縮小印刷）

任意の範囲を印刷設定する際に、Excel で拡大縮小印刷の設定をすることがあると思う。設定方法は２つある。（同時に２つを設定することはできず、どちらかを設定することができる。）

任意の拡大縮小設定を行う
用紙のサイズに合わせて拡大縮小を行う

1. 任意の拡大縮小設定を行う

worksheet.set_print_scale(int)

worksheet1.set_print_scale(10)
worksheet2.set_print_scale(80)
worksheet3.set_print_scale(400)

拡大縮小できる範囲は、10 ~ 400 となっている。

2. 用紙のサイズに合わせて拡大縮小を行う

worksheet.fit_to_pages(width, height)

worksheet1.fit_to_pages(1, 1)  # Fit to 1x1 pages.
worksheet2.fit_to_pages(1, 2)  # Fit to 1x2 pages.
worksheet3.fit_to_pages(1, 0)  # 1 page wide and as long as necessary.

印刷タイトル設定

複数ページにわたる表を Excel で見やすくするために、表のヘッダーを印刷タイトルに設定したい場合があると思う。

worksheet.repeat_rows(first_row[, last_row])

worksheet1.repeat_rows(0)     # Repeat the first row.
worksheet2.repeat_rows(0, 1)  # Repeat the first two rows.

1行の場合は、first_row のみを設定すればいい。複数行の場合は、last_rowを設定する。（ただし、行数が「0」始まりとなっているので、実際に設定したい行数から - 1 した値を設定する必要があるので、注意が必要である。）

まとめ

Python で簡単に Excel へのデータ出力を行うことができた。セルの幅なども調整して、印刷できるところまできちんと設定できたので、とても便利であった。

それでは、ステキな開発ライフを。

7839

雑草魂エンジニアブログ