def create_time_formulas(self, refcol, refrow, startcol, startrow, endrow):
# brevity
refaddr = xladdress.cellStr(refrow, refcol, 0, 1)
# list of 1-tuples
formulas = [("=(%s-%s)*24*60" % (xladdress.cellStr(i, startcol), refaddr),)
for i in range(startrow, endrow + 1)]
return formulas
def _add_data_table(self, date_col):
m_addr = self.m_addr
# cell formulas for "x_range" and "y_range" in docstring matrix
x_range = '=%s&%s&":"&%s&%s' % (m_addr(1, 1), m_addr(2, 1),
m_addr(1, 1), m_addr(3, 1))
y_range = '=%s&%s&":"&%s&%s' % (m_addr(1, 2), m_addr(2, 1),
m_addr(1, 2), m_addr(3, 1))
# column letters
x_col = cellStr(1, date_col + 1).replace("1", "")
y_col = cellStr(1, date_col + 3).replace("1", "")
# m and b
linest_formula = "=index(linest(indirect(%s), indirect(%s)), %d)"
addy_y = m_addr(4, 2)
addy_x = m_addr(4, 1)
m_form = linest_formula % (addy_y, addy_x, 1)
b_form = linest_formula % (addy_y, addy_x, 2)
top_left = self.m_cells(1, 1)
bottom_right = self.m_cells(6, 2)
self.cells.Range(top_left, bottom_right).Value = [
[x_col, y_col],
[self.start_row, None],
[self.end_row, None],
[x_range, y_range],
["m", "b"],
[m_form, b_form]
]
self.m_cells(1, 1).EntireColumn.NumberFormat = "General"
def create_control_data(self):
""" Create the set of control data at the
beginning of the test.
If class is extended, magic numbers may need to be
updated.
"""
# Cell coordinates in form of cellStr() args
ave_m_cell = (5, 1, 0, 1)
ave_b_cell = (5, 2, 0, 1)
ideal_sp_cell = (6, 1, 1, 1)
ideal_formula = '=MIN(%s*%s+%s,%s)' % (cellStr(*ave_m_cell),
'%s',
cellStr(*ave_b_cell),
cellStr(*ideal_sp_cell))
ideal_seconds = 10000
ideal_data = [(i, ideal_formula % cellStr(i + self.header_height, 1))
for i in range(1, ideal_seconds + 1)]
ideal_range = cellRangeStr(
(8, 1),
(ideal_seconds + 7, 2)
)
self.cell_range(ideal_range).Value = ideal_data
def _generate_et_formulas(self, row, col, endrow):
formulas = []
ref_cell = xladdress.cellStr(row, col, 1, 1)
for r in range(row, endrow + 1):
formula = "=(%s - %s)*24*60*60" % (xladdress.cellStr(r, col),
ref_cell)
# Excel demands data to be pasted as list tuples.
# List of rows. Ie, list[row][column].
formulas.append((formula,))
return formulas
def _make_named_ranges(self, wb, date_col):
# address range strings for first cell in x, f(x), and f(-ln(100-x)) columns
x_col_start_cell = cellStr(self.start_row, date_col + 1, 1, 1)
lin_col_start_cell = cellStr(self.start_row, date_col + 2, 1, 1)
ln_col_start_cell = cellStr(self.start_row, date_col + 3, 1, 1)
# build name & formula for named range. this is obnoxious.
# for the sake of readability, I have named them bob and fred.
# bob = x named range, fred = y named range
name_x = "__%d_x_%s"
name_y = "__%d_y_%s"
bob_name_ln = name_x % (date_col, "ln")
fred_name_ln = name_y % (date_col, "ln")
bob_name_lin = name_x % (date_col, "lin")
fred_name_lin = name_y % (date_col, "lin")
book_name = self.wb_name
sheet_name = "'%s'!" % self.ws_name
# offset(ref, rows, cols)
#
# Dynamic named ranges worth with the offset formula,
# but not with the indirect formula (which would make this
# much easier to read).
#
# This formula translates the x_col, y_col, start row, and end_row
# into ranges which correspond to the formulas listed in
# x_range and y_range.
named_range_formula = "=offset(%s%%s,%s%s-%d,0):" \
"offset(%s%%s,%s%s-%d,0)"
named_range_formula %= (sheet_name, sheet_name, self.m_addr(2, 1), self.start_row,
sheet_name, sheet_name, self.m_addr(3, 1), self.start_row)
bob_formula_ln = named_range_formula % (x_col_start_cell, x_col_start_cell)
fred_formula_ln = named_range_formula % (ln_col_start_cell, ln_col_start_cell)
bob_formula_lin = bob_formula_ln
fred_formula_lin = named_range_formula % (lin_col_start_cell, lin_col_start_cell)
add_name = wb.Names.Add
add_name(bob_name_ln, bob_formula_ln)
add_name(fred_name_ln, fred_formula_ln)
add_name(bob_name_lin, bob_formula_lin)
add_name(fred_name_lin, fred_formula_lin)
# these are exported for use in chart data series address strings
chart_form = "='%s'!%%s" % book_name
self.bob_chart_ln_form = chart_form % bob_name_ln
self.fred_chart_ln_form = chart_form % fred_name_ln
self.bob_chart_lin_form = chart_form % bob_name_lin
self.fred_chart_lin_form = chart_form % fred_name_lin
return self.bob_chart_ln_form, self.fred_chart_ln_form, self.bob_chart_lin_form, \
self.fred_chart_lin_form
def _insert_time_col(self, ws, cells, col):
end_row = cells(2, col - 1).End(xlDown).Row
ws.Columns(col).Insert(Shift=xlToRight)
formula = "=(%s-%s) * 24" % (cellStr(2, col - 1), cellStr(2, col - 1, 1, 1))
cells(2, col).Value = formula
fill_range = cellRangeStr(
(2, col), (end_row, col)
)
af_rng = cells.Range(fill_range)
cells(2, col).AutoFill(af_rng)
ws.Columns(col).NumberFormat = "0.00"
def _time_to_sp(ramp_test, col):
"""
Get time to setpoint by scanning backward through the reversed lists
for the first time that temp is *outside* the target.
return minutes elapsed
@param ramp_test: RampTestResult
@type ramp_test: RampTestResult
@param col: column of first column of test data.
@return: float
@rtype: float | int
"""
sp = ramp_test.set_point
ys = ramp_test.y_data
y_len = len(ys)
rev_data = reversed(ys)
end = next((i for i, pv in enumerate(rev_data) if abs(pv - sp) > 0.05), 1)
# if end was in middle of list, use the next index.
# if end was the last time in the list (0), bump it up
# to 1
if 0 == end:
end = 1
# +11 -> account for cell header rows
end_index = y_len - end + 11
magic = "=" + cellStr(end_index, col)
return magic
def _calc_last_80_forms(self, cc_end_row, cond_data, et_cc):
self.logger.debug("Finding average of last 80 seconds.")
ts_data = cond_data[0]
end = len(ts_data) - 1
last_pt = ts_data[-1]
for i in range(end, -1, -1):
if (last_pt - ts_data[i]).total_seconds() >= 80:
i += 1 # back up
break
else:
raise MixingTimeAnalysisError("Failed to find last 80 sec of data")
first_pv_cell = (i + 1, et_cc + 1)
last_pv_cell = (cc_end_row, et_cc + 1)
ave_last_80 = "=average(%s:%s)" % (xladdress.cellStr(*first_pv_cell),
xladdress.cellStr(*last_pv_cell))
return ave_last_80
def SeriesName(self):
""" If series name undefined, return formula
for contents of top cell of Y column.
Otherwise return series name.
"""
series_name = self.series_name
if not series_name:
name_row = max(self.start_row - 1, 1) # avoid negatives or 0
name = "=%s!" % self.sheet_name
name += cellStr(name_row, self.y_column)
else:
name = series_name
return name
def extract_ws_mfc_data(ws, name):
cells = ws.Cells
top_left = cells.Find(What=name, After=cells(1, 1), SearchOrder=const.xlByColumns)
tl_c = top_left.Column
tl_r = top_left.Row
right = top_left.End(const.xlToRight).Column + 1
bottom = tl_r + 4
data = []
for plus_row in range(bottom):
row_data = []
for col in range(tl_c, right):
formula = "=%s" % cellStr(tl_r + plus_row, col)
row_data.append(formula)
data.append(row_data)
return data
def _update_data_sheet(self, cells, report):
fleft, fright, fbottom = self._copy_data(cells, report.metadata.test_name)
named_ranges = _MakeNamedRanges(self._wb, self._data_sheet, self._cells,
3, fbottom + 1, self._current_col_data_ws)
ln_x, ln_y, lin_x, lin_y = named_ranges.get_ranges()
report.metadata.compiled_named_ranges = named_ranges
series_name = "='%s'!%s" % (self._data_sheet.Name, cellStr(1, self._current_col_data_ws))
# add LN chart
if self._ln_chart is None:
self._init_ln_chart()
CreateDataSeries(self._ln_chart, ln_x, ln_y, series_name)
# add linear chart
if self._linear_chart is None:
self._init_linear_chart()
CreateDataSeries(self._linear_chart, lin_x, lin_y, series_name)
self._current_col_data_ws += fright - fleft + 2 + 2 # +2 space, + 2 regression columns
def _make_series_info(column, start_row, rows, ramp_test):
"""
@param column: column
@type column: int
@param start_row: start_row
@type start_row: int
@param rows: number of rows
@type rows: int
@param ramp_test: the ramp test
@type ramp_test: RampTestResult
@return: series info
@rtype: ChartSeries
Helper function for process_tests to make chart series info.
"""
series_info = ChartSeries()
series_info.series_name = "=%s!" + cellStr(1, column + 1)
series_info.start_row = start_row
series_info.end_row = start_row + rows
series_info.x_column = column + 1
series_info.y_column = column + 2
series_info.chart_name = ramp_test.test_name
return series_info
def run_analysis(self):
"""
XXX This analysis routine assumes data collected using the batch
file method detailed in IP00043!
"""
# Initialize
self.logger.info("Beginning analysis on: %s", self.wb.Name)
ws = self.ws
cells = ws.Cells
# 7.4.3 - Find first logger max log interval timestamp
logger_timestamp = self._find_logger_ts_1k(cells)
# 7.4.3 - Elapsed Time column for conductivity
first_data_row, et_col, cc_end_row, cond_data = self._add_cond_et_col(cells, logger_timestamp)
et_cc = et_col.Column
# 7.4.4 - Graph time vs conductivity pv
chart = self._add_raw_chart(cc_end_row, et_cc, first_data_row, ws)
# 7.4.5 - Add initial conductivity, final ave conductivity over 80 sec
free_col = cells.Columns(et_cc + 3)
for _ in range(5):
free_col.Insert()
# 7.4.5.1 - Conductivity at t = 0 + header
cells(1, et_cc + 3).Value = "Conductivity (T=0)"
cells(2, et_cc + 3).Value = "=" + xladdress.cellStr(first_data_row,
et_cc + 1)
cells.Columns(et_cc + 3).AutoFit()
# 7.4.5.2 - final conductivity last 80 sec of batch
ave_last_80 = self._calc_last_80_forms(cc_end_row, cond_data, et_cc)
cells(1, et_cc + 4).Value = "Average last 80 sec"
cells(2, et_cc + 4).Value = ave_last_80
cells.Columns(et_cc + 4).AutoFit()
# 7.4.5.3 - calculate 5% of final - initial conductivity
cells(1, et_cc + 5).Value = "5% of (Final - Initial)"
cells(2, et_cc + 5).Value = "=0.05*(%s-%s)" % (xladdress.cellStr(2, et_cc + 4),
xladdress.cellStr(2, et_cc + 3))
cells.Columns(et_cc + 5).AutoFit()
# 7.4.6 - highlight first measurement within 5% of final,
# provided that no subsequent points are not within 5% of final
row_pv95 = self._find_pv95(cells, cond_data, et_cc)
t95_cell = xladdress.cellStr(row_pv95, et_cc - 1)
lowest_addr = cells(first_data_row, et_cc - 1).Address
# 7.4.7 - time from start of batch to t95
cells(1, et_cc + 6).Value = "T95"
cells(2, et_cc + 6).Value = "=(%s - %s)*24*60*60" % (t95_cell, lowest_addr)
cells(2, et_cc + 6).NumberFormat = "0.0"
self.t95 = cells(2, et_cc + 6).Value
# Format chart axes scale
ymin = math.floor(cells(2, et_cc + 3).Value2 * 0.9 * 10) / 10
ymax = math.ceil(cells(2, et_cc + 4).Value2 * 1.1 * 10) / 10
xlcom.FormatAxesScale(chart, None, None, ymin, ymax)
# t95 indicator
cells(5, et_cc+6).Value = self.t95
cells(6, et_cc+6).Value = self.t95
cells(5, et_cc+7).Value = ymin
cells(6, et_cc+7).Value = ymax
x_rng, y_rng = xladdress.chart_range_strs(et_cc+6, et_cc+7,
5, 6, ws.Name)
series = xlcom.CreateDataSeries(chart, x_rng, y_rng)
# Format as black dotted line. I don't know which
# of these are actually necessary, but it works
# as is, which is good enough for me.
series.MarkerStyle = -4142 # Not visible (from VBA recording)
series.Format.Line.DashStyle = 11 # rounded dot
series.Format.Line.ForeColor.RGB = 0 # black
series.Format.Line.BackColor.RGB = 16777215
series.Format.Line.Style = 1
series.Format.Line.Transparency = 0.0
series.Format.Line.Weight = 1.5 # dash width
return
def m_addr(self, row, col, abs=True):
"""
@rtype: str
"""
row, col = self._m_translate_coord(row, col)
return cellStr(row, col, abs, abs)
