text:
import sys

from PyQt5.QtWidgets import QApplication, QMessageBox

def get_qt_app():
    """Returns a QApplication instance.
    Must be called before showing any dialogs.
    """
    app = QApplication.instance()
    if app is None:
        app = QApplication([sys.executable])
    return app

if __name__ == "__main__":
    app = get_qt_app()
    msgBox = QMessageBox()
    msgBox.setText("This is a multi-line\nmessage for display\nto the user.")
    msgBox.setInformativeText(
        "And here is a detailed explanation," " or at least the closest you will get."
    )
    msgBox.setWindowTitle("TITLE")
    msgBox.exec_()


text:
import sys

from PyQt5.QtWidgets import (
    QApplication,
    QDialog,
    QDialogButtonBox,
    QHBoxLayout,
    QVBoxLayout,
    QLabel,
    QLineEdit,
)

def get_qt_app():
    """Returns a QApplication instance.

    Must be called before showing any dialogs.
    """
    app = QApplication.instance()
    if app is None:
        app = QApplication([sys.executable])
    return app

class OpDialog(QDialog):
    """A Dialog to set input and output ranges for an optimization.

    With added stretch for a more consistent interface."""

    def __init__(self, *args, **kwargs):
        "Create a new dialogue instance."
        super().__init__(*args, **kwargs)
        self.setWindowTitle("Optimization Inputs and Output")
        self.gui_init()

    def gui_init(self):
        "Create and establish the widget layout."
        self.in_range = QLineEdit()
        self.out_cell = QLineEdit()

        row_1 = QHBoxLayout()
        row_1.addWidget(QLabel("Input range:"))
        row_1.addStretch()
        row_1.addWidget(self.in_range)

        row_2 = QHBoxLayout()
        row_2.addWidget(QLabel("Output Cell:"))
        row_2.addStretch()
        row_2.addWidget(self.out_cell)

        row_3 = QHBoxLayout()
        self.buttonBox = QDialogButtonBox(QDialogButtonBox.Ok | QDialogButtonBox.Cancel)
        self.buttonBox.accepted.connect(self.accept)
        self.buttonBox.rejected.connect(self.reject)
        row_3.addWidget(self.buttonBox)

        self.layout = QVBoxLayout()
        self.layout.addWidget(self.buttonBox)

        layout = QVBoxLayout()
        layout.addLayout(row_1)
        layout.addLayout(row_2)
        layout.addLayout(row_3)
        self.setLayout(layout)

if __name__ == "__main__":
    app = get_qt_app()
    msgBox = OpDialog()
    result = msgBox.exec_()
    print("Input range:", msgBox.in_range.text())
    print("Output cell:", msgBox.out_cell.text())
    print("You clicked", "OK" if result else "Cancel")

repo_name: pyxll/pyxll-examples

repo_language: Python

mime_type: text/plain


text:
import sys

from PyQt5.QtWidgets import (
    QApplication,
    QDialog,
    QHBoxLayout,
    QVBoxLayout,
    QLabel,
    QLineEdit,
)

def get_qt_app():
    """Returns a QApplication instance.
    Must be called before showing any dialogs.
    """
    app = QApplication.instance()
    if app is None:
        app = QApplication([sys.executable])
    return app

class OpDialog(QDialog):
    "A Dialog to set input and output ranges for an optimization."

    def __init__(self, *args, **kwargs):
        "Create a new dialogue instance."
        super().__init__(*args, **kwargs)
        self.setWindowTitle("Optimization Inputs and Output")
        self.gui_init()

    def gui_init(self):
        "Create and establish the widget layout."
        self.in_range = QLineEdit()
        self.out_cell = QLineEdit()

        row_1 = QHBoxLayout()
        row_1.addWidget(QLabel("Input range:"))
        row_1.addWidget(self.in_range)

        row_2 = QHBoxLayout()
        row_2.addWidget(QLabel("Output Cell:"))
        row_2.addWidget(self.out_cell)

        layout = QVBoxLayout()
        layout.addLayout(row_1)
        layout.addLayout(row_2)
        self.setLayout(layout)

if __name__ == "__main__":
    app = get_qt_app()
    msgBox = OpDialog()
    msgBox.exec_()


text:
"""
Optimisation example using scipy.optimize.minimize.

Extended to ad.

This code accompanies the blog post
  https://www.pyxll.com/blog/extending-the-excel-user-interface/
"""
import sys

from functools import partial
from pyxll import xl_app, xl_menu
from win32com.client import constants
import numpy as np
from scipy.optimize import minimize
from PyQt5.QtWidgets import QApplication, QMessageBox

def get_qt_app():
    """Returns a QApplication instance.

    MUST be called before showing any dialogs.
    """
    app = QApplication.instance()
    if app is None:
        app = QApplication([sys.executable])
    return app

@xl_menu("Optimize")
def optimize4():
    """
    Trigger optimization of a spreadsheet model that
    takes the named range "Inputs" as inputs and
    produces output in the named range "Output".
    """
    qt_app = get_qt_app()  # pragma noqc
    xl = xl_app()
    # Get the initial values of the input cells
    in_values = list(xl.Range("Inputs").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)
        result = minimize(xl_obj_func, X, method="nelder-mead")
        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:
            xl.Range("Inputs").Value = in_values
        else:
            xl.Range("Inputs").Value = [(float(x),) for x in result.x]

    finally:
        # restore the original calculation
        # and screen updating mode
        xl.Calculation = orig_calc_mode

def obj_func(xl, arg):
    """Wraps a spreadsheet computation as a Python function."""
    # Copy argument values to input range
    xl.Range("Inputs").Value = [(float(x),) for x in arg]

    # Calculate after changing the inputs
    xl.Calculate()

    # Return the value of the output cell
    result = xl.Range("Output").Value
    return result


text:
"""
Optimisation example using scipy.optimize.minimize.

Extended to query the user for input and output cells.

This code accompanies the blog post
  https://www.pyxll.com/blog/extending-the-excel-user-interface/
"""
import sys

from functools import partial
from pyxll import xl_app, xl_menu
from win32com.client import constants
import numpy as np
from scipy.optimize import minimize
from PyQt5.QtWidgets import QApplication, QMessageBox
from op_dialog import OpDialog

def get_qt_app():
    """Returns a QApplication instance.

    MUST be called before showing any dialogs.
    """
    app = QApplication.instance()
    if app is None:
        app = QApplication([sys.executable])
    return app

def get_range(s):
    xl = xl_app()
    try:
        return xl.Range(s)
    except Exception as e:
        raise ValueError("Range specification not acceptable")

@xl_menu("Optimize5")
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(xl, in_range, out_cell, arg):
    """Wraps a spreadsheet computation as a Python function."""
    # Copy argument values to input range
    in_range.Value = [(float(x),) for x in arg]

    # Calculate after changing the inputs
    xl.Calculate()

    # Return the value of the output cell
    result = float(out_cell.Value)
    return result


text:
;
; This file is a simple way to encapsulate the configuration
; required to ensure PyXLL loads all it needs to run the
; optimisation examples.
;
; Suppose this file was at
;     C:\Users\Steve\pyxll-examples\optimization\pyxll.cfg
;
; To use this file you should ensure that its path is included
; as one of the values listed under your "external_config",
; for example:
;
; external_config =
;     C:\Users\sholden\pyxll-examples\optimization\pyxll.cfg

[PYTHON]
;pythonpath:
;
;  A list of paths Python will use when importing modules.
;  Multiple paths can be listed either as a semi-colon delimited list or across multiple indented lines.
;  Paths may be absolute paths or relative to this file.
;
pythonpath = .

[PYXLL]

;modules:
;
;  List of modules that PyXLL will load when starting.
;  Any code that is to be exposed to Excel should be added to this list, or imported from modules in this list.
;  Multiple modules can be listed either as a comma delimited list or across multiple indented lines.
;
modules =
        optimize4
        optimize5


text:
import sys

from PyQt5.QtWidgets import (
    QApplication,
    QDialog,
    QHBoxLayout,
    QVBoxLayout,
    QLabel,
    QLineEdit,
)

def get_qt_app():
    """Returns a QApplication instance.
    Must be called before showing any dialogs.
    """
    app = QApplication.instance()
    if app is None:
        app = QApplication([sys.executable])
    return app

class OpDialog(QDialog):
    """A Dialog to set input and output ranges for an optimization.

    With added stretch for a more consistent interface."""

    def __init__(self, *args, **kwargs):
        "Create a new dialogue instance."
        super().__init__(*args, **kwargs)
        self.setWindowTitle("Optimization Inputs and Output")
        self.gui_init()

    def gui_init(self):
        "Create and establish the widget layout."
        self.in_range = QLineEdit()
        self.out_cell = QLineEdit()

        row_1 = QHBoxLayout()
        row_1.addWidget(QLabel("Input range:"))
        row_1.addStretch()
        row_1.addWidget(self.in_range)

        row_2 = QHBoxLayout()
        row_2.addWidget(QLabel("Output Cell:"))
        row_2.addStretch()
        row_2.addWidget(self.out_cell)

        layout = QVBoxLayout()
        layout.addLayout(row_1)
        layout.addLayout(row_2)
        self.setLayout(layout)

if __name__ == "__main__":
    app = get_qt_app()
    msgBox = OpDialog()
    msgBox.exec_()


text:
### Optimization Examples

These examples go with the blog post at

     https://www.pyxll.com/blog/extending-the-excel-user-interface/

In order to have them work correctly you should add the
path of the configuration file in this directory to the value of
the `external_config` setting in your main configuration file's
`[PYXLL]` section.

