<div dir="ltr">It is gene expression data.</div><div class="gmail_extra"><br><br><div class="gmail_quote">On Mon, Mar 31, 2014 at 11:43 PM, James Nicholson <span dir="ltr"><<a href="mailto:nicholsonjf@gmail.com" target="_blank">nicholsonjf@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Glad you were able to get it working. Out of curiosity, what is that data you're working with above?<span class="HOEnZb"><font color="#888888"><br>
</font></span></div><div class="gmail_extra"><span class="HOEnZb"><font color="#888888"><br clear="all"><div><div dir="ltr"><div>James Nicholson<br></div><a href="http://nicholsonjf.com" target="_blank">nicholsonjf.com</a><br>
</div></div></font></span><div><div class="h5">
<br><br><div class="gmail_quote">On Mon, Mar 31, 2014 at 3:07 PM, Vikram K <span dir="ltr"><<a href="mailto:vikthirtyfive@gmail.com" target="_blank">vikthirtyfive@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">finally got it to work:<div><br></div><div><div><div>from xlrd import *</div><div>from xlwt import Workbook, easyxf</div><div><br></div><div>book = Workbook()</div><div><br></div><div>sheet1 = book.add_sheet('sheet1')</div>
<div><br></div><div>cat_cell = easyxf('pattern: pattern solid, fore_colour orange')</div><div><br></div><div><br></div><div>workbook = open_workbook ("merged_manip_OUT.xlsx")</div><div>sheet = workbook.sheet_by_index(0)</div>
<div>#print sheet.cell_value(0,0)</div><div>print sheet.nrows</div><div>print sheet.ncols</div><div><br></div><div><br></div><div>##for col in range (sheet.ncols):</div><div>## print sheet.cell_value(0,col)</div><div><br>
</div><div>print '------'</div><div>data = [[sheet.cell_value(r,c) for c in range(sheet.ncols)] for r in range (sheet.nrows)]</div><div>print len(data)</div><div><br></div><div><br></div><div>header = data[0]</div>
<div>data = data[1:]</div><div>print len(data)</div><div><br></div><div>print '------'</div><div><br></div><div><br></div><div><br></div><div>for x, row in enumerate(data):</div><div> if str(row[-2]).strip() =='Agree':</div>
<div> for y,value in enumerate(row):</div></div><div> sheet1.write(x,y,value,cat_cell)</div><div><div> else:</div><div> for y,value in enumerate(row):</div></div><div> sheet1.write(x,y,value)</div>
<div>
<br></div><div>book.save('merged_manip_OUT_mod.xls')</div><br><div class="gmail_quote"><div>---------- Forwarded message ----------<br>From: <b class="gmail_sendername">Vikram K</b> <span dir="ltr"><<a href="mailto:vikthirtyfive@gmail.com" target="_blank">vikthirtyfive@gmail.com</a>></span><br>
</div><div><div>
Date: Mon, Mar 31, 2014 at 5:58 PM<br>Subject: Fwd: [Baypiggies] adding color to excel files programmatically<br>To: Baypiggies <<a href="mailto:baypiggies@python.org" target="_blank">baypiggies@python.org</a>><br>
<br><br><div dir="ltr">
Made a slight correction to the code:<div><br></div><div><div><div>from xlrd import *</div><div>from xlwt import Workbook, easyxf</div><div><br></div><div>book = Workbook()</div><div><br></div><div>sheet1 = book.add_sheet('sheet1')</div>
<div><br></div><div>cat_cell = easyxf('pattern: pattern solid, fore_colour orange')</div><div><br></div><div><br></div><div>workbook = open_workbook ("merged_manip_OUT.xlsx")</div><div>sheet = workbook.sheet_by_index(0)</div>
<div>#print sheet.cell_value(0,0)</div><div>print sheet.nrows</div><div>print sheet.ncols</div><div><br></div><div><br></div><div>##for col in range (sheet.ncols):</div><div>## print sheet.cell_value(0,col)</div><div><br>
</div><div>print '------'</div><div>data = [[sheet.cell_value(r,c) for c in range(sheet.ncols)] for r in range (sheet.nrows)]</div><div>print len(data)</div><div><br></div><div><br></div><div>header = data[0]</div>
<div>data = data[1:]</div><div>print len(data)</div><div><br></div><div>print '------'</div><div><br></div><div><br></div><div><br></div><div>for x, row in enumerate(data):</div><div> if str(row[-2]).strip() =='Agree':</div>
<div> for y,value in enumerate(row):</div></div><div> sheet1.write(x,y,value[0],value[1],cat_cell)</div><div><div> else:</div><div> for y,value in enumerate(row):</div></div><div> sheet1.write(x,y,value[0],value[1])</div>
<div><br></div><div>book.save('merged_manip_OUT_mod.xls')</div><div><br></div><div>I am only trying to print the first two values of each row in the nested list data to the output file (for starters). Error msg is:</div>
<div><br></div><div><div><div><br></div><div>Traceback (most recent call last):</div></div><div> File "/Users/katju/Desktop/cell_line_comparison/comparison/Merged/color/color.py", line 40, in <module></div>
<div> sheet1.write(x,y,value[0],value[1])</div><div>
<div> File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/xlwt/Worksheet.py", line 1030, in write</div><div> self.row(r).write(c, label, style)</div><div> File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/xlwt/Row.py", line 234, in write</div>
<div> self.__adjust_height(style)</div><div> File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/xlwt/Row.py", line 64, in __adjust_height</div><div> twips = style.font.height</div>
</div><div>AttributeError: 'unicode' object has no attribute 'font'</div><div>>>> </div></div><div><br></div><div>i want the first element value[0] to be a string and the second element value[1] to be an int. I tried doing str(value[0]) and int(value[1]) but again got a similar error msg.</div>
<div><div>
<br><div class="gmail_quote">---------- Forwarded message ----------<br>From: <b class="gmail_sendername">Vikram K</b> <span dir="ltr"><<a href="mailto:vikthirtyfive@gmail.com" target="_blank">vikthirtyfive@gmail.com</a>></span><br>
Date: Mon, Mar 31, 2014 at 5:28 PM<br>Subject: Re: [Baypiggies] adding color to excel files programmatically<br>To: James Nicholson <<a href="mailto:nicholsonjf@gmail.com" target="_blank">nicholsonjf@gmail.com</a>><br>
Cc: Martin Falatic <<a href="mailto:martin@falatic.com" target="_blank">martin@falatic.com</a>>, Baypiggies <<a href="mailto:baypiggies@python.org" target="_blank">baypiggies@python.org</a>><br>
<br><br><div dir="ltr">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.<div>
<br></div><div>This is my code:<div><br></div><div><div><div>from xlrd import *</div><div><div>from xlwt import Workbook, easyxf</div><div><br></div><div>book = Workbook()</div><div><br></div><div>sheet1 = book.add_sheet('sheet1')</div>
<div><br></div></div><div>cat_cell = easyxf('pattern: pattern solid, fore_colour orange')</div><div><br></div><div><br></div><div>workbook = open_workbook ("merged_manip_OUT.xlsx")</div><div>sheet = workbook.sheet_by_index(0)</div>
<div>#print sheet.cell_value(0,0)</div><div>print sheet.nrows</div><div>print sheet.ncols</div><div><br></div><div><br></div><div>##for col in range (sheet.ncols):</div><div>## print sheet.cell_value(0,col)</div><div><br>
</div><div>print '------'</div><div>data = [[sheet.cell_value(r,c) for c in range(sheet.ncols)] for r in range (sheet.nrows)]</div><div>print len(data)</div><div><br></div><div><br></div><div>header = data[0]</div>
<div>data = data[1:]</div><div>print len(data)</div><div><br></div><div>print '------'</div><div><br></div><div><br></div><div>for x, row in enumerate(data):</div><div> if str(row[-2]).strip() =='Agree':</div>
<div> for y,value in enumerate(row):</div><div> sheet1.write(x,y,row[0],row[1],cat_cell)</div><div> else:</div><div> for y,value in enumerate(row):</div><div> sheet1.write(x,y,row[0],row[1])</div>
<div><br></div><div>book.save('merged_manip_OUT_mod.xls')</div><div> </div></div></div></div><div>***********</div><div><br></div><div>This is the error message:</div><div><br></div><div><div>>>> </div><div>
2452</div><div>27</div><div>------</div><div>2452</div><div>2451</div><div>------</div><div><br></div><div>Traceback (most recent call last):</div><div> File "/Users/king/Desktop/cell_line_comparison/comparison/Merged/color/color.py", line 39, in <module></div>
<div> sheet1.write(x,y,row[0],row[1])</div><div> File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/xlwt/Worksheet.py", line 1030, in write</div><div> self.row(r).write(c, label, style)</div>
<div> File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/xlwt/Row.py", line 234, in write</div><div> self.__adjust_height(style)</div><div> File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/xlwt/Row.py", line 64, in __adjust_height</div>
<div> twips = style.font.height</div><div>AttributeError: 'float' object has no attribute 'font'</div><div>>>></div></div><div><br></div><div>*******</div><div><br></div><div>And these are the first two elements of the nested list data:</div>
<div><br></div><div><div>>>> data[0]</div><div>[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']</div>
<div>>>> data[1]</div><div>[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', '']</div>
</div><div><br></div><div>Notice that the string values have all been converted to unicode string values by xlrd. </div></div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">
On Thu, Mar 27, 2014 at 12:04 PM, Vikram K <span dir="ltr"><<a href="mailto:vikthirtyfive@gmail.com" target="_blank">vikthirtyfive@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">This is perfect. Many thanks.</div><div><div><div class="gmail_extra"><br><br>
<div class="gmail_quote">On Thu, Mar 27, 2014 at 1:11 AM, James Nicholson <span dir="ltr"><<a href="mailto:nicholsonjf@gmail.com" target="_blank">nicholsonjf@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div>Hey Vikram,<br><br></div>Tested the below code, it works. Output file is attached.<br><br></div>
<div>Let me know if you have any questions, hopefully you can adapt this to what you're trying to do.<br>
<br></div><div>Also, check out the <a href="http://www.simplistix.co.uk/presentations/python-excel.pdf" target="_blank">python-excel pdf</a>, it's an excellent resource.<br><br>--------------------------------------------------------------------------------------------------------------------------<br>
from xlwt import Workbook, easyxf<br><br>book = Workbook()<br><br>sheet1 = book.add_sheet('sheet1')<br><br></div><div>cat_cell = easyxf('pattern: pattern solid, fore_colour red')<br><br>rows = [['cat',10,20,30],['cat',50,60,70],['dog',20,30,40]]<br>
<br>for x, row in enumerate(rows):<br> if row[0] == 'cat':<br> for y, value in enumerate(row):<br> sheet1.write(x, y, value, cat_cell)<br> else:<br> for y, value in enumerate(row):<br>
sheet1.write(x, y, value)<br><br>book.save('cat.xls')<br>--------------------------------------------------------------------------------------------------------------------------<br></div></div><div class="gmail_extra">
<br clear="all"><div><div dir="ltr"><div>James Nicholson<br></div><a href="http://nicholsonjf.com" target="_blank">nicholsonjf.com</a><br></div></div><div><div>
<br><br><div class="gmail_quote">On Wed, Mar 26, 2014 at 9:11 PM, Vikram K <span dir="ltr"><<a href="mailto:vikthirtyfive@gmail.com" target="_blank">vikthirtyfive@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">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:<div><br></div><div><div>
import xlwt</div>
<div>book = xlwt.Workbook()</div><div>xlwt.add_palette_colour("custom_color",0x21)</div><div><br></div><div>book.set_colour_RGB(0x21,251,228,228)</div><div><br></div><div>sheet1 = book.add_sheet('Sheet1')</div>
<div><br></div><div>style = xlwt.easyxf('pattern:pattern solid, fore_colour 0x21')</div><div>sheet1.write(0,0,'Some text', style)</div><div>book.save('test.xls')</div></div><div><br></div><div>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:</div>
<div><br></div><div><div>>>> x = [['cat',10,20,30],['cat',50,60,70],['dog',20,30,40]]</div><div>>>> x</div><div>[['cat', 10, 20, 30], ['cat', 50, 60, 70], ['dog', 20, 30, 40]]</div>
<div>>>> for i in x:</div><div><span style="white-space:pre-wrap"> </span>print i</div><div><br></div><div><span style="white-space:pre-wrap"> </span></div><div>['cat', 10, 20, 30]</div>
<div>['cat', 50, 60, 70]</div><div>['dog', 20, 30, 40]</div><div>>>> </div></div><div><br></div><div>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.</div>
</div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">On Wed, Mar 26, 2014 at 4:12 PM, Martin Falatic <span dir="ltr"><<a href="mailto:martin@falatic.com" target="_blank">martin@falatic.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">If you're generating a new excel file, xlwt works... some ideas:<br>
<br>
<a href="http://stackoverflow.com/questions/15649482/how-to-set-color-of-text-using-xlwt" target="_blank">http://stackoverflow.com/questions/15649482/how-to-set-color-of-text-using-xlwt</a><br>
<br>
<a href="http://stackoverflow.com/questions/7746837/python-xlwt-set-custom-background-colour-of-a-cell" target="_blank">http://stackoverflow.com/questions/7746837/python-xlwt-set-custom-background-colour-of-a-cell</a><br>
<br>
<a href="http://stackoverflow.com/questions/2981293/python-excel-xlwt-colouring-every-second-row?rq=1" target="_blank">http://stackoverflow.com/questions/2981293/python-excel-xlwt-colouring-every-second-row?rq=1</a><br>
<br>
- Marty<br>
<div><br>
<br>
On Wed, March 26, 2014 12:10, Vikram K wrote:<br>
> Could someone kindly tell me how i can add color programmatically to<br>
> specific rows in an excel file. Thank you.<br>
</div><div><div>> _______________________________________________<br>
> Baypiggies mailing list<br>
> <a href="mailto:Baypiggies@python.org" target="_blank">Baypiggies@python.org</a><br>
> To change your subscription options or unsubscribe:<br>
> <a href="https://mail.python.org/mailman/listinfo/baypiggies" target="_blank">https://mail.python.org/mailman/listinfo/baypiggies</a><br>
<br>
<br>
</div></div></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
Baypiggies mailing list<br>
<a href="mailto:Baypiggies@python.org" target="_blank">Baypiggies@python.org</a><br>
To change your subscription options or unsubscribe:<br>
<a href="https://mail.python.org/mailman/listinfo/baypiggies" target="_blank">https://mail.python.org/mailman/listinfo/baypiggies</a><br></blockquote></div><br></div></div></div>
</blockquote></div><br></div>
</div></div></blockquote></div><br></div>
</div></div></div><br></div></div></div></div>
</div></div></div><br></div></div>
<br>_______________________________________________<br>
Baypiggies mailing list<br>
<a href="mailto:Baypiggies@python.org" target="_blank">Baypiggies@python.org</a><br>
To change your subscription options or unsubscribe:<br>
<a href="https://mail.python.org/mailman/listinfo/baypiggies" target="_blank">https://mail.python.org/mailman/listinfo/baypiggies</a><br></blockquote></div><br></div></div></div>
</blockquote></div><br></div>