[Baypiggies] adding color to excel files programmatically
Vikram K
vikthirtyfive at gmail.com
Mon Mar 31 23:28:00 CEST 2014
Got stuck again. Please help. I only wanted to color the values of the
nested list 'data' after they have been printed to an excel sheet based on
whether the second last element in each row of the nested list data has the
value 'Agree'. After giving the code and the error msg, i give the value of
data[0] and data[1] to give you an idea of the data structure.
This is my code:
from xlrd import *
from xlwt import Workbook, easyxf
book = Workbook()
sheet1 = book.add_sheet('sheet1')
cat_cell = easyxf('pattern: pattern solid, fore_colour orange')
workbook = open_workbook ("merged_manip_OUT.xlsx")
sheet = workbook.sheet_by_index(0)
#print sheet.cell_value(0,0)
print sheet.nrows
print sheet.ncols
##for col in range (sheet.ncols):
## print sheet.cell_value(0,col)
print '------'
data = [[sheet.cell_value(r,c) for c in range(sheet.ncols)] for r in range
(sheet.nrows)]
print len(data)
header = data[0]
data = data[1:]
print len(data)
print '------'
for x, row in enumerate(data):
if str(row[-2]).strip() =='Agree':
for y,value in enumerate(row):
sheet1.write(x,y,row[0],row[1],cat_cell)
else:
for y,value in enumerate(row):
sheet1.write(x,y,row[0],row[1])
book.save('merged_manip_OUT_mod.xls')
***********
This is the error message:
>>>
2452
27
------
2452
2451
------
Traceback (most recent call last):
File
"/Users/king/Desktop/cell_line_comparison/comparison/Merged/color/color.py",
line 39, in <module>
sheet1.write(x,y,row[0],row[1])
File
"/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/xlwt/Worksheet.py",
line 1030, in write
self.row(r).write(c, label, style)
File
"/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/xlwt/Row.py",
line 234, in write
self.__adjust_height(style)
File
"/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/xlwt/Row.py",
line 64, in __adjust_height
twips = style.font.height
AttributeError: 'float' object has no attribute 'font'
>>>
*******
And these are the first two elements of the nested list data:
>>> data[0]
[u'230484_at', 55349.0, u'CHDH', u'choline dehydrogenase', u'3p21.1',
7.40318345, 7.40318345, 7.40318345, 0.0, 7.40318345, 1.0, 1.0, u'Same',
u'Same', u'ILMN_2135321', u'CHDH', 55349.0, 3.0, 4.682925638, 3.856160407,
0.000480349, 3.765115429, -0.82676523, 0.563791944, u'Down', u'Disagree',
u'Favorite']
>>> data[1]
[u'1559591_s_at', 55349.0, u'CHDH', u'choline dehydrogenase', u'3p21.1',
6.682511457, 7.221781517, 9.847400986, -3.164889529, 6.977198106,
0.111499602, 0.136767478, u'Down', u'Down', u'ILMN_2135321', u'CHDH',
55349.0, 3.0, 4.682925638, 3.856160407, 0.000480349, 3.765115429,
-0.82676523, 0.563791944, u'Down', u'Agree', '']
Notice that the string values have all been converted to unicode string
values by xlrd.
On Thu, Mar 27, 2014 at 12:04 PM, Vikram K <vikthirtyfive at gmail.com> wrote:
> This is perfect. Many thanks.
>
>
> On Thu, Mar 27, 2014 at 1:11 AM, James Nicholson <nicholsonjf at gmail.com>wrote:
>
>> 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/20140331/47003660/attachment.html>
More information about the Baypiggies
mailing list