The optimization function can then be triggered using the
"Optimize" menu item on the "PyXLL" menu on the "Add-Ins"
ribbon.


text:
<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <!-- The ribbon and context menus can be specified in the same file -->
    </ribbon>
    <contextMenus>
        <contextMenu idMso="ContextMenuCell">
            <button id="MyButton" label="Toggle Case Upper/Lower/Proper"
                insertBeforeMso="Cut"
                onAction="context_menus.toggle_case"
                imageMso="HappyFace"/>
            <menu id="MySubMenu" label="Case Menu" insertBeforeMso="Cut"  >
                <button id="Menu1Button1" label="Upper Case"
                    imageMso="U"
                    onAction="context_menus.toupper"/>
                <button id="Menu1Button2" label="Lower Case"
                    imageMso="L"
                    onAction="context_menus.tolower"/>
                <button id="Menu1Button3" label="Proper Case"
                    imageMso="P"
                    onAction="context_menus.toproper"/>
            </menu>
            <dynamicMenu id="MyDynamicMenu"
                label= "My Dynamic Menu"
                imageMso="ChangeCase"
                insertBeforeMso="Cut"
                getContent="context_menus.dynamic_menu"/>
            <menuSeparator id="MySeparator" insertBeforeMso="Cut" />
        </contextMenu>
   </contextMenus>
</customUI>


text:
"""
Callbacks for context menus example.
Requires PyXLL >= 4 and Excel >= 2010.
See ribbon.xml.
"""
from pyxll import xl_app

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 tolower(control):
    """Set the currently selected cells to lower 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.lower()

def toupper(control):
    """Set the currently selected cells to upper 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.upper()

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 dynamic_menu(control):
    """Return an xml fragment for the dynamic menu"""
    xml = """
         <menu xmlns="http://schemas.microsoft.com/office/2009/07/customui">
            <button id="Menu2Button1" label="Upper Case"
                imageMso="U"
                onAction="context_menus.toupper"/>

            <button id="Menu2Button2" label="Lower Case"
                imageMso="L"
                onAction="context_menus.tolower"/>

            <button id="Menu2Button3" label="Proper Case"
                imageMso="P"
                onAction="context_menus.toproper"/>
         </menu>
    """
    return xml


text:
; Example showing how to configure Excel context menus
;
; To try this out, add this file to your existing pyxll.cfg
; file using the 'external_config' option.
;
; Requires PyXLL >= 4 and Excel >= 2010.
;
[PYXLL]
ribbon = ribbon.xml
modules = context_menus

[PYTHON]
pythonpath = .


text:
"""
** For an easier way to do real time data in Excel with Python **
** see https://www.pyxll.com/docs/userguide/rtd.html **

This example shows how to write an Excel function using PyXLL
that connect to an RTD server and return values from it.

When the RTD server publishes updates Excel will automatically update
the cell the RTD function is called from.

The RTD server from the `exceltypes` package is used as the example
server and so that will need to be installed before this demo will work.

The `exceltypes` package can be downloaded from here:
https://github.com/pyxll/exceltypes
"""
from pyxll import xl_func, get_active_object
import win32com.client
import win32api
import win32con
import pythoncom
import win32com.client

# the TimeServer from the exceltypes demos is used as the RTD server
from exceltypes.demos.excelRTDServer import TimeServer

# calling this function from Excel returns a value from the RTD server that ticks
# when the RTD server publishes updates.
@xl_func("int seconds: var")
def py_rtd_test(seconds):
    xl = win32com.client.Dispatch(get_active_object()).Application
    return xl.WorksheetFunction.RTD(TimeServer._reg_progid_, "", "seconds", str(seconds))

def _register(cls):
    """Register an inproc com server in HKEY_CURRENT_USER.

    This may be used as a replacement for win32com.server.register.UseCommandLine
    to register the server into the HKEY_CURRENT_USER area of the registry
    instead of HKEY_LOCAL_MACHINE.
    """
    clsid_path = "Software\\Classes\\CLSID\\" + cls._reg_clsid_
    progid_path = "Software\\Classes\\" + cls._reg_progid_
    spec = cls.__module__ + "." + cls.__name__

    # register the class information
    win32api.RegSetValue(win32con.HKEY_CURRENT_USER, clsid_path, win32con.REG_SZ, cls._reg_desc_)
    win32api.RegSetValue(win32con.HKEY_CURRENT_USER, clsid_path + "\\ProgID", win32con.REG_SZ, cls._reg_progid_)
    win32api.RegSetValue(win32con.HKEY_CURRENT_USER, clsid_path + "\\PythonCOM", win32con.REG_SZ, spec)
    hkey = win32api.RegCreateKey(win32con.HKEY_CURRENT_USER, clsid_path + "\\InprocServer32")
    win32api.RegSetValueEx(hkey, None, None, win32con.REG_SZ, pythoncom.__file__)
    win32api.RegSetValueEx(hkey, "ThreadingModel", None, win32con.REG_SZ, "Both")

    # and add the progid
    win32api.RegSetValue(win32con.HKEY_CURRENT_USER, progid_path, win32con.REG_SZ, cls._reg_desc_)
    win32api.RegSetValue(win32con.HKEY_CURRENT_USER, progid_path + "\\CLSID", win32con.REG_SZ, cls._reg_clsid_)

# make sure the example RTD server is registered
_register(TimeServer)


text:
# Real Time Data in Excel with Python

This example has been superseded by the real time data feature in PyXLL 3.

https://www.pyxll.com/docs/userguide/rtd.html


text:
# IPython Excel Integration

These samples run an IPython kernel in-process in Excel and launch an IPython Qt terminal connected to that kernel.

Some of the examples also include a ribbon.xml file which can either be added to your pyxll.cfg file or combined with any existing
ribbon xml file you might have.

Add the one corresponding to your version of IPython to your pyxll.cfg file, e.g.

```ini
[PYTHON]
pythonpath =
  {path to pyxll-examples}/ipython/7.x

[PYXLL]
modules =
  ipython
ribbon = {path to pyxll-examples}/ipython/7.x/ribbon.xml
```

See requirements.txt in the version sub-folders for dependencies.


text:
"""
Start an IPython Qt console connected to the python session running in Excel.

This doesn't work with an IPython notebook as it's not possible to connect
a notebook to an existing kernel, the notebook app always creates its own.

This version is intended to work with IPython versions 1.x only.
"""
from pyxll import xl_menu
import logging
import timer
import sys
import os

_log = logging.getLogger(__name__)

_kernel_running = False
_ipython_app = None

@xl_menu("IPython")
def ipython_qtconsole():
    """
    Launches an IPython Qt console
    """
    # try to set sys.executable if it's not already set
    _fixup_sys_executable()

    # start the IPython kernel
    app = _start_kernel()

    # start a subprocess to run the Qt console
    from multiprocessing import Process
    proc = Process(target=_launch_qt_console, args=[os.getpid(), app.connection_file])
    proc.daemon = True
    proc.start()

def _fixup_sys_executable():
    """
    Set sys.executable to the default python executable, if it's not already set.
    This expects that python will be installed as the default python and pythonw.exe
    exists on the PATH.

    If you get errors when trying to launch the Qt IPython prompt with multiprocessing
    check this, and set sys.executable to the absolute location of your installed python.
    """
    # don't do anything if it's already set
    if sys.executable and os.path.basename(sys.executable) in ("python.exe", "pythonw.exe"):
        return

    executable = _which("pythonw.exe")
    if not executable:
        _log.error("Couldn't find pythonw.exe on the PATH. Starting the subprocess will fail.")
        return

    _log.info("Setting sys.executable to '%s'" % executable)
    sys.executable = executable

def _which(program):
    """find an exe's full path by looking at the PATH environment variable"""
    def is_exe(fpath):
        return os.path.isfile(fpath) and os.access(fpath, os.X_OK)

    fpath, fname = os.path.split(program)
    if fpath:
        if is_exe(program):
            return program
    else:
        for path in os.environ["PATH"].split(os.pathsep):
            path = path.strip('"')
            exe_file = os.path.join(path, program)
            if is_exe(exe_file):
                return exe_file

    return None

def _start_kernel():
    """starts the ipython kernel and returns the ipython app"""
    import IPython
    from IPython.kernel.zmq.kernelapp import IPKernelApp
    from zmq.eventloop import ioloop

    # patch IPKernelApp.start so that it doesn't block
    def _IPKernelApp_start(self):
        if self.poller is not None:
            self.poller.start()
        self.kernel.start()

        # set up a timer to periodically poll the zmq ioloop
        loop = ioloop.IOLoop.instance()

        def poll_ioloop(timer_id, time):
            global _kernel_running

            # if the kernel has been closed then run the event loop until it gets to the
            # stop event added by IPKernelApp.shutdown_request
            if self.kernel.shell.exit_now:
                _log.debug("IPython kernel stopping (%s)" % self.connection_file)
                timer.kill_timer(timer_id)
                loop.start()
                _kernel_running = False
                return

            # otherwise call the event loop but stop immediately if there are no pending events
            loop.add_timeout(0, lambda: loop.add_callback(loop.stop))
            loop.start()

        global _kernel_running
        _kernel_running = True
        timer.set_timer(100, poll_ioloop)

    IPKernelApp.start = _IPKernelApp_start

    # IPython expects sys.__stdout__ to be set
    sys.__stdout__ = sys.stdout
    sys.__stderr__ = sys.stderr

    # call the API embed function, which will use the monkey-patched method above
    IPython.embed_kernel()

    _ipython_app = IPKernelApp.instance()
    return _ipython_app

