{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "[the xlsxwriter format class](http://xlsxwriter.readthedocs.io/format.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import re\n",
    "import string\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from xlsxwriter.utility import xl_col_to_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "def randStr(length):\n",
    "    rand_str = \"\".join(np.random.choice(np.array(list(string.ascii_uppercase)),length))\n",
    "    return rand_str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "num = 7\n",
    "column_names = [randStr(5) for i in range(num)]\n",
    "\n",
    "my_df = pd.DataFrame(np.random.rand(num,num))\n",
    "my_df.columns = column_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>QNHTR</th>\n",
       "      <th>ENTJP</th>\n",
       "      <th>YPGCH</th>\n",
       "      <th>REMXE</th>\n",
       "      <th>ASROI</th>\n",
       "      <th>XWPAK</th>\n",
       "      <th>WWGKJ</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.757278</td>\n",
       "      <td>0.855409</td>\n",
       "      <td>0.743965</td>\n",
       "      <td>0.278558</td>\n",
       "      <td>0.907111</td>\n",
       "      <td>0.233872</td>\n",
       "      <td>0.070314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.822849</td>\n",
       "      <td>0.395048</td>\n",
       "      <td>0.577086</td>\n",
       "      <td>0.692197</td>\n",
       "      <td>0.701750</td>\n",
       "      <td>0.172315</td>\n",
       "      <td>0.218093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.865188</td>\n",
       "      <td>0.799359</td>\n",
       "      <td>0.003658</td>\n",
       "      <td>0.799052</td>\n",
       "      <td>0.997357</td>\n",
       "      <td>0.854079</td>\n",
       "      <td>0.660088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.013974</td>\n",
       "      <td>0.452349</td>\n",
       "      <td>0.426939</td>\n",
       "      <td>0.280872</td>\n",
       "      <td>0.461789</td>\n",
       "      <td>0.800806</td>\n",
       "      <td>0.874574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.988762</td>\n",
       "      <td>0.389006</td>\n",
       "      <td>0.613281</td>\n",
       "      <td>0.980424</td>\n",
       "      <td>0.808690</td>\n",
       "      <td>0.716430</td>\n",
       "      <td>0.409934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.728441</td>\n",
       "      <td>0.723092</td>\n",
       "      <td>0.774407</td>\n",
       "      <td>0.117822</td>\n",
       "      <td>0.109778</td>\n",
       "      <td>0.040558</td>\n",
       "      <td>0.874075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.987464</td>\n",
       "      <td>0.571264</td>\n",
       "      <td>0.572463</td>\n",
       "      <td>0.058041</td>\n",
       "      <td>0.168523</td>\n",
       "      <td>0.268443</td>\n",
       "      <td>0.425605</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      QNHTR     ENTJP     YPGCH     REMXE     ASROI     XWPAK     WWGKJ\n",
       "0  0.757278  0.855409  0.743965  0.278558  0.907111  0.233872  0.070314\n",
       "1  0.822849  0.395048  0.577086  0.692197  0.701750  0.172315  0.218093\n",
       "2  0.865188  0.799359  0.003658  0.799052  0.997357  0.854079  0.660088\n",
       "3  0.013974  0.452349  0.426939  0.280872  0.461789  0.800806  0.874574\n",
       "4  0.988762  0.389006  0.613281  0.980424  0.808690  0.716430  0.409934\n",
       "5  0.728441  0.723092  0.774407  0.117822  0.109778  0.040558  0.874075\n",
       "6  0.987464  0.571264  0.572463  0.058041  0.168523  0.268443  0.425605"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'A1:G8'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def spanXL(dataframe, start_index=0, row = 1):\n",
    "    first = chr(65+start_index)\n",
    "    last = xl_col_to_name(dataframe.shape[1]-1)\n",
    "    span = [first+str(row),last+str(dataframe.shape[1]+1)]\n",
    "    span = \":\".join(span)\n",
    "    return span\n",
    "spanXL(my_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'A$1&G$8'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def colSpanXL(dataframe, start_index=0, row = 1):\n",
    "    span_str = spanXL(dataframe, start_index, row).replace(\":\",\"&\")\n",
    "#     span_str = re.sub(\"\\d+$\",\"1\",span_str)\n",
    "    rx = re.compile(\"(\\d.+\\D)\")\n",
    "    col_span = \"$\".join(rx.split(span_str))\n",
    "    return col_span\n",
    "\n",
    "colSpanXL(my_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "def makeSearch(term,df):\n",
    "    search_str = '=SEARCH(\"{0}\",{1})'.format(term,colSpanXL(df))\n",
    "    return search_str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'=SEARCH(\"T\",A$1&G$8)'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "makeSearch(\"T\",my_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "def criteriaXL(term,df,xl_fun=\"SEARCH\"):\n",
    "    \n",
    "    xl_fun = xl_fun.upper()\n",
    "    \n",
    "    if not xl_fun.startswith(\"=\"):\n",
    "        xl_fun = \"=\"+xl_fun\n",
    "        \n",
    "    \n",
    "    fun_str = xl_fun+'(\"{0}\",{1})'.format(term,colSpanXL(df))\n",
    "    return fun_str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'=SEARCH(\"acetyl\",A$1&G$8)'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "criteriaXL(\"acetyl\",my_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "def dynFormat(format_set,workbook):\n",
    "    return workbook.add_format(format_set)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "\n",
    "# Create a Pandas Excel writer using XlsxWriter as the engine.\n",
    "writer = pd.ExcelWriter('pandas_conditional.xlsx', engine='xlsxwriter')\n",
    "\n",
    "# Convert the dataframe to an XlsxWriter Excel object.\n",
    "my_df.to_excel(writer, sheet_name='Sheet1',index=False)\n",
    "\n",
    "# Get the xlsxwriter workbook and worksheet objects.\n",
    "workbook  = writer.book\n",
    "worksheet = writer.sheets['Sheet1']\n",
    "\n",
    "\n",
    "worksheet.conditional_format(spanXL(my_df), {'type':'formula',\n",
    "                                             'criteria': makeSearch(column_names[3],my_df),\n",
    "                                             'format': dynFormat({\"bg_color\":\"PeachPuff\"},workbook)})\n",
    "\n",
    "worksheet.conditional_format(spanXL(my_df),{'type':'formula',\n",
    "                                             'criteria': makeSearch(column_names[-1],my_df),\n",
    "                                             'format':  dynFormat({\"bg_color\":\"orange\"},workbook)})\n",
    "\n",
    "# Close the Pandas Excel writer and output the Excel file.\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}