Python对Excel操作详解.doc

实用文案太仓同维电子有限公司测试中心软件测试科拟制人胡张东审批人xxx时间2013-11-04Python对Excel操作详解文档摘要：本文档主要介绍如何通过python对office excel进行读写操作，使用了xlrd、xlwt和xlutils模块。另外还演示了如何通过Tcl tcom包对excel操作。关键字：Python、Excel、xlrd、xlwt、xlutils、TCl、tcom1 Python简介Python是一种面向对象、直译式电脑编程语言，具有近二十年的发展历史，成熟且稳定。它包含了一组完善而且容易理解的标准库，能够轻松完成很多常见的任务。它的语法简捷和清晰，尽量使用无异义的英语单词，与其它大多数程序设计语言使用大括号不一样，它使用縮进来定义语句块。与Scheme、Ruby、Perl、Tcl等动态语言一样，Python具备垃圾回收功能，能够自动管理存储器使用。它经常被当作脚本语言用于处理系统管理任务和网络程序编写，然而它也非常适合完成各种高级任务。Python虚拟机本身几乎可以在所有的作业系统中运行。使用一些诸如py2exe、PyPy、PyInstaller之类的工具可以将Python源代码转换成可以脱离Python解释器运行的程序。2 Python安装Python目前的版本已经更新到3.4.0，本文使用的版本为2.7.5，所有的版本都可以在python官网http:/www.python.org/下载，至于2.x和3.x版本的具体区别也可以在官网查看。从官网下载了python 2.7.5安装文件python-2.7.5.msi后，直接双击就可以安装python了，可以选择安装路径，我改为C:Python2.7.5了，然后一路next就完成安装了，安装完成后在C盘下就多了一个文件夹Python2.7.5。Python也是一种实时交互语言，可以通过自带的IDLE编写python语句并反馈回显信息，可以通过图1方式调出python IDLE。图1也可以在cmd下输入python，但默认情况下python并没有添加到windows环境变量中，导致在cmd下输入python的时候出现提示“'python' 不是内部或外部命令，也不是可运行的程序或批处理文件。”，windows下可执行文件在运行时首先在当前目录下搜索，因为进入cmd下默认路径一般为C:Documents and SettingsAdministrator>，而在这个路径下是找不到python的，所以提示出错，可以进入到python安装目录下，然后执行python就可以进入交互命令行模式下。如果懒的每次都进入python安装，此时需要将python安装路径添加到系统变量中，然后windows在执行命令的时候会去环境变量中查找路径，具体配置如图2所示，在Path中添加python的安装路径“C:Python2.7.5;”，主要路径后面要加”;”分号表面这是一个路径的结束，此时无论在哪个路径下都可以执行python调出交互命令行。图23 Python语法入门在Python简介中提到Python是一种直译式电脑编程语言，体现在语法中，如要将变量a赋值为1，Tcl使用命令%set a 1（本文中为了区分Tcl和Python的命令，Tcl命令前会加上“%”，否则默认为Python命令），在python中命令为a = 1，输出a的值可以直接输入a，也可以通过print语句输出a的值，命令为print a（在python 3.0以后版本中，print不再是一个语句，而是一个函数，所以如果想要输出a，用法为print(a)）。在Tcl中求1和10的和或者变量之间的加减乘除运算需要使用expr命令，在python则直接写表达式就可以了，如图3所示。图3Python很多功能都是靠模块实现的，比如ftplib模块负责ftp功能的实现，math模块囊括了基本数学公式，如果我们想要引用这些模块，需要使用命令import 模块名称，如import ftplib和import math。如果想使用math模块中的函数floor，可以使用命令math.floor(28.5)，语法为“模块.函数”，如果想要直接使用floor函数，必须提前引用，命令为from math import floor，那样就可以直接使用命令floor(28.5)了。如果觉得floor这个函数名称太长了或者不好记忆，可以通过变量引用函数，如f = math.floor，这样变量f就充当了math.floor的功能了。上面提到的模块ftplib和math都是在python安装的时候已经安装了，而接下来重点介绍的xlrd、xlwt、xlutils模块都不是随python安装的。需要手动下载安装，第5节会详细介绍模块的安装。当成功导入了某个模块后，可以通过函数dir (模块名)查看这个模块包含哪些函数，如果对某个函数的作用不了解，可以通过help函数查看，如help(math.pow)本文只是带领大家入门，python的其他语法可以参考其它资料学习。4 Tcl对Excel操作在使用python对excel操作之前搜索过如何通过Tcl对excel操作，Tcl本身没有提供对excel操作的命令，可以通过tcom外部包来调用excel的接口实现，但是个人感觉实现起来比较麻烦，msdn网站上虽然提供了excel的接口，但示例都是针对VB脚本语言写的，Tcl如果想要调用的话还需要转换，如下是一段简单的Tcl代码展示如何通过tcom对excel进行操作，但也花了本人不少时间琢磨。# 加载tcom包package require tcomset filename "F:/1.xls"# 创建com实例，打开工作表，下面四句都是套路set excel :tcom:ref createobject "Excel.Application"set workbooks $excel Workbooksset workbook $workbooks Open $filenameset worksheets $workbook Worksheets# "sheet1"为sheet的名称set worksheet $worksheets Item "sheet1"# 创建单元格对象set cells $worksheet Cells# 给单元格B2赋值为“hsdf”$cells Item 2 B "hsdf"# 获取sheet的个数并赋值给sheetCountset sheetCount $worksheets Count# 获取A1至A15单元的范围对象set range $worksheet Range A1 A15# 给A1至A15单元赋值$range Value2 "abcdefg"# 获取A1至A15的值，并赋值给A,A是一个列表listset A $range Value2# 设置单元的背景色set interior $range Interior$interior Color expr 0x00FFE0# 设置单元的前景色和字体大小、加粗、斜体、字体set font $range Font$font Color expr 0xFF0000$font Bold 1$font Size 10$font Italic 0$font Name "华文行楷"# 设置单元格的宽度为自动调整set entire $range EntireColumn$entire AutoFit# 保存文档$workbook Save# 显示Excel$excel Visible 15 xlwt和xlrd模块的安装Python也是通过导入外部模块来实现对excel的操作，xlrd负责对excel的读取，xlwt负责对excel的写入，xlutils依赖于xlrd和xlwt，可以复制excel文件。这三个包都可以在网站http:/www.python-excel.org/下载。本文使用的xlrd版本为0.8.0，xlwt版本为0.7.5。从网上下载好xlrd和xlwt后，解压缩到C:Python2.7.5Lib下，此时在命令行下输入import xlrd或者import xlwt，会出现提示ImportError: No module named xlwt，这表明还没有安装xlwt模块。python 导入一个模块的过程要求有一个叫做“路径搜索”的操作过程，即是在文件系统“预先设定的区域”查找模块文件以加载模块的过程。这个预先设定的区域其实是python搜索路径的一组目录。这个目录保存在sys.path中，如果你想知道python导入模块时会在哪些路径搜索模块，你可以执行以下命令查看搜索路径目录：>>> import sys>>> sys.path'D:pythonshell2.7.5','C:Python2.7.5Libidlelib','C:Python2.7.5libsite-packagessetuptools-1.3-py2.7.egg','C:Python2.7.5libsite-packagesxlutils-1.7.0-py2.7.egg','C:Windowssystem32python27.zip','C:Python2.7.5DLLs','C:Python2.7.5lib','C:Python2.7.5libplat-win', 'C:Python2.7.5liblib-tk','C:Python2.7.5','C:Python2.7.5libsite-packages','C:Python2.7.5libsite-packageswin32','C:Python2.7.5libsite-packageswin32lib','C:Python2.7.5libsite-packagesPythonwin'在sys.path中找到一个路径为'C:Python2.7.5lib'，所以我们把模块解压缩到这个目录下。命令>>>sys.path.append('C:Python2.7.5lib')在最后添加一个目录，sys.path.insert(0,' C:Python2.7.5lib ')在第一位插入一个目录。解压缩完成并放在正确目录后，在cmd下进入package当前目录，然后输入命令“C:Python2.7.5Libxlrd-0.8.0>python setup.py install”，安装完成后可以输入import xlrd，dir(xlrd)来确认是否已经安装正确。6 xlrd简单使用方法>>>import xlrd>>>excel = xlrd.open_workbook("C:UsersHuZhangdongDesktopASB测试床环境信息图.xls") #打开文件并将对象存储到excel中>>>sheet = excel.sheet_by_index(0) #通过索引读取sheet对象，第一个sheet的索引为"0">>>row_3 = sheet.row_values(2) #读取第3行的所有数据，并以列表的形式存储到row_3中>>>col_3 = sheet.col_values(2) #读取第3列的所有数据，并以列表list的形式存储到col_3中>>>cell_12_7 = sheet.cell_value(11,6) #读取第12行第7列的数据，并存储到cell_12_7中>>>cell_11_11 = sheet.cell(10,10).value #读取第11行第11列的数据，并存储到cell_11_11中>>>cell_7_8 = sheet.row(6)7.value #读取第7行第8列的数据>>>cell_7_8 = sheet.cel(7)6.value #读取第8列第7行的数据>>>num_rows = sheet.nrows #读取sheet的总行数>>>num_cols = sheet.ncols #读取sheet的总列数基本上面的命令已经可以满足目前对excel读取的操作了，接下给大家讲解xlwt的用法。7 xlwt简单使用方法#-*- coding: UTF-8 -*- #设置编码格式为 utf-8import os,xlwt,datetime #导入模块data =xlwt.Workbook() #新建一个Workbooksheet = data.add_sheet(u"sheet") #新建一个sheet，名称为'sheet'style1 = xlwt.XFStyle() #创建格式style1style2 = xlwt.XFStyle()style3 = xlwt.XFStyle()#设置字体格式font1 = xlwt.Font() #创建font1font1.name = 'Times New Roman' #字体为'Times New Roman'font1.bold = True #加粗font1.colour_index = 2 #字体颜色为红色，0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyanfont1.underline = xlwt.Font.UNDERLINE_DOUBLE #下划线类型，UNDERLINE_DOUBLE 代表双下划线，另外还有 UNDERLINE_NONE, UNDERLINE_SINGLE, UNDERLINE_SINGLE_ACC, UNDERLINE_DOUBLE, UNDERLINE_DOUBLE_ACCfont1.escapement = xlwt.Font.ESCAPEMENT_SUPERSCRIPT #设置上标font1.family = xlwt.Font.FAMILY_ROMANfont1.height = 0x190 #0x190是16进制，换成10进制为400，然后除以20，就得到字体的大小为20style1.font = font1 #将创建的font1字体格式应用到style1上font2 = xlwt.Font() #创建font2font2.name = "Algerian" #字体为'Algerian' font2.colour_index = 3 #字体颜色为绿色font2.italic = True #斜体font2.struck_out = True #删除线font2.height = 0x258 #字体大小为30style2.font = font2 #将创建的font2字体格式应用到style2上#设置列宽sheet.col(0).width = 6000sheet.col(1).width = 12000sheet.set_col_default_width(2)#设置单元格对齐方式alignment = xlwt.Alignment() #创建alignmentalignment.horz = xlwt.Alignment.HORZ_CENTER #设置水平对齐为居中，May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTEDalignment.vert = xlwt.Alignment.VERT_CENTER #设置垂直对齐为居中，May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTEDstyle3.alignment = alignment #应用alignment到style3上#插入时间style3.num_format_str = 'YYYY-MM-DD HH:MM:SS' #设置时间格式sheet.write(1,1,datetime.datetime.now(),style3) #在第2行第2列插入当前时间，格式为style3#设置单元格背景颜色pattern_yellow = xlwt.Pattern() #创建pattern_yellowpattern_yellow.pattern = xlwt.Pattern.SOLID_PATTERN #设置填充模式为全部填充pattern_yellow.pattern_fore_colour = 5 #设置填充颜色为yellow黄色style1.pattern = pattern_yellow #把设置的pattern应用到style3上pattern_red = xlwt.Pattern() #创建pattern_redpattern_red.pattern = xlwt.Pattern.SOLID_PATTERN #设置填充模式为全部填充pattern_red.pattern_fore_colour = 2 #设置填充颜色为red红色style2.pattern = pattern_red #把设置的pattern应用到style4上 #设置单元格边框borders = xlwt.Borders() #创建bordersborders.left = xlwt.Borders.DASHED #设置左边框的类型为虚线 May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.borders.right = xlwt.Borders.THIN #设置右边框的类型为细线borders.top = xlwt.Borders.DOTTED #设置上边框的类型为打点的borders.bottom = xlwt.Borders.THICK #设置底部边框类型为粗线borders.left_colour = 0x10 #设置左边框线条颜色borders.right_colour = 0x20borders.top_colour = 0x30borders.bottom_colour = 0x40style1.borders = borders #将borders应用到style1上style2.borders = borders #将borders应用到style2上sheet.write(3, 0, 'HuZhangdong', style1) #在第4行第1列写入'HuZhangdong'，格式引用style1sheet.write(4, 0, 'YinMengran' , style2) #在第5行第1列写入'YinMengran'，格式引用style2data.save(u'e:3.xls') #保存到 e:3.xls函数xlwt.Workbook()只能新建一个excel文档，不能打开一个已经存在的文档，下一章会讲解如何通过xlutils修改一个已经存在的excel文档。8 xlutils简单使用方法本章将讲解如何使用xlutils编辑一个已经存在的文档，需要导入xlutils模块，下载地址是http:/www.python-excel.org/，模块成功安装后就开始编写脚本吧。#-*- coding:utf-8 -*- #设置当前编码格式为utf-8import xlwt #导入xlwt模块import xlrd #导入xlrd模块from xlutils.copy import copy #导入import模块的copy函数，接下来就可以直接使用函数copy了。fileName = "E:4.xls"sheetName = "sheet1"styleBoldRed = xlwt.easyxf('font: color-index red, bold on') #设置字体，颜色为红色，加粗oldWb = xlrd.open_workbook(fileName, formatting_info=True) #使用xlrd.open_workbook函数打开文件，formatting_info=True表示保留该文件的格式newWb = copy(oldWb) #通过copy函数把oldWb copy到newWb，然后通过编辑newWb来实现编辑已经存在的文件。newWs = newWb.get_sheet(0) #读取第一个sheetnewWs.write(4, 0, "value1",styleBoldRed) #第5行第1列写入值“value1”，格式采用styleBoldRed。newWs.write(4, 1, "value2",styleBoldRed) #第5行第2列写入值“value2”newWs.write(4, 2, "value3",styleBoldRed) #第5行第3列写入值“value3”newWb.save(fileName) #文件保存为"E:4.xls"13 为文 ) “ ) " " , 入写 # , ", 式” 值列行 , , . 一读#0 . 件文在已来 过， 到 数函 ) ( = 格的留示 开数 _ . 使 )= _ . 加色色颜置 ) . = : 了 函以就下函 模 # . 模 # 模 入 - 式编前 - 吧脚编开功模/ . ./: " - . : 是址下 要文在已编 使如方用简 档 在经改 如解下，存经开不档 一建( . . 到#' :( 格， 写第行# '0 ( 用格' 入列 # , , . 到 # 应 将 0 . 0 . 0 颜线边#00 _ 粗为边底 . 点点类上置 . . . 细型框右 00 , _ _ _ _ _ , _ _ , , , : 线型左# 创 ( 边边上 到应 把 = 红红色填# _ _ 填全为填 . . _ 创 ) _ 应 置# = . 黄 充设 _ 填全为填设 _ _ 颜背单 为，时入第第 ( , 格时置 - ' . 时 用 = . _ , , 中对置 . = _ _ _ , _ , , _ ，齐平# . 建# ( 方对单 0 0 0 列 应体 建# 小字 除删 斜# 绿色颜# = _ 为 " 创 ) . = 到式 建# = 小的字0除然0进 制 00 . . . = 上上 _ _ , , _ 还另划表 _ ，线下 _ = , = , , , 0色色字 .加 . 体# '= 建#) 格格( . = ( . 式创#( . 称 个#)" ( _ = 个# ( 模导 , - 为格设 *- 方使 法的 讲家，操读 对足可已的列列 # _ 行总 取 . = 数行 取# _ 数列 第# .) _ 储，的 行取读 )0 . 到据列行第# ( = _ _ 存形 以，所列读) = 式表并数所 读 ( 0为索 个，对 过#( _ 到象并件# 图境床 : . 方方简确正经认) ， 入以成安 . 0 . 命后然当 进 目在成完录目入一在 :, 目添最 . : . >命录到解把以所 . '路到 ' - . ' - . , :'. :, . . '' ' . ' , . - . :' . :' . ' . ' . > 录录搜令下执，块路在块导 知如中 在目。目的路 其区定这。块以件找区定“系是程操索路叫个程过一导 块模 安明表 现， 输命此 . 到解后 下网 0 0.版 用本下/. / " . / " 站可都。 复， 依 入 对 的 对 操的 实模外导也安的 文存 调动为的楷文 00 $ 字字斜小体和的0 0 $ 景的置 表 给赋值 赋单 对范单 赋个 为赋格 对单" " 名的 " $ $ " : : 套是句表作， /" 磨间时本但作进 何展代的段下，要的要如 的言脚针都口的 供虽站 较起感人，接 来部 通，的 供没 ， 如索作 操 习料它以语的 入家是 ，查 通了作数对，数含模查块模 函以后个了装安模详节装载要。安 不模 、 点接，经已装 是 块的提。 了就样 数引过可，不了称个 果了.( 用直样， 命引须数 用想果数块“) ( 用可 函模 用果。 名模 命要模用想果式数了块 ，实 责块 如实块是很 图示示，可达表 命 使要除的量者的 中在)( 法用要想所个是语一再 后0 在 令的句 以也输直的输 命中 ，命 默则“会前 令 分为文（ %用 ，为变如，语言程式种 介 入入 图行行出 执都路在时，的一面号”加径主 . “安 添 在所图体径找量去候命执 然中系添路 时安 入次果下行互以就 然下装 进，示所， 找下这> :一认下 为搜目前首运文下 ，文批程的是令命内 “示时的 输 ，变 添没 下认， 输 图 出方图可信回句 编 通，互实是也 个了下 装了成 一， 我路择可了 装可双接 .- 文. 下看网官也具版.于载/. /:/ /: 官 可版所. 版用本 经本前 安 序的器 离可换源 可的 、 如一使中系所可身机虚 级种适非而写程务管统用语作经。储存自，收垃具 样言态 、 块语来用，一大用设数它与语的用量清捷法。见多松够库解容而组包它熟史发年二言编脑、向种 简 、 、 、字作 对 过如演块 用操行 通要要详操 对 0 张试测公公维文准公试 0 要 操用 演 、 、 向脑二发组解松见捷用它用一来 、态具收储经统管非适虚机系如 换 序安经 . : /于版网 .接 可 装个 也互 句可出 输，下 的“ 令程， 首搜下一 下 示 互下入时 路然命候径所 主加面时都 行入介 程，变 （分 则 命 直 令 在 一是要用)的的使命表可图是如 式用模 果函可 块想 引 样 果 不过引 。块 经点 不 装详安以模块，作通 语料 如 没 通来 感起 的针言要，的何 但间磨 " 作套 "" 赋 个 范 值 景$ 0的斜 0 楷动 文 安模的 对对 依 都 "/ . / 00下 到 命 ， 表 导程路操“件。区 的在中知块块执录 . ' , ' . . :.: . - 到 解到 : , 一目在 进后 0 .成 ，)经确 : 床 件到 _ 对 所表 = 列以形 第到 读行储 # _ 取数 . 取行 列已对，家 使 *格- , # = (" . # ' 体 色 , _ ， 表 _ 上= 0制除字 =#式到= " 颜 斜 除 建体 对 建 #齐 , _ =. 中 , _ 格 第， 颜 为 _ 充 . 置 ) . 为 #红 把到 (创 型 , , _, _ 0 右 . 上点. 为 线 0 0 0 将 ,# '用 行 格 : .( 开下 改经 简 已在 是 : / /开编 编 # . 函以 : 颜色. ) 示格 )函 到过已件 #一., 列”式 , # , ) 为 标准文档