python 操作 excel

来源：互联网发布：windows如何切换到桌面编辑：程序博客网时间：2024/06/05 04:04
#! /usr/bin/env python#coding=utf-8'''FuncName: JzOperateExcel.pyDesc: operate excelDate: 2016-10-12 14:30Author: johnnyHome: http://blog.csdn.net/z_johnny'''import xlwtimport xlrdimport osfrom xlutils.copy import copyclass JzWriteExcel(object):    """    操作excel表格    写入excel：1.无文件则自动创建文件，创建新文件和新工作表              2.存在文件且存在指定工作表，直接操作该工作表              3.存在文件且不存在指定工作表，新建指定工作表并写入该表，不影响已存在工作表数据    已存在bug：新创建的xls文件，该文件中新创建的第一个工作表，写入数据后，0行0列不能写入数据，再次写入则0-0数据写入成功    """    def __init__(self, fileName, sheetName):        self.fileName = fileName        self.sheetName = sheetName        self.style = self.sheetStyle()    def sheetStyle(self):        """        添加样式：        http://nullege.com/codes/search/xlwt.Style        https://xlwt.readthedocs.io/en/latest/        :return: 各种配置参数        """        #style = xlwt.easyxf('font: color-index red, bold on')        font = xlwt.Font() # Create the Font        # 字体        font.name = 'Times New Roman'        # 粗体        #font.bold = True        # 下划线        #font.underline = True        # 斜体        #font.italic = True        pattern = xlwt.Pattern() # Create the Pattern        pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN 关模式, SOLID_PATTERN 开模式, or 0x00 through 0x12        pattern.pattern_fore_colour = 2 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...        style = xlwt.XFStyle() # Create the Style 初始化样式        style.font = font # Apply the Font to the Style 为样式设置字体        style.pattern = pattern # Add Pattern to Style        return style    def sheetValue(self,row,col,value,style=False):        """        :param sheetName: 表名, fileName: 文件名, row:行, col：列, value:值, style:格式        :return: 对文件的指定表进行写数据操作，支持重复写        """        # xls文件不存在则新创建文件和新创建工作表        if os.path.exists(self.fileName) == False:            writeExcel = xlwt.Workbook()            # 注意：如果对同一个单元格重复操作，会引发overwrite Exception，想要取消该功能，需要在添加工作表时指定为可覆盖，所以在打开时加cell_overwrite_ok=True解决            writeExcel.add_sheet(self.sheetName, cell_overwrite_ok=True)            # 这里只能保存扩展名为xls的，xlsx的格式不支持            writeExcel.save(self.fileName)        else:            # open existed xls file 需要 newWb = copy('fileName')            # 注意添加参数formatting_info=True，得以保存之前数据的格式            readExcel_old = xlrd.open_workbook(self.fileName, formatting_info=True)            # 判断sheetName的索引，将数据写入该表            sheetNames = readExcel_old.sheet_names()            readExcel_new = copy(readExcel_old)            # 判断是否存在sheetName，不存在则新建该工作表，不影响其他表数据            if self.sheetName not in sheetNames:                readExcel_new.add_sheet(self.sheetName)                readExcel_new.save(self.fileName)                readExcel_new = xlrd.open_workbook(self.fileName, formatting_info=True)                sheetNames = readExcel_new.sheet_names()                readExcel_new = copy(readExcel_new)            else:                pass            sheetNameIndex = sheetNames.index(self.sheetName)            writeExcel= readExcel_new.get_sheet(sheetNameIndex)            if style:                writeExcel.write(row, col, value, self.style)            else:                writeExcel.write(row, col, value)            # 这里只能保存扩展名为xls的，xlsx的格式不支持            readExcel_new.save(self.fileName)class JzReadExcel(object):    def __init__(self, fileName, sheetName):        self.fileName = fileName        self.sheetName = sheetName        self.sheets = xlrd.open_workbook(fileName, formatting_info=True)        self.tables = self.sheets.sheet_by_name(self.sheetName)        self.handele = dict([(sheetName,self.sheets.sheet_by_name(sheetName))                             for sheetName in self.sheets.sheet_names()])    # 获取整行的值    def sheetRowValue(self, row = 0):        if self.sheetName not in self.handele: return None        return self.tables.row_values(row)    # 获取整列的值    def sheetColValue(self, col = 0):        if self.sheetName not in self.handele: return None        return self.tables.row_values(col)    # 获取所有表的名称    def sheetNames(self):        return self.sheets.sheet_names()    # 获取单元格的值    def sheetCellValue(self, row, col):        if self.sheetName not in self.handele: return None        return self.tables.cell(row,col).value    # 获取工作表的行数    def sheetRowNum(self):        if self.sheetName not in self.handele: return None        return self.tables.nrows    # 获取工作表的列数    def sheetColNum(self):        if self.sheetName not in self.handele: return None        return  self.tables.ncolsif __name__ == "__main__":    fileName = "johnny.xls"    sheetName = "Sheet1"    OpWrite = JzWriteExcel(fileName,sheetName)    style = True    #print "Write new values ok!"    #print "row    col    value"    for row in range(10):        col = row        value = "johnny1"        OpWrite.sheetValue(row, col, value, style)        #print "%2s %6s %12s"%(row,col,value)    OpRead = JzReadExcel(fileName, sheetName)    for i in range(OpRead.sheetRowNum()):        print OpRead.sheetRowValue(i)    print OpRead.sheetRowValue(1)    print OpRead.sheetColValue(2)    print OpRead.sheetNames()    print OpRead.sheetCellValue(2,2)    print OpRead.sheetRowNum()    print OpRead.sheetColNum()
0 0