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 step5_mapping_netname(clearData, ws):
textFont = Font(name='Verdana', size=10)
textAlignment = Alignment(vertical='center')
for netName, netData in clearData.items():
for i in ws['D']:#intel裡面的ball name
if type(i.value) == str:
result = re.findall(f"(UCPU1\.{i.value}\,|UCPU1\.{i.value}$)", netData, re.I)
if result:
#確認有無重複
if not bool(ws.cell(row = row , column= 5).value):
row = i.row #i.coordinate
ws.cell(row = row , column= 5).value = netName
ws.cell(row = row , column= 5).font = textFont
ws.cell(row = row , column= 5).alignment = textAlignment
else:
ws.insert_cols(5,1)
ws.cell(row = row , column= 6).value = netName
ws.cell(row = row , column= 6).font = textFont
ws.cell(row = row , column= 6).alignment = textAlignment
else:
row = i.row
ws.cell(row= row , column= 5).value = ""
titlefont = Font(name='Verdana', size=10, color = "FF00B050", family=2.0, bold=True)
titlefontAlignment = Alignment(horizontal='center',vertical='center', wrapText=True)
titleborder = Border(left = styles.Side(border_style='medium', color='FF000000'),
right = styles.Side(border_style='medium', color='FF000000'),
top = styles.Side(border_style='medium', color='FF000000'),
bottom = styles.Side(border_style='medium', color='FF000000'),
)
ws['E2'] = "Project"
ws['E2'].font = titlefont
ws['E2'].alignment = titlefontAlignment
ws['E2'].border = titleborder
def exportar_planilha_importacao_usuarios_perfil_dre(request, **kwargs):
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=planilha_importacao_usuarios_perfil_DRE.xlsx'
workbook: Workbook = Workbook()
ws = workbook.active
ws.title = 'DRE'
headers = [
'Cód. EOL da DRE',
'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='"COGESTOR, ADMINISTRADOR_DRE"',
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 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 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
def anovarun(file='data.csv', order=['supp', 'dose', 'len']):
"""
Docstring: Function generates runs an Anova 2 ways analysis from a csv file
"""
# Convert csv data to `pandas.DataFrame` [df2]
ddf2 = pd.read_csv(file, header=0, sep=',')
if order:
emplist = []
for mat in order:
emplist.append(mat in ddf2.columns)
if sum(emplist) == 3:
ddf2 = ddf2[order]
fcols = list(ddf2.columns)[0:2] # factor columns for DataFrame
vcols = list(ddf2.columns)[2] # value columns for DataFrame
ddf2.sort_values(by=[fcols[0], fcols[1]], inplace=True)
ddf2.index = range(1, len(ddf2) + 1) # Renumber [df2]
# Pivot table based on the 1st and 2nd factors
df2_pivot = ddf2.pivot_table(columns=fcols,
values=vcols,
index=ddf2.index,
fill_value='--')
df2_pivot = remnan(df2_pivot) # Removes nan values from [df2_pivot]
print(df2_pivot)
dm = df2_pivot.melt(value_name=vcols) # Melt table back to original [dm]
dmgr = dm.groupby(
fcols).mean() # Grouping Melted table and calculating mean [dmgr]
dmgr_unstack = dmgr.unstack(1) # unstacking data of 1st factor on index
# Calculation of mean values
dmgr_unstack_mean = dmgr_unstack.copy(
) # DataFrame copy to [dmgr_unstack_mean]
dmgr_unstack_mean.loc[:, (vcols, 'Average')] = dmgr_unstack_mean.apply(
lambda x: round(np.mean(x), 1),
axis=1) # calculating mean of values along row as [['Average']]
dmgr_unstack_mean.loc['Average', :] = dmgr_unstack_mean.apply(
np.mean, axis=0
) # calculating mean of values along column on index [['Average']]
# Sum of Square of 1st factor
def catall(df):
li5 = []
li7 = []
for nums1, li6 in zip(range(1, len(df.columns) + 1), df.columns):
if nums1 == 1:
li5.append(li6[0])
elif nums1 == (len(df2_pivot.columns) / 2) + 1:
li5.append(li6[0])
for v in df.iteritems():
li7.append(v[1].count())
return li7, li5
nv, lab = catall(df2_pivot) # finding number of data for 1st factors [nv]
mv = dmgr_unstack_mean.loc[:, (
vcols,
'Average')].values[0:2] # Mean of first category on first factor
xv = dmgr_unstack_mean.loc['Average',
(vcols, 'Average')] # Data overall mean
chk = int(len(nv) / len(mv))
index = 0
ss1v = 0
emptylist = []
for me_in in mv: # Mathematical calculation for [ss1v]
for m2 in range(index, chk + index):
emptylist.append((nv[m2], me_in))
ss1v = ss1v + (nv[m2] * (me_in - xv)**2)
index = m2 + 1
ss1v = round(ss1v, 4)
# Sum of Square of 2nd factor
nps = dmgr_unstack_mean.iloc[-1, :].values[0:3]
ss2v = 0
## Loop to solve [ss2v]
for cc in lab:
ins = df2_pivot.loc[:, (cc)].count().values
for no, nums in zip(ins, nps):
ss2v = ss2v + no * (nums - xv)**2
ss2v = round(ss2v, 4)
# Sum of Square within (Error)
df2_pivot_ssw = df2_pivot.copy() # Copy [df2_pivot] to [df2_pivot_ssw]
## function to perform iteration on apply method [df2_pivot_ssw]
def sswdo(arg, x):
li1 = []
arg = arg.values
for num2, xi in zip(arg, x):
if str(num2) != '--':
li1.append((num2 - xi)**2)
out = sum(li1)
return out
ref = len(dmgr_unstack_mean.columns) - 1
li2 = []
for seg in range(len(lab)):
li2.extend(list(dmgr_unstack_mean.iloc[seg, 0:ref].values))
df2_pivot_ssw.fillna('--', inplace=True)
ssw = round(sum(df2_pivot_ssw.apply(sswdo, x=li2, axis=1)), 4)
# Sum of Square Total
sst = round(
dm.iloc[:, -1].apply(lambda x: (x - xv)**2 if x > 1 else None).sum(),
2) # (x-xv)**2: for sum of square total [sst]
# Sum of square for both factors [ssb]
ssb = round(sst - ss1v - ss2v - ssw, 2)
# Degree of Freedom calcution
df1 = len(dmgr_unstack_mean.index) - 2 # degree of freedom for factor 1
df2 = len(dmgr_unstack_mean.columns) - 2 # degree of freedom for factor 2
ref = len(dmgr_unstack_mean.columns) - 1
out = 0
for num3 in nv:
out = out + num3 - 1
dfw = out # degree of freedom for within
dfb = df1 * df2 # degree of freedom for both
dft = df1 + df2 + dfw + dfb # degree of freedon both
# Create Anova table
anova = pd.DataFrame(columns=['SS', 'DF', 'MEAN_SUM', 'F'])
anova.index.name = 'SOURCES'
# Fill Anova table
row1 = df2_pivot.columns.names[0].upper()
row2 = df2_pivot.columns.names[1].upper()
row3 = df2_pivot.columns.names[0].upper(
) + ':' + df2_pivot.columns.names[1].upper()
anova.loc[row1, :] = [
round4(ss1v), df1,
round4(round(ss1v / df1, 4)),
round4(round((ss1v / df1) / (ssw / dfw), 4))
]
anova.loc[row2, :] = [
round4(ss2v), df2,
round4(round(ss2v / df2, 4)),
round4(round((ss2v / df2) / (ssw / dfw), 4))
]
anova.loc[row3, :] = [
round4(ssb), dfb,
round4(round(ssb / dfb, 4)),
round4(round((ssb / dfb) / (ssw / dfw), 4))
]
anova.loc['RESIDUAL'.upper(), :] = [
round4(ssw), dfw, round4(round(ssw / dfw, 4)), ''
]
anova.loc['TOTAL'.upper(), :] = [round4(sst), dft, '', '']
# Profile plot preparation
li4 = []
for nums1, li3 in zip(range(1,
len(df2_pivot.columns) + 1),
df2_pivot.columns):
if nums1 == 1:
li4.append(li3[0])
elif nums1 == (len(df2_pivot.columns) / 2) + 1:
li4.append(li3[0])
fig0 = plt.figure(2, figsize=(10, 10))
ax = plt.subplot(111, label='Axes 1')
dmgr_unstack_plt = dmgr.unstack(0)
dmgr_unstack_plt.plot(ax=ax, marker='H')
plt.legend(li4)
plt.tight_layout()
ax.set_ylabel(ylabel='Estimated Marginal means', fontdict={'size': 20})
ax.set_xlabel(xlabel=ax.get_xlabel(), fontdict={'size': 20})
fig0.savefig('plot.png', format='png', bbox_inches='tight')
plt.show()
# change NaN values to '--'
df2_pivot.fillna('--', inplace=True)
# Report creation into Pdf
filename = "ereport.xlsx"
anova.to_excel(filename, sheet_name='Table')
wb = load_workbook(filename)
ws = wb.worksheets[0]
# Setting up border styles
bleftv = styles.Side(style='thin')
brightv = styles.Side(style='thin')
btopv = styles.Side(style='thin')
bbottomv = styles.Side(style='thin')
borderv = styles.borders.Border(bleftv, brightv, btopv, bbottomv)
for cols_cells in ws.columns:
length = max(len(str(cell.value)) for cell in cols_cells) + 5
ws.column_dimensions[cols_cells[0].column_letter].width = length
for cell in cols_cells:
cell.border = borderv
wb.save(filename)
wb.close()
# Generating report to html
reportname = 'report'
def spacefact(df):
max1 = 0
for idf in df.iterrows():
n_len = len(str(idf[0]))
if n_len > max1:
max1 = n_len
return max1
templateLoader = jinja2.FileSystemLoader(searchpath="./")
templateEnv = jinja2.Environment(loader=templateLoader,
extensions=['jinja2.ext.do'])
TEMPLATE_FILE = "template.jinja"
template = templateEnv.get_template(TEMPLATE_FILE)
outputText = template.render(ddf2=ddf2,
df=df2_pivot,
df2=dmgr_unstack_mean,
ano=anova,
zip=zip,
list=list,
len=len,
space=spacefact)
html_file = open(f'{reportname}.html', 'w')
html_file.write(outputText)
html_file.close()
# Converting Html report to pdf
path_wkhtmltopdf = r'C:\Program Files\wkhtmltopdf\bin\wkhtmltopdf.exe'
config = pdfkit.configuration(wkhtmltopdf=path_wkhtmltopdf)
pdfkit.from_file(f'{reportname}.html',
f'{reportname}.pdf',
configuration=config)
os.system(f'cmd /c "explorer {reportname}.pdf"')
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 _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
# #
# If you add a fill type, remember to add it to fill_tbl #
#################################################################
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 #
#################################################################
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)))
_light_blue_style = {
'fill': {
'start_color': 'F7F9FC',
'end_color': 'F7F9FC',
'fill_type': 'solid',
},
'font': {
'name': 'Arial',
'size': 11,
}
}
ADI_FINAL_ROW_STYLE = {
'border': {
'top': styles.Side(style='thin', color='C7C7C7'),
'bottom': styles.Side(style='thin', color='C7C7C7'),
},
'fill': {
'start_color': 'F2F4F7',
'end_color': 'F2F4F7',
'fill_type': 'solid',
},
'font': {
'name': 'Arial',
'size': 11,
}
}
ADI_JOURNAL_FIELDS = {
'upload': {
# -*- 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,
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) + '!'