""" Profiling tools for use with PyXLL. This code accompanies the blog post https://www.pyxll.com/blog/how-to-profile-python-code-in-excel """ from pyxll import xl_menu, xl_app, xlcAlert from win32com.client import constants from functools import wraps from ctypes import c_int64, byref, windll import win32clipboard import line_profiler import cProfile import pstats import math try: from io import StringIO except ImportError: from StringIO import StringIO _active_cprofiler = None _active_line_profiler = None def _median(values): """Return the median of a list of values""" n = len(values) if n < 1: return 0.0 if n % 2 == 1: return sorted(values)[n//2] return sum(sorted(values)[n//2-1:n//2+1])/2.0 @xl_menu("Time to Calculate", menu="Profiling Tools") 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)) @xl_menu("Start", menu="Profiling Tools", sub_menu="cProfile") 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() @xl_menu("Stop", menu="Profiling Tools", sub_menu="cProfile") 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.") # Current active line profiler _active_line_profiler = None def enable_line_profiler(func): """Decorator to switch on line profiling for a function. If using line_profiler from the command line, use the built-in @profile decorator instead of this one. """ @wraps(func) def wrapper(*args, **kwargs): nonlocal func if _active_line_profiler: func = _active_line_profiler(func) return func(*args, **kwargs) return wrapper @xl_menu("Start", menu="Profiling Tools", sub_menu="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.") @xl_menu("Stop", menu="Profiling Tools", sub_menu="Line Profiler") 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.")