def _launch_qt_console(ppid, connection_file):
    """called as a new process"""
    from IPython.terminal.ipapp import TerminalIPythonApp
    import threading
    import psutil
    import time

    # start a thread to kill this process when the parent process exits
    def thread_func():
        while True:
            if not psutil.pid_exists(ppid):
                os._exit(1)
            time.sleep(5)
    thread = threading.Thread(target=thread_func)
    thread.daemon = True
    thread.start()

    # start the qtconsole app
    app = TerminalIPythonApp.instance()
    app.initialize(["qtconsole", "--existing", connection_file])
    app.start()


text:
<!-- Example ribbon.xml file showing how IPython can be integrated with Excel -->
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>
            <tab id="pyxll" label="PyXLL">
                <group id="Tools" label="Tools">
                    <button id="IPython"
                            size="large"
                            label="IPython"
                            onAction="ipython.ipython_qtconsole"/>
                </group>
            </tab>
        </tabs>
    </ribbon>
    <contextMenus>
        <contextMenu idMso="ContextMenuCell">
            <button id="SelectionToIPython"
                    label="Selection To IPython"
                    onAction="ipython.set_selection_in_ipython"/>
        </contextMenu>
    </contextMenus>
</customUI>


text:
"""
Start an IPython Qt console connected to the python session running in Excel.

This doesn't work with an IPython notebook as it's not possible to connect
a notebook to an existing kernel, the notebook app always creates its own.

This version is intended to work with IPython versions 7.x only.

To add this to your own pyxll config, add the following to your pyxll.cfg file:

[PYXLL]
external_config = <path to this folder>/pyxll.cfg
ribbon = <path to this folder>/ribbon.xml

The ribbon is optional. You may prefer to copy and paste the buttons from the
ribbon xml file to your own ribbon file.
"""
from pyxll import xl_menu, xl_app, xlcAlert, get_type_converter
import pyxll
import win32api
import threading
import asyncio
import logging
import sys
import os

_log = logging.getLogger(__name__)

if getattr(sys, "_ipython_kernel_running", None) is None:
    sys._ipython_kernel_running = False

if getattr(sys, "_ipython_app", None) is None:
    sys._ipython_app = False

@xl_menu("Open QtConsole", menu="IPython")
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)

@xl_menu("Selection to IPython", menu="IPython")
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 _start_kernel():
    """starts the ipython kernel in a background thread"""
    from ipykernel.kernelapp import IPKernelApp
    from IPython.terminal.ipapp import TerminalIPythonApp
    import IPython

    if sys._ipython_app and sys._ipython_kernel_running:
        return sys._ipython_app

    # IPython expects sys.__stdout__ to be set
    sys.__stdout__ = sys.stdout
    sys.__stderr__ = sys.stderr

    # event to be set when IPython is running
    event = threading.Event()

    # called after the event loop has been started and IPython is running
    def setup_ipython():
        try:
            ipy = IPKernelApp.instance()

            # Keep a reference to the kernel even if this module is reloaded
            sys._ipython_app = ipy
            sys._ipython_kernel_running = True

            # patch user_global_ns so that it always references the user_ns dict
            setattr(ipy.shell.__class__, 'user_global_ns', property(lambda self: self.user_ns))

            # patch ipapp so anything else trying to get a terminal app (e.g. ipdb) gets our IPKernalApp.
            TerminalIPythonApp.instance = lambda: ipy
            __builtins__["get_ipython"] = lambda: ipy.shell.__class__

            # Use the inline matplotlib backend
            mpl = ipy.shell.find_magic("matplotlib")
            if mpl:
                mpl("inline")
        finally:
            event.set()

    # start the event loop in a new thread
    def thread_func():
        try:
            # create a new event loop and schedule setup_ipython
            loop = asyncio.new_event_loop()
            asyncio.set_event_loop(loop)
            loop.call_soon(setup_ipython)

            # this starts the event loop
            IPython.embed_kernel()
        except:
            event.set()
            raise

    thread = threading.Thread(target=thread_func)
    thread.setDaemon(True)
    thread.start()

    # Wait for the kernel to start, or for an error to occur
    if not event.wait(10):
        raise RuntimeError("Timed out waiting for the IPython kernel to start")

    if not sys._ipython_app:
        raise RuntimeError("An error occurred starting the IPython kernel")

    return sys._ipython_app

def _which(program):
    """find an exe's full path by looking at the PATH environment variable"""
    def is_exe(fpath):
        return os.path.isfile(fpath) and os.access(fpath, os.X_OK)

    fpath, fname = os.path.split(program)
    if fpath:
        if is_exe(program):
            return program
    else:
        for path in os.environ["PATH"].split(os.pathsep):
            path = path.strip('"')
            exe_file = os.path.join(path, program)
            if is_exe(exe_file):
                return exe_file

    return None

def _launch_qt_console(connection_file):
    """Starts the jupyter console"""
    from subprocess import Popen

    # Find juypter-qtconsole.exe in the Scripts path local to python.exe
    exe = None
    if sys.executable and os.path.basename(sys.executable) in ("python.exe", "pythonw.exe"):
        path = os.path.join(os.path.dirname(sys.executable), "Scripts")
        exe = os.path.join(path, "jupyter-qtconsole.exe")

    # If it wasn't found look for it on the system path
    if exe is None or not os.path.exists(exe):
        exe = _which("jupyter-qtconsole.exe")

    if exe is None or not os.path.exists(exe):
        raise Exception("jupyter-qtconsole.exe not found")

    # run jupyter in it's own process
    cmd = [exe, "--existing", connection_file]
    proc = Popen(cmd, shell=True)
    if proc.poll() is not None:
        raise Exception("Command '%s' failed to start" % " ".join(cmd))


text:
# Requirements for PyXLL IPython itegration.
# Install using pip or conda:
#
# > pip install -r requirements.txt
# or
# > conda install --file requirements.txt
#
ipython >= 7.0.0
ipykernel
jupyter_core
jupyter_client
matplotlib
qtconsole


text:
[PYTHON]
pythonpath = .

[PYXLL]
modules = ipython


text:
"""
Start an IPython Qt console connected to the python session running in Excel.

This doesn't work with an IPython notebook as it's not possible to connect
a notebook to an existing kernel, the notebook app always creates its own.

This version is intended to work with IPython versions 4.x only.

This example requires sys.executable to be set, and so it's recommended
that the following is added to the pyxll.cfg file:

[PYTHON]
executable = <path to your python installation>/pythonw.exe

"""
from pyxll import xl_menu
import logging
import timer
import sys
import os

_log = logging.getLogger(__name__)

if getattr(sys, "_ipython_kernel_running", None) is None:
    sys._ipython_kernel_running = False

if getattr(sys, "_ipython_app", None) is None:
    sys._ipython_app = False

@xl_menu("IPython")
def ipython_qtconsole(*args):
    """
    Launches an IPython Qt console
    """
    # try to set sys.executable if it's not already set
    _fixup_sys_executable()

    # 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)

def _fixup_sys_executable():
    """
    Set sys.executable to the default python executable, if it's not already set.
    This expects that python will be installed as the default python and pythonw.exe
    exists on the PATH.

    If you get errors when trying to launch the Qt IPython prompt with multiprocessing
    check this, and set sys.executable to the absolute location of your installed python.
    """
    # don't do anything if it's already set
    if sys.executable and os.path.basename(sys.executable) in ("python.exe", "pythonw.exe"):
        return

    executable = _which("pythonw.exe")
    if not executable:
        _log.error("Couldn't find pythonw.exe on the PATH. Starting the subprocess will fail.")
        return

    _log.info("Setting sys.executable to '%s'" % executable)
    sys.executable = executable

def _which(program):
    """find an exe's full path by looking at the PATH environment variable"""
    def is_exe(fpath):
        return os.path.isfile(fpath) and os.access(fpath, os.X_OK)

    fpath, fname = os.path.split(program)
    if fpath:
        if is_exe(program):
            return program
    else:
        for path in os.environ["PATH"].split(os.pathsep):
            path = path.strip('"')
            exe_file = os.path.join(path, program)
            if is_exe(exe_file):
                return exe_file

    return None

def _start_kernel():
    """starts the ipython kernel and returns the ipython app"""
    if sys._ipython_app and sys._ipython_kernel_running:
        return sys._ipython_app

    import IPython
    from ipykernel.kernelapp import IPKernelApp
    from zmq.eventloop import ioloop

    # patch IPKernelApp.start so that it doesn't block
    def _IPKernelApp_start(self):
        if self.poller is not None:
            self.poller.start()
        self.kernel.start()

        # set up a timer to periodically poll the zmq ioloop
        loop = ioloop.IOLoop.instance()

        def poll_ioloop(timer_id, time):
            # if the kernel has been closed then run the event loop until it gets to the
            # stop event added by IPKernelApp.shutdown_request
            if self.kernel.shell.exit_now:
                _log.debug("IPython kernel stopping (%s)" % self.connection_file)
                timer.kill_timer(timer_id)
                loop.start()
                sys._ipython_kernel_running = False
                return

            # otherwise call the event loop but stop immediately if there are no pending events
            loop.add_timeout(0, lambda: loop.add_callback(loop.stop))
            loop.start()

        sys._ipython_kernel_running = True
        timer.set_timer(100, poll_ioloop)

    IPKernelApp.start = _IPKernelApp_start

    # IPython expects sys.__stdout__ to be set
    sys.__stdout__ = sys.stdout
    sys.__stderr__ = sys.stderr

    # call the API embed function, which will use the monkey-patched method above
    IPython.embed_kernel()

    sys._ipython_app = IPKernelApp.instance()

    # patch ipapp so anything else trying to get a terminal app (e.g. ipdb)
    # gets our IPKernalApp.
    from IPython.terminal.ipapp import TerminalIPythonApp
    TerminalIPythonApp.instance = lambda: sys._ipython_app
    __builtins__["get_ipython"] = lambda: sys._ipython_app.shell

    return sys._ipython_app

