Python对Excel操作详解

文档摘要：

 

本文档主要介绍如何通过python对office excel进行读写操作，使用了xlrd、xlwt和xlutils模块。另外还演示了如何通过Tcl  tcom包对excel操作。
 
关键字：
Python、Excel、xlrd、xlwt、xlutils、TCl、tcom
1      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则直接写表达式就可以了。
Python很多功能都是靠模块实现的，比如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的其他语法可以参考其它资料学习。
 
2      Tcl对Excel操作
在使用python对excel操作之前搜索过如何通过Tcl对excel操作，Tcl本身没有提供对excel操作的命令，可以通过tcom外部包来调用excel的接口实现，但是个人感觉实现起来比较麻烦，msdn网站上虽然提供了excel的接口，但示例都是针对VB脚本语言写的，Tcl如果想要调用的话还需要转换，如下是一段简单的Tcl代码展示如何通过tcom对excel进行操作，但也花了本人不少时间琢磨。
  1. # 加载tcom包
  2. package require tcom
  3. set filename "F:/1.xls"
  4. # 创建com实例，打开工作表，下面四句都是套路
  5. set excel [::tcom::ref createobject "Excel.Application"]
  6. set workbooks [$excel Workbooks]
  7. set workbook [$workbooks Open $filename]
  8. set worksheets [$workbook Worksheets]
  9. # "sheet1"为sheet的名称
  10. set worksheet [$worksheets Item "sheet1"]
  11. # 创建单元格对象
  12. set cells [$worksheet Cells]
  13. # 给单元格B2赋值为“hsdf”
  14. $cells Item 2 B "hsdf"
  15. # 获取sheet的个数并赋值给sheetCount
  16. set sheetCount [$worksheets Count]
  17. # 获取A1至A15单元的范围对象
  18. set range [$worksheet Range A1 A15]
  19. # 给A1至A15单元赋值
  20. $range Value2 "abcdefg"
  21. # 获取A1至A15的值，并赋值给A,A是一个列表list
  22. set  A [$range Value2]
  23. # 设置单元的背景色
  24. set interior [$range Interior]
  25. $interior Color [expr 0x00FFE0]
  26. # 设置单元的前景色和字体大小、加粗、斜体、字体
  27. set font [$range Font]
  28. $font Color [expr 0xFF0000]
  29. $font Bold 1
  30. $font Size 10
  31. $font Italic 0
  32. $font Name "华文行楷"
  33. # 设置单元格的宽度为自动调整
  34. set entire [$range EntireColumn]
  35. $entire AutoFit
  36. # 保存文档
  37. $workbook Save
  38. # 显示Excel
  39. $excel Visible 1
3      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.5\Lib下，此时在命令行下输入import xlrd或者import xlwt，会出现提示ImportError: No module named xlwt，这表明还没有安装xlwt模块。
python 导入一个模块的过程要求有一个叫做“路径搜索”的操作过程，即是在文件系统“预先设定的区域”查找模块文件以加载模块的过程。这个预先设定的区域其实是python搜索路径的一组目录。这个目录保存在sys.path中，如果你想知道python导入模块时会在哪些路径搜索模块，你可以执行以下命令查看搜索路径目录：
>>> import sys
 >>> sys.path
 ['D:\\python\\shell\\2.7.5','C:\\Python2.7.5\\Lib\\idlelib','C:\\Python2.7.5\\lib\\site-packages\\setuptools-1.3-py2.7.egg','C:\\Python2.7.5\\lib\\site-packages\\xlutils-1.7.0-py2.7.egg','C:\\Windows\\system32\\python27.zip','C:\\Python2.7.5\\DLLs','C:\\Python2.7.5\\lib','C:\\Python2.7.5\\lib\\plat-win', 'C:\\Python2.7.5\\lib\\lib-tk','C:\\Python2.7.5','C:\\Python2.7.5\\lib\\site-packages','C:\\Python2.7.5\\lib\\site-packages\\win32','C:\\Python2.7.5\\lib\\site-packages\\win32\\lib','C:\\Python2.7.5\\lib\\site-packages\\Pythonwin']
