Reading floats from Excel using COM

[KB]

Hi, I am using the Range function to return a tuple from Excel.

The data in the range (nREVENUE) in the excel file is 100.0, 101.0,
102.0, 103.0, 104.0

I can successfully iterate across the tuple and list, but when I try
and cast to a float to do some math, I get:

File "C:\Python25\lib\site-packages\win32com\client\__init__.py",
line 454, in __getattr__
raise AttributeError, "'%s' object has no attribute '%s'" % (repr
(self), attr)
AttributeError: '<win32com.gen_py.Microsoft Excel 12.0 Object
Library.Range instance at 0x37766608>' object has no attribute
'__float__'

**** CODE *****
import win32com.client
import os

excel=win32com.client.Dispatch("Excel.Application")
excel.Visible=0
excel.DisplayAlerts=False


xlsname=os.path.join(os.getcwd(),"nametest.xlsx")
nametest=excel.Workbooks.Open(xlsname)

revenue_list=excel.Range("nREVENUE")

revenue=[]
revenue.extend(revenue_list)

for i in range(len(revenue)):
rev=revenue[i]
print float(rev)

excel.Quit()
del excel

****************

I need to use COM as I will eventually need formula support, otherwise
I would use xlutils.

Any advice on how to cast the elements to a float would be greatly
appreciated!

[John Machin]

On 15/07/2009 10:31 AM, KB wrote:
> Hi, I am using the Range function to return a tuple from Excel.
>
> The data in the range (nREVENUE) in the excel file is 100.0, 101.0,
> 102.0, 103.0, 104.0
>
> I can successfully iterate across the tuple and list, but when I try
> and cast to a float to do some math, I get:
>
> File "C:\Python25\lib\site-packages\win32com\client\__init__.py",
> line 454, in __getattr__
> raise AttributeError, "'%s' object has no attribute '%s'" % (repr
> (self), attr)
> AttributeError: '<win32com.gen_py.Microsoft Excel 12.0 Object
> Library.Range instance at 0x37766608>' object has no attribute
> '__float__'

That tells you that you are attempting float(a_Range_object) which tends
to indicate that you still have a level of unpacking/iterating to do.

>
> **** CODE *****
> import win32com.client
> import os
>
> excel=win32com.client.Dispatch("Excel.Application")
> excel.Visible=0
> excel.DisplayAlerts=False
>
>
> xlsname=os.path.join(os.getcwd(),"nametest.xlsx")
> nametest=excel.Workbooks.Open(xlsname)
>
> revenue_list=excel.Range("nREVENUE")

Perhaps Range() is two-dimensional i.e. it is returning a list of lists
or similar.

The print statement and the repr() function are your friends. They will
stick by you even when internet access is down.

Insert here:

print type(revenue_list)
print repr(revenue_list)

>
> revenue=[]
> revenue.extend(revenue_list)

Perhaps nothing to do with your problem, but why not replace the above
two lines with one:

revenue = revenue_list[:]

What are you trying to achieve? Why do you think that you need a copy,
anyway?

>
> for i in range(len(revenue)):
> rev=revenue[i]

Insert here:

print i, type(rev), repr(rev)

> print float(rev)
>
> excel.Quit()
> del excel

HTH,

John

[KB]

Thanks John!

I inserted the revenue=revenue_list[:] but received:
Traceback (most recent call last):
File "C:\Python25\Lib\site-packages\pythonwin\pywin\framework
\scriptutils.py", line 312, in RunScript
exec codeObject in __main__.__dict__
File "C:\Users\Alagalah\Desktop\System\Source\test\useexcelname.py",
line 40, in <module>
revenue=revenue_list[:]
File "C:\Python25\lib\site-packages\win32com\gen_py
\00020813-0000-0000-C000-000000000046x0x1x6\Range.py", line 691, in
__getitem__
return self._enum_.__getitem__(index)
File "C:\Python25\lib\site-packages\win32com\client\util.py", line
37, in __getitem__
return self.__GetIndex(index)
File "C:\Python25\lib\site-packages\win32com\client\util.py", line
42, in __GetIndex
if type(index)!=type(0): raise TypeError, "Only integer indexes
are supported for enumerators"
TypeError: Only integer indexes are supported for enumerators
>>>


prior to that I did get feedback from:
print type(revenue_list)
print repr(revenue_list)

<type 'instance'>

<win32com.gen_py.Microsoft Excel 12.0 Object Library.Range instance at

0x36527080>

By reverting back to:

revenue=[]
revenue.extend(revenue_list)

