input.xls
打开时保留原始格式:
from xlrd import open_workbook
input_wb = open_workbook('input.xls', formatting_info=True)
基于此模板创建一个新的工作簿:
from xlutils.copy import copy as copy_workbook
output_wb = copy_workbook(input_wb)
定义一些新的单元格样式:
from xlwt import easyxf
red_background = easyxf("pattern: pattern solid, fore_color red;")
black_with_white_font = easyxf('pattern: pattern solid, fore_color black; font: color-index white, bold on;")
评估和修改单元格:
input_ws = input_wb.sheet_by_name('StackOverflow')
output_ws = output_wb.get_sheet(0)
for rindex in range(0, input_ws.nrows):
for cindex in range(0, input_ws.ncols):
input_cell = input_ws.cell(rindex, cindex)
if input_cell.value[ input_cell.value.rfind('.'): ] == 'pf':
output_ws.write(rindex, cindex, input_cell.value, red_background)
elif input_cell.value.find('deleted') >= 0:
output_ws.write(rindex, cindex, input_cell.value, black_with_white_font)
else:
pass # we don't need to modify it
保存新工作簿
output_wb.save('output.xls')
使用上面的示例,未修改的单元格应保留其原始格式。
如果您需要更改单元格内容并希望保留原始格式(即不使用自定义easyxf
实例),则可以使用以下代码段:
def changeCell(worksheet, row, col, text):
""" Changes a worksheet cell text while preserving formatting """
# Adapted from https://stackoverflow.com/a/7686555/1545769
prevIoUsCell = worksheet._Worksheet__rows.get(row)._Row__cells.get(col)
worksheet.write(row, col, text)
newCell = worksheet._Worksheet__rows.get(row)._Row__cells.get(col)
newCell.xf_idx = prevIoUsCell.xf_idx
# ...
changeCell(worksheet_instance, 155, 2, "New Value")
对于比较,可以使用字符串方法find
和rfind
(从右侧搜索)。它们返回字符串中子字符串位置的索引。-1
如果未找到子字符串,它们将返回。如此,您会在上方看到`input_cell.value.find(‘deleted’)