Python从excel模板中替换字符串并生成新文件

普通替换，不保留样式

# Open Excel file from a user imput
import xlrd, xlwt
filename = raw_input("Enter Excel file name with extension (.xls) and path")
oldbook = xlrd.open_workbook(filename)
newbook = xlwt.Workbook()
# For all the sheets in the workbook
for sheetname in oldbook.sheet_names():
    oldsheet = oldbook.sheet_by_name(sheetname)
    newsheet = newbook.add_sheet(sheetname)
    # For all the rows and all the columns in an excel
    for ii in range(oldsheet.nrows):
        for jj in range(oldsheet.ncols):
            # Replace
            CellString=str(oldsheet.cell(ii, jj).Value)
            CellString=CellString.replace("%", "Perc")
            CellString=CellString.replace(" ", "_")
            newsheet.write(ii, jj, CellString)
# Save the file in a desired location with the desired name
savelocation = raw_input("Enter a new path and file name with extension (.xls) to save the new Excel spread sheet ")
newbook.save(savelocation)

运用xlutils可保留样式

import xlrd
from xlutils.filter import process, XLRDReader, XLWTWriter

rb = xlrd.open_workbook('excel.xls', formatting_info=True)

# 参考xlutils.copy库内的用法 参考xlutils.filter内的参数定义style_list
w = XLWTWriter()
process(XLRDReader(rb, 'unknown.xls'), w)
wb = w.output[0][1]
style_list = w.style_list

peple = ("张三", "李四", "王五")

for i in range(len(peple)):
    for j in range(i):
        left = peple[i]
        right = peple[j]
        for n, sheet in enumerate(rb.sheets()):
            sheet2 = wb.get_sheet(n)
            for r in range(sheet.nrows):
                for c, cell in enumerate(sheet.row_values(r)):
                    style = style_list[sheet.cell_xf_index(r, c)]
                    val = sheet.cell_value(r, c)
                    if val == "{left}":
                        val = left
                    elif val == "{right}":
                        val = right
                    sheet2.write(r, c, val, style)
        # print(str(i) + str(j)+left+right)
        wb.save('比赛(' + left + "vs" + right + ').xls')