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 sample_output_excel_chart():
# Sample from http://zetcode.com/articles/openpyxl/
book = Workbook()
sheet = book.active
rows = [
("USA", 46),
("China", 38),
("UK", 29),
("Russia", 22),
("South Korea", 13),
("Germany", 11)
]
for row in rows:
sheet.append(row)
data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=6)
categs = Reference(sheet, min_col=1, min_row=1, max_row=6)
chart = BarChart()
chart.add_data(data=data)
chart.set_categories(categs)
chart.legend = None
chart.y_axis.majorGridlines = None
chart.varyColors = True
chart.title = "Olympic Gold medals in London"
sheet.add_chart(chart, "A8")
book.save("bar_chart.xlsx")
def chart_bar(self, ws, destination_cell_coordinate,
label_min_row, label_max_row, label_min_col, label_max_col,
data_min_row, data_max_row, data_min_col, data_max_col,
title='',from_rows=False, chart_height = default_chart_height, chart_width = default_chart_width,
is_3d = False, add_chart = True, show_legend=True
, **kw
):
if is_3d:
chart = BarChart3D()
else:
chart = BarChart()
chart.height = chart_height # default is 7.5
chart.width = chart_width # default is 15
labels = Reference(ws, min_row=label_min_row, max_row=label_max_row, min_col=label_min_col, max_col=label_max_col,)
data = Reference(ws, min_row=data_min_row, max_row=data_max_row, min_col=data_min_col, max_col=data_max_col,)
chart.add_data(data, titles_from_data=False, from_rows=from_rows)
chart.set_categories(labels)
chart.title = title
if not show_legend:
chart.legend = None
if add_chart:
ws.add_chart(chart, destination_cell_coordinate)
for seri in chart.series:
seri.label = True
return chart
def main():
book = Workbook()
sheet = book.active
rows = [("USA", 46), ("UK", 27), ("China", 26), ("Russia", 19),
("Germany", 17), ("Japan", 12)]
for row in rows:
sheet.append(row)
data = Reference(sheet, min_row=1, min_col=2, max_row=6, max_col=2)
categs = Reference(sheet, min_row=1, min_col=1, max_row=6)
chart = BarChart()
chart.add_data(data=data)
chart.set_categories(categs)
chart.legend = None
chart.y_axis.majorGridlines = None
chart.varyColors = True
chart.title = 'Olympic Gold medals in Rio 2016'
sheet.add_chart(chart, 'A8')
book.save('bar_chart.xlsx')
def sheetThree():
ws = wb.create_sheet('Evaluation Summary')
ruleSummaryRes = client.get_compliance_summary_by_config_rule()
global column_num
column_num = 3
ws.merge_cells('A1:D1')
ws['A1'] = "Evaluation Summary(by config rule)"
#border line
comp = ruleSummaryRes['ComplianceSummary']['CompliantResourceCount'][
'CappedCount']
noncomp = ruleSummaryRes['ComplianceSummary']['NonCompliantResourceCount'][
'CappedCount']
#rows = [
# ('', '준수', '미준수', '전체'),
# ('Rule count',comp,noncomp,comp+noncomp)
#]
excelData = ['AWS Config rule 개수', comp, noncomp, comp + noncomp]
saveContent(excelData, ws)
#for row in rows:
# ws.append(row)
bc = BarChart()
bc.type = "col"
bc.style = 10
bc.title = "Evaluation Summary"
bc.y_axis.title = 'Rule Count'
bc.x_axis.title = 'Compliant Status'
data = Reference(ws, min_col=2, min_row=1, max_row=4, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=4)
bc.add_data(data, titles_from_data=True)
bc.set_categories(cats)
bc.shape = 4
bc.width = 8
bc.legend = None
ws.add_chart(bc, "A6")
rownum = 0
while rownum < 3:
ws.cell(row=rownum + 1, column=1).style = paintstyle
ws.cell(row=rownum + 1, column=2).style = paintstyle
ws.cell(row=rownum + 1, column=3).style = paintstyle
ws.cell(row=rownum + 1, column=4).style = paintstyle
rownum += 1
setTitleCell(ws.cell(row=1, column=1))
excelColName = ['', '준수', '미준수', '전체']
writeColName(excelColName, 2, ws)
ws.row_dimensions[1].height = 25
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 15
print('Evaluation Summary sheet done')
def draw_bar2D(
self,
sheet_name,
data_position=[],
label_position=[],
display_position="A10",
title="Bar Chart",
x_title="display_x",
y_title="display_y",
is_display_legend=False
):
"""
数据必须为列。。。不知道为啥。。
"""
ws = self.wb[sheet_name]
# 数据所在列的坐标范围， 不包含label
DATA_COL_MIN = data_position[0]
DATA_COL_MAX = data_position[1]
DATA_ROW_MIN = data_position[2]
DATA_ROW_MAX = data_position[3]
# label 所在范围
LABEL_COL_MIN = label_position[0]
LABEL_COL_MAX = label_position[1]
LABEL_ROW_MIN = label_position[2]
LABEL_ROW_MAX = label_position[3]
# 创建 chart 对象
chart1 = BarChart()
# 竖直的柱状图"col"
chart1.type = "col"
chart1.style = 10
chart1.shape = 4
chart1.title = title
chart1.y_axis.title = y_title
chart1.x_axis.title = x_title
data = Reference(ws, min_col=DATA_COL_MIN, max_col=DATA_COL_MAX, min_row=DATA_ROW_MIN, max_row=DATA_ROW_MAX)
cats = Reference(ws, min_col=LABEL_COL_MIN, max_col=LABEL_COL_MAX, min_row=LABEL_ROW_MIN, max_row=LABEL_ROW_MAX)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
# label , 柱状图上的数字
chart1.dLbls = DataLabelList()
chart1.dLbls.showVal = True
# 是否显示图例图例
if is_display_legend == False:
chart1.legend = None
# 显示位置
ws.add_chart(chart1, display_position)
def createEmptyChart(ws):
for row in data:
ws.append(row)
values = Reference(ws, min_col=1, max_col=1, min_row=1, max_row=1)
chart = BarChart()
chart.add_data(values)
chart.x_axis.allow_none = True
chart.y_axis.allow_none = True
chart.y_axis.majorGridlines = None
chart.legend = None
chart.x_axis.delete = True
chart.y_axis.delete = True
chart.title = 'Test'
ws.add_chart(chart, 'A1')
def createChart(ws):
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Violation occurrence per area"
chart1.y_axis.title = 'Violation occurrence'
chart1.x_axis.title = 'Violation and Location'
cats = Reference(ws, min_col=1, max_col=2, min_row=2, max_row=ws.max_row)
data = Reference(ws, min_col=5, max_col=5, min_row=1, max_row=ws.max_row)
chart1.add_data(data, titles_from_data=True)
chart1.legend = None
chart1.set_categories(cats)
chart1.width = 30
chart1.height = 15
ws.add_chart(chart1, "K2")
def genChart(self):
'''
Generates a bar graph to visually represent the data.
'''
chart = BarChart()
chart.type = "col"
chart.style = 1
chart.title = None
chart.y_axis.title = 'Force'
chart.x_axis.title = 'Operation'
chartData = Reference(self.workSheet, min_col=1, \
min_row=2, max_row=len(self.forceDataFrame)+1, max_col=1)
chart.add_data(chartData, titles_from_data=None)
chart.legend = None
chart.height = 15
chart.width = 30
self.workSheet.add_chart(chart, "G5")
def open_close_delta():
"""Create a sheet for Open/Close delta."""
ws2 = Workbook.wb.create_sheet('Open Close Delta')
mysel, connection = DB_Connection.connect_db()
cursor = connection.execute('SELECT * FROM {}'.format(stock))
names = list(map(lambda x: x[0], cursor.description))
idx = 1
for i in range(6):
if i == 0 or i == 1 or i == 4:
ws2.cell(row=1, column=idx).value = names[i]
idx += 1
counter = 1
"""Write relevant data to Excel."""
for i, row in enumerate(mysel):
post = row[0], row[1], row[4]
counter += 1
ws2.append(post)
Workbook.wb.save(Workbook.wbook_name)
"""Use pandas to create a new field, Open/Close Delta."""
ws2['D1'] = 'Delta'
df = pd.read_excel(Workbook.wbook_name, 'Open Close Delta')
df['Delta'] = df['OpenPrice'] - df['ClosePrice']
for dl in range(2, counter+1):
ws2['D{}'.format(dl)] = float(df['Delta'][dl-2])
"""Plot delta as bar chart."""
ws2.sheet_view.zoomScale = 85
bc_three_d = BarChart()
bc_three_d.type = 'col'
bc_three_d .style = 10
bc_three_d .title = 'Open/Close Delta for Past 30 Trading Days'
bc_three_d.y_axis.title = 'Price'
bc_three_d.x_axis.title = 'Date'
data = Reference(ws2, min_col=4, min_row=counter-29, max_row=counter, max_col=4)
cats = Reference(ws2, min_col=1, min_row=counter-28, max_row=counter)
bc_three_d.add_data(data, titles_from_data=True)
bc_three_d.set_categories(cats)
bc_three_d.height = 20
bc_three_d.width = 30
bc_three_d.legend = None
ws2.add_chart(bc_three_d, 'F2')
Workbook.wb.save(Workbook.wbook_name)
def create_bar_chart(sheet, start_row, size):
bar_chart = BarChart()
bar_chart.type = "col"
bar_chart.style = 10
bar_chart.title = "Память квазициклов"
bar_chart.y_axis.title = 'Память'
bar_chart.x_axis.title = 'Квазициклы'
data = Reference(sheet,
min_col=2,
min_row=start_row - 1,
max_row=start_row + size)
indexes = Reference(sheet,
min_col=1,
min_row=start_row,
max_row=start_row + size)
bar_chart.add_data(data, titles_from_data=True)
bar_chart.set_categories(indexes)
bar_chart.shape = 4
bar_chart.legend = None
return bar_chart
def create_bar_chart(x_cells, y_cells, title, range=None, height=10, width=20):
"""
@fn create_bar_chart()
@brief
@param x_cells カテゴリ参照範囲(Reference)
@param y_cells データ参照範囲(Reference)
@param title タイトル
@param range 値域
@param height グラフの高さ
@param width グラフの幅
@retval chart グラフ
"""
chart = BarChart()
chart.title = title
chart.style = 2
chart.height = height
chart.width = width
chart.legend = None
chart.add_data(y_cells)
chart.set_categories(x_cells)
return chart
def avg_chart():
chart1 = BarChart()
ws = wb["Past Data"]
data = Reference(worksheet=ws,
min_row=1,
max_row=ws.max_row,
min_col=2,
max_col=2)
cats = Reference(worksheet=ws,
min_row=2,
max_row=ws.max_row,
min_col=1,
max_col=1)
# set the title of the chart
chart1.title = f'Average Regen Timing'
chart1.add_data(data, titles_from_data=True)
chart1.legend = None
chart1.set_categories(cats)
count_line = LineChart()
count = Reference(worksheet=ws,
min_row=2,
max_row=ws.max_row - 1,
min_col=3)
count_line.add_data(count)
chart1 += count_line
# set data labels and styles
s1 = chart1.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showVal = True
pt = DataPoint(idx=ws.max_row - 3)
pt.graphicalProperties.solidFill = "ff9900"
pt1 = DataPoint(idx=ws.max_row - 2)
pt1.graphicalProperties.solidFill = "00ff00"
s1.dPt.append(pt)
s1.dPt.append(pt1)
# print(type(ws.max_row))
wb["Regen Timing"].add_chart(chart1, "A1")
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
[3, 25, 40],
[4 ,30, 50],
[5 ,10, 30],
[6, 5, 25],
[7 ,10, 50],
]
for row in rows:
ws3.append(row)
chart = AreaChart3D()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
chart.legend = None
cats = Reference(ws3, min_col=1, min_row=1, max_row=7)
data = Reference(ws3, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws3.add_chart(chart, "A10")
#######################
## STACKED BAR CHART ##
#######################
from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference
def __init__(self,
filename,
sheet_name=[],
chart_title=[],
xaxis_title=[],
yaxis_title=[],
stack=[],
chart_position="F10",
whiten_all_area=True,
font_name='Calibri',
axis_title_font_size=10,
output_filename=None):
wb = load_workbook(filename)
counts = len(sheet_name)
wsDict = {}
for count in range(counts):
wsDict['ws' + str(count)] = wb[sheet_name[count]]
chart = BarChart()
chart.style = 10
chart.type = "col"
if stack[count] == True:
chart.grouping = "stacked"
chart.overlap = 100
chart.title = chart_title[count]
chart.y_axis.title = xaxis_title[count]
chart.x_axis.title = yaxis_title[count]
data = Reference(wsDict['ws' + str(count)],
min_col=wsDict['ws' + str(count)].min_column + 1,
min_row=wsDict['ws' + str(count)].min_row,
max_row=wsDict['ws' + str(count)].max_row,
max_col=wsDict['ws' + str(count)].max_column)
cats = Reference(wsDict['ws' + str(count)],
min_col=wsDict['ws' + str(count)].min_column,
min_row=wsDict['ws' + str(count)].min_row + 1,
max_row=wsDict['ws' + str(count)].max_row,
max_col=wsDict['ws' + str(count)].min_column)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
## set axis title property??
font_test = draw_Font(typeface=font_name)
cp = CharacterProperties(latin=font_test,
sz=axis_title_font_size * 100)
chart.x_axis.txPr = RichText(p=[
Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)
])
chart.y_axis.txPr = RichText(p=[
Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)
])
xPara = [
Paragraph(pPr=ParagraphProperties(defRPr=cp),
r=RegularTextRun(t=s))
for s in xaxis_title[count].split("\n")
]
yPara = [
Paragraph(pPr=ParagraphProperties(defRPr=cp),
r=RegularTextRun(t=s))
for s in yaxis_title[count].split("\n")
]
chart.x_axis.title.tx.rich.paragraphs = xPara
chart.y_axis.title.tx.rich.paragraphs = yPara
## hide legend
if not stack[count]:
chart.legend = None
wsDict['ws' + str(count)].add_chart(chart, chart_position)
## whiten data
if whiten_all_area:
table = wsDict['ws' + str(count)]["A1":"AZ200"]
color = 'FFFFFFFF'
font = Font(bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=color)
fill = PatternFill(start_color=color,
end_color=color,
fill_type="solid")
for tr in table:
for td in tr:
td.font = font
td.border = None
td.fill = fill
### data label
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
if output_filename is None:
wb.save(filename=filename)
else:
wb.save(filename=output_filename)
#
# barchart_creater(filename = 'temp0.xlsx',sheet_name=["Issue Count by Due Date","Issue Count By Owner","MTM Issue Comparison"],
# chart_title=["Number of Issues By Due Date","Number of outstanding Issues by Owner","Number of Issues comparison month to month by Source"],
# xaxis_title = ["Number of Issues",'Issue Owner','Month'],
# yaxis_title=['Number of Days till Due','Number of Issues','Number Of Issues'],stack = [False,False,True])
def excel_create_chart(excel_file):
"""
生成图表
:param excel_file:
:return:
"""
wb = load_workbook(excel_file)
print(f'{datetime.now()} | 信息 | 开始Excel图表渲染')
# 进行净值走势图渲染
net_worth_sheet = wb['账户净值']
net_worth_chart_sheet = wb.create_chartsheet(title='净值走势图')
chart1 = LineChart()
dates1 = Reference(net_worth_sheet,
min_col=1,
min_row=2,
max_row=len(net_worth_sheet['A']))
data1 = Reference(net_worth_sheet,
min_col=3,
min_row=1,
max_row=len(net_worth_sheet['C']))
chart1.add_data(data1, titles_from_data=True)
chart1.y_axis = NumericAxis(title='净值', majorTickMark='out')
chart1.x_axis = TextAxis(majorTickMark='out',
tickLblSkip=10,
tickMarkSkip=10,
noMultiLvlLbl=True,
numFmt='yyyy-mm-dd')
chart1.legend = None
chart1.title = str()
chart1.style = 1
chart1.set_categories(dates1)
net_worth_chart_sheet.add_chart(chart1)
# 进行权益走势图渲染
account_sheet = wb['账户统计']
account_chart_sheet = wb.create_chartsheet(title='权益走势图')
dates2 = Reference(account_sheet,
min_col=1,
min_row=2,
max_row=len(account_sheet['A']))
chart2 = LineChart(varyColors=True)
data2 = Reference(account_sheet,
min_col=8,
min_row=1,
max_row=len(account_sheet['H']))
chart2.add_data(data2, titles_from_data=True)
chart2.y_axis = NumericAxis(title='权益', majorTickMark='out')
chart2.legend = None
chart2.set_categories(dates2)
chart3 = BarChart()
data3 = Reference(account_sheet,
min_col=10,
min_row=1,
max_row=len(account_sheet['J']))
chart3.add_data(data3, titles_from_data=True)
chart3.y_axis = NumericAxis(axId=200,
title='风险度',
majorGridlines=None,
majorTickMark='out',
crosses='max')
chart3.x_axis = TextAxis(majorTickMark='out',
tickLblSkip=10,
tickMarkSkip=10,
noMultiLvlLbl=True,
numFmt='yyyy-mm-dd')
chart3.legend = None
chart3.set_categories(dates2)
chart2 += chart3
account_chart_sheet.add_chart(chart2)
# 进行交易分布图的渲染
categories_analysis_sheet = wb['交易分析(按品种)']
trading_frequency_analysis_sheet = wb.create_chartsheet(title='交易分布图')
labels = Reference(categories_analysis_sheet,
min_col=1,
min_row=2,
max_row=len(categories_analysis_sheet['A']))
chart4 = PieChart(varyColors=True)
chart4.style = 34
data4 = Reference(categories_analysis_sheet,
min_col=4,
min_row=2,
max_row=len(categories_analysis_sheet['D']))
chart4.add_data(data4)
chart4.set_categories(labels)
chart4.legend = None
# chart4.series[0].data_points = [DataPoint(idx=i, explosion=8)
# for i in range(len(categories_analysis_sheet['D']) - 1)]
chart4.series[0].dLbls = DataLabelList(dLblPos='bestFit',
showPercent=True,
showCatName=True,
showVal=True,
showLeaderLines=True)
chart4.layout = Layout(manualLayout=ManualLayout(
x=0, y=0, h=0.75, w=0.75, xMode='factor', yMode='factor'))
trading_frequency_analysis_sheet.add_chart(chart4)
# 进行品种盈亏图的渲染
categories_win_and_loss_chart_sheet = wb.create_chartsheet(title='品种盈亏图')
chart5 = BarChart(barDir='col')
chart5.style = 18
data5 = Reference(categories_analysis_sheet,
min_col=2,
min_row=2,
max_row=len(categories_analysis_sheet['B']))
chart5.add_data(data5)
chart5.set_categories(labels)
chart5.legend = None
chart5.series[0].dLbls = DataLabelList(showVal=True)
chart5.y_axis = NumericAxis(title='平仓盈亏',
majorTickMark='out',
minorTickMark='out')
categories_win_and_loss_chart_sheet.add_chart(chart5)
# 进行交易盈亏图的渲染
trading_win_and_loss_chart_sheet = wb.create_chartsheet(title='交易盈亏图')
chart6 = RadarChart()
chart6.style = 24
data6 = Reference(categories_analysis_sheet,
min_col=8,
max_col=9,
min_row=1,
max_row=categories_analysis_sheet.max_row)
chart6.add_data(data6, titles_from_data=True)
chart6.set_categories(labels)
trading_win_and_loss_chart_sheet.add_chart(chart6)
# 图表保存
wb.save(excel_file)
wb.close()
# 输出信息
print(f'{datetime.now()} | 信息 | 已生成Excel图表')
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())
book.save("./data/meltop100.xlsx")
# Trythis 3-1번
book = openpyxl.load_workbook("./data/meltop100.xlsx")
sheet3 = book.create_sheet()
sheet3.title = "차트출력"
datax = Reference(sheet1, min_col=4, min_row=2, max_col=4, max_row=11)
categs = Reference(sheet1, min_col=2, min_row=2, max_row=11)
chart1 = BarChart()
chart1.add_data(datax)
chart1.set_categories(categs)
chart1.legend = None # 범례
chart1.varyColors = True
chart1.title = "상위 10위 좋아요수"
sheet3.add_chart(chart1, "A8")
# Trythis 3-2번
chart2 = ScatterChart()
chart2.style = 13
datax = Reference(sheet1, min_col=1, min_row=2, max_row=11)
value = Reference(sheet1, min_col=5, min_row=1, max_row=11)
series = Series(value, datax, title_from_data=True)
chart2.series.append(series)
categs = Reference(sheet1, min_col=2, min_row=2, max_row=11)
# chart1.add_data(datax)
def regen_time_tab():
youtec_df = trouble_report.youtec()
df1 = pd.merge(clean, youtec_df, on='Date', how='left').replace(np.nan, "")
df1 = df1[[
'Date', 'Line', 'Hours', 'Target', 'Program', 'Disk Type', 'Duration',
'CVD Change', 'Activities', 'Leakages'
]]
start_row = 16
# # ws = wb.active
ws = wb.create_sheet("Regen Timing", 0)
ws.freeze_panes = 'A17'
ws.sheet_view.showGridLines = False
ws.title = "Regen Timing"
listy = dataframe_to_rows(df1, index=False, header=True)
for row, row_entries in enumerate(listy, start=16):
for column, value in enumerate(row_entries, start=1):
ws.cell(column=column, row=row, value=value)
# for r in dataframe_to_rows(df1, index=False, header=True):
# ws.append(r)
for cell in ws[start_row]:
cell.font = Font(bold=True)
cell.alignment = Alignment(horizontal="center")
cell.fill = PatternFill(start_color="feedc6",
end_color="feedc6",
fill_type="solid")
# merge
# for i in range(start_row, ws.max_row+1):
# ws.merge_cells(start_row=i, start_column=9, end_row=i,
# end_column=16)
# border
BORDER_LIST = [f'A{x}:J{x}' for x in range(start_row, ws.max_row + 1)]
for pos in BORDER_LIST:
set_border(ws, pos)
# center characters
for row_cells in ws.iter_rows(min_row=start_row,
max_row=ws.max_row,
min_col=1,
max_col=2):
for cell in row_cells:
cell.alignment = Alignment(horizontal='center', vertical='center')
for row_cells in ws.iter_rows(min_row=start_row,
max_row=ws.max_row,
min_col=3,
max_col=7):
for cell in row_cells:
cell.alignment = Alignment(horizontal='right', vertical='center')
for row_cells in ws.iter_rows(min_row=start_row,
max_row=ws.max_row,
min_col=8,
max_col=8):
for cell in row_cells:
cell.alignment = Alignment(horizontal='center', vertical='center')
for row_cells in ws.iter_rows(min_row=start_row,
max_row=ws.max_row,
min_col=9,
max_col=10):
for cell in row_cells:
cell.alignment = Alignment(vertical='center', wrapText=True)
# cell.style.alignment.wrap_text = True
ws.column_dimensions['G'].width = 10
ws.column_dimensions['H'].width = 16
ws.column_dimensions['I'].width = 41
ws.column_dimensions['J'].width = 41
chart1 = BarChart()
data = Reference(worksheet=ws,
min_row=start_row + 1,
max_row=ws.max_row,
min_col=3,
max_col=3)
cats = Reference(ws,
min_row=start_row + 1,
max_row=ws.max_row,
min_col=1,
max_col=2)
# set the title of the chart
chart1.title = f'{config.month_name} Regen Timing'
chart1.add_data(data, titles_from_data=False)
chart1.legend = None
chart1.set_categories(cats)
# Style the lines
s1 = chart1.series[0]
s1.graphicalProperties.solidFill = "ff4400"
target_line = LineChart()
target = Reference(worksheet=ws,
min_row=start_row + 1,
max_row=ws.max_row,
min_col=4)
target_line.add_data(target)
chart1 += target_line
ws.add_chart(chart1, "I1")
print('done')
def draw_chart(ws,
type_chart,
position,
title,
min_col_data,
min_row_data,
max_col_data,
max_row_data,
style=2,
legend=None,
x_title=None,
y_title=None,
min_col_cat=None,
min_row_cat=None,
max_col_cat=None,
max_row_cat=None,
width=None,
height=None):
if type_chart == 'bar':
chart = BarChart()
elif type_chart == 'line':
chart = LineChart()
elif type_chart == 'pie':
chart = PieChart()
else:
print('Enter bar, line or pie!')
# default chart
chart = BarChart()
if type_chart != 'pie':
chart.x_axis.title = x_title
chart.y_axis.title = y_title
if legend is None:
chart.legend = legend
if (width and height) is not None:
chart.width = width
chart.height = height
chart.style = style
chart.title = title
data = Reference(ws,
min_col=min_col_data,
min_row=min_row_data,
max_col=max_col_data,
max_row=max_row_data)
chart.add_data(data)
if (min_col_cat and min_row_cat and max_col_cat
and max_row_cat) is not None:
cats = Reference(ws,
min_col=min_col_cat,
min_row=min_row_cat,
max_col=max_col_cat,
max_row=max_row_cat)
chart.set_categories(cats)
ws.add_chart(chart, position)
imgFile = '../crawl/images/aaa.png'
img = openpyxl.drawing.image.Image(imgFile)
sheet.add_image(img, 'B5')
img2 = Image.open(imgFile)
new_img = img2.resize((30, 30))
new_img.save('aaa.png')
img3 = openpyxl.drawing.image.Image('aaa.png')
sheet.add_image(img3, 'D1')
img4 = openpyxl.drawing.image.Image('aaa.png')
sheet.add_image(img4, 'D2')
# chart
sheet1 = book.worksheets[0]
data = Reference(sheet1, min_col=5, min_row=2, max_col=5, max_row=11)
cates = Reference(sheet1, min_col=1, min_row=2, max_row=11)
chart = BarChart()
chart.add_data(data=data)
chart.set_categories(cates)
chart.legend = 'Legend'
# chart.y_axis.majorGridlines = None
chart.varyColors = True
chart.title = "Top 10 Likes"
sheet.add_chart(chart, "A8")
book.save(file)
def create_chart(FILENAME, TABLE_FILENAME):
SMPL = find_smpl_number(FILENAME)
true_smpl_name, median_size = find_true_smpl_name(TABLE_FILENAME, SMPL)
#os.chdir("files")
wb = load_workbook('%s' % FILENAME)
ws = wb.active
max_size = []
end = False
current_row = 5
final_row = 4
rows = []
beginning = True
subsequent_zeros = 0
while (end == False):
if subsequent_zeros > 4:
end = True
d = ws.cell(row=current_row, column=2)
if (d.value != None):
if (d.value == 0 and beginning == True):
current_row += 1
continue
else:
beginning = False
new_row = []
e = ws.cell(row=current_row, column=1)
new_row.append(e.value)
new_row.append(d.value)
rows.append(new_row)
if (d.value == 100):
max_size.append(e.value)
final_row += 1
current_row += 1
if (d.value == 0):
subsequent_zeros += 1
else:
end = True
print(final_row)
print(rows)
wb2 = Workbook()
ws2 = wb2.active
ws2['A1'] = "size"
ws2['B1'] = "frequency"
for row in rows:
ws2.append(row)
chart1 = BarChart()
# chart1.type = "col"
chart1.style = 3
chart1.title = '%s' % true_smpl_name
chart1.y_axis.title = 'normalized frequency / %'
chart1.x_axis.title = 'size / nm'
data = Reference(ws2,
min_col=2,
min_row=2,
max_row=final_row - 4,
max_col=2)
cats = Reference(ws2,
min_col=1,
min_row=2,
max_row=final_row - 4,
max_col=1)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 3
chart1.legend = None
chart1.y_axis.scaling.max = 100
chart1.height = 15
chart1.width = 25
chart1.x_axis.delete = False
chart1.y_axis.delete = False
ws2.add_chart(chart1, "D10")
print(cats)
ws2['D3'] = "median size / nm"
ws2['D4'] = median_size
FILENAME = FILENAME.rstrip('.xlsx')
wb2.save("{}_{}_CHART.xlsx".format(FILENAME, true_smpl_name))
true_smpl_name = group_smpl_by_content(true_smpl_name)
size = [max_size, median_size]
return true_smpl_name, size
break
sheet.cell(row=row_number, column=1).value = key
if data[key]:
sheet.cell(row=row_number, column=2).value = data[key]
else:
sheet.cell(row=row_number, column=2).value = 0
row_number += 1
chartEndRow = row_number - 1
row_number += 1
# グラフ生成
chart = BarChart()
chart.title = master_column
chart.legend = None
titles = Reference(
sheet, min_col=1, min_row=chartStartRow,
max_row=chartEndRow)
values = Reference(
sheet, min_col=2, min_row=chartStartRow,
max_col=2, max_row=chartEndRow)
chart.add_data(values)
chart.set_categories(titles)
chartPosition = 1 + i * 20 #1,21,41,...
sheet.add_chart(chart, f"E{chartPosition}")
book.save(excel_bookname)
def Excel(a, b, c, d):
#Create Data For Excel
PCount = b.count("Pass") #counts passes
FCount = b.count("Fail") #counts failures
FPerc = ((FCount / len(b)) * 100
) #creates a percentage of failures to total operations
FAvg = sum(a) / len(a) #calculates average forced required for press fit
#Start Workbook
wb = Workbook()
#Creating Sheet 1
ws1 = wb.active
ws1.title = d
#Entering Headers
ws1['A1'] = 'Force'
#Turning Data Into An Array of Vectors (Columns in Excel)
data_out1 = column_stack([a])
#Writing to Excel
for row in data_out1.tolist():
ws1.append(row)
ws1['C2'] = 'Total Pass'
ws1['C7'] = 'Total Fail'
ws1['C12'] = 'Percent Fail'
ws1['C17'] = 'Average Force'
ws1['E2'] = PCount
ws1['E7'] = FCount
ws1['E12'] = FPerc
ws1['E17'] = '%.4f' % FAvg
#Merging Cells
ws1.merge_cells('C2:D3')
ws1.merge_cells('E2:E3')
ws1.merge_cells('C7:D8')
ws1.merge_cells('E7:E8')
ws1.merge_cells('C12:D13')
ws1.merge_cells('E12:E13')
ws1.merge_cells('C17:D18')
ws1.merge_cells('E17:E18')
#Creating Alignment Properties of Cells
CELLS = ['A1', 'C2', 'E2', 'C7', 'E7', 'C12', 'E12', 'C17',
'E17'] #List of cells subject to alignment
#For Loop to Align the Cells in CELLS
for i in CELLS:
ws1[i].alignment = Alignment(horizontal='center', vertical='center')
#Defining Border Properties and Applying them with For Loops
thick_border = Border(left=Side(border_style=BORDER_THICK,
color='00000000'), )
for j in range(17):
ws1.cell(row=j + 2, column=3).border = thick_border
ws1.cell(row=j + 2, column=3).border = thick_border
j += 1
for j in range(17):
ws1.cell(row=j + 2, column=6).border = thick_border
ws1.cell(row=j + 2, column=6).border = thick_border
j += 1
#Creating Bar Graph
chart1 = BarChart()
chart1.type = "col"
chart1.style = 1
chart1.title = None
chart1.y_axis.title = 'Force'
chart1.x_axis.title = ''
ChartData = Reference(ws1,
min_col=1,
min_row=2,
max_row=len(a) + 1,
max_col=1)
chart1.add_data(ChartData, titles_from_data=None)
chart1.legend = None
chart1.height = 15
chart1.width = 30
ws1.add_chart(chart1, "G5")
#Saving/Closing
wb.save(c)
def sheet_partecipazione(filename, start_time, participants, workbook):
worksheet = workbook.create_sheet('Partecipazione')
titles = ['Partecipante']
max_interruptions = 0
for pp in participants:
if len(pp['periods']) > max_interruptions:
max_interruptions = len(pp['periods'])
for i in range(max_interruptions):
titles.append(f'Inizio {i+1}')
titles.append(f'Fine {i+1}')
worksheet.append(titles)
for cell in worksheet["1:1"]:
cell.font = Font(bold=True)
i = 1
for pp in participants:
i = i + 1
cur_start = start_time
row = [pp['name']]
for j in range(len(pp['periods'])):
p = pp['periods'][j]
row.append(p[0] - cur_start)
row.append(p[1] - p[0])
cur_start = p[1]
worksheet.append(row)
for ii in range(1, len(row) + 1):
cell = worksheet.cell(i, ii)
cell.number_format = 'hh "ore e" mm "minuti"'
column_widths = [30]
for _i in range(max_interruptions * 2):
column_widths.append(20)
for ii, column_width in enumerate(column_widths):
worksheet.column_dimensions[get_column_letter(ii +
1)].width = column_width
max_col = get_column_letter((max_interruptions * 2) + 1)
mediumStyle = TableStyleInfo(name='TableStyleMedium2', showRowStripes=True)
worksheet.add_table(
Table(ref=f'A1:{max_col}{i}',
displayName='RegistroPartecipazione',
tableStyleInfo=mediumStyle))
worksheet.sheet_view.showGridLines = False
report_id = os.path.basename(filename).replace('.json', '')
worksheet.append([f''])
worksheet.append([f'Report generato per il meeting con ID: {report_id}'])
data = Reference(worksheet,
min_col=2,
min_row=1,
max_row=i,
max_col=(max_interruptions * 2) + 1)
cats = Reference(worksheet, min_col=1, min_row=2, max_row=i)
chart = BarChart()
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.type = "bar"
chart.grouping = "stacked"
chart.overlap = 100
chart.legend = None
chart.height = 20
chart.width = 40
chart.x_axis.scaling.orientation = "maxMin"
for ii, s in enumerate(chart.series):
s.graphicalProperties.line.nofill = True
s.graphicalProperties.line.shadow = None
if ii % 2 == 0:
s.graphicalProperties.noFill = True
else:
s.graphicalProperties.solidFill = "008F00"
worksheet.add_chart(chart, "A43")
wb = Workbook()
ws = wb.active
rows = [['Number', 'Batch 1', 'Batch 2'], #column headings
[2, 40, 30], #Row 2
[3, 40, 25]]
for row in rows:
ws.append(row)
chart = AreaChart() #Create chart. AreaChart3D() for 3D
chart.title = "Area Chart" #title of chart
chart.style = 13 #
chart.x_axis.title = 'Test' #title of x-axis
chart.y_axis.title = 'Percentage' #title of y-axis
chart.legend = None #for the legend
cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "A10")
#Bar Charts
from openpyxl.chart import BarChart, Series, Reference
wb = Workbook(write_only=True)
ws = wb.create_sheet()
rows = [('Number', 'Batch 1', 'Batch 2'),
(2, 10, 30)]
def lesfimi_excel_entire_country_stats():
ref_values = {
1: (20, 55, 75),
2: (40, 85, 100),
3: (55, 100, 120),
4: (80, 120, 145),
5: (90, 140, 160),
6: (105, 155, 175),
7: (120, 165, 190),
8: (130, 180, 210),
9: (140, 180, 210),
10: (145, 180, 210),
}
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response[
'Content-Disposition'] = 'attachment; filename=Lesfimi - Allt Landið.xlsx'
wb = openpyxl.Workbook()
ws = wb.get_active_sheet()
wb.remove_sheet(ws)
tests = (
('b{}_LF_mai17', 'Maí 2017'),
('b{}_LF_jan17', 'Janúar 2017'),
('{}b_LF_sept', 'September 2016'),
)
for test in tests:
identifier = test[0]
title = test[1]
ws = wb.create_sheet(title=title)
ws['A1'] = 'Árgangur'
ws['B1'] = 'Fjöldi nemenda'
ws['C1'] = 'Fjöldi sem þreytti próf'
ws['D1'] = 'Hlutfall sem þreytti próf'
ws['E1'] = 'Hlutfall sem nær 90% viðmiðum'
ws['F1'] = 'Hlutfall sem nær 50% viðmiðum'
ws['G1'] = 'Hlutfall sem nær 25% viðmiðum'
index = 2
errors = []
for year in range(1, 11):
ws['A' + str(index)] = year
survey = Survey.objects.filter(
identifier=identifier.format(year)).first()
studentgroups = StudentGroup.objects.filter(
student_year=year).all()
this_year_result = {
'students': 0,
'students_who_took_test': 0,
'students_over_25pct': 0,
'students_over_50pct': 0,
'students_over_90pct': 0,
}
for studentgroup in studentgroups:
this_year_result['students'] += studentgroup.students.all(
).count()
groupsurveys = GroupSurvey.objects.filter(
studentgroup=studentgroup, survey=survey)
if groupsurveys.all().count() > 1:
errors.append(
'sama próf skráð {} sinnum fyrir {} í {}'.format(
groupsurveys.all().count(), studentgroup.name,
studentgroup.school.name))
for groupsurvey in groupsurveys.all():
for student in studentgroup.students.all():
surveyresults = SurveyResult.objects.filter(
survey=groupsurvey, student=student)
if surveyresults.all().count() > 1:
errors.append(
'{} niðurstöður í sama prófi skráðar fyrir nemanda {} í bekk {} í {}'
.format(
surveyresults.all().count(),
student.ssn,
studentgroup.name,
studentgroup.school.name,
))
for surveyresult in surveyresults.all():
try:
survey_student_result = surveyresult.calculated_results(
)
if not survey_student_result[0] == '':
this_year_result[
'students_who_took_test'] += 1
if int(survey_student_result[0]
) >= ref_values[year][2]:
this_year_result[
'students_over_25pct'] += 1
if int(survey_student_result[0]
) >= ref_values[year][1]:
this_year_result[
'students_over_50pct'] += 1
if int(survey_student_result[0]
) >= ref_values[year][0]:
this_year_result[
'students_over_90pct'] += 1
except:
pass
ws['B' + str(index)] = this_year_result['students']
ws['C' + str(index)] = this_year_result['students_who_took_test']
if this_year_result['students'] > 0 and this_year_result[
'students_who_took_test'] > 0:
ws['D' +
str(index)] = (this_year_result['students_who_took_test'] /
this_year_result['students']) * 100
pct_over_90pct = (
this_year_result['students_over_90pct'] /
this_year_result['students_who_took_test']) * 100
ws['E' + str(index)] = pct_over_90pct
pct_over_50pct = (
this_year_result['students_over_50pct'] /
this_year_result['students_who_took_test']) * 100
ws['F' + str(index)] = pct_over_50pct
pct_over_25pct = (
this_year_result['students_over_25pct'] /
this_year_result['students_who_took_test']) * 100
ws['G' + str(index)] = pct_over_25pct
else:
ws['D' + str(index)] = 0
ws['E' + str(index)] = 0
ws['F' + str(index)] = 0
ws['G' + str(index)] = 0
index += 1
dims = {}
for row in ws.rows:
for cell in row:
if cell.value:
dims[cell.column] = max(
(dims.get(cell.column, 0), len(str(cell.value))))
for col, value in dims.items():
ws.column_dimensions[col].width = int(value) + 2
chart = AreaChart()
chart.title = "Lesfimi í {} - Allt landið".format(title)
chart.style = 10
chart.width = 40
chart.height = 20
chart.layout = Layout(ManualLayout(
xMode="edge",
yMode="edge",
))
chart.x_axis.title = 'Árgangur'
chart.y_axis.title = 'Prósent'
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 100
cats = Reference(ws, min_col=1, min_row=2, max_row=index - 1)
data = Reference(ws, min_col=5, min_row=1, max_col=7, max_row=index)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
bchart = BarChart()
bchart.title = "Hlutfall nemenda sem þreyttu próf"
bchart.style = 10
bchart.width = 20
bchart.height = 10
bchart.x_axis.title = 'Árgangur'
bchart.y_axis.title = 'Prósent'
bchart.y_axis.scaling.min = 0
bchart.y_axis.scaling.max = 100
bdata = Reference(ws, min_col=4, max_col=4, min_row=2, max_row=index)
bchart.add_data(bdata)
bchart.legend = None
bchart.set_categories(cats)
ws.add_chart(bchart, "I1")
if index > 20:
ws.add_chart(chart, "A" + str(index + 2))
else:
ws.add_chart(chart, "A22")
if errors:
ws = wb.create_sheet(title='Villur')
index = 1
for error in errors:
ws['A' + str(index)] = "ATH: " + error
ws['A' + str(index)].fill = PatternFill(start_color='ff0000',
end_color='ff0000',
fill_type='solid')
ws.merge_cells('A' + str(index) + ':F' + str(index))
index += 1
wb.save(filename='/tmp/test.xlsx')
# wb.save(response)
return response
img = openpyxl.drawing.image.Image(imgFile)
col = ((i + 9) // 10) * 2 - 1
row = (7 * i - 6) % 70 + 1
tmpCell = ws2.cell(row=row - 1, column=col)
tmpCell.value = "Top{} Image".format(i)
ws2.add_image(img, '{}{}'.format(chr(col + 64), row))
print("OK === >> {}".format(i))
dat = Reference(ws, min_col=4, min_row=2, max_col=4, max_row=11)
cat = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=11)
barChart = BarChart()
barChart.add_data(data=dat)
barChart.set_categories(cat)
barChart.legend = None # 범례
barChart.varyColors = True
barChart.title = "Top10 Likes"
ws3.add_chart(barChart, "D1")
scatChart = ScatterChart()
scatChart.style = 13
scatChart.x_axis.title = 'SongName'
scatChart.y_axis.title = 'DiffLikes'
xvalues = Reference(ws, min_col=1, min_row=2, max_row=11)
values = Reference(ws, min_col=5, min_row=1, max_row=11)
series = Series(values, xvalues, title_from_data=True)
scatChart.series.append(series)
