Python操作Excel——win32com模块和xlrd+xlwt+xlutils组合

今天，接到一个任务，要生成大约两百个excel文件，从2006年到2013年，每个月两个文件，这些文件中除了几个关于日期的单元格不同外，其他数据都相同，所以就想到可以用python写一个小脚本，自动生成文件。

从网上查找到python中操作Excel文件主要有两个模块，分别为win32com模块和xlrd+xlwt+xlutils组合

win32com模块很强大，但是读取文件的速度稍慢，而且只能在ms系统运行。

xlrd+xlwt+xlutils组合，xlrd只能读取excel文件，xlwt只能修改文件，xlutils可以复制excel文件，但要借助xlrd和xlwt才能运行，所以一般都是这三个模块结合起来使用（感觉好麻烦，要下载三个模块）。这个组合读取文件的速度很快，不过对于样式的把控不太好，复制的文件与原文件的样式有点区别，如果对样式的要求不高可以使用。

由于xlrd+xlwt+xlutils组合对样式的把控不会，所以最后我是用win32com模块成功实现脚本的。如果是ms系统，也推荐大家用win32com模块。

win32com的辅助类easyExcel：

#!/usr/bin/env python

# -*- coding: utf-8 -*-

from win32com.client import Dispatch

import win32com.client

class easyExcel:

    """A utility to make it easier to get at Excel.  Remembering

    to save the data is your problem, as is  error handling.

    Operates on one workbook at a time."""

    def __init__(self, filename=None):

        self.xlApp = win32com.client.Dispatch('Excel.Application')

        if filename:

            self.filename = filename

            self.xlBook = self.xlApp.Workbooks.Open(filename)

        else:

            self.xlBook = self.xlApp.Workbooks.Add()

            self.filename = ''

    def save(self, newfilename=None):

        if newfilename:

            self.filename = newfilename

            self.xlBook.SaveAs(newfilename)

        else:

            self.xlBook.Save()

    def close(self):

        self.xlBook.Close(SaveChanges=0)

        del self.xlApp

    def getCell(self, sheet, row, col):

        "Get value of one cell"

        sht = self.xlBook.Worksheets(sheet)

        return sht.Cells(row, col).Value

    def setCell(self, sheet, row, col, value):

        "set value of one cell"

        sht = self.xlBook.Worksheets(sheet)

        sht.Cells(row, col).Value = value

    def getRange(self, sheet, row1, col1, row2, col2):

        "return a 2d array (i.e. tuple of tuples)"

        sht = self.xlBook.Worksheets(sheet)

        return sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Value

    def addPicture(self, sheet, pictureName, Left, Top, Width, Height):

        "Insert a picture in sheet"

        sht = self.xlBook.Worksheets(sheet)

        sht.Shapes.AddPicture(pictureName, 1, 1, Left, Top, Width, Height)

    def cpSheet(self, before):

        "copy sheet"

        shts = self.xlBook.Worksheets

        shts(1).Copy(None,shts(1))

类的使用例子

myExcel=easyExcel("E:\python\sb.xls") #实例化一个easyExcel类 

myExcel.setCell(1,12,6,"hello")  #修改单元格内容，第一个参数是sheet的编号，第二个为行数，第三个为列数，（全部都以1开始，下面的xlrd那几个模块都以0开始的），最后是要修改的内容
a=myExcel.getCell(1,12,6) #获取单元格内容

myExcel.save("E:\python\sb1winD.xls") #保存文件，如果路径与打开时相同，即保存文件，如果不同即新建文件

myExcel.close()

#其他api可以参考easyExcel类

我的脚本的实现代码：

#encoding=utf-8

import easyExcel

from time import sleep

myExcel=easyExcel.easyExcel("E:\python\sb2.xls")

def getLastDay(year,month):

  if month in [1,3,5,7,8,10,12]:

    return ""

  elif month==2:

    if year in ["",""]:

      return ""

    else: return ""

  else:

    return ""

for i in range(2006,2014):

  for j in range(1,13):

#if 1:

#    i=2006

#    j=2

    getDateText="所得期间:  %s年   %s月"%(i,j)

    lastDay=getLastDay(i,j)

    writeDateText="填表日期：  %s年 %s月 %s日 "%(i,j,lastDay)

    getDateText=getDateText.decode("utf-8")

    writeDateText=writeDateText.decode("utf-8")

    dateStar="%s-%s-1"%(i,j)

    dateEnd="%s-%s-%s"%(i,j,lastDay)

    myExcel.setCell(1,4,1,writeDateText)

    myExcel.setCell(1,4,12,getDateText)

    myExcel.setCell(1,12,6,dateStar)

    myExcel.setCell(1,12,7,dateEnd)

    myExcel.save("E:\python\sb2\SB009-2--%s-%s.xls"%(i,j))

    print "Save ",i,j

    #wFile.save("sb1%s-%s.xls"%(i,j))

myExcel.close()

print "DONE"

xlrd+xlwt+xlutils 教程

xlrd

1.打开文件（不能用中文文件名，只能打开xls文件）

r_file =xlrd.open_workbook("demo1.xls")

2.获取工作表

sheet0=r_file.sheets()[0]#通过索引顺序获取

sheet0 = r_file.sheet_by_index(0) #通过索引顺序获取

sheet0 = r_file.sheet_by_name(u'Sheet1') #通过名称获取

3.读取行和列

sheet0.row_values(0) #读取第一行

sheet0.col_values(0) #读取第一列

4.获取行数和列数

nrows = sheet0.nrows #获取行数

ncols = sheet0.ncols #获取列数

5.读取单元格

cell_A1 = sheet0.cell(0,0).value

cell_A1 = sheet0.row(0)[0].value

