[Baypiggies] Fwd: adding color to excel files programmatically

James Nicholson nicholsonjf at gmail.com
Tue Apr 1 05:43:17 CEST 2014


Glad you were able to get it working. Out of curiosity, what is that data
you're working with above?

James Nicholson
nicholsonjf.com


On Mon, Mar 31, 2014 at 3:07 PM, Vikram K <vikthirtyfive at gmail.com> wrote:

> finally got it to work:
>
> 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,value,cat_cell)
>     else:
>         for y,value in enumerate(row):
>             sheet1.write(x,y,value)
>
> book.save('merged_manip_OUT_mod.xls')
>
> ---------- Forwarded message ----------
> From: Vikram K <vikthirtyfive at gmail.com>
> Date: Mon, Mar 31, 2014 at 5:58 PM
> Subject: Fwd: [Baypiggies] adding color to excel files programmatically
> To: Baypiggies <baypiggies at python.org>
>
>
> Made a slight correction to the 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,value[0],value[1],cat_cell)
>     else:
>         for y,value in enumerate(row):
>             sheet1.write(x,y,value[0],value[1])
>
> book.save('merged_manip_OUT_mod.xls')
>
> 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:
>
>
> Traceback (most recent call last):
>   File
> "/Users/katju/Desktop/cell_line_comparison/comparison/Merged/color/color.py",
> line 40, in <module>
>     sheet1.write(x,y,value[0],value[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: 'unicode' object has no attribute 'font'
> >>>
>
> 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.
>
> ---------- Forwarded message ----------
> From: Vikram K <vikthirtyfive at gmail.com>
> Date: Mon, Mar 31, 2014 at 5:28 PM
> Subject: Re: [Baypiggies] adding color to excel files programmatically
> To: James Nicholson <nicholsonjf at gmail.com>
> Cc: Martin Falatic <martin at falatic.com>, Baypiggies <baypiggies at python.org
> >
>
>
> 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
>>>>
>>>
>>>
>>
>
>
>
> _______________________________________________
> 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/1e470371/attachment-0001.html>


More information about the Baypiggies mailing list