python实现对excel的操作

最近在做数据迁移，需要在excell比对新旧模型的数据，可谓工程浩大。所以决定用python对数据进行批量操作。封装了一个对excel操作的简单类，方便使用。

deal_excel.py:

###Date:20180110
###Desc:excel简单操作类
import xlrd
from xlutils import copy
class Excel:
    #excel简单操作类
    def __init__(self):
        self.handle,self.mhandle = None, None
        self.name_sheets = {}
        self.newsheet = None
        self.filename = None
        self.ceil_list = []
        self.rows_list = []
    def setSheet(self,fileName):
        self.filename = fileName
        self.handle = xlrd.open_workbook(fileName)
        self.sheets = dict([(sheetname,self.handle.sheet_by_name(sheetname)) for sheetname in self.handle.sheet_names()])
        self.mhandle = copy(xlrd.open_workbook(self.filename, formatting_info=True))
    def getCeilList(self,sheetName,clos):
        for i in range(self.sheets[sheetName].nrows):
            self.ceil_list.append(self.sheets[sheetName].cell(i,clos).value)
        return self.ceil_list
    def getRowsList(self,sheetName):
        for i in range(self.sheets[sheetName].nrows):
            self.rows_list.append(self.sheets[sheetName].row_values(i))
        return self.rows_list
    def getNameByIndex(self,index):
        return self.handle.sheet_by_index(index).name
    def modifyToNewSheet(self,row,coil,str,sheet = 0):
        self.newsheet = self.mhandle.get_sheet(sheet)
        self.newsheet.write(row,coil,str)

excel_demo.py:

#!/usr/bin/env python
#coding=gbk


from sys import path
path.append('../class')


import deal_excel as excel

if __name__ == "__main__":
    myExcel = excel.Excel()
    myExcel.setSheet("../data/数据迁移比对.xls")
    name = myExcel.getNameByIndex(0)
    print(name)
    ceil=myExcel.getCeilList(name,2)
    for i in ceil:
        print (i)
        rows = myExcel.getRowsList(name)
    for i in rows:
        print(i[0])