def on_timer(timer_id, unused):
# when Excel is starting up and PyXLL imports its modules there
# is no Excel window, and so the COM object needed to register
# the shortcut might not be available yet.
try:
xl_window = get_active_object()
except RuntimeError:
if time.time() - start_time > timeout:
_log.error("Timed out waiting for Excel COM interface to become available.")
timer.kill_timer(timer_id)
_timer_id = None
return
# Get the Excel application from its window
xl_app = Dispatch(xl_window).Application
while _shortcuts_to_add:
accelerator, macroname = _shortcuts_to_add.pop()
try:
xl_app.OnKey(accelerator, macroname)
except:
_log.error("Failed to add shortcut %s -> %s" %
(accelerator, macroname), exc_info=True)
_log.debug("Finished adding shortcuts")
timer.kill_timer(timer_id)
_timer_id = None
def start_subprocess(target_address):
"""
Start a sub-process that will write back to a cell.
:param target_address: address of cell to write to from the child process.
"""
# Get the window handle of the Excel process so the sub-process can
# find the right Excel Application instance.
xl_app = win32com.client.Dispatch(get_active_object()).Application
os.environ["PYXLL_EXCEL_HWND"] = str(xl_app.Hwnd)
# Get the log file name for the subproces to log to.
root = logging.getLogger()
logfile = None
for handler in root.handlers:
if isinstance(handler, logging.FileHandler):
logfile = handler.baseFilename
break
# Start the subprocess that will write back to the target cell.
# It's a daemon process so that it doesn't stop the main Excel process
# from terminating even if it's still running.
process = Process(target=_subprocess_func, args=(target_address, logfile))
process.daemon = True
process.start()
return "Child process %d started" % process.pid
def xl_app():
"""returns a Dispatch object for the current Excel instance"""
# get the Excel application object from PyXLL and wrap it
xl_window = get_active_object()
xl_app = win32com.client.Dispatch(xl_window).Application
# it's helpful to make sure the gen_py wrapper has been created
# as otherwise things like constants and event handlers won't work.
win32com.client.gencache.EnsureDispatch(xl_app)
return xl_app
def resize_array_formula():
"""
Recalculates and resizes a range to show all the results of a formula.
"""
xl_window = get_active_object()
xl = Dispatch(xl_window).Application
selection = xl.Selection
formula = selection.FormulaArray
if not (formula and (formula.startswith("=") or formula.startswith("+"))):
# nothing to do
return
# get the range of the entire formula
current_range = _expand_range(xl, selection)
# evaluate the formula to get the dimensions of the result
# (this is an optimization to avoid converting the range into a python list)
# result = xl.Evaluate(formula)
result = xl._oleobj_.InvokeTypes(1, 0, 1, (12, 0), ((12, 1), ), formula)
width, height = 0, len(result)
if height > 0 and isinstance(result[0], (list, tuple)):
width = len(result[0])
width, height = max(width, 1), max(height, 1)
new_range = xl.Range(current_range.Offset(1, 1),
current_range.Offset(height, width))
# check if we're overwriting any existing data
if new_range.Count > current_range.Count:
current_non_blank = current_range.Count - xl.WorksheetFunction.CountBlank(
current_range)
new_non_blank = new_range.Count - xl.WorksheetFunction.CountBlank(
new_range)
if current_non_blank != new_non_blank:
new_range.Select()
result = win32api.MessageBox(
None, "Content will be overwritten in %s" % new_range.Address,
"Warning", win32con.MB_OKCANCEL | win32con.MB_ICONWARNING)
if result == win32con.IDCANCEL:
current_range.FormulaArray = formula
return
# clear the old range
current_range.ClearContents()
# set the formula on the new range
try:
new_range.FormulaArray = formula
except Exception:
result = win32api.MessageBox(
None, "Error resizing range", "Error",
win32con.MB_OKCANCEL | win32con.MB_ICONERROR)
_log.error("Error resizing range", exc_info=True)
current_range.FormulaArray = formula
def resize_array_formula():
"""
Recalculates and resizes a range to show all the results of a formula.
"""
xl_window = get_active_object()
xl = Dispatch(xl_window).Application
selection = xl.Selection
formula = selection.FormulaArray
if not (formula and (formula.startswith("=") or formula.startswith("+"))):
# nothing to do
return
# get the range of the entire formula
current_range = _expand_range(xl, selection)
# evaluate the formula to get the dimensions of the result
# (this is an optimization to avoid converting the range into a python list)
# result = xl.Evaluate(formula)
result = xl._oleobj_.InvokeTypes(1, 0, 1, (12, 0), ((12, 1),), formula)
width, height = 0, len(result)
if height > 0 and isinstance(result[0], (list, tuple)):
width = len(result[0])
width, height = max(width, 1), max(height, 1)
new_range = xl.Range(current_range.Offset(1, 1),
current_range.Offset(height, width))
# check if we're overwriting any existing data
if new_range.Count > current_range.Count:
current_non_blank = current_range.Count - xl.WorksheetFunction.CountBlank(current_range)
new_non_blank = new_range.Count - xl.WorksheetFunction.CountBlank(new_range)
if current_non_blank != new_non_blank:
new_range.Select()
result = win32api.MessageBox(None,
"Content will be overwritten in %s" % new_range.Address,
"Warning",
win32con.MB_OKCANCEL | win32con.MB_ICONWARNING)
if result == win32con.IDCANCEL:
current_range.FormulaArray = formula
return
# clear the old range
current_range.ClearContents()
# set the formula on the new range
try:
new_range.FormulaArray = formula
except Exception:
result = win32api.MessageBox(None,
"Error resizing range",
"Error",
win32con.MB_OKCANCEL | win32con.MB_ICONERROR)
_log.error("Error resizing range", exc_info=True)
current_range.FormulaArray = formula
def recalc_selection():
"""
Recalculates selection
"""
xl_window = get_active_object()
xl = Dispatch(xl_window).Application
selection = xl.Selection
selection.Dirty()
selection.Calculate()
def recalc_selection():
"""
Recalculates selection
"""
xl_window = get_active_object()
xl = Dispatch(xl_window).Application
selection = xl.Selection
selection.Dirty()
selection.Calculate()
def _xl_app():
"""returns a Dispatch object for the current Excel instance"""
# get the Excel application object from PyXLL and wrap it
xl_window = pyxll.get_active_object()
xl_app = win32com.client.Dispatch(xl_window).Application
# it's helpful to make sure the gen_py wrapper has been created
# as otherwise things like constants and event handlers won't work.
win32com.client.gencache.EnsureDispatch(xl_app)
return xl_app
def py_rtd_test(seconds):
xl = win32com.client.Dispatch(get_active_object()).Application
return xl.WorksheetFunction.RTD(TimeServer._reg_progid_, "", "seconds", str(seconds))
def py_rtd_test(seconds):
xl = win32com.client.Dispatch(get_active_object()).Application
return xl.WorksheetFunction.RTD(TimeServer._reg_progid_, "", "seconds", str(seconds))
def xl_app():
xl_window = get_active_object()
xl_app = win32com.client.Dispatch(xl_window).Application
return xl_app
def xl_app():
xl_window = get_active_object()
xl_app = win32com.client.Dispatch(xl_window).Application
return xl_app
def Simulate():
global num_of_sim
global op
global num_of_inp
global iter_num
global current_inp
global inpcell
global output_flag
global usr_selection
global inparraycell
global formstr
global arrayvals
global simcomplete
global inp
xlcCalculateNow()
xlcCalculateDocument()
if not output_flag:
xlcAlert("Select a output cell. If selected, click reload")
return
#
checkMIArray=False
win32com.client.gencache.Rebuild()
xl_window = get_active_object()
xl_app = win32com.client.Dispatch(xl_window).Application
xl_app.Calculation=xlCalculationManual
# xl_app = win32com.client.GetActiveObject("Excel.Application")
# win32com.client.gencache.EnsureDispatch(xl_app)
num_of_sim = 0
if ((inpcell is not None) and formstr == str(inpcell.formula) ):
checkMIArray=True
# xlcAlert('inside looop:'+ str(inp).strip('[]'))
rect = inpcell.rect
# xlcAlert(inpcell.address)
# xlcAlert(str(rect.first_row))
# xlcAlert(str(rect.first_col))
selection = xl_app.ActiveSheet.Cells(int(rect.first_row)+1,int(rect.first_col)+1)
# selection.value = 100
# inpcell.value = 100
# xlcAlert("click OK")
app1 = QApplication(sys.argv)
form = Dialog.popup()
app1.exec_()
num_of_sim = int(Dialog.retval())
usr_selection = str(Dialog.retsel())
writeFG = Dialog.writeflag()
if num_of_sim > 0:
current_inp =1
# xlcAlert(str(num_of_sim))
start = time.time()
op=None
op = zeros((num_of_inp,num_of_sim))
xl_app.ScreenUpdating = False
xl_app.DisplayStatusBar = False
xl_app.EnableEvents = False
for j in range(num_of_inp):
current_inp = j
if (checkMIArray):
selection.Value = inp[current_inp]
for i in range(num_of_sim):
iter_num = i
xlcCalculateDocument()
end = time.time()
xl_app.ScreenUpdating = True
xl_app.DisplayStatusBar = True
xl_app.EnableEvents = True
current_inp = 0
simcomplete= True
# selection.value = inp[current_inp]
# selection.Formula = inpcell.formula
if checkMIArray:
fstr = '=MiInput(' + inparraycell.address + ')'
selection.Formula = fstr
# xlcAlert(str(checkMIArray))
if not checkMIArray:
inp = [0]
UI.draw(op, inp,usr_selection)
# Store data in a CSV format
if checkMIArray:
popupstr = ''
for idx,inpvalue in enumerate(inp):
tupleop = None
tupleop = tuple(op[idx])
datastr = "data" + str(idx)
xl_app.ActiveWorkbook.Names.Add(datastr,tupleop,False)
popupstr = popupstr + "Output variable for Input " + str(inpvalue) + "is: "+str(datastr) + "\n"
popupstr = popupstr + "You can use all the excel statistical functions on these variables"
xlcAlert(popupstr)
else:
tupleop = tuple(op)
xl_app.ActiveWorkbook.Names.Add("data",tupleop,False)
xlcAlert("Your Output Variable is 'data'" + "\n" + "You can use all the excel statistical functions on this variable")
if writeFG:
config = get_config()
config.read('pyxll.cfg')
dir_path = config.get('LOG','path')
xlcAlert("Data stored at "+str(dir_path))
file_name = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
if checkMIArray:
# xlcAlert(str(len(op[1])))
for idx,inpvalue in enumerate(inp):
file_name1 = file_name +"-input "+str(inpvalue) +'.csv'
if os.path.exists(dir_path):
myfile = open(os.path.join(dir_path, file_name1), 'wb')
wr = csv.writer(myfile, dialect='excel')
wr.writerow(op[idx])
else:
if os.path.exists(dir_path):
file_name = file_name + '.csv'
myfile = open(os.path.join(dir_path, file_name), 'wb')
wr = csv.writer(myfile, dialect='excel')
wr.writerows(op)
