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")
