python操作excel，pythonexcel,python操作ex

python操作excel，pythonexcel,python操作ex

python操作exce的方式:

使用win32com使用xlrd(读excel)、xlwt(写excel)

1.使用win32com方式

代码:

# coding=utf-8from win32com.client import Dispatchimport pywintypes‘‘‘查看excel最大行数和列数打开一个空白新建EXCEL表格，按CTRL+下箭头，可以查看到最大行数；按CTRL+右箭头,可以查看到最大列标（若想显示列数，可在最右一列的某单元格中输入=column()回车，出现数字就是最大列数)。‘‘‘class Excel:    def __init__(self, filename=None):        self.excel = Dispatch(‘Excel.Application‘)  # 打开excel应用程序        self.excel.DisplayAlerts = 0        self.excel.Visible = 0        if filename is not None:            self.filename = filename            self.workBook = self.excel.Workbooks.Open(filename)        else:            self.filename = ‘‘            self.workBook = self.excel.Workbooks.Add()  # 新建excel    # 保存excel文件    def save(self, save_path=None):        if not save_path:            self.filename = save_path            self.workBook.SaveAs(save_path)        else:            self.workBook.Save()    # 关闭excel文件    def close(self):        self.workBook.Close(SaveChanges=0)    # 添加工作表    def addSheet(self, sheetName=None):        sheet = self.workBook.Sheets.Add()        if sheetName is not None:            sheet.Name = sheetName    def copySheet(self, srcSheetName, destSheetName=None, before=None):        sheet = self.workBook.WorkSheets(srcSheetName)        # 在指定工作表的后面插入新的工作表        if before is None:            sheet.Copy(None, sheet)            newSheet = sheet.Next            if destSheetName is not None:                newSheet.Name = destSheetName        # 在指定工作表的前面插入新的工作表        else:            sheet.Copy(before, None)            if destSheetName is not None:                index = before.Index - 1  # 所获新插入的工作表所在的位置(工作表的index从1开始）                self.workBook.Sheets(index).Name = destSheetName    # 删除工作表    def deleteSheet(self, sheetName):        try:            sheet = self.workBook.WorkSheets(sheetName)            if sheet is not None:                sheet.Delete()        except pywintypes.com_error:            pass    # 获取指定单元格的内容    def getCell(self, sheet, row, col):        workSheet = self.workBook.WorkSheets(sheet)        return workSheet.Cells(row, col).Value    # 设置单元格内容    def setCell(self, sheet, row, col, val):        workSheet = self.workBook.WorkSheets(sheet)        workSheet.Cells(row, col).Value = val    # 获取一块区域的内容    def getRange(self, sheet, row1, col1, row2, col2):        workSheet = self.workBook.WorkSheets(sheet)        return workSheet.Range(workSheet.Cells(            row1, col1), workSheet.Cells(row2, col2)).Value    # 拷贝一块区域    def copyRange(self, sheet, row1, col1, row2, col2, targetRow, targetCol):        workSheet = self.workBook.WorkSheets(sheet)        srcRange = workSheet.Range(            workSheet.Cells(                row1, col1), workSheet.Cells(                row2, col2))        targetRange = workSheet.Range(            workSheet.Cells(                targetRow, targetCol), workSheet.Cells(                targetRow + row2 - row1, targetCol + col2 - col1))        #targetRange.Value = srcRange.Value        srcRange.Copy(targetRange)  # 与targetRange.Value = srcRange.Value效果相同    # 移动一块区域    def cutRange(self, sheet, row1, col1, row2, col2, targetRow, targetCol):        workSheet = self.workBook.WorkSheets(sheet)        srcRange = workSheet.Range(            workSheet.Cells(                row1, col1), workSheet.Cells(                row2, col2))        # 方案1        #targetRange = workSheet.Range(workSheet.Cells(targetRow, targetCol), workSheet.Cells(targetRow + row2 - row1, targetCol + col2 - col1))        # targetRange.Value = srcRange.Value        # srcRange.Clear()  #清空原区域的值        # 方案2        # targetRange = workSheet.Range(workSheet.Cells(targetRow, targetCol), workSheet.Cells(targetRow + row2 - row1, targetCol + col2 - col1))        # srcRange.Cut(targetRange)  #直接调用系统函数        # 方案3与方案2类似        srcRange.Cut(workSheet.Cells(targetRow, targetCol))    # 清空指定区域内容    def clearRange(self, sheet, row1, col1, row2, col2):        workSheet = self.workBook.WorkSheets(sheet)        srcRange = workSheet.Range(            workSheet.Cells(                row1, col1), workSheet.Cells(                row2, col2))        srcRange.Clear()    # 添加图片    def addPicture(self, sheet, pictureName, left, top, width, height):        workSheet = self.workBook.WorkSheets(sheet)        ‘‘‘，AddPicture(self, Filename=<PyOleMissing object>, LinkToFile=<PyOleMissing object>,>, Left=<PyOleMissing object>, Top=<PyOleMissing object>, Width=<PyOleMissing objechod of win32com.client.CDispatch instance        ‘‘‘        workSheet.Shapes.AddPicture(            pictureName, 1, 1, left, top, width, height)    # 删除指定行    def deletRow(self, sheet, row):        if row < 1 or row > 1048576:            return        deleteRow = self.workBook.WorkSheets(sheet).Rows(row)        deleteRow.Delete()    # 删除指定列    def deleteCol(self, sheet, col):        if col < 1 or col > 16384:            return        deleteCol = self.workBook.WorkSheets(sheet).Columns(col)        deleteCol.Delete()

2.使用xlrd,xlwt操作excel

可参考:

http://xlwt.readthedocs.io/en/latest/

http://www.cnblogs.com/hupeng1234/p/6681830.html

http://www.cnblogs.com/hupeng1234/p/6681800.html

python操作excel