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 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 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 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_menu_item_3(): xlcAlert("Wow, a different menu!")
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 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
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())