在sys.path中找到一个路径为'C:\\Python2.7.5\\lib'，所以我们把模块解压缩到这个目录下。命令>>>sys.path.append('C:\\Python2.7.5\\lib')在最后添加一个目录，sys.path.insert(0,' C:\\Python2.7.5\\lib ')在第一位插入一个目录。
解压缩完成并放在正确目录后，在cmd下进入package当前目录，然后输入命令“C:\Python2.7.5\Lib\xlrd-0.8.0>python setup.py install”，安装完成后可以输入import xlrd，dir(xlrd)来确认是否已经安装正确。
4      xlrd简单使用方法
  1. >>>import xlrd
  2. >>>excel = xlrd.open_workbook("C:\Users\HuZhangdong\Desktop\ASB测试床环境信息图.xls") #打开文件并将对象存储到excel中
  3. >>>sheet = excel.sheet_by_index(0) #通过索引读取sheet对象，第一个sheet的索引为"0"
  4. >>>row_3 = sheet.row_values(2) #读取第3行的所有数据，并以列表的形式存储到row_3中
  5. >>>col_3 = sheet.col_values(2) #读取第3列的所有数据，并以列表list的形式存储到col_3中
  6. >>>cell_12_7 = sheet.cell_value(11,6) #读取第12行第7列的数据，并存储到cell_12_7中
  7. >>>cell_11_11 = sheet.cell(10,10).value #读取第11行第11列的数据，并存储到cell_11_11中
  8. >>>cell_7_8 = sheet.row(6)[7].value   #读取第7行第8列的数据
  9. >>>cell_7_8 = sheet.cel(7)[6].value   #读取第8列第7行的数据
  10. >>>num_rows = sheet.nrows   #读取sheet的总行数
  11. >>>num_cols = sheet.ncols  #读取sheet的总列数
 
基本上面的命令已经可以满足目前对excel读取的操作了，接下给大家讲解xlwt的用法。
5      xlwt简单使用方法
  1. #-*- coding: UTF-8 -*- #设置编码格式为 utf-8
  2.  
  3. import os,xlwt,datetime #导入模块
  4.  
  5. data =xlwt.Workbook() #新建一个Workbook
  6. sheet = data.add_sheet(u"sheet") #新建一个sheet，名称为'sheet'
  7.  
  8. style1 = xlwt.XFStyle() #创建格式style1
  9. style2 = xlwt.XFStyle()
  10. style3 = xlwt.XFStyle()
  11.  
  12. #设置字体格式
  13. font1 = xlwt.Font() #创建font1
  14. font1.name = 'Times New Roman' #字体为'Times New Roman'
  15. font1.bold = True #加粗
  16. font1.colour_index = 2 #字体颜色为红色，0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan
  17. font1.underline = xlwt.Font.UNDERLINE_DOUBLE #下划线类型，UNDERLINE_DOUBLE 代表双下划线，另外还有 UNDERLINE_NONE, UNDERLINE_SINGLE, UNDERLINE_SINGLE_ACC, UNDERLINE_DOUBLE, UNDERLINE_DOUBLE_ACC
  18. font1.escapement = xlwt.Font.ESCAPEMENT_SUPERSCRIPT #设置上标
  19. font1.family = xlwt.Font.FAMILY_ROMAN
  20. font1.height = 0x190 #0x190是16进制，换成10进制为400，然后除以20，就得到字体的大小为20
  21. style1.font = font1 #将创建的font1字体格式应用到style1上
  22.  
  23. font2 = xlwt.Font() #创建font2
  24. font2.name = "Algerian" #字体为'Algerian'       
  25. font2.colour_index = 3 #字体颜色为绿色
  26. font2.italic = True #斜体
  27. font2.struck_out = True #删除线
  28. font2.height = 0x258 #字体大小为30
  29. style2.font = font2 #将创建的font2字体格式应用到style2上
  30.  
  31. #设置列宽
  32. sheet.col(0).width = 6000
  33. sheet.col(1).width = 12000
  34. sheet.set_col_default_width(2)
  35.  
  36. #设置单元格对齐方式
  37. alignment = xlwt.Alignment() #创建alignment
  38. alignment.horz = xlwt.Alignment.HORZ_CENTER  #设置水平对齐为居中，May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
  39. alignment.vert = xlwt.Alignment.VERT_CENTER #设置垂直对齐为居中，May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
  40. style3.alignment = alignment #应用alignment到style3上
  41.  
  42. #插入时间
  43. style3.num_format_str = 'YYYY-MM-DD HH:MM:SS' #设置时间格式
  44. sheet.write(1,1,datetime.datetime.now(),style3) #在第2行第2列插入当前时间，格式为style3
  45.  
  46. #设置单元格背景颜色
  47. pattern_yellow = xlwt.Pattern() #创建pattern_yellow
  48. pattern_yellow.pattern = xlwt.Pattern.SOLID_PATTERN #设置填充模式为全部填充
  49. pattern_yellow.pattern_fore_colour = 5 #设置填充颜色为yellow黄色
  50. style1.pattern = pattern_yellow  #把设置的pattern应用到style3上
  51. pattern_red = xlwt.Pattern() #创建pattern_red
  52. pattern_red.pattern = xlwt.Pattern.SOLID_PATTERN #设置填充模式为全部填充
  53. pattern_red.pattern_fore_colour = 2 #设置填充颜色为red红色
  54. style2.pattern = pattern_red  #把设置的pattern应用到style4上 
  55.  
  56. #设置单元格边框
  57. borders = xlwt.Borders() #创建borders
  58. borders.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.
  59. borders.right = xlwt.Borders.THIN #设置右边框的类型为细线
  60. borders.top = xlwt.Borders.DOTTED #设置上边框的类型为打点的
  61. borders.bottom = xlwt.Borders.THICK #设置底部边框类型为粗线
  62. borders.left_colour = 0x10 #设置左边框线条颜色
  63. borders.right_colour = 0x20
  64. borders.top_colour = 0x30
  65. borders.bottom_colour = 0x40
  66. style1.borders = borders #将borders应用到style1上
  67. style2.borders = borders #将borders应用到style2上
  68.  
  69. sheet.write(3, 0, 'HuZhangdong', style1) #在第4行第1列写入'HuZhangdong'，格式引用style1
  70. sheet.write(4, 0, 'YinMengran' , style2) #在第5行第1列写入'YinMengran'，格式引用style2
  71.  
  72. data.save(u'e:\\3.xls') #保存到 e:\\3.xls
  73.  