def _launch_qt_console(connection_file):
    """Starts the jupyter console"""
    from subprocess import Popen

    # run jupyter in it's own process
    cmd = [sys.executable, "-m", "jupyter", "qtconsole", "--existing", connection_file]
    proc = Popen(cmd)
    if proc.poll() is not None:
        raise Exception("Command '%s' failed to start" % " ".join(cmd))


text:
"""
Start an IPython Qt console connected to the python session running in Excel.

This doesn't work with an IPython notebook as it's not possible to connect
a notebook to an existing kernel, the notebook app always creates its own.

This version is intended to work with IPython 0.13 only.
"""
from pyxll import xl_menu
import logging
import timer
import sys
import os

_log = logging.getLogger(__name__)

_kernel_running = False
_ipython_app = None

@xl_menu("IPython")
def ipython_qtconsole():
    """
    Launches an IPython Qt console
    """
    # try to set sys.executable if it's not already set
    _fixup_sys_executable()

    # start the IPython kernel
    app = _start_kernel()

    # start a subprocess to run the Qt console
    from multiprocessing import Process
    proc = Process(target=_launch_qt_console, args=[os.getpid(), app.connection_file])
    proc.daemon = True
    proc.start()

def _fixup_sys_executable():
    """
    Set sys.executable to the default python executable, if it's not already set.
    This expects that python will be installed as the default python and pythonw.exe
    exists on the PATH.

    If you get errors when trying to launch the Qt IPython prompt with multiprocessing
    check this, and set sys.executable to the absolute location of your installed python.
    """
    # don't do anything if it's already set
    if sys.executable and os.path.basename(sys.executable) in ("python.exe", "pythonw.exe"):
        return

    executable = _which("pythonw.exe")
    if not executable:
        _log.error("Couldn't find pythonw.exe on the PATH. Starting the subprocess will fail.")
        return

    _log.info("Setting sys.executable to '%s'" % executable)
    sys.executable = executable

def _which(program):
    """find an exe's full path by looking at the PATH environment variable"""
    def is_exe(fpath):
        return os.path.isfile(fpath) and os.access(fpath, os.X_OK)

    fpath, fname = os.path.split(program)
    if fpath:
        if is_exe(program):
            return program
    else:
        for path in os.environ["PATH"].split(os.pathsep):
            path = path.strip('"')
            exe_file = os.path.join(path, program)
            if is_exe(exe_file):
                return exe_file

    return None

def _start_kernel():
    """starts the ipython kernel and returns the ipython app"""
    from IPython.zmq.ipkernel import IPKernelApp
    from zmq.eventloop import ioloop

    global _kernel_running, _ipython_app
    if _kernel_running:
        return _ipython_app

    # get the app if it exists, or set it up if it doesn't
    if IPKernelApp.initialized():
        app = IPKernelApp.instance()
    else:
        app = IPKernelApp.instance()
        app.initialize()

        # Undo unnecessary sys module mangling from init_sys_modules.
        # This would not be necessary if we could prevent it
        # in the first place by using a different InteractiveShell
        # subclass, as in the regular embed case.
        main = app.kernel.shell._orig_sys_modules_main_mod
        if main is not None:
            sys.modules[app.kernel.shell._orig_sys_modules_main_name] = main

    app.kernel.user_module = sys.modules[__name__]
    app.kernel.user_ns = {}

    # patch in auto-completion support
    # added in https://github.com/ipython/ipython/commit/f4be28f06c2b23cd8e4a3653b9e84bde593e4c86
    # we effectively make the same patches via monkeypatching
    from IPython.core.interactiveshell import InteractiveShell
    old_set_completer_frame = InteractiveShell.set_completer_frame

    # restore old set_completer_frame that gets no-op'd out in ZmqInteractiveShell.__init__
    bound_scf = old_set_completer_frame.__get__(app.shell, InteractiveShell)
    app.shell.set_completer_frame = bound_scf
    app.shell.set_completer_frame()

    # start the kernel
    app.kernel.start()

    # set up a timer to periodically poll the zmq ioloop
    loop = ioloop.IOLoop.instance()

    def poll_ioloop(timer_id, time):
        global _kernel_running

        # if the kernel has been closed then run the event loop until it gets to the
        # stop event added by IPKernelApp.shutdown_request
        if app.kernel.shell.exit_now:
            _log.debug("IPython kernel stopping (%s)" % app.connection_file)
            timer.kill_timer(timer_id)
            ioloop.IOLoop.instance().start()
            _kernel_running = False
            return

        # otherwise call the event loop but stop immediately if there are no pending events
        loop.add_timeout(0, lambda: loop.add_callback(loop.stop))
        ioloop.IOLoop.instance().start()

    _log.debug("IPython kernel starting. Use '--existing %s' to connect." % app.connection_file)
    timer.set_timer(100, poll_ioloop)
    _kernel_running = True

    _ipython_app = app
    return _ipython_app

def _launch_qt_console(ppid, connection_file):
    """called as a new process"""
    from IPython.frontend.terminal.ipapp import TerminalIPythonApp
    import threading
    import psutil
    import time

    # start a thread to kill this process when the parent process exits
    def thread_func():
        while True:
            if not psutil.pid_exists(ppid):
                os._exit(1)
            time.sleep(5)
    thread = threading.Thread(target=thread_func)
    thread.daemon = True
    thread.start()

    # start the qtconsole app
    app = TerminalIPythonApp.instance()
    app.initialize(["qtconsole", "--existing", connection_file])
    app.start()


text:
<!-- Example ribbon.xml file showing how IPython can be integrated with Excel -->
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>
            <tab id="pyxll" label="PyXLL">
                <group id="Tools" label="Tools">
                    <button id="IPython"
                            size="large"
                            label="IPython"
                            onAction="ipython.ipython_qtconsole"/>
                </group>
            </tab>
        </tabs>
    </ribbon>
    <contextMenus>
        <contextMenu idMso="ContextMenuCell">
            <button id="SelectionToIPython"
                    label="Selection To IPython"
                    onAction="ipython.set_selection_in_ipython">
        </contextMenu>
    </contextMenus>
</customUI>


text:
"""
Start an IPython Qt console connected to the python session running in Excel.

This doesn't work with an IPython notebook as it's not possible to connect
a notebook to an existing kernel, the notebook app always creates its own.

This version is intended to work with IPython versions 6.x only.

This example requires sys.executable to be set, and so it's recommended
that the following is added to the pyxll.cfg file:

[PYTHON]
executable = <path to your python installation>/pythonw.exe

"""
from pyxll import xl_menu, xl_app, get_type_converter
import pyxll
import logging
import timer
import sys
import os

_log = logging.getLogger(__name__)

try:
    import win32api
except ImportError:
    win32api = None

if getattr(sys, "_ipython_kernel_running", None) is None:
    sys._ipython_kernel_running = False

if getattr(sys, "_ipython_app", None) is None:
    sys._ipython_app = False

@xl_menu("Open QtConsole", menu="IPython")
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:
        if win32api:
            win32api.MessageBox(None, "Error starting IPython Qt console")
        _log.error("Error starting IPython Qt console", exc_info=True)

@xl_menu("Selection to IPython", menu="IPython")
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 _which(program):
    """find an exe's full path by looking at the PATH environment variable"""
    def is_exe(fpath):
        return os.path.isfile(fpath) and os.access(fpath, os.X_OK)

    fpath, fname = os.path.split(program)
    if fpath:
        if is_exe(program):
            return program
    else:
        for path in os.environ["PATH"].split(os.pathsep):
            path = path.strip('"')
            exe_file = os.path.join(path, program)
            if is_exe(exe_file):
                return exe_file

    return None

def _start_kernel():
    """starts the ipython kernel and returns the ipython app"""
    if sys._ipython_app and sys._ipython_kernel_running:
        return sys._ipython_app

    import IPython
    from ipykernel.kernelapp import IPKernelApp
    from zmq.eventloop import ioloop

    # patch IPKernelApp.start so that it doesn't block
    def _IPKernelApp_start(self):
        if self.poller is not None:
            self.poller.start()
        self.kernel.start()

        # set up a timer to periodically poll the zmq ioloop
        loop = ioloop.IOLoop.instance()

        def poll_ioloop(timer_id, time):
            # if the kernel has been closed then run the event loop until it gets to the
            # stop event added by IPKernelApp.shutdown_request
            if self.kernel.shell.exit_now:
                _log.debug("IPython kernel stopping (%s)" % self.connection_file)
                timer.kill_timer(timer_id)
                loop.start()
                sys._ipython_kernel_running = False
                return

            # otherwise call the event loop but stop immediately if there are no pending events
            loop.add_timeout(0, lambda: loop.add_callback(loop.stop))
            loop.start()

        sys._ipython_kernel_running = True
        timer.set_timer(100, poll_ioloop)

    IPKernelApp.start = _IPKernelApp_start

    # IPython expects sys.__stdout__ to be set
    sys.__stdout__ = sys.stdout
    sys.__stderr__ = sys.stderr

    # call the API embed function, which will use the monkey-patched method above
    IPython.embed_kernel()

    ipy = IPKernelApp.instance()

    # Keep a reference to the kernel even if this module is reloaded
    sys._ipython_app = ipy

    # patch user_global_ns so that it always references the user_ns dict
    setattr(ipy.shell.__class__, 'user_global_ns', property(lambda self: self.user_ns))

    # patch ipapp so anything else trying to get a terminal app (e.g. ipdb) gets our IPKernalApp.
    from IPython.terminal.ipapp import TerminalIPythonApp
    TerminalIPythonApp.instance = lambda: ipy
    __builtins__["get_ipython"] = lambda: ipy.shell.__class__

    # Use the inline matplotlib backend
    mpl = ipy.shell.find_magic("matplotlib")
    if mpl:
        mpl("inline")

    return ipy