cell_B4 = sheet0.cell(3,1).value

xlwt

1.新建一个excel文件 file = xlwt.Workbook()

2. 新建一个sheet sheet0= file.add_sheet('sheet name')

3.写入数据

table.write(行,列,value) sheet0.write(0,0,‘第一行，第一列（A1）'.decode('utf-8'))

sheet0.write(3,1,‘第四行，第二列（B4）'.decode('utf-8')) 保存文件 File.save('demo.xls')

4.xlwt样式

修改字体

style = xlwt.XFStyle() # 初始化样式

font = xlwt.Font() #为样式创建字体

font.name = 'Times New Roman'

font.bold = True

style.font = font #为样式设置字体

table.write(0, 1, 'some bold Times text', style) # 使用样式

更多样式的说明可以参考： http://blog.sina.com.cn/s/blog_5357c0af01019gjo.html

xlutils

复制excel对象

wb = copy(r_file)

获取工作表

sheet0=wb.get_sheet(0)

保存

wb.save('demo.xls')

copy有一个缺点，就是copy后会把样式格式化，我们可以写一个copy2，来让copy后的文件保留原有的样式

def copy2(r_file):

    '''

    附带样式的copy
　　xlrd打开文件，必须加参数formatting_info=True

    '''

    w = XLWTWriter()

    process(

        XLRDReader(r_file,'unknown.xls'),

        w

    )

    r_sheets=r_file.sheets()

    w_file, style_list = w.output[0][1], w.style_list

    for index,r_sheets in enumerate(r_sheets):

        w_sheet = w_file.get_sheet(index)

        rows = r_sheets.nrows  #获取行数

        cols = r_sheets.ncols  #获取列数

        for row in range(rows):

            for col in range(cols):

                xf_index = r_sheets.cell_xf_index(row, col)

                value=r_sheets.cell(row,col).value

                w_sheet.write(row, col, value, style_list[xf_index])

    return w_file,style_list

画边框的函数

def draw_border(r_sheet, w_sheet, left_top, right_bottom, style_list, border_type_index=5, border_color=0x40):

    '''

    @r_sheet:workbook 读取的sheet

    @w_sheet:workbook 写入的sheet

    @left_top:tuple 边框的左上角的坐标，如 (0,1)

    @right_bottom:tuple 边框的右下角的坐标，如 (10,5)

    @style_list : 读取的sheet的样式列表，通过copy2方法获取

    @border_type_index:int 边框的样式的下标

    @border_color:int 边框的颜色

    return 1

    '''

    import xlwt

    border_types = ['NO_LINE', 'THIN', 'MEDIUM', 'DASHED', 'DOTTED', 'THICK', 'DOUBLE']

    border_type = border_types[border_type_index % len(border_types)]

    border_type = getattr(xlwt.Borders, border_type)

    def _get_border(type, color, top=0, right=0, bottom=0, left=0):

        border = xlwt.Borders()

        for direct in ('top', 'right', 'bottom', 'left'):

            if locals().get(direct):

                setattr(border, direct, type)

                setattr(border, direct + '_colour', color)

        return border

    def _draw_boder(row, col, border):

        try:

            style_index = r_sheet.cell_xf_index(row, col)

            style = style_list[style_index]

            value = r_sheet.cell(row, col).value

        except:

            style = xlwt.XFStyle()

            value = ''

        style.borders = border

        w_sheet.write(row, col, value, style)

    if left_top > right_bottom:

        left_top, right_bottom = right_bottom, left_top

    left, top, right, bottom = left_top + right_bottom

    for row in range(top, bottom + 1):

        for col in range(left, right + 1):

            left_ = 1 if col == left else 0

            top_ = 1 if row == top else 0

            right_ = 1 if col == right else 0

            bottom_ = 1 if row == bottom else 0

            border = _get_border(border_type, border_color, top_, right_, bottom_, left_)

            _draw_boder(row, col, border)

    return 1

调用方法

import xlrd

r_file= xlrd.open_workbook('service_base.xls', formatting_info=True)

r_sheet=r_file.sheets()[0]

w_file,style_list =copy2(r_file)

w_sheet=w_file.get_sheet(0)

draw_border(r_sheet,w_sheet,(5,5),(0,0),style_list)

w_file.save('service_base1.xls')

以下是xlrd+xlwt+xlutils组合的代码：

#encoding=utf-8

import  xlrd

import xlwt

from xlutils.copy import copy

def getLastDay(year,month):

  if month in ["","","","","","",""]:

    return ""

  elif month=="":

    if year in ["",""]:

      return ""

    else: return ""

  else:

    return ""

rFile =xlrd.open_workbook("sb1.xls")

#for i in range(2006,2014):

#  for j in range(1,13):

if 1:

    i=2006

    j=2

    dateText="所得期间:  %s年   %s月"%(i,j)

    dateText=dateText.decode("utf-8")

    dateStar="%s-%s-1"%(i,j)

    dateEnd="%s-%s-%s"%(i,j,getLastDay(i,j))

    wFile=copy(rFile)

    sheet1=wFile.get_sheet(0)

    sheet1.write(3,11,dateText)

    sheet1.write(11,5,dateStar)

    sheet1.write(11,6,dateEnd)

    #wFile.save("sb1.xls")

    wFile.save("sb1%s-%s.xls"%(i,j))

在保存xls文件时，报错：'ascii' codec can't decode byte 0xe6 in position 0: ordinal not in range(128)

解决方法是write的内容都decode一下

sheet1.write(4+i,j+1,'你好'.decode('utf-8'))

巴特西

Python操作Excel——win32com模块和xlrd+xlwt+xlutils组合

最新文章

热门文章