Excel scripting using Python

Python_Excel_Scripting.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center><h1>Excel Formatting Cheat Sheet</h1></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center><h4>Using xlwings and win32com</h4></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[xlwings](http://xlwings.org) is a Python library that allows you to essentially write Excel VBA code, but using the Python language.  But, it has its limitations.  To overcome the limitations, we can also use the win32com library which taps into Microsoft Excel's COM objects.  With win32com, we can access almost all the Excel objects that we need.  The downsides to using win32com is that it is platform specific, API syntax is less Pythonic, and there are no official documentation.  StackOverflow and Google will be your friend.  These snippets were tested on Windows 7 and Excel 2007.  \n",
    "  \n",
    "**UPDATE:** Tested on Excel 2016 and the snippets appear to work.  \n",
    "**UPDATE #2:** Thanks to Felix at ZoomerAnalytics, found out you can get Excel constants directly from xlwings via:  \n",
    "    \n",
    "    from xlwings import constants  \n",
    "    \n",
    "    example: constants.HAlign.xlHAlignCenter\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To obtain list of constants, just do: **dir(constants)**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"top\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Quick Links:**\n",
    " - [Adding line borders](#line_borders)\n",
    " - [Changing font size](#font_size)\n",
    " - [Adding background color to cell using ColorIndex](#bg_color_colorindex)\n",
    " - [Changing font color using ColorIndex](#font_color_colorindex)\n",
    " - [Changing background color of cell using RGB with win32com](#bg_color_rgb_win32)\n",
    " - [Changing background color of cell using RGB with xlwings](#bg_color_rgb_xlwings)\n",
    " - [Sorting a column](#sort_column)\n",
    " - [Horizontally or vertically align text](#align_text)\n",
    " - [Make a font bold](#bold_font)\n",
    " - [Modify specific characters](#modify_text)\n",
    " - [Merge/un-merge cells](#merge_unmerge)\n",
    " - [Autofill formula](#autofill)\n",
    " - [Autofit column(s) and/or row(s)](#autofit)\n",
    " - [Searching for text](#search_text)\n",
    " - [Freeze top row](#freeze_row)\n",
    " - [Copy / Paste](#copy_paste)\n",
    " - [Inserting values into adjacent column of cells](#inserting_values_adjacently)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import the necessary libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<win32com.gen_py.None.Workbook>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import win32com.client as win32\n",
    "from xlwings import Range, Sheet, Workbook, constants\n",
    "\n",
    "wb = Workbook.active()\n",
    "wb.xl_workbook            # connect to win32com object\n",
    "\n",
    "# Disable screen updating to improve performance, then enable it when done\n",
    "# wb.xl_app.ScreenUpdating = False\n",
    "# Do something...\n",
    "# wb.xl_app.ScreenUpdating = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"line_borders\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding line borders around range of cells per this [SO question](http://stackoverflow.com/questions/19095659/python-win32com-excel-border-formatting)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Border ids are 7 through 12 and correspond to borders for xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlEdgeLeft, xlInsideHorizontal, and xlInsideVertical"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "for cell in Range('A2').current_region:\n",
    "    for border_id in range(7,12):\n",
    "        cell.xl_range.Borders(border_id).LineStyle = 1\n",
    "        cell.xl_range.Borders(border_id).Weight = 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"font_size\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Change font size"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "Range('D1').xl_range.Font.Size = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"bg_color_colorindex\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding background color to a cell using [ColorIndex](https://msdn.microsoft.com/en-us/library/office/ff840443.aspx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "Range('A2').xl_range.Interior.ColorIndex = 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"font_color_colorindex\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Changing font color using [ColorIndex](https://msdn.microsoft.com/en-us/library/office/ff840443.aspx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "Range('A2').xl_range.Font.ColorIndex = 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"bg_color_rgb_win32\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Changing background color using RGB values per [SO question](http://stackoverflow.com/questions/11444207/setting-a-cells-fill-rgb-color-with-pywin32-in-excel) when using win32com"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "def rgbToInt(rgb):\n",
    "    colorInt = rgb[0] + (rgb[1] * 256) + (rgb[2] * 256 * 256)\n",
    "    return colorInt\n",
    "\n",
    "Range('D1').xl_range.Interior.Color = rgbToInt((255,255,0))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"bg_color_rgb_xlwings\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Changing background color using RGB values using xlwings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "Range('A1').color = (255,255,255)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"sort_column\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sorting a column per this [SO](http://stackoverflow.com/questions/34247373/sort-a-range-of-cells-win32com-client)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Order: 1 = ascending, 2 = descending"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Range('A2:B64').xl_range.Sort(Key1=Range('B2').xl_range, Order1=1, Orientation=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"align_text\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Horizontally or vertically align text using Excel VBA [constants](https://msdn.microsoft.com/en-us/library/aa221100&#40;v=office.11&#41;.aspx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "# do: dir(constants.HAlign) to get list of valid constants\n",
    "Range('A2').xl_range.HorizontalAlignment = constants.HAlign.xlHAlignCenter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "code_folding": [],
    "collapsed": true,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "# do: dir(constants.VAlign) to get list of valid constants\n",
    "Range('A2').xl_range.VerticalAlignment = constants.HAlign.xlHAlignCenter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"bold_font\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Make a font bold"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "Range('J1').xl_range.Font.Bold = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"modify_text\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Make specific characters in a text bold"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "# GetCharacters(start_index, length)\n",
    "Range('J1').xl_range.GetCharacters(1,4).Font.Bold = True  # make first 4 characters bold"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"merge_unmerge\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge / Un-merge Cells"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "Range('J12:K12').xl_range.MergeCells = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "Range('J12:K12').xl_range.MergeCells = False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"autofill\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Autofill Formula"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Do: dir(constants.AutoFillType) to get list of valid constants\n",
    "Range('A1').xl_range.AutoFill(Range('A1:A4').xl_range, constants.AutoFillType.xlFillDefault)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### or to autofill to last row, create last_cell variable:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 300,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 300,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import win32com.client as win32\n",
    "last_cell = Range('B2:J2').current_region.last_cell.get_address(False, False)\n",
    "Range('B2:J2').xl_range.AutoFill(Range('B2:'+last_cell).xl_range, constants.AutoFillType.xlFillDefault)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"autofit\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Autofit column and/or rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "Range('A2').autofit()  # autofit column(s) and row(s)\n",
    "Range('A2').autofit('c') # or Range('A2').autofit('columns')\n",
    "Range('A2').autofit('r') # or Range('A2').autofit('rows')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"search_text\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Searching for a text"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "for x in Range('A1:F22'):\n",
    "    if x.value == 'Claim Month':\n",
    "        print(x.get_address())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"freeze_row\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Freeze top row"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "wb.xl_workbook.Windows(1).SplitColumn = 0\n",
    "wb.xl_workbook.Windows(1).SplitRow = 1\n",
    "wb.xl_workbook.Windows(1).FreezePanes = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"copy_paste\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Copy / Paste"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copy / Paste more than 1 column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "Range('E1').value = Range('A1:C1').vertical.value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When copying / pasting one column, you have to use the options method or else you'll get an error.  Not sure why.  I opened a github [issue](https://github.com/ZoomerAnalytics/xlwings/issues/398)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "Range('E1').value = Range('A1').vertical.options(ndim=2).value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"inserting_values_adjacently\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inserting values into adjacent column of cells"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[[back to top](#top)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This snippet below will insert numbers 0 through 9 in a column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "run_control": {
     "read_only": false
    }
   },
   "outputs": [],
   "source": [
    "# From any location / selected cell\n",
    "current_location = wb.get_selection()  # get current location\n",
    "for n in range(10):\n",
    "    current_location.value = n\n",
    "    current_location = current_location.offset(row_offset = 1)\n",
    "    \n",
    "# or from a specific location (hard-coded)\n",
    "start_index = 1\n",
    "for n in range(10):\n",
    "    Range('A' + str(start_index)).value = n\n",
    "    start_index = start_index + 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Upcoming snippet: How to duplicate VLOOKUP functionality using Pandas merge() function"
   ]
  }
 ],
 "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.4.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}