Python对Excel进行读写，pythonexcel读写,#coding:utf=
#coding:utf=8__author__ = 'ZZ'import osimport natsortfrom operator import itemgetterfrom datetime import datetime,timedeltafrom xlrd import open_workbook, cellname, xldate_as_tuple,error_text_from_codefrom xlwt import easyxf, Workbookfrom xlutils.copy import copyfrom version import VERSION_version_ = VERSIONclass ExcelDriverLibrary: def __init__(self): self.wb = None self.rb = None self.Pid = None self.rowIndex = None self.sheetNum = None self.SheetNames = None self.fileName = None def open_excel(self,filename): tempDir = 'D:\\Codes\\PycharmProjects\\ExcelDataDriver\\ResFile\\' # filename = 'TestFile.xls' try: if filename.find(':')==1: self.rb = open_workbook(filename,formatting_info=True) else: print 'Opening file at %s' % filename #self.wb = open_workbook(os.path.join("/",self.tmpDir,filename),formatting_info=True, on_demand=True) self.rb = open_workbook(os.path.join(tempDir,filename),formatting_info=True, on_demand=True) filename = os.path.join(tempDir,filename) self.fileName = filename self.SheetNames = self.rb.sheet_names except Exception,e: print str(e) def get_sheet_names(self): SheetNames = self.rb.sheet_names() return SheetNames def get_number_of_sheets(self): sheetNum = self.rb.nsheets return sheetNum def get_column_count(self,SheetName): sheet = self.rb.sheet_by_name(SheetName) return sheet.ncols def get_row_count(self,SheetName): sheet = self.rb.sheet_by_name(SheetName) return sheet.nrows def get_column_values(self,SheetName,column,includeEmptyCells=True): sheet = self.rb.sheet_by_name(SheetName) data = {} for row_index in range(sheet.nrows): cell = cellname(row_index,int(column)) value = sheet.cell(row_index,int(column)).value data[cell] = value if includeEmptyCells is True: sortedData = natsort.natsorted(data.items(),key=itemgetter(0)) return sortedData else: data = dict([(k,v) for (k,v) in data.items() if v]) OrderedData = natsort.natsorted(data.items(),key=itemgetter(0)) return OrderedData def get_row_values(self,SheetName,row,includeEmptyCells=True): sheet = self.rb.sheet_by_name(SheetName) data = {} for col_index in range(sheet.ncols): cell = cellname(int(row),col_index) value = sheet.cell(int(row),col_index).value data[cell] = value if includeEmptyCells is True: sortedData = natsort.natsorted(data.items(),key=itemgetter(0)) return sortedData else: data = dict([(k,v) for (k,v) in data.items() if v]) OrderedData = natsort.natsorted(data.items(),key=itemgetter(0)) return OrderedData def get_sheet_values(self,SheetName,includeEmptyCells=True): sheet = self.rb.sheet_by_name(SheetName) data = {} for row_index in range(sheet.nrows): for col_index in range(sheet.ncols): cell = cellname(row_index,col_index) value = sheet.cell(row_index,col_index).value data[cell] = value if includeEmptyCells is True: sortedData = natsort.natsorted(data.items(),key=itemgetter(0)) return sortedData else: data = dict([(k,v) for (k,v) in data.items() if v]) OrderedData = natsort.natsorted(data.items(),key=itemgetter(0)) return OrderedData def get_workboot_value(self,includeEmptyCells=True): sheetData = [] workbookData = [] for sheet_name in self.SheetNames: if includeEmptyCells is True: sheetData = self.get_sheet_values(sheet_name) else: sheetData = self.get_sheet_values(sheet_name,False) sheetData.insert(0,sheet_name) workbookData.append(sheetData) return workbookData def read_cell_data_by_name(self,SheetName,cell_name): """ Uses the cell name to return the data from that cell. sheet = self.rb.sheet_by_name(SheetName) for row_index in range(sheet.nrows): for col_index in range(sheet.ncols): cell = cellname(row_index,col_index) if cell_name ==cell: cellValue = sheet.cell(row_index,col_index).value return cellValue def read_cell_data_by_coordinates(self, SheetName, column, row): Uses the column and row to return the data from that cell. my_sheet_index = self.SheetNames.index(SheetName) sheet = self.rb.sheet_by_index(my_sheet_index) cellValue = sheet.cell(int(row), int(column)).value return cellValue def Get_Sheet_Index(self,SheetName): sheetnum = self.get_number_of_sheets() for sheet_index in range(sheetnum): Current_SheetName = self.rb.sheet_names()[sheet_index] if SheetName == Current_SheetName.encode('gb2312').decode('gb2312'): return sheet_index def Get_Cell_Data_By_PID(self,SheetName,Pid,CellName): sheet = self.rb.sheet_by_name(SheetName) for row_index in range(sheet.nrows): Cell_Value = sheet.cell(int(row_index),0).value if Pid == Cell_Value: break for col_index in range(sheet.ncols): Cell_TitValue = sheet.cell(0,int(col_index)).value if CellName == Cell_TitValue: break cellValue = sheet.cell(row_index, col_index).value return cellValue def Modify_index_cell(self,SheetName,Pid,CellName,CellValue): CellName_index = self._get_CellName_index(CellName,SheetName) Pid_index = self._get_PID_index(Pid,SheetName) rb = open_workbook(self.fileName,formatting_info=True) try: sheet_index = rb._sheet_names.index(SheetName) except Exception,e: print u'请输入正确的Sheet名,如果是中文,记得在变量名前面加u.具体错误消息为：',e exit() wb =copy(rb) sheet = wb.get_sheet(sheet_index) sheet.write(Pid_index,CellName_index,CellValue) wb.save(self.fileName)
评论关闭