def insertTotalMoneyCPChart(sheet_obj, num_of_exp, symbol, finalOptionExpDate,
final_chart_col_loc, final_chart_insert_pos,
getNumberColsData):
if (Commonapi.debug == 1):
print("insertTotalMoneyCPChart started")
for i in range(0, (int)(num_of_exp)):
min_cols = (int)(final_chart_col_loc[i])
max_cols = min_cols + 1
m_rows = min_cols + getNumberColsData
print("m_rows min_cols is :", m_rows, min_cols)
chart = BarChart()
chart.type = "col"
chart.grouping = "stacked"
chart.overlap = 100
chart.title = symbol + '_' + finalOptionExpDate[i]
chart.y_axis.title = 'TOTAL_CALL_PUT_TM x K'
chart.x_axis.title = 'Dates'
data = Reference(sheet_obj,
min_col=min_cols,
min_row=1,
max_row=m_rows,
max_col=max_cols)
cats = Reference(sheet_obj, min_col=1, min_row=2, max_row=m_rows)
chart.add_data(data, titles_from_data=True)
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
chart.set_categories(cats)
sheet_obj.add_chart(chart, final_chart_insert_pos[i])
title = symbol + '_' + finalOptionExpDate[i]
print("Bar chart :", title)
if (Commonapi.debug == 1):
print("insertTotalMoneyCPChart ended")
def implementChart(listOfDate, Errordate, save_excel_file_today):
wb_obj = openpyxl.load_workbook(save_excel_file_today)
wb_obj.create_sheet('Chart view')
sheet_obj = wb_obj["Chart view"]
Drows = 1
Erows = 1
cell_obj = sheet_obj.cell(row=1, column=1)
cell_obj.value = "listOfDate"
cell_obj = sheet_obj.cell(row=1, column=2)
cell_obj.value = "Error_data"
for i in listOfDate:
cell_obj = sheet_obj.cell(row=Drows + 1, column=1)
cell_obj.value = i
Drows += 1
for i in Errordate:
cell_obj = sheet_obj.cell(row=Erows + 1, column=2)
cell_obj.value = i
Erows += 1
dates = Reference(sheet_obj, min_col=1, min_row=2, max_col=1, max_row=16)
values = Reference(sheet_obj, min_col=2, min_row=2, max_col=2, max_row=16)
chart = BarChart()
chart.add_data(values)
chart.set_categories(dates)
chart.height = 15 # default is 7.5
chart.width = 30
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
chart.title = " 15 Day's Error Count "
chart.x_axis.title = " Dates "
chart.y_axis.title = " Number of Error "
sheet_obj.add_chart(chart, "A1")
wb_obj.save(save_excel_file_today)
def insertCPIOMOIchartToCPMISheet(sheet_obj, symbol, finalOptionExpDate,
num_of_exp, final_OTMOI_chart_insert_pos,
iotmio_data_col_loc, getNumberColsData,
iotmio_row_loc):
if (Commonapi.debug == 1):
print("insertCPIOMOIchartToCPMISheet started")
for i in range(0, num_of_exp):
min_rows = (int)(iotmio_row_loc[1])
min_cols = (int)(iotmio_data_col_loc[i])
max_cols = min_cols + 3
max_rows = min_rows + getNumberColsData
chart = BarChart()
chart.type = "col"
chart.grouping = "stacked"
chart.overlap = 100
chart.title = symbol + '_' + finalOptionExpDate[i]
chart.y_axis.title = final_OTM_chart_y_axis[0]
chart.x_axis.title = 'Dates'
data = Reference(sheet_obj,
min_col=min_cols,
min_row=min_rows,
max_row=max_rows,
max_col=max_cols)
cats = Reference(sheet_obj,
min_col=1,
min_row=min_rows + 1,
max_row=max_rows)
chart.add_data(data, titles_from_data=True)
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
chart.set_categories(cats)
sheet_obj.add_chart(chart, final_OTMOI_chart_insert_pos[i])
title = symbol + '_' + finalOptionExpDate[i]
print("Bar chart :", title)
if (Commonapi.debug == 1):
print("insertCPIOMOIchartToCPMISheet ended")
datasheet_3.cell(row=_row, column=_col).border = border_s
for _row in range(2, 34):
datasheet_3.cell(row=_row, column=6).number_format = number_format_s
# 生成表2图表1
chart2_1 = BarChart()
chart2_1.style = 3
chart2_1.width = 30
chart2_1.title = "周开通量统计"
chart2_1.y_axis.title = '周开通量（台）'
data = Reference(datasheet_2, min_col=6, min_row=1, max_row=32)
cats = Reference(datasheet_2, min_col=2, min_row=2, max_row=32)
chart2_1.add_data(data, titles_from_data=True)
chart2_1.set_categories(cats)
datasheet_2.add_chart(chart2_1, "A35")
chart2_1.dataLabels = DataLabelList()
chart2_1.dataLabels.showVal = True
# 生成表2图表2
chart2_2 = BarChart()
chart2_2.style = 3
chart2_2.width = 30
chart2_2.title = "当年累计开通量统计"
chart2_2.y_axis.title = '当年累计开通量（台）'
data = Reference(datasheet_2, min_col=4, min_row=1, max_row=32)
cats = Reference(datasheet_2, min_col=2, min_row=2, max_row=32)
chart2_2.add_data(data, titles_from_data=True)
chart2_2.set_categories(cats)
datasheet_2.add_chart(chart2_2, "A52")
chart2_2.dataLabels = DataLabelList()
chart2_2.dataLabels.showVal = True
# 生成表3图表1
def transcribe_client_data_to_workbooks(client):
report_path = r'C:\Program Files\Notepad++\reports' + '\\' + string.replace(
client, '/', '-') + '_report.csv'
wb = openpyxl.load_workbook(
'C:\Program Files\Notepad++\Bucket-Asset Allocation Model.xlsm',
read_only=False,
keep_vba=True)
wb.active = 5
ws = wb.active
with open(report_path, 'rU') as f:
reader = csv.reader(f)
for row_index, row in enumerate(reader):
for column_index, cell in enumerate(row):
column_letter = get_column_letter((column_index + 1))
if column_letter == 'A':
if row_index > 0:
ws[column_letter + str(row_index + 1)] = long(
string.replace(string.replace(cell, 'Z', '9'), 'X',
'0'))
else:
ws[column_letter + str(row_index + 1)] = cell
#Proposed allocation pie chart creation
#Close the file
f.close()
#Proposed Allocation pie chart creation
wb.active = 1
ws = wb.active
pie1 = PieChart()
labels1 = Reference(ws, min_col=14, min_row=19, max_row=21)
data1 = Reference(ws, min_col=15, min_row=18, max_row=21)
pie1.add_data(data1, titles_from_data=True)
pie1.set_categories(labels1)
pie1.title = 'Proposed Allocation'
pie1.height = 14
pie1.width = 18
ws.add_chart(pie1, "F20")
pie1.dataLabels = DataLabelList()
pie1.dataLabels.showPercent = True
#Current allocation pie chart creation
pie2 = PieChart()
labels2 = Reference(ws, min_col=14, min_row=29, max_row=31)
data2 = Reference(ws, min_col=15, min_row=28, max_row=31)
pie2.add_data(data2, titles_from_data=True)
pie2.set_categories(labels2)
pie2.title = 'Current Allocation'
pie2.height = 14
pie2.width = 18
ws.add_chart(pie2, 'B20')
pie2.dataLabels = DataLabelList()
pie2.dataLabels.showPercent = True
#Bar chart creation
#Change to allocations worksheet
wb.active = 3
ws = wb.active
#Short term bucket bar graph creation
chart1 = BarChart()
chart1.type = "col"
chart1.style = 12
chart1.title = "Bucket I \n Short - Term"
data = Reference(ws, min_col=14, min_row=61, max_row=62, max_col=15)
cats = Reference(ws, min_col=13, min_row=62)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "B25")
chart1.dataLabels = DataLabelList()
chart1.dataLabels.showVal = True
#Intermediate term bucket bar graph creation
chart2 = BarChart()
chart2.type = "col"
chart2.style = 10
chart2.title = "Bucket II \n Intermediate - Term"
data2 = Reference(ws, min_col=14, min_row=63, max_row=64, max_col=15)
cats2 = Reference(ws, min_col=13, min_row=64)
chart2.add_data(data2, titles_from_data=True)
chart2.set_categories(cats2)
chart2.shape = 4
ws.add_chart(chart2, "I25")
chart2.dataLabels = DataLabelList()
chart2.dataLabels.showVal = True
#Long term bucket bar graph creation
chart3 = BarChart()
chart3.type = "col"
chart3.style = 13
chart3.title = "Bucket III \n Long - Term"
data3 = Reference(ws, min_col=14, min_row=65, max_row=66, max_col=15)
cats3 = Reference(ws, min_col=13, min_row=66)
chart3.add_data(data3, titles_from_data=True)
chart3.set_categories(cats3)
chart3.shape = 4
ws.add_chart(chart3, "P25")
chart3.dataLabels = DataLabelList()
chart3.dataLabels.showVal = True
#0 portfolio bar graph creation
chart4 = BarChart()
chart4.type = "col"
chart4.style = 10
chart4.height = 10
chart4.width = 20
chart4.title = "0 Portfolio"
data4 = Reference(ws, min_col=14, min_row=57, max_row=58, max_col=15)
cats4 = Reference(ws, min_col=14, min_row=57, max_col=15)
chart4.add_data(data4, titles_from_data=True)
chart4.set_categories(cats4)
chart4.shape = 4
ws.add_chart(chart4, "B3")
chart4.dataLabels = DataLabelList()
chart4.dataLabels.showVal = True
#Allocation Comparison Bar graph creation
chart5 = BarChart()
chart5.type = "col"
chart5.style = 10
chart5.title = "Allocation Comparison"
chart5.height = 10
chart5.width = 20
data5 = Reference(ws, min_col=10, min_row=62, max_row=64, max_col=12)
cats5 = Reference(ws, min_col=9, min_row=63, max_row=64)
chart5.add_data(data5, titles_from_data=True)
chart5.set_categories(cats5)
chart5.shape = 4
ws.add_chart(chart5, "M3")
chart5.dataLabels = DataLabelList()
chart5.dataLabels.showVal = True
print(len(set(cdict.values())))
#Attempt to save, move on if the file is open for reading.
try:
wb.save('C:\\Program Files\\Notepad++\\workbooks\\' +
string.replace(client, '/', '-') + '_workbook.xlsm')
print(string.replace(client, '/', '-') + ' workbook completed')
except IOError:
print("Workbook already open, cannot overwrite, moving on.")
def generate_charts(filename):
wb = openpyxl.load_workbook(filename)
mysheet = wb.get_active_sheet()
# 找到最大行
for i in range(3, 30):
if mysheet['B' + str(i)].value == None:
max_row = i - 1
break
#
ft = Font(bold=True)
mysheet['B' + str(max_row + 1)].value = 'total'
mysheet['B' + str(max_row + 1)].font = ft
k = 54
t = 61
for x in 'CDEFGHIJKLMN':
x_max = x + str(max_row)
x_cell = x + str(max_row + 1)
mysheet[x_cell].value = '=SUM(' + x + str(4) + ':' + x_max + ')'
if x > 'D' and x < 'J':
mysheet['C' + str(k)].value = mysheet[x_cell].value
k += 1
if x >= 'J' and x <= 'N':
mysheet['C' + str(t)].value = mysheet[x_cell].value
t += 1
mysheet[x_cell].font = ft
chart = BarChart()
chart.style = 11
chart.type = "bar"
chart.title = "问题燃尽情况"
# 系列重叠
chart.overlap = 100
# 添加数据标签
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
data = Reference(mysheet, min_col=3, min_row=3, max_row=max_row, max_col=4)
cats = Reference(mysheet, min_col=2, min_row=4, max_row=max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4
mysheet.add_chart(chart, 'B' + str(max_row + 3))
pie1 = PieChart()
labels = Reference(mysheet, min_col=2, min_row=54, max_row=58)
data = Reference(mysheet, min_col=3, min_row=53, max_row=58)
pie1.add_data(data, titles_from_data=True)
pie1.set_categories(labels)
pie1.title = "问题分类占比"
pie1.dataLabels = DataLabelList()
pie1.dataLabels.showVal = True
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie1.series[0].data_points = [slice]
mysheet.add_chart(pie1, 'I' + str(max_row + 3))
pie2 = PieChart()
labels = Reference(mysheet, min_col=2, min_row=61, max_row=65)
data = Reference(mysheet, min_col=3, min_row=60, max_row=65)
pie2.add_data(data, titles_from_data=True)
pie2.set_categories(labels)
pie2.title = "严重级别占比"
pie2.dataLabels = DataLabelList()
pie2.dataLabels.showVal = True
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie2.series[0].data_points = [slice]
mysheet.add_chart(pie2, 'B' + str(max_row + 19))
wb.save(filename)
def createTwoAxisChart(wb,
readType,
rows,
titles,
lineNumber,
place="C10",
max_row=-1,
max_col=-1,
min_row=1,
min_col=1,
isDesc=True,
isMark=True,
isLineWidth=True,
isDataLable=True):
ws = excel_util.creatSheet(wb, False, titles[0])
excel_util.putRowsToSheet(rows, ws)
if max_row == -1:
max_row = len(rows)
if max_col == -1:
max_col = len(rows[0])
c1 = LineChart()
c2 = BarChart()
if readType == "row":
addChartDataBtType(ws, readType, c2, max_row, max_col, lineNumber,
min_col)
addChartDataBtType(ws, readType, c1, lineNumber, max_col, min_row,
min_col)
elif readType == "col":
addChartDataBtType(ws, readType, c2, max_row, max_col, min_row,
lineNumber)
addChartDataBtType(ws, readType, c1, max_row, lineNumber, min_row,
min_col)
c1.y_axis.axId = 200
c1.y_axis.crosses = "max"
c1 += c2
c2.y_axis.title = titles[3]
c2.y_axis.title.tx.rich.p[0].r.rPr = cpAxisTitle
c2.y_axis.txPr = RichText(p=[
Paragraph(pPr=ParagraphProperties(defRPr=cpAxisText),
endParaRPr=cpAxisText)
])
c2.dataLabels = DataLabelList()
c2.dataLabels.txPr = RichText(p=[
Paragraph(pPr=ParagraphProperties(defRPr=cpDataLabel),
endParaRPr=cpDataLabel)
])
c2.dataLabels.showVal = True
setChartTitleAndSize(c1, titles)
setChartByParameter(c1, isDesc, isMark, isLineWidth, isDataLable)
# 逆序
if isDesc:
c1.y_axis.scaling.orientation = "maxMin"
c1.x_axis.crosses = "min"
ws.add_chart(c1, place)
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])
