def create_pie_chart(sheet, numOfStocks, sectors):
dictSector = Counter(sectors)
pie = PieChart()
labels = Reference(sheet, min_col=1, min_row=2, max_row=numOfStocks + 1)
data = Reference(sheet, min_col=5, min_row=1, max_row=numOfStocks + 1)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Percentage of Shares in Portfolio"
pie.height = 12
pie.width = 20
sheet.add_chart(pie, "K1")
pie2 = PieChart()
labels = Reference(sheet,
min_col=7,
min_row=27,
max_row=26 + len(dictSector))
data = Reference(sheet,
min_col=8,
min_row=26,
max_row=26 + len(dictSector))
pie2.add_data(data, titles_from_data=True)
pie2.set_categories(labels)
pie2.title = "Percentage of Sectors in Portfolio"
pie2.height = 12
pie2.width = 20
sheet.add_chart(pie2, "K26")
def contoh_gauge_chart(self, ws):
data = [
["Donut", "Pie"],
[25, 75],
[50, 1],
[25, 124],
[100],
]
# based on http://www.excel-easy.com/examples/gauge-chart.html
for row in data:
ws.append(row)
# First chart is a doughnut chart
c1 = DoughnutChart(firstSliceAng=270, holeSize=50)
c1.title = "Code coverage"
c1.legend = None
ref = Reference(ws, min_col=1, min_row=2, max_row=5)
s1 = Series(ref, title_from_data=False)
slices = [DataPoint(idx=i) for i in range(4)]
slices[0].graphicalProperties.solidFill = "FF3300" # red
slices[1].graphicalProperties.solidFill = "FCF305" # yellow
slices[2].graphicalProperties.solidFill = "1FB714" # green
slices[3].graphicalProperties.noFill = True # invisible
s1.data_points = slices
c1.series = [s1]
# Second chart is a pie chart
c2 = PieChart(firstSliceAng=270)
c2.legend = None
ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4)
s2 = Series(ref, title_from_data=False)
slices = [DataPoint(idx=i) for i in range(3)]
slices[0].graphicalProperties.noFill = True # invisible
slices[1].graphicalProperties.solidFill = "000000" # black needle
slices[2].graphicalProperties.noFill = True # invisible
s2.data_points = slices
c2.series = [s2]
c1 += c2 # combine charts
ws.add_chart(c1, "D1")
# Second chart is a pie chart
c2 = PieChart(firstSliceAng=270)
# c2.legend = None
ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4)
s2 = Series(ref, title_from_data=False)
# slices = [DataPoint(idx=i) for i in range(3)]
# slices[0].graphicalProperties.noFill = True # invisible
# slices[1].graphicalProperties.solidFill = "000000" # black needle
# slices[2].graphicalProperties.noFill = True # invisible
# s2.data_points = slices
c2.series = [s2]
ws.add_chart(c2, "D20")
def pie_chart():
data = [
['Pie', 'Sold'],
['Apple', 50],
['Cherry', 30],
['Pumpkin', 10],
['Chocolate', 40],
]
wb = Workbook()
ws = wb.active
for row in data:
ws.append(row)
pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Pies sold by category"
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
ws.add_chart(pie, "D1")
wb.save("chart.xlsx")
def chartBreakdown():
'''Makes pie chart and bar chart for summary tab'''
pie = PieChart()
z = len(categories)
data = Reference(Sum, min_col=9, min_row=2, max_row= z-1)
labels = Reference(Sum, min_col=8, min_row=2, max_row= z-1)
pie.add_data(data)
pie.set_categories(labels)
pie.title = 'Breakdown of Expenses'
pie.width = 15.0
pie.height = 12.0
pie.legend.layout = Layout(manualLayout=ManualLayout(x=0.25, y=0.25, h=0.99, w=0.25))
Sum.add_chart(pie, 'A1')
pie.dataLabels = DataLabelList()
pie.dataLabels.showPercent = True
bar = BarChart()
barData1 = Reference(Sum, min_col=mNum+9, min_row=1, max_row=z-1)
barData2 = Reference(Sum, min_col=mNum+12, min_row=1, max_row=z-1)
bar.add_data(barData1, titles_from_data=True)
bar.add_data(barData2, titles_from_data=True)
bar.set_categories(labels)
bar.title = 'Goal Comparison'
bar.width = 2.0*z
bar.height = 12.0
bar.legend.layout = Layout(manualLayout=ManualLayout(x=0.25, y=0.25, h=1.99, w=0.25))
Sum.add_chart(bar, 'A28')
def excel_automation(filename):
# for experiment, ignore it
# wb = xl.load_workbook("transactions.xlsx")
wb = xl.load_workbook(f"{filename}")
sheet = wb['Sheet1']
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
# add corrected formula here
corrected_price = cell.value * 0.7
corrected_price_cell = sheet.cell(row, sheet.max_column + 1)
corrected_price_cell.value = corrected_price
graphValues = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = PieChart()
chart.add_data(graphValues)
sheet.add_chart(chart, 'f2')
wb.save(f'{filename}')
def pieChart():
from openpyxl import Workbook
from openpyxl.chart import PieChart, Reference
data = [
['水果', '销量'],
['苹果', 50],
['樱桃', 30],
['橘子', 10],
['香蕉', 40],
]
wb = Workbook()
ws = wb.active
for row in data:
ws.append(row)
pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "水果销量占比"
ws.add_chart(pie, "D1")
wb.save('piechart.xlsx')
print('打开文件 piechart.xlsx 查看结果')
def pie_chart(wp):
table = wp.get_sheet_by_name(wp.get_sheet_names()[0])
#生成饼图对象
pie = PieChart()
#图的标题
pie.title = "API接口测试统计"
'''行数和列数都是从1开始的，和遍历用例是一样都是从1开始'''
#获取标签(取范围第一列的最小行数和最大行数)
labels = Reference(table, min_col=4, min_row=6, max_col=4, max_row=7)
#获取数据（取范围第二列的最小行数-1和最大行数）
data = Reference(table, min_col=5, min_row=5, max_col=5, max_row=7)
#添加数据和标签到饼图中
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
#放在excel表中
table.add_chart(pie, "A10")
#保存excel
# wb.save("test1.xlsx")
# wb = load_workbook("D:\\PycharmProjects\\test.xlsx")
# pie_chart(wb)
def __init__(self, db_name, save_atexit_only=True):
self.db_name = db_name
if path.exists(self.db_name):
self.wb = openpyxl.load_workbook(self.db_name)
self.sheet = self.wb.active
else:
self.wb = openpyxl.Workbook()
self.sheet = self.wb.active
data = Reference(self.sheet,
min_col=4,
min_row=1,
max_col=6,
max_row=2)
chart = PieChart()
chart.title = 'Статистика'
chart.add_data(data, titles_from_data=True)
self.sheet.add_chart(chart, 'D4')
self.sheet['D1'] = 'Негативных'
self.sheet['D2'] = '=COUNTIF(B3:B20000,"Негативный")'
self.sheet['E1'] = 'Нейтральных'
self.sheet['E2'] = '=COUNTIF(B3:B20000,"Нейтральный")'
self.sheet['F1'] = 'Позитивных'
self.sheet['F2'] = '=COUNTIF(B3:B20000,"Позитивный")'
if save_atexit_only:
atexit.register(self.save)
def create_chart_file(name_file, ws_result):
actual_path = os.path.abspath(os.path.dirname(__file__))
result_file_path = os.path.join(actual_path,
f"../files/result_files/{name_file}.xlsx")
try:
wb = openpyxl.load_workbook(result_file_path)
chart_worksheet = wb.create_sheet(title="Gráfico")
chart_worksheet['A1'] = "Total Sucess"
chart_worksheet['A2'] = "Total Failed"
chart_worksheet['B1'] = f'=COUNTIF(${ws_result}.E:E;"Sucess")'
chart_worksheet['B2'] = f'=COUNTIF(${ws_result}.E:E;"Failed")'
pie_chart = PieChart()
values = Reference(chart_worksheet,
min_col=1,
min_row=1,
max_col=2,
max_row=2)
labels = Reference(chart_worksheet,
min_col=1,
min_row=1,
max_col=2,
max_row=2)
pie_chart.add_data(values, titles_from_data=True)
pie_chart.set_categories(labels)
chart_worksheet.add_chart(pie_chart, "A1")
wb.save(result_file_path)
except Exception as e:
print(e)
def make_pie(sheet, left_col, top_row, bot_row, title, print_cell, height,
width):
left_col = int(left_col)
right_col = left_col + 1
top_row = int(top_row)
bot_row = int(bot_row)
title = str(title)
print_cell = str(print_cell)
height = float(height)
width = float(width)
pie = PieChart()
labels = Reference(sheet,
min_col=left_col,
max_col=left_col,
min_row=top_row + 1,
max_row=bot_row)
data = Reference(sheet,
min_col=right_col,
max_col=right_col,
min_row=top_row,
max_row=bot_row)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = title
pie.height = height
pie.width = width
sheet.add_chart(pie, print_cell)
return None
def add_pie_chart(writer, df):
# 初始化excel并确立图表类型
sheet = writer.sheets['全球最新疫情分布']
chart = PieChart()
# 设置插入序列
max_row = len(df)
labels = Reference(sheet,
min_col=2,
max_col=2,
min_row=2,
max_row=max_row + 1)
chart_data = Reference(sheet,
min_col=3,
max_col=3,
min_row=1,
max_row=max_row + 1)
chart.add_data(chart_data, titles_from_data=True)
chart.set_categories(labels)
# 设置图表格式
chart.title = "除湖北外全球各地区确诊人数占比"
chart.height, chart.width = 14, 21
chart.style = 5
chart.dataLabels = DataLabelList()
chart.dataLabels.showCatName = True
chart.dataLabels.showPercent = True
# 插入图表
sheet.add_chart(chart, 'F1')
writer.save()
def summarize_amount(file: str, sheet_name: str):
wb = openpyxl.load_workbook(file)
sheet = wb[sheet_name]
last_row = sheet.max_row
last_col = sheet.max_column
for i in range(2, last_row + 1):
for j in range(6, last_col):
sheet.cell(row=i, column=j).number_format = '#,##,0.00'
sheet.cell(row=i, column=last_col).number_format = '0.00%'
summaries = [
{'location': (last_row + 2, 1),
'letter': 'A',
'labels': ['招商银行', '恒生银行', '银河', '华盛*', '富途*', '蛋卷*', '同花顺'],
'category': 'platform',
'anchor': 'K1'},
{'location': (last_row + 2, 4),
'letter': 'B',
'labels': ['rmb', 'hkd', 'usd'],
'category': 'currency',
'anchor': 'K16'},
{'location': (last_row + 2, 7),
'letter': 'E',
'labels': [0, 1, 2, 3],
'category': 'risk',
'anchor': 'K31'}
]
for summary in summaries:
row, col = summary['location']
le = [get_column_letter(j) for j in range(col, col + 3)]
for i in range(len(summary['labels'])):
sheet.cell(row=row+i, column=col).value = summary['labels'][i]
c = sheet.cell(row=row+i, column=col+1)
c.number_format = "#,##,0.00"
c.value = '=SUMIF(${0}$2:${0}${1},{2}{3},$H$2:$H${1})'.format(summary['letter'],
last_row, le[0], row + i)
c = sheet.cell(row=row+i, column=col+2)
c.number_format = "#,##,0.00"
c.value = '=SUMIF(${0}$2:${0}${1},{2}{3},$I$2:$I${1})'.format(summary['letter'],
last_row, le[0], row + i)
sheet.cell(row=row+i+1, column=col).value = 'sum'
c = sheet.cell(row=row+i+1, column=col+1)
c.number_format = "#,##,0.00"
c.value = '=SUM({0}{1}:{0}{2})'.format(le[1], row, row + i)
c = sheet.cell(row=row+i+1, column=col+2)
c.number_format = "#,##,0.00"
c.value = '=SUM({0}{1}:{0}{2})'.format(le[2], row, row + i)
pie = PieChart()
labels = Reference(sheet, min_col=col, min_row=row, max_row=row+i)
data = Reference(sheet, min_col=col+1, min_row=row-1, max_row=row+i)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = summary['category']
sheet.add_chart(pie, summary['anchor'])
wb.save(file)
def test_duplicate_chart(ExcelWriter, archive):
from openpyxl.chart import PieChart
pc = PieChart()
wb = Workbook()
writer = ExcelWriter(wb, archive)
writer._charts = [pc] * 2
with pytest.raises(InvalidFileException):
writer._write_charts()
def graphs(wb):
ws_sky=wb.worksheets[1]
ws_RC=wb.worksheets[2]
ws_both=wb.worksheets[3]
chart_sky = PieChart()
labels_Sky = Reference(ws_sky, min_col = 7, max_col=8, min_row = 1)
data_Sky = Reference(ws_sky, min_col = 7,max_col=8, min_row = 2)
chart_sky.add_data(data_Sky, titles_from_data = True)
# set labels in the chart object
chart_sky.set_categories(labels_Sky)
# set the title of the chart
chart_sky.title = " Porcentaje de Placas "
ws_sky.add_chart(chart_sky, "F9")
chart_RC = PieChart()
labels_RC = Reference(ws_RC, min_col = 8, max_col=9, min_row = 1)
data_RC = Reference(ws_RC, min_col = 8,max_col=9, min_row = 2)
chart_RC.add_data(data_RC, titles_from_data = True)
# set labels in the chart object
chart_RC.set_categories(labels_RC)
# set the title of the chart
chart_RC.title = " Porcentaje de Lineas "
ws_RC.add_chart(chart_RC, "I9")
chart_both = PieChart()
labels_both = Reference(ws_both, min_col = 8, max_col=9, min_row = 1)
data_both = Reference(ws_both, min_col = 8,max_col=9, min_row = 2)
chart_both.add_data(data_both, titles_from_data = True)
# set labels in the chart object
chart_both.set_categories(labels_both)
# set the title of the chart
chart_both.title = " Porcentaje de Viajes "
ws_both.add_chart(chart_both, "F9")
def _draw_pie_charts(self):
"""
画两个饼图
:return: None
"""
ws = self._wb['analysis']
# 设置单元格值，饼图引用
ws['G3'] = '失败'
ws['G4'] = '通过'
ws['H3'] = self._api_failure
ws['H4'] = self._api_num - self._api_failure
ws['N3'] = '失败'
ws['N4'] = '通过'
ws['O3'] = self._case_failure
ws['O4'] = self._case_num - self._case_failure
# 画接口饼图
pie = PieChart()
labels = Reference(ws, min_col=7, min_row=3, max_row=4)
data = Reference(ws, min_col=8, min_row=2, max_row=4)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "接口执行情况"
slice_ = DataPoint(idx=0, explosion=10)
pie.series[0].data_points = [slice_]
ws.add_chart(pie, "F1")
pie.height = 9.5
pie.width = 13
self._log.info('已生成接口执行情况饼图.')
# 画用例饼图
pie2 = PieChart()
labels2 = Reference(ws, min_col=14, min_row=3, max_row=4)
data2 = Reference(ws, min_col=15, min_row=2, max_row=4)
pie2.add_data(data2, titles_from_data=True)
pie2.set_categories(labels2)
pie2.title = "用例执行情况"
slice2_ = DataPoint(idx=0, explosion=10)
pie2.series[0].data_points = [slice2_]
ws.add_chart(pie2, "M1")
pie2.height = 9.5
pie2.width = 13
self._log.info('已生成用例执行情况饼图.')
def draw_chart(input_wb, max_row):
# 拿到我们需要操作的sheet top10
top_10_sheet = input_wb.worksheets[-1]
top_10_sheet.sheet_view.zoomScale = 200
# 初始化chart
bar_chart = BarChart()
line_chart = LineChart()
pie_chart = PieChart()
# 生成数据
bar_chart_data = Reference(top_10_sheet,
min_col=4,
max_col=7,
min_row=1,
max_row=max_row)
line_chart_data = Reference(top_10_sheet,
min_col=4,
max_col=4,
min_row=1,
max_row=max_row)
pie_chart_data = Reference(top_10_sheet,
min_col=4,
max_col=4,
min_row=1,
max_row=max_row)
# 指定chart的x_axis
x_data = Reference(top_10_sheet,
min_col=2,
max_col=2,
min_row=2,
max_row=max_row)
# 设置chart样式
bar_chart.height, bar_chart.width = 7, 15
line_chart.height, line_chart.width = 7, 15
pie_chart.height, pie_chart.width = 7, 15
bar_chart.title, bar_chart.y_axis.title, bar_chart.x_axis.title = 'top10', '人数', '国家'
# bar_chart.y_axis.scaling.max = 5000000
# 把数据添加进chart
bar_chart.add_data(bar_chart_data, titles_from_data=True)
line_chart.add_data(line_chart_data, titles_from_data=True)
bar_chart.set_categories(x_data)
line_chart.set_categories(x_data)
pie_chart.add_data(pie_chart_data, titles_from_data=True)
pie_chart.set_categories(x_data)
pie_chart.dataLabels = DataLabelList()
pie_chart.dataLabels.showVal = True
pie_chart.dataLabels.showLegendKey = True
# 把chart添加到sheet
# top_10_sheet.add_chart(bar_chart, 'I1')
# top_10_sheet.add_chart(pie_chart, 'I11')
bar_chart += line_chart
top_10_sheet.add_chart(bar_chart, 'I1')
# 保存我们的workbook
input_wb.save('./excel_files/report_chart.xlsx')
def chartInit(self):
'''
画一个饼图
:return:
'''
self.pie = PieChart()
lables = Reference(self.ws, min_col=5, min_row=2, max_row=3)
data = Reference(self.ws, min_col=6, min_row=2, max_row=3)
self.pie.add_data(data)
self.pie.set_categories(lables)
self.pie.title = '通过率对比图'
self.ws.add_chart(self.pie, 'B8')
def plot_sentiment(tickers):
df = scored_news(tickers)
#getrange of headlines dates and daily scores
dates = date_range(df)
daily = daily_sentiment(tickers)
df = tally_scores(df).T
blank = []
columns = list(df)
path = "C:\\Users\\matth\\Desktop\\stock-sentiment-analyzer\\pie.xlsx"
sf = StyleFrame(daily.T)
col_list = list(daily.columns)
writer = pd.ExcelWriter(path, engine='xlsxwriter')
daily.T.to_excel(excel_writer=writer, sheet_name='Daily Sentiment Summary')
worksheet = writer.sheets['Daily Sentiment Summary']
worksheet.set_column('B:B', 15)
writer.save()
wb = load_workbook("pie.xlsx")
for i in range(0, len(df.columns)):
ws = wb.create_sheet(tickers[i])
score = 1
for index, row in df.iterrows():
vals = []
if index == 'tickers':
continue
vals.append(df.loc[index, i])
vals.insert(0, df.index[score])
score = score + 1
ws.append(vals)
pie = PieChart()
labels = Reference(ws, min_col=1, max_col=1, min_row=1, max_row=3)
data = Reference(ws, min_col=2, max_col=2, min_row=1, max_row=3)
pie.add_data(data)
pie.set_categories(labels)
pie.title = tickers[i] + ' Sentiment'
pie.layout = Layout(manualLayout=ManualLayout(
h=0.7,
w=0.7,
))
ws.add_chart(pie, "E3")
wb.save("pie.xlsx")
def make_chart_carrier(result_file="MainTestResults/MainResult.xlsx",
summary_file="CarrierTestResults"
"/CarrierTestResult.json"):
try:
wb = load_workbook(os.path.join(current_path, result_file))
except:
wb = Workbook()
if "Carrier Summary" in wb.sheetnames:
del wb["Carrier Summary"]
wb.create_sheet("Carrier Summary")
ws = wb["Carrier Summary"]
_ = ws.cell(row=1, column=1, value='Carrier Summary')
_.font = Font(bold=True)
# summary_file = "CarrierTestResults/CarrierTestResult.json"
with open(os.path.join(current_path, summary_file), 'r') as f:
res = json.load(f)
ips = [key for key in res.keys() if key != 'summary']
row = 1
pie_row_start = 4
for ip in ips:
data_to_add = [["made ip", ip]]
for key in res[ip].keys():
data_to_add.append([key, res[ip][key]])
for line in data_to_add:
ws.append(line)
pie_row_end = pie_row_start + 3
pie = PieChart()
print("pie row start:", pie_row_start)
print("pie row end:", pie_row_end)
labels = Reference(ws,
min_col=1,
min_row=pie_row_start,
max_row=pie_row_end)
data = Reference(ws,
min_col=2,
min_row=pie_row_start - 1,
max_row=pie_row_end)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = ip + " fail rate"
pie.dataLabels = DataLabelList()
pie.dataLabels.showPercent = True
ws.add_chart(pie, "D" + str(row))
pie_row_start += len(data_to_add)
row += 18
wb.save(os.path.join(current_path, result_file))
def create_pie_chart():
wb, ws, max_row = load_book(Main)
chart_worksheet = wb[Category_Data]
cs = wb.create_chartsheet("Category Chart")
r = load_chart_data(ws, "Category", chart_worksheet)
category_expenses = PieChart()
labels = Reference(chart_worksheet, min_col=2, min_row=3, max_row=r - 1)
data = Reference(chart_worksheet, min_col=3, min_row=2, max_row=r - 1)
category_expenses.add_data(data, titles_from_data=True)
category_expenses.set_categories(labels)
category_expenses.title = "Expenses by Category"
cs.add_chart(category_expenses)
wb.save(file_path)
def add_pie_chart(workbook, worksheet_level):
pie = PieChart()
cats = Reference(worksheet_level,
min_col=1,
min_row=2,
max_row=worksheet_level.max_row)
data = Reference(worksheet_level,
min_col=2,
min_row=1,
max_row=worksheet_level.max_row)
pie.add_data(data, titles_from_data=True)
pie.set_categories(cats)
pie.title = '问题评级'
data_points = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [data_points]
worksheet_level.add_chart(pie, "C9")
workbook.save(PATH + '/total.xlsx')
def B():
sheetB = wb.create_sheet('주요국가별 출원동향', 1)
B그래프data = Data.주요국출원동향()
for r in dataframe_to_rows(B그래프data, index=False, header=True):
sheetB.append(r)
sheetB.insert_cols(2)
for row, cellobj in enumerate(list(sheetB.columns)[1]):
n = '=right(A%d,2)' % (row + 1)
cellobj.value = n
sheetB['B22'] = '합계'
sheetB['C22'] = '=SUM(C2:C21)'
sheetB['D22'] = '=SUM(D2:D21)'
sheetB['E22'] = '=SUM(E2:E21)'
sheetB['F22'] = '=SUM(F2:F21)'
chartB1 = LineChart()
dataB1 = Reference(sheetB, min_col=3, min_row=1, max_row=21, max_col=6)
catsB1 = Reference(sheetB, min_col=2, min_row=2, max_row=21)
chartB1.add_data(dataB1, titles_from_data=True)
chartB1.set_categories(catsB1)
chartB1.y_axis.majorGridlines = None
chartB1.width = 15
chartB1.height = 10
chartB1.legend.position = 't'
chartB1.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
sheetB.add_chart(chartB1, 'H2')
chartB2 = PieChart()
dataB2 = Reference(sheetB, min_col=3, min_row=22, max_col=6)
labelsB2 = Reference(sheetB, min_col=3, min_row=1, max_col=6)
chartB2.add_data(dataB2, from_rows=22, titles_from_data=False)
chartB2.set_categories(labelsB2)
chartB2.width = 5
chartB2.height = 5
chartB2.legend = None
chartB2.graphical_properties = GraphicalProperties(
ln=LineProperties(noFill=True, solidFill=None, gradFill=None))
chartB2.dLbls = DataLabelList()
chartB2.dLbls.showPercent = True
sheetB.add_chart(chartB2, 'M1')
def plot_pie_chart():
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 = PieChart()
chart.append(series_object)
sheet.add_chart(chart, 'j2')
wb.save('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx')
def monthChartBreakdown():
'''Makes pie chart for each month'''
curMonth = ['', 'January', 'February', 'March', 'April', 'May', 'June',
'July', 'August', 'September', 'October', 'November', 'December']
for i in range(1, mNum+1):
pie = PieChart()
length_m = len(categories)
monthData = Reference(Sum, min_col = i+9, min_row = 2, max_row = length_m-1)
labels = Reference(Sum, min_col=8, min_row=2, max_row = length_m-1)
pie.add_data(monthData)
pie.set_categories(labels)
pie.title = curMonth[i]+ ' Expenses by Category'
pie.width = 18.0
pie.height = 12.0
pie.legend.layout = Layout(manualLayout=ManualLayout(x=0.25, y=0.25, h=0.99, w=0.25))
wb.get_sheet_by_name(TM[i]).add_chart(pie, 'G3')
def export_pie_chart(self, data, title):
wb = Workbook()
ws = wb.active
for row in [('', '')] + data:
ws.append(row)
pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=len(data) + 1)
items = Reference(ws, min_col=2, min_row=1, max_row=len(data) + 1)
pie.add_data(items, titles_from_data=True)
pie.set_categories(labels)
pie.title = title
ws.add_chart(pie, 'A{0}'.format(len(data) + 3))
output = StringIO.StringIO()
wb.save(filename=output)
output.flush()
return output.getvalue()
def generate_picks_chart(workbook):
print("Generating picks chart")
chart = PieChart()
chart.title = "Picks"
datasheet = workbook['Summary']
data = Reference(datasheet, min_col=4, max_col=4, min_row=13, max_row=17)
titles = Reference(datasheet, min_col=1, min_row=14, max_row=17)
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
chart.dataLabels = DataLabelList()
chart.dataLabels.showPercent = True
chart.dataLabels.showVal = True
chart.dataLabels.showCatName = False
chart.dataLabels.showSerName = False
chart.dataLabels.showLegendKey = False
return chart
def make_pie_chart(self):
sheetnames = self.__wb.get_sheet_names()
# ws = self.__wb.get_sheet_by_name(sheetnames[0])
ws_new = self.__wb.create_sheet("pie chart")
# 最后一行
max_r = self.__ws.max_row
pie = PieChart()
labels = Reference(self.__ws, min_col=1, min_row=max_r - 2, max_row=max_r - 1)
data = Reference(self.__ws, min_col=2, min_row=max_r - 3, max_row=max_r - 1)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Test Result Pie Chart"
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
ws_new.add_chart(pie, "A1")
self.__wb.save(self.__filename)
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
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
print(type(corrected_price))
value = Reference(sheet, min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = PieChart()
chart.add_data(value)
sheet.add_chart(chart, 'a2')
wb.save(filename)
wb.close()
def write_text_xlsx_file(used_list):
file1 = open("WordAnalysis.txt", 'w')
word_string = list()
value_string = list()
excel_file = Workbook()
sheet = excel_file.active
end_word = "A" + str(length)
end_data = "B" + str(length)
cell_range_word = sheet["A2": end_word]
cell_range_data = sheet["B2": end_data]
cell_range = sheet["A1": end_data] # 엑셀 범위 지정
sheet["A1"].value = "단어"
sheet["B1"].value = "횟수"
sheet['C1'].value = "많이 쓰인 단어"
sheet['D1'].value = "횟수"
marker = 2 # 셀번호
for word in used_list:
word_string.append(str(word))
sheet["A" + str(marker)] = str(word)
value_string.append(my_list.count(word))
sheet["B"+str(marker)] = my_list.count(word)
marker += 1
file1.write(str(word) + " : " + str(my_list.count(word)) + "\n") # 엑셀, 텍스트파일에 자료 이동
row_num = 0
for row in cell_range:
if row[1].value > (length / 200):
row_num += 1
sheet.cell(row = row_num, column = 4, value = row[1].value)
sheet.cell(row = row_num, column = 3, value = row[0].value) # 파이 차트에서 쓸 값 정리
pie = PieChart()
labels = Reference(sheet, min_col = 3, min_row = 2, max_row = marker - 1)
data = Reference(sheet, min_col = 4, min_row = 1, max_row = marker - 1)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Significant words in article"
sheet.add_chart(pie, "M1")
excel_file.save("Word_AnalyticsFinal.xlsx")
def results_to_excel(position, techs, posts_seen, total_found):
wb = Workbook()
sheet = wb.active
date = datetime.datetime.now()
# initial format of the sheet
sheet.cell(1, 1).value = 'Total posts viewed:'
sheet.cell(1, 2).value = posts_seen
sheet.cell(1, 4).value = 'Date:'
sheet.cell(1, 5).value = date
sheet.cell(3, 1).value = 'Tech/Lang'
sheet.cell(3, 2).value = 'Number of appearances'
sheet.cell(3, 3).value = 'Ocurrence percentage (*)'
sheet['F19'] = "(*) respect to the total number of posts viewed"
sheet.cell(1, 1).font = Font(name='Arial', bold=True, size=13)
sheet.cell(1, 4).font = Font(name='Arial', bold=True, size=13)
sheet.cell(3, 1).font = Font(name='Arial', bold=True, size=13)
sheet.cell(3, 2).font = Font(name='Arial', bold=True, size=13)
sheet.cell(3, 3).font = Font(name='Arial', bold=True, size=13)
sheet['F19'].font = Font(name='Arial', size=13)
# dump the techs dictionary the sheet
row = 4
for key, value in techs.items():
sheet.cell(row, 1).value = key
sheet.cell(row, 2).value = value
sheet.cell(row, 3).value = f'{"{:.2f}".format(value*100/posts_seen)}%'
sheet.cell(row, 3).alignment = Alignment(horizontal='right')
row += 1
# generates the chart of the results
pie = PieChart()
labels = Reference(sheet, min_col=1, min_row=4, max_row=row - 1)
data = Reference(sheet, min_col=2, min_row=4, max_row=row - 1)
pie.add_data(data)
pie.set_categories(labels)
pie.title = f'Number of times a technology/language was found from {total_found} techs found'
sheet.add_chart(pie, 'F3')
wb.save(f'{position} {date}.xlsx')
