Last active
August 29, 2015 14:00
-
-
Save yubessy/11497306 to your computer and use it in GitHub Desktop.
PythonでExcelファイルを作成 + similarity matrix ref: http://qiita.com/yubessy/items/a39a565cdc2c5872cce9
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$ pip install xlsxwriter |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# -*- 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() |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 新しいファイルとワークシートを作成 | |
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