def openpyxl_write_excel():
workbook = openpyxl.Workbook() # 创建一个excel文件夹
worksheet =workbook.active # 获取当前最活跃的表，默认第一个
worksheet.title ='工作汇报2021年4月'
# worksheet1 = workbook.create_sheet() # 默认在工作簿的最后一页（传入页码参数可以插入到指定页码前）
# worksheet1.title = 'openpyxl1'
# 设置边框样式
border_thin = styles.Side(border_style='thin',color=openpyxl.styles.colors.BLUE)
# 设置单元格水平居中
worksheet.alignment = Alignment(horizontal='center', vertical='center')
for row in worksheet.rows:
for cell in row:
# 给每一个单元格加四个边框（可以指定加上下左右边框）
cell.boder =styles.Border(left=border_thin,right=border_thin,top=border_thin,bottom=border_thin)
# 将数据一行一行写入excel中（可以将整张表放在整个列表中写入）
worksheet.merge_cells('A1:O1') # 合并一行中的几个单元格
worksheet.cell(1,1,'周报')
for i in range(len(Reportrow)):
worksheet.cell(2, i + 1, Reportrow[i]) # 写第一行数据，openpyxl单元格下标是从1开始的
for i in range(len(content1)):
worksheet.cell(i + 3, 1, content1[i]) # 写第一列数据，i+2表示第一行数据已经写入
for i in range(len(content2)):
worksheet.cell(i + 3, 2, content2[i]) # 写入第二列数据
for i in range(len(content3)):
worksheet.cell(i + 3, 3, content3[i]) # 写入第三列数据
# 写入每一天的内容
for i in range(len(substance)):
worksheet.cell(3,i+4,substance[i])
workbook.save(r'C:/Users/ceshi001/Desktop/工作汇报2021年4月.xlsx') # 将excel保存到指定位置
def test_to_excel_styleconverter(ext):
from openpyxl import styles
hstyle = {
"font": {"color": "00FF0000", "bold": True},
"borders": {"top": "thin", "right": "thin", "bottom": "thin", "left": "thin"},
"alignment": {"horizontal": "center", "vertical": "top"},
"fill": {"patternType": "solid", "fgColor": {"rgb": "006666FF", "tint": 0.3}},
"number_format": {"format_code": "0.00"},
"protection": {"locked": True, "hidden": False},
}
font_color = styles.Color("00FF0000")
font = styles.Font(bold=True, color=font_color)
side = styles.Side(style=styles.borders.BORDER_THIN)
border = styles.Border(top=side, right=side, bottom=side, left=side)
alignment = styles.Alignment(horizontal="center", vertical="top")
fill_color = styles.Color(rgb="006666FF", tint=0.3)
fill = styles.PatternFill(patternType="solid", fgColor=fill_color)
number_format = "0.00"
protection = styles.Protection(locked=True, hidden=False)
kw = _OpenpyxlWriter._convert_to_style_kwargs(hstyle)
assert kw["font"] == font
assert kw["border"] == border
assert kw["alignment"] == alignment
assert kw["fill"] == fill
assert kw["number_format"] == number_format
assert kw["protection"] == protection
def set_border(sheet, row):
thick_bottom = styles.Border(bottom=styles.Side(style='thick'))
col = 'B'
while col != 'X':
cell = col + str(row)
sheet[cell].border = thick_bottom
col = chr(ord(col) + 1) # the alphabet is indeed in order
def test_to_excel_styleconverter(ext):
from openpyxl import styles
hstyle = {
"font": {
"color": '00FF0000',
"bold": True,
},
"borders": {
"top": "thin",
"right": "thin",
"bottom": "thin",
"left": "thin",
},
"alignment": {
"horizontal": "center",
"vertical": "top",
},
"fill": {
"patternType": 'solid',
'fgColor': {
'rgb': '006666FF',
'tint': 0.3,
},
},
"number_format": {
"format_code": "0.00"
},
"protection": {
"locked": True,
"hidden": False,
},
}
font_color = styles.Color('00FF0000')
font = styles.Font(bold=True, color=font_color)
side = styles.Side(style=styles.borders.BORDER_THIN)
border = styles.Border(top=side, right=side, bottom=side, left=side)
alignment = styles.Alignment(horizontal='center', vertical='top')
fill_color = styles.Color(rgb='006666FF', tint=0.3)
fill = styles.PatternFill(patternType='solid', fgColor=fill_color)
number_format = '0.00'
protection = styles.Protection(locked=True, hidden=False)
kw = _OpenpyxlWriter._convert_to_style_kwargs(hstyle)
assert kw['font'] == font
assert kw['border'] == border
assert kw['alignment'] == alignment
assert kw['fill'] == fill
assert kw['number_format'] == number_format
assert kw['protection'] == protection
def stylesBorder(self):
'''
边框设置
:return:
'''
left = styles.Side(border_style='thin')
right = styles.Side(border_style='thin')
top = styles.Side(border_style='thin')
bottom = styles.Side(border_style='thin')
self.border = styles.Border(left=left,
right=right,
top=top,
bottom=bottom)
return self.border
def __add_border(sheet: opx.worksheet.worksheet.Worksheet,
row: int,
style: Optional[str] = "thin") -> None:
"""
Add borders to cells
:param sheet: Sheet where the row will be bordered
:param row: Row to be colored accessed with cell.row
:param style: How the row will be bordered
:return: None
"""
for row_cells in sheet.iter_cols(1, sheet.max_column, row, row):
for cell in row_cells:
cell.border = opx_style.Border(opx_style.Side(style), opx_style.Side(style), opx_style.Side(style),
opx_style.Side(style))
def exportar_planilha_importacao_usuarios_perfil_codae(request, **kwargs):
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=planilha_importacao_usuarios_perfil_CODAE.xlsx'
workbook: Workbook = Workbook()
ws = workbook.active
ws.title = 'CODAE'
headers = [
'Nome do Usuário',
'Cargo',
'Email',
'CPF',
'Telefone',
'RF',
'Perfil',
'Número CRN'
]
_font = styles.Font(name='Calibri', sz=10)
{k: setattr(styles.DEFAULT_FONT, k, v) for k, v in _font.__dict__.items()}
for i in range(0, len(headers)):
cabecalho = ws.cell(row=1, column=1 + i, value=headers[i])
cabecalho.fill = styles.PatternFill('solid', fgColor='ffff99')
cabecalho.font = styles.Font(name='Calibri', size=10, bold=True)
cabecalho.border = styles.Border(
left=styles.Side(border_style='thin', color='000000'),
right=styles.Side(border_style='thin', color='000000'),
top=styles.Side(border_style='thin', color='000000'),
bottom=styles.Side(border_style='thin', color='000000')
)
dv = DataValidation(
type='list',
formula1='"COORDENADOR_GESTAO_ALIMENTACAO_TERCEIRIZADA,'
'COORDENADOR_DIETA_ESPECIAL,'
'COORDENADOR_SUPERVISAO_NUTRICAO,'
'COORDENADOR_GESTAO_PRODUTO"',
allow_blank=True
)
dv.error = 'Perfil Inválido'
dv.errorTitle = 'Perfil não permitido'
ws.add_data_validation(dv)
dv.add('G2:G1048576')
workbook.save(response)
return response
def exportar_planilha_importacao_usuarios_perfil_escola(request, **kwargs):
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=planilha_importacao_usuarios_perfil_ESCOLA.xlsx'
workbook: Workbook = Workbook()
ws = workbook.active
ws.title = 'ESCOLA'
headers = [
'Cód. EOL da U.E',
'Nome do Usuário',
'Cargo',
'Email',
'CPF',
'Telefone',
'RF',
'Perfil',
]
_font = styles.Font(name='Calibri', sz=10)
{k: setattr(styles.DEFAULT_FONT, k, v) for k, v in _font.__dict__.items()}
for i in range(0, len(headers)):
cabecalho = ws.cell(row=1, column=1 + i, value=headers[i])
cabecalho.fill = styles.PatternFill('solid', fgColor='ffff99')
cabecalho.font = styles.Font(name='Calibri', size=10, bold=True)
cabecalho.border = styles.Border(
left=styles.Side(border_style='thin', color='000000'),
right=styles.Side(border_style='thin', color='000000'),
top=styles.Side(border_style='thin', color='000000'),
bottom=styles.Side(border_style='thin', color='000000')
)
dv = DataValidation(
type='list',
formula1='"DIRETOR, DIRETOR CEI"',
allow_blank=True
)
dv.error = 'Perfil Inválido'
dv.errorTitle = 'Perfil não permitido'
ws.add_data_validation(dv)
dv.add('H2:H1048576')
workbook.save(response)
return response
def __init__(self, reportfile, Dict):
self.report = openpyxl.load_workbook(reportfile)
self.Result_Dict = Dict
active = self.report.active
self.sheet = self.report[active.title]
self.border = styles.Border(left=styles.Side(style='thin',
color='FF000000'),
right=styles.Side(style='thin',
color='FF000000'),
top=styles.Side(style='thin',
color='FF000000'),
bottom=styles.Side(style='thin',
color='FF000000'),
diagonal=styles.Side(style='thin',
color='FF000000'),
diagonal_direction=0,
outline=styles.Side(style='thin',
color='FF000000'),
vertical=styles.Side(style='thin',
color='FF000000'),
horizontal=styles.Side(style='thin',
color='FF000000'))
def save_dataframe(outdir, format, event, dataframe, product=None):
border = styles.Border(left=styles.Side(border_style=None, color='FFFFFF'),
right=styles.Side(border_style=None,
color='FFFFFF'),
top=styles.Side(border_style=None, color='FFFFFF'),
bottom=styles.Side(border_style=None,
color='FFFFFF'),
diagonal=styles.Side(border_style=None,
color='FFFFFF'),
diagonal_direction=0,
outline=styles.Side(border_style=None,
color='FFFFFF'),
vertical=styles.Side(border_style=None,
color='FFFFFF'),
horizontal=styles.Side(border_style=None,
color='FFFFFF'))
if format == 'excel':
if product is not None:
outfile = os.path.join(outdir, event.id + '_' + product + '.xlsx')
else:
outfile = os.path.join(outdir, event.id + '.xlsx')
dataframe.to_excel(outfile, index=False)
wb = load_workbook(outfile)
ws = wb.active
ws.insert_rows(0, amount=6)
ws.cell(1, 1, value='Event ID')
ws.cell(1, 2, value=event.id)
ws.cell(2, 1, value='Origin Time')
ws.cell(2, 2, value=event.time.strftime(TIMEFMT))
ws.cell(3, 1, value='Magnitude')
ws.cell(3, 2, value=event.magnitude)
ws.cell(4, 1, value='Latitude')
ws.cell(4, 2, value=event.latitude)
ws.cell(5, 1, value='Longitude')
ws.cell(5, 2, value=event.longitude)
ws.cell(6, 1, value='Depth')
ws.cell(6, 2, value=event.depth)
fills = {}
for product, color in COLORS.items():
my_color = styles.colors.Color(rgb=color)
my_fill = styles.fills.PatternFill(patternType='solid',
fgColor=my_color)
fills[product] = my_fill
# color rows by product type
for row in ws.iter_rows(min_row=8, min_col=2, max_col=2):
mycell = row[0]
if mycell.value in fills:
fill = fills[mycell.value]
else:
fill = fills['default']
row_range = '%i:%i' % (mycell.row, mycell.row)
for cell in ws[row_range]:
cell.fill = fill
# TODO - figure out why this doesn't do anything!
cell.border = border
wb.save(outfile)
else:
if product is not None:
outfile = os.path.join(outdir, event.id + '_' + product + '.csv')
else:
outfile = os.path.join(outdir, event.id + '.csv')
if format == 'tab':
dataframe.to_csv(outfile, sep='\t', index=False)
else:
dataframe.to_csv(outfile, index=False)
cdata = open(outfile, 'rt').read()
with open(outfile, 'wt') as f:
f.write('# Event ID: %s\n' % event.id)
f.write('# Origin Time: %s\n' % event.time.strftime(TIMEFMT))
f.write('# Magnitude: %s\n' % event.magnitude)
f.write('# Latitude: %s\n' % event.latitude)
f.write('# Longitude: %s\n' % event.longitude)
f.write('# Depth: %s\n' % event.depth)
f.write(cdata)
return outfile
lightblue_fill = xl_styles.PatternFill(
fill_type='solid',
start_color='FFCCE5FF',
)
fill_tbl = dict(lightblue=lightblue_fill, )
#################################################################
# Border Types #
# #
# If you add a border type, remember to add it to border_tbl #
#################################################################
thin_border = xl_styles.Border(
left=xl_styles.Side(border_style='thin', color='FF000000'),
right=xl_styles.Side(border_style='thin', color='FF000000'),
top=xl_styles.Side(border_style='thin', color='FF000000'),
bottom=xl_styles.Side(border_style='thin', color='FF000000'),
# diagonal=xl_styles.Side(border_style=None,color='FF000000'),
# diagonal_direction=0,outline=xl_styles.Side(border_style=None,color='FF000000'),
# vertical=xl_styles.Side(border_style=None,color='FF000000'),
# horizontal=xl_styles.Side(border_style=None,color='FF000000')
)
border_tbl = dict(thin=thin_border, )
#################################################################
# Align Types #
# #
# If you add an align type, remember to add it to align_tbl #
#################################################################
wrap_center_alignment = xl_styles.Alignment(horizontal='center',
vertical='top',
text_rotation=0,
wrap_text=True,
font = styles.Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
font_b = styles.Font(name='Calibri',
size=11,
bold=True,
color='FF000000')
thin = styles.Side(border_style="thin", color="000000")
border = styles.Border(outline = thin, top = thin, left=thin, right=thin, bottom=thin, vertical=thin, horizontal=thin)
alignment = styles.Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
ws.merge_cells('B1:D2')
# show error if insert value into first
# row - строка, column - поле
ws['C8'] = str(ws['C9'].value) + '!'
ws.cell(row=1, column=2, value='huy')
def __init__(self,
filename=None,
model_dir=None,
license_summary=False,
params=None):
self.wb = None
self.filename = filename
self.params = params
if self.params is None:
self.params = {}
if filename is not None:
self.wb = openpyxl.load_workbook(filename=filename)
else:
self.wb = openpyxl.Workbook()
self.ws_models = self.wb.active
self.ws_models.title = 'Index'
thin = styles.Side(
border_style=self.params.get('side_border', 'thin'),
color=self.params.get('side_color', '999999'))
for i in range(1, len(group_styles) + 1):
key = 'suns_group_%s' % i
name = 'suns_group_entry_%s' % i
style = styles.NamedStyle(name=name)
color = group_styles[key]['group_color']
# self.params.get('group_color', color)
style.fill = styles.PatternFill('solid', fgColor=color)
style.font = styles.Font()
style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
style.alignment = styles.Alignment(horizontal='center',
wrapText=True)
self.wb.add_named_style(style)
name = 'suns_group_text_%s' % i
style = styles.NamedStyle(name=name)
style.fill = styles.PatternFill('solid', fgColor=color)
style.font = styles.Font()
style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
style.alignment = styles.Alignment(horizontal='left',
wrapText=True)
self.wb.add_named_style(style)
name = 'suns_point_entry_%s' % i
style = styles.NamedStyle(name=name)
color = group_styles[key]['point_color']
# self.params.get('group_color', color)
style.fill = styles.PatternFill('solid', fgColor=color)
style.font = styles.Font()
style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
style.alignment = styles.Alignment(horizontal='center',
wrapText=True)
self.wb.add_named_style(style)
name = 'suns_point_text_%s' % i
style = styles.NamedStyle(name=name)
style.fill = styles.PatternFill('solid', fgColor=color)
style.font = styles.Font()
style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
style.alignment = styles.Alignment(horizontal='left',
wrapText=True)
self.wb.add_named_style(style)
if 'suns_hdr' not in self.wb.named_styles:
hdr_style = styles.NamedStyle(name='suns_hdr')
hdr_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('hdr_color', 'dddddd'))
hdr_style.font = styles.Font(bold=True)
hdr_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
hdr_style.alignment = styles.Alignment(horizontal='center',
wrapText=True)
self.wb.add_named_style(hdr_style)
if 'suns_group_entry' not in self.wb.named_styles:
model_entry_style = styles.NamedStyle(
name='suns_group_entry')
model_entry_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('group_color', 'fff9e5'))
model_entry_style.font = styles.Font()
model_entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
model_entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(model_entry_style)
if 'suns_group_text' not in self.wb.named_styles:
model_text_style = styles.NamedStyle(
name='suns_group_text')
model_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('group_color', 'fff9e5'))
model_text_style.font = styles.Font()
model_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
model_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(model_text_style)
if 'suns_point_entry' not in self.wb.named_styles:
fixed_entry_style = styles.NamedStyle(
name='suns_point_entry')
fixed_entry_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('point_color', 'e6f2ff'))
fixed_entry_style.font = styles.Font()
fixed_entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
fixed_entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(fixed_entry_style)
if 'suns_point_text' not in self.wb.named_styles:
fixed_text_style = styles.NamedStyle(
name='suns_point_text')
fixed_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('point_color', 'e6f2ff'))
fixed_text_style.font = styles.Font()
fixed_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
fixed_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(fixed_text_style)
if 'suns_point_variable_entry' not in self.wb.named_styles:
fixed_entry_style = styles.NamedStyle(
name='suns_point_variable_entry')
fixed_entry_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('point_variable_color',
'ecf9ec'))
fixed_entry_style.font = styles.Font()
fixed_entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
fixed_entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(fixed_entry_style)
if 'suns_point_variable_text' not in self.wb.named_styles:
fixed_text_style = styles.NamedStyle(
name='suns_point_variable_text')
fixed_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('point_variable_color',
'ecf9ec'))
fixed_text_style.font = styles.Font()
fixed_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
fixed_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(fixed_text_style)
if 'suns_symbol_entry' not in self.wb.named_styles:
repeating_entry_style = styles.NamedStyle(
name='suns_symbol_entry')
repeating_entry_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('symbol_color', 'fafafa'))
repeating_entry_style.font = styles.Font()
repeating_entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
repeating_entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(repeating_entry_style)
if 'suns_symbol_text' not in self.wb.named_styles:
repeating_text_style = styles.NamedStyle(
name='suns_symbol_text')
repeating_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('symbol_color', 'fafafa'))
repeating_text_style.font = styles.Font()
repeating_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
repeating_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(repeating_text_style)
if 'suns_comment' not in self.wb.named_styles:
symbol_text_style = styles.NamedStyle(name='suns_comment')
symbol_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('comment_color', 'dddddd'))
# fgColor=self.params.get('symbol_color', 'fffcd9'))
symbol_text_style.font = styles.Font()
symbol_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
symbol_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(symbol_text_style)
if 'suns_entry' not in self.wb.named_styles:
entry_style = styles.NamedStyle(name='suns_entry')
entry_style.fill = styles.PatternFill('solid',
fgColor='ffffff')
entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(entry_style)
if 'suns_text' not in self.wb.named_styles:
text_style = styles.NamedStyle(name='suns_text')
text_style.font = styles.Font()
text_style.alignment = styles.Alignment(horizontal='left',
wrapText=True)
self.wb.add_named_style(text_style)
if 'suns_hyper' not in self.wb.named_styles:
hyper_style = openpyxl.styles.NamedStyle(name='suns_hyper')
hyper_style.font = openpyxl.styles.Font(color='0000ee',
underline='single')
hyper_style.alignment = openpyxl.styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(hyper_style)
for i in range(len(models_hdr)):
self.set_cell(self.ws_models, 1, i + 1, models_hdr[i][0],
'suns_hdr')
if models_hdr[i][1]:
self.ws_models.column_dimensions[chr(
65 + i)].width = models_hdr[i][1]
def export_table_views(tment, workbook, time_fmt, team_info, rnd_abbrevs,
rooms, tnames):
"""Adds the competition table focused sheets to the output workbook."""
thin = styles.Side(border_style='thin', color='000000')
thick = styles.Side(border_style='thick', color='000000')
team_width = 2 + len(team_info)
space = 2
split = 1 + 2 * team_width * ((tment.t_pairs + 1) // 2)
staggered = [
tment.t_stagger and i > (tment.t_pairs - 1) // 2
for i in range(tment.t_pairs)
]
#writing data
sheet_overall = workbook.create_sheet("Competition Tables")
header = sum([[tbl] + (team_width - 1) * [''] for tbl in rooms[5]], [''])
header[split:split] = tment.t_stagger * (space + 1) * ['']
sheet_overall.append(header)
t_pair_sheets = [workbook.create_sheet('-'.join(tbls)) for tbls in tnames]
for t_pair in range(tment.t_pairs):
t_pair_sheets[t_pair]\
.append([''] + header[2*team_width*t_pair + 1 + staggered[t_pair]*(space + 1):
2*team_width*(t_pair + 1) + staggered[t_pair]*(space + 1)])
for slot in tment.t_slots:
if slot is None:
for sheet in [
sheet for sheet in t_pair_sheets + [sheet_overall]
for i in range(2)
]:
sheet.append([''])
elif all([team is None for team in slot[2]]):
sheet_overall.append(
[slot[0][0].strftime(time_fmt)] + tment.t_stagger *
((split + 1) * [''] + [slot[0][1].strftime(time_fmt)]))
for i, sheet in enumerate(t_pair_sheets):
sheet.append([slot[0][staggered[i]].strftime(time_fmt)])
else:
line = sum([(team_width - 1) * [''] + ['None'] if t is None else
[rnd_abbrevs[rnd], tment.teams[t].num] + team_info
for t, rnd in slot[2]],
[slot[0][0].strftime(time_fmt)])
line[split:split] = space * [''] + [slot[0][1].strftime(time_fmt)
] if tment.t_stagger else []
sheet_overall.append(line)
for t_pair in range(tment.t_pairs):
time_str = slot[0][staggered[t_pair]].strftime(time_fmt)
ls_start = 2 * team_width * t_pair + (
space + 1) * staggered[t_pair] + 1
t_pair_sheets[t_pair].append([time_str] +
line[ls_start:ls_start +
2 * team_width])
#formatting - borders, cell merges, striped shading, etc
col_wide = [1 + max(len(str(rnd)) for rnd in rnd_abbrevs)]
col_wide += [
1 + max(len(str(text)) for text in cat)
for cat in zip(*[team.info(tment.divisions) for team in tment.teams])
]
if tment.divisions:
col_wide[2] += 4
col_wide = [-1] + 2 * tment.t_pairs * col_wide
col_wide[split + 1:split + 1] = tment.t_stagger * (space * [10] + [-1])
for sheet in [sheet_overall] + t_pair_sheets:
basic_sheet_format(sheet, 2)
for col, width in [(col, width)
for col, width in enumerate(col_wide, 1)
if width > 0]:
sheet.column_dimensions[get_column_letter(col)].width = width
thick_border = [
1 + 2 * i * team_width for i in range(tment.t_pairs + 1)
]
if tment.t_stagger:
thick_border = [
1 + 2 * i * team_width
for i in range(math.ceil(tment.t_pairs / 2) + 1)
]
thick_border += [
val + thick_border[-1] + space for val in thick_border
]
for cell in [cell for row in sheet for cell in row]:
if tment.t_stagger and split < cell.column <= split + space:
cell.fill = openpyxl.styles.PatternFill(None)
elif cell.column in thick_border or cell.column + team_width in thick_border:
cell.border = styles.Border(
right=thick if cell.column in thick_border else thin)
for i in range(2 * tment.t_pairs):
start_col = 2 + i * team_width + (space + 1) * staggered[i // 2]
sheet.merge_cells(start_row=1,
start_column=start_col,
end_row=1,
end_column=start_col + team_width - 1)
def export_judge_views(tment, workbook, time_fmt, team_info, event_names,
rooms):
"""Adds the four judging-focused sheets to the output workbook."""
thin = styles.Side(border_style='thin', color='000000')
thick = styles.Side(border_style='thick', color='000000')
team_width = 1 + len(team_info)
sheets = [
workbook.create_sheet(name)
for name in ["Judging Rooms"] + event_names[2:5]
]
#writing data
rows = [[['']], [['']]]
for i in range(3):
rows[0].append([event_names[i + 2]] +
(tment.j_sets * team_width - 1) * [''])
rows[1].append(
sum([[room] + (team_width - 1) * [''] for room in rooms[i + 2]],
[]))
for time, teams in tment.j_slots:
rows.append([[time.strftime(time_fmt)]])
if teams is not None:
teams = [[None if t is None else tment.teams[t] for t in cat]
for cat in teams]
if len(teams[0]) == 1 and tment.j_calib:
rows[-1] += [[teams[i][0].num] + team_info + [
"all {} judges in {}".format(event_names[i + 2].lower(),
rooms[i + 2][0])
] + (team_width * (tment.j_sets - 1) - 1) * ['']
for i in range(3)]
else:
rows[-1] += [
sum([[''] * (team_width - 1) +
['None'] if team is None else [team.num] + team_info
for team in cat], []) for cat in teams
]
for row in rows:
sheets[0].append(sum(row, []))
for i in range(3):
sheets[i + 1].append(row[0] + (row[i + 1] if len(row) > 1 else []))
#formatting - borders, cell merges, striped shading, etc
col_sizes = [
1 + max(len(str(text)) for text in cat)
for cat in zip(*[team.info(tment.divisions) for team in tment.teams])
]
col_sizes[1] += 5 * tment.divisions
for sheet in sheets:
basic_sheet_format(sheet, 4)
for col in list(sheet.columns)[1:]:
sheet.column_dimensions[get_column_letter(col[0].column)].width =\
col_sizes[(col[0].column - 2) % len(col_sizes)]
sheet_borders(
sheet,
((styles.Border(left=thick), team_width * tment.j_sets, 2, 0),
(styles.Border(left=thin), team_width, 2, 1 + 2 * tment.j_calib)))
for i in range(
(len(list(sheet.columns)) - 1) // (team_width * tment.j_sets)):
sheet.cell(row=1, column=team_width * tment.j_sets * i +
2).font = styles.Font(bold=True)
sheet.merge_cells(start_row=1,
start_column=2 + i * team_width * tment.j_sets,
end_row=1,
end_column=1 +
(i + 1) * team_width * tment.j_sets)
for j in range(tment.j_sets):
sheet.merge_cells(
start_row=2,
start_column=team_width * (tment.j_sets * i + j) + 2,
end_row=2,
end_column=team_width * (tment.j_sets * i + j + 1) + 1)
if tment.j_calib:
sheet.merge_cells(
start_row=3,
start_column=2 + team_width * (tment.j_sets * i + 1),
end_row=3,
end_column=1 + team_width * (tment.j_sets * (i + 1)))
def _innerforms(self, sheet):
# eval the innerforms to sheet
# steps: merge-width-height-frame-fixhead
merges = self.forms.get('merges')
widths = self.forms.get('widths')
heights = self.forms.get('heights')
frames = self.forms.get('frames')
def _keyuper(fromc, toc):
# 'A' -> 'ZZ' etc.
# fool way
fromc = fromc.upper()
toc = toc.upper()
line = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
emptybit = ' '
maxi = len(line) - 1
bits = [x for x in fromc]
bits.reverse()
if len(bits) < len(toc):
for _ in xrange(len(toc) - len(bits)):
bits.append(emptybit)
bit_inc = True
out = ""
while 1:
out = ''.join(bits)[::-1].strip()
yield out
if out == toc:
break
bit_inc = True
cbit = 0
for b in bits:
if bit_inc is False:
break
i = line.find(b)
if i == maxi:
bits[cbit] = line[0]
bit_inc = True
elif i == -1 and bits[cbit] == emptybit:
# empty bit as space
bits[cbit] = line[0]
bit_inc = False
else:
bits[cbit] = line[i + 1]
bit_inc = False
cbit += 1
def _csplit(s):
for _ in xrange(len(s)):
if s[_].isdigit():
return s[:_], int(s[_:])
def _frame2cells(lt, rb):
# lt="A1", rt="B3" etc.
lt_n, lt_v = _csplit(lt)
rb_n, rb_v = _csplit(rb)
for c in _keyuper(lt_n, rb_n):
for v in xrange(lt_v, rb_v + 1):
yield c + str(v)
if widths:
for c, w in widths:
w = int(w.strip())
c = c.strip().upper()
if '-' in c:
f, t = c.split('-')
for _ in _keyuper(f, t):
sheet.column_dimensions[_].width = w
else:
sheet.column_dimensions[c].width = w
if heights:
for r, h in heights:
h = int(h.strip())
if '-' in r:
s, e = r.split('-')
for _ in xrange(int(s.strip()), int(e.strip()) + 1):
sheet.row_dimensions[_].height = h
else:
sheet.row_dimensions[int(r)].height = h
if merges:
for corner in merges:
#for lt,rb in merges:
# sheet.merge_cells('%s:%s' % (lt, rb))
sheet.merge_cells(':'.join(corner))
if frames:
thin = styles.Side(border_style="thin", color="000000")
for frame in frames:
if len(frame) == 3:
rgba = frame.pop()
else:
rgba = None
for c in _frame2cells(*frame):
sheet[c].border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
if rgba:
sheet[c].fill = styles.PatternFill('solid',
fgColor=rgba)
if self.forms.get('fixhead'):
sheet.freeze_panes = 'A2'
return sheet
# -*- coding: utf-8 -*- """Модуль роутинга главной страницы. """ # noqa # from openpyxl import Workbook from openpyxl import styles as xlstyles THIN_BORDER_STYLE: object = xlstyles.Side(border_style="thin", color="000000") TOP_BORDER: object = xlstyles.Border(top=THIN_BORDER_STYLE) BOTTOM_BORDER: object = xlstyles.Border(bottom=THIN_BORDER_STYLE) LEFT_BORDER: object = xlstyles.Border(left=THIN_BORDER_STYLE) RIGHT_BORDER: object = xlstyles.Border(right=THIN_BORDER_STYLE) COLUMN_A = 1 COLUMN_B = 2 COLUMN_C = 3 COLUMN_D = 4 COLUMN_E = 5 COLUMN_F = 6 COLUMN_G = 7 COLUMN_H = 8 COLUMN_I = 9 COLUMN_J = 10 COLUMN_K = 11 COLUMN_L = 12 COLUMN_M = 13 COLUMN_N = 14 COLUMN_O = 15 COLUMN_P = 16 COLUMN_Q = 17 COLUMN_R = 18 COLUMN_S = 19
def bloco3_resumo_por_acao(worksheet, acoes, conta_associacao, periodo):
global OFFSET
thin = styles.Side(border_style="thin", color="000000")
linha_inicial = 21 + OFFSET
linha_atual = linha_inicial
offset_local = 0
total_valores = 0
total_conciliacao = 0
destinacoes = ['C', 'K', 'CK']
totalizador = {
SALDO_ANTERIOR: {
'C': 0,
'K': 0,
'CK': 0
},
CREDITO: {
'C': 0,
'K': 0,
'CK': 0
},
DESPESA_REALIZADA: {
'C': 0,
'K': 0,
'CK': 0
},
DESPESA_NAO_REALIZADA: {
'C': 0,
'K': 0,
'CK': 0
},
SALDO_REPROGRAMADO_PROXIMO: {
'C': 0,
'K': 0,
'CK': 0
},
DESPESA_NAO_DEMONSTRADA_OUTROS_PERIODOS: {
'C': 0,
'K': 0,
'CK': 0
},
SALDO_BANCARIO: {
'C': 0,
'K': 0,
'CK': 0
},
TOTAL_SALDO_BANCARIO: {
'C': 0,
'K': 0,
'CK': 0
},
CREDITO_NAO_DEMONSTRADO: {
'C': 0,
'K': 0,
'CK': 0
},
}
logging.info(f'Linha inicial:{linha_inicial}')
for linha_acao, acao_associacao in enumerate(acoes):
# Movendo as linhas para baixo antes de inserir os dados novos
linha_atual = linha_inicial + (linha_acao * 3)
offset_local = linha_atual - linha_inicial
logging.info(
f'LAção:{linha_acao}, LAtual:{linha_atual}, offset:{offset_local}, Ação:{acao_associacao.acao.nome}'
)
if offset_local > 0:
insert_row(worksheet, LAST_LINE + OFFSET + offset_local,
linha_atual - 1)
insert_row(worksheet, LAST_LINE + OFFSET + offset_local,
linha_atual - 1)
insert_row(worksheet, LAST_LINE + OFFSET + offset_local,
linha_atual - 1)
fechamento_periodo = FechamentoPeriodo.fechamentos_da_acao_no_periodo(
acao_associacao=acao_associacao,
periodo=periodo,
conta_associacao=conta_associacao).first()
sub_valores, sub_conciliacao, totalizador = sintese_receita_despesa(
worksheet, acao_associacao, conta_associacao, periodo,
fechamento_periodo, linha_atual, totalizador)
total_valores += sub_valores
total_conciliacao += sub_conciliacao
for destinacao_idx in range(0, 2):
if destinacao_idx == 0:
try:
worksheet.unmerge_cells(
f'B{linha_atual + 1}:B{linha_atual + 3}')
except ValueError:
# Ignora caso o campo ja esteja desmergeado
pass
row = list(worksheet.rows)[linha_atual + destinacao_idx]
row[0].value = destinacoes[destinacao_idx]
if destinacao_idx == 0:
worksheet.merge_cells(f'B{linha_atual + 1}:B{linha_atual + 3}')
top_left_cell = worksheet[f'B{linha_atual + 1}']
top_left_cell.value = acao_associacao.acao.nome
top_left_cell.font = styles.Font(name='Arial',
size=10.5,
b=True,
color="000000")
top_left_cell.alignment = styles.Alignment(horizontal="left",
vertical="center")
# Ajusta o fundo para que a apenas as Colunas E, F, I e L tenham fundo cinza na linha de destinação CK
for col in ('E', 'F', 'I', 'L'):
col_cell = worksheet[
f'{col}{linha_atual + destinacao_idx + 1}']
col_cell.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
col_cell.fill = styles.PatternFill(
"solid",
fgColor="808080" if destinacao_idx == 2 else "FFFFFF")
linha_atual += 3
worksheet.merge_cells(f'B{linha_atual + 1}:B{linha_atual + 3}')
top_left_cell = worksheet[f'B{linha_atual + 1}']
top_left_cell.value = "TOTAL"
top_left_cell.font = styles.Font(name='Arial',
size=10.5,
b=True,
color="000000")
top_left_cell.alignment = styles.Alignment(horizontal="left",
vertical="center")
for idx, destinacao in enumerate(destinacoes):
row = list(worksheet.rows)[linha_atual + idx]
row[SALDO_ANTERIOR].value = formata_valor(
totalizador[SALDO_ANTERIOR][destinacao])
row[CREDITO].value = formata_valor(totalizador[CREDITO][destinacao])
row[DESPESA_REALIZADA].value = formata_valor(
totalizador[DESPESA_REALIZADA][destinacao])
row[DESPESA_NAO_REALIZADA].value = formata_valor(
totalizador[DESPESA_NAO_REALIZADA][destinacao])
row[SALDO_REPROGRAMADO_PROXIMO].value = formata_valor(
totalizador[SALDO_REPROGRAMADO_PROXIMO][destinacao])
row[DESPESA_NAO_DEMONSTRADA_OUTROS_PERIODOS].value = formata_valor(
totalizador[DESPESA_NAO_DEMONSTRADA_OUTROS_PERIODOS][destinacao])
row[SALDO_BANCARIO].value = formata_valor(
totalizador[SALDO_BANCARIO][destinacao])
if destinacao != 'CK':
row[CREDITO_NAO_DEMONSTRADO].value = formata_valor(
totalizador[CREDITO_NAO_DEMONSTRADO][destinacao])
if destinacao == 'K':
valor_total_reprogramado_proximo = totalizador[
SALDO_REPROGRAMADO_PROXIMO]['CK']
valor_total_reprogramado_proximo = (
valor_total_reprogramado_proximo +
totalizador[SALDO_REPROGRAMADO_PROXIMO]['C']
if totalizador[SALDO_REPROGRAMADO_PROXIMO]['C'] > 0 else
valor_total_reprogramado_proximo)
valor_total_reprogramado_proximo = (
valor_total_reprogramado_proximo +
totalizador[SALDO_REPROGRAMADO_PROXIMO]['K']
if totalizador[SALDO_REPROGRAMADO_PROXIMO]['K'] > 0 else
valor_total_reprogramado_proximo)
row[TOTAL_REPROGRAMADO_PROXIMO].value = formata_valor(
valor_total_reprogramado_proximo)
# Apresenta o subtotal bancário da ação na posição destinada ao valor
valor_saldo_bancario_total = totalizador[SALDO_BANCARIO]['C'] + totalizador[SALDO_BANCARIO]['K'] + \
totalizador[SALDO_REPROGRAMADO_PROXIMO]['CK']
row[TOTAL_SALDO_BANCARIO].value = "Subtotal"
row[TOTAL_SALDO_BANCARIO].value = formata_valor(
valor_saldo_bancario_total)
OFFSET += offset_local
####################################################################
"""-----------------------------------------------------------------
STYLE SETUP
-----------------------------------------------------------------"""
####################################################################
TITLESTYLE = xlstyle.NamedStyle(name='TITLESTYLE')
TITLESTYLE.font = xlstyle.Font(bold=True, size=16)
TITLESTYLE.alignment = xlstyle.Alignment(horizontal="center")
TOTALCELLSTYLE = xlstyle.NamedStyle(name="TOTALCELLSTYLE")
TOTALCELLSTYLE.font = xlstyle.Font(bold=True, size=12, color='FFFFFF')
ALLTHICKBORDERS = xlstyle.Border(
**{
side: xlstyle.Side(border_style='thick', color='000000')
for side in ['left', 'right', 'top', 'bottom']
})
TOTALCELLSTYLE.border = ALLTHICKBORDERS
TOTALCELLSTYLE.fill = xlstyle.PatternFill(fill_type="solid",
start_color='BFBFBF')
MONTHTABLESTYLE = xltable.TableStyleInfo(name="TableStyleMedium4",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False)
SUMMARYTABLESTYLE = xltable.TableStyleInfo(name="TableStyleLight9",
showFirstColumn=False,
showLastColumn=False,
