使用python从Excel格式化文本字符串 [英] Conditionally formatting text strings from Excel using python

问题描述

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

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.

例如，如果单元格包含单词deleted，则将其填充为黑色，并将文本绘制为白色。
如果单元格以.pf结尾，则绘制单元格为红色。

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.

我从几年前发现了类似的问题，建议如下： p>

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

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

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.

if数字值的声明似乎有效，但是我又要寻找一种格式化包含某些字符串的单元格的方法，谢谢！

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

推荐答案

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

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

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('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')

---

使用上述示例，未修改的单元格应具有原始单元格格式化完整。

---

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

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

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:

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 。 Ergo，你看到上面 input_cell.value.find（'deleted'）> = 0 来评估是否存在子字符串已删除。对于 .pf 比较，我使用 rfind 以及Python中的一些名为创建。

---

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.

这篇关于使用python从Excel格式化文本字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！