def _launch_qt_console(connection_file):
    """Starts the jupyter console"""
    from subprocess import Popen

    # Find juypter-qtconsole.exe in the Scripts path local to python.exe
    exe = None
    if sys.executable and os.path.basename(sys.executable) in ("python.exe", "pythonw.exe"):
        path = os.path.join(os.path.dirname(sys.executable), "Scripts")
        exe = os.path.join(path, "jupyter-qtconsole.exe")

    # If it wasn't found look for it on the system path
    if exe is None or not os.path.exists(exe):
        exe = _which("jupyter-qtconsole.exe")

    if exe is None or not os.path.exists(exe):
        raise Exception("jupyter-qtconsole.exe not found")

    # run jupyter in it's own process
    cmd = [exe, "--existing", connection_file]
    proc = Popen(cmd, shell=True)
    if proc.poll() is not None:
        raise Exception("Command '%s' failed to start" % " ".join(cmd))


text:
# Requirements for PyXLL IPython itegration.
# Install using pip or conda:
#
# > pip install -r requirements.txt
# or
# > conda install --file requirements.txt
#
ipython >= 6.0.0
ipykernel
jupyter_core
jupyter_client
pyqt


text:
"""
Optimisation example using scipy.optimize.minimize.

This example shows how to find a minimum for a function
of two variables, though the principles are very easily
extended to N.

This version uses named ranges to access the input and
output cells of the objective function.

This code accompanies the blog post
  https://www.pyxll.com/blog/a-better-goal-seek/
"""
from functools import partial
from pyxll import xl_macro, xl_app
from win32com.client import constants
import numpy as np
from scipy.optimize import minimize

@xl_macro(shortcut="Ctrl+Alt+R")
def optimize3():
    xl = xl_app()
    # Get the initial values of the input cells
    in_values = xl.Range('Inputs').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)
        minimize(xl_obj_func, X, method='nelder-mead')

    finally:
        # restore the original calculation
        # and screen updating mode
        xl.ScreenUpdating = True
        xl.Calculation = orig_calc_mode

def obj_func(xl, arg):
    """Wraps a spreadsheet computation as a Python function."""
    # Copy argument values to input range
    xl.Range('Inputs').Value = [(float(x), ) for x in arg]

    # Calculate after changing the inputs
    xl.Calculate()

    # Return the value of the output cell
    result = xl.Range("Output").Value
    return result


text:
"""
Optimisation example using scipy.optimize.minimize.

This example shows how to find a minimum for a function
of two variables, though the principles are very easily
extended to N.

This version uses ranges to access the input values
rather than loading each cell individually.

This code accompanies the blog post
  https://www.pyxll.com/blog/a-better-goal-seek/
"""
from pyxll import xl_macro, xl_app
from win32com.client import constants
import numpy as np
from scipy.optimize import minimize

@xl_macro(shortcut="Ctrl+Alt+Q")
def optimize2():
    xl = xl_app()
    # Get the initial values of the input cells
    in_values = xl.Range('C11:C12').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
        minimize(obj_func, X, method='nelder-mead')

    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


text:
"""
Optimisation example using scipy.optimize.minimize.

This example shows how to find a minimum for a function
of two variables, though the principles are very easily
extended to N.

This code accompanies the blog post
  https://www.pyxll.com/blog/a-better-goal-seek/
"""
from pyxll import xl_macro, xl_app
from scipy.optimize import minimize
from win32com.client import constants
import numpy as np

@xl_macro(shortcut="Ctrl+Alt+P")
def optimise1():
    xl = xl_app()
    # Get the initial values of the input cells
    x = xl.Range("C11").Value
    y = xl.Range("C12").Value
    X = np.array([x, y])

    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
        minimize(obj_func, X, method='nelder-mead')

    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').Value = float(arg[0])
    xl.Range('C12').Value = float(arg[1])

    # Calculate after changing the inputs
    xl.Calculate()

    # Return the value of the output cell
    result = xl.Range("E11").Value
    return result


text:
;
; This file is a simple way to encapsulate the configuration
; required to ensure PyXLL loads all it needs to run the
; optimisation examples.
;
; Suppose this file was at
;     C:\Users\Steve\pyxll-examples\optimization\pyxll.cfg
;
; To use this file you should ensure that its path is included
; as one of the values listed under your "external_config",
; for example:
;
; external_config =
;     C:\Users\sholden\pyxll-examples\optimization\pyxll.cfg

[PYTHON]
;pythonpath:
;
;  A list of paths Python will use when importing modules.
;  Multiple paths can be listed either as a semi-colon delimited list or across multiple indented lines.
;  Paths may be absolute paths or relative to this file.
;
pythonpath = .

[PYXLL]

;modules:
;
;  List of modules that PyXLL will load when starting.
;  Any code that is to be exposed to Excel should be added to this list, or imported from modules in this list.
;  Multiple modules can be listed either as a comma delimited list or across multiple indented lines.
;
modules =
        optimize1
        optimize2
        optimize3


text:
### Optimization Examples

These examples go with the blog post at

     https://www.pyxll.com/blog/a-better-goal-seek/

In order to have them work correctly you should add the
path of the configuration file in this directory to the value of
the `external_config` setting in your main configuration file's
`[PYXLL]` section.

The three different macro implementations will then be available
as Ctrl+Alt+P, Ctrl+Alt+Q and Ctrl+Alt+R.


text:
"""
Example Excel macro bound to a shortcut key.

The macro 'resize_array_formula' looks at the currently selected cell
and if it's an array formula re-calculates that cell and resizes the
output range so that it matches the dimensions of the result of the array
formula.

This can be useful when dealing with array formulas where the dimensions
of the result can vary and it's tedious to have to keep resizing the
Excel formula manually.

The function is registered as a menu item as well as a macro and can
be run from the menu or via the keyboard shortcut Ctrl+Shift+R.
"""
from pyxll import xl_menu, xl_macro, get_active_object
from shortcuts import xl_shortcut
from pywintypes import com_error
from shortcuts import xl_shortcut
from win32com.client import Dispatch
from win32com.client import constants
import win32api
import win32con
import logging

_log = logging.getLogger(__name__)

@xl_shortcut("Ctrl+Shift+R")
@xl_menu("Resize Array Formula (Ctrl+Shift+R)")
@xl_macro("")
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)

    try:
        width, height = 0, len(result)
    except (AttributeError, TypeError) as e:
        _log.warn("Not an array function. '%s' returned '%s'" % (formula, result), exc_info=True)
        result = win32api.MessageBox(None,
                                     "Formula didn't return an array: %s" % result,
                                     "Error",
                                     win32con.MB_OKCANCEL | win32con.MB_ICONERROR)
        return

    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

    try:
        # clear the old range (checking first we can set the formula on the old range
        # back later if necessary)
        current_range.FormulaArray = formula
        current_range.ClearContents()

        # set the formula on the new range
        new_range.FormulaArray = formula
    except Exception as e:
        result = win32api.MessageBox(None,
                                     ("Error resizing range: %s\n\n" % e) + formula,
                                     "Error",
                                     win32con.MB_OKCANCEL | win32con.MB_ICONERROR)
        _log.error("Error resizing range", exc_info=True)
        current_range.FormulaArray = formula

def _expand_range(xl, selection):
    """
    Expand a range to include the whole formula range, if the initial
    range is part of an array formula.
    """
    formula = selection.FormulaArray
    if not (formula and (formula.startswith("=") or formula.startswith("+"))):
        # nothing to do
        return  selection

    # Range.Offset if weird as a value of 1 results in no offset
    idx_offset = 1
    # find if there are any cells above the selection with the same formula
    top_left = selection
    try:
        if top_left.Offset(idx_offset-1).FormulaArray == formula:
            # select up (this could go outside the formula range)
            top_left = top_left.End(constants.xlUp)

            # move down until we find the formula
            while top_left.FormulaArray != formula:
                top_left = top_left.Offset(idx_offset+1)
    except com_error:
        pass

    try:
        if top_left.Offset(idx_offset+0, idx_offset-1).FormulaArray == formula:
            # select left (this could go outside the formula range)
            top_left = top_left.End(constants.xlToLeft)

            # move right until we find the formula
            while top_left.FormulaArray != formula:
                top_left = top_left.Offset(idx_offset+0, idx_offset+1)
    except com_error:
        pass

    bottom_right = selection
    try:
        if bottom_right.Offset(idx_offset+1).FormulaArray == formula:
            # select down (this could go outside the formula range)
            bottom_right = bottom_right.End(constants.xlDown)

            # move up until we find the formula
            while bottom_right.FormulaArray != formula:
                bottom_right = bottom_right.Offset(idx_offset-1)
    except com_error:
        pass

    try:
        if bottom_right.Offset(idx_offset+0, idx_offset+1).FormulaArray == formula:
            # select right (this could go outside the formula range)
            bottom_right = bottom_right.End(constants.xlToRight)

            # move left until we find the formula
            while bottom_right.FormulaArray != formula:
                bottom_right = bottom_right.Offset(idx_offset+0, idx_offset-1)
    except com_error:
        pass

    return xl.Range(top_left, bottom_right)


