def _set_swiss7_chart(self, workbook):
ws = workbook['SWISS7_G']
ws_data = workbook['Data_category']
chart1 = BarChart()
chart1.type = "col"
# chart1.style = 12
chart1.width = 21
chart1.height = 12
chart1.grouping = "stacked"
chart1.overlap = 100
chart1.y_axis.title = 'Volume du trafic en %'
# chart1.x_axis.title = 'Heure'
chart1.gapWidth = 2
data = Reference(ws_data, min_col=2, min_row=4, max_row=28, max_col=8)
# cats = Reference(ws_data, min_col=2, min_row=4, max_row=28)
chart1.add_data(data, titles_from_data=True)
# chart1.set_categories(cats)
chart1.shape = 4
chart1.legend = None
chart1.series[0].graphicalProperties.solidFill = "00a9ff"
chart1.series[1].graphicalProperties.solidFill = "bce273"
chart1.series[2].graphicalProperties.solidFill = "ff708c"
chart1.series[3].graphicalProperties.solidFill = "003366"
chart1.series[4].graphicalProperties.solidFill = "ff00ff"
chart1.series[5].graphicalProperties.solidFill = "ff3399"
chart1.series[6].graphicalProperties.solidFill = "ff99cc"
ws.add_chart(chart1, "A11")
chart1 = BarChart()
chart1.type = "col"
# chart1.style = 12
chart1.width = 21
chart1.height = 12
chart1.grouping = "stacked"
chart1.overlap = 100
chart1.y_axis.title = 'Volume du trafic en %'
# chart1.x_axis.title = 'Heure'
chart1.gapWidth = 2
data = Reference(ws_data, min_col=2, min_row=32, max_row=56, max_col=8)
# cats = Reference(ws_data, min_col=2, min_row=32, max_row=56)
chart1.add_data(data, titles_from_data=True)
# chart1.set_categories(cats)
chart1.shape = 4
chart1.legend = None
chart1.series[0].graphicalProperties.solidFill = "00a9ff"
chart1.series[1].graphicalProperties.solidFill = "bce273"
chart1.series[2].graphicalProperties.solidFill = "ff708c"
chart1.series[3].graphicalProperties.solidFill = "003366"
chart1.series[4].graphicalProperties.solidFill = "ff00ff"
chart1.series[5].graphicalProperties.solidFill = "ff3399"
chart1.series[6].graphicalProperties.solidFill = "ff99cc"
ws.add_chart(chart1, "A46")
def _set_cv_lv_chart(self, workbook):
ws = workbook['CV_LV']
ws_data = workbook['Data_day']
chart1 = BarChart()
chart1.type = "col"
chart1.width = 19
chart1.height = 8
chart1.style = 10
chart1.y_axis.title = 'Vehicules à moteur en % du TJMO de la section'
chart1.x_axis.title = "Selon l'heure de la journée"
chart1.gapWidth = 0
#chart1.legend = None
data = Reference(ws_data,
min_col=11,
min_row=4,
max_row=28,
max_col=11)
cats = Reference(ws, min_col=1, min_row=5, max_row=28)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
# chart1.shape = 4
s = chart1.series[0]
s.graphicalProperties.line.solidFill = "000000"
s.graphicalProperties.solidFill = "66ffb2"
chart2 = LineChart()
data = Reference(ws_data,
min_col=11,
min_row=34,
max_row=58,
max_col=11)
chart2.add_data(data, titles_from_data=True)
data = Reference(ws_data,
min_col=11,
min_row=65,
max_row=89,
max_col=11)
chart2.add_data(data, titles_from_data=True)
# chart2.style = 12
s = chart2.series[0]
s.graphicalProperties.line.solidFill = "3333ff"
s.smooth = False
s = chart2.series[1]
s.graphicalProperties.line.solidFill = "ff3333"
s.graphicalProperties.line.dashStyle = "sysDash"
s.smooth = False
chart1 += chart2
ws.add_chart(chart1, "A15")
(bin, )):
if (rfu > max_rfu_c[col_num2] * (second_screen / 100)):
ws8.cell(row=v_offset + col_num2,
column=cx8 + 1).value = round(
(100 * rfu) / minus_2_rfu_c[col_num2], 1)
cx8 = cx8 + 1
# calculate placement of data - link to chart
ws8.column_dimensions['A'].width = 50
chart1 = BarChart()
chart1.type = "col"
chart1.style = 2
chart1.width = 0.7 * sites
chart1.height = 15
chart1.gapWidth = 10
chart1.overlap = 100
chart1.grouping = "percentStacked"
chart1.legend = None
# chart1.title = 'Percent Stacked Chart'
# chart1.y_axis.title = 'Percentage'
# chart1.x_axis.title = 'Sample Sites'
# chart1.varyColors = True
# chart1.shape = 4
site_list = Reference(ws8, min_col=1, min_row=35, max_row=35 + sites)
data = Reference(ws8, min_col=1, min_row=34, max_row=34 + sites, max_col=cx8)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(site_list)
def exp_excel(cls, aa, file, norm='hist_raw', additional_info=None):
"""
Export results in an Excel file.
Parameters
----------
aa:
ArrayAnalyse instant
norm: str
evaluation strategy selection (see ArrayAnalyse.evaluate)
file:
file can be a path to a file (a string), a path-like object, or a file-like object
additional_info: List[Dict]
list with dictionaries containing 'name' and 'value' key
"""
if not aa.is_finalized:
warnings.warn(
'Data collection needs to be finalized to generate a report.',
RuntimeWarning)
return None
wb = Workbook()
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=20)
wb.add_named_style(highlight)
bold = NamedStyle(name="bold")
bold.font = Font(bold=True)
wb.add_named_style(bold)
bold_right = NamedStyle(name="bold_right")
bold_right.font = Font(bold=True)
bold_right.alignment = Alignment(horizontal='right')
wb.add_named_style(bold_right)
bold_center = NamedStyle(name="bold_center")
bold_center.font = Font(bold=True)
bold_center.alignment = Alignment(horizontal='center')
wb.add_named_style(bold_center)
# Overview worksheet
ws = wb.active
alignment_img = opImage(config.template_folder / 'logo.png')
ws.add_image(alignment_img, f"B1")
ws['E4'] = f'proMAD'
ws['E5'] = config.version
ws['E4'].style = highlight
ws.title = "Overview"
data = aa.evaluate(norm=norm)
row_offset = 12
column_offset = 2
if cls.norm_return[norm] == ('values', ):
for entry in data:
ws.cell(column=entry['position'][1] + column_offset,
row=entry['position'][0] + row_offset,
value=entry['value'][-1])
else:
for entry in data:
ws.cell(column=entry['position'][1] + column_offset,
row=entry['position'][0] + row_offset,
value=entry['value'][0])
for column in range(sum(aa.array_data['net_layout_x'])):
ws.cell(column=column + column_offset,
row=row_offset - 1,
value=column + 1).style = bold_center
for row in range(sum(aa.array_data['net_layout_y'])):
ws.cell(column=column_offset - 1,
row=row + row_offset,
value=get_column_letter(row + 1)).style = bold_right
alignment_img_raw = io.BytesIO()
aa.figure_alignment(file=alignment_img_raw)
alignment_img = opImage(alignment_img_raw)
ws[f"B{sum(aa.array_data['net_layout_y']) + row_offset + 1}"] = 'Fig. 1: Overview and alignment check'
ws.add_image(alignment_img,
f"B{sum(aa.array_data['net_layout_y'])+row_offset+2}")
# Result worksheet
ws = wb.create_sheet()
ws.title = "Results"
new_rows = []
ref_spots = []
data = aa.evaluate(norm=norm, double_spot=True)
for entry in data:
pos = aa.get_position_string(entry['position'])
if not isinstance(pos, str):
pos = ", ".join(pos)
if 'Reference' in entry['info'][0] or 'Negative Controls' in entry[
'info'][0]:
ref_spots.append([entry['info'][0], pos, entry['value']])
else:
new_rows.append([entry['info'][0], pos, entry['value']])
ws['A1'] = 'Membrane Results'
ws['A1'].style = highlight
ws.append([])
ws.append(('Name', 'Position', 'Value'))
ws['A3'].style = bold
ws['B3'].style = bold
ws['C3'].style = bold_right
data_start = 5 + len(ref_spots)
for row in sorted(ref_spots, key=lambda s: s[2], reverse=True):
ws.append(row)
ws.append([])
for row in sorted(new_rows, key=lambda s: s[2], reverse=True):
ws.append(row)
ws.column_dimensions['A'].width = 20
ws.column_dimensions['C'].width = 12
cutoff = max(min(len(new_rows), 15), int(len(new_rows) * 0.2))
values = Reference(ws,
min_col=3,
min_row=data_start,
max_col=3,
max_row=cutoff + data_start)
categories = Reference(ws,
min_col=1,
min_row=data_start,
max_col=1,
max_row=cutoff + data_start)
chart = BarChart()
chart.add_data(values)
chart.set_categories(categories)
chart.y_axis.title = cls.norm_unit[norm][0].upper(
) + cls.norm_unit[norm][1:]
chart.title = f'Top spots'
chart.width = 30
chart.height = 15
chart.gapWidth = 25
chart.legend = None
ws.add_chart(chart, "E3")
# Result details worksheet
ws = wb.create_sheet()
ws.title = "Result details"
new_rows = []
data = aa.evaluate(norm=norm)
for entry in data:
new_rows.append(
[entry['info'][0],
aa.get_position_string(entry['position'])] +
[v for v in entry['value']])
ws['A1'] = 'Membrane Result Details'
ws['A1'].style = highlight
ws.append([])
ws.append(['Name', 'Position'] +
[head.title() for head in cls.norm_return[norm]])
ws['A3'].style = bold
ws['B3'].style = bold
for n in range(len(cls.norm_return[norm])):
ws[get_column_letter(n + 3) + '3'].style = bold_right
for row in sorted(new_rows, key=lambda s: s[0].lower()):
ws.append(row)
ws.column_dimensions['A'].width = 20
for n in range(len(new_rows[0]) - 2):
ws.column_dimensions[get_column_letter(n + 3)].width = 12
# Info worksheet
ws = wb.create_sheet()
ws.title = "Info"
ws['A1'] = f'Technical data'
ws['A1'].style = highlight
detail_values, detail_names = cls.get_details(aa, norm)
info_list = [
'date', 'time', 'skip', 'program', 'version', 'url', 'skip',
'array_name', 'array_type', 'array_id', 'norm_name', 'norm',
'norm_description', 'unit'
]
tech_data_list = []
for key in info_list:
if key == 'skip':
tech_data_list.append(('', ''))
else:
tech_data_list.append((detail_names[key], detail_values[key]))
if additional_info:
tech_data_list += [('', '')] + [(entry['name'], entry['value'])
for entry in additional_info]
ws.column_dimensions['A'].width = 15
ws.column_dimensions['B'].width = 15
for n, content in enumerate(tech_data_list):
ws.cell(column=1, row=n + 3, value=content[0]).style = bold_right
ws.cell(column=2, row=n + 3, value=content[1])
if isinstance(file, os.PathLike) or isinstance(file, str):
wb.save(file)
elif isinstance(file, (io.RawIOBase, io.BufferedIOBase)):
with NamedTemporaryFile() as tmp:
wb.save(tmp.name)
tmp.seek(0)
file.write(tmp.read())
