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