def xl_app_checker(*args, **kwargs):
if "xl" not in kwargs.keys():
kwargs["xl"] = xl_app()
# logging.info(f"xl_app created for {func.__name__}")
elif kwargs["xl"] is None:
kwargs["xl"] = xl_app()
# logging.info(f"xl_app created for {func.__name__}")
return func(*args, **kwargs)
def connect_to_excel() -> w32:
"""
Connects to the current active excel workbook and return win32com client object.
Using this function has known issues.
:return: w32
Active open excel worksheet
"""
xl = xl_app()
if xl.ActiveWorkbook is None:
kill_excel_bg()
xl = xl_app() # type: w32
logging.info(f"Connected to excel sheet {xl.ActiveWorkbook.Name}")
return (xl if check_jupyter_excel_connection(xl) else
Exception("Not connected to excel"))
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 set_dca_pars():
xl = xl_app()
dm = get_cached_object(get_value("data_obj_manager", xl=xl))
selected_well = get_value("fm_fn_producing_well_name", xl=xl)
dca_pars_oil = pd.DataFrame(
dm.session.query(
Well_Oneline.ip_final_oil,
Well_Oneline.di_oil,
Well_Oneline.b_oil,
Well_Oneline.dmin_oil,
Well_Oneline.ip_oil_idx,
).filter(Well_Oneline.well_str == selected_well).all())
dca_pars_oil.columns = ["IP", "De", "B", "Dmin", "Max IP Month"]
dca_pars_gas = pd.DataFrame(
dm.session.query(
Well_Oneline.ip_final_gas,
Well_Oneline.di_gas,
Well_Oneline.b_gas,
Well_Oneline.dmin_gas,
Well_Oneline.ip_gas_idx,
).filter(Well_Oneline.well_str == selected_well).all())
dca_pars_gas.columns = ["IP", "De", "B", "Dmin", "Max IP Month"]
dca_pars = pd.concat([dca_pars_oil, dca_pars_gas])
copy_df_xl(
df=dca_pars,
sheet="Producing",
name="fm_fn_producing_dca_pars",
copy_columns=True,
xl=xl,
)
def create_fm_data_manager(restore=None):
"""
Creates a fm data manager using the xl instance passed.
WARNING! dont change the wrapper order.
:param restore:
:param xl:
:return:
"""
xl = xl_app()
assets = to_df(get_value("project_state_assets", xl=xl))
if (assets.shape[0] == 0) and restore is None:
message_box("Provide at least a section to start the analysis")
return
cfg = get_cached_object(get_value("data_obj_cfg"))
source_connector = create_source_connector(xl=xl)
query_manager = create_query_manager(xl=xl)
data_loader = FMDataLoader()
data_formatter = FMDataFormatter()
dm = FMDataManager(
cfg=cfg,
qm=query_manager,
sc=source_connector,
dl=data_loader,
df=data_formatter,
restore=restore,
)
return dm
def checkbox_example():
xl = xl_app()
check_box = xl.ActiveSheet.CheckBoxes(xl.Caller)
if check_box.Value == 1:
xl.Range("checkbox_output").Value = "CHECKED"
else:
xl.Range("checkbox_output").Value = "Click the check box"
def _get_notebook_path(cfg):
"""Return the path to open the Jupyter notebook in."""
# Use the path of the active workbook if use_workbook_dir is set
use_workbook_dir = False
if cfg.has_option("JUPYTER", "use_workbook_dir"):
try:
use_workbook_dir = bool(int(cfg.get("JUPYTER",
"use_workbook_dir")))
except (ValueError, TypeError):
_log.error("Unexpected value for JUPYTER.use_workbook_dir.")
if use_workbook_dir:
xl = xl_app(com_package="win32com")
wb = xl.ActiveWorkbook
if wb is not None and wb.FullName and os.path.exists(wb.FullName):
return os.path.dirname(wb.FullName)
# Otherwise use the path option
if cfg.has_option("JUPYTER", "notebook_dir"):
path = cfg.get("JUPYTER", "notebook_dir").strip("\"\' ")
if os.path.exists(path):
return os.path.normpath(path)
_log.warning("Notebook path '%s' does not exist" % path)
# And if that's not set use My Documents
CSIDL_PERSONAL = 5 # My Documents
SHGFP_TYPE_CURRENT = 0 # Get current, not default value
buf = ctypes.create_unicode_buffer(ctypes.wintypes.MAX_PATH)
ctypes.windll.shell32.SHGetFolderPathW(None, CSIDL_PERSONAL, None,
SHGFP_TYPE_CURRENT, buf)
return buf.value
def set_selection_in_ipython(*args):
"""
Gets the value of the selected cell and copies it to
the globals dict in the IPython kernel.
"""
try:
if not getattr(sys, "_ipython_app", None) or not sys._ipython_kernel_running:
raise Exception("IPython kernel not running")
xl = xl_app(com_package="win32com")
selection = xl.Selection
if not selection:
raise Exception("Nothing selected")
value = selection.Value
# convert any cached objects (PyXLL >= 4 only)
pyxll_version = int(pyxll.__version__.split(".")[0])
if pyxll_version >= 4 and isinstance(value, str):
try:
to_object = get_type_converter("var", "object")
value = to_object(value)
except KeyError:
pass
# set the value in the shell's locals
sys._ipython_app.shell.user_ns["_"] = value
print("\n\n>>> Selected value set as _")
except:
if win32api:
win32api.MessageBox(None, "Error setting selection in Excel")
_log.error("Error setting selection in Excel", exc_info=True)
def add_meridian(control):
xl = xl_app()
for cell in xl.Selection:
value = cell.Value
if not isinstance(value, str):
continue
cell.Value = value + " IM"
def _setup_event_handler(cache):
# Only setup the app event handler once.
if cache not in _event_handlers:
xl = xl_app(com_package="win32com")
app_handler = win32com.client.DispatchWithEvents(
xl, ObjectCacheApplicationEventHandler)
app_handler.set_cache(cache)
_event_handlers[cache] = app_handler
def ui_closure():
xl = xl_app()
# message_box(
# "WARNING! Closing the excel will clear all the session data from the template. Make sure to save the session."
# )
clear_list(containing_string="fm_fn", xl=xl)
clear_list(containing_string="obj", xl=xl)
clear_user_zones(containing_string="state", xl=xl)
def recalc_selection():
"""
Recalculates selection
"""
xl = xl_app(com_package="win32com")
selection = xl.Selection
selection.Dirty()
selection.Calculate()
def normalize_formation(trigger):
logging.info("Normalizing Formation")
xl = xl_app()
dm = get_cached_object(
get_value("data_obj_manager")) # type: FMDataManager
data = (dm.session.query(
Well_Oneline.api,
Well_Oneline.well_name,
Well_Oneline.well_number,
Well_Oneline.well_str,
Well_Oneline.operator_name,
Well_Oneline.formation,
Section_Assumption.formation_1,
Section_Assumption.formation_2,
).join(Section_Well).join(Project_State_Asset).join(
Section_Assumption).all())
df = pd.DataFrame(data)
# This line added in case a multi unit well lying in two different sections have two different
# section assumptions
df = df.drop_duplicates(["api"]).reset_index(drop=True)
str_columns = [
"well_name",
"well_number",
"operator_name",
"formation",
"formation_1",
"formation_2",
]
string_array = df.loc[:, str_columns].values.transpose().astype("unicode")
formation = form_norm(string_array)
df["norm_formation"] = formation
formation_columns = [
"api",
"well_str",
"operator_name",
"formation",
"formation_1",
"formation_2",
"norm_formation",
]
df = df.loc[:, formation_columns]
with dm.session_scope() as session:
for i, row in df.iterrows():
selected_well = (session.query(Well_Oneline).filter(
Well_Oneline.api == row["api"]).one()) # type: Well_Oneline
selected_well.norm_formation = row.norm_formation
set_formation_normalizer()
def set_selection_in_ipython(*args):
"""Gets the value of the selected cell and copies it to
the globals dict in the IPython kernel.
"""
from pyxll import xl_app, XLCell
try:
if not getattr(sys, "_ipython_app",
None) or not sys._ipython_kernel_running:
raise Exception("IPython kernel not running")
# Get the current selected range
xl = xl_app(com_package="win32com")
selection = xl.Selection
if not selection:
raise Exception("Nothing selected")
# Check to see if it looks like a pandas DataFrame
try_dataframe = False
has_index = False
if selection.Rows.Count > 1 and selection.Columns.Count > 1:
try:
import pandas as pd
except ImportError:
pd = None
pass
if pd is not None:
# If the top left corner is empty assume the first column is an index.
try_dataframe = True
top_left = selection.Cells[1].Value
if top_left is None:
has_index = True
# Get an XLCell object from the range to make it easier to get the value
cell = XLCell.from_range(selection)
# Get the value using PyXLL's dataframe converter, or as a plain value.
value = None
if try_dataframe:
try:
type_kwargs = {"index": 1 if has_index else 0}
value = cell.options(type="dataframe",
type_kwargs=type_kwargs).value
except:
_log.warning("Error converting selection to DataFrame",
exc_info=True)
if value is None:
value = cell.value
# set the value in the shell's locals
sys._ipython_app.shell.user_ns["_"] = value
print("\n\n>>> Selected value set as _")
except:
app = _get_qt_app()
QMessageBox.warning(None, "Error", "Error setting selection in Excel")
_log.error("Error setting selection in Excel", exc_info=True)
def nn_Run(net, image_name, scale=1, offset=-0.5, seed=None):
"""Run the neural network with random weights"""
# See the seed for the RNG
seed = int(seed) if seed is not None else torch.random.initial_seed()
torch.manual_seed(seed)
# Initialize the weights
def init_weights(m):
if isinstance(m, nn.Linear):
nn.init.normal_(m.weight)
net.apply(init_weights)
# Find the Excel image
xl = xl_app()
sheet = xl.Caller.Worksheet
image = sheet.Pictures(image_name)
# Get the image size in pixels
size_x, size_y = get_image_size(image)
# Create the inputs
inputs = np.zeros((size_y, size_x, 2))
for x in np.arange(0, size_x, 1):
for y in np.arange(0, size_y, 1):
scaled_x = scale * ((float(x) / size_x) + offset)
scaled_y = scale * ((float(y) / size_y) + offset)
inputs[y][x] = np.array([scaled_x, scaled_y])
inputs = inputs.reshape(size_x * size_y, 2)
# Compute the results
result = net(torch.tensor(inputs).type(torch.FloatTensor)).detach().numpy()
result = result.reshape((size_y, size_x, 3))
# Create a temporary file to write the result to
file = create_temporary_file(suffix=".png")
# Write the image to the file
plt.imsave(file, result)
file.flush()
# Replace the old image with the new one
new_image = sheet.Shapes.AddPicture(Filename=file.name,
LinkToFile=0, # msoFalse
SaveWithDocument=-1, # msoTrue
Left=image.Left,
Top=image.Top,
Width=image.Width,
Height=image.Height)
image_name = image.Name
image.Delete()
new_image.Name = image_name
return f"[{new_image.Name}]"
def recalc_selection():
"""
Recalculates selection
"""
xl = xl_app()
selection = xl.Selection
selection.Dirty()
selection.Calculate()
def example_macro():
# Get the Excel.Application object
xl = xl_app()
# Add A1 to A2 and store the result in A3
a = xl.Range("A1")
b = xl.Range("A2")
c = xl.Range("A3")
c.Value = a.Value + b.Value
def recalc_selection():
"""
Recalculates selection
"""
xl = xl_app()
selection = xl.Selection
selection.Dirty()
selection.Calculate()
def resize_array_formula():
"""
Recalculates and resizes a range to show all the results of a formula.
"""
xl = xl_app(com_package="win32com")
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 optimize5():
"""
Trigger optimization of a spreadsheet model that
takes the named range "Inputs" as inputs and
produces output in the named range "Output".
"""
xl = xl_app()
qt_app = get_qt_app() # pragma noqc
# Get the initial values of the input cells
msgBox = OpDialog()
result = msgBox.exec_()
if not result: # user cancelled
return
in_range = get_range(msgBox.in_range.text())
out_cell = get_range(msgBox.out_cell.text())
in_values = list(in_range.Value)
X = np.array([x[0] for x in in_values])
orig_calc_mode = xl.Calculation
try:
# switch Excel to manual calculation
# and disable screen updating
xl.Calculation = constants.xlManual
xl.ScreenUpdating = False
# run the minimization routine
xl_obj_func = partial(obj_func, xl, in_range, out_cell)
print(f"X = {X}")
result = minimize(xl_obj_func, X, method="nelder-mead")
in_range.Value = [(float(x), ) for x in result.x]
xl.ScreenUpdating = True
mbox = QMessageBox()
mbox.setIcon(QMessageBox.Information)
mbox.setText("Optimization results shown below."
"\nMake changes permanent?")
mbox.setWindowTitle("Optimization Complete")
mbox.setInformativeText("\n".join([
"Successful: %s" % result.success,
result.message,
"After %d iterations" % result.nit,
]))
mbox.setStandardButtons(QMessageBox.Ok | QMessageBox.Cancel)
yes_no = mbox.exec_()
if yes_no != QMessageBox.Ok:
in_range.Value = in_values
else:
in_range.Value = [(float(x), ) for x in result.x]
finally:
# restore the original calculation
# and screen updating mode
xl.ScreenUpdating = True
xl.Calculation = orig_calc_mode
def obj_func(arg):
"""Wraps a spreadsheet computation as a Python function."""
xl = xl_app()
# Copy argument values to input range
xl.Range('C11:C12').Value = [(float(x), ) for x in arg]
# Calculate after changing the inputs
xl.Calculate()
# Return the value of the output cell
result = xl.Range("E11").Value
return result
def rtd_set_throttle_interval(interval):
"""Set Excel's RTD throttle interval (in milliseconds).
When real time data objects notify Excel that they have changed
the displayed value in Excel doesn't actually update until
Excel refreshes. How often Excel refreshes due to RTD updates
defaults to every 2 seconds, and so to see data refresh more
frequently this function may be used.
"""
xl = xl_app()
xl.RTD.ThrottleInterval = interval
return "OK"
def get_image_size(image):
"""Return the size of an image in pixels as (width, height)."""
# Get the size of the input image in pixels (Excel sizes are in points,
# or 1/72th of an inch.
xl = xl_app()
dc = win32gui.GetDC(xl.Hwnd)
pixels_per_inch_x = win32ui.GetDeviceCaps(dc, win32con.LOGPIXELSX)
pixels_per_inch_y = win32ui.GetDeviceCaps(dc, win32con.LOGPIXELSY)
size_x = int(image.Width * pixels_per_inch_x / 72)
size_y = int(image.Height * pixels_per_inch_y / 72)
return size_x, size_y
def update_func():
# Get the Excel.Application COM object
xl = xl_app()
# Get an Excel.Range object from the XLCell instance
range = caller.to_range(com_package="win32com")
# get the cell below and expand it to rows x cols
range = xl.Range(range.Resize(2, 1), range.Resize(rows + 1, cols))
# and set the range's value
range.Value = value
def ui_initiation():
try:
xl = xl_app()
except AttributeError:
clear_win32com_cache()
raise Win32COMCacheException
set_formula("data_obj_cfg", fn=load_config_xl, xl=xl)
reset_defaults(xl=xl)
load_module_xml("session", xl=xl)
load_module_xml("project", xl=xl)
load_module_xml("tools", xl=xl)
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 plot_to_excel(figure, name):
"""Plot a figure in Excel"""
# write the figure to a temporary image file
filename = os.path.join(os.environ["TEMP"], "xlplot_%s.png" % name)
canvas = FigureCanvas(figure)
canvas.draw()
canvas.print_png(filename)
# Show the figure in Excel as a Picture object on the same sheet
# the function is being called from.
xl = xl_app()
caller = xlfCaller()
sheet = xl.Range(caller.address).Worksheet
# if a picture with the same figname already exists then get the position
# and size from the old picture and delete it.
for old_picture in sheet.Pictures():
if old_picture.Name == name:
height = old_picture.Height
width = old_picture.Width
top = old_picture.Top
left = old_picture.Left
old_picture.Delete()
break
else:
# otherwise place the picture below the calling cell.
top_left = sheet.Cells(caller.rect.last_row + 2,
caller.rect.last_col + 1)
top = top_left.Top
left = top_left.Left
width, height = figure.bbox.bounds[2:]
# insert the picture
# Ref: http://msdn.microsoft.com/en-us/library/office/ff198302%28v=office.15%29.aspx
picture = sheet.Shapes.AddPicture(
Filename=filename,
LinkToFile=0, # msoFalse
SaveWithDocument=-1, # msoTrue
Left=left,
Top=top,
Width=width,
Height=height)
# set the name of the new picture so we can find it next time
picture.Name = name
# delete the temporary file
os.unlink(filename)
return "[Plotted '%s']" % name
def toproper(control):
"""Set the currently selected cells to 'proper' case"""
# get the Excel Application object
xl = xl_app()
# iterate over the currently selected cells
for cell in xl.Selection:
# get the cell value
value = cell.Value
# skip any cells that don't contain text
if not isinstance(value, str):
continue
cell.Value = value.title()
def toproper(control):
"""Set the currently selected cells to 'proper' case"""
# get the Excel Application object
xl = xl_app()
# iterate over the currently selected cells
for cell in xl.Selection:
# get the cell value
value = cell.Value
# skip any cells that don't contain text
if not isinstance(value, str):
continue
cell.Value = value.title()
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 __init__(self, xl_name="data_obj_cfg"):
"""
Initialize ParametersParser
Looks for Excel named range xl_name that contains ParametersParser object
Parameters
----------
xl_name
"""
self.xl: w32 = xl_app()
self.xl_name = xl_name
self._log = logging.getLogger(__name__)
ConfigParser.__init__(self, interpolation=ExtendedInterpolation())
self._log.info(f"{__class__.__name__}({len(self)} groups)")
def save_as_session(trigger):
xl = xl_app()
cfg = get_cached_object(get_value("data_obj_cfg",
xl=xl)) # type: ParametersParser
save_file = file_saver("Save Session",
path=cfg["PROJECT"]["named"] + "/*" +
cfg["PROJECT"]["format"])
if save_file is None:
return
save_file = save_file + cfg["PROJECT"]["format"]
data_manager = get_cached_object(
get_value("data_obj_manager")) # type: FMDataManager
data_manager.save_as_db(save_file)
logging.info(f"Saving Session as {save_file}")
def show_image_in_excel(classification, figname="prediction_image"):
"""Plot a figure in Excel"""
# Show the figure in Excel as a Picture object on the same sheet
# the function is being called from.
xl = xl_app()
sheet = xl.ActiveSheet
# if a picture with the same figname already exists then get the position
# and size from the old picture and delete it.
for old_picture in sheet.Pictures():
if old_picture.Name == figname:
height = old_picture.Height
width = old_picture.Width
top = old_picture.Top
left = old_picture.Left
old_picture.Delete()
break
else:
# otherwise create a new image
top_left = sheet.Cells(1, 1)
top = top_left.Top
left = top_left.Left
width, height = 100, 100
# insert the picture
# Ref: http://msdn.microsoft.com/en-us/library/office/ff198302%28v=office.15%29.aspx
filename = os.path.join(os.path.dirname(__file__), "images", _zoo_classifications[classification] + ".jpg")
picture = sheet.Shapes.AddPicture(Filename=filename,
LinkToFile=0, # msoFalse
SaveWithDocument=-1, # msoTrue
Left=left,
Top=top,
Width=width,
Height=height)
# set the name of the new picture so we can find it next time
picture.Name = figname
def toggle_case(control):
"""Toggle the case of the currently selected cells"""
# get the Excel Application object
xl = xl_app()
# iterate over the currently selected cells
for cell in xl.Selection:
# get the cell value
value = cell.Value
# skip any cells that don't contain text
if not isinstance(value, str):
continue
# toggle between upper, lower and proper case
if value.isupper():
value = value.lower()
elif value.islower():
value = value.title()
else:
value = value.upper()
# set the modified value on the cell
cell.Value = value
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 resize_array_formula():
"""
Recalculates and resizes a range to show all the results of a formula.
"""
xl = xl_app()
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)
if isinstance(result[0], tuple):
height = len(result)
width = len(result[0])
else:
height = 1
width = len(result)
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 hc_plot(chart, control_name, theme=None):
"""
This function is used by the other plotting functions to render the chart as html
and display it in Excel.
"""
# add the theme if there is one
if theme:
chart.add_JSsource(["https://code.highcharts.com/themes/%s.js" % theme])
# get the calling sheet
caller = xlfCaller()
sheet_name = caller.sheet_name
# split into workbook and sheet name
match = re.match("^\[(.+?)\](.*)$", sheet_name.strip("'\""))
if not match:
raise Exception("Unexpected sheet name '%s'" % sheet_name)
workbook, sheet = match.groups()
# get the Worksheet object
xl = xl_app()
workbook = xl.Workbooks(workbook)
sheet = workbook.Sheets(sheet)
# find the existing webbrowser control, or create a new one
try:
control = sheet.OLEObjects(control_name[:31])
browser = control.Object
except:
control = sheet.OLEObjects().Add(ClassType="Shell.Explorer.2",
Left=147,
Top=60.75,
Width=400,
Height=400)
control.Name = control_name[:31]
browser = control.Object
# set the chart aspect ratio to match the browser
if control.Width > control.Height:
chart.set_options("chart", {
"height": "%d%%" % (100. * control.Height / control.Width)
})
else:
chart.set_options("chart", {
"width": "%d%%" % (100. * control.Width / control.Height)
})
# get the html and add the 'X-UA-Compatible' meta-tag
soup = BeautifulSoup(chart.htmlcontent)
metatag = soup.new_tag("meta")
metatag.attrs["http-equiv"] = "X-UA-Compatible"
metatag.attrs['content'] = "IE=edge"
soup.head.insert(0, metatag)
# write out the html for the browser to render
fh = tempfile.NamedTemporaryFile("wt", suffix=".html", delete=False)
filename = fh.name
# clean up the file after 10 seconds to give the browser time to load
def on_timer(timer_id, time):
timer.kill_timer(timer_id)
os.unlink(filename)
timer.set_timer(10000, on_timer)
fh.write(soup.prettify())
fh.close()
# navigate to the temporary file
browser.Navigate("file://%s" % filename)
return "[%s]" % control_name