[Baypiggies] adding color to excel files programmatically

[James Nicholson]

Hey Vikram,

Tested the below code, it works. Output file is attached.

Let me know if you have any questions, hopefully you can adapt this to what
you're trying to do.

Also, check out the python-excel
pdf<http://www.simplistix.co.uk/presentations/python-excel.pdf>,
it's an excellent resource.

--------------------------------------------------------------------------------------------------------------------------
from xlwt import Workbook, easyxf

book = Workbook()

sheet1 = book.add_sheet('sheet1')

cat_cell = easyxf('pattern: pattern solid, fore_colour red')

rows = [['cat',10,20,30],['cat',50,60,70],['dog',20,30,40]]

for x, row in enumerate(rows):
    if row[0] == 'cat':
        for y, value in enumerate(row):
            sheet1.write(x, y, value, cat_cell)
    else:
        for y, value in enumerate(row):
            sheet1.write(x, y, value)

book.save('cat.xls')
--------------------------------------------------------------------------------------------------------------------------

James Nicholson
nicholsonjf.com


On Wed, Mar 26, 2014 at 9:11 PM, Vikram K <vikthirtyfive at gmail.com> wrote:

> I had already worked with xlrd in the past (while continuing to use csv
> module for writing a file) so decided to take a look at xlwt. I found an
> example on the net which works fine:
>
> import xlwt
> book = xlwt.Workbook()
> xlwt.add_palette_colour("custom_color",0x21)
>
> book.set_colour_RGB(0x21,251,228,228)
>
> sheet1 = book.add_sheet('Sheet1')
>
> style = xlwt.easyxf('pattern:pattern solid, fore_colour 0x21')
> sheet1.write(0,0,'Some text', style)
> book.save('test.xls')
>
> The above code generates an excel file with the top-left cell having the
> value 'Some text' which is in color. Now, suppose i have a nested list like
> this:
>
> >>> x = [['cat',10,20,30],['cat',50,60,70],['dog',20,30,40]]
> >>> x
> [['cat', 10, 20, 30], ['cat', 50, 60, 70], ['dog', 20, 30, 40]]
> >>> for i in x:
> print i
>
> ['cat', 10, 20, 30]
> ['cat', 50, 60, 70]
> ['dog', 20, 30, 40]
> >>>
>
> I wish to write out the nested list x to an excel file using xlwt in such
> a way that the rows which start with 'cat' are colored while the row
> starting with 'dog' are in a different color. Alternatively, the row
> starting with 'cat' can be colored, while the row starting with 'dog' can
> be left as is without any color. If anyone has worked on something like
> this, please help. Thank you.
>
>
> On Wed, Mar 26, 2014 at 4:12 PM, Martin Falatic <martin at falatic.com>wrote:
>
>> If you're generating a new excel file, xlwt works... some ideas:
>>
>>
>> http://stackoverflow.com/questions/15649482/how-to-set-color-of-text-using-xlwt
>>
>>
>> http://stackoverflow.com/questions/7746837/python-xlwt-set-custom-background-colour-of-a-cell
>>
>>
>> http://stackoverflow.com/questions/2981293/python-excel-xlwt-colouring-every-second-row?rq=1
>>
>>  - Marty
>>
>>
>> On Wed, March 26, 2014 12:10, Vikram K wrote:
>> > Could someone kindly tell me how i can add color programmatically to
>> > specific rows in an excel file. Thank you.
>> > _______________________________________________
>> > Baypiggies mailing list
>> > Baypiggies at python.org
>> > To change your subscription options or unsubscribe:
>> > https://mail.python.org/mailman/listinfo/baypiggies
>>
>>
>>
>
> _______________________________________________
> Baypiggies mailing list
> Baypiggies at python.org
> To change your subscription options or unsubscribe:
> https://mail.python.org/mailman/listinfo/baypiggies
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/baypiggies/attachments/20140326/06cbcf96/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: cat.xls
Type: application/vnd.ms-excel
Size: 5632 bytes
Desc: not available
URL: <http://mail.python.org/pipermail/baypiggies/attachments/20140326/06cbcf96/attachment.xls>