def MakeChart(xl, reactor_name, filename, export_by): wb = xl.Workbooks.Open(filename) ws = wb.Worksheets(1) cells = ws.Cells target_cell = FindTempPV(cells) time_col = target_cell.Column pv_col = time_col + 1 end_row = target_cell.End(xlDown).Row x_data = cellRangeStr((2, time_col, 1, 1), (end_row, time_col, 1, 1)) y_data = cellRangeStr((2, pv_col, 1, 1), (end_row, pv_col, 1, 1)) chart = CreateChart(ws) SeriesCollection = chart.SeriesCollection() for i in range(SeriesCollection.Count, 0, -1): SeriesCollection(i).Delete() Series = SeriesCollection.NewSeries() cell_range = cells.Range Series.XValues = cell_range(x_data) Series.Values = cell_range(y_data) ChartTitle = " ".join((reactor_name, "Export by", export_by)) XAxisTitle = "Time(date)" YAxisTitle = "TempPV" FormatChart(chart, ChartTitle=ChartTitle, xAxisTitle=XAxisTitle, yAxisTitle=YAxisTitle) chart.Location(xlLocationAsNewSheet)
def get_regression(cells, startrow, startcol): from officelib.xllib.xladdress import cellRangeStr from officelib.const import xlDown end_row = cells(startrow, startcol).End(xlDown).Row xrange = cellRangeStr((startrow, startcol), (end_row, startcol)) yrange = cellRangeStr((startrow, startcol + 1), (end_row, startcol + 1)) linest_formula = "=linest(%s, %s)" % (yrange, xrange) cells.Range(cells(startrow, startcol + 2), cells(startrow, startcol + 3)).FormulaArray = linest_formula m = cells(startrow, startcol + 2).Value2 b = cells(startrow, startcol + 3).Value2 m = D(m) b = D(b) return m, b
def create_control_header(self): """ Create the control header in the first three columns of the worksheet. """ control_header = ( ("Control", None, None), (None, None, None), ('Ave_m', 'Ave_b', 'offset'), (None, None, None), ('SP', None, None), (self.TestSP, None, None), ("Ideal Time", "Ideal Temp", None) ) self.header_width = max([len(i) for i in control_header]) self.header_height = len(control_header) header_range = cellRangeStr( (1,1), (self.header_height, self.header_width) ) self.cell_range(header_range).Value = control_header
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 _plot_test(self, row, column, test): header = test.RawHeader() data = test.RawData() rows, columns = test.Size() end_col = column + columns - 1 header_range = cellRangeStr( (1, column), (len(header), end_col)) data_range = cellRangeStr( (len(header) + 1, column), (rows, end_col)) self.cell_range(header_range).Value = header self.cell_range(data_range).Value = data
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 plot_m(h, linest_formula, linest_range, plotcell, xcol, ycol): from scripts.run.temp_sim import TempSim from officelib.xllib.xladdress import cellRangeStr sim = TempSim(28, 19, 50, heat_constant=h) data = sim.iterate(30000) xs, ys = zip(*data) start = next(i for i, pv in enumerate(ys) if pv > 30) end = next((i for i, pv in enumerate(ys, start) if pv > 36), len(ys) - 1) assert ys[start] > 30 assert ys[end] > 36 xldata = [(str(x), str(y)) for x, y in zip(xs[start:end], ys[start:end])] plotxl_by_cell(xldata, plotcell) linest_args = linest_formula % ( cellRangeStr( (2, ycol), (end - start, ycol) ), cellRangeStr( (2, xcol), (end - start, xcol) ) ) # print(linest_args) linest_range.FormulaArray = linest_args
def plot_tests(header, column_data, chart_series_list, header_row_offset=0): """ @param header: iterable of tuples of header data to plot @type header: collections.Sequence[collections.Sequence] @param column_data: iterable of tuples of column data to plot @type column_data: collections.Sequence[collections.Sequence] @param chart_series_list: iterable of ChartSeries instances to plot @type chart_series_list: collections.Sequence[ChartSeries] @param header_row_offset: move the data down by this # of rows @type header_row_offset: int @return: workbook @rtype: officelib.xllib.typehint.th0x1x6._Workbook._Workbook Plot all the tests. Header and column data should correspond. Function isn't private but is probably very hard to use outside of being called automatically by other module functions. """ data_row_offset = len(header) + header_row_offset rows = len(column_data) columns = len(column_data[0]) data_area = cellRangeStr( (1 + data_row_offset, 1), (rows + data_row_offset, columns) ) header_area = cellRangeStr( (1 + header_row_offset, 1), (len(header) + header_row_offset, columns) ) # Because importing xllib requires importing win32com, and # win32com can be very slow to initialize, wait until here to # do the import, so that any errors thrown will abort analysis # before getting to this point. from officelib.xllib.xlcom import xlObjs, CreateDataSeries, HiddenXl from officelib.const import xlLocationAsNewSheet xl, wb, ws, cells = xlObjs(visible=False) with HiddenXl(xl): ws_name = ws.Name cells.Range(header_area).Value = header cells.Range(data_area).Value = column_data chart_map = _prepare_charts(ws, chart_series_list) for series in chart_series_list: # Set these to get proper formula from SeriesRange properties series.sheet_name = ws_name series.series_name = series.series_name % ws_name chart = chart_map[series.chart_name] CreateDataSeries(chart, series.XSeriesRange, series.YSeriesRange, series.SeriesName) for name, chart in chart_map.items(): chart.Location(Where=xlLocationAsNewSheet, Name=name) return wb
def YSeriesRange(self, cellRangeStr=cellRangeStr): return "=%s!" % self.sheet_name + cellRangeStr( (self.start_row, self.y_column, 1, 1), (self.end_row, self.y_column, 1, 1) )
def tpid_eval_full_scan(data_file, steps_file, time_offset=0): """ @param data_file: data report filename @type data_file: str @param steps_file: steps report filename @type steps_file: str @return: @rtype: """ data = full_open_data_report(data_file) steps = full_open_eval_steps_report(steps_file) test_list = tpid_eval_data_scan(data, steps, time_offset) # Plot data and make chart, then take wb obj # and do a bit more work wb = process_tests(test_list) ws = wb.Worksheets(1) cell_range = ws.Cells.Range chart = wb.Charts(1) from officelib.xllib.xlcom import FormatAxesScale, HiddenXl from officelib.xllib.xladdress import cellRangeStr FormatAxesScale(chart, None, None, 29, 39.2) # Cleanup on header because we piggyback on process_tests() # to do our heavy lifting, but func was not designed with # eval test in mind. with HiddenXl(wb.Application): for i, test in enumerate(test_list): col = 2 + i * 5 name_rng = cellRangeStr( (1, col), (1, col)) minmax_pv_rng = cellRangeStr( (3, col), (3, col + 1)) start_tmp_rng = cellRangeStr( (5, col), (5, col + 1)) overshoot_rng = cellRangeStr( (4, col), (4, col + 1)) time_to_sp_rng = cellRangeStr( (2, col), (2, col + 1)) if test.start_temp < test.set_point: minmax_pv = max(test.y_data) minmax_lbl = "Max PV:" else: minmax_pv = min(test.y_data) minmax_lbl = "Min PV:" start_temp = test.start_temp time_to_sp = _time_to_sp(test, col) cell_range(name_rng).Value = '=CONCATENATE("Start:", R[4]C[%d],"SP:", R[5]C[%d])' % (col, col) cell_range(time_to_sp_rng).Formula = ("Min to SP:", time_to_sp) cell_range(minmax_pv_rng).Value = (minmax_lbl, minmax_pv) cell_range(overshoot_rng).Value = ("Overshoot:", minmax_pv - test.set_point) cell_range(start_tmp_rng).Value = ("Start Temp:", start_temp) return wb
def ySeriesRange(self, cellRangeStr=cellRangeStr): return "=%s!%s" % ( self.sheet_name, cellRangeStr((self.start_row, self.y_column, 1, 1), (self.end_row, self.y_column, 1, 1)), )
def PlotData(self, xlctxt, data): """Take _data from input _data, do the actual work of plotting in excel @param xlctxt: context object (tuple) containing references to the relevant workbook, worksheet, and worksheet's cells object. @param data: data dictionary from successful CalibrationPrompt dialog But, as long as dictionary has a 'Data' key, it will attempt to get values. """ _xl, wb, ws, cells = xlctxt # xl unused # Extract _data with default get values reactor_name = data.get('Name', '') model_size = data.get('Size', '') printout = data.get('Printout', False) test_type = data.get('Type', '') ylabel = data.get('yLabel', 'y Value') xlabel = data.get('xLabel', 'x Value') save_folder_base = data.get('SaveFolder', FUNC_TEST_FOLDER) cal_data = data['Data'] data_points = len(cal_data) # Setup template, minus chart self.setup_cal_template(cells, data_points, reactor_name, test_type, xlabel, ylabel) cell_range = cells.Range x_col = 2 y_col = 3 start_row = 5 end_row = 4 + data_points abs_ = True # absolute address eg $A1 # Address strings y_range = cellRangeStr( (start_row, y_col, abs_, abs_), (end_row, y_col, abs_, abs_)) x_range = cellRangeStr( (start_row, x_col, abs_, abs_), (end_row, x_col, abs_, abs_)) data_range = ':'.join(( x_range, y_range)) cell_range(data_range).Value = cal_data chart = CreateChart(ws, Top=(15 * (4.5 + data_points)), # sizes in pixels Left=(64 * XL_PIXEL_TO_POINT), Height=(14.5 * 15), Width=(64 * XL_PIXEL_TO_POINT * 7.5)) try: chart.SeriesCollection(1).Delete() # sometimes xl adds a series for no reason except: pass Series = chart.SeriesCollection().NewSeries() Series.Values = cell_range(y_range) Series.XValues = cell_range(x_range) FormatChart(chart, Legend=False, ChartTitle=(reactor_name + " " + test_type), Trendline=True, xAxisTitle=xlabel, yAxisTitle=ylabel) save_path = ''.join((save_folder_base, "\\", model_size, "\\", reactor_name)) try: makedirs(save_path) except OSError: # dir already exists. pass # Sanitize reactor_name, test_type if they # contain invalid filename characters. # reference: http://stackoverflow.com/questions/295135/turn-a-string-into-a-valid-filename-in-python # Use a more conservative (olny alphanumeric) magic_re # if not on windows, because I don't know what # chars are not allowed on other filesystems. if os_name == 'nt': magic_re = r"[\/:*?\"<>|]+" else: magic_re = r"[^\s\w.]+" safe_reactor_name = re_sub(magic_re, '', reactor_name) safe_test_name = re_sub(magic_re, '', data['Type']) if not safe_reactor_name: safe_reactor_name = "PBS Bioreactor" if not safe_test_name: safe_test_name = "Test" filename = ''.join((save_path, "\\", safe_reactor_name, " ", safe_test_name, '.xlsx' )) filename = unique_name(filename) wb.SaveAs(Filename=filename) if printout: ws.PrintOut() print("Printout!")