Esempio n. 1
0
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)
Esempio n. 2
0
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
Esempio n. 3
0
    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
Esempio n. 4
0
    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
Esempio n. 5
0
 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
Esempio n. 6
0
    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"
Esempio n. 7
0
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
Esempio n. 8
0
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
Esempio n. 9
0
 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)
                                                 )
Esempio n. 10
0
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
Esempio n. 11
0
 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)),
     )
Esempio n. 12
0
    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!")