In [3]:
import numpy as np
import openpyxl as px
import pandas as pd
from openpyxl.styles import Font
from openpyxl.styles.colors import Color
from openpyxl.styles import Alignment
from openpyxl.styles import Border, Side
c = Color(theme=1, tint=0.0)
font = Font(
name='ＭＳ ゴシック',
charset=128,
family=3.0,
b=False,
i=False,
strike=None,
outline=None,
shadow=None,
condense=None,
color=c,
size=9,
)
font_red = Font(name='ＭＳ ゴシック',charset=128,size=6,color="FFAA0000")
al = Alignment(
shrinkToFit=None, textRotation=0, vertical='center', horizontal='center', indent=0.0, justifyLastLine=None, relativeIndent=0.0, wrapText=None, readingOrder=0.0
)
thin = Side(border_style="thin", color="000000")
double = Side(border_style="double", color="ff0000")
border_bottom = Border(top=None, left=None, right=None, bottom=thin)
In [2]:
# File Path
dir_template = "../app/organizer/content/programs"
path_temp = dir_template+"/template_track_1.xlsx"
dir_out = "./output"
# データ読み込み&グループ番号の付与
df = pd.read_csv("../demo_data/Cardinal_EntryFile/demo.csv")
group = 1
lane = 2
for i in range(0,20):
if lane == 8:
lane = int(2)
group += int(1)
df.ix[i,"group"] = group
df.ix[i,"order_lane"] = lane
print(df.shape)
df.head()
Out[2]:
In [3]:
class GR:
def __init__(self, comp, event, mark, name, club, year):
self.como = comp
self.event = event
self.mark = mark
self.name = name
self.club = club
self.year = year
gr_100m = GR("名阪戦", "100m", '11"34', u'阪大太郎', '大阪大', '2017')
print(gr_100m, gr_100m.name)
In [37]:
def style_track_sheet(ws, row):
# Params
# - ws: worksheet
# - row: The last row number of the page you edit (type=int)
# - col: The last col number of the page you edit (type=int)
# 行の高さの指定
for i in range(0,row):
ws.row_dimensions[i].height = 13
# 列の幅の指定
col_width = [("A", 4.0), ("B", 7.0), ("C", 11.0), ("D", 13.0), ("E", 7.0), ("F", 11.0),("G", 7.0), ("H", 8.0), ("I", 5.0), ("J", 9.0), ("K", 9.0)]
for t in col_width:
ws.column_dimensions[t[0]].width = t[1]
In [5]:
def cell_posi(row, col):
return col+str(row)
In [6]:
def write_cell(cell, value, font, al):
cell.value = value
cell.font = font
cell.alignment = al
In [7]:
def write_title(ws, gr):
# 種目名
ws.merge_cells("A1:C3")
write_cell(ws['A1'], gr.event, font, al)
ws['A1'].font = Font(size=24)
ws['A1'].alignment =Alignment(vertical='center', horizontal='center')
# 大会記録
write_cell(ws['D2'], '大会記録', font, al)
write_cell(ws['E2'], gr.mark, font, al) #記録
write_cell(ws['F2'], gr.name, font, al) #氏名
write_cell(ws['G2'], gr.club, font, al)#所属
write_cell(ws['H2'], gr.year, font, al) #年
In [26]:
def write_track_head(ws, row, group=True, wind=True):
# 1行目
if group:
write_cell(ws.cell(row=row, column=1), str(group)+u"組", font, al)
if wind:
ws.merge_cells(start_row=row,start_column=9,end_row=row,end_column=11)
write_cell(ws.cell(row=row, column=9), u"(風速＋･－ m/sec)", font, al) # Merged
if group or wind: # どちらかを書き込む場合は
row += 1
# 2行目
write_cell(ws.cell(row=row, column=1), u"ﾚｰﾝ", font, al)
write_cell(ws.cell(row=row, column=2), u"No.", font, al)
print(cell_posi(row+1, "C")+":"+cell_posi(row+1,"D"))
ws.merge_cells(cell_posi(row, "C")+":"+cell_posi(row,"D"))
write_cell(ws.cell(row=row, column=3), u"氏名", font, al) # Merged
write_cell(ws.cell(row=row, column=5), u"学年", font, al)
write_cell(ws.cell(row=row, column=6), u"所属", font, al)
write_cell(ws.cell(row=row, column=7), u"陸協", font, al)
write_cell(ws.cell(row=row, column=8), u"所属", font, al)
write_cell(ws.cell(row=row, column=9), u"順位", font, al)
ws.merge_cells(cell_posi(row, "J")+":"+cell_posi(row,"K"))
write_cell(ws.cell(row=row, column=10), u"記録", font, al)
# 次の空白行の行番号を返す
return row+1
In [27]:
def write_track_row(ws, row, entry, lane):
write_cell(ws.cell(row=row, column=1), lane, font, al)
write_cell(ws.cell(row=row, column=2), entry["bib"], font, al)
write_cell(ws.cell(row=row, column=3), entry["name"], font, al)
write_cell(ws.cell(row=row, column=4), entry["kana"], font, al)
write_cell(ws.cell(row=row, column=5), entry["grade"], font, al)
write_cell(ws.cell(row=row, column=6), entry["club"], font, al)
write_cell(ws.cell(row=row, column=7), entry["jaaf_branch"], font, al)
write_cell(ws.cell(row=row, column=8), entry["PB"], font, al)
# 記録欄
write_cell(ws.cell(row=row, column=9), "( )", font, al)
ws.cell(row=row, column=9).border = border_bottom
ws.cell(row=row, column=10).border = border_bottom
ws.cell(row=row, column=11).border = border_bottom
In [28]:
def write_group_lane8(ws, row, df, group=False, wind=True):
# Header
row = write_track_head(ws, row, group)
# エントリーの書き込み
for i in [0,1,2,3,4,5,6,7]:
print(row+i)
write_track_row(ws, row+i, df.ix[i,:].to_dict(), i+1)
In [29]:
# 長距離&補欠用
def write_group_laneN(ws, row, df, group=False, wind=True):
# Params
# - ws: worksheet
# - row: 書き込み開始行番号
# - df: dataFrame
# Header
row = write_track_head(ws, row, group=group, wind=wind)
# エントリーの書き込み
c = 0
for i in np.arange(0, len(df), 1):
print("laneN: ", i)
write_track_row(ws, row+i, df.ix[i,:].to_dict(), i+1)
c += 1
# Finish
print("> Write_group_laneN: write ", str(c), " entries")
In [35]:
# Write !!
# Open Excel file
#wb = px.load_workbook(path_temp)
wb = px.Workbook()
ws = wb.active
# シートの 作成
#ws_source = wb.get_sheet_by_name('track_white')
#ws = wb.copy_worksheet(ws_source)
#ws = wb.create_sheet('男子100m', 0)
ws.title = '男子100m'
print(wb.get_sheet_names())
write_head(ws, gr_100m)
# 対校
ws.merge_cells("A5:C5")
write_cell(ws["A5"], "<対校の部>", font, al)
ws['A5'].alignment =Alignment(vertical='center', horizontal='left')
write_group_lane8(ws, 5, df, False)
# 補欠
ws.merge_cells("A16:C16")
write_cell(ws["A16"], "< 補欠>", font, al)
ws['A16'].alignment =Alignment(vertical='center', horizontal='left')
write_group_laneN(ws, 17, df[:3], False, False)
# OP
ws.merge_cells("A22:C22")
write_cell(ws["A22"], "<オープンの部>", font, al)
ws['A22'].alignment =Alignment(vertical='center', horizontal='left')
write_group_lane8(ws, 23, df, 1)
write_group_lane8(ws, 35, df, 2)
write_group_lane8(ws, 47, df, 3)
In [36]:
# Save
style_track_sheet(ws, 56)
wb.save(dir_out+"/test3.xlsx")
In [21]:
ws["I16"].value
Out[21]:
In [ ]:
print(font)
In [ ]:
ws['A16'].font
In [ ]:
#ws['C8'].font = font
ws['A5'].font
In [ ]:
print(ws['C5'].alignment)
print(ws['C7'].alignment)