1 #!/usr/bin/env python2 3 ###file：xlrdT3a.py4 5 classreadexcel(object):6 """Simple OS Independent Class for Extracting Data from Excel Files7 the using xlrd module found at http://www.lexicon.net/sjmachin/xlrd.htm8 Author:snowzjy Email:farsoftwind@gmail.com9 10 Versions of Excel supported: 2004, 2002, XP, 2000, 97, 95, 5, 4, 311 xlrd version tested: 0.6.112 13 Data is extracted by creating a iterator object which can be used to14 return data one row at a time. The default extraction method assumes15 that the worksheet is in tabular format with the first nonblank row16 containing variable names and all subsequent rows containing values.17 This method returns a dictionary which uses the variables names as keys18 for each piece of data in the row. Data can also be extracted with19 each row represented by a list.20 21 Extracted data is represented fairly logically. By default dates are22 returned as strings in "yyyy/mm/dd" format or "yyyy/mm/dd hh:mm:ss",23 as appropriate. However, dates can be return as a tuple containing24 (Year, Month, Day, Hour, Min, Second) which is appropriate for usage25 with mxDateTime or DateTime. Numbers are returned as either INT or26 FLOAT, whichever is needed to support the data. Text, booleans, and27 error codes are also returned as appropriate representations.28 29 Quick Example:30 xl = readexcel('testdata.xls')31 sheetnames = xl.worksheets()32 for sheet in sheetnames:33 print sheet34 for row in xl.getiter(sheet):35 # Do Something here36 """37 def__init__(self, filename):38 """Returns a readexcel object of the specified filename - this may39 take a little while because the file must be parsed into memory"""40 importxlrd41 importos.path42 ifnotos.path.isfile(filename):43 raiseNameError,"%s is not a valid filename"%filename44 self.__filename__=filename45 self.__book__=xlrd.open_workbook(filename,formatting_info=True)46 self.__sheets__={}47 self.__sheetnames__=[]48 foriinself.__book__.sheet_names():49 uniquevars=[]50 firstrow=051 sheet=self.__book__.sheet_by_name(i)52 forrowinrange(firstrow,sheet.nrows):53 types,values=sheet.row_types(row),sheet.row_values(row)54 nonblank=False55 forjinvalues:56 ifj!='':57 nonblank=True58 break59 ifnonblank:60 #Generate a listing of Unique Variable Names for Use as61 #Dictionary Keys In Extraction. Duplicate Names will62 #be replaced with "F#"63 variables=self.__formatrow__(types,values,False)64 unknown=165 whilevariables:66 var=variables.pop(0)67 ifvarinuniquevarsorvar=='':68 var='F'+str(unknown)69 unknown+=170 uniquevars.append(str(var))71 firstrow=row+172 break73 self.__sheetnames__.append(i)74 self.__sheets__.setdefault(i,{}).__setitem__('rows',sheet.nrows)75 self.__sheets__.setdefault(i,{}).__setitem__('cols',sheet.ncols)76 self.__sheets__.setdefault(i,{}).__setitem__('firstrow',firstrow)77 self.__sheets__.setdefault(i,{}).__setitem__('variables',uniquevars[:])78 defgetiter(self, sheetname, returnlist=False, returntupledate=False):79 """Return an generator object which yields the lines of a worksheet;80 Default returns a dictionary, specifing returnlist=True causes lists81 to be returned. Calling returntupledate=True causes dates to returned82 as tuples of (Year, Month, Day, Hour, Min, Second) instead of as a83 string"""84 ifsheetnamenotinself.__sheets__.keys():85 raiseNameError,"%s is not present in %s"%(sheetname,\86 self.__filename__)87 ifreturnlist:88 return__iterlist__(self, sheetname, returntupledate)89 else:90 return__iterdict__(self, sheetname, returntupledate)91 92 defgetxfiter(self, sheetname, returnlist=False, returntupledate=False):93 """Return an generator object which yields the lines of a worksheet;94 Default returns a dictionary, specifing returnlist=True causes lists95 to be returned. Calling returntupledate=True causes dates to returned96 as tuples of (Year, Month, Day, Hour, Min, Second) instead of as a97 string"""98 ifsheetnamenotinself.__sheets__.keys():99 raiseNameError,"%s is not present in %s"%(sheetname,\100 self.__filename__)101 ifreturnlist:102 return__XF_iterlist__(self, sheetname, returntupledate)103 else:104 return__XF_iterdict__(self, sheetname, returntupledate)105 106 defworksheets(self):107 """Returns a list of the Worksheets in the Excel File"""108 returnself.__sheetnames__109 defnrows(self, worksheet):110 """Return the number of rows in a worksheet"""111 returnself.__sheets__[worksheet]['rows']112 defncols(self, worksheet):113 """Return the number of columns in a worksheet"""114 returnself.__sheets__[worksheet]['cols']115 defvariables(self,worksheet):116 """Returns a list of Column Names in the file,117 assuming a tabular format of course."""118 returnself.__sheets__[worksheet]['variables']119 def__formatrow__(self, types, values, wanttupledate):120 """Internal function used to clean up the incoming excel data"""121 ## Data Type Codes:122 ## EMPTY 0123 ## TEXT 1 a Unicode string124 ## NUMBER 2 float125 ## DATE 3 float126 ## BOOLEAN 4 int; 1 means TRUE, 0 means FALSE127 ## ERROR 5128 importxlrd129 returnrow=[]130 foriinrange(len(types)):131 type,value=types[i],values[i]132 iftype==2:133 ifvalue==int(value):134 value=int(value)135 eliftype==3:136 datetuple=xlrd.xldate_as_tuple(value, self.__book__.datemode)137 ifwanttupledate:138 value=datetuple139 else:140 #time only no date component141 ifdatetuple[0]==0anddatetuple[1]==0and\142 datetuple[2]==0:143 value="%02d:%02d:%02d"%datetuple[3:]144 #date only, no time145 elifdatetuple[3]==0anddatetuple[4]==0and\146 datetuple[5]==0:147 value="%04d/%02d/%02d"%datetuple[:3]148 else:#full date149 value="%04d/%02d/%02d %02d:%02d:%02d"%datetuple150 eliftype==5:151 value=xlrd.error_text_from_code[value]152 returnrow.append(value)153 returnreturnrow154 155 156 def__iterlist__(excel, sheetname, tupledate):157 """Function Used To Create the List Iterator"""158 sheet=excel.__book__.sheet_by_name(sheetname)159 forrowinrange(excel.__sheets__[sheetname]['rows']):160 types,values=sheet.row_types(row),sheet.row_values(row)161 yieldexcel.__formatrow__(types, values, tupledate)162 163 def__iterdict__(excel, sheetname, tupledate):164 """Function Used To Create the Dictionary Iterator"""165 sheet=excel.__book__.sheet_by_name(sheetname)166 forrowinrange(excel.__sheets__[sheetname]['firstrow'],\167 excel.__sheets__[sheetname]['rows']):168 types,values=sheet.row_types(row),sheet.row_values(row)169 formattedrow=excel.__formatrow__(types, values, tupledate)170 #Pad a Short Row With Blanks if Needed171 foriinrange(len(formattedrow),\172 len(excel.__sheets__[sheetname]['variables'])):173 formattedrow.append('')174 yielddict(zip(excel.__sheets__[sheetname]['variables'],formattedrow))175 176 #-----------------------------------------------------------------------------177 178 defget_color(book, color, styleName) :179 #Trying default value180 ifcolor==None:181 color=book.colour_map[0x7FFF]182 colorStyle=''183 ifnotcolor==None:184 colorStyle='%s(%d,%d,%d)'%(styleName,color[0],color[1],color[2])185 returncolorStyle186 187 def__XF_iterlist__(excel, sheetname, tupledate):188 importxlrd189 """Function Used To Create the Dictionary Iterator"""190 sheet=excel.__book__.sheet_by_name(sheetname)191 ## if not sheet.nrows:192 ## return None # skip empty sheets193 merged={}194 skipped={}195 forcrangeinsheet.merged_cells:196 rlo, rhi, clo, chi=crange197 merged[(rlo,clo)]=(rhi-rlo,chi-clo)#colspan,rowspan198 forrxinxrange(rlo, rhi):199 forcxinxrange(clo, chi):200 skipped[(rx,cx)]=True201 skipped[(rlo,clo)]=False202 forridxinrange(excel.__sheets__[sheetname]['rows']):203 types,values=sheet.row_types(ridx),sheet.row_values(ridx)204 rowvalue=excel.__formatrow__(types, values, tupledate)205 206 bufxf=[]207 row=sheet.row(ridx)208 ifnotfilter(lambdacell: unicode(cell.value), row):209 continue210 print(u'\nrow%d'%(ridx+1))211 forcidx, cellinenumerate(row):212 213 key=(ridx,cidx)214 ifkeyinskippedandskipped[key]:215 continue#skipping - merged columns216 formatIndex=sheet.cell_xf_index(ridx, cidx)217 format=excel.__book__.computed_xf_list[formatIndex]218 font=excel.__book__.font_list[format.font_index]219 color=excel.__book__.colour_map[font.colour_index]220 bgColor=excel.__book__.colour_map[format.background.pattern_colour_index]221 style='normal'222 iffont.italic:223 style='italic'224 decor='none'225 iffont.underlined:226 decor='underline'227 colspan=''228 rowspan=''229 ifkeyinmerged:230 spans=merged[key]231 rowspan='rowspan="%d"'%spans[0]232 colspan='colspan="%d"'%spans[1]233 234 ifnotcolor==NoneandbgColor==None:235 print(u'col%d'%(cidx+1)),236 #buf.append(u'style="font-weight:%(weight)d; font-style:%(style)s; %(colorStyle)s;237 238 #\%(bgColorStyle)s;\239 #text-decoration:%(decor)s " %(rowspan)s %(colspan)s>%(value)s' %240 print(u'%(colorStyle)s;%(bgColorStyle)s'%241 {242 #"weight":font.weight,243 #"style":style,244 #"value":sheet.cell_value(ridx, cidx),245 "colorStyle":get_color(excel.__book__,color,'fc'),246 "bgColorStyle":get_color(excel.__book__,bgColor,'bc'),247 #"decor":decor,248 #"colspan":colspan,249 #"rowspan":rowspan250 }),251 252 xfc= (rowvalue[cidx],get_color(excel.__book__,color,'fc'),get_color(excel.__book__,bgColor,'bc'))
253
254255 bufxf.append(xfc)256 257 yieldbufxf258 259 def__XF_iterdict__(excel, sheetname, tupledate):260 importxlrd261 """Function Used To Create the Dictionary Iterator"""262 sheet=excel.__book__.sheet_by_name(sheetname)263 ## if not sheet.nrows:264 ## return None # skip empty sheets265 merged={}266 skipped={}267 forcrangeinsheet.merged_cells:268 rlo, rhi, clo, chi=crange269 merged[(rlo,clo)]=(rhi-rlo,chi-clo)#colspan,rowspan270 forrxinxrange(rlo, rhi):271 forcxinxrange(clo, chi):272 skipped[(rx,cx)]=True273 skipped[(rlo,clo)]=False274 forridxinrange(excel.__sheets__[sheetname]['firstrow'],\275 excel.__sheets__[sheetname]['rows']):276 types,values=sheet.row_types(ridx),sheet.row_values(ridx)277 rowvalue=excel.__formatrow__(types, values, tupledate)278 279 bufxf=[]280 row=sheet.row(ridx)281 ifnotfilter(lambdacell: unicode(cell.value), row):282 continue283 print(u'\nrow%d'%(ridx+1))284 forcidx, cellinenumerate(row):285 key=(ridx,cidx)286 ifkeyinskippedandskipped[key]:287 continue#skipping - merged columns288 formatIndex=sheet.cell_xf_index(ridx, cidx)289 format=excel.__book__.computed_xf_list[formatIndex]290 font=excel.__book__.font_list[format.font_index]291 color=excel.__book__.colour_map[font.colour_index]292 bgColor=excel.__book__.colour_map[format.background.pattern_colour_index]293 style='normal'294 iffont.italic:295 style='italic'296 decor='none'297 iffont.underlined:298 decor='underline'299 colspan=''300 rowspan=''301 ifkeyinmerged:302 spans=merged[key]303 rowspan='rowspan="%d"'%spans[0]304 colspan='colspan="%d"'%spans[1]305 306 ifcolor!=NoneorbgColor!=None:307 print(u'col%d'%(cidx+1)),308 #buf.append(u'style="font-weight:%(weight)d; font-style:%(style)s; %(colorStyle)s;
#\(bgColorStyle)s;\309 #text-decoration:%(decor)s " %(rowspan)s %(colspan)s>%(value)s' %310 print(u'%(colorStyle)s %(bgColorStyle)s;'%311 {312 #"weight":font.weight,313 #"style":style,314 #"value":sheet.cell_value(ridx, cidx),315 "colorStyle":get_color(excel.__book__,color,'fc'),316 "bgColorStyle":get_color(excel.__book__,bgColor,'bc'),317 #"decor":decor,318 #"colspan":colspan,319 #"rowspan":rowspan320 }),321 322 xfc=(rowvalue[cidx],u'%(colorStyle)s'%{"colorStyle":get_color(excel.__book__,color,'fc')},\323 u'%(bgColorStyle)s'%{"bgColorStyle":get_color(excel.__book__,bgColor,'bc')})324 bufxf.append(xfc)325 326 yielddict(zip(excel.__sheets__[sheetname]['variables'],bufxf))
678
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
