excel_in_python.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# libraries-xlrd and xlwt, but xlwt can only write .xls excel. So, we need to use xlsxwriter and OpenPyxl that can create .xlsx excel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "EOL while scanning string literal (<ipython-input-35-86e0956eb087>, line 5)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-35-86e0956eb087>\"\u001b[1;36m, line \u001b[1;32m5\u001b[0m\n\u001b[1;33m    path = 'data\\'\u001b[0m\n\u001b[1;37m                 ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m EOL while scanning string literal\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import xlrd, xlwt\n",
    "import xlsxwriter \n",
    "path = 'data\\'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1  create Workbook(.xls)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Create a Workbook with two Worksheets. wb is the Workbook object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<xlwt.Workbook.Workbook at 0xa53bcc0>"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wb = xlwt.Workbook()\n",
    "wb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<xlwt.Worksheet.Worksheet at 0xa53bc50>"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Secondly, add one or more Worksheet:\n",
    "wb.add_sheet('first_sheet', cell_overwrite_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wb.get_active_sheet()   #index of a worksheet is 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#define as alias for worksheet\n",
    "ws_1 = wb.get_sheet(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#the above can be merged into one step\n",
    "ws_2 = wb.add_sheet('second_sheet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ 1,  2,  3,  4,  5,  6,  7,  8],\n",
       "       [ 9, 10, 11, 12, 13, 14, 15, 16],\n",
       "       [17, 18, 19, 20, 21, 22, 23, 24],\n",
       "       [25, 26, 27, 28, 29, 30, 31, 32],\n",
       "       [33, 34, 35, 36, 37, 38, 39, 40],\n",
       "       [41, 42, 43, 44, 45, 46, 47, 48],\n",
       "       [49, 50, 51, 52, 53, 54, 55, 56],\n",
       "       [57, 58, 59, 60, 61, 62, 63, 64]])"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = np.arange(1, 65).reshape((8, 8))\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#use method write and support row/column messages, write data into a cell of worksheet\n",
    "ws_1.write(0, 0, 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for c in range(data.shape[0]):\n",
    "    for r in range(data.shape[1]):\n",
    "        ws_1.write(r, c, data[c, r])\n",
    "        ws_2.write(r, c, data[r, c])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#method `save` of Workbook object can write workbook into disk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "wb.save('workbook1.xls')  # write into working directory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C:\\\\Users\\\\libin'"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2 create Workbook(.xlsx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#basically the same as the above"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#create a Workbook objecet\n",
    "wb = xlsxwriter.Workbook('workbook.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#create worksheet object\n",
    "ws_1 = wb.add_worksheet('hehe')\n",
    "ws_2 = wb.add_worksheet('haha')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#write data into worksheet\n",
    "for c in range(data.shape[0]):\n",
    "    for r in range(data.shape[1]):\n",
    "        ws_1.write(r, c, data[c, r])\n",
    "        ws_2.write(r, c, data[r, c])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "wb.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-60-9ee8e7e31dca>, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-60-9ee8e7e31dca>\"\u001b[1;36m, line \u001b[1;32m1\u001b[0m\n\u001b[1;33m    ll $path*\u001b[0m\n\u001b[1;37m       ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "ll $path*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlrd\n",
    "import xlwt\n",
    "import xlsxwriter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#xlrd read data from excel\n",
    "book = xlrd.open_workbook('C:/users/libin/workbook1.xls')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<xlrd.book.Book at 0x65c6320>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "book"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[u'first_sheet', u'second_sheet']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "book.sheet_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(<xlrd.sheet.Sheet at 0x65d2710>, <xlrd.sheet.Sheet at 0x65d2ac8>)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_1 = book.sheet_by_name('first_sheet')\n",
    "sheet_2 = book.sheet_by_index(1)\n",
    "sheet_1,  sheet_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'second_sheet'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_2.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_1.ncols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8, 8)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_1.ncols, sheet_2.nrows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#cell object visited by method `cell`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "c1 = sheet_1.cell(0, 0)  #以列、行号作为参数，采用零基编号"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#value属性给出保存在特定单元格的数据\n",
    "c1.value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#ctype给出了单元格的类型\n",
    "c1.ctype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###                    Excel单元格类型"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 类型——————————编号——————Python类型"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### XL_CELL_EMPTY-----------0--------------空字符串\n",
    "#### XL_CELL_TEXT------------1--------------unicode字符串\n",
    "#### XL_CELL_NUMBER----------2--------------float\n",
    "#### XL_CELL_DATA------------3--------------float\n",
    "#### XL_CELL_BOOLEAN---------4--------------int(1=True, 0=False)\n",
    "#### XL_CELL_ERROR-----------5--------------int表示Excel内部编码\n",
    "#### XL_CELL_BLANK-----------6--------------空字符串，仅当formatting_info=True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[number:25.0,\n",
       " number:26.0,\n",
       " number:27.0,\n",
       " number:28.0,\n",
       " number:29.0,\n",
       " number:30.0,\n",
       " number:31.0,\n",
       " number:32.0]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_2.row(3)  # visit the whole row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[number:4.0,\n",
       " number:12.0,\n",
       " number:20.0,\n",
       " number:28.0,\n",
       " number:36.0,\n",
       " number:44.0,\n",
       " number:52.0,\n",
       " number:60.0]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_2.col(3)   #visit the whole column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[28.0, 29.0, 30.0, 31.0]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_1.col_values(3, start_rowx=3, end_rowx=7)      #col: 3, row: 3-7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[28.0, 36.0, 44.0, 52.0]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_1.row_values(3, start_colx=3, end_colx=7)       #row: 3  col: 3-7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64\n"
     ]
    }
   ],
   "source": [
    "#read the whole data in the excel\n",
    "\n",
    "for c in range(sheet_1.ncols):\n",
    "    for r in range(sheet_1.nrows):\n",
    "        print '%i' % sheet_1.cell(r, c).value,\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 2 3 4 5 6 7 8\n",
      "9 10 11 12 13 14 15 16\n",
      "17 18 19 20 21 22 23 24\n",
      "25 26 27 28 29 30 31 32\n",
      "33 34 35 36 37 38 39 40\n",
      "41 42 43 44 45 46 47 48\n",
      "49 50 51 52 53 54 55 56\n",
      "57 58 59 60 61 62 63 64\n"
     ]
    }
   ],
   "source": [
    "for c in range(sheet_1.ncols):\n",
    "    for r in range(sheet_1.nrows):\n",
    "        print '%i' % sheet_1.cell(r, c).value,\n",
    "    print"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "53\n",
      "54\n",
      "55\n",
      "56\n",
      "\n",
      "57\n",
      "58\n",
      "59\n",
      "60\n",
      "61\n",
      "62\n",
      "63\n",
      "64\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for c in range(sheet_1.ncols):\n",
    "    for r in range(sheet_1.nrows):\n",
    "        print '%i' % sheet_1.cell(r, c).value\n",
    "    print"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C:\\\\Users\\\\libin'"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}