Skip to content

Instantly share code, notes, and snippets.

@yubessy
Last active Aug 29, 2015
Embed
What would you like to do?
PythonでExcelファイルを作成 + similarity matrix ref: http://qiita.com/yubessy/items/a39a565cdc2c5872cce9
$ pip install xlsxwriter
# -*- coding: utf-8 -*-
import xlsxwriter
from xlsxwriter.utility import xl_rowcol_to_cell, xl_range_abs
# ワークブックとワークシートを作成
wb = xlsxwriter.Workbook("sim_matrix.xlsx")
ws = wb.add_worksheet("similarity matrix")
# データセルのサイズを設定
for i in range(5):
ws.set_row(i, 40.5)
ws.set_column(i, i, 6.0)
# データ入力
data = [[1.00, 0.15, 0.09, 0.01, 0.02],
[0.15, 1.00, 0.12, 0.06, 0.03],
[0.09, 0.12, 1.00, 0.08, 0.01],
[0.01, 0.06, 0.08, 1.00, 0.05],
[0.02, 0.03, 0.01, 0.05, 1.00]]
for i in range(5):
for j in range(5):
ws.write_number(i, j, data[i][j])
# 配列数式を使って対角要素を除く最大値・最小値を求める
data_field = xl_range_abs(0, 0, 4, 4)
ws.write_string(0, 6, "max sim")
max_formula = '{{=MAX(IF(ROW({0})=COLUMN({0}),"",{0}))}}'.format(data_field)
ws.write_formula(0, 7, max_formula)
ws.write_string(1, 6, "min sim")
min_formula = '{{=MIN(IF(ROW({0})=COLUMN({0}),"",{0}))}}'.format(data_field)
ws.write_formula(1, 7, min_formula)
# 条件付き書式を使ってカラースケールを適用
max_cell = xl_rowcol_to_cell(0, 7, row_abs=True, col_abs=True)
min_cell = xl_rowcol_to_cell(1, 7, row_abs=True, col_abs=True)
props = {
"type": "2_color_scale",
"max_color": "#FF6347", # tomato
"min_color": "#FFFFE0", # lightyellow
"max_type": "formula",
"min_type": "formula",
"max_value": max_cell,
"min_value": min_cell}
ws.conditional_format(0, 0, 4, 4, props)
# 書き込み
wb.close()
# 新しいファイルとワークシートを作成
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()
# 列Aの幅を変更
worksheet.set_column('A:A', 20)
# 太字にする書式を追加
bold = workbook.add_format({'bold': True})
# テキストの書き込み
worksheet.write('A1', 'Hello')
# テキストの書き込み・書式の適用
worksheet.write('A2', 'World', bold)
# 数値の書き込み(セル番地を数字で指定)
worksheet.write(2, 0, 123)
worksheet.write(3, 0, 123.456)
# 画像を挿入
worksheet.insert_image('B5', 'logo.png')
workbook.close()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment