def get_chart(): "Returns schatter chart for example" chart = ScatterChart() chart.height = 10 chart.width = 15 chart.style = 2 chart.x_axis.title = "X" chart.y_axis.title = "Y" return chart
def scatter(wb): ws = wb.create_sheet(6, "Scatter") for i in range(10): ws.append([i, i]) chart = ScatterChart() xvalues = Reference(ws, (0, 1), (9, 1)) values = Reference(ws, (0, 0), (9, 0)) series = Series(values, xvalues=xvalues) chart.append(series) ws.add_chart(chart)
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 ws.cell(row=i, column=1).value = i self.scatterchart = ScatterChart() self.scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)), xvalues=Reference(ws, (0, 1), (10, 1)))) self.cw = ChartWriter(self.scatterchart) self.root = Element('test')
def _create_chart(worksheet): """Create the f*****g chart""" chart = ScatterChart() chart.varyColors = True chart.title = "Financial Analysis" chart.style = 1 chart.height = 10 chart.width = 20 chart.x_axis.title = "Financial Quarter" chart.y_axis.title = "Cost" chart.legend = None chart.x_axis.majorUnit = 0.5 chart.x_axis.minorGridlines = None # chart.y_axis.majorUnit = 200 xvalues = Reference(worksheet, min_col=1, min_row=3, max_row=6) picker = _color_gen() for i in range(2, 7): values = Reference(worksheet, min_col=i, min_row=2, max_row=6) series = Series(values, xvalues, title_from_data=True) series.smooth = True series.marker.symbol = "circle" line_prop = LineProperties(solidFill=next(picker)) series.graphicalProperties.line = line_prop chart.series.append(series) worksheet.add_chart(chart, "G1") return worksheet
def format_graph(chart_data, multiple=None): wb = load_workbook(chart_data) ws = wb['Graph'] # dimensions of the excel data ===> A1:D6 table_dimension = ws.dimensions #split dimensions to [A1, D6] table_dimension = table_dimension.split(':') print(table_dimension) col_row_list = [] #do this to split table_dimensions into columns and rows ===> [A, 1, D, 6] for col_row in table_dimension: for x in col_row: if x.isnumeric(): ind = col_row.index(x) col_row_list.append(col_row[:ind]) col_row_list.append(col_row[ind:]) break print(col_row_list) #use the ord method to convert letters to numbers ==> [A, 1, D, 6] = [1, 1, 4, 6] y = [ord(i.lower()) - 96 if i.isalpha() else int(i) for i in col_row_list] min_column = y[0] + 1 min_row = y[1] max_column = y[2] max_row = y[3] chart = ScatterChart() chart.title = "Graph" chart.style = 2 chart.x_axis.title = 'Distance' chart.y_axis.title = 'RSSI' x_data = Reference(ws, min_col=min_column, min_row=min_row + 1, max_row=max_row) for i in range(min_column + 1, max_column + 1): values = Reference(ws, min_col=i, min_row=min_row, max_row=max_row) series = Series(values, x_data, title_from_data=True) chart.series.append(series) ws.add_chart(chart, "G10") wb.save(chart_data)
class TestScatterChartWriter(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 ws.cell(row=i, column=1).value = i self.scatterchart = ScatterChart() self.scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)), xvalues=Reference(ws, (0, 1), (10, 1)))) self.cw = ChartWriter(self.scatterchart) self.root = Element('test') def test_write_xaxis(self): self.scatterchart.x_axis.title = 'test x axis title' self.cw._write_axis(self.root, self.scatterchart.x_axis, 'c:valAx') eq_(get_xml(self.root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:valAx><c:axId val="60871424" /><c:scaling><c:orientation val="minMax" /><c:max val="10.0" /><c:min val="0.0" /></c:scaling><c:axPos val="b" /><c:majorGridlines /><c:numFmt formatCode="General" sourceLinked="1" /><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>test x axis title</a:t></a:r></a:p></c:rich></c:tx><c:layout /></c:title><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:crossBetween val="midCat" /><c:majorUnit val="2.0" /></c:valAx></test>') def test_write_yaxis(self): self.scatterchart.y_axis.title = 'test y axis title' self.cw._write_axis(self.root, self.scatterchart.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.0" /></c:scaling><c:axPos val="l" /><c:majorGridlines /><c:numFmt formatCode="General" sourceLinked="1" /><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>test y axis title</a:t></a:r></a:p></c:rich></c:tx><c:layout /></c:title><c:tickLblPos val="nextTo" /><c:crossAx val="60871424" /><c:crosses val="autoZero" /><c:crossBetween val="midCat" /><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:marker><c:symbol val="none" /></c:marker><c:xVal><c:numRef><c:f>\'data\'!$B$1:$B$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:xVal><c:yVal><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:yVal></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) # Truncate floats because results differ with Python >= 3.2 and <= 3.1 test_xml = sub('([0-9][.][0-9]{4})[0-9]*', '\\1', get_xml(self.root)) eq_(test_xml, '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test><c:chart><c:plotArea><c:layout><c:manualLayout><c:layoutTarget val="inner" /><c:xMode val="edge" /><c:yMode val="edge" /><c:x val="1.2857" /><c:y val="0.2125" /><c:w val="0.6" /><c:h val="0.6" /></c:manualLayout></c:layout><c:scatterChart><c:scatterStyle val="lineMarker" /><c:ser><c:idx val="0" /><c:order val="0" /><c:marker><c:symbol val="none" /></c:marker><c:xVal><c:numRef><c:f>\'data\'!$B$1:$B$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:xVal><c:yVal><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:yVal></c:ser><c:marker val="1" /><c:axId val="60871424" /><c:axId val="60873344" /></c:scatterChart><c:valAx><c:axId val="60871424" /><c:scaling><c:orientation val="minMax" /><c:max val="10.0" /><c:min val="0.0" /></c:scaling><c:axPos val="b" /><c:majorGridlines /><c:numFmt formatCode="General" sourceLinked="1" /><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:crossBetween val="midCat" /><c:majorUnit val="2.0" /></c:valAx><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="midCat" /><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 export(): print("in export") # get all data restaurant_list = Restaurant.query.all() # print(restaurant_list) # create the excel workbook wb = Workbook() sheet = wb.active style_headline = 'Headline 1' style_data = 'Headline 1' # header sheet.cell(row=1, column=1).value = 'Bill' sheet.cell(row=1, column=1).style = style_headline sheet.cell(row=1, column=2).value = 'Tip' sheet.cell(row=1, column=2).style = style_headline # data row_index = 2 for restaurant in restaurant_list: sheet.cell(row=row_index, column=1).value = restaurant.bill sheet.cell(row=row_index, column=1).style = style_data sheet.cell(row=row_index, column=2).value = restaurant.tip sheet.cell(row=row_index, column=2).style = style_data row_index += 1 # add chart chart = ScatterChart() chart.title = "Scatter Chart" chart.style = 13 chart.x_axis.title = 'Bill Amount' chart.y_axis.title = 'Tip Amount' xvalues = Reference(sheet, min_col=1, min_row=2, max_row=1001) for i in range(2, 3): values = Reference(sheet, min_col=i, min_row=1, max_row=1001) series = Series(values, xvalues, title_from_data=True) chart.series.append(series) sheet.add_chart(chart, "D1") filename = "restaurant.xlsx" full_path = os.path.join(app.config['UPLOAD_FOLDER'], filename) print("full_path", full_path) # save the workbook wb.save(full_path) return send_file(full_path, as_attachment=True)
def test_no_write_legend(self): wb = Workbook() ws = wb.get_active_sheet() ws.title = 'data' for i in range(10): ws.cell(row=i, column=0).value = i ws.cell(row=i, column=1).value = i scatterchart = ScatterChart() scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)), xvalues=Reference(ws, (0, 1), (10, 1)))) cw = ChartWriter(scatterchart) root = Element('test') scatterchart.show_legend = False cw._write_legend(root) eq_(get_xml(root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test />')
def gen_workbook(input_file_or_dir,output_file): wb = Workbook() info_token=plot_state() if os.path.isfile(input_file_or_dir): files=[input_file_or_dir] if os.path.isdir(input_file_or_dir): files=glob.glob(os.path.join(input_file_or_dir,"*.dat")) else: return for my_file in files: print("about to save1",my_file) if plot_load_info(info_token,my_file)==True: x=[] y=[] z=[] data=dat_file() if dat_file_read(data,my_file)==True: print("read",my_file) ws = wb.create_sheet(title=title_truncate(os.path.basename(my_file))) ws.cell(column=1, row=1, value=info_token.title) ws.cell(column=1, row=2, value=info_token.x_label+" ("+info_token.x_units+") ") ws.cell(column=2, row=2, value=info_token.y_label+" ("+info_token.y_units+") ") for i in range(0,data.y_len): ws.cell(column=1, row=i+3, value=data.y_scale[i]) ws.cell(column=2, row=i+3, value=data.data[0][0][i]) c1 = ScatterChart() c1.title = info_token.title c1.style = 13 c1.height=20 c1.width=20 c1.y_axis.title = info_token.y_label+" ("+info_token.y_units+") " c1.x_axis.title = info_token.x_label+" ("+info_token.x_units+") " xdata = Reference(ws, min_col=1, min_row=3, max_row=3+data.y_len) ydata = Reference(ws, min_col=2, min_row=3, max_row=3+data.y_len) series = Series(ydata,xdata, title_from_data=True) c1.series.append(series) ws.add_chart(c1, "G4") print("about to save1") wb.save(filename = output_file) print("about to save0")
def graph_bake(self, parameters: BakingGraphParameters): last_row = parameters.num_rows + 1 start_row = 2 start_column = 2 x_axis_title = "{} Time from start (hr)".format(u"\u0394") y_axis_title = "{} Wavelength (pm)".format(u"\u0394") y_axis_title_sensitivity = "Drift (mK)" y_axis_title_temperature = "{} Temperature (K)".format(u"\u0394") x_values = Reference(parameters.data_sheet, min_col=start_column, min_row=start_row, max_row=last_row) for i, fbg_name in enumerate(self.fbg_names): chart_title = "{} {} Wavelength (pm) vs. {} Time from start ; {}"\ .format(fbg_name, u"\u0394", u"\u0394", self.excel_file_name) chart = ScatterChart() y_values = Reference(parameters.data_sheet, min_col=self._get_baking_wavelength_column(i), min_row=start_row, max_row=last_row) series = self.create_series_bake(x_values, y_values, i) chart.series.append(series) trend_values = Reference(parameters.data_sheet, min_col=parameters.trend_line_indexes[i] + 1, min_row=start_row, max_row=last_row) series = Series(trend_values, x_values, title="Trend (pm)") chart.series.append(series) format_chart(chart, x_axis_title, y_axis_title, chart_title) parameters.chart_sheet.add_chart(chart, "B{}".format(30*i + 2)) if self._valid_sensitivities(): sensitivity_chart_title = "{} drift (mK) vs. {} Time from start; {}"\ .format(fbg_name, u"\u0394", self.excel_file_name) sensitivity_chart = ScatterChart() sensitivity_values = Reference(parameters.data_sheet, min_col=parameters.sensitivity_indexes[i] + 1, min_row=start_row, max_row=last_row) series = Series(sensitivity_values, x_values, title="Drift (mK)") sensitivity_chart.series.append(series) format_chart(sensitivity_chart, x_axis_title, y_axis_title_sensitivity, sensitivity_chart_title) parameters.chart_sheet.add_chart(sensitivity_chart, "V{}".format(30 * i + 2)) temperature_graph_start_column = "AO" if self._valid_sensitivities() else "V" chart_title = "{0} Temperature vs. {0} Time from start; {1}".format(u"\u0394", self.excel_file_name) chart = ScatterChart() values = Reference(parameters.data_sheet, min_col=2, min_row=start_row, max_row=last_row) series = Series(values, x_values, title="{} Temperature (K)".format(u"\u0394")) chart.series.append(series) format_chart(chart, x_axis_title, y_axis_title_temperature, chart_title) parameters.chart_sheet.add_chart(chart, "{}2".format(temperature_graph_start_column))
def main(): wb = openpyxl.Workbook() start_generator = _start_cells() segment_series_generator = _segment_series() for p in range(1, 31): proj_num, st_row = _row_calc(p) wb = milestone_swimlane(st_row, proj_num, wb, block_start_row=90, interested_range=365)[0] chart = ScatterChart() chart.title = "Swimlane Chart" chart.style = 1 chart.x_axis.title = 'Days from Today' chart.y_axis.title = 'Project No' derived_end = 2 for p in range(1, NUMBER_OF_PROJECTS): for i in range( 1, 8 ): # 8 here is hard-coded number of segments within a project series (ref: dict in _segment_series() if i == 1: inner_start_row = derived_end else: inner_start_row = derived_end _inner_step = next(segment_series_generator)[1] series, derived_end = _series_producer(wb.active, inner_start_row, _inner_step) if _inner_step == 1: series.marker.symbol = "triangle" series.marker.graphicalProperties.solidFill = "01a852" else: series.marker.symbol = "square" series.marker.graphicalProperties.solidFill = "FF0000" series.marker.size = 10 chart.series.append(series) start_generator = _start_cells() segment_series_generator = _segment_series() derived_end = derived_end + 1 wb.active.add_chart(chart, "E1") wb.save(os.path.join(DESKTOP, 'output.xlsx'))
def average_se_trace_full_experiment_chart(file_max_column, file_max_row, sheet): # average_se_trace_full_experiment_chart generates a plot with the average values from the average_se_trace_full_experiment function. chart_cell = sheet.cell(row=4, column=file_max_column + 7).coordinate chart = ScatterChart() chart.style = 2 chart.title = "Experiment average trace" chart.y_axis.title = "Fura2 fluorescence ratio (a.u)" chart.x_axis.title = "Time (s)" chart.legend = None chart.height = 10 # default is 7.5 chart.width = 20 # default is 15 chart.x_axis.majorUnit = 60 ca_ex_st.style_chart(chart.title, chart) xvalues = Reference(sheet, min_col=file_max_column + 3, min_row=3, max_col=file_max_column + 3, max_row=file_max_row) yvalues = Reference(sheet, min_col=file_max_column + 4, min_row=3, max_col=file_max_column + 4, max_row=file_max_row) series = Series(yvalues, xvalues) series_trendline = Series(yvalues, xvalues) chart.series.append(series) chart.series.append(series_trendline) sheet.add_chart(chart, chart_cell)
def plot_scatter_chart(): wb = xl.load_workbook('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx') # load xl workbook sheet = wb['dATA'] # get the worksheet for i in range(2, 10): cell = sheet['f'+str(i)] sheet['a'+str(i)] = cell.value values = Reference( sheet, min_row=2, max_row=10, min_col=1, max_col=1 ) series_object = xl.chart.Series(values, title="Yearly Revenue") chart = ScatterChart() chart.append(series_object) sheet.add_chart(chart, 'j2') wb.save('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx')
def _chart2excel(writer, sheet, charts): import xlrd from openpyxl.chart import ScatterChart, Series sn = writer.book.sheetnames named_ranges = { '%s!%s' % (sn[d.localSheetId], d.name): d.value for d in writer.book.defined_names.definedName } m, h, w = 3, 7.94, 13.55 for i, (k, v) in enumerate(sorted(charts.items())): chart = ScatterChart() chart.height = h chart.width = w _map = { ('title', 'name'): ('title', ), ('y_axis', 'name'): ('y_axis', 'title'), ('x_axis', 'name'): ('x_axis', 'title'), } _filter = { ('legend', 'position'): lambda x: x[0], } it = { s: _filter[s](o) if s in _filter else o for s, o in sh.stack_nested_keys(v['set']) } for s, o in sh.map_dict(_map, it).items(): c = chart for j in s[:-1]: c = getattr(c, j) setattr(c, s[-1], o) for s in v['series']: xvalues = named_ranges[_data_ref(s['x'])] values = named_ranges[_data_ref(s['y'])] series = Series(values, xvalues, title=s['label']) chart.series.append(series) n = int(i / m) j = i - n * m sheet.add_chart(chart, xlrd.cellname(15 * j, 8 * n))
def setup(self): wb = Workbook() ws = wb.get_active_sheet() ws.title = "Scatter" for i in range(10): ws.cell(row=i, column=0).value = i ws.cell(row=i, column=1).value = i self.scatterchart = ScatterChart() self.scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)), xvalues=Reference(ws, (0, 1), (10, 1)))) self.cw = ScatterChartWriter(self.scatterchart) self.root = Element("test")
def format_chart(chart: ScatterChart, x_axis_title: str, y_axis_title: str, title: str): chart.height = 15 chart.width = 30 chart.x_axis.tickLblPos = "low" chart.title = title chart.x_axis.title = x_axis_title chart.y_axis.title = y_axis_title font = drawing.text.Font(typeface='Arial') cp_axis = CharacterProperties(latin=font, sz=1600, b=True) cp_axis_title = CharacterProperties(latin=font, sz=1600) cp_title = CharacterProperties(latin=font, sz=1200) chart.y_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis), endParaRPr=cp_axis)]) chart.y_axis.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis), endParaRPr=cp_axis_title)]) chart.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis), endParaRPr=cp_axis)]) chart.x_axis.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis), endParaRPr=cp_axis_title)]) chart.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_title), endParaRPr=cp_title)])
def createWorkBook () : wb = Workbook() ws = wb.active for row in data: ws.append(row) chart = ScatterChart() chart.title = "Scatter Chart" chart.style = 13 chart.x_axis.title = 'Size' chart.y_axis.title = 'Percentage' j = 1 valueList = [] for row in ws : for cell in row : chart = ScatterChart() if str(cell.value) == 'from' : xvalues = Reference(ws, min_col = 2, max_col=3, min_row = cell.row +1, max_row= cell.row +1) values = Reference(ws, min_col = 2, max_col=3,min_row= cell.row +2, max_row = cell.row +2) series = Series(values,xvalues , title_from_data=False) valueList.append(series) elif (str(cell.value) == 'title' and cell.row > 1) or str(cell.value) == 'end': for value in valueList : chart.series.append(value) ws.add_chart(chart, "A10") valueList = [] wb.save('test.xlsx')
def write_excel(data, fname): # write data to excel spreadsheet wb = Workbook() ws = wb.active for row in data: ws.append(row) # create chart = ScatterChart() chart.title = "RSSI v. Distance (ft)" chart.style = 1 chart.x_axis.title = "Distance" chart.y_axis.title = "RSSI" x_values = Reference(ws, min_col=1, min_row=1, max_row=len(data)) y_values = Reference(ws, min_col=2, min_row=1, max_row=len(data)) series = Series(y_values, x_values, title_from_data=True) chart.series.append(series) ws.add_chart(chart, "D2") wb.save(fname) print "%s saved!" % (fname)
def plot_inside_excel(): wb = load_workbook(filename=saints_excel_name) # Active WorkSheet ws = wb.active chat1 = ScatterChart() # style = MinMax(allow_none=True, min=1, max=48) # chat1.style = 7 chat1.title = '2020 One Year Bible Study' chat1.x_axis.title = 'Date' chat1.y_axis.title = 'Time(o\'clock)' # set major/minor unit and max value of y axis chat1.y_axis.majorUnit = 1 chat1.y_axis.minorUnit = 1 chat1.y_axis.scaling.max = 24 chat1.y_axis.scaling.min = 0 # enlarge the chart, default is too small # width = 15 # in cm, approx 5 rows # height = 7.5 # in cm, approx 14 rows chat1.height = chat1.height + 8 chat1.width = chat1.width + 32 xvalues = Reference(ws, min_col=2, min_row=2, max_row=ws.max_row) for i in range(len(saint_name_list)): values = Reference(ws, min_col=8 + i, min_row=2, max_row=ws.max_row) series = Series(values, xvalues, title=saint_name_list[i], title_from_data=False) # {'triangle', 'dash', 'x', 'auto', 'diamond', 'circle', 'star', # 'picture', 'square', 'dot', 'plus'} series.marker = openpyxl.chart.marker.Marker('circle') series.graphicalProperties.line.noFill = True chat1.series.append(series) ws.add_chart(chat1, "A10") wb.save(filename=saints_excel_name)
def build_scatter_with_mean_stdev(xvalues_refs, yvalues_refs, stdev_refs, titles, errDir='y'): """Given x values, a list of y values, and the y values' corresponding stdev, it will return a scatter chart with stdev as error bars""" if len(yvalues_refs) != len(stdev_refs) != len(titles): raise ValueError("y-values and stdev list length must be the same") chart = ScatterChart() for xvalues, yvalues, stdev, title in zip(xvalues_refs, yvalues_refs, stdev_refs, titles): # convert reference to data source stdev_data_source = data_source.NumDataSource(numRef=data_source.NumRef(f=stdev)) error_bars = ErrorBars(errDir=errDir, errValType='cust', plus=stdev_data_source, minus=stdev_data_source) series = Series(yvalues, xvalues, title=title) series.errBars = error_bars chart.series.append(series) return chart
def parse_statistics(logfile): xl = pd.ExcelFile(logfile) df = xl.parse("Sheet") df = df.sort_values(by='Line Numbers') writer = pd.ExcelWriter(logfile) df.to_excel(writer, sheet_name='Sheet', index=False) writer.save() wb = openpyxl.load_workbook(logfile) ws = wb.active row_count = ws.max_row column_count = ws.max_column chart = ScatterChart() chart.title = "Time upload domain names" chart.style = 13 chart.x_axis.title = "Line numbers" chart.y_axis.title = "Time, sec" xvalues = Reference(ws, min_col=1, min_row=2, max_row=row_count) color_choice = ['3F888F', 'D24D57'] for i in range(2, column_count + 1): values = Reference(ws, min_col=i, min_row=1, max_row=row_count) series = Series(values, xvalues, title_from_data=True) series.marker.symbol = "diamond" series.graphicalProperties.line.solidFill = color_choice[i - 2] series.marker.graphicalProperties.line.solidFill = color_choice[i - 2] series.marker.graphicalProperties.solidFill = color_choice[i - 2] series.graphicalProperties.line.width = 20000 chart.series.append(series) chart.legend.legendPos = 'b' ws.add_chart(chart) wb.save(logfile)
def createGraph2(disl, disl2): #,disl2,disl3): res = 'Resistencia' efg = 'Ef.Generador' disl = [res] + disl disl2 = [efg] + disl2 for g in range(1): graph2.append(disl) graph2.append(disl2) chart = ScatterChart() chart.title = 'R[Ω] vs Eficiencia Generador' chart.x_axis.title = 'Efm' chart.y_axis.title = 'R[Ω]' xvals = Reference(graph2, min_col=2, min_row=1, max_col=len(disl)) #for s in range(1,len(disl)): #10 values = Reference(graph2, min_col=2, min_row=2, max_col=len(disl)) series = Series(values, xvals, title_from_data=True) chart.series.append(series) #chart.add_data(values) #s = chart.series[1] graph2.add_chart(chart, "M1") wb.save(filesheet)
def parse_statistics(logfile): xl = pd.ExcelFile(logfile) df = xl.parse("Sheet") df = df.sort_values(by='Line Numbers') writer = pd.ExcelWriter(logfile) df.to_excel(writer, sheet_name='Sheet', index=False) writer.save() wb = openpyxl.load_workbook(logfile) ws = wb.active row_count = ws.max_row column_count = ws.max_column chart = ScatterChart() chart.title = "Time upload domain names" chart.style = 13 chart.x_axis.title = "Line numbers" chart.y_axis.title = "Time, sec" xvalues = Reference(ws, min_col=1, min_row=2, max_row=row_count) color_choice = ['3F888F', 'D24D57'] for i in range(2, column_count + 1): values = Reference(ws, min_col=i, min_row=1, max_row=row_count) series = Series(values, xvalues, title_from_data=True) series.marker.symbol = "diamond" series.graphicalProperties.line.solidFill = color_choice[i-2] series.marker.graphicalProperties.line.solidFill = color_choice[i-2] series.marker.graphicalProperties.solidFill = color_choice[i-2] series.graphicalProperties.line.width = 20000 chart.series.append(series) chart.legend.legendPos = 'b' ws.add_chart(chart) wb.save(logfile)
def createEXCEL(fileName, dic): if fileName is None or dic is None: print("Error occurred") return headers = ['x', 'y'] wb = Workbook() ws = wb.active # Create table headers ws.append(headers) # Create ordered table for k, v in sorted(dic.items(), key=operator.itemgetter(0)): ws.append([k, v]) # Center all cels for col in ws.columns: for cell in col: cell.alignment = Alignment(horizontal="center") # Border + background for headers thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) for i in range(len(dic) + 1): for j in range(len(headers)): ws.cell(row=i + 1, column=j + 1).border = thin_border if i == 0: ws.cell(1, j + 1).fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid") # Create graph chart = ScatterChart() chart.title = "LineChart" chart.style = 13 chart.x_axis.title = 'X' chart.y_axis.title = 'Y' chart.legend = None x = Reference(ws, min_col=1, min_row=2, max_col=1, max_row=(len(dic) + 1)) y = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=(len(dic) + 1)) s = Series(y, xvalues=x) chart.append(s) ws.add_chart(chart, "E4") wb.save(fileName + ".xlsx")
def csv_treat(save_path): wb = openpyxl.Workbook() ws_data = wb.create_sheet(title='data_sheet') ws_chart = wb.create_sheet(title='chart_sheet') wb.remove(wb.worksheets[0]) file = glob.glob(save_path + '\\*.csv') chart = ScatterChart() num_shape = 0 for i in file: csv_file = open(i, "r", encoding="ms932", errors="", newline="") f = csv.reader(csv_file, delimiter=",", doublequote=True, lineterminator="\r\n", quotechar='"', skipinitialspace=True) ws_data.cell(1, num_shape * 2 + 1, value="object#{}".format(num_shape)) num_row = 0 for row in f: row1_flt = float(row[0].split("E")[0]) row2_flt = float(row[1].split("E")[0]) ws_data.cell(num_row + 2, num_shape * 2 + 1, value=row1_flt) ws_data.cell(num_row + 2, num_shape * 2 + 2, value=row2_flt) num_row += 1 xvalues = Reference(ws_data, min_col=num_shape * 2 + 1, min_row=2, max_row=num_row) values = Reference(ws_data, min_col=num_shape * 2 + 2, min_row=2, max_row=num_row) series = Series(values, xvalues, title="object#{}".format(num_shape)) chart.series.append(series) print(num_shape) num_shape += 1 ws_chart.add_chart(chart, "A1") wb.save('confirm.xlsx')
def single_cell_slope_trace_chart(column, column_slope_charts, chart_name, row_charts, row_number, sheet, slope_name, slope_time, time_column): # single_cell_slope_trace_chart function generates 1 scatter chart within the file for each of the traces where: # x_axis = slope_time # y_axis = Fura2 fluorescence.# # column_individual_trace_charts: Determines the column where the chart will be created # experiment_number: Used as the chart title. # file_max_row: calculated by any of the analysis functions. # row_individual_trace_charts: Determines the column where the chart will be created chart_cell = sheet.cell(row=row_charts, column=column_slope_charts).coordinate chart = ScatterChart() chart.style = 2 chart.title = f"{chart_name}: {slope_name} slope" chart.y_axis.title = "Fura2 fluorescence ratio (a.u)" chart.x_axis.title = "Time (s)" chart.legend = None chart.height = 7.5 # default is 7.5 chart.width = 15 # default is 15 chart.x_axis.majorUnit = 10 ca_ex_st.style_chart(chart.title, chart) xvalues = Reference(sheet, min_col=time_column, min_row=row_number + 1, max_col=time_column, max_row=row_number + 1 + slope_time) yvalues = Reference(sheet, min_col=column, min_row=row_number + 1, max_col=column, max_row=row_number + 1 + slope_time) series = Series(yvalues, xvalues) series_trendline = Series(yvalues, xvalues) chart.series.append(series) chart.series.append(series_trendline) line = chart.series[0] line.graphicalProperties.line.noFill = True line.trendline = Trendline(dispRSqr=True, dispEq=True) sheet.add_chart(chart, chart_cell)
def _generate_chart(worksheet, top_row: int, leftmost_col: int) -> ScatterChart: chart = ScatterChart() chart.title = "RCF" chart.style = 13 chart.height = 18 chart.width = 28 chart.x_axis.title = "Days" chart.y_axis.title = "Milestone Type" xvalues = Reference(worksheet, min_col=3, min_row=10, max_row=33) yvalues = Reference(worksheet, min_col=4, min_row=10, max_row=33) series = Series(yvalues, xvalues) series.marker.size = 6 chart.series.append(series) return chart
def single_cell_trace_in_individual_chart(column, column_individual_trace_charts, chart_name, file_max_row, sheet, row_individual_trace_charts, time_column): # single_cell_trace_in_individual_chart function generates 1 scatter chart within the file for each of the traces where: # x_axis = time(s) # y_axis = Fura2 fluorescence.# # column_individual_trace_charts: Determines the column where the chart will be created # experiment_number: Used as the chart title. # file_max_row: calculated by any of the analysis functions. # row_individual_trace_charts: Determines the column where the chart will be created # sheet: calculated by any of the analysis functions. # time_column: calculates the maximun column number within the file. chart_cell = sheet.cell(row=row_individual_trace_charts, column=column_individual_trace_charts).coordinate chart = ScatterChart() chart.style = 2 chart.title = f"{chart_name}: individual_trace" chart.y_axis.title = "Fura2 fluorescence ratio (a.u)" chart.x_axis.title = "Time (s)" chart.legend = None chart.height = 7.5 # default is 7.5 chart.width = 15 # default is 15 chart.x_axis.majorUnit = 60 ca_ex_st.style_chart(chart.title, chart) xvalues = Reference(sheet, min_col=time_column, min_row=3, max_col=time_column, max_row=file_max_row) yvalues = Reference(sheet, min_col=column, min_row=3, max_col=column, max_row=file_max_row) series = Series(yvalues, xvalues) chart.series.append(series) sheet.add_chart(chart, chart_cell)
def create_diagram(quasicycle, height=7, width=10, style=11): chart = ScatterChart() chart.title = quasicycle.name chart.height = height chart.width = width chart.x_axis.title = '' chart.y_axis.title = '' chart.legend = None rows_reference = Reference(quasicycle.sheet, min_col=quasicycle.start_cell_col, min_row=quasicycle.start_cell_row, max_row=quasicycle.start_cell_row + quasicycle.size) cols_reference = Reference(quasicycle.sheet, min_col=quasicycle.start_cell_col + 1, min_row=quasicycle.start_cell_row, max_row=quasicycle.start_cell_row + quasicycle.size) series = Series(cols_reference, rows_reference, title_from_data=False) chart.layoutTarget = "inner" chart.style = style chart.series.append(series) return chart
def draw_chart(wb_path: str): wb = openpyxl.load_workbook(wb_path) ws = wb.active c1 = ScatterChart() c1.y_axis.majorGridlines = None c1.x_axis.majorGridlines = None c1.x_axis.tickLblSkip = 100 #data = Reference(ws, min_col=1, min_row=1, max_col=ws.max_column, max_row=ws.max_row) #c1.add_data(data, titles_from_data=True) skips = ws.max_column / 2 col = 1 for i in range(1, ws.max_column, 2): xvalues = Reference(ws, min_col=i, min_row=1, max_row=ws.max_row) values = Reference(ws, min_col=i + 1, min_row=1, max_row=ws.max_row) series = Series(values, xvalues, title_from_data=True) series.smooth = True c1.series.append(series) ws.add_chart(c1, "D10") wb.save(wb_path) return True
def histogram(ws, series: np.ndarray, title="Distribution", bins=50): """ :param title: 图表标题 :param ws: 储存数据的worksheet :param series: 画直方图的数据 :param bins: 分段的个数 :return: chart """ hist, bin_edges = np.histogram(series, bins) count = np.insert(hist, 0, hist[0]) max_row = bins + 1 current = np.repeat(series[-1], max_row) max_number = np.linspace(0, hist.max(), max_row, endpoint=True) data = np.vstack((bin_edges, count, current, max_number)).transpose() row_offset = 0 if ws.max_row == 1 else ws.max_row print(row_offset) for r in data: ws.append(r.tolist()) chart = ScatterChart() chart.width = 22 # default is 15 chart.height = 15 # default is 7.5 chart.style = 2 chart.title = title chart.y_axis.title = 'Count' chart.x_axis.majorGridlines = None chart.y_axis.number_format = COMMA0_FORMAT chart.x_axis.title = 'Bin' chart.x_axis.number_format = PERCENT_FORMAT yvalues = Reference(ws, min_col=2, min_row=row_offset+1, max_row=ws.max_row) xvalues = Reference(ws, min_col=1, min_row=row_offset+1, max_row=ws.max_row) series = Series(values=yvalues, xvalues=xvalues, title='Count') series.smooth = True chart.series.append(series) yvalues = Reference(ws, min_col=4, min_row=row_offset+1, max_row=ws.max_row) xvalues = Reference(ws, min_col=3, min_row=row_offset+1, max_row=ws.max_row) series = Series(values=yvalues, xvalues=xvalues, title='Current') chart.series.append(series) return chart
def excel_graph_scatter(path,WSName,column_x,column_y_min,column_y_num,row_min,row_max,title): #ワークブックを開く wb = openpyxl.load_workbook(path) #ScatterChartオブジェクトを作成 chart = ScatterChart() #グラフのX軸の範囲を設定する為に、Referenceオブジェクト作る x_values = Reference(wb[WSName], min_col = column_x, min_row = row_min, max_row = row_max) #データの書き込み for i in range(0, column_y_num): min_col = i + column_y_min #データの範囲(Y軸)をReferenceで選択 values = Reference(wb[WSName], min_col = min_col, min_row = row_min, max_row = row_max) #Seriesオブジェクトを作成 series = Series(values, x_values, title=i) #線を消す series.graphicalProperties.line.noFill = True #マーカーを表示する series.marker.symbol = 'circle' #散布図として定義したchartへデータを指定したseries変数を渡す chart.series.append(series) #B2セルにグラフを表示 wb[WSName].add_chart(chart,"B2") #Fileを保存 wb.save(path)
def single_cell_traces_in_one_chart(file_max_column, file_max_row, sheet): # single_cell_traces_in_one_chart function generates 1 single scatter chart within the file with all the traces represented where: # x_axis = time(s) # y_axis = Fura2 fluorescence. # series = one serie for each analyzed cell # file_max_column: calculated by any of the analysis functions. # file_max_row: calculated by any of the analysis functions. # sheet: calculated by any of the analysis functions. chart_cell = sheet.cell(row=25, column=file_max_column + 7).coordinate chart = ScatterChart() chart.style = 2 chart.title = "Single cell traces" chart.y_axis.title = "Fura2 fluorescence ratio (a.u)" chart.x_axis.title = "Time (s)" chart.legend = None chart.height = 10 # default is 7.5 chart.width = 20 # default is 15 chart.x_axis.majorUnit = 60 ca_ex_st.style_chart(chart.title, chart) xvalues = Reference(sheet, min_col=file_max_column + 3, min_row=3, max_col=file_max_column + 3, max_row=file_max_row) for column in range(2, file_max_column + 1): # print(column) values = Reference(sheet, min_col=column, min_row=3, max_row=file_max_row) series = Series(values, xvalues) chart.series.append(series) sheet.add_chart(chart, chart_cell)
def create_scatter_chart(x_cells, y_cells, x_title, y_title, x_range=None, y_range=None, legends=None, height=10, width=20): """ @fn create_scatter_chart() @brief @param x_cells 横軸データ参照範囲(Reference) @param y_cells 縦軸データ参照範囲(Reference) @param x_title 横軸ラベル @param y_title 縦軸ラベル @param x_range 定義域 @param y_range 値域 @param legends 凡例 @param height グラフの高さ @param width グラフの幅 @retval chart グラフ """ chart = ScatterChart() chart.x_axis.title = x_title chart.y_axis.title = y_title chart.style = 2 chart.height = height chart.width = width if x_range is not None: chart.x_axis.scaling.min = min(x_range) chart.x_axis.scaling.max = max(x_range) if y_range is not None: chart.y_axis.scaling.min = min(y_range) chart.y_axis.scaling.max = max(y_range) if legends is None: chart.legend = None else: chart.legend.position = "t" if type(x_cells) != list and type(y_cells) != list: series = Series(y_cells, x_cells, title=legends) chart.series.append(series) elif type(x_cells) != list and type(y_cells) == list: for y_cells_unit, legend in zip(y_cells, legends): series = Series(y_cells_unit, x_cells, title=legend) chart.series.append(series) elif type(x_cells) == list and type(y_cells) == list: for x_cells_unit, y_cells_unit, legend in zip(x_cells, y_cells, legends): series = Series(y_cells_unit, x_cells_unit, title=legend) chart.series.append(series) return chart
def generate_captures_graph(self, captures_info, row_count): """ Gera o gráfico Sinais X Tempo. """ my_chart = ScatterChart() my_chart.title = 'Gráfico dos Sinais' my_chart.style = 16 my_chart.y_axis.title = 'Sinal' my_chart.x_axis.title = 'Tempo (segundos)' x_values = Reference(self.spreadsheet, min_col=2, min_row=row_count - len(captures_info), max_row=row_count - 3) y_values = Reference(self.spreadsheet, min_col=6, min_row=row_count - len(captures_info) - 1, max_row=row_count - 3) series = Series(y_values, x_values, title_from_data=True) my_chart.series.append(series) my_chart.width = 23 my_chart.height = 10 self.spreadsheet.add_chart(my_chart, f"A{row_count}")
from openpyxl import Workbook from openpyxl.chart import ( ScatterChart, Reference, Series, ) import math wb = Workbook() ws = wb.active ws.append(['X', 'Gaussian']) for i, x in enumerate(range(-10, 11)): ws.append([x, "=EXP(-(($A${row}/6)^2))".format(row = i + 2)]) chart1 = ScatterChart() chart1.title = "No Scaling" chart1.x_axis.title = 'x' chart1.y_axis.title = 'y' chart1.legend = None chart2 = ScatterChart() chart2.title = "X Log Scale" chart2.x_axis.title = 'x (log10)' chart2.y_axis.title = 'y' chart2.legend = None chart2.x_axis.scaling.logBase = 10 chart3 = ScatterChart() chart3.title = "Y Log Scale" chart3.x_axis.title = 'x'
ws = wb.active rows = [ ['Size', 'Batch 1', 'Batch 2'], [2, 40, 30], [3, 40, 25], [4, 50, 30], [5, 30, 25], [6, 25, 35], [7, 20, 40], ] for row in rows: ws.append(row) chart = ScatterChart() chart.title = "Scatter Chart" chart.style = 13 chart.x_axis.title = 'Size' chart.y_axis.title = 'Percentage' xvalues = Reference(ws, min_col=1, min_row=2, max_row=7) for i in range(2, 4): values = Reference(ws, min_col=i, min_row=1, max_row=7) series = Series(values, xvalues, title_from_data=True) chart.series.append(series) ws.add_chart(chart, "A10") wb.save("scatter.xlsx")
class TestScatterChartWriter(object): def setup(self): wb = Workbook() ws = wb.get_active_sheet() ws.title = 'Scatter' for i in range(10): ws.cell(row=i, column=0).value = i ws.cell(row=i, column=1).value = i self.scatterchart = ScatterChart() self.scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)), xvalues=Reference(ws, (0, 1), (10, 1)))) self.cw = ScatterChartWriter(self.scatterchart) self.root = Element('test') def test_write_xaxis(self): self.scatterchart.x_axis.title = 'test x axis title' self.cw._write_axis(self.root, self.scatterchart.x_axis, '{%s}valAx' % CHART_NS) expected = """<test xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:valAx><c:axId val="60871424" /><c:scaling><c:orientation val="minMax" /><c:max val="10.0" /><c:min val="0.0" /></c:scaling><c:axPos val="b" /><c:majorGridlines /><c:numFmt formatCode="General" sourceLinked="1" /><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>test x axis title</a:t></a:r></a:p></c:rich></c:tx><c:layout /></c:title><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:crossBetween val="midCat" /><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_yaxis(self): self.scatterchart.y_axis.title = 'test y axis title' self.cw._write_axis(self.root, self.scatterchart.y_axis, '{%s}valAx' % CHART_NS) expected = """<test xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" 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: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>test y axis title</a:t></a:r></a:p></c:rich></c:tx><c:layout /></c:title><c:tickLblPos val="nextTo" /><c:crossAx val="60871424" /><c:crosses val="autoZero" /><c:crossBetween val="midCat" /><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 = """<test xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:ser><c:idx val="0" /><c:order val="0" /><c:xVal><c:numRef><c:f>\'Scatter\'!$B$1:$B$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:xVal><c:yVal><c:numRef><c:f>\'Scatter\'!$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:yVal></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 = """<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_write_print_settings(self): self.cw._write_print_settings(self.root) expected = """<test xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><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>""" xml = get_xml(self.root) diff = compare_xml(xml, expected) assert diff is None, diff def test_write_chart(self): self.cw._write_chart(self.root) xml = get_xml(self.root) expected = """<test xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:chart><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:scatterChart><c:scatterStyle val="lineMarker" /><c:ser><c:idx val="0" /><c:order val="0" /><c:xVal><c:numRef><c:f>\'Scatter\'!$B$1:$B$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:xVal><c:yVal><c:numRef><c:f>\'Scatter\'!$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:yVal></c:ser><c:axId val="60871424" /><c:axId val="60873344" /></c:scatterChart><c:valAx><c:axId val="60871424" /><c:scaling><c:orientation val="minMax" /><c:max val="10.0" /><c:min val="0.0" /></c:scaling><c:axPos val="b" /><c:majorGridlines /><c:numFmt formatCode="General" sourceLinked="1" /><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:crossBetween val="midCat" /><c:majorUnit val="2.0" /></c:valAx><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="midCat" /><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>""" diff = compare_xml(xml, expected) assert diff is None, diff def test_serialised(self): xml = self.cw.write() fname = os.path.join(DATADIR, "writer", "expected", "ScatterChart.xml") with open(fname) as expected: diff = compare_xml(xml, expected.read()) assert diff is None, diff
from openpyxl import Workbook from openpyxl.chart import ( ScatterChart, Reference, Series, ) wb = Workbook() ws = wb.active ws.append(['X', '1/X']) for x in range(-10, 11): if x: ws.append([x, 1.0 / x]) chart1 = ScatterChart() chart1.title = "Full Axes" chart1.x_axis.title = 'x' chart1.y_axis.title = '1/x' chart1.legend = None chart2 = ScatterChart() chart2.title = "Clipped Axes" chart2.x_axis.title = 'x' chart2.y_axis.title = '1/x' chart2.legend = None chart2.x_axis.scaling.min = 0 chart2.y_axis.scaling.min = 0 chart2.x_axis.scaling.max = 11 chart2.y_axis.scaling.max = 1.5
ws = wb.active rows = [ ['Size', 'Batch 1', 'Batch 2'], [2, 40, 30], [3, 40, 25], [4, 50, 30], [5, 30, 25], [6, 25, 35], [7, 20, 40], ] for row in rows: ws.append(row) ch1 = ScatterChart() xvalues = Reference(ws, min_col=1, min_row=2, max_row=7) for i in range(2, 4): values = Reference(ws, min_col=i, min_row=1, max_row=7) series = Series(values, xvalues, title_from_data=True) ch1.series.append(series) ch1.title = "Default layout" ch1.style = 13 ch1.x_axis.title = 'Size' ch1.y_axis.title = 'Percentage' ch1.legend.position = 'r' ws.add_chart(ch1, "B10")