Tutorial 3 - Add-In & RunPython.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Excel Add-in & RunPython: Call Python from Excel\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How does it work?!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](img/xlwings_runpython_architecture.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    "**Note:** `RunPython` is a VBA function that works on Windows and Mac and hence allows you to create **cross-platform** tools.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Excel add-in"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The xlwings addin can be installed via command line client: `xlwings addin install`. If that doesn't work, you can also download it from the release page on GitHub and install it manually (`File > Options > Add-ins > Go...`): https://github.com/ZoomerAnalytics/xlwings/releases\n",
    "\n",
    "The ribbon settings are stored in an external **config file**:\n",
    "\n",
    "**Windows**: `.xlwings\\xlwings.conf in your user folder`  \n",
    "**Mac Excel 2016**: `~/Library/Containers/com.microsoft.Excel/Data/xlwings.conf`  \n",
    "**Mac Excel 2011**: `~/.xlwings/xlwings.conf`\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Quickstart"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run the following in a Command Prompt/Terminal:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "cd Desktop\n",
    "xlwings quickstart hello\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# RunPython: Hello World"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`hello.py` already comes with samples (on Mac, the UDF sample is missing):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import xlwings as xw\n",
    "\n",
    "\n",
    "def hello_xlwings():\n",
    "    wb = xw.Book.caller()\n",
    "    wb.sheets[0].range(\"A1\").value = \"Hello xlwings!\"\n",
    "\n",
    "\n",
    "@xw.func\n",
    "def hello(name):\n",
    "    return \"hello {0}\".format(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    " \n",
    "**Note**: with `RunPython`, use `xw.Book.caller()` to reference the calling workbook.\n",
    "\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now open the VBA Editor with e.g. `Alt-F11` and have a look a the sample function that uses RunPython:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Sub SampleCall()\n",
    "    mymodule = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, \".\", -1, vbTextCompare) - 1))\n",
    "    RunPython (\"import \" & mymodule & \";\" & mymodule & \".hello_xlwings()\")\n",
    "End Sub"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This looks a bit akward but it's really just so it works whatever you call the file. You can also hardcode it like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Sub SampleCall():\n",
    "    RunPython(\"import hello;hello.hello_xlwings()\")\n",
    "End Sub"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    " \n",
    "**Note**: Make sure that the **reference** to xlwings is checked under `Tools > References`. This is necessary to run macros from the add-in and required for `RunPython` calls and `Import UDFs` (it's not necessary to run the UDFs as we shall see later).\n",
    "\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "On the `Developer` ribbon tab, add a Button and bind your `SampleCall` macro to it. You may first need to enable the `Developer` tab under `File > Options > Customize Ribbon`. Alternatively, you can also run the code directly from the VBA Editor via F5 or pull up a list with macros from the Workbook via Alt-F8."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    " \n",
    "**Note**: If you want your tool to work on Windows and Mac, you must not use any **ActiveX controls**, i.e. make sure to pick a **Form Controls** button. \n",
    "\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add-in settings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The idea of xlwings is that things should *just work* for most users. This means that if you have a default Python installation (i.e. Python can be launched by just typing `python` in a terminal/command prompt), then there's nothing that has to be changed. The only requirement is to have a Python installation with xlwings installed.\n",
    "For instance, to run an example from the homepage, just download and unzip it.\n",
    "\n",
    "However, there are few Settings in the xlwings add-in that you may still want to change, most notably:\n",
    "\n",
    "* `Interpreter`\n",
    "* `PYTHONPATH`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Global vs Directory vs Workbook specific settings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "xlwings applies settings in the following order:\n",
    "\n",
    "* `xlwings.conf` sheet\n",
    "* `xlwings.conf` file in the same directory as the workbook\n",
    "* Add-in (i.e. global config file)\n",
    "* Defaults"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Gotchas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Current Working directory**:\n",
    "The cwd is currently not handled consistently nor is it settable in the settings, therefore use:\n",
    "\n",
    "* full file paths (using something like `os.path.dirname(__file__)`)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  },
  "toc": {
   "toc_cell": true,
   "toc_number_sections": true,
   "toc_section_display": "none",
   "toc_threshold": 6,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}