def get_xl_data2():
from officelib.xllib.xlcom import xlBook2
from officelib.const import xlDown
from pysrc.snippets import smooth1
import re
from itertools import takewhile
xl, wb = xlBook2('PID.xlsx')
ws = wb.Worksheets('p30')
cells = ws.Cells
# columns = (5, 9, 13, 17, 21, 25, 29)
columns = range(2, 19, 4)
parse_name = re.compile(r"p(\d*)i([\d\.]*)").match
all_dat = []
ap = all_dat.append
row = 2
def good(o):
return o != (None, None)
for col in columns:
data = cells.Range(cells(row, col), cells(row, col + 1).End(xlDown)).Value
name = cells(1, col + 1).Value
xldata = takewhile(good, data)
x, y = zip(*xldata)
x_data, y_data = smooth1(x, y)
p, i = parse_name(name).groups()
ap(xlData(name, x_data, y_data, p, i))
return all_dat
def plotpid2(n=15000):
import sys
try:
del sys.modules['scripts.run.temp_sim']
except KeyError:
pass
from scripts.run.temp_sim import TempSim, PIDController
sim = TempSim(D('28.183533'), 19, 0)
pid = PIDController(37, 40, D('0.55'))
# pid = PIDController(37, 40, 0.5)
pid.off_to_auto(sim.current_temp)
pv = sim.current_temp
#: @type: list[tuple[D]]
steps = [('0', '0', '0', '0', '0') for _ in range(11)]
for _ in range(n):
hd = pid.step_output(pv)
t, pv = sim.step_heat(hd)
steps.append((t, pv, pid.last_error, pid.accumulated_error, hd))
from officelib.xllib.xlcom import xlBook2
xl, wb = xlBook2('PID.xlsx')
ws = wb.Worksheets(2)
cells = ws.Cells
firstcol = 30
cells.Range(cells(1, firstcol), cells(1, firstcol + 4)).Value = (
"Time", "Temp", "LastErr", "AccumErr", "HeatDuty%")
cells.Range(cells(2, firstcol), cells(len(steps) + 1, len(steps[0]) + firstcol - 1)).Value = \
[tuple(map(str, data)) for data in steps]
def get_xl_data():
from officelib.xllib.xlcom import xlBook2
from officelib.const import xlDown
from itertools import dropwhile
import re
xl, wb = xlBook2('PID.xlsx')
ws = wb.Worksheets(3)
cells = ws.Cells
columns = (5, 9, 13, 17, 21, 25, 29)
def bad(tpl):
x, y = tpl
return x is None or y < 28
parse_name = re.compile(r"p(\d*)i([\d\.]*)").match
all_dat = []
ap = all_dat.append
row = 2
for col in columns:
xldat = cells.Range(cells(row, col), cells(row, col + 1).End(xlDown)).Value
name = cells(1, col + 1).Value
data = dropwhile(bad, xldat)
x, y = tuple(zip(*data))
x_data, y_data = smooth1(x, y)
p, i = parse_name(name).groups()
ap(xlData(name, x_data, y_data, p, i))
return all_dat
def extract_data():
xl, wb = xlcom.xlBook2("M3-TR-3-004a.xlsx")
rv = []
for n in (0, 1, 2):
name = "C-20 %d" % n
ws = wb.Worksheets(name)
data = extract_ws_data(ws)
rv.append((name, data))
return rv
def supermath():
from officelib.xllib.xlcom import xlBook2
xl, wb = xlBook2("PID.xlsx")
ws = wb.Worksheets(2)
cells = ws.Cells
xl.Visible = False
n = 5400
# use excel to do regression work for us.
ref_m, ref_b = get_regression(cells, 2, 20)
refdata = [ref_m * i + ref_b for i in range(1, n + 1)] # start at 1
print("ref m: %.6f" % ref_m, "ref b: %.6f" % ref_b)
c = D('-0.0000615198895')
start_temp = D('37.04')
best_diff = 9999999999
m = b = None
try:
while True:
steps = run_decay_test(n, start_temp, c)
strsteps = [(str(t), str(v)) for t, v in steps]
ws = wb.Worksheets(3)
cells = ws.Cells
cell_range = cells.Range
ls = len(steps)
cell_range(cells(1, 1), cells(ls, 2)).Value = strsteps
m, b = get_regression(cells, 1, 1)
print("c: %s" % c, "m: %.8f" % m, "b: %.8f" % b)
pvs = [tpl[1] for tpl in steps]
diffs = map(D.__sub__, refdata, pvs)
totaldiff = sum(diffs)
if totaldiff < best_diff:
best_diff = totaldiff
else:
start_temp += totaldiff / len(pvs)
print("totaldiff: %.8f" % totaldiff)
c = (ref_m / m) * c
# start_temp += ref_b - b
except:
return c, m, b, start_temp
finally:
xl.Visible = True
return c, m, b, start_temp
def from_csv(cls, csv, name=None, logger=None, cvar='pHARaw'):
"""
@param csv: filename of csv file
@param logger: Logger
Instantize class from filename of raw batch file.
Enter function at IP00043 Rev A 7.4.2
"""
xl, wb = xlcom.xlBook2(csv)
if name is None:
name = _get_filename(csv)
return cls(name, xl, wb, logger, cvar)
def get_m():
from cli_data.cli import delsim
delsim()
from scripts.run.temp_sim import TempSim
from officelib.xllib.xlcom import xlBook2
# from officelib.xllib.xladdress import cellRangeStr
from time import sleep
# from pysrc.snippets import smooth1
xl, wb = xlBook2('PID.xlsx')
h = TempSim.DEFAULT_HEAT_CONSTANT
cells = wb.Worksheets('xtra').Cells
# refdata = get_paired_cols(cells.Range('E2'))
# refx, refy = zip(*refdata)
# refx, refy = smooth1(refx, refy)
# refx = refx[:]
incr = h / 20
plotcell = cells.Range('J2')
cell2 = cells.Range('L2')
ref_m = D('0.005497')
b_cell = cells.Range("M2")
linest_range = cells.Range(cell2, b_cell)
linest_formula = "=linest(%s,%s)"
xcol = 10
ycol = 11
linest_range.Value = ((0.002, 0),)
while D(cell2.Value) < ref_m:
print("Plotting data with h=%s" % h)
plot_m(h, linest_formula, linest_range, plotcell, xcol, ycol)
sleep(0.2)
h += incr
incr /= 10
while D(cell2.Value) > ref_m:
print("Plotting data with h=%s" % h)
plot_m(h, linest_formula, linest_range, plotcell, xcol, ycol)
sleep(0.2)
h -= incr
return h
def plotxl(data, firstcol=1, firstrow=2,
y_header="SimData", wb_name="PID.xlsx", ws_id=3):
from officelib.xllib.xlcom import xlBook2
xl, wb = xlBook2(wb_name)
ws = wb.Worksheets(ws_id)
cells = ws.Cells
startcell = cells(firstrow, firstcol)
endcell = cells(len(data) + firstrow - 1, firstcol + len(data[0]) - 1)
if y_header and firstrow > 1:
cells(firstrow - 1, firstcol + 1).Value = y_header
cells.Range(startcell, endcell).Value = data
return startcell, endcell
def manysuper3():
d = 0
last = 99999999999
totaldiff = 0
from officelib.xllib.xlcom import xlBook2
from cli_data.cli import supermath3
xl, wb = xlBook2("PID.xlsx")
cell = wb.Worksheets(1).Cells.Range("C8")
try:
while True:
totaldiff = supermath3(d)
cell.Value = d
d += 10
except KeyboardInterrupt:
pass
return d, totaldiff
def __init__(self, name=None, xl=None, wb=None, logger=None, saveas_info=None, tests_per_group=3):
if xl or wb:
if xl:
wb = xl.Workbooks.Add()
elif wb:
xl = wb.Application
else:
xl, wb = xlcom.xlBook2()
self.xl = xl
self.wb = wb
self.ws = wb.Worksheets(1)
self.init_ws()
self.name = name or "Compiled Data"
self.logger = logger or BuiltinLogger(self.__class__.__name__, logging.INFO)
self.tests = []
self.compiled_tests = 0
self.tests_per_group = tests_per_group
self.saveas_info = saveas_info or CompiledMixingSaveAsInfo("C:/.replcache/mixing test/")
def plotsim(n=7200, c=D('-0.00004679011328')):
try:
import sys
del sys.modules['scripts.run.temp_sim']
except KeyError:
pass
from scripts.run.temp_sim import TempSim
sim = TempSim(D('37.115'), 19, 0, c)
steps = sim.iterate(n)
from officelib.xllib.xlcom import xlBook2
xl, wb = xlBook2('PID.xlsx')
ws = wb.Worksheets(2)
cells = ws.Cells
firstcol = 19
cells.Range(cells(2, firstcol), cells(len(steps) + 1, firstcol + 1)).Value = [(str(t), str(v)) for t, v in steps]
def xl_2_data():
from officelib.xllib.xlcom import xlBook2, HiddenXl
from officelib.xllib.xladdress import cellStr
fpath = "C:\\users\\public\\documents\\pbsss\\temp pid\\pbs 3 thick sleeve\\compiled.xlsx"
xl, wb = xlBook2(fpath)
ws = wb.Worksheets("ProcedureEx")
#: @type: typehint.Range
cells = ws.Cells
found = set()
# tests = []
def cell_hash(cell):
to_hash = (cell.Row, cell.Column)
print(to_hash)
return hash(to_hash)
with HiddenXl(xl):
match = cells.Find("Name:", cells(1, 1), SearchOrder=xlByRows)
h = cell_hash(match)
found.add(h)
tests = [get_test_data(match)]
while True:
match = cells.FindNext(match)
h = cell_hash(match)
if h in found:
break
else:
found.add(h)
test = get_test_data(match)
tests.append(test)
return tests
def move_data():
data = extract_data()
xl, wb = xlcom.xlBook2("M3-TR-3-004a.xlsx")
for name, ws_data in data:
move_ws_data(wb, name, data)