函数xlwt.Workbook()只能新建一个excel文档，不能打开一个已经存在的文档，下一章会讲解如何通过xlutils修改一个已经存在的excel文档。
6      xlutils简单使用方法
本章将讲解如何使用xlutils编辑一个已经存在的文档，需要导入xlutils模块，下载地址是http://www.python-excel.org/，模块成功安装后就开始编写脚本吧。
  1. #-*- coding:utf-8 -*- #设置当前编码格式为utf-8
  2. import xlwt #导入xlwt模块
  3. import xlrd #导入xlrd模块
  4. from xlutils.copy import copy       #导入import模块的copy函数，接下来就可以直接使用函数copy了。
  5.  
  6. fileName = "E:\\4.xls"
  7. sheetName = "sheet1"
  8.  
  9. styleBoldRed = xlwt.easyxf('font: color-index red, bold on') #设置字体，颜色为红色，加粗
  10. oldWb = xlrd.open_workbook(fileName, formatting_info=True) #使用xlrd.open_workbook函数打开文件，formatting_info=True表示保留该文件的格式
  11. newWb = copy(oldWb) #通过copy函数把oldWb copy到newWb，然后通过编辑newWb来实现编辑已经存在的文件。
  12. newWs = newWb.get_sheet(0) #读取第一个sheet
  13. newWs.write(4, 0, "value1",styleBoldRed) #第5行第1列写入值“value1”，格式采用styleBoldRed。
  14. newWs.write(4, 1, "value2",styleBoldRed) #第5行第2列写入值“value2”
  15. newWs.write(4, 2, "value3",styleBoldRed) #第5行第3列写入值“value3”
  16. newWb.save(fileName) #文件保存为"E:\\4.xls"

 

posted @ 2018-04-26 11:12  攻城獅  阅读(1669)  评论(0)  编辑  收藏  举报