Python and Excel

Using WINXP-SP2, Python 2.3

I am trying to read an excel file and then use the numbers in the excel file to do some calculations
The program reads my excel file properly but the problem comes when I try to use the numbers for example
Cell (1,1) has the number 50 in it
Now I can read it and print it but when I say for example try to add or subtract from the number it gives errors.

Here is my python code.

Expand|Select|Wrap|Line Numbers

1. from win32com.client import Dispatch
2. xlApp = Dispatch ("Excel.Application")
3. xlWb = xlApp.Workbooks.Open ("c:/fahad/new/file.xls")
4. xlSht = xlWb.Worksheets (1)
5. mainload=range(10)
6. k=0
7. for row in range(10):
8.   k=k+1
9.   for col in range(4):
10.       if col==1:
11.         load1=xlSht.Cells(k,1)
12.       elif col==2:
13.         load2=xlSht.Cells(k,2)
14.       elif col==3:
15.         load3=xlSht.Cells(k,3)
16.       elif col==4:
17.         load4=xlSht.Cells(k,4)
18.   mainload[row]=load3
19.  
20. print mainload[0] + 5
21.  

this is the error I am getting

TypeError: unsupported operand type(s) for +: ‘instance’ and ‘float’

And whatever I am doing whether it is + or – or * or / the error respectively
TypeError: unsupported operand type(s) for -: ‘instance’ and ‘float’
TypeError: unsupported operand type(s) for *: ‘instance’ and ‘float’
TypeError: unsupported operand type(s) for /: ‘instance’ and ‘float’

What it looks like to me is that the program does not recognize whats in the cell as a number

Please help

You can easily convert the instance object to a string and evaluate its value:

Expand|Select|Wrap|Line Numbers

1. from win32com.client import Dispatch
2. xlApp = Dispatch ("Excel.Application")
3. xlWb = xlApp.Workbooks.Open('data.xls')
4. xlSht = xlWb.Worksheets (1)
5. dataList = []
6. for row in range(12,16):
7.     for col in range(3,6):
8.             dataList.append(xlSht.Cells(row,col))
9.  
10. for item in dataList:
11.     print item
12.     print 'Convert COMObject to text: %s' % item

Output:

Expand|Select|Wrap|Line Numbers

1. >>> 3.0
2. Convert COMObject to text: 3.0
3. 901C4
4. Convert COMObject to text: 901C4
5. 9.0
6. Convert COMObject to text: 9.0
7. 1.0
8. Convert COMObject to text: 1.0
9. 902C3
10. Convert COMObject to text: 902C3
11. 9.0
12. Convert COMObject to text: 9.0
13. 3.0
14. Convert COMObject to text: 3.0
15. 901C5
16. Convert COMObject to text: 901C5
17. 9.0
18. Convert COMObject to text: 9.0
19. 1.0
20. Convert COMObject to text: 1.0
21. 902C4
22. Convert COMObject to text: 902C4
23. 9.0
24. Convert COMObject to text: 9.0
25. >>>

>>> float(str(dataList[0]))
3.0
>>>

Thanks for that, bvdet. It's good to know (I don't have Excel for testing) that Cells() objects have an __str__() method hung on them. However, using the Value attribute directly sure seems to be a lot cleaner.

Would you mind testing that for me? Thanks,
Barton.

Before today I knew nothing about COMObjects, but it makes sense there would be a directly accessible attribute.

Expand|Select|Wrap|Line Numbers

1. for item in dataList:
2.     print item
3.     print 'Print COMObject value attribute: %s' % item.value
4.  
5. print type(item.value)

Expand|Select|Wrap|Line Numbers

1. >>> 3.0
2. Print COMObject value attribute: 3.0
3. 901C4
4. Print COMObject value attribute: 901C4
5. 9.0
6. Print COMObject value attribute: 9.0
7. 1.0
8. Print COMObject value attribute: 1.0
9. 902C3
10. Print COMObject value attribute: 902C3
11. 9.0
12. Print COMObject value attribute: 9.0
13. 3.0
14. Print COMObject value attribute: 3.0
15. 901C5
16. Print COMObject value attribute: 901C5
17. 9.0
18. Print COMObject value attribute: 9.0
19. 1.0
20. Print COMObject value attribute: 1.0
21. 902C4
22. Print COMObject value attribute: 902C4
23. 9.0
24. Print COMObject value attribute: 9.0
25. <type 'float'>
26. >>>
27.  

Very interesting... The book (©2000) has it with a capital "V" as in ".Value".
Did you try that as well?

Strange, but it works.

>>> item.Value
9.0
>>> item.value
9.0
>>> item.Value2
9.0
>>> item.Address
u'$E$15'
>>> item.address
u'$E$15'
>>>

The attributes were not available through item.__dict__.

Thanks guys.

This fixed my problem:

Expand|Select|Wrap|Line Numbers

1. load3=xlSht.Cells(k,3).Value
2.  

And apparently it only works if the v in value is uppercase.