使用python有条件地从Excel格式化文本字符串

I would like to format a spreadsheet (xls or xlsx) so that any cells containing a word or ending with a certain string are formatted by filling the background with a specific color.

我想格式化电子表格（xls或xlsx），以便通过用特定颜色填充背景来格式化包含单词或以某个字符串结尾的任何单元格。

For example, if the cell contains the word 'deleted', fill it black and paint the text white. if the cell ends with '.pf', paint the cell red.

例如，如果单元格包含单词“已删除”，请将其填充为黑色并将文本绘制为白色。如果单元格以'.pf'结尾，则将单元格绘制为红色。

I found a similar question from several years ago that suggested the following:

几年前我发现了类似的问题，提出以下建议：

import xlrd 
import xlutils.copy 

inBook = xlrd.open_workbook('input.xls', formatting_info=True) 
outBook = xlutils.copy.copy(inBook) 

def _getOutCell(outSheet, colIndex, rowIndex): 
    """ HACK: Extract the internal xlwt cell representation. """ 
    row = outSheet._Worksheet__rows.get(rowIndex) 
    if not row: return None 
    cell = row._Row__cells.get(colIndex) 
    return cell 

def setOutCell(outSheet, col, row, value): 
    """ Change cell value without changing formatting. """ 
    # HACK to retain cell style. 
    previousCell = _getOutCell(outSheet, col, row) 
    # END HACK, PART I 
    outSheet.write(row, col, value) 
    # HACK, PART II 

    if previousCell: 
        newCell = _getOutCell(outSheet, col, row) 
    if newCell:
        newCell.xf_idx = previousCell.xf_idx 
    # END HACK 


outSheet = outBook.get_sheet(0) 
setOutCell(outSheet, 5, 5, 'Test') 
outBook.save('output.xls')

While this does copy the values from input.xls to output.xls, this does not seem to transfer the formatting (the test values from input.xls are no longer formatted when opening output.xls, nor are the conditional formatting rules present under "manage rules" in excel.

虽然这会将input.xls中的值复制到output.xls，但这似乎不会传输格式（打开output.xls时不再格式化input.xls中的测试值，条件格式规则也不会出现在“在excel中管理规则。

"if" statements for number values seem to work, but again, I am looking for a way to format cells containing certain strings. thanks!

“if”数字值的语句似乎有效，但同样，我正在寻找一种格式化包含某些字符串的单元格的方法。谢谢！

1 个解决方案

#1

Preserve the original input.xls formatting when you open it:

打开时保留原始input.xls格式：

from xlrd import open_workbook

input_wb = open_workbook('input.xls', formatting_info=True)

Create a new workbook based on this template:

基于此模板创建新工作簿：

from xlutils.copy import copy as copy_workbook

output_wb = copy_workbook(input_wb)

Define some new cell styles:

定义一些新的单元格样式：

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;")

Evaluate and modify your cells:

评估和修改您的单元格：

input_ws = input_wb.sheet_by_name('*')
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

Save your new workbook

保存新工作簿

output_wb.save('output.xls')

Using the above example, unmodified cells should have their original formatting intact.

使用上面的示例，未修改的单元格应保持原始格式不变。

Should you need to alter the cell content AND would like to preserve the original formatting (i.e. NOT use your custom easyxf instance), you may use this snippet:

如果您需要更改单元格内容并希望保留原始格式（即不使用您的自定义easyxf实例），您可以使用以下代码段：

def changeCell(worksheet, row, col, text):
    """ Changes a worksheet cell text while preserving formatting """
    # Adapted from https://*.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")

For the comparisons, you can use the string methods find and rfind (which searches from the right). They return the index of the position of the substring within the string. They return -1 if the substring is not found. Ergo, you see above input_cell.value.find('deleted') >= 0 to evaluate whether or not the substring 'deleted' exists. For the .pf comparison, I used rfind as well as something in Python called slicing.

对于比较，您可以使用字符串方法find和rfind（从右侧搜索）。它们返回字符串中子字符串位置的索引。如果未找到子字符串，则返回-1。你会看到上面的input_cell.value.find（'deleted'）> = 0来评估子字符串'deleted'是否存在。对于.pf比较，我使用rfind以及Python中称为切片的东西。

#1