class TestBarChartWriter(object):
""""""
def setup(self):
"""Setup a worksheet with one column of data and a bar chart"""
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'Numbers'
for i in range(10):
ws.append([i])
self.piechart = BarChart()
self.piechart.add_serie(Serie(Reference(ws, (0, 0), (9, 0))))
self.cw = BarChartWriter(self.piechart)
self.root = Element('test')
def test_write_chart(self):
"""check if some characteristic tags of LineChart are there"""
self.cw._write_chart(self.root)
tagnames = ['{%s}barChart' % CHART_NS,
'{%s}valAx' % CHART_NS,
'{%s}catAx' % CHART_NS]
root = safe_iterator(self.root)
chart_tags = [e.tag for e in root]
for tag in tagnames:
assert_true(tag in chart_tags, tag)
def test_serialised(self):
"""Check the serialised file against sample"""
xml = self.cw.write()
expected_file = os.path.join(DATADIR, "writer", "expected", "BarChart.xml")
with open(expected_file) as expected:
diff = compare_xml(xml, expected.read())
assert diff is None, diff
def test_write_no_ascii(self):
ws = self.make_worksheet()
ws.append(["D\xc3\xbcsseldorf"] * 10)
serie = Serie(values=Reference(ws, (0, 0), (0, 9)), legend=Reference(ws, (1, 0), (1, 9)))
c = BarChart()
c.add_serie(serie)
cw = ChartWriter(c)
def negative(wb):
ws = wb.create_sheet(1, "Negative")
for i in range(-5, 5):
ws.append([i])
chart = BarChart()
values = Reference(ws, (0, 0), (9, 0))
series = Series(values)
chart.append(series)
ws.add_chart(chart)
def numbers(wb):
ws = wb.create_sheet(1, "Numbers")
for i in range(10):
ws.append([i])
chart = BarChart()
values = Reference(ws, (0, 0), (9, 0))
series = Series(values)
chart.append(series)
ws.add_chart(chart)
def dates(wb):
ws = wb.create_sheet(3, "Dates")
for i in range(1, 10):
ws.append([date(2013, i, 1), i])
chart = BarChart()
values = Reference(ws, (0, 1), (8, 1))
labels = Reference(ws, (0, 0), (8, 0))
labels.number_format = 'd-mmm'
series = Series(values, labels=labels)
chart.append(series)
ws.add_chart(chart)
def letters(wb):
ws = wb.create_sheet(2, "Letters")
for idx, l in enumerate("ABCDEFGHIJ"):
ws.append([l, idx, idx])
chart = BarChart()
labels = Reference(ws, (0, 0), (9, 0))
values = Reference(ws, (0, 1), (9, 1))
series = Series(values, labels=labels)
chart.append(series)
# add second series
values = Reference(ws, (0, 2), (9, 2))
series = Series(values, labels=labels)
chart.append(series)
ws.add_chart(chart)
def draw_chart(self, wsheet, series, left=0, title=""):
chart = BarChart()
chart.title = title
chart.drawing.left = 500 + left
chart.drawing.top = 250 + left
chart.drawing.height = 200
chart.drawing.width = 500
i = 0
for serie in series:
if len(serie) == 4:
x1 = int(serie[0])
y1 = int(serie[1])
x2 = int(serie[2])
y2 = int(serie[3])
if x2 > 3:
if i == 0:
legend = Reference(wsheet, (0, 0))
labels = Reference(wsheet, (x1, 0), (x2, 0))
else:
if y1 < 13:
legend = Reference(wsheet, (0, 4))
if y1 >= 13:
legend = Reference(wsheet, (0, 13))
#value = wsheet.cell(row=4,column=0).value
title = wsheet.title
if title.find("Rate") < 0:
isLabel = True
else:
if len(title) > 12:
isLabel = True
else:
isLabel = False
if i == 0 and isLabel: # type(value).__name__ <> 'int':
seri = Serie(
Reference(wsheet, (x1, y1), (x2, y2)), labels=labels, legend=legend)
else:
seri = Serie(
Reference(wsheet, (x1, y1), (x2, y2)), legend=legend)
chart.add_serie(seri)
i += 1
wsheet.add_chart(chart)
class TestChartWriter(object):
def setUp(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = u'data'
for i in range(10):
ws.cell(row = i, column = 0).value = i
self.chart = BarChart()
self.chart.title = 'TITLE'
self.chart.add_serie(Serie(Reference(ws, (0, 0), (10, 0))))
self.chart._series[-1].color = Color.GREEN
self.cw = ChartWriter(self.chart)
self.root = Element('test')
def test_write_title(self):
self.cw._write_title(self.root)
eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:title><c:tx><c:rich><a:bodyPr /><a:lstStyle /><a:p><a:pPr><a:defRPr /></a:pPr><a:r><a:rPr lang="fr-FR" /><a:t>TITLE</a:t></a:r></a:p></c:rich></c:tx><c:layout /></c:title></test>')
def test_write_xaxis(self):
self.cw._write_axis(self.root, self.chart.x_axis, 'c:catAx')
eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:catAx><c:axId val="60871424" /><c:scaling><c:orientation val="minMax" /></c:scaling><c:axPos val="b" /><c:tickLblPos val="nextTo" /><c:crossAx val="60873344" /><c:crosses val="autoZero" /><c:auto val="1" /><c:lblAlgn val="ctr" /><c:lblOffset val="100" /></c:catAx></test>')
def test_write_yaxis(self):
self.cw._write_axis(self.root, self.chart.y_axis, 'c:valAx')
eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:valAx><c:axId val="60873344" /><c:scaling><c:orientation val="minMax" /><c:max val="10.0" /><c:min val="0" /></c:scaling><c:axPos val="l" /><c:majorGridlines /><c:numFmt formatCode="General" sourceLinked="1" /><c:tickLblPos val="nextTo" /><c:crossAx val="60871424" /><c:crosses val="autoZero" /><c:crossBetween val="between" /><c:majorUnit val="2.0" /></c:valAx></test>')
def test_write_series(self):
self.cw._write_series(self.root)
eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:ser><c:idx val="0" /><c:order val="0" /><c:spPr><a:solidFill><a:srgbClr val="00FF00" /></a:solidFill><a:ln><a:solidFill><a:srgbClr val="00FF00" /></a:solidFill></a:ln></c:spPr><c:marker><c:symbol val="none" /></c:marker><c:val><c:numRef><c:f>data!$A$1:$A$11</c:f><c:numCache><c:formatCode>General</c:formatCode><c:ptCount val="11" /><c:pt idx="0"><c:v>0</c:v></c:pt><c:pt idx="1"><c:v>1</c:v></c:pt><c:pt idx="2"><c:v>2</c:v></c:pt><c:pt idx="3"><c:v>3</c:v></c:pt><c:pt idx="4"><c:v>4</c:v></c:pt><c:pt idx="5"><c:v>5</c:v></c:pt><c:pt idx="6"><c:v>6</c:v></c:pt><c:pt idx="7"><c:v>7</c:v></c:pt><c:pt idx="8"><c:v>8</c:v></c:pt><c:pt idx="9"><c:v>9</c:v></c:pt><c:pt idx="10"><c:v>None</c:v></c:pt></c:numCache></c:numRef></c:val></c:ser></test>')
def test_write_legend(self):
self.cw._write_legend(self.root)
eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:legend><c:legendPos val="r" /><c:layout /></c:legend></test>')
def test_write_print_settings(self):
self.cw._write_print_settings(self.root)
eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:printSettings><c:headerFooter /><c:pageMargins b="0.75" footer="0.3" header="0.3" l="0.7" r="0.7" t="0.75" /><c:pageSetup /></c:printSettings></test>')
def test_write_chart(self):
self.cw._write_chart(self.root)
eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:chart><c:title><c:tx><c:rich><a:bodyPr /><a:lstStyle /><a:p><a:pPr><a:defRPr /></a:pPr><a:r><a:rPr lang="fr-FR" /><a:t>TITLE</a:t></a:r></a:p></c:rich></c:tx><c:layout /></c:title><c:plotArea><c:layout><c:manualLayout><c:layoutTarget val="inner" /><c:xMode val="edge" /><c:yMode val="edge" /><c:x val="1.28571428571" /><c:y val="0.2125" /><c:w val="0.6" /><c:h val="0.6" /></c:manualLayout></c:layout><c:barChart><c:barDir val="col" /><c:grouping val="clustered" /><c:ser><c:idx val="0" /><c:order val="0" /><c:spPr><a:solidFill><a:srgbClr val="00FF00" /></a:solidFill><a:ln><a:solidFill><a:srgbClr val="00FF00" /></a:solidFill></a:ln></c:spPr><c:marker><c:symbol val="none" /></c:marker><c:val><c:numRef><c:f>data!$A$1:$A$11</c:f><c:numCache><c:formatCode>General</c:formatCode><c:ptCount val="11" /><c:pt idx="0"><c:v>0</c:v></c:pt><c:pt idx="1"><c:v>1</c:v></c:pt><c:pt idx="2"><c:v>2</c:v></c:pt><c:pt idx="3"><c:v>3</c:v></c:pt><c:pt idx="4"><c:v>4</c:v></c:pt><c:pt idx="5"><c:v>5</c:v></c:pt><c:pt idx="6"><c:v>6</c:v></c:pt><c:pt idx="7"><c:v>7</c:v></c:pt><c:pt idx="8"><c:v>8</c:v></c:pt><c:pt idx="9"><c:v>9</c:v></c:pt><c:pt idx="10"><c:v>None</c:v></c:pt></c:numCache></c:numRef></c:val></c:ser><c:marker val="1" /><c:axId val="60871424" /><c:axId val="60873344" /></c:barChart><c:catAx><c:axId val="60871424" /><c:scaling><c:orientation val="minMax" /></c:scaling><c:axPos val="b" /><c:tickLblPos val="nextTo" /><c:crossAx val="60873344" /><c:crosses val="autoZero" /><c:auto val="1" /><c:lblAlgn val="ctr" /><c:lblOffset val="100" /></c:catAx><c:valAx><c:axId val="60873344" /><c:scaling><c:orientation val="minMax" /><c:max val="10.0" /><c:min val="0" /></c:scaling><c:axPos val="l" /><c:majorGridlines /><c:numFmt formatCode="General" sourceLinked="1" /><c:tickLblPos val="nextTo" /><c:crossAx val="60871424" /><c:crosses val="autoZero" /><c:crossBetween val="between" /><c:majorUnit val="2.0" /></c:valAx></c:plotArea><c:legend><c:legendPos val="r" /><c:layout /></c:legend><c:plotVisOnly val="1" /></c:chart></test>')
def test_label_no_number_format(self):
ws = self.make_worksheet()
for i in range(10):
ws.append([i, i])
labels = Reference(ws, (0,0), (0,9))
values = Reference(ws, (0,0), (0,9))
serie = Serie(values=values, labels=labels)
c = BarChart()
c.add_serie(serie)
cw = BarChartWriter(c)
root = Element('test')
cw._write_serial(root, c._series[0].labels)
expected = """<?xml version='1.0' ?><test xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:numRef><c:f>'data'!$A$1:$J$1</c:f><c:numCache><c:formatCode>General</c:formatCode><c:ptCount val="10" /><c:pt idx="0"><c:v>0</c:v></c:pt><c:pt idx="1"><c:v>1</c:v></c:pt><c:pt idx="2"><c:v>2</c:v></c:pt><c:pt idx="3"><c:v>3</c:v></c:pt><c:pt idx="4"><c:v>4</c:v></c:pt><c:pt idx="5"><c:v>5</c:v></c:pt><c:pt idx="6"><c:v>6</c:v></c:pt><c:pt idx="7"><c:v>7</c:v></c:pt><c:pt idx="8"><c:v>8</c:v></c:pt><c:pt idx="9"><c:v>9</c:v></c:pt></c:numCache></c:numRef></test>"""
xml = get_xml(root)
diff = compare_xml(xml, expected)
assert diff is None, diff
def build_graph_in_excel(self, ws, table, start_row_in_excel, start_column_in_excel):
columns_list_from_db = self.get_columns_list_without_id(table)[1:-1].split(', ')
count_rows_in_table_db = self.get_count_rows(table)+1
start_column_number = self.get_number_for_letter(start_column_in_excel) + 1
chart = BarChart()
chart.title = '{}'.format(table)
chart.style = 10
#chart.x_axis.title = 'TEst'
chart.y_axis.title = 'Percentage'
cats = Reference(ws,
min_col=start_column_number,
min_row=start_row_in_excel + 1,
max_row='{}'.format(count_rows_in_table_db)
)
data1 = Reference(ws,
min_col=10,
min_row=1,
max_row='{}'.format(count_rows_in_table_db)
)
data2 = Reference(ws, min_col=14, min_row=1, max_row='{}'.format(count_rows_in_table_db))
chart.add_data(data1, titles_from_data=True)
chart.add_data(data2, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, 'A15')
def chart_openpyxl(worksheet):
global TOTAL_DAYS
print 'generate Chart...'
# create open-high-low-close chart
stock = StockChart()
dates = Reference(worksheet, min_col=1, min_row=31, max_row=TOTAL_DAYS+1)
data = Reference(worksheet, min_col=2, max_col=5, min_row=31, max_row=TOTAL_DAYS+1)
stock.add_data(data, titles_from_data= True)
stock.set_categories(dates)
for s in stock.series:
s.graphicalProperties.line.noFill = True
stock.hiLowLines = ChartLines()
stock.upDownBars = UpDownBars()
# Excel is broken and needs a cache of values in order to display hiLoLines :-/
from openpyxl.chart.data_source import NumData, NumVal
pts = [NumVal(idx=i) for i in range(len(data) - 1)]
cache = NumData(pt=pts)
# add dummy cache
stock.series[-1].val.numRef.numCache = cache
# create 5-30MA chart
bar = BarChart()
data = Reference(worksheet, min_col=9, min_row=31, max_row=TOTAL_DAYS+1)
bar.add_data(data, titles_from_data=False)
bar.set_categories(dates)
# merge K-Line & 5-30MA chart
stock_tmp = deepcopy(bar)
bar_tmp = deepcopy(stock)
stock_tmp.title = "TWSE Mometum Chart"
stock_tmp.height = 15
stock_tmp.width = 25
stock_tmp.y_axis.axId = 20
stock_tmp.z_axis = bar_tmp.y_axis
stock_tmp.y_axis.crosses = "max"
stock_tmp.legend = None # hidden series label name
bar_tmp.y_axis.majorGridlines = None
bar_tmp.y_axis.title = "Price"
stock_tmp += bar_tmp
worksheet.add_chart(stock_tmp, "A{}".format(TOTAL_DAYS+2))
def setup(self):
"""Setup a worksheet with one column of data and a bar chart"""
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'Numbers'
for i in range(10):
ws.append([i])
self.piechart = BarChart()
self.piechart.add_serie(Serie(Reference(ws, (0, 0), (9, 0))))
self.cw = BarChartWriter(self.piechart)
self.root = Element('test')
def setUp(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = u'data'
for i in range(10):
ws.cell(row = i, column = 0).value = i
self.chart = BarChart()
self.chart.title = 'TITLE'
self.chart.add_serie(Serie(Reference(ws, (0, 0), (10, 0))))
self.chart._series[-1].color = Color.GREEN
self.cw = ChartWriter(self.chart)
self.root = Element('test')
def add_charts(sheet, mark, model, data, cats):
"""draw charts with median and average mileages for each year"""
chart = BarChart()
chart.title = mark + " " + model
chart.type = "col"
chart.y_axis.title = 'Mileage'
chart.x_axis.title = 'Year of prod.'
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
sheet.add_chart(chart, "E3")
def excel_mtf_barchart(ws):
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "MTF Chart"
chart1.y_axis.title = 'MTF'
chart1.x_axis.title = 'ROI'
# Select all data include title
data = Reference(ws, min_col=2, min_row=1, max_row=19, max_col=2)
# Select data only
cats = Reference(ws, min_col=1, min_row=2, max_row=18)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
chart1.x_axis.scaling.min = 0
chart1.x_axis.scaling.max = 18
chart1.y_axis.scaling.min = 0
chart1.y_axis.scaling.max = 1
ws.add_chart(chart1, "G1")
def excel_sfr_barchart(ws):
chart1 = BarChart()
chart1.type = "col"
chart1.style = 12
chart1.title = "SFR Chart"
chart1.y_axis.title = 'SFR'
chart1.x_axis.title = 'ROI'
# Select all data include title
data = Reference(ws, min_col=5, min_row=1, max_row=37, max_col=5)
# Select data only
cats = Reference(ws, min_col=4, min_row=2, max_row=37)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
chart1.x_axis.scaling.min = 0
chart1.x_axis.scaling.max = 37
chart1.y_axis.scaling.min = 0
chart1.y_axis.scaling.max = 1
ws.add_chart(chart1, "G21")
def save_excel_data():
count = 0
wb=openpyxl.Workbook()
ws1=wb.active
ws1=wb.create_sheet("mysheet2")
ws1.append(['이름','국어','영어'])
for n in myDataList:
count += 1
ws1.append([n['name'],n['korean'],n['english']])
#Chart 1
chart1 = BarChart()
chart1.style=11
chart1.title='Bar chart'
chart1.x_axis.title='이름'
chart1.y_axis.title='점수'
data = Reference(ws1, min_col=1, min_row=1, max_row=count+1, max_col=3)
cat = Reference(ws1, min_col=1, min_row=2, max_row=count+1)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cat)
ws1.add_chart(chart1, 'F1')
wb.save('day2result.xlsx')
print('write...done')
def insertarGraficoMercadoTC(ws):
maxCol = col2num(getMaxCol(ws, 'B', 46))
c1 = BarChart()
v1 = Reference(ws, min_col=1, min_row=47, max_col=maxCol)
c1.add_data(v1, titles_from_data=True, from_rows=True)
c1.y_axis.scaling.min = 0
c1.y_axis.majorGridlines = None
c2 = LineChart()
v2 = Reference(ws, min_col=1, min_row=48, max_col=maxCol)
c2.add_data(v2, titles_from_data=True, from_rows=True)
c2.y_axis.axId = 200
c1.z_axis = c2.y_axis
categories = Reference(ws, min_col=2, min_row=46, max_col=maxCol)
c1.set_categories(categories)
# Display y-axis of the second chart on the right by setting it to cross the x-axis at its maximum
c1.y_axis.crosses = "max"
c1 += c2
ws.add_chart(c1, "A54")
import openpyxl
from openpyxl.chart import BarChart, Reference, Series
workbook = openpyxl.load_workbook("bmi.xlsx")
sheet = workbook["Sheet1"]
chart = BarChart()
# first column is used as label, starting from row 2
labels = Reference(sheet, min_col=1, min_row=2, max_row=3)
# first row is used for header, that is why min_row is 1
data = Reference(sheet, min_col=3, min_row=1, max_row=3)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
chart.title = "Bar Chart"
chart.x_axis.title = "Name"
chart.y_axis.title = "Height"
chart.series[0].SeriesLabel = "height"
sheet.add_chart(chart, 'E1')
workbook.save('bmi_chart.xlsx')
def make_bar(sheet, left_col, top_row, bot_row, title, x_title, y_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)
bar = BarChart()
bar.type = "col"
bar.style = 10
bar.shape = 4
bar.title = title
bar.y_axis.title = y_title
bar.x_axis.title = x_title
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)
bar.add_data(data, titles_from_data=True)
bar.set_categories(labels)
bar.height = height
bar.width = width
sheet.add_chart(bar, print_cell)
# ch19_32.py
import openpyxl
from openpyxl.chart import BarChart, 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 = BarChart() # 長條圖
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_32.xlsx')
def max_mean(self):
keyword = "Odau"
file_list = os.listdir(self.process_folder)
for file in file_list:
if "rms.txt" in file_list:
print("已绘图")
break
else:
if keyword in file:
print(file)
data = pd.read_excel(self.process_folder + file, sheet_name="RMS")
if self.upper_limb:
col = {1: "RMS_1_三角肌前束",
2: "RMS_2_三角肌后束",
3: "RMS_3_肱二头肌",
4: "RMS_4_肱三头肌",
5: "RMS_5_桡侧腕屈肌",
6: "RMS_6_尺侧腕伸肌"}
else:
col = {1: "RMS_1_股直肌",
2: "RMS_2_股二头肌",
3: "RMS_3_半腱肌",
4: "RMS_4_股内侧肌",
5: "RMS_5_胫骨前肌",
6: "RMS_6_外侧腓肠肌"
}
# ["time", "RMS_1_股直肌", "RMS_2_股二头肌", "RMS_3_半腱肌", "RMS_4_股内侧肌", "RMS_5_胫骨前肌", "RMS_6_外侧腓肠肌"]
# 取最大的前40%的值，求平均
frames = len(data)
max_frames = int(0.2 * frames)
rms_max = ["RMS_max", 0, 0, 0, 0, 0, 0]
for i in range(1, 7):
temp = data.iloc[data[col[i]].argsort()[-max_frames:]]
# print("debug1",temp)
rms_max[i] = mean(temp[col[i]])
# print("debug2", rms_max)
print(rms_max)
wb = openpyxl.load_workbook(self.process_folder + file)
# print("open and plot:", self.process_folder + file)
ws = wb["RMS"]
ws.append(rms_max)
wb.save(self.process_folder + file)
wb = openpyxl.load_workbook(self.process_folder + file)
ws = wb["RMS"]
chart = BarChart()
cats = Reference(ws, min_col=2, max_col=7, min_row=1)
chart.set_categories(cats)
chart.width = 20
chart.height = 10
chart.x_axis.title = "time/s"
chart.y_axis.title = "RMS/mV"
chart.y_axis.scaling.max = 0.5
rms_max_data = Reference(ws, min_col=2, max_col=7, min_row=frames+1)
chart.add_data(rms_max_data)
ws.add_chart(chart, "I20")
wb.save(self.process_folder + file)
if ((max_rfu_r.get(row_num, 0)) > 1.0): # data still in this bin
ws8.cell(row=v_offset, column=1 + cx8).value = bin_range
for (col_num2, rfu) in cursor2.execute(
'SELECT sample_number,intensity FROM bin_data WHERE bin = ?',
(bin, )):
if (rfu > max_rfu_c[col_num2] * (second_screen / 100)):
ws8.cell(row=v_offset + col_num2,
column=cx8 + 1).value = round(
(100 * rfu) / minus_2_rfu_c[col_num2], 1)
cx8 = cx8 + 1
# calculate placement of data - link to chart
ws8.column_dimensions['A'].width = 50
chart1 = BarChart()
chart1.type = "col"
chart1.style = 2
chart1.width = 0.7 * sites
chart1.height = 15
chart1.gapWidth = 10
chart1.overlap = 100
chart1.grouping = "percentStacked"
chart1.legend = None
# chart1.title = 'Percent Stacked Chart'
# chart1.y_axis.title = 'Percentage'
# chart1.x_axis.title = 'Sample Sites'
# chart1.varyColors = True
# chart1.shape = 4
def _fill_train_sheet(self, test_errors: list, train_errors: list, wb: Workbook, neat_settings: list,
source_dataframe: pd.DataFrame, train_end_index: int, test_end_index: int, legend: dict,
normalization_statistic: dict, normalization_method: str):
column_offset = 4
wb.create_sheet('Обучение')
sheet = wb['Обучение']
sheet.cell(1, (0 * column_offset) + 1, "Ошибка обучения")
sheet.cell(1, (0 * column_offset) + 2, train_errors[-1])
sheet.cell(1, (0 * column_offset) + 3, "Ошибка тестирования")
sheet.cell(1, (0 * column_offset) + 4, test_errors[-1])
cell = sheet.cell(3, 1, 'Обучение')
cell.fill = PatternFill(start_color='00EE00', end_color='00EE00', fill_type="solid")
cell = sheet.cell(3, 2, 'Тестирование')
cell.fill = PatternFill(start_color='EEEE00', end_color='EEEE00', fill_type="solid")
sheet.cell(4, 1, legend.get("header"))
self._write_vector_as_column(column_to_paste=1,
row_to_paste=5,
sheet=sheet,
vector=legend.get("data"),
train_end_index=train_end_index,
test_end_index=test_end_index)
next_free_column = 2
for i, column_name in enumerate(source_dataframe.columns):
sheet.cell(4, next_free_column, column_name)
self._write_vector_as_column(column_to_paste=next_free_column,
row_to_paste=5,
sheet=sheet,
vector=source_dataframe[column_name].values,
train_end_index=train_end_index,
test_end_index=test_end_index)
next_free_column = i + 2
epochs = x = [i for i in range(1, len(train_errors) + 1)]
sheet.cell(4, next_free_column + (0 * column_offset) + 1, "Эпоха")
train_errors_cell = sheet.cell(4, next_free_column + (0 * column_offset) + 2, 'Ошибка обучения')
self._write_vector_as_column(column_to_paste=next_free_column + (0 * column_offset) + 1,
row_to_paste=5,
sheet=sheet,
vector=epochs)
train_end_row = self._write_vector_as_column(column_to_paste=next_free_column + (0 * column_offset) + 2,
row_to_paste=5,
sheet=sheet,
vector=train_errors)
sheet.cell(4, next_free_column + (1 * column_offset) + 1, "Эпоха")
test_errors_cell = sheet.cell(4, next_free_column + (1 * column_offset) + 2, 'Ошибка тестирования')
self._write_vector_as_column(column_to_paste=next_free_column + (1 * column_offset) + 1,
row_to_paste=5,
sheet=sheet,
vector=epochs)
test_end_row = self._write_vector_as_column(column_to_paste=next_free_column + (1 * column_offset) + 2,
row_to_paste=5,
sheet=sheet,
vector=test_errors)
self._create_chart_for_factor_and_predicted_values(sheet=sheet,
title='Ошибка обучения',
legend_name='Эпоха',
chart_pos=sheet.cell(1, next_free_column + (
1 * column_offset) + column_offset).coordinate,
data_reference=Reference(sheet,
min_col=train_errors_cell.column,
min_row=train_errors_cell.row,
max_col=train_errors_cell.column,
max_row=train_end_row),
marker='none'
)
self._create_chart_for_factor_and_predicted_values(sheet=sheet,
title='Ошибка тестирования',
legend_name='Эпоха',
chart_pos=sheet.cell(16, next_free_column + (
1 * column_offset) + column_offset).coordinate,
data_reference=Reference(sheet,
min_col=test_errors_cell.column,
min_row=test_errors_cell.row,
max_col=test_errors_cell.column,
max_row=test_end_row),
marker='none'
)
next_free_row, next_free_column = self._fill_neat_settings(sheet=sheet, col_to_paste=next_free_column + (1 * column_offset) + column_offset,
row_to_paste=32, neat_settings=neat_settings)
next_free_column += 1
sheet.cell(32, next_free_column + (1 * column_offset) + 1, "Нормализация:")
method = self.neat_settings_translations.get(normalization_method)
method = method if method is not None else normalization_method
sheet.cell(32, next_free_column + (1 * column_offset) + 2, method)
sheet.cell(33, next_free_column + (1 * column_offset) + 1, 'Статистика распределения')
category_cell: Cell = sheet.cell(34, next_free_column + (1 * column_offset) + 1, 'Период')
values_cell: Cell = sheet.cell(34, next_free_column + (1 * column_offset) + 2, 'Процент нормализованных значений в периоде')
category_end_index = self._write_vector_as_column(column_to_paste= next_free_column + (1 * column_offset) + 1, row_to_paste=35,sheet=sheet,vector=list(normalization_statistic.keys()))
values_end_index = self._write_vector_as_column(column_to_paste= next_free_column + (1 * column_offset) + 2, row_to_paste=35,sheet=sheet,vector=list(normalization_statistic.values()))
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = 'Распределение нормализованных значений'
chart1.y_axis.title = 'Процент значений содержащихся в периоде'
chart1.x_axis.title = 'Период'
data = Reference(sheet, min_col=values_cell.column, min_row=values_cell.row, max_row=values_end_index, max_col=values_cell.column)
cats = Reference(sheet, min_col=category_cell.column, min_row=category_cell.row+1, max_row=category_end_index, max_col=category_cell.column)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
sheet.add_chart(chart1, sheet.cell(34, next_free_column + (1 * column_offset) + 3).coordinate)
import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
wb = Workbook()
ws = wb.active
df = pd.read_csv('population.csv') #←CSVファイルを読み込む
ws.append(df.columns.tolist()) #←ワークシートにヘッダーを追加する
for row in df.values:
ws.append(list(row)) #←ワークシートに行データを追加する
row_length = 1 + len(df.values) #←1行目はヘッダーなので行数に1を加算
data = Reference(ws, min_col=2, max_col=2, min_row=1, max_row=row_length)
categories = Reference(ws, min_col=1, max_col=1, min_row=2, max_row=row_length)
chart = BarChart() #←棒グラフ
chart.type = 'col'
chart.style = 10
chart.shape = 4
chart.title = '都道府県別の人口' #←グラフのタイトル
chart.x_axis.title = '都道府県' #←X軸ラベル
chart.y_axis.title = '人口' #←Y軸ラベル
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, 'A9') #←グラフをA列9行目に追加する
wb.save('population_vertical.xlsx')
rows = [
('Month', 'Joined', 'Left'),
(2, 50, 10),
(3, 22, 5),
(4, 21, 7),
(5, 45, 1),
(6, 15, 4),
(7, 12, 3),
]
for row in rows:
ws.append(row)
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Membership'
chart1.x_axis.title = 'Month'
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")
from copy import deepcopy
Reference,
Series,
)
wb = Workbook()
ws = wb.active
rows = [
['Aliens', 2, 3, 4, 5, 6, 7],
['Humans', 10, 40, 50, 20, 10, 50],
]
for row in rows:
ws.append(row)
c1 = BarChart()
v1 = Reference(ws, min_col=1, min_row=1, max_col=7)
c1.series = [Series(v1, title_from_data=True)]
c1.x_axis.title = 'Days'
c1.y_axis.title = 'Aliens'
c1.y_axis.majorGridlines = None
c1.title = 'Survey results'
# Create a second chart
c2 = LineChart()
v2 = Reference(ws, min_col=1, min_row=2, max_col=7)
c2.series = [Series(v2, title_from_data=True)]
c2.y_axis.axId = 20
c2.y_axis.title = "Humans"
for i in range(1, 10):
for j in range(1, 5):
ws41.cell(row=i, column=j).value =i*j
wb4.save('chart.xlsx')
wb4.close()
wb5 = openpyxl.load_workbook('chart.xlsx')
ws51 = wb5['Data']
ws52 = wb5['Area']
ws53 = wb5['Bar']
achart = AreaChart()
category = Reference(ws51, min_row=1, min_col=1, max_row=10)
data = Reference(ws51, min_row=1, min_col=2, max_row=10, max_col=4)
achart.set_categories(category)
achart.add_data(data)
ws52.add_chart(achart, 'A1')
wb5.save('chart.xlsx')
bchart = BarChart()
bchart.set_categories(category)
bchart.add_data(data)
ws53.add_chart(bchart, 'A1')
wb5.save('chart.xlsx')
#ws['A'] => 1 column
#ws['A:C'] => A to C columns
#ws['2'] => 1 row
#ws['2:10'] => 2 to 10 rows
wb = openpyxl.load_workbook(r'C:\Bhushan\Python\log\chart.xlsx')
ws1 = wb['Sheet1']
ws2 = wb.create_sheet('Area')
ws3 = wb.create_sheet('Bar')
from openpyxl.chart import AreaChart, BarChart, Reference
#Chart Obj => Category: x-axis, data: y-axis
#expore other charts by urself
chart1 = AreaChart()
chart2 = BarChart()
cat = Reference(ws1, min_row=1, max_row=10, min_col=1)
data = Reference(ws1, min_row=1, max_row=10, min_col=2, max_col=3)
chart1.set_categories(cat)
chart1.add_data(data)
chart2.set_categories(cat)
chart2.add_data(data)
ws2.add_chart(chart1)
ws3.add_chart(chart2)
wb.save(r'C:\Bhushan\Python\log\chart.xlsx')
c2.add_data(data, titles_from_data=True)
c2.set_categories(labels)
for s in c2.series:
s.graphicalProperties.line.noFill = True
c2.hiLowLines = ChartLines()
c2.upDownBars = UpDownBars()
c2.title = "Open-high-low-close"
# add dummy cache
c2.series[-1].val.numRef.numCache = cache
ws.add_chart(c2, "G10")
# Create bar chart for volume
bar = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=6)
bar.add_data(data, titles_from_data=True)
bar.set_categories(labels)
from copy import deepcopy
# Volume-high-low-close
b1 = deepcopy(bar)
c3 = deepcopy(c1)
c3.y_axis.majorGridlines = None
c3.y_axis.title = "Price"
b1.y_axis.axId = 20
b1.z_axis = c3.y_axis
b1.y_axis.crosses = "max"
b1 += c3
wb = Workbook()
ws = wb.active
rows = [
['Aliens', 2, 3, 4, 5, 6, 7],
['Humans', 100, 100, 100, 100, 100, 100],
['Humans2', 50, 50, 50, 50, 50, 50],
['Humans3', 20, 20, 20, 20, 20, 20],
['Humans5', 2.67, 16.5, 17.5, 10],
]
for row in rows:
ws.append(row)
c1 = BarChart()
v1 = Reference(ws, min_col=1, min_row=1, max_col=7)
c1.add_data(v1, titles_from_data=True, from_rows=True)
c1.x_axis.title = 'Days'
c1.y_axis.title = 'Aliens'
c1.y_axis.majorGridlines = None
c1.title = 'Survey results'
# Create a second chart
c2 = LineChart()
v2 = Reference(ws, min_col=1, min_row=2, max_col=7)
v3 = Reference(ws, min_col=1, min_row=3, max_col=7)
v4 = Reference(ws, min_col=1, min_row=4, max_col=7)
v5 = Reference(ws, min_col=1, min_row=5, max_col=7)
("Sample",),
(1,),
(2,),
(3,),
(2,),
(3,),
(3,),
(1,),
(2,),
]
for r in rows:
ws.append(r)
c = BarChart()
data = Reference(ws, min_col=1, min_row=1, max_row=8)
c.add_data(data, titles_from_data=True)
c.title = "Chart with patterns"
# set a pattern for the whole series
series = c.series[0]
fill = PatternFillProperties(prst="pct5")
fill.foreground = ColorChoice(prstClr="red")
fill.background = ColorChoice(prstClr="blue")
series.graphicalProperties.pattFill = fill
# set a pattern for a 6th data point (0-indexed)
pt = DataPoint(idx=5)
pt.graphicalProperties.pattFill = PatternFillProperties(prst="ltHorz")
series.dPt.append(pt)
def test_add_chart(Worksheet):
from openpyxl.chart import BarChart
ws = Worksheet(DummyWorkbook())
chart = BarChart()
ws.add_chart(chart, "A1")
assert chart.anchor == "A1"
def draw_excel_charts(sblog_dict, excel_filename):
"""
This function accepts a defaultdict which contains all the data of tps and rt
an will create an Microsoft Excel spreadsheet with charts.
:param sblog_dict:
:param excel_filename:
:return:
"""
clean_dict = data_cleansing(sblog_dict)
tps_data, rt_data, avg_rt_data, tps_std_data, rt_std_data = [], [], [], [], []
workload_cols_rows = {}
workload_types = set()
col_name_parsed = False
for key in clean_dict.keys():
data_list = clean_dict[key]
col_name, tps, rt, avg_rt, tps_std, rt_std = zip(*data_list)
if not col_name_parsed:
rt_data.append(col_name)
tps_data.append(col_name)
avg_rt_data.append(col_name)
tps_std_data.append(col_name)
rt_std_data.append(col_name)
workload_types = set(x.split('_')[1] for x in col_name[1:])
workload_cols_rows.update({wl_type: {'cols': 0, 'rows': 0} for wl_type in workload_types})
col_name_parsed = True
tps_data.append(tps)
rt_data.append(rt)
avg_rt_data.append(avg_rt)
tps_std_data.append(tps_std)
rt_std_data.append(rt_std)
# print('tps_data: {}'.format(tps_data))
# print('rt_data: {}'.format(rt_data))
# print('avg_rt_data: {}'.format(avg_rt_data))
wb = Workbook(write_only=True)
for wl_type in workload_types:
wb.create_sheet(title=get_sheetname_by_workload(wl_type))
merged_rows = []
for tps, rt, avg_rt, tps_std, rt_std in zip(tps_data, rt_data, avg_rt_data, tps_std_data, rt_std_data):
merged_rows.append(tps + rt + avg_rt + tps_std + rt_std)
# print(merged_rows)
# The tps chart:
# print('merged_rows: {}\n'.format(merged_rows))
for row in merged_rows:
for wl_type in workload_types:
wl_row = [row[i] for i in range(len(row)) if
wl_type in merged_rows[0][i].split('_') or i == 0 or merged_rows[0][i] == 'Thread']
# print('wl_row: {}'.format(wl_row))
wb.get_sheet_by_name(get_sheetname_by_workload(wl_type)).append(wl_row)
workload_cols_rows[wl_type]['cols'] = len(wl_row)
workload_cols_rows[wl_type]['rows'] += 1
for ws in wb:
global_max_row = workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows']
# Chart of TPS
chart_tps = BarChart(gapWidth=500)
chart_tps.type = "col"
chart_tps.style = 10
chart_tps.title = "TPS chart of {}".format(ws.title)
chart_tps.y_axis.title = 'tps'
chart_tps.y_axis.scaling.min = 0
chart_tps.x_axis.title = 'tps'
data_tps = Reference(ws, min_col=2, min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] / 5)
cats_tps = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_tps.add_data(data_tps, titles_from_data=True)
chart_tps.set_categories(cats_tps)
chart_tps.shape = 4
ws.add_chart(chart_tps, "A{}".format(global_max_row + 5))
# Chart of Response Time
chart_rt = BarChart(gapWidth=500)
chart_rt.type = "col"
chart_rt.style = 10
chart_rt.title = "Response Time(95%) chart of {}".format(ws.title)
chart_rt.y_axis.title = 'rt'
chart_rt.y_axis.scaling.min = 0
chart_rt.x_axis.title = 'response time'
data_rt = Reference(ws, min_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] / 5 + 2,
min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 2 / 5)
cats_rt = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_rt.add_data(data_rt, titles_from_data=True)
chart_rt.set_categories(cats_rt)
chart_rt.shape = 4
ws.add_chart(chart_rt, "I{}".format(global_max_row + 5))
# Chart of avg response time
chart_avg_rt = BarChart(gapWidth=500)
chart_avg_rt.type = "col"
chart_avg_rt.style = 10
chart_avg_rt.title = "Average Response Time chart of {}".format(ws.title)
chart_avg_rt.y_axis.title = 'avg rt'
chart_avg_rt.y_axis.scaling.min = 0
chart_avg_rt.x_axis.title = 'avg resp time'
data_avg_rt = Reference(ws, min_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 2 / 5 + 2,
min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 3 / 5)
cats_avg_rt = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_avg_rt.add_data(data_avg_rt, titles_from_data=True)
chart_avg_rt.set_categories(cats_avg_rt)
chart_avg_rt.shape = 4
ws.add_chart(chart_avg_rt, "Q{}".format(global_max_row + 5))
# Chart of tps standard deviation
chart_tps_std = BarChart(gapWidth=500)
chart_tps_std.type = "col"
chart_tps_std.style = 10
chart_tps_std.title = "tps standard deviation chart of {}".format(ws.title)
chart_tps_std.y_axis.title = 'std'
chart_tps_std.y_axis.scaling.min = 0
chart_tps_std.x_axis.title = 'tps std'
data_tps_std = Reference(ws, min_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 3 / 5 + 2,
min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 4 / 5)
cats_tps_std = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_tps_std.add_data(data_tps_std, titles_from_data=True)
chart_tps_std.set_categories(cats_tps_std)
chart_tps_std.shape = 4
ws.add_chart(chart_tps_std, "A{}".format(global_max_row + 20))
# Chart of response time standard deviation
chart_rt_std = BarChart(gapWidth=500)
chart_rt_std.type = "col"
chart_rt_std.style = 10
chart_rt_std.title = "response time standard deviation chart of {}".format(ws.title)
chart_rt_std.y_axis.title = 'std'
chart_rt_std.y_axis.scaling.min = 0
chart_rt_std.x_axis.title = 'rt std'
data_rt_std = Reference(ws, min_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 4 / 5 + 2,
min_row=1,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'],
max_col=workload_cols_rows[get_wl_from_sheetname(ws.title)]['cols'] * 5 / 5)
cats_rt_std = Reference(ws, min_col=1, min_row=2,
max_row=workload_cols_rows[get_wl_from_sheetname(ws.title)]['rows'])
chart_rt_std.add_data(data_rt_std, titles_from_data=True)
chart_rt_std.set_categories(cats_rt_std)
chart_rt_std.shape = 4
ws.add_chart(chart_rt_std, "I{}".format(global_max_row + 20))
wb.save(excel_filename)
from openpyxl import load_workbook
wb = load_workbook("sample.xlsx")
ws = wb.active
from openpyxl.chart import BarChart, Reference
bar_value = Reference(ws, min_row=2, max_row=11, min_col=2,
max_col=3) # 영어, 수학 성적을 chart로 그리기 위해 영역 지정
bar_chart = BarChart() # 차트 종류 설정 (Bar, Line, Pie, ...)
bar_chart.add_data(bar_value)
ws.add_chart(bar_chart, "E1") # E1에 차트 넣기(위치 선정)
# B1:C11까지의 데이터 + 제목 포함
line_value = Reference(ws, min_row=1, max_row=11, min_col=2, max_col=3)
line_chart = LineChart()
line_chart.add_data(line_value,
titles_from_data=True) # 계열 -> 영어, 수학 (제목에서 가져옴)
line_chart.title = "성적표" # 제목
line_chart.style = 20 # 미리 적용된 스타일을 적용, 사용자 지정 가능
line_chart.y_axis.title = "점수" # y축의 제목
line_chart.x_axis.title = "번호" # x축의 제목
wb.save("sample_chart.xlsx")
def ronava_bar_chart(writingSheet, dataSheet, params):
# TODO add dictionary in parameters to avoid overlapping
if params["use"] == "bars":
data = Reference(
dataSheet,
min_col=params["data_min_col"],
min_row=params["data_min_row"],
max_row=params["data_max_row"],
max_col=params["data_max_col"],
)
cats = Reference(
dataSheet,
min_col=params["cats_min_col"],
min_row=params["cats_min_row"],
max_row=params["cats_max_row"],
max_col=params["cats_max_col"],
)
chart = BarChart()
chart.type = params["type"]
chart.style = 12
# chart.grouping = "stacked"
chart.title = params["title"]
chart.y_axis.title = params["y_axis"]
chart.x_axis.title = params["x_axis"]
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.height = params["heigth"]
chart.width = params["width"]
writingSheet.add_chart(chart, "D2")
elif params["use"] == "single":
c1 = BarChart()
v1 = Reference(
dataSheet, min_col=params["data_min_col"], min_row=params["data_min_row"], max_col=params["data_max_col"]
)
cats = Reference(
dataSheet, min_col=params["cats_min_col"], min_row=params["cats_min_row"], max_col=params["cats_max_col"]
)
c1.series = [Series(v1, title_from_data=True)]
c1.style = 12
c1.set_categories(cats)
c1.x_axis.title = params["x_axis"]
c1.y_axis.title = params["y_axis"]
c1.height = params["heigth"]
c1.width = params["width"]
c1.title = params["title"]
writingSheet.add_chart(c1, "D4")
else:
c1 = BarChart()
v1 = Reference(
dataSheet, min_col=params["data_min_col"], min_row=params["data_min_row"], max_col=params["data_max_col"]
)
cats = Reference(
dataSheet, min_col=params["cats_min_col"], min_row=params["cats_min_row"], max_col=params["cats_max_col"]
)
c1.series = [Series(v1, title_from_data=True)]
c1.y_axis.majorGridlines = None
c1.set_categories(cats)
c1.x_axis.title = params["x_axis"]
c1.y_axis.title = params["y_axis"]
c1.height = params["heigth"]
c1.width = params["width"]
c1.title = params["title"]
c1.style = 12
# Create a second chart
c2 = LineChart()
v2 = Reference(
dataSheet,
min_col=params["data_min_col"],
min_row=params["data_min_row"] + 1,
max_col=params["data_max_col"],
)
c2.series = [Series(v2, title_from_data=True)]
c2.y_axis.axId = 20
c2.y_axis.title = "Porcentaje Produccion"
# Assign the y-axis of the second chart to the third axis of the first chart
c1.z_axis = c2.y_axis
c1.y_axis.crosses = "max"
c1 += c2
writingSheet.add_chart(c1, "D4")
def _set_cv_lv_chart(self, workbook):
ws = workbook['CV_LV']
ws_data = workbook['Data_day']
chart1 = BarChart()
chart1.type = "col"
chart1.width = 19
chart1.height = 8
chart1.style = 10
chart1.y_axis.title = 'Vehicules à moteur en % du TJMO de la section'
chart1.x_axis.title = "Selon l'heure de la journée"
chart1.gapWidth = 0
#chart1.legend = None
data = Reference(ws_data,
min_col=11,
min_row=4,
max_row=28,
max_col=11)
cats = Reference(ws, min_col=1, min_row=5, max_row=28)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
# chart1.shape = 4
s = chart1.series[0]
s.graphicalProperties.line.solidFill = "000000"
s.graphicalProperties.solidFill = "66ffb2"
chart2 = LineChart()
data = Reference(ws_data,
min_col=11,
min_row=34,
max_row=58,
max_col=11)
chart2.add_data(data, titles_from_data=True)
data = Reference(ws_data,
min_col=11,
min_row=65,
max_row=89,
max_col=11)
chart2.add_data(data, titles_from_data=True)
# chart2.style = 12
s = chart2.series[0]
s.graphicalProperties.line.solidFill = "3333ff"
s.smooth = False
s = chart2.series[1]
s.graphicalProperties.line.solidFill = "ff3333"
s.graphicalProperties.line.dashStyle = "sysDash"
s.smooth = False
chart1 += chart2
ws.add_chart(chart1, "A15")
ws = wb.create_sheet('chart', 0)
# ws.merge_cells('A1:D1') # 셀 병합
ws.merge_cells(start_row=1, end_row=1, start_column=1, end_column=4)
ws['A1'] = "성적표"
ws['A1'].font = Font(name='맑은 고딕', size=15, bold=True)
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
ws.append(['이름', '국어', '영어', '수학'])
ws.append(['애옹이', 90, 88, 99])
ws.append(['새옹이', 99, 80, 100])
ws.append(['흰양말', 75, 100, 70])
wb.save('openpyxl_chart222.xlsx')
barchart = BarChart() # 차트 객체 생성
barchart.title = "성적표"
barchart.x_axis.title = "이름"
barchart.y_axis.title = "점수"
data = Reference(ws, min_col=2, max_col=4, min_row=2,
max_row=5) # 차트 만들 때 사용될 데이터 범위지정
cate = Reference(ws, min_col=1, max_col=1, min_row=3,
max_row=5) # 데이터 카테고리 범위지정
barchart.add_data(data, titles_from_data=True)
barchart.set_categories(cate)
# barchaert.shape = 1
barchart.style = 2
ws.add_chart(barchart, 'F1') # F1셀에 차트를 추가
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")
import openpyxl as xl
from openpyxl.chart import BarChart, Reference
wb = xl.load_workbook('transactions.xlsx')
sheet = wb['Sheet1']
cell = sheet['A1']
cell = sheet.cell(1, 1)
for row in range(1, sheet.max_row+1):
cell = sheet.cell(row,3)
corrected_price = cell.value * 1
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 = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'e2')
wb.save('transactions2.xlsx')
def set_charts(self, ws):
chart_peso = BarChart()
chart_peso.type = "col"
chart_peso.style = 10
chart_peso.title = "Peso"
data = Reference(ws, min_col=5, min_row=29, max_col=15)
chart_peso.add_data(data, from_rows=True)
chart_peso.shape = 4
ws.add_chart(chart_peso, "C14")
chart_grasa = BarChart()
chart_grasa.type = "col"
chart_grasa.style = 10
chart_grasa.title = "%GRASA (YC)"
data = Reference(ws, min_col=5, min_row=45, max_col=15)
chart_grasa.add_data(data, from_rows=True)
chart_grasa.shape = 4
ws.add_chart(chart_grasa, "C30")
i = 'S' +str((i-91)*7+1)
sheet2.add_image(img3, i)
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)
continue
else:
wb = Workbook()
sheet = wb.active
monty = opassage[0:8]
preview = "".join([s for s in monty if s != " "])
sheet.title = "{}".format(preview)
i = 1
for word, occur in ordered_dict:
sheet['A' + str(i)] = word
sheet['B' + str(i)] = occur
i += 1
values = Reference(sheet, min_col=1,min_row=1,max_col=2,max_row=len(ordered_dict))
chart = BarChart()
chart.type = "col"
chart.style = 11
chart.title = "Word Frequency"
chart.y_axis.title = "Frequency"
chart.x_axis.title = "Word"
chart.add_data(values, titles_from_data=True)
sheet.add_chart(chart, "D2")
if not(isdir("C:/WordCounterExports")):
mkdir("C:/WordCounterExports/")
name = "wc{}.xlsx".format(preview)
wb.save("C:/WordCounterExports/{}".format(name))
print("Workbook successfully created at C:/WordCounterExports/ named {}".format(name))
continue
#!/usr/bin/env
# -python3*- coding: utf-8 -*-
"""
Created on Mon Aug 24 19:01:54 2020
@author: emmanuelidehen
"""
import pandas as pd
import numpy as np
import openpyxl
from openpyxl import load_workbook
from openpyxl import worksheet
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.chart import BarChart, PieChart, Reference
wb = load_workbook('Numerical_Analysis_Emmanuel_idehen_Report.xlsx')
ws = wb.active
chart = BarChart()
data = Reference(ws, min_row=8, min_col = 1, max_col=13, max_row=13 )
chart.add_data(data, titles_from_data=True)
ws.add_chart(chart, 'B14')
wb.save('line.xlsx')
def sleep(self, bins):
# ------------------------------ STEP 4 整理表 ---------------------------------------------------------------
# ==== sheet 各个bin的变量逾期分布和KS值 ====
self.bins = bins
train_bin = self.woe.cal_bin_ks(self.X_train[self.left_features],
self.y_train)
oot_bin = self.woe.cal_bin_ks(self.X_oot[self.left_features],
self.y_oot,
oot=True)
# 评分相关性
sheet_correlations = self.X_train[self.left_features].corr()
# ==== sheet 特征权重和预测概率分析 ====
# result_train = pd.concat([X_woe, pd.DataFrame(stacking_train)], axis=1).rename(columns={0: 'model_score'})
result_test = pd.concat(
[self.X_oot_woe, pd.DataFrame(self.stacking_oot)],
axis=1).rename(columns={0: 'model_score'})
if judge_h2o(self.clf):
im_final = self.clf._model_json['output'][
'variable_importances'].as_data_frame()[[
'variable', 'percentage'
]].rename(
columns={
'variable': 'feature_name',
'percentage': 'feature_coef / percentage'
}).reset_index(drop=True)
else:
im_final = get_importance(self.clf,
self.X_woe).reset_index(drop=True)
model_col = list(set(result_test.columns) - {'model_score'})
score_corr = []
for i in model_col:
score_corr.append(result_test[i].corr(result_test['model_score']))
label_corr = []
for i in model_col:
label_corr.append(result_test[i].corr(pd.Series(self.y_oot)))
tmp = pd.merge(pd.DataFrame({
'feature_name': model_col,
'与预测概率相关性': score_corr
}),
pd.DataFrame({
'feature_name': model_col,
'与风险相关方向': label_corr
}),
how='inner',
on='feature_name')
# ==== sheet 模型分析，ks曲线，累计bad rate... ====
sheet_weights = pd.merge(tmp, im_final, how='inner', on='feature_name')
model_info_tmp_train = pd.concat(
[self.train_ts, self.y_train,
pd.DataFrame(self.stacking_train)],
axis=1).rename(columns={0: 'model_score'})
model_info_tmp_oot = pd.concat(
[self.oot_ts, self.y_oot,
pd.DataFrame(self.stacking_oot)],
axis=1).rename(columns={0: 'model_score'})
model_info_tmp_train['tag'] = 'INS'
model_info_tmp_oot['tag'] = 'OOT'
model_info_tmp = pd.concat([model_info_tmp_train, model_info_tmp_oot])
model_info_tmp['bin'] = pd.qcut(model_info_tmp['model_score'], 10)
model_info_tmp['month'] = model_info_tmp[
self.datetime_feature].dt.strftime('%Y%m')
model_info_tmp_ins = pd.DataFrame(
model_info_tmp[model_info_tmp['tag'] == 'INS'].groupby('bin').agg({
self.datetime_feature: ['count'],
self.label: ['sum']
})).reset_index()
model_info_tmp_oot = pd.DataFrame(
model_info_tmp[model_info_tmp['tag'] == 'OOT'].groupby('bin').agg({
self.datetime_feature: ['count'],
self.label: ['sum']
})).reset_index()
sheet_model_info_ins = Tea._ks_curve(model_info_tmp_ins)
sheet_model_info_ins['tag'] = 'INS'
sheet_model_info_oot = Tea._ks_curve(model_info_tmp_oot)
sheet_model_info_oot['tag'] = 'OOT'
self.sheets['model_info_tmp'] = model_info_tmp
# ------------------------------- STEP 5 写入表 ---------------------------------------------------------------
bin_ks_ins = pd.DataFrame()
bin_ks_oot = pd.DataFrame()
for i in train_bin.keys():
train_tmp = train_bin[i].reset_index().rename(
columns={'index': 'bins'})
train_tmp.insert(0, 'feature', i)
bin_ks_ins = pd.concat([bin_ks_ins, train_tmp])
oot_tmp = oot_bin[i].reset_index().rename(
columns={'index': 'bins'})
oot_tmp.insert(0, 'feature', i)
bin_ks_oot = pd.concat([bin_ks_oot, oot_tmp])
self.sheets['sheet_feature_bin_ins'] = bin_ks_ins
self.sheets['sheet_feature_bin_oot'] = bin_ks_oot
self.sheets['sheet_correlations'] = sheet_correlations
self.sheets['sheet_weights'] = sheet_weights
self.sheets['sheet_model_info_ins'] = sheet_model_info_ins
self.sheets['sheet_model_info_oot'] = sheet_model_info_oot
writer = pd.ExcelWriter(self.file_path)
self.sheets['sheet_sample'].to_excel(writer,
sheet_name='样本分析',
index=False)
self.sheets['sheet_distribution'].to_excel(
writer, sheet_name='变量缺失率 & 基本探索性分析', index=False)
self.sheets['sheet_psi_ks_iv'].to_excel(writer,
sheet_name='INS变量IV值 & 时间稳定性',
index=False)
row_index = 0
for i in train_bin.keys():
train_tmp = train_bin[i].reset_index().rename(columns={'index': i})
train_tmp['bad'] = '[' + train_tmp['left'].astype(
str) + ',' + train_tmp['right'].astype(str) + ')'
oot_tmp = oot_bin[i].reset_index().rename(columns={'index': i})
oot_tmp['bad'] = '[' + oot_tmp['left'].astype(
str) + ',' + oot_tmp['right'].astype(str) + ')'
train_tmp.to_excel(writer,
startrow=row_index,
startcol=1,
sheet_name='变量逾期分布和KS值',
index=False)
oot_tmp.to_excel(writer,
startrow=row_index,
startcol=13,
sheet_name='变量逾期分布和KS值',
index=False)
row_index += self.bins + 2
sheet_correlations.to_excel(writer, sheet_name='评分相关性', index=True)
sheet_weights.to_excel(writer, sheet_name='模型', index=False)
sheet_model_info_ins.to_excel(writer,
sheet_name='模型',
startcol=6,
index=False)
sheet_model_info_oot.to_excel(writer,
sheet_name='模型',
startrow=11,
startcol=6,
index=False,
header=False)
row_index_8 = 0
for month in model_info_tmp['month'].unique():
trace_back = model_info_tmp[model_info_tmp['month'] == month].drop(
['month'], axis=1).reset_index(drop=True)
trace_back = pd.DataFrame(
trace_back.groupby('bin').agg({
self.datetime_feature: ['count'],
self.label: ['sum']
})).reset_index()
sheet_trace_back = Tea._ks_curve(trace_back, month)
if row_index_8 == 0:
sheet_trace_back.to_excel(writer,
sheet_name='模型回测',
startrow=row_index_8,
startcol=0,
index=False)
row_index_8 += 11
else:
sheet_trace_back.to_excel(writer,
sheet_name='模型回测',
startrow=row_index_8,
startcol=0,
index=False,
header=False)
row_index_8 += 10
writer.save()
def woe_dump(self):
if hasattr(self, 'sheets'):
if 'sheet_feature_bin_ins' in self.sheets.keys():
sheet_feature_bin_ins = self.sheets[
'sheet_feature_bin_ins']
woe_dict = {}
for f in sheet_feature_bin_ins['feature'].unique():
inner_map = {}
_tmp = sheet_feature_bin_ins[
sheet_feature_bin_ins['feature'] == f]
for index, row in _tmp.iterrows():
inner_map['[%s, %s)' %
(row.left, row.right)] = row.woe
woe_dict[f] = inner_map
return woe_dict
else:
raise KeyError(
" 'sheet_feature_bin_ins' not in tea.sheets!")
else:
raise AttributeError("no attribute 'sheets'!")
# ------------------------------- STEP 6 美化（字体/字号/边框/颜色/粗细） --------------------------------------------
if self.embellish:
wb = openpyxl.load_workbook(self.file_path)
left, right, top, bottom = [Side(style='thin', color='000000')] * 4
sheet = wb['样本分析']
for i in sheet['A1':'E3']:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font1'))
i[j].alignment = eval(conf.get('config', 'alignment1'))
i[j].border = eval(conf.get('config', 'border1'))
for i in sheet['A1':'E1']:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font2'))
i[j].fill = eval(conf.get('config', 'fill1'))
sheet = wb['变量缺失率 & 基本探索性分析']
for i in sheet['A1':'L%s' %
(self.sheets['sheet_distribution'].shape[0] + 1)]:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font1'))
i[j].alignment = eval(conf.get('config', 'alignment1'))
i[j].border = eval(conf.get('config', 'border1'))
for i in sheet['A1':'L1']:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font2'))
i[j].fill = eval(conf.get('config', 'fill1'))
sheet = wb['INS变量IV值 & 时间稳定性']
for i in sheet['A1':'E%s' %
(self.sheets['sheet_psi_ks_iv'].shape[0] + 1)]:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font1'))
i[j].alignment = eval(conf.get('config', 'alignment1'))
i[j].border = eval(conf.get('config', 'border1'))
for i in sheet['A1':'E1']:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font2'))
i[j].fill = eval(conf.get('config', 'fill1'))
sheet = wb['变量逾期分布和KS值']
for i in sheet['A1':'X%s' % row_index]:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font1'))
i[j].alignment = eval(conf.get('config', 'alignment1'))
i[j].fill = eval(conf.get('config', 'fill2'))
for ind in range(1, row_index, self.bins + 2):
for i in sheet['B%s' % ind:'L%s' % ind]:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font2'))
i[j].fill = eval(conf.get('config', 'fill1'))
for i in sheet['N%s' % ind:'X%s' % ind]:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font2'))
i[j].fill = eval(conf.get('config', 'fill1'))
sheet['A%s' % ind] = 'INS'
sheet['M%s' % ind] = 'OOT'
sheet['A%s' % ind].font = eval(conf.get('config', 'font3'))
sheet['M%s' % ind].font = eval(conf.get('config', 'font3'))
for i in sheet['A1':'A%s' % row_index]:
for j in range(len(i)):
i[j].fill = eval(conf.get('config', 'fill3'))
for i in sheet['M1':'M%s' % row_index]:
for j in range(len(i)):
i[j].fill = eval(conf.get('config', 'fill3'))
sheet = wb['模型']
for i in sheet['A1':'D%s' % (sheet_weights.shape[0] + 1)]:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font1'))
i[j].alignment = eval(conf.get('config', 'alignment1'))
i[j].border = eval(conf.get('config', 'border1'))
for i in sheet['A1':'D1']:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font2'))
i[j].fill = eval(conf.get('config', 'fill1'))
for i in sheet['G1':'R21']:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font1'))
i[j].alignment = eval(conf.get('config', 'alignment1'))
i[j].border = eval(conf.get('config', 'border1'))
for i in sheet['G1':'R1']:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font2'))
i[j].fill = eval(conf.get('config', 'fill1'))
cnt = 2
while sheet['R' + str(cnt)].value is not None:
c1 = BarChart()
v1 = Reference(sheet,
min_col=16,
min_row=cnt - 1,
max_col=16,
max_row=cnt + 9)
c1.add_data(v1, titles_from_data=True)
c1.x_axis.title = 'Bin Decile'
c1.y_axis.title = 'Bad Rate'
c1.y_axis.majorGridlines = None
c1.title = sheet['R' + str(cnt)].value
# Create a second chart
c2 = LineChart()
v2 = Reference(sheet,
min_col=17,
min_row=cnt - 1,
max_col=17,
max_row=cnt + 9)
c2.add_data(v2, titles_from_data=True)
c2.y_axis.axId = 200
c2.y_axis.title = "Ks"
# Display y-axis of the second chart on the right by setting it to cross the x-axis at its maximum
c1.y_axis.crosses = "max"
c1 += c2
sheet.add_chart(c1, 'T' + str(cnt))
cnt += 10
sheet = wb['模型回测']
for i in sheet['A1':'L%s' % row_index_8]:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font1'))
i[j].alignment = eval(conf.get('config', 'alignment1'))
i[j].border = eval(conf.get('config', 'border1'))
for i in sheet['A1':'L1']:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font2'))
i[j].fill = eval(conf.get('config', 'fill1'))
cnt = 2
while sheet['A' + str(cnt)].value is not None:
c1 = BarChart()
v1 = Reference(sheet,
min_col=11,
min_row=cnt - 1,
max_col=11,
max_row=cnt + 9)
c1.add_data(v1, titles_from_data=True)
c1.x_axis.title = 'Bin Decile'
c1.y_axis.title = 'Bad Rate'
c1.y_axis.majorGridlines = None
c1.title = sheet['A' + str(cnt)].value
# Create a second chart
c2 = LineChart()
v2 = Reference(sheet,
min_col=12,
min_row=cnt - 1,
max_col=12,
max_row=cnt + 9)
c2.add_data(v2, titles_from_data=True)
c2.y_axis.axId = 200
c2.y_axis.title = "Ks"
# Display y-axis of the second chart on the right by setting it to cross the x-axis at its maximum
c1.y_axis.crosses = "max"
c1 += c2
sheet.add_chart(c1, 'N' + str(cnt))
cnt += 10
wb.create_sheet(title='数据测试说明', index=0)
head = wb['数据测试说明']
head['A1'] = '数据测试说明'
head.merge_cells('A1:B1')
head['A2'] = '提供的样本'
head['A3'] = '测试数据变量类型'
head['A4'] = '测试内容'
head.merge_cells('A4:A9')
head['A10'] = '测试结论'
head['B4'] = '样本分析'
head['B5'] = '变量缺失率 & 基本探索性分析'
head['B6'] = 'INS变量IV值 & 时间稳定性'
head['B7'] = '变量逾期分布和KS值'
head['B8'] = '评分相关性'
head['B9'] = '模型'
for i in head['A1':'B10']:
for j in range(len(i)):
if str(i[j])[15:18] in ('B4>', 'B5>', 'B6>', 'B7>', 'B8>',
'B9'):
i[j].font = eval(conf.get('config', 'font4'))
i[j].alignment = eval(conf.get('config', 'alignment1'))
i[j].border = eval(conf.get('config', 'border1'))
else:
i[j].font = eval(conf.get('config', 'font4'))
i[j].alignment = eval(conf.get('config', 'alignment1'))
i[j].border = eval(conf.get('config', 'border1'))
for i in head['A2':'A10']:
for j in range(len(i)):
i[j].font = eval(conf.get('config', 'font2'))
i[j].fill = eval(conf.get('config', 'fill1'))
for i in ['A1', 'B1']:
head[i].font = eval(conf.get('config', 'font2'))
head[i].fill = eval(conf.get('config', 'fill1'))
wb.save(self.file_path)
else:
pass
print('Finish 🍵 ')
ws = wb.create_sheet('barChart')
rows = [
('Number', 'Batch 1', 'Batch 2'),
(2, 10, 30),
(3, 40, 60),
(4, 50, 70),
(5, 20, 10),
(6, 10, 40),
(7, 50, 30),
]
for row in rows:
ws.append(row)
chart1 = BarChart()
chart1.type = 'col'
chart1.style = 10
chart1.title = 'Bar Chart'
chart1.y_axis.title = 'Sample length(mm)'
chart1.x_axis.title = 'Test number'
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
data = Reference(ws, min_col=2, max_col=3, min_row=2, max_row=7)
chart1.add_data(data)
chart1.set_categories(cats)
ws.add_chart(chart1, 'A10')
# Bubble chart
ws = wb.create_sheet('bubbleChart')
aggfunc='sum')
print("Pivot table for the total sum of followers grouped by year and month:")
print(user_followers_by_year_month.head())
file_path = settings.TWEETS_FILE_EXCEL
sheet_name = 'Followers by year and month'
user_followers_by_year_month.to_excel(file_path,
sheet_name=sheet_name,
startrow=3)
wb = load_workbook(file_path)
sheet1 = wb[sheet_name]
sheet1['A1'] = 'Followers by year and month'
sheet1['A2'] = 'elquant.com'
sheet1['A4'] = 'Month/Year'
sheet1['A1'].style = 'Title'
sheet1['A2'].style = 'Headline 2'
bar_chart = BarChart()
data = Reference(sheet1, min_col=2, max_col=15, min_row=4, max_row=16)
categories = Reference(sheet1, min_col=1, max_col=1, min_row=5, max_row=16)
bar_chart.add_data(data, titles_from_data=True)
bar_chart.set_categories(categories)
sheet1.add_chart(bar_chart, anchor="B19")
bar_chart.title = 'Aggregated number of followers by year and month'
bar_chart.style = 3
wb.save(filename=file_path)
def pop_excl (sv_dict, ClusName):
wb = openpyxl.Workbook ()
sh = wb.active
count1 = 0
count2 = 2
alph = ['a', 'b', 'c', 'd']
f = sh['a1']
f.font = Font (bold=True)
f = sh['b1']
f.font = Font (bold=True)
sh.title = 'HighLevel'
sh['a1'] = 'StorageView'
sh['b1'] = 'Size(G)'
for i in sv_dict:
sh[alph[count1] + str (count2)] = i
count1 += 1
sh[alph[count1] + str (count2)] = float (sv_dict[i][-1][-1])
count2 += 1
count1 = 0
count2 = 2
for i in sv_dict:
sh = wb.create_sheet (i)
sh = wb.get_sheet_by_name (i)
f = sh['a1']
f.font = Font (bold=True)
f = sh['b1']
f.font = Font (bold=True)
f = sh['c1']
f.font = Font (bold=True)
f = sh['d1']
f.font = Font (bold=True)
sh['a1'] = 'LunID'
sh['b1'] = 'Name'
sh['c1'] = 'VPD'
sh['d1'] = 'Size(G/T)'
for j in range (len (sv_dict[i])):
for k in range (4):
sh[alph[count1] + str (count2)] = sv_dict[i][j][k]
count1 += 1
count2 += 1
count1 = 0
count2 = 2
logging.debug('Start of chart')
l = len(sv_dict)
sh = wb.get_sheet_by_name ('HighLevel')
logging.debug('sheets: %s' % (wb.get_sheet_names ()))
logging.debug('sh: %s' % (sh.title))
chart1 = BarChart()
chart1.type = "col"
chart1.style = 11
chart1.title = "VPlex Capacity Report"
chart1.y_axis.title = 'Size'
chart1.x_axis.title = 'View Name'
logging.debug('len of sv_dict: %d' % (l))
data = Reference(sh, min_col=2, min_row=2, max_row=l + 1, max_col=2)
cats = Reference(sh, min_col=1, min_row=2, max_row=l + 1)
chart1.add_data(data, titles_from_data=False)
chart1.set_categories(cats)
chart1.top = 100
chart1.left = 30
chart1.width = 27
chart1.height = 10
chart1.shape = sh.add_chart(chart1, "D2")
wb.save (ClusName)
return 0
import openpyxl
from openpyxl.chart import BarChart, Reference
wb = openpyxl.load_workbook(r'文件操作\2-工资处理\工资统计new.xlsx')
sheet = wb['工作量汇总']
last = sheet.max_row + 1
new_wb = openpyxl.Workbook()
new_sheet = new_wb.active
new_sheet['A1'] = '教师姓名'
new_sheet['B1'] = '工作量工资'
for index, values in enumerate(sheet.rows, 1):
if 1 < index < sheet.max_row - 1:
lst = list(map(lambda x: x.value, values))
new_sheet.cell(row=index, column=1).value = lst[1]
new_sheet.cell(row=index, column=2).value = lst[5]
chart = BarChart()
chart.title = '工资图表'
chart.y_axis.title = '工资'
chart.x_axis.title = '姓名'
data = Reference(new_sheet, min_col=2, min_row=1, max_row=new_sheet.max_row)
cats = Reference(new_sheet, min_col=1, min_row=2, max_row=new_sheet.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
new_sheet.add_chart(chart, 'D2')
new_wb.save(r"文件操作\2-工资处理\工资图表.xlsx")
def ExportToExcel():
h = open('final.txt','r')
h = h.read()
book = openpyxl.Workbook()
sheet1 = book.active
sheet1.cell(column=1,row=1,value='Server')
sheet1.cell(column=2,row=1,value='Consumption')
sheet1.cell(column=3,row=1,value='Output')
sheet1.cell(column=4,row=1,value='Average')
sername = [sername.split()[0] for sername in h.splitlines()]
consump = [float(consump.split()[1].replace(',','')) for consump in h.splitlines()]
output = [int(output.split()[2]) for output in h.splitlines()]
for row in range(len(sername)):
_ = sheet1.cell(column=1, row=row+2, value="%s" %sername[row])
_ = sheet1.cell(column=2, row=row+2, value=consump[row])
_ = sheet1.cell(column=3, row=row+2, value=output[row])
_ = sheet1.cell(column=4, row=row+2, value="=B%d/C%d" %(row+2,row+2))
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Server vs Consumption"
chart1.y_axis.title = 'Consumption'
chart1.x_axis.title = 'Server Name'
data = Reference(sheet1, min_col=2, min_row=1, max_row=len(sername)+1, max_col=3)
cats = Reference(sheet1, min_col=1, min_row=2, max_row=len(sername)+1)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
sheet1.add_chart(chart1, "I1")
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Server vs Consumption"
chart1.y_axis.title = 'Consumption'
chart1.x_axis.title = 'Server Name'
data = Reference(sheet1, min_col=4, min_row=1, max_row=len(sername)+1, max_col=4)
cats = Reference(sheet1, min_col=1, min_row=2, max_row=len(sername)+1)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
sheet1.add_chart(chart1, "I20")
global name
name = "EnergyConsumption_{}.xlsx".format(datetime.datetime.now().date())
book.save(name)
return
# sheet['C5'] = 'Two merged cells.'
# wb.save('merged.xlsx')
# wb = openpyxl.load_workbook('merged.xlsx')
# sheet = wb.active
# sheet.unmerge_cells('A1:D3')
# sheet.unmerge_cells('C5:D5')
# wb.save('merged.xlsx')
# wb = openpyxl.load_workbook('productSales.xlsx')
# sheet = wb.active
# sheet.freeze_panes = 'B3'
# wb.save('freezeExample.xlsx')
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference, Series
wb = Workbook()
sheet = wb.active
for i in range(1, 11):
sheet.append([i])
refObj = Reference(worksheet=sheet,
min_col=1,
min_row=1,
max_col=1,
max_row=10)
chart = BarChart()
chart.add_data(refObj)
sheet.add_chart(chart)
wb.save('SampleChart.xlsx')
class TestChartWriter(object):
def setup(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'data'
for i in range(10):
ws.cell(row=i, column=0).value = i
self.chart = BarChart()
self.chart.title = 'TITLE'
self.chart.add_serie(Serie(Reference(ws, (0, 0), (10, 0))))
self.chart._series[-1].color = Color.GREEN
self.cw = BarChartWriter(self.chart)
self.root = Element('test')
def make_worksheet(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'data'
ws.append(list(range(10)))
return ws
def test_write_title(self):
self.cw._write_title(self.root)
expected = """<?xml version='1.0' ?><test xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:title><c:tx><c:rich><a:bodyPr /><a:lstStyle /><a:p><a:pPr><a:defRPr /></a:pPr><a:r><a:rPr lang="en-GB" /><a:t>TITLE</a:t></a:r></a:p></c:rich></c:tx><c:layout /></c:title></test>"""
xml = get_xml(self.root)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_xaxis(self):
self.cw._write_axis(self.root, self.chart.x_axis, '{%s}catAx' % CHART_NS)
expected = """<?xml version='1.0' ?><test xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:catAx><c:axId val="60871424" /><c:scaling><c:orientation val="minMax" /></c:scaling><c:axPos val="b" /><c:tickLblPos val="nextTo" /><c:crossAx val="60873344" /><c:crosses val="autoZero" /><c:auto val="1" /><c:lblAlgn val="ctr" /><c:lblOffset val="100" /></c:catAx></test>"""
xml = get_xml(self.root)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_yaxis(self):
self.cw._write_axis(self.root, self.chart.y_axis, '{%s}valAx' % CHART_NS)
expected = """<?xml version='1.0' ?><test xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:valAx><c:axId val="60873344" /><c:scaling><c:orientation val="minMax" /><c:max val="10.0" /><c:min val="0.0" /></c:scaling><c:axPos val="l" /><c:majorGridlines /><c:numFmt formatCode="General" sourceLinked="1" /><c:tickLblPos val="nextTo" /><c:crossAx val="60871424" /><c:crosses val="autoZero" /><c:crossBetween val="between" /><c:majorUnit val="2.0" /></c:valAx></test>"""
xml = get_xml(self.root)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_series(self):
self.cw._write_series(self.root)
expected = """<?xml version='1.0' ?><test xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:ser><c:idx val="0" /><c:order val="0" /><c:spPr><a:solidFill><a:srgbClr val="00FF00" /></a:solidFill><a:ln><a:solidFill><a:srgbClr val="00FF00" /></a:solidFill></a:ln></c:spPr><c:val><c:numRef><c:f>\'data\'!$A$1:$A$11</c:f><c:numCache><c:formatCode>General</c:formatCode><c:ptCount val="11" /><c:pt idx="0"><c:v>0</c:v></c:pt><c:pt idx="1"><c:v>1</c:v></c:pt><c:pt idx="2"><c:v>2</c:v></c:pt><c:pt idx="3"><c:v>3</c:v></c:pt><c:pt idx="4"><c:v>4</c:v></c:pt><c:pt idx="5"><c:v>5</c:v></c:pt><c:pt idx="6"><c:v>6</c:v></c:pt><c:pt idx="7"><c:v>7</c:v></c:pt><c:pt idx="8"><c:v>8</c:v></c:pt><c:pt idx="9"><c:v>9</c:v></c:pt><c:pt idx="10"><c:v>None</c:v></c:pt></c:numCache></c:numRef></c:val></c:ser></test>"""
xml = get_xml(self.root)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_legend(self):
self.cw._write_legend(self.root)
expected = """<?xml version='1.0' ?><test xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:legend><c:legendPos val="r" /><c:layout /></c:legend></test>"""
xml = get_xml(self.root)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_no_write_legend(self):
self.chart.show_legend = False
self.cw._write_legend(self.root)
children = [e for e in self.root]
eq_(len(children), 0)
def test_write_print_settings(self):
tagnames = ['test',
'{%s}printSettings' % CHART_NS,
'{%s}headerFooter' % CHART_NS,
'{%s}pageMargins' % CHART_NS,
'{%s}pageSetup' % CHART_NS]
for e in self.root:
assert_true(e.tag in tagnames)
if e.tag == "{%s}pageMargins" % CHART_NS:
eq_(e.keys(), list(self.chart.print_margins.keys()))
for k, v in e.items():
eq_(float(v), self.chart.print_margins[k])
else:
eq_(e.text, None)
eq_(e.attrib, {})
def test_write_chart(self):
root = Element('{%s}chartSpace' % CHART_NS)
self.cw._write_chart(root)
tree = fromstring(get_xml(root))
assert_true(chart_schema.validate(tree))
expected = """<?xml version='1.0' ?><c:chartSpace xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:chart><c:title><c:tx><c:rich><a:bodyPr /><a:lstStyle /><a:p><a:pPr><a:defRPr /></a:pPr><a:r><a:rPr lang="en-GB" /><a:t>TITLE</a:t></a:r></a:p></c:rich></c:tx><c:layout /></c:title><c:plotArea><c:layout><c:manualLayout><c:layoutTarget val="inner" /><c:xMode val="edge" /><c:yMode val="edge" /><c:x val="0.03375" /><c:y val="0.31" /><c:w val="0.6" /><c:h val="0.6" /></c:manualLayout></c:layout><c:barChart><c:barDir val="col" /><c:grouping val="clustered" /><c:ser><c:idx val="0" /><c:order val="0" /><c:spPr><a:solidFill><a:srgbClr val="00FF00" /></a:solidFill><a:ln><a:solidFill><a:srgbClr val="00FF00" /></a:solidFill></a:ln></c:spPr><c:val><c:numRef><c:f>'data'!$A$1:$A$11</c:f><c:numCache><c:formatCode>General</c:formatCode><c:ptCount val="11" /><c:pt idx="0"><c:v>0</c:v></c:pt><c:pt idx="1"><c:v>1</c:v></c:pt><c:pt idx="2"><c:v>2</c:v></c:pt><c:pt idx="3"><c:v>3</c:v></c:pt><c:pt idx="4"><c:v>4</c:v></c:pt><c:pt idx="5"><c:v>5</c:v></c:pt><c:pt idx="6"><c:v>6</c:v></c:pt><c:pt idx="7"><c:v>7</c:v></c:pt><c:pt idx="8"><c:v>8</c:v></c:pt><c:pt idx="9"><c:v>9</c:v></c:pt><c:pt idx="10"><c:v>None</c:v></c:pt></c:numCache></c:numRef></c:val></c:ser><c:axId val="60871424" /><c:axId val="60873344" /></c:barChart><c:catAx><c:axId val="60871424" /><c:scaling><c:orientation val="minMax" /></c:scaling><c:axPos val="b" /><c:tickLblPos val="nextTo" /><c:crossAx val="60873344" /><c:crosses val="autoZero" /><c:auto val="1" /><c:lblAlgn val="ctr" /><c:lblOffset val="100" /></c:catAx><c:valAx><c:axId val="60873344" /><c:scaling><c:orientation val="minMax" /><c:max val="10.0" /><c:min val="0.0" /></c:scaling><c:axPos val="l" /><c:majorGridlines /><c:numFmt formatCode="General" sourceLinked="1" /><c:tickLblPos val="nextTo" /><c:crossAx val="60871424" /><c:crosses val="autoZero" /><c:crossBetween val="between" /><c:majorUnit val="2.0" /></c:valAx></c:plotArea><c:legend><c:legendPos val="r" /><c:layout /></c:legend><c:plotVisOnly val="1" /></c:chart></c:chartSpace>"""
xml = get_xml(root)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_rels(self):
xml = self.cw.write_rels(1)
expected = """<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/chartUserShapes" Target="../drawings/drawing1.xml"/></Relationships>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_no_ascii(self):
ws = self.make_worksheet()
ws.append(["D\xc3\xbcsseldorf"]*10)
serie = Serie(values=Reference(ws, (0,0), (0,9)),
legend=Reference(ws, (1,0), (1,9))
)
c = BarChart()
c.add_serie(serie)
cw = ChartWriter(c)
def test_label_no_number_format(self):
ws = self.make_worksheet()
for i in range(10):
ws.append([i, i])
labels = Reference(ws, (0,0), (0,9))
values = Reference(ws, (0,0), (0,9))
serie = Serie(values=values, labels=labels)
c = BarChart()
c.add_serie(serie)
cw = BarChartWriter(c)
root = Element('test')
cw._write_serial(root, c._series[0].labels)
expected = """<?xml version='1.0' ?><test xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:numRef><c:f>'data'!$A$1:$J$1</c:f><c:numCache><c:formatCode>General</c:formatCode><c:ptCount val="10" /><c:pt idx="0"><c:v>0</c:v></c:pt><c:pt idx="1"><c:v>1</c:v></c:pt><c:pt idx="2"><c:v>2</c:v></c:pt><c:pt idx="3"><c:v>3</c:v></c:pt><c:pt idx="4"><c:v>4</c:v></c:pt><c:pt idx="5"><c:v>5</c:v></c:pt><c:pt idx="6"><c:v>6</c:v></c:pt><c:pt idx="7"><c:v>7</c:v></c:pt><c:pt idx="8"><c:v>8</c:v></c:pt><c:pt idx="9"><c:v>9</c:v></c:pt></c:numCache></c:numRef></test>"""
xml = get_xml(root)
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_label_number_format(self):
ws = self.make_worksheet()
for i in range(10):
ws.append([i, i])
labels = Reference(ws, (0,0), (0,9))
labels.number_format = 'd-mmm'
values = Reference(ws, (0,0), (0,9))
serie = Serie(values=values, labels=labels)
c = BarChart()
c.add_serie(serie)
cw = BarChartWriter(c)
root = Element('test')
cw._write_serial(root, c._series[0].labels)
expected = """<?xml version='1.0' ?><test xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:numRef><c:f>'data'!$A$1:$J$1</c:f><c:numCache><c:formatCode>d-mmm</c:formatCode><c:ptCount val="10" /><c:pt idx="0"><c:v>0</c:v></c:pt><c:pt idx="1"><c:v>1</c:v></c:pt><c:pt idx="2"><c:v>2</c:v></c:pt><c:pt idx="3"><c:v>3</c:v></c:pt><c:pt idx="4"><c:v>4</c:v></c:pt><c:pt idx="5"><c:v>5</c:v></c:pt><c:pt idx="6"><c:v>6</c:v></c:pt><c:pt idx="7"><c:v>7</c:v></c:pt><c:pt idx="8"><c:v>8</c:v></c:pt><c:pt idx="9"><c:v>9</c:v></c:pt></c:numCache></c:numRef></test>"""
xml = get_xml(root)
diff = compare_xml(xml, expected)
assert diff is None, diff
cell = sheet["a1"] #所在表格頁中，你所選定的[欄]為名稱
cell = sheet.cell(1, 1)
print(cell.value) #這是選定欄為的內容名稱
print(sheet.max_row) #這是欄(直的)位在Excel的總欄數
for row in range(1, sheet.max_row + 1): #用for方法算出第一欄的欄數
print(row)
for row in range(2, sheet.max_row + 1): #用for方法找出第三欄的值
cell_2 = sheet.cell(row, 3)
print(cell_2.value)
for row in range(2, sheet.max_row + 1): #算出第四欄的欄位數值並另存新黨
cell = sheet.cell(row, 3)
corrected_price = cell.value * 0.9
correct_price_cell = sheet.cell(row, 4)
correct_price_cell.value = corrected_price
values = Reference(
sheet, #給出做圖表的參照範圍
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = BarChart() #開始做出圖表
chart.add_data(values)
sheet.add_chart(chart, "E2") #在這要給出想要做出圖表的位置
wb.save("transactions2.xlsx")
Reference,
Series,
)
wb = Workbook()
ws = wb.active
rows = [
['Aliens', 2, 3, 4, 5, 6, 7],
['Humans', 10, 40, 50, 20, 10, 50],
]
for row in rows:
ws.append(row)
c1 = BarChart()
v1 = Reference(ws, min_col=1, min_row=1, max_col=7)
c1.add_data(v1, titles_from_data=True, from_rows=True)
c1.x_axis.title = 'Days'
c1.y_axis.title = 'Aliens'
c1.y_axis.majorGridlines = None
c1.title = 'Survey results'
# Create a second chart
c2 = LineChart()
v2 = Reference(ws, min_col=1, min_row=2, max_col=7)
c2.add_data(v2, titles_from_data=True, from_rows=True)
c2.y_axis.axId = 200
c2.y_axis.title = "Humans"
def createGraph(excel_file, df):
sheet_name = "Data"
sheet_name_graphs = "Graphs"
writer = pd.ExcelWriter(excel_file, engine="openpyxl")
book = load_workbook(excel_file)
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
ws_graph = writer.book.create_sheet(sheet_name_graphs)
df.to_excel(writer, sheet_name=sheet_name)
ws = writer.sheets[sheet_name]
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "ETL Results"
chart1.y_axis.title = "Count"
chart1.x_axis.title = "Programm"
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
chart1.height = 12
chart1.width = 20
ws_graph.add_chart(chart1, "A10")
writer.save()
from openpyxl import Workbook
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),
(3, 40, 60),
(4, 50, 70),
(5, 20, 10),
(6, 10, 40),
(7, 50, 30),
]
for row in rows:
ws.append(row)
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "Bar Chart"
chart.y_axis.title = 'Test number'
chart.x_axis.title = 'Sample length (mm)'
xData = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
yData = Reference(ws, min_col=1, min_row=2, max_row=7)
chart.add_data(xData, titles_from_data=True)
chart.set_categories(yData)
chart.shape = 4
ws.add_chart(chart, "B10")
wb.save('data/barCharts.xlsx')
import openpyxl
from openpyxl.chart import Reference, Series, BarChart
wb = openpyxl.Workbook()
sheet = wb.active
for i in range(1, 11): # create some data in column A
sheet['A' + str(i)] = i
refObj = Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=10)
seriesObj = Series(refObj, title='First series')
chartObj = BarChart()
chartObj.append(seriesObj)
'''
chartObj.drawing.top = 50
chartObj.drawing.left = 100
chartObj.drawing.width = 300
chartObj.drawing.height = 200
'''
sheet.add_chart(chartObj, 'C2')
wb.save('sampleChart.xlsx')
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 generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
# todo
ws = wb.active
# Row height
ws.row_dimensions[1].height = 118
for i in range(2, 11 + 1):
ws.row_dimensions[i].height = 30
for i in range(12, 43 + 1):
ws.row_dimensions[i].height = 30
# Col width
ws.column_dimensions['A'].width = 1.5
for i in range(ord('B'), ord('I')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000'))
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
ws.add_image(img, 'B1')
# Title
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws.merge_cells("G3:H3")
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
if "reporting_period" not in report.keys() or \
"values" not in report['reporting_period'].keys() or len(report['reporting_period']['values']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
###############################
has_cost_data_flag = True
if "values" not in report['reporting_period'].keys() or len(
report['reporting_period']['values']) == 0:
has_cost_data_flag = False
if has_cost_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + '报告期消耗'
reporting_period_data = report['reporting_period']
category = report['offline_meter']['energy_category_name']
ca_len = len(category)
ws['B7'].fill = table_fill
ws['B8'].font = title_font
ws['B8'].alignment = c_c_alignment
ws['B8'] = '能耗'
ws['B8'].border = f_border
ws['B9'].font = title_font
ws['B9'].alignment = c_c_alignment
ws['B9'] = '环比'
ws['B9'].border = f_border
col = 'B'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + '7'].fill = table_fill
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = report['offline_meter']['energy_category_name'] + \
" (" + report['offline_meter']['unit_of_measure'] + ")"
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = round(reporting_period_data['total_in_category'],
0)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate'] is not None else "-"
ws[col + '9'].border = f_border
# TCE TCO2E
end_col = col
# TCE
tce_col = chr(ord(end_col) + 1)
ws[tce_col + '7'].fill = table_fill
ws[tce_col + '7'].font = name_font
ws[tce_col + '7'].alignment = c_c_alignment
ws[tce_col + '7'] = "TCE"
ws[tce_col + '7'].border = f_border
ws[tce_col + '8'].font = name_font
ws[tce_col + '8'].alignment = c_c_alignment
ws[tce_col + '8'] = round(reporting_period_data['total_in_kgce'], 0)
ws[tce_col + '8'].border = f_border
ws[tce_col + '9'].font = name_font
ws[tce_col + '9'].alignment = c_c_alignment
ws[tce_col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate'] is not None else "-"
ws[tce_col + '9'].border = f_border
# TCO2E
tco2e_col = chr(ord(end_col) + 2)
ws[tco2e_col + '7'].fill = table_fill
ws[tco2e_col + '7'].font = name_font
ws[tco2e_col + '7'].alignment = c_c_alignment
ws[tco2e_col + '7'] = "TCO2E"
ws[tco2e_col + '7'].border = f_border
ws[tco2e_col + '8'].font = name_font
ws[tco2e_col + '8'].alignment = c_c_alignment
ws[tco2e_col + '8'] = round(reporting_period_data['total_in_kgco2e'],
0)
ws[tco2e_col + '8'].border = f_border
ws[tco2e_col + '9'].font = name_font
ws[tco2e_col + '9'].alignment = c_c_alignment
ws[tco2e_col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate'] is not None else "-"
ws[tco2e_col + '9'].border = f_border
else:
for i in range(6, 9 + 1):
ws.rows_dimensions[i].height = 0.1
######################################
has_cost_detail_flag = True
reporting_period_data = report['reporting_period']
category = report['offline_meter']['energy_category_name']
ca_len = len(category)
times = reporting_period_data['timestamps']
if "values" not in reporting_period_data.keys() or len(
reporting_period_data['values']) == 0:
has_cost_detail_flag = False
if has_cost_detail_flag:
ws['B11'].font = title_font
ws['B11'] = name + '详细数据'
ws['B18'].fill = table_fill
ws['B18'].font = title_font
ws['B18'].border = f_border
ws['B18'].alignment = c_c_alignment
ws['B18'] = '日期时间'
time = times
has_data = False
max_row = 0
if len(time) > 0:
has_data = True
max_row = 18 + len(time)
if has_data:
end_data_flag = 19
for i in range(0, len(time)):
col = 'B'
end_data_flag = 19 + i
row = str(end_data_flag)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = time[i]
ws[col + row].border = f_border
ws['B' + str(end_data_flag + 1)].font = title_font
ws['B' + str(end_data_flag + 1)].alignment = c_c_alignment
ws['B' + str(end_data_flag + 1)] = '总计'
ws['B' + str(end_data_flag + 1)].border = f_border
bar = BarChart()
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + '18'].fill = table_fill
ws[col + '18'].font = title_font
ws[col + '18'].alignment = c_c_alignment
ws[col + '18'] = report['offline_meter']['energy_category_name'] + \
" (" + report['offline_meter']['unit_of_measure'] + ")"
ws[col + '18'].border = f_border
time = times
time_len = len(time)
for j in range(0, time_len):
row = str(19 + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(reporting_period_data['values'][j],
0)
ws[col + row].border = f_border
ws[col + str(end_data_flag + 1)].font = title_font
ws[col + str(end_data_flag + 1)].alignment = c_c_alignment
ws[col + str(end_data_flag + 1)] = round(
reporting_period_data['total_in_category'], 0)
ws[col + str(end_data_flag + 1)].border = f_border
bar_data = Reference(ws,
min_col=3 + i,
min_row=18,
max_row=max_row)
bar.series.append(Series(bar_data, title_from_data=True))
labels = Reference(ws, min_col=2, min_row=19, max_row=max_row)
bar.set_categories(labels)
bar.dLbls = DataLabelList()
bar.dLbls.showVal = True
bar.height = 5.25
bar.width = len(time)
ws.add_chart(bar, "B12")
else:
for i in range(11, 43 + 1):
ws.row_dimensions[i].height = 0.0
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
