[Baypiggies] Fwd: adding color to excel files programmatically
Vikram K
vikthirtyfive at gmail.com
Tue Apr 1 10:52:36 CEST 2014
It is gene expression data.
On Mon, Mar 31, 2014 at 11:43 PM, James Nicholson <nicholsonjf at gmail.com>wrote:
> 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/20140401/87f16f1c/attachment-0001.html>
More information about the Baypiggies
mailing list