def bar(self):
from openpyxl.chart import BarChart3D, Series, Reference
wb = load_workbook(path)
ws = wb['Sheet0']
chart1 = BarChart3D()
#chart1.type = "col"
#chart1.style = 12
chart1.title = "EXCEL DATA"
chart1.y_axis.title = 'Marks'
chart1.x_axis.title = 'Student'
#print(col)
bar_r = ws.max_row
bar_c = ws.max_column
print("row: ", bar_r)
print("col: ", bar_c)
if bar_r <= 2:
bar_r = 3
self.data = Reference(ws,
min_col=4,
min_row=abs(bar_r - 2),
max_row=bar_r,
max_col=bar_c)
chart1.add_data(self.data, titles_from_data=True)
#chart1.set_categories(cats)
#chart1.shape = 4
ws.add_chart(chart1, "J15")
wb.save(path)
def price_updater(filename):
wb = xl.load_workbook(filename)
sheet = wb["Sheet1"]
# cell = sheet["a1"]
cell = sheet.cell(1, 1)
print(cell.value)
# print(sheet.max_row)
for row in range(2, sheet.max_row + 1):
print(row)
cell = sheet.cell(row, 3)
print(cell.value)
corrected_price = (cell.value) * (0.9)
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
cell_updated = sheet.cell(1, 4)
cell_updated.value = "Updated"
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart3D()
chart.add_data(values)
sheet.add_chart(chart, "e2")
wb.save(filename)
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 process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
# cell = sheet['a1']
# cell = sheet.cell(1, 1)
# print(cell.value)
# print(sheet.max_row)
for row in range(2, sheet.max_row + 1):
# print(row)
cell = sheet.cell(row, 3)
# print(cell.value)
corrected_price = cell.value * 0.9
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart3D()
chart.add_data(values)
sheet.add_chart(chart, "e2")
wb.save(filename)
def set_home_page(sheet, num_stocks):
titles = Reference(sheet, min_col=1, min_row=2, max_row=num_stocks+1, max_col=1)
r2_val = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=num_stocks+1)
score_val = Reference(sheet, min_col=3, min_row=1, max_col=3, max_row=num_stocks+1)
chart_1 = create_chart(BarChart3D(), 'Stock names', 'R2_Averages', titles, r2_val, "Model", "b")
chart_2 = create_chart(BarChart3D(), 'Stock names', 'Scores_Averages', titles, score_val, "Model", "r")
chart_1.width = 40 # default is 18.5
chart_2.width = 40 # default is 18.5
sheet.add_chart(chart_1, "F2")
sheet.add_chart(chart_2, "F25")
tab = Table(displayName="Table1", ref=f"A1:C{num_stocks+1}")
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True,
showColumnStripes=True)
tab.tableStyleInfo = style
sheet.add_table(tab)
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")
def create_graph(self):
chart = BarChart3D()
data = Reference(self._ws, min_row=1, min_col=4, max_row=22, max_col=5)
categories = Reference(self._ws, min_row=2, min_col=2, max_row=22)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.style = 10
chart.title = "SiGFP Summary Report"
return chart
def _add_chart(work_sheet, cols, rows, title, pos):
chart = BarChart3D()
chart.title = title
for col in cols:
data = Reference(work_sheet, min_col=col, min_row=1, max_row=rows)
chart.add_data(data, titles_from_data=True)
categories = Reference(work_sheet, min_col=1, min_row=2, max_row=rows)
chart.set_categories(categories)
chart.shape = 'box'
work_sheet.add_chart(chart, "B%s" % pos)
def in_excel(data_list, result_file, sheet_name, col_num, col1, col2, title):
#打开文件
if os.path.exists(result_file):
wb = load_workbook(result_file)
else:
wb = Workbook()
ws = wb.active
#读sheet页
sheet_list = wb.sheetnames #表里面有哪些sheet页
print sheet_list
# sheet = wb.get_sheet_by_name("Sheet3")
# print(sheet["C"]) # (<Cell Sheet3.C1>, <Cell Sheet3.C2>, <Cell Sheet3.C3>, <Cell Sheet3.C4>, <Cell Sheet3.C5>, <Cell Sheet3.C6>, <Cell Sheet3.C7>, <Cell Sheet3.C8>, <Cell Sheet3.C9>, <Cell Sheet3.C10>) <-第C列
# print(sheet["4"]) # (<Cell Sheet3.A4>, <Cell Sheet3.B4>, <Cell Sheet3.C4>, <Cell Sheet3.D4>, <Cell Sheet3.E4>) <-第4行
# print(sheet["C4"].value) # c4 <-第C4格的值
# print(sheet.max_row) # 10 <-最大行数
# print(sheet.max_column) # 5 <-最大列数
#写入sheet页
if sheet_name not in sheet_list:
sheet_request = wb.create_sheet(sheet_name)
else:
sheet_request = wb[sheet_name]
# rows = data_list
# for row in rows:
# sheet_request.append(row)
for i in range(len(data_list)):
# print i[1],data_list[i][1]
# print start_row,start_row + data_list[i][1]-1
# sheet_request['A' + str(start_row)]=data_list[i][0]
sheet_request[col1 + str(i + 1)] = data_list[i][0]
sheet_request[col2 + str(i + 1)] = data_list[i][1]
# 删除sheet
# wb.remove('Sheet')
# wb.remove_sheet('Sheet')
#插入图表
data = Reference(sheet_request,
min_col=col_num,
min_row=1,
max_col=col_num,
max_row=15)
# titles = Reference(sheet_request, min_col=1, min_row=2, max_row=10)
titles = Reference(sheet_request, min_col=title, min_row=2, max_row=15)
chart = BarChart3D()
chart.title = "用户行为分析"
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
sheet_request.add_chart(chart, "E5")
#保存
wb.save(result_file)
def makechart(self, title, pos, width, height, col1, row1, col2, row2,
col3, row3, row4):
data = Reference(self.ws,
min_col=col1,
min_row=row1,
max_col=col2,
max_row=row2)
cat = Reference(self.ws, min_col=col3, min_row=row3, max_row=row4)
chart = BarChart3D()
chart.title = title
chart.width = width
chart.height = height
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(cat)
self.ws.add_chart(chart, pos)
self.wb.save(self.filename)
def make_bar_chart(self):
# self.__wb = load_workbook(self.__filename)
sheetnames = self.__wb.get_sheet_names()
# ws = self.__wb.get_sheet_by_name(sheetnames[0])
ws_new = self.__wb.create_sheet("bar chart")
max_r = self.__ws.max_row - 3
data = Reference(self.__ws, min_col=3, min_row=1, max_col=4, max_row=max_r)
titles = Reference(self.__ws, min_col=1, min_row=2, max_row=max_r)
chart = BarChart3D()
chart.title = "Test Result Bar Chart"
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
chart.x_axis.title = ' '
ws_new.add_chart(chart, "A1")
self.__wb.save(self.__filename)
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb["Sheet1"]
for row in range(1, sheet.max_row + 1):
cell = sheet.cell(row, 1)
enter_value = cell.value * 10
enter_value_cell = sheet.cell(row, 2)
enter_value_cell.value = enter_value
values = Reference(sheet,
min_row=1,
max_row=sheet.max_row,
min_col=2,
max_col=2)
chart = BarChart3D()
chart.add_data(values)
sheet.add_chart(chart, "a15")
wb.save(filename)
def plot_bar_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
)
series_object = xl.chart.Series(values, title="Yearly Revenue")
chart = BarChart3D()
chart.append(series_object)
sheet.add_chart(chart, 'j2')
wb.save('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx')
def bar(self):
from openpyxl.chart import BarChart3D, Reference
b_wb = load_workbook(self.path)
ws = b_wb['Sheet0']
chart1 = BarChart3D()
# adding title, x-axis and y-axis to the bar chart
chart1.title = "EXCEL DATA"
chart1.y_axis.title = 'Marks'
chart1.x_axis.title = 'Student'
bar_r = ws.max_row
bar_c = ws.max_column
if bar_r <= 2:
bar_r = 3
e_data = Reference(ws, min_col=4, min_row=abs(bar_r-2), max_row=bar_r, max_col=bar_c)
chart1.add_data(e_data, titles_from_data=True)
ws.add_chart(chart1, "J15")
b_wb.save(self.path)
def bar_3d():
wb = Workbook()
ws = wb.active
rows = [(None, 2013, 2014), ("Apples", 5, 4), ("Oranges", 6, 2),
("Pears", 8, 3)]
for row in rows:
ws.append(row)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
titles = Reference(ws, min_col=1, min_row=2, max_row=4)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
ws.add_chart(chart, "E5")
wb.save("out/bar3d.xlsx")
def sales_func(filename):
# Load the file and the sheet tab
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
# Create a new price column based on the original price
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = cell.value * 0.9
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
# Bar chart
values = Reference(sheet,
min_row=1,
max_row=sheet.max_row,
min_col=3,
max_col=4)
transaction_id = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=1,
max_col=1)
chart = BarChart3D()
chart.title = '3D Bar Chart'
chart.x_axis.title = "Transaction ID"
chart.y_axis.title = "Adjusted Price ($)"
chart.add_data(values, titles_from_data=True)
chart.set_categories(transaction_id)
sheet.add_chart(chart, 'E2')
# Pie chart
quantities = Reference(sheet, min_row=1, max_row=5, min_col=13, max_col=13)
labels = Reference(sheet, min_row=2, max_row=5, min_col=12, max_col=12)
chart2 = PieChart()
chart2.title = 'Pie Chart'
chart2.add_data(quantities, titles_from_data=True)
chart2.set_categories(labels)
sheet.add_chart(chart2, 'N2')
wb.save(filename)
def excelsheet_read_processor(excelfilename):
path = pathlib.Path()
address = path.glob(excelfilename)
for file in address:
print(file.exists())
wb = xl.load_workbook(file)
sheet = wb['Sheet1']
for cell_value in range(2, sheet.max_row + 1):
print(sheet.cell(cell_value, 2).value)
annual_salary = sheet.cell(cell_value, 2).value * 12
sheet.cell(cell_value, 3).value = annual_salary
values = Reference(
sheet,
min_row=2,
max_row=sheet.max_row,
min_col=3,
max_col=3,
)
chart = BarChart3D()
chart.add_data(values)
sheet.add_chart(chart, 'D2')
wb.save(excelfilename)
def create_bar_chart_2(file_path):
"""
插入3D柱形图
:param file_path: Excel 文件路径
:return: None
"""
wb = load_workbook(file_path)
st = wb.active
data1 = Reference(st, min_col=2, min_row=1, max_row=7, max_col=3)
cats1 = Reference(st, min_col=1, min_row=2, max_row=7)
chart1 = BarChart3D()
chart1.type = "bar"
chart1.style = 10
chart1.title = "日均值对比"
chart1.x_axis.title = None
chart1.shape = 'cylinder'
chart1.add_data(data1, titles_from_data=True)
chart1.set_categories(cats1)
st.add_chart(chart1, 'A26')
wb.save(file_path)
def file_updater(folder):
print("Fetching files ... \nPlease wait . . .")
directory = os.path.join("/mnt/c/Users/7K/", folder)
filename = Path()
os.chdir(directory)
print(f"Currently working at: '{directory}' directory.")
n = 0
for i, filename in enumerate(filename.glob("*.xlsx")):
wb = xl.load_workbook(filename)
sheet = wb["Sheet1"]
cell_ttl = sheet.cell(1, sheet.max_column + 1, value="Updated Price")
# cell_ttl.value = "Updated Price"
for row in range(2, sheet.max_row + 1):
old_cell = sheet.cell(row, sheet.max_column - 1)
upd_val = (old_cell.value) * (1.25)
new_cell = sheet.cell(row, sheet.max_column)
new_cell.value = upd_val
values = Reference(worksheet=sheet,
min_col=4,
max_col=4,
min_row=2,
max_row=sheet.max_row)
chart1, chart2 = BarChart3D(), PieChart3D()
chart1.add_data(values)
chart2.add_data(values)
place1, place2 = "f2", "f20"
sheet.add_chart(chart1, place1)
sheet.add_chart(chart2, place2)
wb.save(f"New_{i+1}-{filename}")
n += 1
print(f"({n} Files Found and Updated)")
return print("\nFiles Succesfully Updated.")
from openpyxl.chart import (AreaChart, Reference, Series, BarChart3D)
wb = load_workbook('G:/Local Disk/Python/ANZ_Day4/LinkData.xlsx',
read_only=False,
data_only=True)
sheet = wb.get_sheet_by_name("Sheet1")
outerList = []
for cell in range(1, sheet.max_row):
innerList = []
for col in range(3, 5):
# print(cell,sheet.cell(row=cell,column=col).value)
innerList.append(sheet.cell(row=cell, column=col).value)
outerList.append(innerList)
print(outerList)
chart = BarChart3D()
chart.title = "Link Utilization"
chart.style = 13
chart.x_axis.title = 'Time'
chart.y_axis.title = 'bandwidth'
cats = Reference(sheet, min_col=3, min_row=1, max_row=7)
data = Reference(sheet, min_col=4, min_row=1, max_col=4, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
sheet.add_chart(chart, "G10")
print(os.getcwd())
wb.save('G:/Local Disk/Python/ANZ_Day4/LinkData.xlsx')
def printPollStat(self, quizStats):
self.addIntoExecutionLog("Poll Statistics is being generated...")
if not os.path.exists('Results'):
os.makedirs('Results')
if not os.path.exists('./Results/poll_statistics'):
os.makedirs('./Results/poll_statistics')
for i, keyQuizName in enumerate(quizStats):
writer = pd.ExcelWriter("./Results/poll_statistics/" +
keyQuizName + "_stats.xlsx",
engine='xlsxwriter')
quizStat = quizStats[keyQuizName]
for j, quizPart in enumerate(quizStat.questionStatDict):
questionStat = quizStat.questionStatDict[quizPart]
answerNumDict = questionStat.getAnswerStat().answerNumDict
dfAnswers = pd.DataFrame(answerNumDict,
columns=answerNumDict.keys(),
index=[0])
dfAnswers = dfAnswers.transpose().rename(columns={
0: "count",
1: "%"
})
nums = [n for n in answerNumDict.values()]
mysum = sum(nums)
for key in answerNumDict:
dfAnswers.loc[key, '%'] = float("{:.2f}".format(
100 * (answerNumDict[key] / mysum)))
dfAnswers.index.name = quizPart.getQuestion().getQuestionText()
dfAnswers.to_excel(writer, sheet_name="q" + str(j + 1))
writer.save()
wb = load_workbook("./Results/poll_statistics/" + keyQuizName +
"_stats.xlsx")
my_blue = openpyxl.styles.colors.Color(rgb='CCE5FF')
my_fill_blue = openpyxl.styles.fills.PatternFill(
patternType='solid', fgColor=my_blue)
my_red = openpyxl.styles.colors.Color(rgb='FFCCCC')
my_fill_red = openpyxl.styles.fills.PatternFill(
patternType='solid', fgColor=my_red)
my_green = openpyxl.styles.colors.Color(rgb='CCFFCC')
my_fill_green = openpyxl.styles.fills.PatternFill(
patternType='solid', fgColor=my_green)
my_orange = openpyxl.styles.colors.Color(rgb='FFE5CC')
my_fill_orange = openpyxl.styles.fills.PatternFill(
patternType='solid', fgColor=my_orange)
for k, ws in enumerate(wb.worksheets):
correctAnswer = ""
for j, quizPart in enumerate(quizStat.questionStatDict):
str2 = str(ws['A1'].value)
qmatch = StringComparator(
quizPart.getQuestion().getQuestionText(),
str2).cmp_ig_CaseSpacePunc
if qmatch == 0:
correctAnswer = quizPart.getQuestion().getAnswer()
break
for row in ws.iter_rows():
for cell in row:
cell.alignment = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=1)
for cell in ws['A']:
cell.alignment = Alignment(horizontal='left',
vertical='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=1)
for column in ws.iter_cols():
column[0].fill = my_fill_blue
for row in ws.iter_rows():
row[0].fill = my_fill_orange
trueCellIndex = 0
for cell in ws['A']:
s = cell.internal_value
if s is not None:
for answer in correctAnswer:
match = StringComparator(str(s),
answer).cmp_ig_C_S_P_N
if match == 0:
cell.fill = my_fill_green
trueCellIndex = cell.row
break
if match != 0:
cell.fill = my_fill_red
ws['A1'].fill = my_fill_orange
ws['A1'].font = Font(bold=True)
ws.column_dimensions['A'].width = 40
ws.column_dimensions['B'].width = 15
ws.column_dimensions['A'].height = 20
p = BarChart3D()
data = Reference(worksheet=ws,
min_col=2,
max_col=ws.max_column,
min_row=2,
max_row=ws.max_row)
# p.add_data(data)
p.title = "Answer Distribution"
pt = DataPoint(idx=trueCellIndex - 2)
pt.graphicalProperties.solidFill = "b2ff59"
answers = Reference(worksheet=ws,
min_col=1,
max_col=1,
min_row=2,
max_row=ws.max_row)
l = list(ws.iter_rows())
g = 2
for row in ws.iter_rows():
value = Reference(worksheet=ws,
min_col=3,
max_col=3,
min_row=g,
max_row=g)
ans = str(ws['A' + str(g)].internal_value)
serie = Series(value, title=ans)
if ans != None:
for answer in correctAnswer:
match = StringComparator(ans,
answer).cmp_ig_C_S_P_N
if match == 0:
serie.graphicalProperties.solidFill = "b2ff59"
break
p.append(serie)
g += 1
p.height = 13
p.width = 20
p.style = 2
p.legend.legendPos.upper()
p.y_axis.scaling.min = 0
p.y_axis.scaling.max = 100
p.y_axis.title = "%"
ws.add_chart(p, "D1")
k += 1
wb.save("./Results/poll_statistics/" + keyQuizName + "_stats.xlsx")
self.addIntoExecutionLog(
keyQuizName +
"_stats.xlsx file has been generated in 'poll_statistics' directory"
)
# create sheets #
for x in range(len(new_years.months_two)):
create_month_v4(x, counter)
create_day_v4(x)
counter = counter + 1
counter = 8
for y in range(len(new_years.months_two)):
new_years.expenses_overview(y, counter, overview_sheet)
counter = counter + 1
# create charts #
chart_sheet = workbook.create_sheet("Charts", 0)
expenses_in_total = BarChart3D()
expenses_in_total.style = 48
ref_obj = openpyxl.chart.Reference(overview_sheet,
min_col=11,
min_row=8,
max_col=11,
max_row=19)
ref_names = Reference(overview_sheet,
min_col=9,
min_row=8,
max_col=9,
max_row=19)
real_obj = openpyxl.chart.Series(ref_obj, title="Expenses 2020 per month")
expenses_in_total.title = "Expenses in 2020 TOTAL"
expenses_in_total.add_data(data=ref_obj, titles_from_data=False)
expenses_in_total.set_categories(ref_names)
else:
# ws = wb.get_sheet_by_name('Sheet0')
ws = wb['Sheet0']
s = ws.max_row
for i in range(1, len(head)+1):
ws.cell(row=s + 1, column=i).value = data[i - 1]
wb.save(path)
if found == 0:
print("DATA NOT FOUND")
from openpyxl.chart import BarChart3D, Series, Reference
wb = load_workbook(path)
ws = wb['Sheet0']
chart1 = BarChart3D()
#chart1.type = "col"
#chart1.style = 12
chart1.title = "EXCEL DATA"
chart1.y_axis.title = 'Marks'
chart1.x_axis.title = 'Student'
print(col)
bar_r = ws.max_row
bar_c = ws.max_column
print("row: ",bar_r)
print("col: ",bar_c)
data = Reference(ws, min_col=4, min_row=bar_r-2, max_row=bar_r, max_col=bar_c)
def writeOutput2(outfile,records): #################################### wb = Workbook() summaryColumn = 3 for record in records: sheetname = str(label[record]) wb.create_sheet(sheetname) ws = wb[str(sheetname)] wsummary = wb.worksheets[0] # write labels ws.cell(row=1, column=1).value = "code" ws.cell(row=1, column=2).value = "name" columnVar = 1 rowVar = 2 for x in sorted(major): ws.cell(row=rowVar, column=columnVar).value = x ws.cell(row=rowVar, column=columnVar+1).value = label[x] rowVar += 1 columnVar = 3 rowVar = 2 for r in records[record]: # r is column ws.cell(row=1, column=columnVar).value = label[r] for x in xrange(0,len(major)): code = str(ws.cell(row=rowVar+x, column=1).value) if code in records[record][r]: ws.cell(row=rowVar+x, column=columnVar).value = records[record][r][code] if isinstance(wsummary.cell(row=rowVar+x, column=summaryColumn).value,float): wsummary.cell(row=rowVar+x, column=summaryColumn).value += float(records[record][r][code]) else: wsummary.cell(row=rowVar+x, column=summaryColumn).value = float(records[record][r][code]) columnVar += 1 for col in ws.columns: max_length = 0 column = col[0].column # Get the column name for cell in col: try: # Necessary to avoid error on empty cells if len(str(cell.value)) > max_length: max_length = len(cell.value) except: pass adjusted_width = (max_length) * 1 ws.column_dimensions[column].width = adjusted_width summaryColumn+=1 data = Reference(ws, min_col=3, min_row=1, max_col=columnVar-1, max_row=6) titles = Reference(ws, min_col=2, min_row=2, max_row=6) chart = BarChart() chart.title = label[record] chart.add_data(data=data, titles_from_data=True) chart.set_categories(titles) chart.x_axis.delete = False chart.y_axis.delete = False ws.add_chart(chart, "A10") #################################### # write values ws = wb.worksheets[0] ws.title="summary" columnVar = 1 rowVar = 2 # write labels ws.cell(row=1, column=1).value = "code" ws.cell(row=1, column=2).value = "name" for x in sorted(major): ws.cell(row=rowVar, column=columnVar).value = x ws.cell(row=rowVar, column=columnVar+1).value = label[x] rowVar +=1 # make multiple outputs per project and by per employee columnVar = 1 rowVar = 1 for record in records: ws.cell(row=rowVar, column=columnVar+2).value = label[record] columnVar+=1 for col in ws.columns: max_length = 0 column = col[0].column # Get the column name for cell in col: try: # Necessary to avoid error on empty cells if len(str(cell.value)) > max_length: max_length = len(cell.value) except: pass adjusted_width = (max_length) * 1 ws.column_dimensions[column].width = adjusted_width print outfile, columnVar data = Reference(ws, min_col=3, min_row=1, max_col=columnVar+1, max_row=6) titles = Reference(ws, min_col=2, min_row=2, max_row=6) chart = BarChart3D() chart.title = "3D Bar Chart" chart.add_data(data=data, titles_from_data=True) chart.set_categories(titles) ws.add_chart(chart, "A10") chart3 = BarChart() chart3 = deepcopy(chart) chart3.type = "col" chart3.style = 10 chart3.grouping = "stacked" chart3.overlap = 100 chart3.title = 'Stacked Chart' chart3.set_categories(titles) ws.add_chart(chart3, "A30") chart.title.delete = False chart.x_axis.delete = False chart.y_axis.delete = False chart3.title.delete = False chart3.x_axis.delete = False chart3.y_axis.delete = False wb.save(year+"_"+month+"_"+outfile)
插入柱形图
:param file_path: Excel 文件路径
:param bar_chart 要添加的图表
:param chart_location 图表位置
:return: None
"""
wb = load_workbook(file_path)
st = wb.active
data1 = Reference(st, min_col=4, min_row=1, max_col=7, max_row=6)
cats1 = Reference(st, min_col=1, min_row=2, max_row=6)
chart1 = bar_chart
chart1.type = "col"
chart1.style = 10
chart1.title = 'rustfisher.com'
chart1.x_axis.title = None
chart1.add_data(data1, titles_from_data=True)
chart1.set_categories(cats1)
st.add_chart(chart1, chart_location)
wb.save(file_path)
if __name__ == '__main__':
excel_path = 'out/指数.xlsx'
create_bar_chart_1(excel_path)
create_bar_chart_2(excel_path)
create_bar_chart_for_ratios(excel_path, BarChart())
create_bar_chart_for_ratios(excel_path, BarChart3D(), chart_location='F26')
def testChart(file, scores):
#have a test
wb = Workbook(write_only=True)
ws_sta = wb.create_sheet()
#header = [
# ' ', 'Discussion', 'Implementation started', 'Implementation Done', 'Total Innovation Point'
#]
headers = row_labels
headers.insert(0, ' ')
revertrows = list()
for i in range(len(team_list)):
revertrow = [x[i] for x in scores]
revertrow.insert(0, team_list[i])
print("revertrow:", revertrow)
revertrows.append(revertrow)
print("revertrows:", revertrows)
ws_sta.append(headers)
for row in revertrows:
print(row)
ws_sta.append(row)
data = Reference(ws_sta,
min_col=2,
min_row=1,
max_col=4,
max_row=len(team_list) + 1)
titles = Reference(ws_sta,
min_col=1,
min_row=2,
max_row=len(team_list) + 1)
print("data:", data)
print("title:", titles)
chart = BarChart3D()
chart.title = "Innovation Statistic - Idea status"
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
ws_sta.add_chart(chart, "A15")
data = Reference(ws_sta,
min_col=5,
min_row=1,
max_col=5,
max_row=len(team_list) + 1)
titles = Reference(ws_sta,
min_col=1,
min_row=2,
max_row=len(team_list) + 1)
print("data:", data)
print("title:", titles)
chart = BarChart3D()
chart.title = "Innovation Statistic - Innovation Point"
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
ws_sta.add_chart(chart, "I15")
wb.save(file)
work_book.save('AirLineBumps_bar_chart.xlsx')
from copy import deepcopy
hor_chart = deepcopy(chart)
hor_chart.type = "bar"
hor_chart.title = "Horizontal Bar Chart"
sheet.add_chart(hor_chart, "H16")
work_book.save('AirLineBumps_bar_chart.xlsx')
stacked_chart = deepcopy(chart)
stacked_chart.type = "col"
stacked_chart.grouping = "stacked"
stacked_chart.overlap = 100
stacked_chart.title = "Stacked Chart"
sheet.add_chart(stacked_chart, "N1")
work_book.save('AirLineBumps_bar_chart.xlsx')
from openpyxl.chart import BarChart3D
chart_3d = BarChart3D()
chart_3d.type = "col"
chart_3d.title = "3D Bar Chart"
chart_3d.y_axis.title = "Number of bumps"
chart_3d.x_axis.title="Airline"
chart_3d.add_data(data, titles_from_data = True)
chart_3d.set_categories(cats)
sheet.add_chart(chart_3d, "N16")
work_book.save('AirLineBumps_bar_chart.xlsx')
# ch19_33.py
import openpyxl
from openpyxl.chart import BarChart3D, Reference
wb = openpyxl.Workbook() # 開啟活頁簿
ws = wb.active # 獲得目前工作表
rows = [['', '2020年', '2021年'], ['亞洲', 100, 300], ['歐洲', 400, 600],
['美洲', 500, 700], ['非洲', 200, 100]]
for row in rows:
ws.append(row)
chart = BarChart3D() # 3D長條圖
chart.title = '深石軟件銷售表' # 圖表標題
chart.y_axis.title = '業績金額' # y軸標題
chart.x_axis.title = '地區' # x軸標題
data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=5) # 圖表資料
chart.add_data(data, titles_from_data=True) # 建立圖表
xtitle = Reference(ws, min_col=1, min_row=2, max_row=5) # x軸標記名稱
chart.set_categories(xtitle) # 設定x軸標記名稱(亞洲歐洲美洲非洲)
ws.add_chart(chart, 'E1') # 放置圖表位置
wb.save('out19_33.xlsx')
# 获取文件夹下的所有文件名
file_list = os.listdir(path)
# 遍历文件名列表，取得每一个文件名
for file_name in file_list:
# 拼接文件路径
file_path = path + file_name
print('正在处理：' + file_name)
# 读取工作簿
wb = load_workbook(file_path)
# 读取工作簿中的活跃工作表
ws = wb.active
# 实例化 LineChart 类，得到 折线图 - LineChart 对象；柱状图 - BarChart3D 对象
chart1 = LineChart()
chart2 = BarChart3D()
# 引用工作表的部分数据
data = Reference(worksheet=ws, min_row=3, max_row=9, min_col=1, max_col=5)
# 添加被引用的数据到 LineChart 对象 chart.add_data(data, from_rows=True, titles_from_data=True)
chart1.add_data(data, from_rows=True, titles_from_data=True)
chart2.add_data(data, from_rows=True, titles_from_data=True)
# chart1.varyColors = True
# chart2.varyColors = True
# 添加 LineChart 对象到工作表中，指定折线图的位置
ws.add_chart(chart1, "C12")
ws.add_chart(chart2, "C29")
# 引用工作表的表头数据
cats = Reference(worksheet=ws, min_row=2, max_row=2, min_col=2, max_col=5)
# 设置类别轴的标签
chart1.set_categories(cats)