text:
"""
Example code showing how PyXLL and win32com can be used to register macros and
associate them to keyboard shortcuts.

The decorator @xl_shortcut is used to decorate a macro function, and when
Excel starts win32com is used to associate that macro to a shortcut.

Example::

    from pyxll import xl_macro, xlcAlert
    from shortcuts import xl_shortcut

    @xl_shortcut("Ctrl+Shift+H")
    @xl_macro()
    def my_macro():
        xlcAlert("Hello!")

Pressing Ctrl+Shift+H calls the macro and shows the alert.

"""
from pyxll import get_active_object
from win32com.client import Dispatch
import logging
import timer
import time

_log = logging.getLogger(__name__)

# the shortcuts are added in the main window loop using timer
_timer_id = None
_shortcuts_to_add = []

def xl_shortcut(shortcut):
    """
    A decorator that can be applied to a PyXLL macro function
    that assocaiates a keyboard shortcut with the macro.

    The macro should take no arguments.

    :param shortcut: keyboard shortcut to assign to the macro,
                     e.g. Ctrl+Shift+R.
    :return: decorator to be applied to a macro function.
    """
    def make_decorator(shortcut):
        # convert the shortcut into a key code OnKey understands
        special_keys = {
            "ctrl"  : "^",
            "alt"   : "%",
            "shift" : "+",
        }

        accelerator = ""
        for key in shortcut.lower().split("+"):
            if key in special_keys:
                accelerator += special_keys[key]
                continue

            if key[0] == "f" and key[1:].isdigit():
                accelerator += "{F%s}" % key[1:]
                continue

            if len(key) > 1:
                raise Exception("Unrecognized shortcut %s" % shortcut)
            accelerator += key

        # the decorator that will add the shortcut and macroname to the list of
        # pending shortcuts to be added later.
        def xl_shortcut_decorator(func):
            global _timer_id

            # add the function and accelerator to the list of shortcuts to be processed
            _log.debug("Adding shortcut %s -> %s" % (shortcut, func.__name__))
            _shortcuts_to_add.append((accelerator, func.__name__))

            # start the timer to process the shortcuts list if necessary
            if _timer_id is None:
                _start_timer()

        return xl_shortcut_decorator

    return make_decorator(shortcut)

def _start_timer(timeout=300, interval=0.1):
    """
    Creates and starts a timer function that adds any registered shortcuts
    to Excel.

    As the COM object needed to do that may not exist at startup this
    function retries a number of times.
    """
    global _timer_id

    def make_timer_func():
        start_time = time.time()
        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
        return on_timer

    # start the timer
    _timer_id = timer.set_timer(int(interval * 1000), make_timer_func())


text:
"""
Test code from the docstring in shortcuts.py.

Add this to your pyxll.cfg to test.
"""
from pyxll import xl_macro, xlcAlert
from shortcuts import xl_shortcut

@xl_shortcut("Ctrl+Shift+H")
@xl_macro("")
def my_macro():
    xlcAlert("Hello!")


text:
# Excel Keyboard Shortcuts

Keyboard shortcuts are supported by PyXLL 3 without any additional code.

New users should use this instead of code from this example.

See https://www.pyxll.com/docs/userguide/macros.html#keyboard-shortcuts for details.


text:
"""This example shows using the unittest.mock package
to mock the results of xl_app.Range.

Note: unitest.mock was added in Python 3.3. If you are
using an earlier version then you will need to pip
install 'mock' and change the import to import from
'mock' instead of 'unittest.mock'.

If you cannot import pyxll then see the notes in the README.md
file alongside this file.
"""
from pyxll import xl_macro, xl_app
from unittest.mock import patch, MagicMock
import unittest

#
# This is an example macro that we might want to test.
#
# It is just a simple example that uses PyXLL's
# xl_app function so we can demonstrate how to mock
# the Excel.Application object returned by xl_app.
#
@xl_macro("Example Macro")
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

class MacroTests(unittest.TestCase):

    # IMPORTANT
    #
    # When mocking we must patch *where an object is looked up*.
    # This is not necessarily the same place as where it is defined.
    # Here our code uses the local module variable 'xl_app' and
    # *not' 'pyxll.xl_app' and so we have to patch the local 'xl_app'.
    #
    # You will want to replace this with @patch("your-module.xl_app")
    # if you are using "import xl_app from pyxll", or
    # @patch("pyxll.xl_app") if you are using "import pyxll".
    #
    @patch(f"{__name__}.xl_app")
    def test_macro(self, mock_xl_app):
        # Mock the "Range" property with a side effect to return a different
        # mocked 'Range' object for different addresses, with "Value" set
        # for address A1 and A2.
        mocked_range_values = {
            "A1": 1.0,
            "A2": 2.0
        }

        mocked_ranges = {}

        # This function is the side_effect of xl_app().Range and
        # returns a different mock object for each address.
        def create_mock_range(address):
            if address in mocked_ranges:
                return mocked_ranges[address]

            mocked_range = MagicMock()
            if address in mocked_range_values:
                mocked_range.Value = mocked_range_values[address]

            mocked_ranges[address] = mocked_range
            return mocked_range

        mock_xl = mock_xl_app()
        mock_xl.Range.side_effect = create_mock_range

        # Call the example macro
        example_macro()

        # Check that A3 has been set to 3.0 (A3 = A1 + A2; A1 = 1.0; A2 = 2.0)
        self.assertEqual(3.0, mock_xl_app().Range("A3").Value)


text:
"""This example shows a unit test of a simple Excel
worksheet function.

If you cannot import pyxll then see the notes in the README.md
file alongside this file.
"""
from pyxll import xl_func
import unittest

#
# This is an example UDF that we might want to test.
# For a more complex example showing mocking, see the
# test_macro.py test in the same folder as this file.
#
@xl_func
def example_udf(a, b, c):
    return (a + b) * c

class UdfTests(unittest.TestCase):

    def test_udf(self):
        # When called outside of Excel the @xl_func decorator doesn't
        # do anything so we can simply call the function in the same
        # way as any other Python function.
        actual = example_udf(1, 2, 3)
        expected = (1 + 2) * 3

        self.assertEqual(expected, actual)


text:
# Unit Testing PyXLL Functions

PyXLL provides an implementation of the `pyxll` module that can
be imported *outside* of Excel. This is so that you can import
your PyXLL code without it running in Excel, for unit-testing
as one example.

It is supplied as a wheel file in the downloaded PyXLL zip file
and can be installed using *pip*.

You will need to substitute the folder and wheel filename to where
you have unzipped PyXLL in the below example.

```bash
cd c:\Users\jondoe\pyxll
pip install  pyxll-4.4.2-cp37-none-win32.whl
```

The files in this folder demonstrate how the Python unittest
package can be used to test PyXLL code.


text:
# Example showing how to efficiently use pandas in Excel
# to analyse the US Cities data set.
#
# The US Cities data set needed for this example can be downloaded
# from https://simplemaps.com/data/us-cities.
#
# For more information about using pandas types with PyXLL see:
# https://www.pyxll.com/docs/userguide/pandas.html
#
# For information about pandas see:
# http://pandas.pydata.org/
#
[PYTHON]
pythonpath = .

[PYXLL]
modules =
    uscities


text:
"""
This module contains the Excel functions used in the uscities.xlsx workbook.

See README.md for more information about this example.
"""
from pyxll import xl_func, xl_arg, xl_return
import pandas as pd
import os

# The signature specifies that 'path' is a string, and the result will be
# returned to Excel as an object handle instead of expanding the DataFrame
# to a range in Excel.
@xl_func("string path: object")
def load_csv_data(path):
    """Loads a CSV file as a pandas DataFrame."""

    # If the path is a relative path make it absolute from this folder.
    if path.startswith("."):
        path = os.path.join(os.path.dirname(__file__), path)

    # Load the CSV file and return it as a pandas DataFrame
    df = pd.read_csv(path)
    return df

# Here the signature specifies that the returned DataFrame should be be
# returned to Excel as a DataFrame (rather than an object as above) and
# it should include the index.
@xl_func("dataframe: dataframe<index=True>")
def df_describe(df):
    """Get some stats about the dataframe"""
    return df.describe()

@xl_func("dataframe df, var[][] filter_values: object")
def df_filter(df, filter_values):
    """
    Filter a dataframe and return the filtered result.
    Takes a dataframe and a list of (boolean operator, column, operation, value)
    rows, eg
        [ (None,  "A", "==", "X"),
          ("AND", "B", "==", "Y"),
          ("OR",  "C", "!=", "Z") ]
    """
    mask = None
    mask_op = "AND"

    for bool_op, col, row_op, value in filter_values:
        # The 'bool_op' variable can be None, in which case the previous value is used.
        if bool_op is not None:
            mask_op = bool_op.upper()

        # Skip blank rows
        if not col:
            continue

        # Get the values that match
        if row_op == "==":
            row_mask = df[col] == value
        elif row_op == "!=":
            row_mask = df[col] != value
        elif row_op == ">=":
            row_mask = df[col] >= value
        elif row_op == ">":
            row_mask = df[col] > value
        elif row_op == "<=":
            row_mask = df[col] <= value
        elif row_op == "<":
            row_mask = df[col] < value
        else:
            raise ValueError(f"Unexpected operator '{row_op}'")

        # If it's the first row then there's nothing to combine it with
        if mask is None:
            mask = row_mask
            continue

        # Combine the mask from this row with the main mask
        if mask_op == "AND":
            mask = mask & row_mask
        elif mask_op == "OR":
            mask = mask | row_mask
        else:
            raise ValueError(f"Unexpected mask operator '{mask_op}'")

    # Filter the DataFrame using the mask
    if mask is not None:
        df = df[mask]

    return df