I got the following output:
<type 'instance'>

<win32com.gen_py.Microsoft Excel 12.0 Object Library.Range instance at

0x36597352>
0 <type 'instance'> <win32com.gen_py.Microsoft Excel 12.0 Object
Library.Range instance at 0x36597392>
1 <type 'instance'> <win32com.gen_py.Microsoft Excel 12.0 Object
Library.Range instance at 0x36527200>
2 <type 'instance'> <win32com.gen_py.Microsoft Excel 12.0 Object
Library.Range instance at 0x36671616>
3 <type 'instance'> <win32com.gen_py.Microsoft Excel 12.0 Object
Library.Range instance at 0x36671656>
4 <type 'instance'> <win32com.gen_py.Microsoft Excel 12.0 Object
Library.Range instance at 0x36671696>
5 <type 'instance'> <win32com.gen_py.Microsoft Excel 12.0 Object
Library.Range instance at 0x36671736>
DONE


From the following code:

import win32com.client
import os

excel=win32com.client.Dispatch("Excel.Application")
excel.Visible=0
excel.DisplayAlerts=False


xlsname=os.path.join(os.getcwd(),"nametest.xlsx")
nametest=excel.Workbooks.Open(xlsname)





revenue_list=excel.Range("nREVENUE")

print type(revenue_list)
print repr(revenue_list)


revenue=[]
revenue.extend(revenue_list)

for i in range(len(revenue)):
rev=revenue[i]

print i, type(rev), repr(rev)




print "DONE"

excel.Quit()
del excel
excel=None

Many thanks for the quick response!

[KB]

In fact, if you can show me a way of converting the tuple to a float
that would suffice, and I would be greatly appreciative. I appreciate
your time!

[John Machin]

On 15/07/2009 12:38 PM, KB wrote:
> Thanks John!
>
> I inserted the revenue=revenue_list[:] but received:
> Traceback (most recent call last):
> File "C:\Python25\Lib\site-packages\pythonwin\pywin\framework
> \scriptutils.py", line 312, in RunScript
> exec codeObject in __main__.__dict__
> File "C:\Users\Alagalah\Desktop\System\Source\test\useexcelname.py",
> line 40, in <module>
> revenue=revenue_list[:]
> File "C:\Python25\lib\site-packages\win32com\gen_py
> \00020813-0000-0000-C000-000000000046x0x1x6\Range.py", line 691, in
> __getitem__
> return self._enum_.__getitem__(index)
> File "C:\Python25\lib\site-packages\win32com\client\util.py", line
> 37, in __getitem__
> return self.__GetIndex(index)
> File "C:\Python25\lib\site-packages\win32com\client\util.py", line
> 42, in __GetIndex
> if type(index)!=type(0): raise TypeError, "Only integer indexes
> are supported for enumerators"
> TypeError: Only integer indexes are supported for enumerators

OK, so a Range object doesn't support slicing ... submit a bug report to
the pywin32 project :-)

Never mind the copy mechanism ... why do you think you need to make the
copy?

>
>
> prior to that I did get feedback from:
> print type(revenue_list)
> print repr(revenue_list)
>
> <type 'instance'>
> <win32com.gen_py.Microsoft Excel 12.0 Object Library.Range instance at
> 0x36527080>
>
> By reverting back to:
>
> revenue=[]
> revenue.extend(revenue_list)
>
> I got the following output:
> <type 'instance'>
> <win32com.gen_py.Microsoft Excel 12.0 Object Library.Range instance at
> 0x36597352>
> 0 <type 'instance'> <win32com.gen_py.Microsoft Excel 12.0 Object
> Library.Range instance at 0x36597392>

[snip]

> 5 <type 'instance'> <win32com.gen_py.Microsoft Excel 12.0 Object
> Library.Range instance at 0x36671736>
> DONE
>
>
>>From the following code:

[BIG SNIP]

Ummm yeah that's about what I expected ...

So where does that leave us? Have you now (as I suggested in the
previous message) drilled down into each of those 6 Range instances, or
are you waiting for further advice?

[John Machin]

On 15/07/2009 12:39 PM, KB wrote:
> In fact, if you can show me a way of converting the tuple to a float
> that would suffice, and I would be greatly appreciative. I appreciate
> your time!

Ummmm perhaps you mean "extract floats from tuple of tuples" ... like this:

| >>> import win32com.client
| >>> import os
| >>> excel=win32com.client.Dispatch("Excel.Application")
| >>> excel.Visible=0
| >>> excel.DisplayAlerts=False
| >>> xlsname=os.path.join(os.getcwd(),"nametest.xlsx")
| >>> nametest=excel.Workbooks.Open(xlsname)
| >>> revenue_list=excel.Range("nREVENUE")

| >>> r = revenue_list
| >>> print r
| ((100.0,), (101.0,), (102.0,), (103.0,), (104.0,), (105.0,))
| >>> for rowx, row in enumerate(r):
| ... for colx, value in enumerate(row):
| ... print rowx, colx, value
| ...
| 0 0 100.0
| 1 0 101.0
| 2 0 102.0
| 3 0 103.0
| 4 0 104.0
| 5 0 105.0
| >>>

Note: the above row and column indexes are relative to the top-left cell
in the range. In the xlsx file that I lashed up for the above
demonstration (your file must have been been detached in the mail
somewhere), the range covers cells D2:D7.

[John Machin]

On 15/07/2009 1:38 PM, John Machin wrote:
> On 15/07/2009 12:39 PM, KB wrote:
>> In fact, if you can show me a way of converting the tuple to a float
>> that would suffice, and I would be greatly appreciative. I appreciate
>> your time!

Perhaps you meant a_float = the_tuple[0]
?

[KB]

John,

Thanks a bunch already, but yes, that is the issue I seem to be
having, getting "value" from your code above to be cast to a float...
I still get the same error when I try float(value).

[John Machin]

On 15/07/2009 4:42 PM, KB wrote:
> John,
>
> Thanks a bunch already, but yes, that is the issue I seem to be
> having, getting "value" from your code above to be cast to a float...
> I still get the same error when I try float(value).

Sorry, I'm a bit rusty on this COM stuff ... I fled screaming about 4 or
5 years ago and wrote xlrd and have rarely looked back -- lest I turn
into a pillar of salt :-)

OK, "COMorrah Revisited", Take 4, with an augmented input file:

>>> import win32com.client
>>> import os
>>> excel=win32com.client.Dispatch("Excel.Application")
>>> excel.Visible=0
>>> excel.DisplayAlerts=False
>>> xlsname=os.path.join(os.getcwd(),"nametest.xlsx")
>>> nametest=excel.Workbooks.Open(xlsname)
>>> revenue_list=excel.Range("nREVENUE")

>>> r = revenue_list.GetValue()
# r = revenue_list.Value will do just as well
>>> from pprint import pprint as pp
>>> pp(r)
((100.0, u'foo', True),
(101.0, u'bar', False),
(102.0, u'baz', -2146826246), # the magic number means #N/A
(103.0, u'zot', <PyTime:31/12/1999 12:00:00 AM>), # urggghh
(104.0, u'oof', None), # None means blank cell
(105.0, u'rab', None))
>>> type(r[0][0])
<type 'float'> # ALREADY a float
>>> type(r[0][1])
<type 'unicode'>
>>> type(r[0][2])
<type 'bool'>
>>> type(r[2][2])
<type 'int'>
>>> type(r[3][2])
<type 'time'>
>>>

So: the float/unicode/bool/None stuff is straightforward; for the dates,
look at the pywin32 doco or ask on the pywin32 mailing list. Here's a
reference for the magic error codes:
http://xldennis.wordpress.com/2006/11/22/dealing-with-cverr-values-in-net-%E2%80%93-part-i-the-problem/

Note the magic numbers are just 2 layers of opacity wrapped around the
raw byte in an XLS BOOLERR record: raw = magic & 0xfff - 2000

HTH,
John

[KB]

John!

Sorry to make you look at COM :) but I do appreciate the help!

So if I read this correctly, I just need to reference the list as a 2-
dimensional array to extract the "actual" number? I will give this a
go (my windoze box is out of commission at the mo' - shocking I know)

BTW, a draw in the 1st test was not a bad result... see how the
Aussie's go in the 2nd ;-)

I will search the archives for info on xlutils support for formula
(future?) as that is the only reason I am using COM instead of your
excellent interface.

> reference for the magic error codes:http://xldennis.wordpress.com/2006/11/22/dealing-with-cverr-values-in...

[John Machin]

On 16/07/2009 2:03 AM, KB wrote:

> So if I read this correctly, I just need to reference the list as a 2-
> dimensional array to extract the "actual" number?

More precisely, what you get from the_range.Value is a standard Python
tuple of tuples of cell values. The cell value types can be float,
unicode, int (error values), bool, NoneType (blank cells) or that PyTime
thingy.

Cheers,
John

[KB]

You are the man! Many thanks.