def format_cell_style(ws, language_dict):
style_int = NamedStyle('int')
style_int.number_format = '0'
style_str = NamedStyle('str')
style_str.number_format = '@'
style_pcnt = NamedStyle('pcnt')
style_pcnt.number_format = '0.0%'
for cell in ws[column_index[language_dict['id']]][1:]:
cell.style = style_int
for cell in ws[column_index[language_dict['total_submitted']]][1:]:
cell.style = style_int
for cell in ws[column_index[language_dict['total_acs']]][1:]:
cell.style = style_int
for cell in ws[column_index[language_dict['title']]][1:]:
cell.style = style_str
for cell in ws[column_index[language_dict['slug']]][1:]:
cell.style = style_str
for cell in ws[column_index[language_dict['difficulty']]][1:]:
cell.style = style_str
for cell in ws[column_index[language_dict['paid_only']]][1:]:
cell.style = style_str
for cell in ws[column_index[language_dict['status']]][1:]:
cell.style = style_str
for cell in ws[column_index[language_dict['acceptance']]][1:]:
cell.style = style_pcnt
def __init__(
self,
lang: str = "FR",
url_base_edit: str = "",
url_base_view: str = "",
# additional
**kwargs,
):
"""Instanciating the output workbook.
:param str lang: selected language for output
:param str url_base_edit: base url to format edit links (basically app.isogeo.com)
:param str url_base_view: base url to format view links (basically open.isogeo.com)
"""
super(Isogeo2xlsx, self).__init__()
# super(Isogeo2xlsx, self).__init__(write_only=True)
self.stats = Stats()
# URLS
utils.app_url = url_base_edit # APP
utils.oc_url = url_base_view # OpenCatalog url
# styles
s_date = NamedStyle(name="date")
s_wrap = NamedStyle(name="wrap")
s_wrap.alignment = Alignment(wrap_text=True)
self.add_named_style(s_date)
self.add_named_style(s_wrap)
# deleting the default worksheet
ws = self.active
self.remove(ws)
# LOCALE
if lang.lower() == "fr":
s_date.number_format = "dd/mm/yyyy"
self.dates_fmt = "DD/MM/YYYY"
self.locale_fmt = "fr_FR"
self.tr = I18N_FR
else:
s_date.number_format = "yyyy/mm/dd"
self.dates_fmt = "YYYY/MM/DD"
self.locale_fmt = "uk_UK"
self.tr = I18N_EN
# FORMATTER
self.fmt = Formatter(output_type="Excel")
def makeStyle (self, name, copyfromcell, sheet=None):
ws = self._getTable (sheet)
newstyle = NamedStyle (name=name)
newstylecell = ws [copyfromcell]
newstyle.font = copy (newstylecell.font)
newstyle.fill = copy (newstylecell.fill)
newstyle.border = copy (newstylecell.border)
newstyle.alignment = copy (newstylecell.alignment)
newstyle.number_format = newstylecell.number_format
newstyle.protection = copy (newstylecell.protection)
return newstyle
def __init__(self, lang: str = "EN", texts=OrderedDict()):
"""TO DOC.
Keyword arguments:
"""
super(MetadataToXlsx, self).__init__()
self.txt = texts
# styles
s_date = NamedStyle(name="date")
s_date.number_format = "dd/mm/yyyy"
s_wrap = NamedStyle(name="wrap")
s_wrap.alignment = Alignment(wrap_text=True)
self.add_named_style(s_date)
self.add_named_style(s_wrap)
# deleting the default worksheet
ws = self.active
self.remove_sheet(ws)
def add_named_style(wb,
name,
number_format=None,
font_name='Calibri',
font_size=10,
font_color='FF000000',
fill=None):
"""Add a named style to the open workbook.
Already named styles are ignored. See add_default_styles for example usage.
"""
if name in wb.named_styles:
return
sty = NamedStyle(name=name)
sty.font = Font(name=font_name, size=font_size, color=font_color)
if fill:
sty.fill = fill
if number_format:
sty.number_format = number_format
wb.add_named_style(sty)
#
# Write Output
#
centered = Alignment(horizontal="center", vertical="center")
bd = Side(border_style="thin")
header_style = NamedStyle(name="header_style")
header_style.alignment = centered
header_style.font = Font(bold=True)
header_style.border = Border(left=bd, right=bd, top=bd, bottom=bd)
data_style = NamedStyle(name="data_style")
# data_style.border = Border(left=bd, right=bd, top=bd, bottom=bd)
data_style.alignment = centered
data_style.number_format = '0.0'
def get_header(data_frame):
header = data_frame.columns.tolist()
header.extend(STAT_HEADER)
return header
def get_n_data_rows(data_frame):
return data_frame.shape[0]
def get_meth_percent_range(header):
"""
Get (first_col_no, last_col_no) of the columns that represent
the methylation percentage measurements.
"""
first_col = header.index('Pos. 1')
def generate_styles(self):
"""Создание базовых стилей
Названия стилей:
'Заголовок 1'
'Заголовок 2'
'Заголовок 3'
'Заголовок 32'
'Заголовок 4'
'Заголовок 42'
'Заголовок 5'
'Заголовок 52'
'Заголовок 6'
'Заголовок 62'
'Заголовок 7'
'Заголовок 8'
'Итоги 1'
'Таблица 1'
'Линейка 1
'Шапка 1'
"""
styles = [
{
"name": "Заголовок 1",
"sc": "F2F2F2",
"ec": "F2F2F2",
"bc": "D9D9D9",
"b": True,
"horAlign": "right",
"bb": "thin",
"ft": "solid",
},
{
"name": "Заголовок 2",
"sc": "D9D9D9",
"ec": "D9D9D9",
"bc": "BFBFBF",
"b": True,
"horAlign": "right",
"bb": "thin",
"ft": "solid",
},
{
"name": "Заголовок 3",
"sc": "8DB4E2",
"ec": "8DB4E2",
"bc": "16365C",
"b": True,
"horAlign": "right",
"bb": "thin",
"ft": "solid",
},
{
"name": "Заголовок 4",
"sc": "B8CCE4",
"ec": "B8CCE4",
"bc": "366092",
"b": True,
"horAlign": "right",
"bb": "thin",
"ft": "solid",
},
{
"name": "Заголовок 5",
"sc": "E6B8B7",
"ec": "E6B8B7",
"bc": "963634",
"b": True,
"horAlign": "right",
"bb": "thin",
"ft": "solid",
},
{
"name": "Заголовок 6",
"sc": "D8E4BC",
"ec": "D8E4BC",
"bc": "76933C",
"b": True,
"horAlign": "right",
"bb": "thin",
"ft": "solid",
},
{
"name": "Заголовок 7",
"sc": "CCC0DA",
"ec": "CCC0DA",
"bc": "60497A",
"b": True,
"horAlign": "right",
"bb": "thin",
"ft": "solid",
},
{
"name": "Заголовок 8",
"sc": "B7DEE8",
"ec": "B7DEE8",
"bc": "31869B",
"b": True,
"horAlign": "right",
"bb": "thin",
"ft": "solid",
},
{
"name": "Заголовок 32",
"sc": "8DB4E2",
"ec": "8DB4E2",
"bc": "16365C",
"b": True,
"horAlign": "right",
"bb": "double",
"bt": "double",
"ft": "solid",
},
{
"name": "Заголовок 42",
"sc": "B8CCE4",
"ec": "B8CCE4",
"bc": "366092",
"b": True,
"horAlign": "right",
"bb": "double",
"bt": "double",
"ft": "solid",
},
{
"name": "Заголовок 52",
"sc": "E6B8B7",
"ec": "E6B8B7",
"bc": "963634",
"b": True,
"horAlign": "right",
"bb": "double",
"bt": "double",
"ft": "solid",
},
{
"name": "Заголовок 62",
"sc": "D8E4BC",
"ec": "D8E4BC",
"bc": "76933C",
"b": True,
"horAlign": "right",
"bb": "double",
"bt": "double",
"ft": "solid",
},
{
"name": "Заголовок 72",
"sc": "CCC0DA",
"ec": "CCC0DA",
"bc": "60497A",
"b": True,
"horAlign": "right",
"bb": "double",
"bt": "double",
"ft": "solid",
},
{
"name": "Заголовок 82",
"sc": "B7DEE8",
"ec": "B7DEE8",
"bc": "31869B",
"b": True,
"horAlign": "right",
"bb": "double",
"bt": "double",
"ft": "solid",
},
{
"name": "Итоги 1",
"sc": "FABF8F",
"ec": "FABF8F",
"bc": "E26B0A",
"b": True,
"horAlign": "right",
"bb": "thin",
"bt": "thick",
"ft": "solid",
},
{
"name": "Таблица 1",
"bl": "thin",
"br": "thin",
"bt": "thin",
"bb": "thin",
"wr": True,
"bc": "808080",
},
{
"name": "Линейка 1",
"bb": "thin",
"wr": True,
"bc": "808080"
},
{
"name": "Шапка 1",
"bl": "thin",
"br": "thin",
"bt": "thin",
"bb": "thin",
"b": True,
},
]
for s in styles:
font = Font(
name=self.font,
size=self.fontsize,
bold=s.get("b", False),
italic=s.get("itl", False),
vertAlign=s.get("vertAlign"),
underline="none",
strike=False,
color=s.get("txtclr", "000000"),
)
fill = PatternFill(fill_type=s.get("ft"),
start_color=s.get("sc"),
end_color=s.get("ec"))
border = Border(
left=Side(border_style=s.get("bl"),
color=s.get("bc", "000000")),
right=Side(border_style=s.get("br"),
color=s.get("bc", "000000")),
top=Side(border_style=s.get("bt"), color=s.get("bc",
"000000")),
bottom=Side(border_style=s.get("bb"),
color=s.get("bc", "000000")),
diagonal=Side(border_style=None, color=s.get("bc", "000000")),
diagonal_direction=0,
outline=Side(border_style=None, color=s.get("bc", "000000")),
vertical=Side(border_style=s.get("bv"),
color=s.get("bc", "000000")),
horizontal=Side(border_style=s.get("bh"),
color=s.get("bc", "000000")),
)
alignment = Alignment(
horizontal=s.get("horAlign", "left"),
vertical="bottom",
text_rotation=0,
wrap_text=s.get("wr", False),
shrink_to_fit=False,
indent=0,
)
tab = NamedStyle(name=s["name"])
tab.font = font
tab.fill = fill
tab.border = border
tab.alignment = alignment
tab.number_format = None
self.wb.add_named_style(tab)
title_style.alignment = Alignment(horizontal='center',
vertical='center',
wrapText=True)
title_style.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
num_style = NamedStyle(name='num_style')
num_style.font = Font(name='微软雅黑', size=11)
num_style.alignment = Alignment(horizontal='center', vertical='center')
num_style.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
num_style.number_format = '0.00'
num_style_fill = NamedStyle(name='num_style_fill')
num_style_fill.font = Font(name='微软雅黑', size=11)
num_style_fill.alignment = Alignment(horizontal='center', vertical='center')
num_style_fill.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
num_style_fill.fill = PatternFill('solid', fgColor='E5E5E5')
num_style_fill.number_format = '0.00'
str_style = NamedStyle(name='str_style')
str_style.font = Font(name='微软雅黑', size=11)
str_style.alignment = Alignment(horizontal='center', vertical='center')
str_style.border = Border(left=Side(style='thin'),
def __init__(self, wb, a=(1, 1)):
'''
Create named styles. Register them with the Workbook wb, and add them to self.styles, a
dict with named style title as a key.
:params wb: The openpyxl Workbook object to register styes into
:params a: A safe place to store the anchor in self.tradeAnchor. Overridable with every
method and function that can use an achor to translate the location in the Workbook.
'''
self.tradeAnchor = a
self.styles = dict()
# This code is subject to change on a whim. Primary importance is easy to find styles and
# consistent layout
# ###################################### titleStyle ######################
titleStyle = NamedStyle(name="titleStyle")
titleStyle.font = Font(color="FFFFFF", size=16)
titleStyle.alignment = Alignment(horizontal="center",
vertical="center",
wrapText=True)
titleStyle.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
titleStyle.border = Border(left=Side(style='double'),
right=Side(style='double'),
top=Side(style='double'),
bottom=Side(style='double'))
self.addNamedStyle(titleStyle, 'titleStyle', wb)
# ###################################### titleLeft #######################
titleLeft = NamedStyle(name="titleLeft")
titleLeft.font = Font(color="FFFFFF", size=16)
titleLeft.alignment = Alignment(horizontal="center",
vertical="center",
wrapText=True)
titleLeft.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
titleLeft.border = Border(left=Side(style='double'),
top=Side(style='double'),
bottom=Side(style='double'))
self.addNamedStyle(titleLeft, 'titleLeft', wb)
# ###################################### titleNumberRight ######################
titleNumberRight = NamedStyle(name="titleNumberRight")
titleNumberRight.font = Font(color="FFFFFF", size=16)
titleNumberRight.alignment = Alignment(horizontal="center",
vertical="center",
wrapText=True)
titleNumberRight.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
titleNumberRight.border = Border(right=Side(style='double'),
top=Side(style='double'),
bottom=Side(style='double'))
titleNumberRight.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)'
self.addNamedStyle(titleNumberRight, 'titleNumberRight', wb)
# ###################################### finalNoteStyle ########################
finalNoteStyle = NamedStyle(name="finalNoteStyle")
finalNoteStyle.font = Font(color="FFFFFF", size=8)
finalNoteStyle.alignment = Alignment(horizontal="left",
vertical="center",
wrap_text=True)
finalNoteStyle.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
finalNoteStyle.border = Border(left=Side(style='double'),
right=Side(style='double'),
top=Side(style='double'),
bottom=Side(style='double'))
self.addNamedStyle(finalNoteStyle, 'finalNoteStyle', wb)
# ###################################### titleRight ############################
titleRight = NamedStyle(name="titleRight")
titleRight.font = Font(color="FFFFFF", size=16)
titleRight.alignment = Alignment(horizontal="center",
vertical="center",
wrapText=True)
titleRight.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
titleRight.border = Border(right=Side(style='double'),
top=Side(style='double'),
bottom=Side(style='double'))
# titleRight.number_format = '"$"#,##0.00_);[Red]\("$"#,##0.00\)'
self.addNamedStyle(titleRight, 'titleRight', wb)
# ###################################### normStyle #############################
normStyle = NamedStyle(name="normStyle")
normStyle.font = Font(color="FFFFFF", size=11)
normStyle.alignment = Alignment(horizontal="left",
vertical="center",
wrap_text=True)
normStyle.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normStyle.border = Border(left=Side(style='double'),
right=Side(style='double'),
top=Side(style='double'),
bottom=Side(style='double'))
normStyle.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)'
self.addNamedStyle(normStyle, 'normStyle', wb)
# ###################################### normalNumber ##########################
normalNumber = NamedStyle(name="normalNumber")
normalNumber.font = Font(color="FFFFFF", size=11)
normalNumber.alignment = Alignment(horizontal="left",
vertical="bottom")
normalNumber.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normalNumber.border = Border(left=Side(style='double'),
right=Side(style='double'),
top=Side(style='double'),
bottom=Side(style='double'))
normalNumber.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)'
self.addNamedStyle(normalNumber, 'normalNumber', wb)
# ###################################### normalFraction ########################
normalFraction = NamedStyle(name="normalFraction")
normalFraction.font = Font(color="FFFFFF", size=11)
normalFraction.alignment = Alignment(horizontal="left",
vertical="bottom")
normalFraction.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normalFraction.border = Border(left=Side(style='double'),
right=Side(style='double'),
top=Side(style='double'),
bottom=Side(style='double'))
normalFraction.number_format = '# ??/16'
self.addNamedStyle(normalFraction, 'normalFraction', wb)
# ###################################### linkStyle ##############################
linkStyle = NamedStyle(name="linkStyle")
linkStyle.font = Font(color="FFFFFF", size=16)
linkStyle.alignment = Alignment(horizontal="center",
vertical="center",
wrapText=True)
linkStyle.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
linkStyle.border = Border(left=Side(style='double'),
right=Side(style='double'),
top=Side(style='double'),
bottom=Side(style='double'))
self.addNamedStyle(linkStyle, 'linkStyle', wb)
# ###################################### normalNumberTopLeft ###################
normalNumberTopLeft = NamedStyle(name="normalNumberTopLeft")
normalNumberTopLeft.font = Font(color="FFFFFF", size=11)
normalNumberTopLeft.alignment = Alignment(horizontal="left",
vertical="bottom")
normalNumberTopLeft.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normalNumberTopLeft.border = Border(left=Side(style='double'),
right=Side(style='thin'),
top=Side(style='double'),
bottom=Side(style='thin'))
normalNumberTopLeft.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)'
self.addNamedStyle(normalNumberTopLeft, 'normalNumberTopLeft', wb)
# ###################################### normalNumberTop #######################
normalNumberTop = NamedStyle(name="normalNumberTop")
normalNumberTop.font = Font(color="FFFFFF", size=11)
normalNumberTop.alignment = Alignment(horizontal="left",
vertical="bottom")
normalNumberTop.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normalNumberTop.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='double'),
bottom=Side(style='thin'))
normalNumberTop.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)'
self.addNamedStyle(normalNumberTop, 'normalNumberTop', wb)
# ###################################### normalNumberTopRight ##################
normalNumberTopRight = NamedStyle(name="normalNumberTopRight")
normalNumberTopRight.font = Font(color="FFFFFF", size=11)
normalNumberTopRight.alignment = Alignment(horizontal="left",
vertical="bottom")
normalNumberTopRight.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normalNumberTopRight.border = Border(left=Side(style='thin'),
right=Side(style='double'),
top=Side(style='double'),
bottom=Side(style='thin'))
normalNumberTopRight.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)'
self.addNamedStyle(normalNumberTopRight, 'normalNumberTopRight', wb)
# ###################################### normalNumberLeft ######################
normalNumberLeft = NamedStyle(name="normalNumberLeft")
normalNumberLeft.font = Font(color="FFFFFF", size=11)
normalNumberLeft.alignment = Alignment(horizontal="left",
vertical="bottom")
normalNumberLeft.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normalNumberLeft.border = Border(left=Side(style='double'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
normalNumberLeft.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)'
self.addNamedStyle(normalNumberLeft, 'normalNumberLeft', wb)
# ###################################### normalNumberRight #####################
normalNumberRight = NamedStyle(name="normalNumberRight")
normalNumberRight.font = Font(color="FFFFFF", size=11)
normalNumberRight.alignment = Alignment(horizontal="left",
vertical="bottom")
normalNumberRight.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normalNumberRight.border = Border(left=Side(style='thin'),
right=Side(style='double'),
top=Side(style='thin'),
bottom=Side(style='thin'))
normalNumberRight.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)'
self.addNamedStyle(normalNumberRight, 'normalNumberRight', wb)
# ###################################### normalNumberInside ####################
normalNumberInside = NamedStyle(name="normalNumberInside")
normalNumberInside.font = Font(color="FFFFFF", size=11)
normalNumberInside.alignment = Alignment(horizontal="left",
vertical="bottom")
normalNumberInside.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normalNumberInside.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
normalNumberInside.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)'
self.addNamedStyle(normalNumberInside, 'normalNumberInside', wb)
# ###################################### timeSubLeft #########################
timeSubLeft = NamedStyle(name="timeSubLeft")
timeSubLeft.font = Font(color="FABF8F", size=11)
timeSubLeft.alignment = Alignment(horizontal="left", vertical="bottom")
timeSubLeft.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
timeSubLeft.border = Border(left=Side(style='double'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
timeSubLeft.number_format = 'h:mm:ss'
self.addNamedStyle(timeSubLeft, 'timeSubLeft', wb)
# ###################################### timeSub #############################
timeSub = NamedStyle(name="timeSub")
timeSub.font = Font(color="FABF8F", size=11)
timeSub.alignment = Alignment(horizontal="left", vertical="bottom")
timeSub.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
timeSub.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
timeSub.number_format = 'h:mm:ss'
self.addNamedStyle(timeSub, 'timeSub', wb)
# ###################################### timeSubRight ########################
timeSubRight = NamedStyle(name="timeSubRight")
timeSubRight.font = Font(color="FABF8F", size=11)
timeSubRight.alignment = Alignment(horizontal="left",
vertical="bottom")
timeSubRight.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
timeSubRight.border = Border(left=Side(style='thin'),
right=Side(style='double'),
top=Side(style='thin'),
bottom=Side(style='thin'))
# normalSubLeft.number_format = '"$"#,##0.00_);[Red]\("$"#,##0.00\)'
self.addNamedStyle(timeSubRight, 'timeSubRight', wb)
# ###################################### normalSubLeft #########################
normalSubLeft = NamedStyle(name="normalSubLeft")
normalSubLeft.font = Font(color="FABF8F", size=11)
normalSubLeft.alignment = Alignment(horizontal="left",
vertical="bottom")
normalSubLeft.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normalSubLeft.border = Border(left=Side(style='double'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
self.addNamedStyle(normalSubLeft, 'normalSubLeft', wb)
# ###################################### normalSub #############################
normalSub = NamedStyle(name="normalSub")
normalSub.font = Font(color="FABF8F", size=11)
normalSub.alignment = Alignment(horizontal="left", vertical="bottom")
normalSub.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normalSub.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
self.addNamedStyle(normalSub, 'normalSub', wb)
# ###################################### normalSubRight ########################
normalSubRight = NamedStyle(name="normalSubRight")
normalSubRight.font = Font(color="FABF8F", size=11)
normalSubRight.alignment = Alignment(horizontal="left",
vertical="bottom")
normalSubRight.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normalSubRight.border = Border(left=Side(style='thin'),
right=Side(style='double'),
top=Side(style='thin'),
bottom=Side(style='thin'))
# normalSubLeft.number_format = '"$"#,##0.00_);[Red]\("$"#,##0.00\)'
self.addNamedStyle(normalSubRight, 'normalSubRight', wb)
# ###################################### normalSubNumberBottomLeft #############
normalSubNumberBottomLeft = NamedStyle(
name="normalSubNumberBottomLeft")
normalSubNumberBottomLeft.font = Font(color="FABF8F", size=11)
normalSubNumberBottomLeft.alignment = Alignment(horizontal="left",
vertical="bottom")
normalSubNumberBottomLeft.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normalSubNumberBottomLeft.border = Border(left=Side(style='double'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='double'))
normalSubNumberBottomLeft.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)'
self.addNamedStyle(normalSubNumberBottomLeft,
'normalSubNumberBottomLeft', wb)
# ###################################### normalSubNumberBottom #################
normalSubNumberBottom = NamedStyle(name="normalSubNumberBottom")
normalSubNumberBottom.font = Font(color="FABF8F", size=11)
normalSubNumberBottom.alignment = Alignment(horizontal="left",
vertical="bottom")
normalSubNumberBottom.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normalSubNumberBottom.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='double'))
normalSubNumberBottom.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)'
self.addNamedStyle(normalSubNumberBottom, 'normalSubNumberBottom', wb)
# ###################################### normalSubNumberBottomRight ############
normalSubNumberBottomRight = NamedStyle(
name="normalSubNumberBottomRight")
normalSubNumberBottomRight.font = Font(color="FABF8F", size=11)
normalSubNumberBottomRight.alignment = Alignment(horizontal="left",
vertical="bottom")
normalSubNumberBottomRight.fill = PatternFill(start_color='A6A6A6',
end_color='A6A6A6',
fill_type='solid')
normalSubNumberBottomRight.border = Border(left=Side(style='thin'),
right=Side(style='double'),
top=Side(style='thin'),
bottom=Side(style='double'))
normalSubNumberBottomRight.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)'
self.addNamedStyle(normalSubNumberBottomRight,
'normalSubNumberBottomRight', wb)
# ###################################### explain ###############################
explain = NamedStyle(name="explain")
explain.font = Font(color="000000", size=10)
explain.alignment = Alignment(horizontal="left",
vertical="top",
wrapText=True)
explain.fill = PatternFill(start_color='FFFFCC',
end_color='FFFFCC',
fill_type='solid')
explain.border = Border(left=Side(style='double'),
right=Side(style='double'),
top=Side(style='double'),
bottom=Side(style='double'))
self.addNamedStyle(explain, 'explain', wb)
# ###################################### noteStyle #############################
noteStyle = NamedStyle(name="noteStyle")
noteStyle.font = Font(color="E26B0A", size=10)
noteStyle.alignment = Alignment(horizontal="left",
vertical="top",
wrapText=True)
noteStyle.fill = PatternFill(start_color='FFFFCC',
end_color='FFFFCC',
fill_type='solid')
noteStyle.border = Border(left=Side(style='double'),
right=Side(style='double'),
top=Side(style='double'),
bottom=Side(style='double'))
self.addNamedStyle(noteStyle, 'noteStyle', wb)
def write_file(self, order_details):
if self.delete_file_if_exists():
order_wb = Workbook()
order_ws = order_wb.active
# set sheet name
order_ws.title = "Order Data (2014-2016)"
# defining column styling
header_style = NamedStyle(name="headerStyle")
header_style.font = Font(bold=True)
date_style = NamedStyle(name="dateStyle")
date_style.number_format = 'MM/DD/YYYY'
amount_style = NamedStyle(name="amountStyle")
amount_style.number_format = u'_-"€"* #,##0.00_-;-"€"* #,##0.00_-;_-"€"* "-"??_-;_-@_-'
order_ws.append([
"Contact Name", "Company", "Address 1", "County",
"Country Code", "Phone", "Amount", "Order Date"
])
# applying styles to header columns
order_ws["A1"].style = header_style
order_ws.column_dimensions["A"].width = 25
order_ws["B1"].style = header_style
order_ws.column_dimensions["B"].width = 25
order_ws["C1"].style = header_style
order_ws.column_dimensions["C"].width = 40
order_ws["D1"].style = header_style
order_ws.column_dimensions["D"].width = 15
order_ws["E1"].style = header_style
order_ws.column_dimensions["E"].width = 12
order_ws["F1"].style = header_style
order_ws.column_dimensions["F"].width = 10
order_ws["G1"].style = header_style
order_ws.column_dimensions["G"].width = 14
order_ws["H1"].style = header_style
order_ws.column_dimensions["H"].width = 12
max_count = len(order_details)
row_index = 1
for index in range(1, max_count, 1):
order = order_details[index]
# print vars(order)
# adding only valid rows to sheet
if order.is_valid:
row = (order.contact_name, order.company, order.address1,
order.county, order.country_code, order.phone,
order.amount, order.order_date)
order_ws.append(row)
# applying styles to Order Date and Amount columns
order_ws["H" + str(row_index + 1)].style = date_style
order_ws["G" + str(row_index + 1)].style = amount_style
row_index += 1
order_wb.save(self.file_name)
else:
print(
"Unable to delete file. Please close the file if it is already open."
)
def exam_scores(exam_id):
# Читаем данные из базы данных
exam = Exam.objects.select_related('course').get(id=exam_id)
group = exam.course.group
exam_points = exam.exammarks_set.all().order_by('student__FIO')
semester = exam.course.discipline_detail.semester.name
if int(semester) % 2 == 1:
additional = int(semester) // 2
else:
additional = (int(semester) - 1) // 2
edu_period = EduPeriod.objects.get(begin_year__year=group.begin_year.year + additional)
# Открываем шаблон
app_dir = os.path.dirname(os.path.abspath(__file__))
template = os.path.join(app_dir, 'exam_scores.xlsx')
workbook = load_workbook(template)
ws = workbook.active
# Семестр
ws['A5'] = ws['L5'] = 'Семестр: {}, {} – {} уч.г.'.format(
semester,
edu_period.begin_year.year,
edu_period.end_year.year,
)
# Форма контроля
ws['A6'] = ws['L6'] = 'Форма контроля: {}, курс: {}, группа: {}'.format(
exam.get_controlType_display(),
group.year,
group.name,
)
# Дисциплина
ws['A7'] = ws['L7'] = 'Дисциплина: ' + exam.course.discipline_detail.discipline.name
# ФИО преподавателя
ws['A8'] = ws['L8'] = 'Фамилия, имя, отчество преподавателя: ' + exam.course.lecturer.FIO
# Дата
ws['A9'] = ws['L9'] = 'Дата проведения зачета/экзамена: {:%d.%m.%Y}'.format(exam.examDate)
# Таблица с баллами
summary = {ExamMarks.MARKS[0][0]: 0, ExamMarks.MARKS[1][0]: 0, ExamMarks.MARKS[2][0]: 0, ExamMarks.MARKS[3][0]: 0,
ExamMarks.MARKS[4][0]: 0, ExamMarks.MARKS[5][0]: 0, ExamMarks.MARKS[6][0]: 0, ExamMarks.MARKS[7][0]: 0,
ExamMarks.MARKS[8][0]: 0, ExamMarks.MARKS[9][0]: 0}
k = 0
for points in exam_points:
k += 1
ws.insert_rows(11 + k)
ws.row_dimensions[11 + k].height = 30
row = str(11 + k)
ws['A' + row] = ws['L' + row] = str(k)
ws['B' + row] = ws['M' + row] = points.student.FIO
ws['C' + row] = ws['N' + row] = points.student.student_id
ws['D' + row] = ws['O' + row] = points.inPoints + points.additional_points
ws['E' + row] = ws['P' + row] = points.examPoints
ws['F' + row] = ws['Q' + row] = points.total_points
ws['G' + row] = ws['R' + row] = points.get_mark_display()
ws['H' + row] = ws['S' + row] = points.mark_symbol
summary[points.mark]+=1
# Стиль для ячеек таблицы
solid_line = Side(style='thin', color='000000')
cell_style = NamedStyle(name='cell_style')
cell_style.alignment.horizontal = 'center'
cell_style.alignment.vertical = 'center'
cell_style.alignment.wrapText = Bool(True)
cell_style.border = Border(left=solid_line, right=solid_line, top=solid_line, bottom=solid_line)
cell_style.font = Font(name='Arial', size=9)
cell_style.number_format = '#.0'
# Применяем стили к таблице
for i in range(12, k + 12):
for j in range(1, 10):
ws.cell(row=i, column=j).style = ws.cell(row=i, column=j+11).style = cell_style
ws['C' + str(k + 14)] = ws['N' + str(k + 14)] = summary[6]
ws['C' + str(k + 15)] = ws['N' + str(k + 15)] = summary[7]
ws['C' + str(k + 16)] = ws['N' + str(k + 16)] = summary[8]
ws['C' + str(k + 17)] = ws['N' + str(k + 17)] = summary[5]
ws['C' + str(k + 18)] = ws['N' + str(k + 18)] = summary[4]
ws['C' + str(k + 19)] = ws['N' + str(k + 19)] = summary[3]
ws['C' + str(k + 20)] = ws['N' + str(k + 20)] = summary[2]
ws['C' + str(k + 21)] = ws['N' + str(k + 21)] = summary[0]
# Суммы баллов и буквенные эквиваленты оценки
for i in range(8):
row = str(14 + k + i)
ws.merge_cells('E' + row + ':F' + row)
ws.merge_cells('P' + row + ':Q' + row)
ws.merge_cells('G' + row + ':I' + row)
ws.merge_cells('R' + row + ':T' + row)
ws['E' + row].style = ws['F' + row].style = ws['G' + row].style = \
ws['H' + row].style = ws['I' + row].style = ws['P' + row].style = \
ws['Q' + row].style = ws['R' + row].style = ws['S' + row].style = \
ws['T' + row].style = cell_style
# Подпись директора
row = str(24 + k)
ws.merge_cells('A' + row + ':I' + row)
ws.merge_cells('L' + row + ':T' + row)
ws.print_area = 'A1:T' + str(row)
ws.page_setup = PrintPageSetup(worksheet=ws)
ws.page_setup.paperSize = '9'
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.fitToHeight = True
ws.page_setup.fitToWidth = True
ws.page_setup.fitToPage = True
ws.sheet_properties.pageSetUpPr.fitToPage = True
return workbook
# Alapértelmezett változók
olvasando_fajl = "sms_full.xml"
logging.info('A beolvasando file: ' + olvasando_fajl)
kiirando_fajl = "balance.xlsx"
logging.info('A kiírandó file: ' + kiirando_fajl)
arfolyam_file = "arfolyamok.csv"
logging.info('A használt árfolyamok file: ' + arfolyam_file)
arfolyam = {}
egyenleg2 = ""
# xml fájl betöltése
mydoc = minidom.parse(olvasando_fajl)
# Munkalap stílusának beálítása forintra
still = NamedStyle(name="Pénzecske") # Ilyen nevű stílus hozzáadása
still.number_format = '#,##0 "HUF";-#,##0 "HUF"' # A számformátum beállítása forintra
diff_style = DifferentialStyle(fill=PatternFill(
bgColor='C6EFCE', fgColor='006100')) # A feltételes formázás beállítása
rule = Rule(type="expression", dxf=diff_style) # Feltételes kifejezés megadása
rule.formula = ["$B2>0"] # Formula a feltételes formázáshoz
# Árfolyam file meglétének ellenőrzése és frissítése ha két óránál régebbi
fileido = datetime.fromtimestamp(os.stat(
'./arfolyamok.csv').st_ctime) # a meglévő file idejének lekérdezése
now = datetime.now()
max_delay = timedelta(hours=2)
try:
if now - fileido > max_delay:
shutil.copyfile('arfolyamok.csv', 'arfolyamok.old')
logging.debug(
"Régi a fájl ezért lekérdezem az árfolyamokat: {} ".format(
def cell_style(
*,
wb: Workbook,
style_name: str = 'cell_style',
font_name: Optional[str] = 'Lucida Sans',
font_size: Optional[int] = 11,
font_bold: Optional[bool] = True,
font_colour: Optional[str] = '000000',
horizontal_alignment: Optional[str] = 'center',
vertical_alignment: Optional[str] = 'center',
wrap_text: Union[str, bool] = None,
fill_type: Union[str, bool] = 'solid',
foreground_colour: Union[str, bool] = 'd9d9d9',
border_style: Union[str, bool] = None,
border_colour: Union[str, bool] = None,
number_format: Union[str, bool] = None
) -> NamedStyle:
"""
Define a cell style
Parameters
----------
wb : Workbook
The workbook in which to define the cell style.
style_name : str = 'cell_style'
The name for the cell style.
font_name : Optional[str] = 'Lucida Sans'
The font name for the style.
font_size : Optional[int] = 11
The font size for the style.
font_bold : Optional[bool] = True
A boolean or string to apply bold style.
font_colour : Optional[str] = 'ffffff'
The string for the font colour.
horizontal_alignment : Optional[str] = 'center'
The string for horizontal alignment.
vertical_alignment : Optional[str] = 'center'
The string for vertical alignment.
wrap_text : Union[str, bool] = None
A boolean or string to wrap text.
fill_type : Optional[str] = 'solid'
The string for the fill type.
foreground_colour : Optional[str] = 'd9d9d9'
The string for the foreground colour.
border_style : Union[str, bool] = None
A boolean or string to apply a border.
border_colour : Union[str, bool] = None
A boolean or string to apply a border colour.
number_format : Union[str, bool] = None
A boolean or string to apply a number format.
Returns
-------
row_style : NamedStyle
The named style.
Example
-------
>>> red_cell_style = ds.cell_style(
>>> style_name='red_cell_style',
>>> font_colour='ffffff',
>>> foreground_colour='c00000'
>>> )
>>> wb.add_named_style(red_cell_style)
>>> for cell in ['C1', 'D1', 'E1']:
>>> ws[cell].style = red_cell_style
"""
cell_style = NamedStyle(name=style_name)
cell_style.font = Font(
name=font_name,
size=font_size,
bold=font_bold,
color=font_colour
)
cell_style.alignment = Alignment(
horizontal=horizontal_alignment,
vertical=vertical_alignment,
wrap_text=wrap_text
)
cell_style.fill = PatternFill(
fill_type=fill_type,
fgColor=foreground_colour
)
cell_style.border = Border(
bottom=Side(
border_style=border_style,
color=border_colour
)
)
cell_style.number_format = number_format
wb.add_named_style(cell_style)
return (wb, cell_style)
def aplicarFormatos():
# estilo de borde
thin = Side(border_style="thin", color="000000")
double = Side(border_style="double", color="ff0000")
# color de celda
my_red = openpyxl.styles.colors.Color(rgb='F2F2F2')
my_fill = openpyxl.styles.fills.PatternFill(patternType='solid',
fgColor=my_red)
# elementos de formato
normal = NamedStyle(name="normal")
normal.font = Font(bold=False, size=11, name="verdana")
normal.number_format = numbers.FORMAT_GENERAL
normal.alignment.wrap_text = False
# 'bottom', 'top', 'distributed', 'justify', 'center'
normal.alignment.vertical = "center"
normal.alignment.horizontal = "center"
normal.border = Border(top=thin, left=thin, right=thin, bottom=thin)
# elementos de formato
bold = NamedStyle(name="bold")
bold.font = Font(bold=True, size=11, name="verdana")
bold.number_format = numbers.FORMAT_GENERAL
bold.alignment.wrap_text = False
# 'bottom', 'top', 'distributed', 'justify', 'center'
bold.alignment.vertical = "center"
bold.alignment.horizontal = "center"
bold.border = Border(top=thin, left=thin, right=thin, bottom=thin)
# formato de fecha
fecha = NamedStyle(name="fecha")
fecha.font = Font(bold=True, size=11, name="verdana")
fecha.number_format = numbers.FORMAT_DATE_DMYSLASH
fecha.alignment.wrap_text = False
# 'bottom', 'top', 'distributed', 'justify', 'center'
fecha.alignment.vertical = "center"
fecha.alignment.horizontal = "center"
fecha.border = Border(top=thin, left=thin, right=thin, bottom=thin)
# formato de numero
money = NamedStyle(name="money")
money.font = Font(bold=False, size=11, name="verdana")
money.number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE
money.alignment.wrap_text = False
# 'bottom', 'top', 'distributed', 'justify', 'center'
money.alignment.vertical = "center"
money.alignment.horizontal = "center"
money.border = Border(top=thin, left=thin, right=thin, bottom=thin)
# iterar por todas las filas
for i, row in enumerate(ws.iter_rows(ws.min_row, ws.max_row)):
# print(i)
ws.row_dimensions[i + 1].height = 40
# print(row[0])
# iterar en cada columna de la fila
for cell in row:
column_number = cell.column
column_letter = get_column_letter(column_number)
# print(column_letter)
# print(cell)
cell.style = normal
cell.fill = my_fill
if column_letter == "A":
cell.style = fecha
cell.fill = my_fill
elif (column_letter == "H") or (column_letter == "I"):
cell.style = money
cell.fill = my_fill
elif (column_letter == "L") or (column_letter == "J"):
cell.style = bold
cell.fill = my_fill
return
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
# format header cells
header = NamedStyle(name="header")
header.font = Font(bold=True, color='FFFFFFFF')
header.alignment = Alignment(horizontal="center", vertical="center")
header.height = 25
header.fill = time_head_fill
header.border = thin_border
# format numeric cells
number_cell = NamedStyle(name="number_cell")
number_cell.alignment = Alignment(horizontal="center", vertical="center")
number_cell.number_format = '#,##0.000'
number_cell.border = thin_border
# format generic text cells
text_cell = NamedStyle(name="text_cell")
text_cell.alignment = Alignment(horizontal="center", vertical="center")
text_cell.border = thin_border
# open excel file and ammend styling as required
workbook = openpyxl.load_workbook('temp_excelfile.xlsx')
# sequentially format all sheets containing tables
for sheet_name in final_df_names:
current_sheet = workbook[sheet_name]
print(f"Currently editing sheet {sheet_name}")
def excel_export(user_inputs, ten_table_data, bar_chart_x_axis_values,
bar_chart_y_axis_values):
wb = Workbook()
# ---- create styles/formats to be used later ----
# for applicable numerical values
comma_no_decimal_style = NamedStyle(name='comma_no_decimal_style')
comma_no_decimal_style.number_format = '#,##0'
comma_no_decimal_style.alignment = Alignment(
horizontal='left') # left align like text is by default in Excel
# for table header
ten_table_header_style = NamedStyle(name='ten_table_header_style')
ten_table_header_style.font = Font(bold=True)
ten_table_header_style.fill = PatternFill(start_color='8DB4E2',
end_color='8DB4E2',
fill_type='solid')
ten_table_header_style.border = Border(top=Side(border_style=BORDER_THIN,
color='00000000'),
bottom=Side(
border_style=BORDER_THIN,
color='00000000'),
left=Side(border_style=BORDER_THIN,
color='00000000'),
right=Side(border_style=BORDER_THIN,
color='00000000'))
# for table title
ten_table_title_style = NamedStyle(name='ten_table_title_style')
ten_table_title_style.font = Font(size=18, bold=True)
ten_table_title_style.alignment = Alignment(horizontal='center')
# -----------------------INPUT TAB-----------------------
ws_input = wb.active
ws_input.title = 'input'
# provide user selections
for row, label in enumerate([
'Rank Position', 'Rank By', 'State', 'City', 'Zip Code',
'Place of Service', 'Provider Type', 'Credential', 'HCPCS Code'
],
start=1):
ws_input.cell(column=1, row=row, value=label).font = Font(bold=True)
ws_input.cell(
column=2,
row=1,
value=', '.join(user_inputs['rank_position'])
if 'rank_position' in user_inputs else ''
) # (rank position should always be in user inputs, since it's required)
ws_input.cell(
column=2,
row=2,
value=', '.join(user_inputs['rank_by'])
if 'rank_by' in user_inputs else
'') # (rank by should always be in user inputs, since it's required)
ws_input.cell(column=2,
row=3,
value=', '.join(user_inputs['state'])
if 'state' in user_inputs else '(all)')
ws_input.cell(column=2,
row=4,
value=', '.join(user_inputs['city'])
if 'city' in user_inputs else '(all)')
ws_input.cell(column=2,
row=5,
value=', '.join(user_inputs['zip_code'])
if 'zip_code' in user_inputs else '(all)')
ws_input.cell(column=2,
row=6,
value=', '.join(user_inputs['place_of_service'])
if 'place_of_service' in user_inputs else '(all)')
ws_input.cell(column=2,
row=7,
value=', '.join(user_inputs['provider_type'])
if 'provider_type' in user_inputs else '(all)')
ws_input.cell(column=2,
row=8,
value=', '.join(user_inputs['credential'])
if 'credential' in user_inputs else '(all)')
ws_input.cell(column=2,
row=9,
value=', '.join(user_inputs['hcpcs_code'])
if 'hcpcs_code' in user_inputs else '(all)')
for col in ['A', 'B']:
ws_input.column_dimensions[col].width = 20
# -----------------------DATA TAB-----------------------
ws_data = wb.create_sheet(title='data')
# ----provide table data----
for col, label in enumerate(
['Provider ID', 'Patients', 'Avg Charged', 'Avg Allowed', 'Avg Paid'],
start=1):
ws_data.cell(column=col, row=1, value=label)
for row, data in enumerate(ten_table_data, start=2):
ws_data.cell(column=1, row=row, value=data['provider_id'])
ws_data.cell(
column=2, row=row, value=int(data['patients'].replace(',', ''))
).style = comma_no_decimal_style # transforming string into number (so user can more easily perform calculations in Excel) and formatting
ws_data.cell(column=3,
row=row,
value=int(data['avg_charged'].replace(
',', ''))).style = comma_no_decimal_style
ws_data.cell(column=4,
row=row,
value=int(data['avg_allowed'].replace(
',', ''))).style = comma_no_decimal_style
ws_data.cell(column=5,
row=row,
value=int(data['avg_paid'].replace(
',', ''))).style = comma_no_decimal_style
# ----provide chart data----
ws_data.cell(column=8, row=1, value='Bar Chart')
for row, x in enumerate(bar_chart_x_axis_values, start=2):
ws_data.cell(column=8, row=row, value=x)
for row, y in enumerate(bar_chart_y_axis_values, start=2):
ws_data.cell(column=9, row=row, value=y).style = comma_no_decimal_style
# set column width and bold headings for table & chart data
for col in ['A', 'B', 'C', 'D', 'E', 'H', 'I']:
ws_data.column_dimensions[col].width = 16
ws_data[f'{col}1'].font = Font(bold=True)
# -----------------------RESULTS TAB-----------------------
ws_results = wb.create_sheet(title='results')
# ---- create and format table ----
ten_table_title = f"{user_inputs['rank_position'][0]} 10 Providers by {'Number of Patients' if user_inputs['rank_by'][0] == 'Patients' else 'Average Charged Amount'}"
ws_results.merge_cells('A1:E1')
ws_results.cell(column=1, row=1,
value=ten_table_title).style = ten_table_title_style
for col, label in enumerate(
['Provider ID', 'Patients', 'Avg Charged', 'Avg Allowed', 'Avg Paid'],
start=1):
ws_results.cell(column=col, row=2,
value=label).style = ten_table_header_style
for row in range(3, len(ten_table_data) + 3):
ws_results.cell(column=1, row=row, value=f"=data!A{row-1}")
ws_results.cell(column=2, row=row,
value=f"=data!B{row-1}").style = comma_no_decimal_style
ws_results.cell(column=3, row=row,
value=f"=data!C{row-1}").style = comma_no_decimal_style
ws_results.cell(column=4, row=row,
value=f"=data!D{row-1}").style = comma_no_decimal_style
ws_results.cell(column=5, row=row,
value=f"=data!E{row-1}").style = comma_no_decimal_style
ten_table = Table(
displayName="TenTable",
ref=f'A3:E{len(ten_table_data)+2}',
headerRowCount=0
) # didn't specify a header so that default column filters aren't created
ten_table.tableStyleInfo = TableStyleInfo(name="TableStyleLight15",
showRowStripes=True)
ws_results.add_table(ten_table)
for col in ['A', 'B', 'C', 'D', 'E']:
ws_results.column_dimensions[col].width = 16
# ---- create bar chart ----
bar_chart = BarChart()
bar_chart.type = 'col'
bar_chart.style = 10
bar_chart.title = 'Total Patients by Average Charged Amount'
bar_chart.y_axis.title = 'Patients'
bar_chart.x_axis.title = 'Avg Charged'
bar_chart.add_data(Reference(ws_data,
min_col=9,
max_col=9,
min_row=2,
max_row=len(bar_chart_y_axis_values) + 1),
titles_from_data=False)
bar_chart.set_categories(
Reference(ws_data,
min_col=8,
max_col=8,
min_row=2,
max_row=len(bar_chart_x_axis_values) + 1))
bar_chart.shape = 4
bar_chart.legend = None
bar_chart.height = 9
bar_chart.width = 18
ws_results.add_chart(bar_chart, 'H1')
# ---- add footnotes ----
footnote1 = "Based on 2017 Medicare Provider Utilization and Payment Data accessed December 18, 2019 from data.cms.gov: https://data.cms.gov/Medicare-Physician-Supplier/Medicare-Provider-Utilization-and-Payment-Data-Phy/fs4p-t5eq."
footnote2 = "Only providers listed as individuals and their services within the 50 states/DC are included. Please note that Provider ID is a generated number created by the developer in an effort to de-identify providers."
footnote3 = "Additional data cleaning/transformations on the data set were performed as needed at the sole discretion of the developer."
footnote4 = "The specific data shown in the above table & graph is based on user selections (see input tab) in the web application."
ws_results.cell(column=1, row=20,
value='Source Data:').font = Font(size=9,
underline='single')
for i, note in enumerate([footnote1, footnote2, footnote3], start=1):
ws_results.cell(column=1, row=20 + i, value=note).font = Font(size=9)
ws_results.cell(column=1, row=25, value=footnote4).font = Font(
size=9
) # writes a couple of lines down from the previous footnote in order to leave a blank line between this last footnote and the prior ones
# set the results sheet (which is at index 2) as the active worksheet so that the user will be on this sheet when opening the file
wb.active = 2 # the active worksheet is now the one at index 2
# save workbook to a stream (vs. saving to a file on disk) to later send to user
excel_stream = io.BytesIO()
wb.save(excel_stream)
excel_stream.seek(0) # go to the beginning of the stream
return excel_stream
def nanodrop():
if request.method == 'GET':
return render_template('nanodrop/form.html')
#
# Validation
#
# Otherwise validate the form on a POST request and process uploaded files
f = request.files.getlist('input')
ref = request.files.getlist('reference')
# Throw error if one of the required files are missing
if not f[0]:
flash('A required field is missing', 'alert-warning')
return render_template('nanodrop/form.html')
# Validate numeric fields
try:
# Acceptance criteria limits
od_low = float(request.form.get("od_min")) if request.form.get("od_min") else 1.7
od_high = float(request.form.get("od_max")) if request.form.get("od_max") else 2.3
conc_low = float(request.form.get("conc_min")) if request.form.get("conc_min") else 80
conc_high = float(request.form.get("conc_max")) if request.form.get("conc_max") else 120
except:
flash('Please make sure that the optional fields are all numbers')
return render_template('nanodrop/form.html')
# Validate and save input file
filename = f[0].filename
filefront, extension = os.path.splitext(filename)
if not extension == '.xls' and not extension == '.xlsx' and not extension == '.xml':
flash('Only .xls, .xlsx, and .xml files are allowed.', 'alert-warning')
return render_template('nanodrop/form.html')
# Throw error if more than one reference list was provided
if len(ref) > 1:
flash('Only one reference file should be selected', 'alert-warning')
return render_template('nanodrop/form.html')
# If a reference file was uploaded...
if ref[0]:
# Validate the file object.
ref_filename = ref[0].filename.replace(' ', '_')
ref_filefront, ref_extension = os.path.splitext(ref_filename)
if not ref_extension == '.xlsx':
flash('Only .xlsx files are allowed.', 'alert-warning')
return render_template('nanodrop/form.html')
# Save the file
ref_file_path = os.path.join(app.config['UPLOAD_FOLDER'], ref_filename)
ref[0].save(ref_file_path)
os.chmod(ref_file_path, stat.S_IRWXO | stat.S_IRWXG | stat.S_IRWXU)
list_ref = load_workbook(ref_file_path)
else:
list_ref = None
ref_file_path = ''
#
# Prepare data
#
# Save the raw data file (to delete at the end of the program
path = os.path.join(app.config['UPLOAD_FOLDER'], f[0].filename)
f[0].save(path)
# Convert the file in f to xlsx so that openpyxl can work with them
if extension == '.xls':
csv_to_xlsx(path)
extension = '.xlsx'
f[0].filename = filefront + extension
path = os.path.join(app.config['UPLOAD_FOLDER'], f[0].filename)
elif extension == '.xml':
xml_to_xlsx(path)
extension = '.xlsx'
f[0].filename = filefront + extension
path = os.path.join(app.config['UPLOAD_FOLDER'], f[0].filename)
################################
# Main part of computation
################################
#
# Read the input spreadsheet into data
#
input_worksheet = load_workbook(path).active
next(input_worksheet.rows) # Skip the first row
data = []
for row in input_worksheet.rows:
data.append([tryStrToNum(cell.value) for cell in row])
#
# Read the reference spread sheet
#
# Open the ref file and store reference sequences in a list
ref_seqs = {} # Key: Sample ID, Value: (Sequence, Factor)
if list_ref:
for sheet in list_ref.worksheets:
header_row = [cell.value for cell in sheet[1]]
# Skip the first row
for row in range(2, sheet.max_row + 1):
sample_id = sheet.cell(row=row, column=2).value
ref_sequence = sheet.cell(row=row, column=3).value
factor_col = getFactorColumn(header_row)
if factor_col:
factor = sheet.cell(row=row, column=factor_col).value
else:
factor = None
ref_seqs[sample_id] = (ref_sequence, factor)
#
# Here are the column indices for the various data fields
#
# Raw data fields
DATA_FIELDS = ['#', 'Sample ID', 'User name', 'Date and Time', 'Nucleic Acid Conc.', 'Unit',
'A260', 'A280', '260/280', '260/230', 'Sample Type', 'Factor',
'', ''] # Last two fields are filled in later depending on order of sequence and mol weight
# Important data fields
SAMPLEID = 1
USERNAME = 2
DATETIME = 3
CONC = 4
ABSORPTION = 6
SAMPLETYPE = 10
FACTOR = 11
SEQUENCE, MOLWEIGHT = getSequenceColumn(data[0])
DATA_FIELDS[SEQUENCE] = 'Sequence'
DATA_FIELDS[MOLWEIGHT] = 'Molecular Weight'
#
# Create the output workbook and write the headers
#
# Header data
date = data[1][DATETIME].split(' ', 1)[0] # Parse '6/1/2021 2:55:08 PM' ==> '6/1/2021'
operator = data[1][USERNAME]
# Create the output Excel file
output = Workbook()
output_raw_title = 'Raw Data'
output_final_title = 'NanoDrop Report'
output_raw_sheet = output.active
output_raw_sheet.title = output_raw_title
output_final_sheet = output.create_sheet(output_final_title)
# Styles
bd = Side(border_style="thin")
header_style = NamedStyle(name="header_style")
header_style.font = Font(bold=True)
header_style.alignment = Alignment(horizontal="center", vertical="center")
header_style.border = Border(left=bd, right=bd, top=bd, bottom=bd)
data_style = NamedStyle(name="data_style")
# data_style.border = Border(left=bd, right=bd, top=bd, bottom=bd)
data_style.alignment = Alignment(horizontal="center", vertical="center")
data_style.number_format = '0.0'
output_raw_sheet['B1'] = '=@CELL("filename")'
output_final_sheet['B1'] = 'Date'
output_final_sheet['B1'].style = header_style
output_final_sheet['C1'] = date
output_final_sheet['B2'] = 'Operator'
output_final_sheet['B2'].style = header_style
output_final_sheet['C2'] = operator
output_final_sheet['B3'] = 'Acceptance Criteria'
output_final_sheet['B3'].style = header_style
output_final_sheet['C3'] = 'OD260/280 is between ' + str(od_low) + ' - ' + str(od_high) + \
'. Concentration is between ' + str(conc_low) + ' uM - ' + str(conc_high) + ' uM'
project_id = request.form.get('project_id')
if project_id:
output_final_sheet['B4'] = 'Project ID'
output_final_sheet['B4'].style = header_style
output_final_sheet['C4'] = project_id
output_final_sheet['D6'] = 'Concentration'
output_final_sheet.merge_cells('D6:E6')
output_final_sheet['D6'].style = header_style
output_final_sheet['E6'].style = header_style
output_final_sheet['B7'] = 'Oligo Name'
output_final_sheet['B7'].style = header_style
output_final_sheet['C7'] = 'OD260/280'
output_final_sheet['C7'].style = header_style
output_final_sheet['D7'] = 'ng/uL'
output_final_sheet['D7'].style = header_style
output_final_sheet['E7'] = 'uM'
output_final_sheet['E7'].style = header_style
output_final_sheet['F7'] = 'QC'
output_final_sheet['F7'].style = header_style
output_raw_sheet.append(DATA_FIELDS) # The header
for i in range(len(DATA_FIELDS)):
output_raw_sheet.cell(row=2, column=i+1).style = header_style
#
# Write the data to the output file
#
# First write to the raw data sheet
output_raw_start_row = 3
final_data = OrderedDict()
for i, row in enumerate(data[1:]):
# Skip the blank, indicated by a sample ID of 'b'
if row[SAMPLEID] == 'b' or row[SAMPLEID].lower() == 'Blank':
output_raw_sheet.append(row)
continue
if len(row) <= MOLWEIGHT or not row[MOLWEIGHT]:
# Get the formula for molecular weight
# Source: https://www.thermofisher.com/us/en/home/references/ambion-tech-support/
# rna-tools-and-calculators/dna-and-rna-molecular-weights-and-conversions.html
sequence_col = "M" if SEQUENCE == 12 else "N"
sequence = sequence_col + str(output_raw_start_row + i)
if row[SAMPLETYPE] == "DNA" or row[SAMPLETYPE] == "dsDNA":
mol_weight_formula = '=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(' + sequence + \
',"A",""),"a",""),"T",""),"t","")) * (289.2 + 329.2) + ' + \
"LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" + sequence + \
',"C",""),"c",""),"G",""),"g","")) * (313.2 + 304.2) + 157.9'
elif row[SAMPLETYPE] == "Oligo DNA" or row[SAMPLETYPE] == "ssDNA":
mol_weight_formula = '=313.2 * (LEN(' + sequence + ') - LEN(SUBSTITUTE(SUBSTITUTE(' \
+ sequence + ',"A",""),"a",""))) + ' + \
'304.2 * (LEN(' + sequence + ') - LEN(SUBSTITUTE(SUBSTITUTE(' \
+ sequence + ',"T",""),"t",""))) + ' + \
'289.2 * (LEN(' + sequence + ') - LEN(SUBSTITUTE(SUBSTITUTE(' \
+ sequence + ',"C",""),"c",""))) + ' + \
'329.2 * (LEN(' + sequence + ') - LEN(SUBSTITUTE(SUBSTITUTE(' \
+ sequence + ',"G",""),"g",""))) + 79'
else:
mol_weight_formula = "Sample type not recognized"
if len(row) <= MOLWEIGHT:
row.append(mol_weight_formula)
else:
row[MOLWEIGHT] = mol_weight_formula
# Get the sequence from the reference list
if len(row) <= SEQUENCE or not row[SEQUENCE]:
print("HELLO")
try:
(dna_string, factor) = ref_seqs[row[SAMPLEID]]
if len(row) <= SEQUENCE:
row.append(dna_string)
else:
row[SEQUENCE] = dna_string
if factor:
row[FACTOR] = factor
factor_col = get_column_letter(FACTOR + 1)
abs_col = get_column_letter(ABSORPTION + 1)
row_num = str(output_raw_start_row + i)
# Concentration = factor * absorption at 260nm
row[CONC] = "=" + factor_col + row_num + "*" + abs_col + row_num
except:
row[MOLWEIGHT] = ''
# Copy from data to raw_sheet
output_raw_sheet.append(row)
output_raw_sheet.cell(row=output_raw_start_row + i, column=CONC+1).number_format = '0.0'
# Now update the dict that will give us the final data
sample_id = row[SAMPLEID]
if sample_id not in final_data:
final_data[sample_id] = [output_raw_start_row + i]
else:
final_data[sample_id].append(output_raw_start_row + i)
# Then write tot he summary data sheet
output_final_start_row = 8
for i, (sample_id, raw_rows) in enumerate(final_data.items()):
output_final_row = str(output_final_start_row + i)
# Copy Sample ID from raw_sheet to final_sheet
output_final_sheet.cell(row=output_final_start_row + i, column=2).value = \
"='" + output_raw_title + "'!B" + str(raw_rows[0])
output_final_sheet.cell(row=output_final_start_row + i, column=2).style = data_style
# Copy OD260/280 from raw_sheet to final_sheet
ods = [f"'{output_raw_title}'!I{str(raw_row)}" for raw_row in raw_rows]
output_final_sheet.cell(row=output_final_start_row + i, column=3).value = \
f"=AVERAGE({', '.join(ods)})"
output_final_sheet.cell(row=output_final_start_row + i, column=3).style = data_style
# Copy Oligo Conc. from raw_sheet to final_sheet
concs = [f"IF('{output_raw_title}'!F{str(raw_row)}=\"ng/µl\", "
f"'{output_raw_title}'!E{str(raw_row)}, "
f"'{output_raw_title}'!E{str(raw_row)} * 1000)" for raw_row in raw_rows]
output_final_sheet.cell(row=output_final_start_row + i, column=4).value = \
f"=AVERAGE({', '.join(concs)})"
output_final_sheet.cell(row=output_final_start_row + i, column=4).style = data_style
# Calculate the uM concentration
molweight_col = "M" if MOLWEIGHT == 12 else "N"
um_concs = [f"D{output_final_row}*1000/'{output_raw_title}'!{molweight_col}{str(raw_row)}" for raw_row in raw_rows]
output_final_sheet.cell(row=output_final_start_row + i, column=5).value = \
f"=AVERAGE({', '.join(um_concs)})"
output_final_sheet.cell(row=output_final_start_row + i, column=5).number_format = '0.0'
output_final_sheet.cell(row=output_final_start_row + i, column=5).alignment = \
Alignment(horizontal="center", vertical="center")
# Calculate the QC
output_final_sheet.cell(row=output_final_start_row + i, column=6).value = \
"=IF(AND(AND(C" + output_final_row + ">=" + str(od_low) + ",C" + \
output_final_row + "<=" + str(od_high) + "),AND(E" + output_final_row + \
">=" + str(conc_low) + ",E" + output_final_row + "<=" + str(conc_high) + \
")), \"Passed\", \"Failed\")"
output_final_sheet.cell(row=output_final_start_row + i, column=6).style = data_style
#
# Make some final touches to the output file
#
# Edit column styles so that their widths are BestFit
column_letters = tuple(get_column_letter(col_number + 1) for col_number in range(output_raw_sheet.max_column))
for column_letter in column_letters:
output_raw_sheet.column_dimensions[column_letter].width = 15
column_letters = tuple(get_column_letter(col_number + 1) for col_number in range(output_final_sheet.max_column))
for column_letter in column_letters:
output_final_sheet.column_dimensions[column_letter].width = 17
# Insert the EpigenDx logo
logo_path = os.path.join(app.config['STATIC_FOLDER'], 'img/logo_small.gif')
logo = drawing.image.Image(logo_path)
output_final_sheet.add_image(logo, 'H1')
#
# Save the output file and send it to the user
#
# Save the output file (and overwrite the previous output file)
out_path = os.path.join(app.config['UPLOAD_FOLDER'], 'nanodrop_results.xlsx')
output.save(out_path)
os.chmod(out_path, stat.S_IRWXO | stat.S_IRWXG | stat.S_IRWXU)
# Delete the uploaded file from the server
os.remove(path)
if ref[0]:
os.remove(ref_file_path)
# Send the output sheet to the user
user_filename = request.form.get("filename")
if user_filename:
if user_filename.endswith(".xlsx"):
final_filename = user_filename
else:
final_filename = user_filename + ".xlsx"
else:
final_filename = "nanodrop_results.xlsx"
try:
return send_file(os.path.join(app.config['UPLOAD_FOLDER'], 'nanodrop_results.xlsx'),
attachment_filename=final_filename,
as_attachment=True)
except:
os.remove(out_path)
flash('Could not send the results file', 'alert-warning')
return render_template('nanodrop/form.html')
title_style_i = NamedStyle(name='income_title')
title_style_i.font = Font(size=20, bold=True, underline='single', )
title_style_i.alignment = Alignment(horizontal='center', vertical='center')
title_style_i.fill = PatternFill(fgColor=Color("66CC66"),
fill_type=fills.FILL_SOLID
)
title_style_i.border = title_border
# Control header
title_style_c = NamedStyle(name='control_title')
title_style_c.font = Font(size=20, bold=True, underline='single', )
title_style_c.alignment = Alignment(horizontal='center', vertical='center')
title_style_c.fill = PatternFill(fgColor=Color("AA00FF"),
fill_type=fills.FILL_SOLID
)
title_style_c.border = title_border
# --------------- Field Styles ---------------
# Field label style
label_style = NamedStyle(name='field_labels')
label_style.alignment = Alignment(horizontal='center', vertical='center')
label_style.border = f_label_border
label_style.font = Font(bold=True)
# Field placeholder style w/ currency format
ph_style = NamedStyle(name="ph_style")
ph_style.alignment = Alignment(horizontal='center', vertical='center')
ph_style.border = f_label_border
ph_style.number_format = '$#,##0.00'