@xl_func("dataframe df, int n: dataframe<index=True>")
def df_head(df, n):
    """Get the first n rows of a dataframe"""
    return df.head(n)

# I've used xl_arg and xl_return instead of a signature here. The two methods
# are equivalent, but using xl_arg and xl_return can be easier when dealing
# with a number of complex arguments.
@xl_func
@xl_arg("df", "dataframe")
@xl_arg("columns", "string[]")
@xl_arg("agg_funcs", "dict<string, string>")
@xl_arg("transpose", "bool")
@xl_return("dataframe", index=True, multi_sparse=False)
def df_pivot_table(df, columns, agg_funcs, transpose=False):
    # remove empty columns
    columns = filter(None, columns)

    # pivot the data
    df = pd.pivot_table(df, columns=columns, aggfunc=agg_funcs)

    # transpose if needed
    if transpose:
        df = df.transpose()

    return df

# Plot the population density on a map using plotly.
# This will only work if you have plotly and kaleido installed.
# See https://www.pyxll.com/docs/userguide/plotting/plotly.html
@xl_func
def plot_density_mapbox(df,
                        enabled=True,
                        lat_center=40,
                        lng_center=-95,
                        zoom=3,
                        radius=25,
                        lat="lat",
                        lng="lng",
                        z="population",
                        mapbox_style="stamen-terrain"):
    """Plots a density mapbox using Plotly Express."""
    if not enabled:
        return "[PLOTTING DISABLED]"

    # Use plotly express to plot the mapbox
    import plotly.express as px

    fig = px.density_mapbox(df,
                            lat=lat,
                            lon=lng,
                            z=z,
                            radius=radius,
                            center=dict(lat=lat_center, lon=lng_center),
                            zoom=zoom,
                            mapbox_style=mapbox_style)

    # Display the figure in Excel (requires kaleido)
    from pyxll import plot
    plot(fig)

    return "[OK]"


text:
# US Cities Pandas Example

This example shows how to efficiently use pandas in Excel to analyse the US Cities data set.
By keeping the data in Python and performing filter and aggregation in Python we keep
the spreadsheet small and simply, while still processing the entire data set.

You will need to download the US Cities Data Set from https://simplemaps.com/data/us-cities
in order to use this example. Download and unzip the data set, and then update the path to
the uscities.csv file in the example workbook uscities.xlsx.

For more information about using pandas types with PyXLL see
https://www.pyxll.com/docs/userguide/pandas.html

For information about pandas see
http://pandas.pydata.org/


text:
"""
Custom excel types for pandas objects (eg dataframes).

For information about custom types in PyXLL see:
https://www.pyxll.com/docs/udfs.html#custom-types

For information about pandas see:
http://pandas.pydata.org/

Including this module in your pyxll config adds the following custom types that can
be used as return and argument types to your pyxll functions:

    - dataframe
    - series
    - series_t

Dataframes with multi-index indexes or columns will be returned with the columns and
index values in the resulting array. For normal indexes, the index will only be
returned as part of the resulting array if the index is named.

eg::

    from pyxll import xl_func
    import pandas as pa

    @xl_func("int rows, int cols, float value: dataframe")
    def make_empty_dataframe(rows, cols, value):
        # create an empty dataframe
        df = pa.DataFrame({chr(c + ord('A')) : value for c in range(cols)}, index=range(rows))

        # return it. The custom type will convert this to a 2d array that
        # excel will understand when this function is called as an array
        # function.
        return df

    @xl_func("dataframe df, string col: float")
    def sum_column(df, col):
        return df[col].sum()

In excel (use Ctrl+Shift+Enter to enter an array formula)::

    =make_empty_dataframe(3, 3, 100)

    >>  A    B    C
    >> 100    100    100
    >> 100    100    100
    >> 100    100    100

    =sum_column(A1:C4, "A")

    >> 300
"""
from pyxll import xl_return_type, xl_arg_type
import datetime as dt
import pandas as pa
import numpy as np
import pytz

try:
    import pywintypes
except ImportError:
    pywintypes = None

@xl_return_type("dataframe", "var")
def _dataframe_to_var(df):
    """return a list of lists that excel can understand"""
    if not isinstance(df, pa.DataFrame):
        return df
    df = df.applymap(lambda x: RuntimeError() if isinstance(x, float) and np.isnan(x) else x)

    index_header = [str(df.index.name)] if df.index.name is not None else []
    if isinstance(df.index, pa.MultiIndex):
        index_header = [str(x) or "" for x in df.index.names]

    if isinstance(df.columns, pa.MultiIndex):
        result = [([""] * len(index_header)) + list(z) for z in zip(*list(df.columns))]
        for header in result:
            for i in range(1, len(header) - 1):
                if header[-i] == header[-i-1]:
                    header[-i] = ""

        if index_header:
            column_names = [x or "" for x in df.columns.names]
            for i, col_name in enumerate(column_names):
                result[i][len(index_header)-1] = col_name

            if column_names[-1]:
                index_header[-1] += (" \ " if index_header[-1] else "") + str(column_names[-1])

            num_levels = len(df.columns.levels)
            result[num_levels-1][:len(index_header)] = index_header
    else:
        if index_header and df.columns.name:
            index_header[-1] += (" \ " if index_header[-1] else "") + str(df.columns.name)
        result = [index_header + list(df.columns)]

    if isinstance(df.index, pa.MultiIndex):
        prev_ix = None
        for ix, row in df.iterrows():
            header = list(ix)
            if prev_ix:
                header = [x if x != px else "" for (x, px) in zip(ix, prev_ix)]
            result.append(header + list(row))
            prev_ix = ix

    elif index_header:
        for ix, row in df.iterrows():
            result.append([ix] + list(row))
    else:
        for ix, row in df.iterrows():
            result.append(list(row))

    return _normalize_dates(result)

@xl_return_type("series", "var")
def _series_to_var(s):
    """return a list of lists that excel can understand"""
    if not isinstance(s, pa.Series):
        return s

    # convert any errors to exceptions so they appear correctly in Excel
    s = s.apply(lambda x: RuntimeError() if isinstance(x, float) and np.isnan(x) else x)

    result = list(map(list, zip(s.index, s)))
    return _normalize_dates(result)

@xl_return_type("series_t", "var")
def _series_to_var_transform(s):
    """return a list of lists that excel can understand"""
    if not isinstance(s, pa.Series):
        return s

    # convert any errors to exceptions so they appear correctly in Excel
    s = s.apply(lambda x: RuntimeError() if isinstance(x, float) and np.isnan(x) else x)

    result = list(map(list, zip(*zip(s.index, s))))
    return _normalize_dates(result)

@xl_arg_type("dataframe", "var")
def _var_to_dataframe(x):
    """return a pandas DataFrame from a list of lists"""
    if not isinstance(x, (list, tuple)):
        raise TypeError("Expected a list of lists")

    x = _fix_pywintypes(x)
    columns = x[0]
    rows = x[1:]
    return pa.DataFrame(list(rows), columns=columns)

@xl_arg_type("series", "var")
def _var_to_series(s):
    """return a pandas Series from a list of lists (arranged vertically)"""
    if not isinstance(s, (list, tuple)):
        raise TypeError("Expected a list of lists")

    s = _fix_pywintypes(s)
    keys, values = [], []
    for row in s:
        if not isinstance(row, (list, tuple)):
            raise TypeError("Expected a list of lists")

        if len(row) < 2:
            raise RuntimeError("Expected rows of length 2 to convert to a pandas Series")
        key, value = row[:2]
        # skip any empty rows
        if key is None and value is None:
            continue
        keys.append(key)
        values.append(value)

    return pa.Series(values, index=keys)

@xl_arg_type("series_t", "var")
def _var_to_series_t(s):
    """return a pandas Series from a list of lists (arranged horizontally)"""
    if not isinstance(s, (list, tuple)):
        raise TypeError("Expected a list of lists")

    s = _fix_pywintypes(s)
    keys, values = [], []
    for row in zip(*s):
        if not isinstance(row, (list, tuple)):
            raise TypeError("Expected a list of lists")

        if len(row) < 2:
            raise RuntimeError("Expected rows of length 2 to convert to a pandas Series")
        key, value = row[:2]
        # skip any empty rows
        if key is None and value is None:
            continue
        keys.append(key)
        values.append(value)

    return pa.Series(values, index=keys)

def _normalize_dates(data):
    """
    Ensure all date types returns are standard datetimes with a timezone.
    pythoncom will fail to convert datetimes to Windows dates without tzinfo.

    This is useful if using these functions to convert a dataframe to native
    python types for setting to a Range using COM. If only passing objects
    to/from python using PyXLL functions then this isn't necessary (but
    isn't harmful either).
    """
    def normalize_date(x):
        if isinstance(x, pa.tslib.NaTType):
            return ValueError()
        elif isinstance(x, pa.tslib.Timestamp) or isinstance(x, dt.datetime):
            return dt.datetime(*x.timetuple()[:6], tzinfo=x.tzinfo or pytz.utc)
        elif isinstance(x, dt.date):
            return dt.datetime(*x.timetuple()[:3], tzinfo=pytz.utc)
        return x

    return [[normalize_date(c) for c in r] for r in data]

def _fix_pywintypes(data):
    """
    Converts any pywintypes.TimeType instances passed in to the
    conversion functions into datetime types.

    This is useful if using these functions to convert a n Excel Range of
    of values a pandas type, as pandas will crash if called with the
    pywintypes.TimeType.
    """
    if pywintypes is None:
        return data

    def fix_pywintypes(c):
        if isinstance(c, pywintypes.TimeType):
            return dt.datetime(*c.timetuple()[:6])
        return c

    return [[fix_pywintypes(c) for c in r] for r in data]


