def areachart3(self):
wb = Workbook()
ws = wb.active
rows = [
['Number', 'Batch 1', 'Batch 2'],
[2, 30, 40],
[3, 25, 40],
[4, 30, 50],
[5, 10, 30],
[6, 5, 25],
[7, 10, 50],
]
for row in rows:
ws.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(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")
wb.save("area3D.xlsx")
def plot_area_chart3_d():
wb = xl.load_workbook('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx') # load xl workbook
sheet = wb['dATA'] # get the worksheet
for i in range(2, 10):
cell = sheet['f'+str(i)]
sheet['a'+str(i)] = cell.value
values = Reference(
sheet,
min_row=2,
max_row=10,
min_col=1,
max_col=1
)
seriesobject = xl.chart.Series(values, title="Yearly Revenue")
chart = AreaChart3D()
chart.append(seriesobject)
sheet.add_chart(chart, 'j2')
wb.save('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx')
def process_workbook(stock_names, predictions, prices, r2_scores, scores, score_avgs, r2_avgs):
# Creating the xl file with the analyzed data
wb = Workbook()
ws = wb.create_sheet('Home-Page')
ws.append(['Stock', 'R2_avgs', 'Scores_avgs'])
for i, stock in enumerate(stock_names):
ws1 = wb.create_sheet(stock)
ws1.append(['Days', 'Values', 'R2_Scores', 'My_Scores'])
ws1.append(['Price', prices[i], 1, 0])
for j, raw in enumerate(preds):
ws1.append([raw, predictions[j][i], r2_scores[j][i], scores[j][i]])
# Creating and adding the charts of the analyzed data
titles = Reference(ws1, min_col=1, min_row=2, max_row=12, max_col=1)
data_val = Reference(ws1, min_col=2, min_row=1, max_col=2, max_row=12)
data_r2_score = Reference(ws1, min_col=3, min_row=1, max_col=3, max_row=12)
data_my_score = Reference(ws1, min_col=4, min_row=1, max_col=4, max_row=12)
chart_1 = create_chart(AreaChart3D(), 'Predictions', 'Values', titles, data_val, stock, "y")
chart_2 = create_chart(BarChart3D(), 'Predictions', 'R2_Scores', titles, data_r2_score, stock, "b")
chart_3 = create_chart(BarChart3D(), 'Predictions', 'My_Scores', titles, data_my_score, stock, "r")
ws1.add_chart(chart_1, "F2")
ws1.add_chart(chart_2, "F25")
ws1.add_chart(chart_3, "P2")
# Add a table
tab = Table(displayName="Table1", ref="A1:D12")
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
ws1.add_table(tab)
# Add hyperlinks with font between the current sheet to the home page sheet
ft = Font(color=colors.RED)
ws1.cell(row=len(preds) + 4, column=2).value = "Home-Page"
ws1.cell(row=len(preds) + 4, column=2).font = ft
ws1.cell(row=len(preds) + 4, column=2).hyperlink = '#\'Home-Page\'!A1'
ws.append([' '+stock_names[i], r2_avgs[i], score_avgs[i]])
ws.cell(row=i+2, column=1).hyperlink = f'#{stock_names[i]}!A1'
ws.cell(row=i + 2, column=1).font = ft
std = wb.get_sheet_by_name('Sheet')
wb.remove_sheet(std)
set_home_page(ws, len(stock_names))
wb.save(f"xl_Static_analyzes/Data_Anlaz_{today}.xlsx")
['E', 25, 5],
['F', 50, 10],
]
for row in rows:
ws.append(row)
chart = AreaChart()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
# yData needs to start on row=1 to include titles
xData = Reference(ws, min_col=1, min_row=2, max_row=7)
yData = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=7)
chart.add_data(yData, titles_from_data=True)
chart.set_categories(xData)
ws.add_chart(chart, "C10")
chart = AreaChart3D()
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, "B30")
wb.save('data/charts.xlsx')