#coding=utf-8import osimport os.pathimport sysfrom xlrd import open_workbookfrom xlutils.copy import copyfrom g315.config import confimport chardetimport rep=pile(r'(?i){{(.*?)}}')source_file_mold = os.path.join(conf.APP_DIR, "public/excel/source/module.xls") target_file_mold = os.path.join(conf.APP_DIR, "public/excel/target/result.xls")import xlrdimport xlwtfrom xlrd import open_workbook,cellnameabsfrom xlutils.copy import copydef copy_xf(rdbook,rdxf):"""clone a XFstyle from xlrd XF class,the code is copied from xlutils.copy module """wtxf = xlwt.Style.XFStyle()## number format#wtxf.num_format_str = rdbook.format_map[rdxf.format_key].format_str## font#wtf = wtxf.fontrdf = rdbook.font_list[rdxf.font_index]wtf.height = rdf.heightwtf.italic = rdf.italicwtf.struck_out = rdf.struck_outwtf.outline = rdf.outlinewtf.shadow = rdf.outlinewtf.colour_index = rdf.colour_indexwtf.bold = rdf.bold #### This attribute is redundant, should be driven by weight wtf._weight = rdf.weight #### Why "private"?wtf.escapement = rdf.escapementwtf.underline = rdf.underline_type ##### wtf.???? = rdf.underline #### redundant attribute, set on the fly when writing wtf.family = rdf.familywtf.charset = rdf.character_set = ## protection#wtp = wtxf.protectionrdp = rdxf.protectionwtp.cell_locked = rdp.cell_lockedwtp.formula_hidden = rdp.formula_hidden## border(s) (rename ????)#wtb = wtxf.bordersrdb = rdxf.borderwtb.left = rdb.left_line_stylewtb.right = rdb.right_line_stylewtb.top = rdb.top_line_stylewtb.bottom = rdb.bottom_line_stylewtb.diag = rdb.diag_line_stylewtb.left_colour = rdb.left_colour_indexwtb.right_colour = rdb.right_colour_indexwtb.top_colour = rdb.top_colour_indexwtb.bottom_colour = rdb.bottom_colour_indexwtb.diag_colour = rdb.diag_colour_indexwtb.need_diag1 = rdb.diag_downwtb.need_diag2 = rdb.diag_up## background / pattern (rename???)#wtpat = wtxf.patternrdbg = rdxf.backgroundwtpat.pattern = rdbg.fill_patternwtpat.pattern_fore_colour = rdbg.pattern_colour_indexwtpat.pattern_back_colour = rdbg.background_colour_index## alignment#wta = wtxf.alignmentrda = rdxf.alignmentwta.horz = rda.hor_alignwta.vert = rda.vert_alignwta.dire = rda.text_direction# wta.orie # orientation doesn't occur in BIFF8! Superceded by rotation ("rota").wta.rota = rda.rotationwta.wrap = rda.text_wrappedwta.shri = rda.shrink_to_fitwta.inde = rda.indent_level# wta.merg = ????#return wtxfdef fill_value(position_sheet={},cell_value=""):"""replace the position({{varible}}) of the cellvalue with the varible's value"""if cell_value:m=p.search(cell_value)#查询是否存在{{?p<name>}}while m:var_name=m.groups()[0]print "var_name:",var_nametry:value = position_sheet.get(var_name) #读取第一个参数的值except Exception, e:error= u"模板文件:%(source_file)s的第%(index)s个sheet中的参数{{%(var_name)s}}在position中找不到对应的key" % \{"source_file":os.path.split(source_file)[1],"index":i+1,"var_name":var_name}print errorreturn dict(error=error,e=e)if value and not isinstance(value, unicode):value= value.decode("utf-8")print 'value', valueif not value:breakcell_value=p.sub(value,cell_value,count=1)#用这个值替换cell_value中的该{{参数}}m=p.search(cell_value)#检查是否还有参数return cell_valuedef excel_create(source_file="",position=[{}],target_file=""):'''根据source_file传入的excel模板文件路径，使用position中map对模板中的{{varible}}中的varible字段进行数据填充，生成新的文件放入file_pathsource_file:模板文件名;非空路径为：/public/excel/source/<name>.xls,空值默认模板：/public/excel/source/module.xls,position:[{key:value,key1:value1,key2:value2,...},#模板excel的sheet[0]内的参数map {key:value,key1:value1,key2:value2,...},#模板excel的sheet[1]内的参数map{key:value,key1:value1,key2:value2,...},#模板excel的sheet[...]内的参数map]file_path：生成的文件路径以及文件名;空值为默认路径：public/excel/target/result.xls '''warning=""if target_file:result_file=os.path.join(conf.APP_DIR, "public/excel/target", target_file) else:result_file=target_file_moldif source_file:source_file=os.path.join(conf.APP_DIR, "public/excel/source", source_file) else:source_file=source_file_moldtry:rb = open_workbook(source_file,on_demand=True,formatting_info=True) except:error= u"the (%s) is not a correct path!" % source_filereturn dict(error=error)wb = copy(rb)shxrange = range(rb.nsheets)while len(shxrange)>len(position):warning= u"the length of position's map is lesser than the number of the module Excel's sheets!"position.append({})for i in shxrange:ws = wb.get_sheet(i)sheet = rb.sheet_by_index(i)for rowx in range(0,sheet.nrows):for colx in range(0,sheet.ncols):#get the cell valuecellvalue=sheet.cell_value(rowx,colx)#get the cell typecelltype=sheet.cell_type(rowx,colx)if celltype == xlrd.XL_CELL_DATE:try:showval = xlrd.xldate_as_tuple(cellvalue, rb.datemode)except xlrd.XLDateError:e1, e2 = sys.exc_info()[:2]showval = "%s:%s" % (e1.__name__, e2)elif celltype == xlrd.XL_CELL_ERROR:showval = xlrd.error_text_from_code.get(cellvalue, 'Unknown error code 0x%02x' % cellvalue)else:showval = cellvalue#get stylexf=rb.xf_list[sheet.cell_xf_index(rowx,colx)]wtxf=copy_xf(rb,xf)#fill value(replace the varible in the cell_value)if showval:m=p.search(showval)#查询是否存在{{?p<name>}}while m:var_name=m.groups()[0]try:if var_name in position[i].keys():value = position[i].get(var_name,"") #读取第一个参数的值print "begin||",var_name,":",value,chardet.detect(value)# assert value is Trueif value:if not isinstance(value, unicode):value= value.decode("utf-8")else:value=""print "decode||",var_name,":",valueprint "-----------------------------------"else:raise Exceptionexcept Exception, e:error= u"the param:{{%(var_name)s}} in sheet(%(index)s) of the template:%(source_file)s can't be found in position's keys list" % \{"source_file":os.path.split(source_file)[1],"index":i+1,"var_name":var_name}print error,ereturn dict(error=error.encode('utf-8'))showval=p.sub(value,showval,count=1)#用这个值替换cell_value 中的该{{参数}}m=p.search(showval)#检查是否还有参数ws.write(rowx,colx,showval,wtxf)wb.save(result_file)return dict(target_file=os.path.join("/public/excel/target",target_file),warning=warning)def _test():#open the excel filerb=open_workbook("test1.xls",on_demand=True,formatting_info=True)for attr in ("biff_version","codepage","countries","encoding","colour_map","font_list","format_list","format_map","user_name","nsheets"):print "%s=%s" %(attr, rb.__getattribute__(attr))#show the loaded status for sheetsfor sheet_name in rb.sheet_names():print "%s loaded = %s" %(sheet_name, rb.sheet_loaded(sheet_name))#get the sheet1sheet=rb.sheet_by_index(0)print "%s has %d rows, %d cols" %(, sheet.nrows, sheet.ncols)print "Shoe the file content"for rowx in range(0,sheet.nrows):for colx in range(0,sheet.ncols):#get the cell valuecellvalue=sheet.cell_value(rowx,colx)#get the cell typecelltype=sheet.cell_type(rowx,colx)#init the showable valueshowvalue=""if celltype == xlrd.XL_CELL_DATE:try:showval = xlrd.xldate_as_tuple(cellvalue, rb.datemode)except xlrd.XLDateError:e1, e2 = sys.exc_info()[:2]showval = "%s:%s" % (e1.__name__, e2)elif celltype == xlrd.XL_CELL_ERROR:showval = xlrd.error_text_from_code.get(cellvalue, 'Unknown error code 0x%02x' % cellvalue)else:showval = cellvalue#get stylexf=rb.xf_list[sheet.cell_xf_index(rowx,colx)]#print rb.colour_map[xf.background.background_colour_index]#display the cell forecolorcolor=rb.colour_map[xf.background.pattern_colour_index]#show the contentprint ("[%s]=%s,[color]=%s" % (cellnameabs(rowx,colx),showval,color))#show the loaded status for sheets after load sheet1for sheet_name in rb.sheet_names():print "%s loaded = %s" %(sheet_name, rb.sheet_loaded(sheet_name))##################change the excel and write it out##################get the xlwt object from rb objectwb = copy(rb)#get the original excel cell stylerbxf=rb.xf_list[sheet.cell_xf_index(0,0)]#copy the xlrd style to xlwt stylewtrf=copyXF(rb,rbxf)#change an attribute of this style, the color index can refer to VBA document wtrf.pattern.pattern_fore_colour=15#set the new value and new style#wb.get_sheet(0).write(0,0,'changed!')wb.get_sheet(0).write(0,0,'changed!',wtrf)#output to filewb.save('test2.xls')def test(source_file="",position=[{}],target_file=""):'''根据source_file传入的excel模板文件路径，使用position中map对模板中的{{varible}}中的varible字段进行数据填充，生成新的文件放入file_pathsource_file:模板文件名;非空路径为：/public/excel/source/<name>.xls,空值默认模板：/public/excel/source/module.xls,position:[{key:value,key1:value1,key2:value2,...},#模板excel的sheet[0]内的参数map {key:value,key1:value1,key2:value2,...},#模板excel的sheet[1]内的参数map{key:value,key1:value1,key2:value2,...},#模板excel的sheet[...]内的参数map]file_path：生成的文件路径以及文件名;空值为默认路径：public/excel/target/result.xls '''print positionif target_file:target_file=os.path.join(conf.APP_DIR, "public/excel/target", target_file) else:target_file=target_file_moldif source_file:source_file=os.path.join(conf.APP_DIR, "public/excel/source", source_file) else:source_file=source_file_moldtry:rb = open_workbook(source_file,formatting_info=True)except:print "(%s)路径名不正确" % source_fileerror= "(%s)路径名不正确" % source_filereturn dict(error=error)wb = copy(rb)shxrange = range(rb.nsheets)while len(shxrange)>len(position):print '警告：position的map数小于sheets个数'position.append({})for index in shxrange:ws = wb.get_sheet(index)sh = rb.sheet_by_index(index)nrows = sh.nrowsncols = sh.ncolsfor i in range(0,nrows):for j in range(0,ncols):cell_value=sh.cell(i,j).valuecell_type=sh.cell(i,j).typem=p.search(cell_value)#查询是否存在{{?p<name>}}while m:var_name=m.groups()[0]print "var_name:",var_nametry:value = position[index].get(var_name) #读取第一个参数的值if value and not isinstance(value, unicode):value= value.decode("utf-8")except Exception, e:error= u"模板文件:%(source_file)s的第%(index)s个sheet中的参数{{%(var_name)s}}在position中找不到对应的key" % \{"source_file":os.path.split(source_file)[1],"index":index+1,"var_name":var_name}print errorreturn dict(error=error)print 'value', valueif not value:breakcell_value=p.sub(value,cell_value,count=1)#用这个值替换cell_value中的该{{参数}}m=p.search(cell_value)#检查是否还有参数#把处理后的cell_value填入结果单元格try:ws.write(i, j, cell_value)except:error= u"excel写入失败"return dict(error=error)wb.save(target_file)return dict(target_file=target_file)def test2():pass# Step 1: Create an input file for the demodef create_input_file():wtbook = xlwt.Workbook()wtsheet = wtbook.add_sheet(u'First')colours = 'white black red green blue pink turquoise yellow'.split()fancy_styles = [xlwt.easyxf('font: name Times New Roman, italic on;''pattern: pattern solid, fore_colour %s;'% colour) for colour in colours]for rowx in xrange(8):wtsheet.write(rowx, 0, rowx)wtsheet.write(rowx, 1, colours[rowx], fancy_styles[rowx]) wtbook.save('demo_copy2_in.xls')# Step 2: Copy the file, changing data content# ('pink' -> 'MAGENTA', 'turquoise' -> 'CYAN')# without changing the formattingfrom xlutils.filter import process,XLRDReader,XLWTWriter# Patch: add this function to the end of xlutils/copy.pydef copy2(wb):w = XLWTWriter()process(XLRDReader(wb,'unknown.xls'),w)return w.output[0][1], w.style_listdef update_content():rdbook = xlrd.open_workbook('demo_copy2_in.xls', formatting_info=True) sheetx = 0rdsheet = rdbook.sheet_by_index(sheetx)wtbook, style_list = copy2(rdbook)wtsheet = wtbook.get_sheet(sheetx)fixups = [(5, 1, 'MAGENTA'), (6, 1, 'CYAN')]for rowx, colx, value in fixups:xf_index = rdsheet.cell_xf_index(rowx, colx)wtsheet.write(rowx, colx, value, style_list[xf_index])wtbook.save('demo_copy2_out.xls')。