def create_series_bake(self, x_values: Reference, y_values: Reference, index: int) -> Series:
series = Series(values=y_values, xvalues=x_values, title=self.fbg_names[index])
rgb_percent = RGBPercent(*hex_to_rgb(index))
series.marker = marker.Marker(symbol=MARKERS[index % len(MARKERS)],
spPr=GraphicalProperties(solidFill=ColorChoice(rgb_percent)))
series.graphicalProperties.line.noFill = True
return series
def _add_series(chart: ScatterChart, index: int, temperature_column: int,
series_parameters: SeriesParameters, cycle: int = None):
start_row = CALIBRATION_START_ROW
end_row = series_parameters.last_row
extra_point_indexes = []
if cycle is not None and series_parameters.extra_point_temperatures is not None:
for temperature in series_parameters.extra_point_temperatures:
for row_number in range(start_row, end_row+1):
column_letter = get_column_letter(temperature_column)
column = "{}{}".format(column_letter, row_number)
excel_temperature = series_parameters.data_sheet[column].value
if math.isclose(temperature, excel_temperature, abs_tol=.5):
extra_point_indexes.append(row_number - 1)
for i in extra_point_indexes:
if i < 2:
start_row += 1
else:
end_row -= 1
x_values = Reference(series_parameters.data_sheet, min_col=temperature_column, min_row=start_row, max_row=end_row)
y_values = Reference(series_parameters.data_sheet, min_col=series_parameters.indexes[index] + 1,
min_row=start_row, max_row=end_row)
series_title = series_parameters.sub_type.get_series_title(cycle)
series = Series(xvalues=x_values, values=y_values, title=series_title)
series.marker = marker.Marker(MARKERS[index % len(MARKERS)])
series.marker.size = 12
chart.series.append(series)
return index + 1
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 drawChartMem(stIdx, endIdx):
c2 = LineChart()
c2.title = sheet_ranges['A' + str(stIdx)].value[4:] + '_MEM'
c2.style = 12
c2.x_axis.number_format = 'yyyymmdd'
c2.x_axis.majorTimeUnit = "days"
c2.legend.position = 'b'
data = Reference(ws, min_col=5, min_row=stIdx, max_row=endIdx)
series = Series(data, title="MEMORY PCTUSED MAX(%)")
c2.append(series)
data = Reference(ws, min_col=6, min_row=stIdx, max_row=endIdx)
series = Series(data, title="MEMORY PCTUSED AVG(%)")
c2.append(series)
data = Reference(ws, min_col=7, min_row=stIdx, max_row=endIdx)
series = Series(data, title="MEMORY_SWAPPCTUSED_MAX(%)")
c2.append(series)
data = Reference(ws, min_col=8, min_row=stIdx, max_row=endIdx)
series = Series(data, title="MEMORY_SWAPPCTUSED_AVG(%)")
c2.append(series)
dates = Reference(ws, min_col=2, min_row=stIdx, max_row=endIdx)
c2.set_categories(dates)
s1 = c2.series[0]
s1.graphicalProperties.line.solidFill = "5698d4"
s2 = c2.series[1]
s2.graphicalProperties.line.solidFill = "ed7d30"
s3 = c2.series[2]
s3.graphicalProperties.line.solidFill = "adadad"
s4 = c2.series[3]
s4.graphicalProperties.line.solidFill = "ffc100"
ws.add_chart(c2, "P" + str(stIdx))
def write_plots(worksheet, spec):
chart = ScatterChart()
chart.title = 'Measured Data w/ Exponential Fit \n Pressure vs. Temperature'
chart.style = 13
chart.x_axis.title = 'Time (hrs)'
chart.y_axis.title = 'Pressure (psi)'
# time values (hours)
x_values = Reference(worksheet, min_col=9, min_row=3, max_row=len(spec.p) + 3)
# pressure data (psi)
p = Reference(worksheet, min_col=11, min_row=3, max_row=len(spec.p) + 3)
p_series = Series(p, x_values)
chart.series.append(p_series)
# exponential curve fit data (psi)
exp = Reference(worksheet, min_col=10, min_row=3, max_row=len(spec.p) + 3)
exp_series = Series(exp, x_values)
chart.series.append(exp_series)
# Style the lines
s1 = chart.series[1]
s1.marker.symbol = "triangle"
s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling
s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline
s1.graphicalProperties.line.noFill = True
s2 = chart.series[0]
s2.graphicalProperties.line.solidFill = "00AAAA"
s2.graphicalProperties.line.dashStyle = "sysDot"
s2.graphicalProperties.line.width = 100050 # width in EMUs
worksheet.add_chart(chart, 'A7')
return worksheet
def draw_bar_chart(writer, max_row):
sheet = writer.sheets['死亡率']
date = sheet['A2'].value
# 初始化我们的bar chart
chart = BarChart()
# 指定bar chart 的数据范围
chart_data = Reference(sheet, min_col=6, max_col=6, min_row=2, max_row=max_row-1)
chart_series = Series(chart_data, title='截止{}死亡率'.format(date))
chart_series.graphicalProperties.solidFill = '5DD092' # Silver Tree
# 指定x轴
x_axis_data = Reference(sheet, min_col=3, max_col=3, min_row=2, max_row=max_row-1)
# chart 添加到 sheet里
chart.append(chart_series)
chart.set_categories(x_axis_data)
# 设置chart的样式
chart.height, chart.width = 14, 21
chart.title, chart.y_axis.title = '主要疫情地区死亡率', '死亡率'
chart.y_axis.number_format = '0.0%'
chart.legend.position = 't'
# 画平均死亡率横线line chart
line_chart = LineChart()
line_data = Reference(sheet, min_col=7, max_col=7, min_row=2, max_row=max_row - 1)
line_series = Series(line_data, title='除湖北外死亡率')
line_chart.append(line_series)
# 合并图表
chart += line_chart
# 添加图表并保存
sheet.add_chart(chart, 'I1')
writer.save()
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_graph(workbook, output_file, index_ticker, company_ticker):
sheets_list = workbook.sheetnames
beta_output = sheets_list[0]
company_ticker = sheets_list[1]
market_ticker = sheets_list[2]
beta_worksheet = workbook[beta_output]
company_change_len = len(beta_worksheet['A'])
chart = ScatterChart()
chart.title = "Beta"
chart.style = 13
chart.x_axis.title = '{0} % change'.format(index_ticker)
chart.y_axis.title = '{0} % change'.format(company_ticker)
xvalues = Reference(beta_worksheet,
min_col=2,
min_row=2,
max_row=company_change_len)
yvalues = Reference(beta_worksheet,
min_col=1,
min_row=2,
max_row=company_change_len)
series = Series(yvalues, xvalues, title_from_data=False)
series.graphicalProperties.line.noFill = True
series.marker.symbol = "circle"
series.marker.size = "2"
series.trendline = Trendline(dispRSqr=True,
trendlineType='linear',
dispEq=True)
chart.series.append(series)
beta_worksheet.add_chart(chart, "F5")
workbook.save(output_file)
def copyToSheet(die, test):
dieSheets = []
for sheet in range(len(clSheets)):
if die.lower() in clSheets[sheet].lower() and test.lower(
) in clSheets[sheet].lower():
dieSheets.append(clSheets[sheet])
if len(dieSheets) > 0:
print('Copying ' + die + ' ' + test)
ws = lifetime.create_sheet(title=die + ' ' + test)
pasteRow = 2
for sheet in range(len(dieSheets)):
active = clWings.sheets[dieSheets[sheet]]
for i in range(1, 22):
for j in range(1, 3):
ws.cell(row=i + pasteRow - 1,
column=j).value = active.range(
get_column_letter(j) + str(i + 32)).value
pasteRow = pasteRow + 21
ws.cell(row=1, column=1).value = '[Cl2] (ppm)'
ws.cell(row=1, column=2).value = 'Response (nA)'
ws.cell(row=1, column=3).value = 'Calibration (nA)'
ws.cell(row=1, column=5).value = 'Slope'
ws.cell(row=2, column=5).value = 'Intercept'
ws.cell(
row=1,
column=6).value = clWings.sheets[dieSheets[0]].range('B12').value
ws.cell(
row=2,
column=6).value = clWings.sheets[dieSheets[0]].range('B13').value
for i in range(2, ws.max_row):
ws.cell(row=i, column=3).value = float(ws['F1'].value) * float(
ws.cell(row=i, column=1).value) + float(ws['F2'].value)
chart = ScatterChart()
chart.title = str(die + ' ' + test + ' Lifetime (' +
str(len(dieSheets)) + 'Calibrations)')
chart.x_axis.title = '[Cl2] (ppm)'
chart.y_axis.title = 'Response (nA)'
xvalues = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
roamValues = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
roamSeries = Series(roamValues, xvalues, title_from_data=True)
calValues = Reference(ws, min_col=3, min_row=1, max_row=ws.max_row)
calSeries = Series(
calValues,
xvalues,
title_from_data=True,
)
roamSeries.marker = openpyxl.chart.marker.Marker('x')
roamSeries.graphicalProperties.line.noFill = True
chart.series.append(roamSeries)
chart.series.append(calSeries)
cs.add_chart(chart, 'A' + str(len(lifetime.worksheets) * 15 - 29))
def create_line_chart(self):
c1 = LineChart()
c1.title = chart_spend_title
c1.style = 12
c1.y_axis.title = "Euros"
c1.y_axis.crossAx = 500
c1.x_axis = DateAxis(crossAx=100)
c1.x_axis.number_format = 'd-mm-yy'
c1.x_axis.majorTimeUnit = "days"
self.open_sheet(raw_chart_data_sheet)
values = Reference(self.__working_sheet,
min_row=1,
max_row=self.__working_sheet.max_row,
min_col=3,
max_col=3)
series = Series(values, title_from_data=True)
c1.append(series)
dates = Reference(self.__working_sheet,
min_col=1,
max_col=1,
min_row=1,
max_row=self.__working_sheet.max_row)
c1.set_categories(dates)
c1.height = 30
c1.width = 50
c2 = LineChart()
c2.title = chart_topup_title
c2.style = 12
c2.y_axis.title = "Euros"
c2.y_axis.crossAx = 500
c2.x_axis = DateAxis(crossAx=100)
c2.x_axis.number_category = 'Date'
c2.x_axis.number_format = 'd-mm-yy'
c2.x_axis.majorTimeUnit = "days"
self.open_sheet(raw_chart_data_sheet)
values = Reference(self.__working_sheet,
min_row=1,
max_row=self.__working_sheet.max_row,
min_col=2,
max_col=2)
series = Series(values, title_from_data=True)
c2.append(series)
dates = Reference(self.__working_sheet,
min_col=1,
max_col=1,
min_row=1,
max_row=self.__working_sheet.max_row)
c2.set_categories(dates)
c2.height = 30
c2.width = 50
self.open_sheet(chart_sheet)
self.__working_sheet.add_chart(c1, "C1")
self.__working_sheet.add_chart(c2, "C60")
self.__wb.save(self.__new_file_name)
self.open_sheet(raw_data_sheet)
def create_new_chart(base_chart, data, data_points, categories):
#←作成済みのチャートをコピーしてデータのみ書き換える
series = Series(data, title_from_data=True)
series.data_points = data_points
chart = deepcopy(base_chart)
chart.title = None
chart.series.append(series)
chart.set_categories(categories)
return chart
def all_sheets(sheet_name):
chart = ScatterChart()
chart.title = "Scatter Chart"
chart.style = 2
last_index_landing, last_index_parking, last_index_takeoff = 0, 0, 0
ws = wb[sheet_name]
for i in range(2, ws.max_row + 2):
for j in range(8, 19, 5):
if ws.cell(row=i, column=j).value is None:
if j == 8 and last_index_landing == 0:
last_index_landing = i - 1
elif j == 13 and last_index_parking == 0:
last_index_parking = i - 1
elif j == 18 and last_index_takeoff == 0:
last_index_takeoff = i - 1
print(last_index_landing, last_index_parking, last_index_takeoff)
# -------------------LANDING---------------------------------------------
x_landing = Reference(ws, min_col=8, min_row=2, max_row=last_index_landing)
y_landing = Reference(ws, min_col=9, min_row=2, max_row=last_index_landing)
seriesLanding = Series(y_landing, x_landing, title="landing_queue")
chart.series.append(seriesLanding)
# -------------------TAKEOFF---------------------------------------------
x_takeoff = Reference(ws,
min_col=18,
min_row=2,
max_row=last_index_takeoff)
y_takeoff = Reference(ws,
min_col=19,
min_row=2,
max_row=last_index_takeoff)
seriesTakeoff = Series(y_takeoff, x_takeoff, title="takeoff_queue")
chart.series.append(seriesTakeoff)
# -------------------PARKING---------------------------------------------
x_parking = Reference(ws,
min_col=13,
min_row=2,
max_row=last_index_parking)
y_parking = Reference(ws,
min_col=14,
min_row=2,
max_row=last_index_parking)
seriesParking = Series(y_parking, x_parking, title="parking_queue")
chart.series.append(seriesParking)
# -----------------------------------------------------------------------
ws.add_chart(chart, "A10")
wb.save(file)
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 create_avg_chart(workbook, worksheet, min_col, min_row, max_col, max_row):
chart = LineChart()
#Sets titles on graph
chart.y_axis.title = '5S Audit Socre Average'
chart.x_axis.title = '5S Weekly Audit Dates'
chart.title = 'Past ' + string_weeks + ' Weeks Average Score For 539'
#Gets the data from the table
data = Reference(worksheet,
min_col=min_col,
min_row=min_row + 14,
max_col=max_col,
max_row=max_row)
#Gets the Dates from the first row and stores them in a variable
dates = Reference(worksheet,
min_col=min_col,
min_row=min_row,
max_col=max_col,
max_row=max_row - 14)
#Creates a series using the data collected from the specified rows of the table
series = Series(data, title='Building 539')
#Sets the marker on the chart to be a triangle
series.marker.symbol = "triangle"
#Adds the data and date labels to the chart
chart.append(series)
chart.set_categories(dates)
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
#Adds the chart to the worksheet at cell M18 and saves the file
worksheet.add_chart(chart, 'K18')
workbook.save(wb_name)
def createGraph3(disl, disl3): #,disl2,disl3):
res = 'Resistencia'
eft = 'Ef.Turbina'
disl = [res] + disl
disl3 = [eft] + disl3
#for i in range(1,len(disl)):
for g in range(1):
graph3.append(disl)
graph3.append(disl3)
chart = ScatterChart()
chart.title = 'R[Ω] vs Eficiencia turbina'
chart.x_axis.title = 'Eft'
chart.y_axis.title = 'R'
xvals = Reference(graph3, min_col=2, min_row=1, max_col=len(disl))
#for s in range(1,len(disl)): #10
values = Reference(graph3, 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]
graph3.add_chart(chart, "M1")
wb.save(filesheet)
def create_scatter(df, wb, sheet_name):
df_columns = df.columns.tolist()
ws = wb[sheet_name]
chart = ScatterChart()
chart.title = sheet_name
chart.height = 15
chart.width = 30
chart.x_axis.scaling.max = max(df['timestamps'])
chart.x_axis.scaling.min = min(df['timestamps'])
chart.x_axis.title = df_columns[1]
for i in df_columns:
if i in sheet_name:
chart.x_axis.title = i
chart.y_axis.title = 'intensity_of_emotion'
start_row = 2
end_row = len(df) + 1
x_values = Reference(ws, min_col=1, min_row=start_row, max_row=end_row)
for i in range(df_columns.index('happy') + 1, df_columns.index('confused') + 2):
values = Reference(ws, min_col=i, min_row=1, max_row=end_row)
series = Series(values, x_values, title_from_data=True)
chart.series.append(series)
letter = get_column_letter(len(df_columns) + 2)
ws.add_chart(chart, f"{letter}1")
def addChart(groupLength1):
print('Printing Chart...')
# print('grouplength1: '+str(len(groupLength1)))
chart = ScatterChart()
chart.title = "Capacity Vs Voltage"
chart.style = 13
chart.x_axis.title = 'Capacity'
chart.y_axis.title = 'Voltage'
le = (len(groupLength1) * 4) + len(groupLength1)
# print("The sizes of each grp" + str(groupLength1[0]))
k = 0
m = 1
# print("list siz = " + str(len(groupLength1)))
# print("le = " + str(le))
for j in range(4, le, 5):
print("j = " + str(j))
xvalues = Reference(AutoExcel.sh1_O,
min_col=j,
min_row=1,
max_row=groupLength1[k])
values = Reference(AutoExcel.sh1_O,
min_col=m,
min_row=1,
max_row=groupLength1[k])
series = Series(values, xvalues, title_from_data=True)
chart.series.append(series)
k += 1
m += 5
AutoExcel.sh1_O.add_chart(chart, 'L3')
out_f = "C:\\Users\\Vignesh\\PycharmProjects\\AutoExcel\\src\\uploads\\" + AutoExcel.outputFile + '.xlsx'
AutoExcel.wb_O.save(out_f)
def sample_bar_chart(self, title, matrix):
"""
more details
https://openpyxl.readthedocs.io/en/2.5/charts/chart_layout.html#size-and-position
"""
rows, cols = len(matrix), len(matrix[0])
wb = openpyxl.Workbook()
sheet = wb.active
for data_row in matrix:
sheet.append(data_row)
chart = ScatterChart()
x = Reference(sheet, min_col=1, min_row=1, max_row=rows)
for i in range(2, cols + 1):
y = Reference(sheet, min_col=i, min_row=1, max_row=rows)
s = Series(y, x, title_from_data=True)
chart.series.append(s)
chart.title = title
chart.style = 12
chart.x_axis.title = "X"
chart.y_axis.title = "Y"
chart.legend.position = 'r'
sheet.add_chart(chart, 'B12')
wb.save("sample_chart.xlsx")
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 produce(filename, name, date):
wb = xl.load_workbook(filename)
rez = False
for sheet in wb:
if sheet.title == name + date:
ws = wb[name + date]
rez = True
break
if not rez:
ws = wb.create_sheet(title=name + date)
for i in range(1, 13):
ws.cell(row=1, column=i).value = i
ws.cell(row=2, column=i).value = 0
for prod in IterRowDict(wb, "Products"):
for sale in IterRowDict(wb, "Sales"):
if prod["Name"] == name and sale["Date"][6:] == date and prod[
"id"] == prod["id"]:
amount = int(prod["Price"]) * int(sale["Quantity"])
ws.cell(row=2, column=int(sale["Date"][3:5])).value = amount
ws = wb[name + date]
chart = BarChart()
x = Reference(ws, min_col=1, max_col=12, min_row=1)
y = Reference(ws, min_col=1, max_col=12, min_row=2)
chart.append(Series(y))
chart.set_categories(x)
ws.add_chart(chart, "A10")
wb.save(filename)
def create_annual_chart(worksheet=None, year=0, min_row=1, max_row=1):
chart = ScatterChart()
# sets the chart styling
chart.title = f'{year}'
chart.x_axis.title = 'Month'
chart.y_axis.title = 'Amount'
chart.legend.position = 'b'
chart.height = 7.7
chart.width = 21.5
xvalues = Reference(worksheet=worksheet,
min_col=2,
min_row=min_row + 1,
max_row=max_row)
for col in range(3, 6):
values = Reference(worksheet=worksheet,
min_col=col,
min_row=min_row,
max_row=max_row)
series = Series(values, xvalues, title_from_data=True)
chart.series.append(series)
return chart
def createGraphs(disl, disl1): #,disl2,disl3):
#for i in range(1,len(disl)):
res = 'Resistencia'
rps = 'RPM'
disl = [res] + disl
disl1 = [rps] + disl1
for g in range(1):
graph.append(disl)
graph.append(disl1)
chart = ScatterChart()
chart.title = 'R[Ω] vs RPM'
chart.x_axis.title = 'RPM'
chart.y_axis.title = 'R[Ω]'
xvals = Reference(graph, min_col=2, min_row=1, max_col=len(disl))
#for s in range(1,len(disl)): #10
values = Reference(graph, 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]
graph.add_chart(chart, "M1")
wb.save(filesheet)
def create_graph(result_file, result_book, sheet_name_active, current_format_sheet):
# Valiable Assignment
book_name_will_plot_graph = os.path.basename(result_file)
sheet_name_will_plot_graph = result_book[sheet_name_active]
max_row_in_sheet_name = sheet_name_will_plot_graph.max_row
# Assign value in X-axis and Y-axis
scatter_chart = ScatterChart()
y_values = Reference(sheet_name_will_plot_graph, min_row=3, max_row=max_row_in_sheet_name, min_col=3)
x_values = Reference(sheet_name_will_plot_graph, min_row=3, max_row=max_row_in_sheet_name, min_col=2)
series = Series(y_values, x_values, title=None, title_from_data=False)
scatter_chart.series.append(series)
# Adjust Scatter Element
scatter_chart.y_axis.scaling.min = 0
scatter_chart.legend = None
scatter_chart.y_axis.number_format = "0.00"
# Paste Scatter_chart
current_format_sheet.add_chart(scatter_chart, "A5")
# Setting width and height of scatter_chart
scatter_chart.width = 14
scatter_chart.height = 8
return scatter_chart
def draw_pattern_scatterchart(data_work_sheet, data_rows_seq, data_columns_list, chart_position, chart_title):
chart = ScatterChart()
chart.title = str(chart_title)
chart.legend.position = 't'
#chart.x_axis.title = 'samples from:' + data_work_sheet.title[0:21]
#chart.x_axis.title = u'样本:' + data_work_sheet.title[0:25] + '..'
chart.x_axis.title = data_work_sheet.title + '..'
#chart.x_axis.txPr =
#chart_title_font = Font(name='Arial Narrow',size=12)
chart.y_axis.title = 'lantency(ms)'
#chart.style = 13
#chart.layout =
#chart.graphical_properties =
#chart.varyColors = 'blue'
#chart.scatterStyle = 'marker'
#same sheet same sample width of chart, x ray values, get min and max row from pattern_col
xvalues = Reference(data_work_sheet, min_col=1, min_row=data_rows_seq[0], max_row=data_rows_seq[-1])
for i in data_columns_list :
#print(data_work_sheet.cell(row=1,column=i).value)
line_title = data_work_sheet.cell(row=1,column=i).value
yvalues = Reference(data_work_sheet, min_col=i, min_row=data_rows_seq[0], max_row=data_rows_seq[-1])
series = Series(yvalues, xvalues, title=line_title)
chart.series.append(series)
#print(chart.series[0])
# Style the lines
for i in range(len(data_columns_list)) :
s1 = chart.series[i]
s1.marker.symbol = "circle"
#s1.marker.symbol = "triangle"
#s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling
#s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline
s1.graphicalProperties.line.noFill = True
# print(wb.chartsheets)
wb['ScatterChart'].add_chart(chart, chart_position)
def create_charts_multiple(worksheet):
chart = LineChart()
#Sets titles on graph
chart.y_axis.title = '5S Audit Socres'
chart.x_axis.title = '5S Weekly Audit Dates'
chart.title = 'Past ' + string_weeks + ' Weeks Scores For All Zones'
for i in range(1, 13):
#Gets the data from the table
data = Reference(worksheet,
min_col=2,
min_row=1 + i,
max_col=weeks + 1,
max_row=1 + i)
#Gets the Dates from the first row and stores them in a variable
dates = Reference(worksheet,
min_col=2,
min_row=1,
max_col=weeks + 1,
max_row=1)
#Creates a series using the data collected from the specified rows of the table
series = Series(data, title='Zone ' + str(i))
#Adds the data and date labels to the chart
chart.append(series)
chart.set_categories(dates)
#chart.dataLabels = DataLabelList()
#chart.dataLabels.showVal = True
#Adds the chart to the worksheet at cell A10 and saves the file
chart.height = 10
chart.width = 20
worksheet.add_chart(chart, 'A18')
workbook.save(wb_name)
def create_norms(self):
x_values = Reference(ws, min_col=self.x_column, min_row=2, max_row=3)
y_values = Reference(ws, min_col=self.y_column, min_row=2, max_row=3)
norms = Series(y_values, x_values)
norms.marker.symbol = 'triangle'
norms.marker.size = 3
norms.marker.graphicalProperties.solidFill = "FFFFFF"
return norms
def get_throughput_records_series(self, ws, ws_name):
cols = self.get_columns_from_worksheet(ws)
return Series(Reference(ws,
min_col=cols["Records/Sec"],
min_row=2,
max_col=cols["Records/Sec"],
max_row=ws.max_row),
title=ws_name + "-Records/Sec")
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 contoh_gauge_chart(self, ws):
data = [
["Donut", "Pie"],
[25, 75],
[50, 1],
[25, 124],
[100],
]
# based on http://www.excel-easy.com/examples/gauge-chart.html
for row in data:
ws.append(row)
# First chart is a doughnut chart
c1 = DoughnutChart(firstSliceAng=270, holeSize=50)
c1.title = "Code coverage"
c1.legend = None
ref = Reference(ws, min_col=1, min_row=2, max_row=5)
s1 = Series(ref, title_from_data=False)
slices = [DataPoint(idx=i) for i in range(4)]
slices[0].graphicalProperties.solidFill = "FF3300" # red
slices[1].graphicalProperties.solidFill = "FCF305" # yellow
slices[2].graphicalProperties.solidFill = "1FB714" # green
slices[3].graphicalProperties.noFill = True # invisible
s1.data_points = slices
c1.series = [s1]
# Second chart is a pie chart
c2 = PieChart(firstSliceAng=270)
c2.legend = None
ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4)
s2 = Series(ref, title_from_data=False)
slices = [DataPoint(idx=i) for i in range(3)]
slices[0].graphicalProperties.noFill = True # invisible
slices[1].graphicalProperties.solidFill = "000000" # black needle
slices[2].graphicalProperties.noFill = True # invisible
s2.data_points = slices
c2.series = [s2]
c1 += c2 # combine charts
ws.add_chart(c1, "D1")
# Second chart is a pie chart
c2 = PieChart(firstSliceAng=270)
# c2.legend = None
ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4)
s2 = Series(ref, title_from_data=False)
# slices = [DataPoint(idx=i) for i in range(3)]
# slices[0].graphicalProperties.noFill = True # invisible
# slices[1].graphicalProperties.solidFill = "000000" # black needle
# slices[2].graphicalProperties.noFill = True # invisible
# s2.data_points = slices
c2.series = [s2]
ws.add_chart(c2, "D20")
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)
]
# based on http://www.excel-easy.com/examples/gauge-chart.html
wb = Workbook()
ws = wb.active
for row in data:
ws.append(row)
# First chart is a doughnut chart
c1 = DoughnutChart(firstSliceAng=270, holeSize=50)
c1.title = "Code coverage"
c1.legend = None
ref = Reference(ws, min_col=1, min_row=2, max_row=5)
s1 = Series(ref, title_from_data=False)
slices = [DataPoint(idx=i) for i in range(4)]
slices[0].graphicalProperties.solidFill = "FF3300" # red
slices[1].graphicalProperties.solidFill = "FCF305" # yellow
slices[2].graphicalProperties.solidFill = "1FB714" # green
slices[3].graphicalProperties.noFill = True # invisible
s1.data_points = slices
c1.series = [s1]
# Second chart is a pie chart
c2 = PieChart(firstSliceAng=270)
c2.legend = None
ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
chart.title = "Doughnuts sold by category"
chart.style = 26
# Cut the first slice out of the doughnut
slices = [DataPoint(idx=i) for i in range(4)]
plain, jam, lime, chocolate = slices
chart.series[0].data_points = slices
plain.graphicalProperties.solidFill = "FAE1D0"
jam.graphicalProperties.solidFill = "BB2244"
lime.graphicalProperties.solidFill = "22DD22"
chocolate.graphicalProperties.solidFill = "61210B"
chocolate.explosion = 10
ws.add_chart(chart, "E1")
from copy import deepcopy
chart2 = deepcopy(chart)
chart2.title = None
data = Reference(ws, min_col=3, min_row=1, max_row=5)
series2 = Series(data, title_from_data=True)
series2.data_points = slices
chart2.series.append(series2)
ws.add_chart(chart2, "E17")
wb.save("doughnut.xlsx")
