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)