text:
# Custom excel types for pandas objects (eg dataframes).
#
# For information about custom types in PyXLL see:
# https://www.pyxll.com/docs/udfs.html#custom-types
#
# For information about pandas see:
# http://pandas.pydata.org/
#
# Add the pandastypes module to your own project to be able to
# seamlessly use pandas types with PyXLL.

[PYXLL]
pythonpath = .
modules =
    pandastypes
    pandas_example


text:
"""
Custom excel types for pandas objects (eg dataframes).

For information about custom types in PyXLL see:
https://www.pyxll.com/docs/udfs.html#custom-types

For information about pandas see:
http://pandas.pydata.org/

Example code using 'pandastypes.py'.
"""

from pyxll import xl_func
import pandas as pa
import numpy as np

@xl_func("int rows, int cols: dataframe")
def make_random_dataframe(rows, cols):
    # create a random dataframe
    df_dict = {}
    for col_index in range(cols):
        col_name = chr(col_index + ord('A'))
        col_values = [np.random.random() for r in range(rows)]
        df_dict[col_name] = col_values

    df = pa.DataFrame(df_dict)

    # return it. The custom type will convert this to a 2d array that
    # excel will understand when this function is called as an array
    # function.
    return df

@xl_func("dataframe df, string col: float")
def sum_column(df, col):
    """take a dataframe and return the sum of a single column"""
    return df[col].sum()

@xl_func("dataframe df, string group_by_col, string value_col: series")
def group_by(df, group_by_col, value_col):
    """take a dataframe and group it by a single column and return the sum of another"""
    return df.groupby([group_by_col])[value_col].sum()


text:
# Pandas types are now officially supported by PyXLL

This is example is now obsolete as Pandas type support is included in PyXLL 4.0.

See https://www.pyxll.com/docs/userguide/udfs.html#pandas-types.


text:
# Config file for twitterxl demo.
# This can be referenced by your existing pyxll.cfg by adding
#
# [PYXLL]
# external_config = <full path to this file>
#
[PYXLL]
modules = twitterxl

[PYTHON]
pythonpath = .


text:
from pyxll import RTD, xl_func, xl_on_close
from tweepy.streaming import StreamListener
from tweepy import OAuthHandler
from tweepy import Stream
import threading
import itertools
import logging
import json

_log = logging.getLogger(__name__)

# User credentials to access Twitter API
access_token = "YOUR ACCESS TOKEN"
access_token_secret = "YOUR ACCESS TOKEN SECRET"
consumer_key = "YOUR CONSUMER KEY"
consumer_secret = "YOUR CONSUMER KEY SECRET"

class TwitterListener(StreamListener):
    """tweepy.StreamListener that notifies multiple subscribers when
    new tweets are received and keeps a buffer of the last 100 tweets
    received.
    """
    __listeners = {}  # class level cache of listeners, keyed by phrases
    __lock = threading.RLock()
    __max_size = 100

    @classmethod
    def get_listener(cls, phrases, subscriber):
        """Fetch an ExcelListener listening to a set of phrases and subscribe to it"""
        with cls.__lock:
            # get the listener from the cache or create a new one
            phrases = frozenset(map(str, phrases))
            listener = cls.__listeners.get(phrases, None)
            if listener is None:
                listener = cls(phrases)
                cls.__listeners[phrases] = listener

            # add the subscription and return
            listener.subscribe(subscriber)
            return listener

    def __init__(self, phrases):
        """Use static method 'get_listener' instead of constructing directly"""
        _log.info("Creating listener for [%s]" % ", ".join(phrases))
        self.__phrases = phrases
        self.__subscriptions = set()
        self.__tweets = [None] * self.__max_size

        # listen for tweets in a background thread using the 'async' keyword
        auth = OAuthHandler(consumer_key, consumer_secret)
        auth.set_access_token(access_token, access_token_secret)
        self.__stream = Stream(auth, listener=self)
        self.__stream.filter(track=phrases, is_async=True)
        self.__connected = True

    @property
    def tweets(self):
        return list(self.__tweets)

    def subscribe(self, subscriber):
        """Add a subscriber that will be notified when new tweets are received"""
        with self.__lock:
            self.__subscriptions.add(subscriber)

    def unsubscribe(self, subscriber):
        """Remove subscriber added previously.
        When there are no more subscribers the listener is stopped.
        """
        with self.__lock:
            self.__subscriptions.remove(subscriber)
            if not self.__subscriptions:
                self.disconnect()

    def disconnect(self):
        """Disconnect from the twitter stream and remove from the cache of listeners."""
        with self.__lock:
            if self.__connected:
                _log.info("Disconnecting twitter stream for [%s]" % ", ".join(self.__phrases))
                self.__listeners.pop(self.__phrases)
                self.__stream.disconnect()
                self.__connected = False

    @classmethod
    def disconnect_all(cls):
        """Disconnect all listeners."""
        with cls.__lock:
            for listener in list(cls.__listeners.values()):
                listener.disconnect()

    def on_data(self, data):
        data = json.loads(data)
        with self.__lock:
            self.__tweets.insert(0, data)
            self.__tweets = self.__tweets[:self.__max_size]
            for subscriber in self.__subscriptions:
                try:
                    subscriber.on_data(data)
                except:
                    _log.error("Error calling subscriber", exc_info=True)
        return True

    def on_error(self, status):
        with self.__lock:
            for subscriber in self.__subscriptions:
                try:
                    subscriber.on_error(status)
                except:
                    _log.error("Error calling subscriber", exc_info=True)

# Make sure all the listeners get disconnected when Excel is closing
@xl_on_close
def disconnect_all_listeners():
    TwitterListener.disconnect_all()

class TwitterRTD(RTD):
    """Twitter RTD class that notifies Excel whenever a new tweet is received."""

    def __init__(self, phrases, row=0, key="text"):
        super(TwitterRTD, self).__init__(value="Waiting for tweets...")
        self.__listener = TwitterListener.get_listener(phrases, self)
        self.__row = row
        self.__key = key

    def disconnect(self):
        self.__listener.unsubscribe(self)

    def on_error(self, status):
        self.value = "#ERROR %s" % status

    def on_data(self, data):
        # if there are no tweets for this row return an empty string
        tweets = self.__listener.tweets
        if len(tweets) < self.__row or not tweets[self.__row]:
            self.value = ""
            return

        # get the value from the tweets
        value = tweets[self.__row]
        for key in self.__key.split("/"):
            if not isinstance(value, dict):
                value = ""
                break
            value = value.get(key, {})

        # set the value back in Excel
        self.value = str(value)

@xl_func("string[] phrases, int row, string key: rtd")
def twitter_listen(phrases, row=0, key="text"):
    """Listen for tweets containing certain phrases"""
    # flatten the 2d list of lists into a single list of phrases
    phrases = [str(x) for x in itertools.chain(*phrases) if x]
    assert len(phrases) > 0, "At least one phrase is required"

    # return our TwitterRTD object that will update when a tweet is received
    return TwitterRTD(phrases, row, key)

if __name__ == '__main__':
    import time
    logging.basicConfig(level=logging.INFO)

    class TestSubscriber(object):
        """simple subscriber that just prints tweets as they arrive"""

        def on_error(self, status):
            print("Error: %s" % status)

        def on_data(self, data):
            print(data.get("text"))

    subscriber = TestSubscriber()
    listener = TwitterListener.get_listener(["python", "excel", "pyxll"], subscriber)

    # listen for 60 seconds then stop
    time.sleep(60)
    listener.unsubscribe(subscriber)


text:
# A Real-Time Twitter Feed in Excel

Please see the blog post accompanying this code here:
https://www.pyxll.com/blog/a-real-time-twitter-feed-in-excel/


text:
"""
Functions used to demonstrate performance profiling.

This code accompanies the blog post
https://www.pyxll.com/blog/how-to-profile-python-code-in-excel
"""
from pyxll import xl_func
import numba

@xl_func
def average_tol_simple(values, tolerance):
    """Compute the mean of values where value is > tolerance

    :param values: Range of values to compute average over
    :param tolerance: Only values greater than tolerance will be considered
    """
    total = 0.0
    count = 0
    for row in values:
        for value in row:
            if not isinstance(value, (float, int)):
                continue
            if value <= tolerance:
                continue
            total += value
            count += 1
    return total / count

@xl_func
def average_tol_try_except(values, tolerance):
    """Compute the mean of values where value is > tolerance

    :param values: Range of values to compute average over
    :param tolerance: Only values greater than tolerance will be considered
    """
    total = 0.0
    count = 0
    for row in values:
        for value in row:
            try:
                if value <= tolerance:
                    continue
                total += value
                count += 1
            except TypeError:
                continue
    return total / count

@xl_func
@numba.jit(locals={"value": numba.double, "total": numba.double, "count": numba.int32})
def average_tol_numba(values, tolerance):
    """Compute the mean of values where value is > tolerance

    :param values: Range of values to compute average over
    :param tolerance: Only values greater than tolerance will be considered
    """
    total = 0.0
    count = 0
    for row in values:
        for value in row:
            if value <= tolerance:
                continue
            total += value
            count += 1
    return total / count

@xl_func("numpy_array<float>, float: float")
def average_tol_numpy(values, tolerance):
    """Compute the mean of values where value is > tolerance

    :param values: Range of values to compute average over
    :param tolerance: Only values greater than tolerance will be considered
    """
    return values[values > tolerance].mean()