Python42-python操作Excel文件(读写).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1-读操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#1.导入模块\n",
    "import xlrd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#2.打开excel文件读取数据\n",
    "data=xlrd.open_workbook('files/data/python42-names.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<xlrd.book.Book object at 0x000001CAB42C7588>\n"
     ]
    }
   ],
   "source": [
    "print(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<xlrd.sheet.Sheet object at 0x000001CAB42B4FD0>\n"
     ]
    }
   ],
   "source": [
    "#3 获取一个工作表\n",
    "table=data.sheets()[0]\n",
    "# table=data.sheet_by_index(0)\n",
    "# table=data.sheet_by_name('Sheet1')\n",
    "print(table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['冷浩然', 86.0, 85.0, 98.0, 100.0, 95.0, 98.0, 64.0, 69.0, 94.0, 95.0]\n",
      "['冷浩然', '房桂全', '唐庆康', '金钰', '孙乐陶', '沈博', '于佳阳', '裴佳华', '王宏泽', '田彬', '徐广博', '尹瀚泽', '周正', '冉博文', '刘秋男', '孙国源', '郭子钦', '周弛政', '王浩宇', '沈子傲', '刘宗祺', '刘文宣', '王子阳', '张展鹏', '孙伊健', '佟健', '李博文', '孙茗', '王嗣童', '孙靖淇', '徐泽铭', '王忠旭', '李佳岳', '刘航', '张正', '赵作政', '赵圣博', '梁炜', '赵文硕', '白锦菊', '陈铭慧', '张琪凡', '冷寒', '王彤宇', '曲知非', '李闯', '韩昌玉', '王子睿', '胡泰奇', '邹健东', '丁明彬', '初广阔', '王鑫宇', '焦靖涵', '王韩洋', '巩昊楠', '刘子棋', '闫芮', '张以宁', '姜萱格', '冯思源', '曹心研', '王岩', '宋一卓', '孙靖轩', '杨宏岩', '周忠冰', '马福鹏', '刘静聪', '李昊男', '李子昊', '孙芯瑶', '赵雨欣', '孙怡涵', '高丹丹', '赵鑫莹', '于涵', '潘春艳', '王佳仪', '秦玉欣', '栗紫阳', '王依然', '耿岩', '杨洋', '林鹏旭', '岳帅宇', '刘瑞', '腾书哲', '张薪', '陈思言', '郭子慧', '', '', '', '']\n"
     ]
    }
   ],
   "source": [
    "# 获取行和列的数据\n",
    "print(table.row_values(0))\n",
    "print(table.col_values(0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "95 11\n"
     ]
    }
   ],
   "source": [
    "#获取行和的数目\n",
    "nrows=table.nrows#行数\n",
    "ncols=table.ncols#列数\n",
    "print(nrows,ncols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "冷浩然 86.0\n"
     ]
    }
   ],
   "source": [
    "# 获取单元格 (0,0)坐标 从0开始\n",
    "cell_A1=table.cell(0,0).value\n",
    "cell_B1=table.cell(0,1).value\n",
    "print(cell_A1,cell_B1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "冷浩然 86.0\n"
     ]
    }
   ],
   "source": [
    "# 使用行列索引\n",
    "cell_A1=table.row(0)[0].value\n",
    "cell_B1=table.col(1)[0].value\n",
    "print(cell_A1,cell_B1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2-写操作"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error\n",
    "- ctype = 1 value = '单元格的值'  xf = 0 # 扩展的格式化\n",
    "- table.put_cell(row, col, ctype, value, xf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'单元格的值'"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row = 0\n",
    "col = 0\n",
    "# 类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error\n",
    "ctype = 1 \n",
    "value = '单元格的值'\n",
    "xf = 0 # 扩展的格式化\n",
    "table.put_cell(row, col, ctype, value, xf)\n",
    "table.cell(0,0)  #单元格的值'\n",
    "table.cell(0,0).value #单元格的值'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}