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

#!/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 "31"
  elif month==2:
    if year in ["2008","2012"]:
    
      return "29"
    else: return "28"
  else:
    return "30"
        

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"

#encoding=utf-8
import  xlrd
import xlwt
from xlutils.copy import copy

def getLastDay(year,month):
  if month in ["1","3","5","7","8","10","12"]:
    return "31"
  elif month=="2":
    if year in ["2008","2012"]:
    
      return "29"
    else: return "28"
  else:
    return "30"
    
    
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))