python复制和编辑excel

#coding=utf-8

import os

import os.path

import sys

from xlrd import open_workbook

from xlutils.copy import copy

from g315.config import conf

import chardet

import re

p=https://www.360docs.net/doc/519335788.html,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 xlrd

import xlwt

from xlrd import open_workbook,cellnameabs

from xlutils.copy import copy

def 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.font

rdf = rdbook.font_list[rdxf.font_index]

wtf.height = rdf.height

wtf.italic = rdf.italic

wtf.struck_out = rdf.struck_out

wtf.outline = rdf.outline

wtf.shadow = rdf.outline

wtf.colour_index = rdf.colour_index

wtf.bold = rdf.bold #### This attribute is redundant, should be driven by weight wtf._weight = rdf.weight #### Why "private"?

wtf.escapement = rdf.escapement

wtf.underline = rdf.underline_type ####

# wtf.???? = rdf.underline #### redundant attribute, set on the fly when writing wtf.family = rdf.family

wtf.charset = rdf.character_set

https://www.360docs.net/doc/519335788.html, = https://www.360docs.net/doc/519335788.html,

#

# protection

#

wtp = wtxf.protection

rdp = rdxf.protection

wtp.cell_locked = rdp.cell_locked

wtp.formula_hidden = rdp.formula_hidden

#

# border(s) (rename ????)

#

wtb = wtxf.borders

rdb = rdxf.border

wtb.left = rdb.left_line_style

wtb.right = rdb.right_line_style

wtb.top = rdb.top_line_style

wtb.bottom = rdb.bottom_line_style

wtb.diag = rdb.diag_line_style

wtb.left_colour = rdb.left_colour_index

wtb.right_colour = rdb.right_colour_index

wtb.top_colour = rdb.top_colour_index

wtb.bottom_colour = rdb.bottom_colour_index

wtb.diag_colour = rdb.diag_colour_index

wtb.need_diag1 = rdb.diag_down

wtb.need_diag2 = rdb.diag_up

#

# background / pattern (rename???)

#

wtpat = wtxf.pattern

rdbg = rdxf.background

wtpat.pattern = rdbg.fill_pattern

wtpat.pattern_fore_colour = rdbg.pattern_colour_index

wtpat.pattern_back_colour = rdbg.background_colour_index

#

# alignment

#

wta = wtxf.alignment

rda = rdxf.alignment

wta.horz = rda.hor_align

wta.vert = rda.vert_align

wta.dire = rda.text_direction

# wta.orie # orientation doesn't occur in BIFF8! Superceded by rotation ("rota").

wta.rota = rda.rotation

wta.wrap = rda.text_wrapped

wta.shri = rda.shrink_to_fit

wta.inde = rda.indent_level

# wta.merg = ????

#

return wtxf

def 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}}

while m:

var_name=m.groups()[0]

print "var_name:",var_name

try:

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 error

return dict(error=error,e=e)

if value and not isinstance(value, unicode):

value= value.decode("utf-8")

print 'value', value

if not value:

break

cell_value=p.sub(value,cell_value,count=1)#用这个值替换cell_value中的该{{参数}}

m=p.search(cell_value)#检查是否还有参数

return cell_value

def excel_create(source_file="",position=[{}],target_file=""):

'''

根据source_file传入的excel模板文件路径，使用position中map对模板中的{{varible}}中的varible字段进行数据填充，生成新的文件放入file_path

source_file:模板文件名;非空路径为：/public/excel/source/.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_mold

if source_file:

source_file=os.path.join(conf.APP_DIR, "public/excel/source", source_file) else:

source_file=source_file_mold

try:

rb = open_workbook(source_file,on_demand=True,formatting_info=True) except:

error= u"the (%s) is not a correct path!" % source_file

return 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 value

cellvalue=sheet.cell_value(rowx,colx)

#get the cell type

celltype=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 style

xf=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}}

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 True

if value:

if not isinstance(value, unicode):

value= value.decode("utf-8")

else:

value=""

print "decode||",var_name,":",value

print "-----------------------------------"

else:

raise Exception

except 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,e

return 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 file

rb=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 sheets

for sheet_name in rb.sheet_names():

print "%s loaded = %s" %(sheet_name, rb.sheet_loaded(sheet_name))

#get the sheet1

sheet=rb.sheet_by_index(0)

print "%s has %d rows, %d cols" %(https://www.360docs.net/doc/519335788.html,, 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 value

cellvalue=sheet.cell_value(rowx,colx)

#get the cell type

celltype=sheet.cell_type(rowx,colx)

#init the showable value

showvalue=""

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 style

xf=rb.xf_list[sheet.cell_xf_index(rowx,colx)]

#print rb.colour_map[xf.background.background_colour_index]

#display the cell forecolor

color=rb.colour_map[xf.background.pattern_colour_index]

#show the content

print ("[%s]=%s,[color]=%s" % (cellnameabs(rowx,colx),showval,color))

#show the loaded status for sheets after load sheet1

for 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 object

wb = copy(rb)

#get the original excel cell style

rbxf=rb.xf_list[sheet.cell_xf_index(0,0)]

#copy the xlrd style to xlwt style

wtrf=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 file

wb.save('test2.xls')

def test(source_file="",position=[{}],target_file=""):

'''

根据source_file传入的excel模板文件路径，使用position中map对模板中的{{varible}}中的varible字段进行数据填充，生成新的文件放入file_path

source_file:模板文件名;非空路径为：/public/excel/source/.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 position

if target_file:

target_file=os.path.join(conf.APP_DIR, "public/excel/target", target_file) else:

target_file=target_file_mold

if source_file:

source_file=os.path.join(conf.APP_DIR, "public/excel/source", source_file) else:

source_file=source_file_mold

try:

rb = open_workbook(source_file,formatting_info=True)

except:

print "(%s)路径名不正确" % source_file

error= "(%s)路径名不正确" % source_file

return 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.nrows

ncols = sh.ncols

for i in range(0,nrows):

for j in range(0,ncols):

cell_value=sh.cell(i,j).value

cell_type=sh.cell(i,j).type

m=p.search(cell_value)#查询是否存在{{?p}}

while m:

var_name=m.groups()[0]

print "var_name:",var_name

try:

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 error

return dict(error=error)

print 'value', value

if not value:

break

cell_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 demo

def 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 formatting

from xlutils.filter import process,XLRDReader,XLWTWriter

# Patch: add this function to the end of xlutils/copy.py

def copy2(wb):

w = XLWTWriter()

process(

XLRDReader(wb,'unknown.xls'),

w

)

return w.output[0][1], w.style_list

def update_content():

rdbook = xlrd.open_workbook('demo_copy2_in.xls', formatting_info=True) sheetx = 0

rdsheet = 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')