def stop_kernels():
"""Starts the remote Jupyter kernels"""
km = KernelManager.instance()
loop = get_event_loop()
f = asyncio.run_coroutine_threadsafe(km.stop_all_kernels(), loop)
f.result()
xlcAlert("Jupyter kernel stopped")
def on_reload(import_info):
"""
on_reload is registered to be called by PyXLL whenever a
reload occurs via the xl_on_reload decorator.
"""
# check to see which modules didn't import correctly
errors = []
for modulename, module, exc_info in import_info:
if module is None:
exc_type, exc_value, exc_traceback = exc_info
errors.append("Error loading '%s' : %s" % (modulename, exc_value))
if errors:
# report any errors to the user
if win32api:
win32api.MessageBox(0,
"\n".join(errors) + "\n\n(See callbacks.py example)",
"PyXLL Callbacks Example",
win32con.MB_ICONWARNING)
else:
_log.info("callbacks.on_reload: " + "\n".join(errors))
else:
# report everything reloaded OK
xlcAlert("PyXLL Reloaded OK\n(See callbacks.py example)")
# recalcuate all open workbooks
xlcCalculateNow()
def stop_profiling():
"""Stop the cProfile profiler and print the results"""
global _active_cprofiler
if not _active_cprofiler:
xlcAlert("No active profiler")
return
_active_cprofiler.disable()
# print the profiler stats
stream = StringIO()
stats = pstats.Stats(_active_cprofiler,
stream=stream).sort_stats("cumulative")
stats.print_stats()
# print the results to the log
print(stream.getvalue())
# and copy to the clipboard
win32clipboard.OpenClipboard()
win32clipboard.EmptyClipboard()
win32clipboard.SetClipboardText(stream.getvalue())
win32clipboard.CloseClipboard()
_active_cprofiler = None
xlcAlert("cProfiler Stopped\n"
"Results have been written to the log and clipboard.")
def popup_messagebox():
xlcAlert("Hello")
xl = xl_app()
tempRange = 'B1'
tempRange2 = 'E2'
tempRange3 = 'A6:B7'
searchRange = 'A1:D4'
x = pd.DataFrame({"A": ['Jim', 6], "B": ['Paula', 9]}, index=None)
arr_x = x.to_numpy()
# 'xl' is an instance of the Excel.Application object
# Get the current ActiveSheet (same as in VBA)
sheet = xl.ActiveSheet
# Call the 'Range' method on the Sheet
xl_range = sheet.Range(tempRange)
# xl_range = sheet.Range('A1:I1')
# xl_range.Merge()
xl_range.Select()
# xl_range.FormulaR1C1 = "Test"
xl_range.Formula = "=$B$2+$C$2"
xl_range = sheet.Range(tempRange2)
xl_range.Select()
xl_range.Formula = "=MIN(COLUMN(" + searchRange + "))+COLUMNS(" + searchRange + ")-1"
xl_range = sheet.Range(tempRange3)
xl_range.Select()
xl_range.Value = arr_x
def stop_profiling():
"""Stop the cProfile profiler and print the results"""
global _active_cprofiler
if not _active_cprofiler:
xlcAlert("No active profiler")
return
_active_cprofiler.disable()
# print the profiler stats
stream = StringIO()
stats = pstats.Stats(_active_cprofiler, stream=stream).sort_stats("cumulative")
stats.print_stats()
# print the results to the log
print(stream.getvalue())
# and copy to the clipboard
win32clipboard.OpenClipboard()
win32clipboard.EmptyClipboard()
win32clipboard.SetClipboardText(stream.getvalue())
win32clipboard.CloseClipboard()
_active_cprofiler = None
xlcAlert("cProfiler Stopped\n"
"Results have been written to the log and clipboard.")
def start_line_profiler():
"""Start the line profiler"""
global _active_line_profiler
_active_line_profiler = line_profiler.LineProfiler()
xlcAlert("Line Profiler Active\n"
"Run the function you are interested in and then stop the profiler.\n"
"Ensure you have decoratored the function with @enable_line_profiler.")
def start_line_profiler():
"""Start the line profiler"""
global _active_line_profiler
_active_line_profiler = line_profiler.LineProfiler()
xlcAlert(
"Line Profiler Active\n"
"Run the function you are interested in and then stop the profiler.\n"
"Ensure you have decoratored the function with @enable_line_profiler.")
def start_profiling():
"""Start the cProfile profiler"""
global _active_cprofiler
if _active_cprofiler is not None:
_active_cprofiler.disable()
_active_cprofiler = cProfile.Profile()
xlcAlert("cProfiler Active\n"
"Recalcuate the workbook and then stop the profiler\n"
"to see the results.")
_active_cprofiler.enable()
def start_profiling():
"""Start the cProfile profiler"""
global _active_cprofiler
if _active_cprofiler is not None:
_active_cprofiler.disable()
_active_cprofiler = cProfile.Profile()
xlcAlert("cProfiler Active\n"
"Recalcuate the workbook and then stop the profiler\n"
"to see the results.")
_active_cprofiler.enable()
def ipython_qtconsole(*args):
"""
Launches an IPython Qt console
"""
try:
# start the IPython kernel
app = _start_kernel()
# start a subprocess to run the Qt console
# run jupyter in it's own process
_launch_qt_console(app.connection_file)
except:
xlcAlert("Error starting IPython Qt console")
_log.error("Error starting IPython Qt console", exc_info=True)
def show_last_error():
selection = xl_app().Selection
exc_type, exc_value, exc_traceback = get_last_error(selection)
if exc_type is None:
xlcAlert("No error found for the selected cell")
return
msg = "".join(
traceback.format_exception(exc_type, exc_value, exc_traceback))
if xl_version() < 12:
msg = msg[:254]
xlcAlert(msg)
def time_calculation():
"""Recalculates the selected range and times how long it takes"""
xl = xl_app()
# switch Excel to manual calculation
orig_calc_mode = xl.Calculation
try:
xl.Calculation = constants.xlManual
# get the current selection and its formula
selection = xl.Selection
# run the calculation a few times
timings = []
for i in range(100):
# Start the timer and set the selection formula to itself.
# This is a reliable way to force Excel to recalculate the range.
start_time = time.clock()
selection.Calculate()
end_time = time.clock()
duration = end_time - start_time
timings.append(duration)
# calculate the mean and stddev
mean = math.fsum(timings) / len(timings)
stddev = (math.fsum([(x - mean)**2
for x in timings]) / len(timings))**0.5
best = min(timings)
worst = max(timings)
# copy the results to the clipboard
data = [["mean", mean], ["stddev", stddev], ["best", best],
["worst", worst]]
text = "\n".join(["\t".join(map(str, x)) for x in data])
win32clipboard.OpenClipboard()
win32clipboard.EmptyClipboard()
win32clipboard.SetClipboardText(text)
win32clipboard.CloseClipboard()
# report the results
xlcAlert(("%0.2f ms \xb1 %d \xb5s\n"
"Best: %0.2f ms\n"
"Worst: %0.2f ms\n"
"(Copied to clipboard)") %
(mean * 1000, stddev * 1000000, best * 1000, worst * 1000))
finally:
# restore the original calculation mode
xl.Calculation = orig_calc_mode
def on_open(import_info):
"""
on_open is registered to be called by PyXLL when the addin
is opened via the xl_on_open decorator.
This happens each time Excel starts with PyXLL installed.
"""
# check to see which modules didn't import correctly
errors = []
for modulename, module, exc_info in import_info:
if module is None:
exc_type, exc_value, exc_traceback = exc_info
errors.append("Error loading '%s' : %s" % (modulename, exc_value))
if errors:
# report any errors to the user
xlcAlert("\n".join(errors) + "\n\n(See callbacks.py example)")
def OpenJupyterNotebook(path=None):
"""
Open a Jupyter notebook in a new task pane.
:param path: Path to Jupyter notebook file or directory.
:return: True on success
"""
try:
if path is not None:
if not os.path.isabs(path):
# Try and get the absolute path relative to the active workbook
xl = xl_app(com_package="win32com")
wb = xl.ActiveWorkbook
if wb is not None and wb.FullName and os.path.exists(
wb.FullName):
abs_path = os.path.join(os.path.dirname(wb.FullName), path)
if os.path.exists(abs_path):
path = abs_path
if not os.path.exists(path):
raise RuntimeError(f"Path '{path}' not found.")
initial_path = None
notebook_path = None
if path is not None:
if os.path.isdir(path):
initial_path = path
elif os.path.isfile(path):
notebook_path = path
else:
raise RuntimeError(f"Something is wrong with {path}")
# Use schedule_call to actually open the notebook since if this was called
# from a Workbook.Open macro Excel may not yet be ready to open a CTP.
schedule_call(
partial(open_jupyter_notebook,
initial_path=initial_path,
notebook_path=notebook_path))
return True
except Exception as e:
xlcAlert(f"Error opening Jupyter notebook: {e}")
raise
def stop_line_profiler():
"""Stops the line profiler and prints the results"""
global _active_line_profiler
if not _active_line_profiler:
return
stream = StringIO()
_active_line_profiler.print_stats(stream=stream)
_active_line_profiler = None
# print the results to the log
print(stream.getvalue())
# and copy to the clipboard
win32clipboard.OpenClipboard()
win32clipboard.EmptyClipboard()
win32clipboard.SetClipboardText(stream.getvalue())
win32clipboard.CloseClipboard()
xlcAlert("Line Profiler Stopped\n"
"Results have been written to the log and clipboard.")
def stop_line_profiler():
"""Stops the line profiler and prints the results"""
global _active_line_profiler
if not _active_line_profiler:
return
stream = StringIO()
_active_line_profiler.print_stats(stream=stream)
_active_line_profiler = None
# print the results to the log
print(stream.getvalue())
# and copy to the clipboard
win32clipboard.OpenClipboard()
win32clipboard.EmptyClipboard()
win32clipboard.SetClipboardText(stream.getvalue())
win32clipboard.CloseClipboard()
xlcAlert("Line Profiler Stopped\n"
"Results have been written to the log and clipboard.")
def attach_to_pydev():
# remove any redirection from previous debugging
if getattr(sys, "_pyxll_pydev_orig_stdout", None) is None:
sys._pyxll_pydev_orig_stdout = sys.stdout
if getattr(sys, "_pyxll_pydev_orig_stderr", None) is None:
sys._pyxll_pydev_orig_stderr = sys.stderr
sys.stdout = sys._pyxll_pydev_orig_stdout
sys.stderr = sys._pyxll_pydev_orig_stderr
# stop any existing PyDev debugger
dbg = pydevd.GetGlobalDebugger()
if dbg:
dbg.FinishDebuggingSession()
time.sleep(0.1)
pydevd_tracing.SetTrace(None)
# remove any additional info for the current thread
if threading:
try:
del threading.currentThread().__dict__["additionalInfo"]
except KeyError:
pass
pydevd.SetGlobalDebugger(None)
pydevd.connected = False
time.sleep(0.1)
_log.info("Attempting to attach to the PyDev debugger")
try:
pydevd.settrace(stdoutToServer=True,
stderrToServer=True,
suspend=False)
except Exception as e:
xlcAlert("Failed to connect to PyDev\n"
"Check the debug server is running.\n"
"Error: %s" % e)
return
xlcAlert("Attatched to PyDev")
def attach_to_pydev():
# remove any redirection from previous debugging
if getattr(sys, "_pyxll_pydev_orig_stdout", None) is None:
sys._pyxll_pydev_orig_stdout = sys.stdout
if getattr(sys, "_pyxll_pydev_orig_stderr", None) is None:
sys._pyxll_pydev_orig_stderr = sys.stderr
sys.stdout = sys._pyxll_pydev_orig_stdout
sys.stderr = sys._pyxll_pydev_orig_stderr
# stop any existing PyDev debugger
dbg = pydevd.GetGlobalDebugger()
if dbg:
dbg.FinishDebuggingSession()
time.sleep(0.1)
pydevd_tracing.SetTrace(None)
# remove any additional info for the current thread
if threading:
try:
del threading.currentThread().__dict__["additionalInfo"]
except KeyError:
pass
pydevd.SetGlobalDebugger(None)
pydevd.connected = False
time.sleep(0.1)
_log.info("Attempting to attach to the PyDev debugger")
try:
pydevd.settrace(stdoutToServer=True, stderrToServer=True, suspend=False)
except Exception as e:
xlcAlert("Failed to connect to PyDev\n"
"Check the debug server is running.\n"
"Error: %s" % e)
return
xlcAlert("Attatched to PyDev")
def on_example_submenu_item_1():
xlcAlert("Sub-menus can be created easily with PyXLL")
def on_unicode_test():
xlcAlert("\u01d9ni\u0186\u020dde")
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)
def on_example_menu_item_2():
xlcAlert("Hello again from PyXLL")
def my_menu_item():
xlcAlert("new menu example")
# def format_cover_page():
# app = xl_app()
# selection = app.Selection.
#
# formatter = Formatter
#
# selection.rows("1:1").RowHeight = 41.25
# Range("A1:I1").Select
# ActiveCell.FormulaR1C1 = "Haverford Trust Portfolio Proposal"
# With
# Selection
# .HorizontalAlignment = xlCenter
# .VerticalAlignment = xlCenter
# .WrapText = False
# .Orientation = 0
# .AddIndent = False
# .IndentLevel = 0
# .ShrinkToFit = False
# .ReadingOrder = xlContext
# .MergeCells = False
# End
# With
# Selection.Merge
# With
# Selection
# .HorizontalAlignment = xlCenter
# .VerticalAlignment = xlCenter
# .WrapText = False
# .Orientation = 0
# .AddIndent = False
# .IndentLevel = 0
# .ShrinkToFit = False
# .ReadingOrder = xlContext
# .MergeCells = True
# End
# With
# With
# Selection.Interior
# .Pattern = xlSolid
# .PatternColorIndex = xlAutomatic
# .ThemeColor = xlThemeColorAccent1
# .TintAndShade = 0
# .PatternTintAndShade = 0
# End
# With
# With
# Selection.Font
# .Name = "Abadi"
# .Size = 24
# .Strikethrough = False
# .Superscript = False
# .Subscript = False
# .OutlineFont = False
# .Shadow = False
# .Underline = xlUnderlineStyleNone
# .ThemeColor = xlThemeColorDark1
# .TintAndShade = 0
# .ThemeFont = xlThemeFontMinor
# End
# With
#add button and assign macro
# ActiveSheet.Buttons.Add(336, 121.5, 93.75, 14.25).Select
# Selection.OnAction = "Macro4"
def win32com_menu_test():
# get the current selected range and set some text
selection = xl_app().Selection
selection.Value = "Hello!"
pyxll.xlcAlert("Some text has been written to the current cell")
def my_macro():
xlcAlert("Hello!")
def on_example_submenu_item_1():
xlcAlert("Sub-menus can be created easily with PyXLL")
def on_example_menu_item_3():
xlcAlert("Wow, a different menu!")
def on_example_menu_item_1():
xlcAlert("Hello from PyXLL")
def on_example_menu_item_1():
xlcAlert("Hello from PyXLL")
def time_calculation():
"""Recalculates the selected range and times how long it takes"""
xl = xl_app()
orig_calc_mode = xl.Calculation
try:
# switch Excel to manual calculation and disable screen updating
xl.Calculation = constants.xlManual
xl.ScreenUpdating = False
# int64 variables used for timing
start_time = c_int64()
end_time = c_int64()
# Get the current selection and its Calculate method (to avoid including the
# method retrieval in the timing)
selection = xl.Selection
selection_Calculate = selection.Calculate
# run the calculation a few times
timings = []
for i in range(100):
# Time calling selection.Calculate() using the Windows high-resolution timers
windll.Kernel32.QueryPerformanceCounter(byref(start_time))
selection_Calculate()
windll.Kernel32.QueryPerformanceCounter(byref(end_time))
duration = float(end_time.value - start_time.value)
timings.append(duration)
finally:
# restore the original calculation mode and screen updating
xl.ScreenUpdating = True
xl.Calculation = orig_calc_mode
# calculate the mean and stddev
mean = math.fsum(timings) / len(timings)
median = _median(timings)
stddev = (math.fsum([(x - mean) ** 2 for x in timings]) / len(timings)) ** 0.5
best = min(timings)
worst = max(timings)
# convert to seconds
freq = c_int64()
windll.Kernel32.QueryPerformanceFrequency(byref(freq))
mean /= freq.value
median /= freq.value
stddev /= freq.value
best /= freq.value
worst /= freq.value
# copy the results to the clipboard
data = [
["mean", mean],
["median", median],
["stddev", stddev],
["best", best],
["worst", worst]
]
text = "\n".join(["\t".join(map(str, x)) for x in data])
win32clipboard.OpenClipboard()
win32clipboard.EmptyClipboard()
win32clipboard.SetClipboardText(text)
win32clipboard.CloseClipboard()
# report the results
xlcAlert(("%0.2f ms \xb1 %d \xb5s\n"
"Median: %0.2f ms\n"
"Best: %0.2f ms\n"
"Worst: %0.2f ms\n"
"(Copied to clipboard)") % (mean * 1000, stddev * 1000000, median * 1000, best * 1000, worst * 1000))
def time_calculation():
"""Recalculates the selected range and times how long it takes"""
xl = xl_app()
orig_calc_mode = xl.Calculation
try:
# switch Excel to manual calculation and disable screen updating
xl.Calculation = constants.xlManual
xl.ScreenUpdating = False
# int64 variables used for timing
start_time = c_int64()
end_time = c_int64()
# Get the current selection and its Calculate method (to avoid including the
# method retrieval in the timing)
selection = xl.Selection
selection_Calculate = selection.Calculate
# run the calculation a few times
timings = []
for i in range(100):
# Time calling selection.Calculate() using the Windows high-resolution timers
windll.Kernel32.QueryPerformanceCounter(byref(start_time))
selection_Calculate()
windll.Kernel32.QueryPerformanceCounter(byref(end_time))
duration = float(end_time.value - start_time.value)
timings.append(duration)
finally:
# restore the original calculation mode and screen updating
xl.ScreenUpdating = True
xl.Calculation = orig_calc_mode
# calculate the mean and stddev
mean = math.fsum(timings) / len(timings)
median = _median(timings)
stddev = (math.fsum([(x - mean)**2 for x in timings]) / len(timings))**0.5
best = min(timings)
worst = max(timings)
# convert to seconds
freq = c_int64()
windll.Kernel32.QueryPerformanceFrequency(byref(freq))
mean /= freq.value
median /= freq.value
stddev /= freq.value
best /= freq.value
worst /= freq.value
# copy the results to the clipboard
data = [["mean", mean], ["median", median], ["stddev", stddev],
["best", best], ["worst", worst]]
text = "\n".join(["\t".join(map(str, x)) for x in data])
win32clipboard.OpenClipboard()
win32clipboard.EmptyClipboard()
win32clipboard.SetClipboardText(text)
win32clipboard.CloseClipboard()
# report the results
xlcAlert(
("%0.2f ms \xb1 %d \xb5s\n"
"Median: %0.2f ms\n"
"Best: %0.2f ms\n"
"Worst: %0.2f ms\n"
"(Copied to clipboard)") % (mean * 1000, stddev * 1000000,
median * 1000, best * 1000, worst * 1000))
def win32com_menu_test():
# get the current selected range and set some text
selection = xl_app().Selection
selection.Value = "Hello!"
pyxll.xlcAlert("Some text has been written to the current cell")
def on_example_menu_item_3():
xlcAlert("Adding multiple menus is easy")
def gemini_reset_menu():
xlcAlert("Remove %d cached items" % len(RESULTS))
gemini_reset()
xl = xl_app()
range = xl.Range(GEMINI_CALL_ID)
range.Value = 0
def on_example_menu_item_2():
xlcAlert("Hello again from PyXLL")
dbg.FinishDebuggingSession()
time.sleep(0.1)
pydevd_tracing.SetTrace(None)
# remove any additional info for the current thread
if threading:
try:
del threading.currentThread().__dict__["additionalInfo"]
except KeyError:
pass
pydevd.SetGlobalDebugger(None)
pydevd.connected = False
time.sleep(0.1)
_log.info("Attempting to attach to the PyDev debugger")
try:
pydevd.settrace(stdoutToServer=True, stderrToServer=True, suspend=False)
except Exception, e:
xlcAlert("Failed to connect to PyDev\n"
"Check the debug server is running.\n"
"Error: %s" % e)
return
xlcAlert("Attatched to PyDev")
except ImportError:
pass
if __name__ == "__main__":
sys.exit(main())
