我正在编写一个使用xlrd库从Excel表格读取数据的Python脚本。工作表中有一些单元格被用不同的颜色突出显示,我想要识别这些单元格的颜色代码。是否有任何方法可以做到这一点?如果能给出一个示例就更好了。
以下是一种处理这个问题的方式:
import xlrd
book = xlrd.open_workbook("sample.xls", formatting_info=True)
sheets = book.sheet_names()
print "sheets are:", sheets
for index, sh in enumerate(sheets):
sheet = book.sheet_by_index(index)
print "Sheet:", sheet.name
rows, cols = sheet.nrows, sheet.ncols
print "Number of rows: %s Number of cols: %s" % (rows, cols)
for row in range(rows):
for col in range(cols):
print "row, col is:", row+1, col+1,
thecell = sheet.cell(row, col)
# could get 'dump', 'value', 'xf_index'
print thecell.value,
xfx = sheet.cell_xf_index(row, col)
xf = book.xf_list[xfx]
bgx = xf.background.pattern_colour_index
print bgx
在Python-Excel Google Group上可以找到更多信息。
import openpyxl
from openpyxl import load_workbook
excel_file = 'color_codes.xlsx'
wb = load_workbook(excel_file, data_only = True)
sh = wb['Sheet1']
color_in_hex = sh['A2'].fill.start_color.index # this gives you Hexadecimal value of the color
print ('HEX =',color_in_hex)
print('RGB =', tuple(int(color_in_hex[i:i+2], 16) for i in (0, 2, 4))) # Color in RGB
color_in_hex:str = f“#{str(color_in_hex).zfill(6)}”
对齐它,以获得类似于#000007
的东西,但是颜色仍然不正确。 - George Zorikov该函数返回单元格背景的rgb值元组。
def getBGColor(book, sheet, row, col):
xfx = sheet.cell_xf_index(row, col)
xf = book.xf_list[xfx]
bgx = xf.background.pattern_colour_index
pattern_colour = book.colour_map[bgx]
#Actually, despite the name, the background colour is not the background colour.
#background_colour_index = xf.background.background_colour_index
#background_colour = book.colour_map[background_colour_index]
return pattern_colour
open_workbook(..., formatting_info=True)
,因此无法用于xlsx文件。 - Echo9k# say you have an Excel file called workbook
and inside it there is a worksheet called worksheet
# inside that worksheet is cell say C1 that is highlighted
and you want to get the color value of the highlighted cell
# Trying the openpyxl package
import openpyxl
# Importing all modules from the openpyxl package
from openpyxl import *
# reading ev2 excel workbook through load_workbook function
workbook = load_workbook("C:PATHTO/workbook.xlsx")
# Accessing existing worksheets
worksheet = workbook ["worksheet"]
## METHOD1 ##
# Getting the highlight property of the cell
highlight=str(worksheet ['C1'].fill)
# Printing out the value of the color
index=int(highlight.find("rgb='"))
print(highlight[index+5:index+13])
## METHOD 2 ##
print(worksheet ['C11'].fill.start_color.index)
repr(value)
。您可能已经将默认格式应用于第4列;sheet.colinfo_map [4] .xf_index
给您什么? - John Machin