How to change background color of excel cell with

问题:

I use xlwt Python library to write data in excel workbook.
And now I have some problems with adding background color to excel cell.

For example I have next color in RGB(10,20,30), what is the easiest way to do this? Is there any way to set this color to cell?

I found only this post which similar with my problem.

回答1:

In this example, I have shown how to set background color for cells, you can run it for result:

from xlwt import Workbook
import xlwt
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
book.add_sheet('Sheet 2')
for i in range(0, 100):
    st = xlwt.easyxf('pattern: pattern solid;')
    st.pattern.pattern_fore_colour = i
    sheet1.write(i % 24, i / 24, 'Test text',st)
book.save('simple.xls')

回答2:

i had this problem and I did a lot of searching

at last i found a proper and good solution in : source of solution

it work very good!

just add this class to your project and set excel colors :

class ColorMatcher(object):
"""
the source is in : http://www.archivum.info/[e-mail]/2012-09/00014/Re-(pyxl)-Re-Background-with-any-rgb-value.html

Prior to Excel 2007, Excel only had color
indexes, and that's all that xlwt supports.  Maybe this will help,
though.  It use a ColorMatcher that takes an RGB input and tries to
return the closest matching Excel color index:
"""

def __init__(self):
    self.reset()

def reset(self):
    self.unused_colors = set(self.xlwt_colors)
    # Never use black.
    self.unused_colors.discard((0, 0, 0))

#Culled from a table at http://www.mvps.org/dmcritchie/excel/colors.htm
xlwt_colors=[
    (0,0,0), (255,255,255), (255,0,0), (0,255,0), (0,0,255), (255,255,0),
    (255,0,255), (0,255,255), (0,0,0), (255,255,255), (255,0,0), (0,255,0),
    (0,0,255), (255,255,0), (255,0,255), (0,255,255), (128,0,0), (0,128,0),
    (0,0,128), (128,128,0), (128,0,128), (0,128,128), (192,192,192),
    (128,128,128), (153,153,255), (153,51,102), (255,255,204),
    (204,255,255), (102,0,102), (255,128,128), (0,102,204), (204,204,255),
    (0,0,128), (255,0,255), (255,255,0), (0,255,255), (128,0,128),
    (128,0,0), (0,128,128), (0,0,255), (0,204,255), (204,255,255),
    (204,255,204), (255,255,153), (153,204,255), (255,153,204),
    (204,153,255), (255,204,153), (51,102,255), (51,204,204), (153,204,0),
    (255,204,0), (255,153,0), (255,102,0), (102,102,153), (150,150,150),
    (0,51,102), (51,153,102), (0,51,0), (51,51,0), (153,51,0), (153,51,102),
    (51,51,153), (51,51,51)
]

@staticmethod
def color_distance(rgb1, rgb2):
    # Adapted from Colour metric by Thiadmer Riemersma,
    # http://www.compuphase.com/cmetric.htm
    rmean = (rgb1[0] + rgb2[0]) / 2
    r = rgb1[0] - rgb2[0]
    g = rgb1[1] - rgb2[1]
    b = rgb1[2] - rgb2[2]
    return (((512 + rmean) * r * r) / 256) + 4 * g * g\
    + (((767 - rmean) * b * b) / 256)

def match_color_index(self, color):
    """Takes an "R,G,B" string or wx.Color and returns a matching xlwt
    color.
    """
    if isinstance(color, int):
        return color
    if color:
        if isinstance(color, basestring):
            rgb = map(int, color.split(','))
        else:
            rgb = color.Get()
        distances = [self.color_distance(rgb, x) for x in self.xlwt_colors]
        result = distances.index(min(distances))
        self.unused_colors.discard(self.xlwt_colors[result])
        return result

def get_unused_color(self):
    """Returns an xlwt color index that has not been previously returned by
    this instance.  Attempts to maximize the distance between the color and
    all previously used colors.
    """
    if not self.unused_colors:
        # If we somehow run out of colors, reset the color matcher.
        self.reset()
    used_colors = [c for c in self.xlwt_colors if c not in
                                                  self.unused_colors]
    result_color = max(self.unused_colors,
        key=lambda c: min(self.color_distance(c, c2)
        for c2 in used_colors))
    result_index = self.xlwt_colors.index(result_color)
    self.unused_colors.discard(result_color)
    return result_index

the source of this code is : http://www.archivum.info/[e-mail]/2012-09/00014/Re-%28pyxl%29-Re-Background-with-any-rgb-value.htmle

回答3:

GREEN_TABLE_HEADER = easyxf(
                 'font: bold 1, name Tahoma, height 160;'
                 'align: vertical center, horizontal center, wrap on;'
                 'borders: left thin, right thin, top thin, bottom thin;'
                 'pattern: pattern solid, pattern_fore_colour green, pattern_back_colour green'
                 )
overviewSheet.row(rowCursor).write(col_0, 'Issue', GREEN_TABLE_HEADER)

回答4:

I recommend to use XlsxWriter module, has awesome features too http://xlsxwriter.readthedocs.io/