In [2]:
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,
)
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)
border_all = Border(top=thin, left=thin, right=thin, bottom=thin)
border_default = Border(
left=Side(border_style=None, color='FF000000'),
right=Side(border_style=None, color='FF000000'),
top=Side(border_style=None, color='FF000000'),
bottom=Side(border_style=None, color='FF000000'),
diagonal=Side(border_style=None, color='FF000000'),
outline=Side(border_style=None, color='FF000000'),
vertical=Side(border_style=None, color='FF000000'),
horizontal=Side(border_style=None, color='FF000000')
)
In [3]:
# File Path
dir_template = "../app/organizer/content/programs"
path_temp = dir_template+"/template_track_1.xlsx"
dir_out = "./output"
# 大会記録
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_HJ = GR("名阪戦", "走高跳", '2m34', u'阪大太郎', '大阪大', '2017')
print(gr_HJ, gr_HJ.name)
# データ読み込み&グループ番号の付与
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[3]:
In [4]:
def style_LJTJ_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
# 列の幅の指定
# [E-AF] = 1pt
col_width = [("A", 3.0), ("B", 6.0), ("C", 10.0), ("D", 10.5), ("E", 5.0), ("N", 4.0)]
col_width_2 = 6.5
for t in col_width:
ws.column_dimensions[t[0]].width = t[1]
for t in [chr(i) for i in range(65+5,65+13)]: # F - M
ws.column_dimensions[t].width = col_width_2
[chr(i) for i in range(65+5,65+13)]
Out[4]:
In [5]:
def cell_posi(row, col):
return col+str(row)
In [6]:
def write_cell(cell, value, font=font, al=al, border=border_default):
cell.value = value
cell.font = font
cell.alignment = al
cell.border = border
In [7]:
def write_LJTJ_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'], '大会記録')
write_cell(ws['E2'], gr.mark) #記録
ws.merge_cells("F2:G2")
write_cell(ws['F2'], gr.name) #氏名
write_cell(ws['H2'], gr.club)#所属
write_cell(ws['I2'], gr.year) #年
In [8]:
def write_LJTJ_head(ws, row, trial=6):
al_wrap = Alignment(vertical='center', horizontal='center', wrapText=True)
# 試技順
ws.merge_cells(cell_posi(row, "A")+":"+cell_posi(row+1,"A"))
write_cell(ws.cell(row=row, column=1), u"試順", al=al_wrap)
# No, 学年
write_cell(ws.cell(row=row, column=2), u"No.")
write_cell(ws.cell(row=row+1, column=2), u"学年")
# 氏名, 所属, 陸協
ws.merge_cells(cell_posi(row, "C")+":"+cell_posi(row,"D"))
write_cell(ws.cell(row=row, column=3), u"氏名") # Merged
write_cell(ws.cell(row=row+1, column=3), u"所属")
write_cell(ws.cell(row=row+1, column=4), u"陸協")
# 参考記録
ws.merge_cells(cell_posi(row, "E")+":"+cell_posi(row+1,"E"))
write_cell(ws.cell(row=row, column=5), u"参考\n記録", al=al_wrap)
# 試技&記録
if trial == 6:
cells = ["1", "2", "3", u"3回目\nベスト", "4","5", "6", u"記録"]
else:
cells = ["1", "2", "3", u"記録"]
for i in np.arange(6,6+len(cells),1):
print(i)
ws.merge_cells(start_row=row,start_column=i,end_row=row+1,end_column=i)
write_cell(ws.cell(row=row, column=i), cells[i-6], al=al_wrap)
# 順位
if trial == 6:
ws.merge_cells(cell_posi(row, "N")+":"+cell_posi(row+1,"N"))
write_cell(ws.cell(row=row, column=14), u"順位", al=al_wrap)
else:
ws.merge_cells(cell_posi(row, "J")+":"+cell_posi(row+1,"J"))
write_cell(ws.cell(row=row, column=10), u"順位", al=al_wrap)
# Borderの設定
if trial == 6: cell_end = 14
else: cell_end = 10
for i in range(1,cell_end+1):
ws.cell(row=row, column=i).border = border_all
ws.cell(row=row+1, column=i).border = border_all
return row+2
In [9]:
def write_LJTJ_row(ws, row, entry, order, trial=6):
font_small = Font(name='ＭＳ ゴシック',charset=128,size=6,)
al_wrap = Alignment(vertical='center', horizontal='center', wrapText=True)
al_left = Alignment(vertical='center', horizontal='left')
# 試技順
ws.merge_cells(cell_posi(row, "A")+":"+cell_posi(row+1,"A"))
write_cell(ws.cell(row=row, column=1), order)
# No, 学年
write_cell(ws.cell(row=row, column=2), entry["bib"])
write_cell(ws.cell(row=row+1, column=2), entry["grade"])
# 氏名, 所属, 陸協
write_cell(ws.cell(row=row, column=3), entry["name"])
write_cell(ws.cell(row=row, column=4), entry["kana"])
write_cell(ws.cell(row=row+1, column=3), entry["club"])
write_cell(ws.cell(row=row+1, column=4), entry["jaaf_branch"])
# 参考記録
ws.merge_cells(cell_posi(row, "E")+":"+cell_posi(row+1,"E"))
write_cell(ws.cell(row=row, column=5), entry["PB"])
# 試技&記録
if trial == 6: cell_end = 14
else: cell_end = 10
for i in np.arange(6,cell_end, 1):
write_cell(ws.cell(row=row, column=i), "m", font=font_small)
write_cell(ws.cell(row=row+1, column=i), "＋･－", font=font_small, al=al_left)
# 順位
if trial == 6: ws.merge_cells(cell_posi(row, "N")+":"+cell_posi(row+1,"N"))
else: ws.merge_cells(cell_posi(row, "J")+":"+cell_posi(row+1,"J"))
# Borderの設定
for i in range(1,cell_end+1):
ws.cell(row=row, column=i).border = border_all
ws.cell(row=row+1, column=i).border = border_all
return row + 2
In [ ]:
np.arange(6,14+1, 1)
In [10]:
def write_LJTJ(ws, row, df, trial=6):
# Header
row = write_LJTJ_head(ws, row, trial=trial)
# エントリーの書き込み
for i in np.arange(0, len(df), 1):
print(row)
row = write_LJTJ_row(ws, row, df.ix[i,:].to_dict(), i+1, trial=trial)
return row
In [ ]:
# Save
style_track_sheet(ws, 56)
wb.save(dir_out+"/test3.xlsx")
In [11]:
# Open Excel file
wb = px.Workbook()
# シートの 作成
ws = wb.active
ws.title = '男子走幅跳'
print(wb.get_sheet_names())
# Head: 種目
write_LJTJ_title(ws, gr_HJ)
# 対校
ws.merge_cells("A5:C5")
write_cell(ws["A5"], "<対校の部>", font, al)
ws['A5'].alignment =Alignment(vertical='center', horizontal='left')
#write_HJPV_head(ws, 6)
row = write_LJTJ(ws, 6, df[:7], trial=6)
# OP
row += 2
ws.merge_cells(start_row=row,start_column=1,end_row=row,end_column=3)
write_cell(ws.cell(row=row, column=1), "<オープンの部>", font, al)
ws.cell(row=row, column=1).alignment =Alignment(vertical='center', horizontal='left')
#write_HJPV_head(ws, 6)
row += 1
row = write_LJTJ(ws, row, df[:10], trial=3)
# シートのスタイル
style_LJTJ_sheet(ws, 56)
# 保存
wb.save(dir_out+"/testLJTJ.xlsx")
In [ ]:
ws["E6"].value
In [ ]:
print(font)
In [ ]:
ws['A16'].font
In [ ]:
#ws['C8'].font = font
ws['A5'].font
In [ ]:
print(ws['C5'].alignment)
print(ws['C7'].alignment)
In [ ]:
# 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())
# 補欠
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)