By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Python and Excel

P: 28
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
Oct 1 '07 #1
Share this Question
Share on Google+
9 Replies


bartonc
Expert 5K+
P: 6,596
On line 15, for example:
Expand|Select|Wrap|Line Numbers
  1. load3=xlSht.Cells(k,3).Value
The "instance" of a Cells() object has other attributes as well.
Oct 1 '07 #2

bvdet
Expert Mod 2.5K+
P: 2,851
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
>>>
Oct 1 '07 #3

bartonc
Expert 5K+
P: 6,596
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.
Oct 1 '07 #4

bvdet
Expert Mod 2.5K+
P: 2,851
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.  
Oct 1 '07 #5

bartonc
Expert 5K+
P: 6,596
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?
Oct 1 '07 #6

bvdet
Expert Mod 2.5K+
P: 2,851
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__.
Oct 2 '07 #7

bartonc
Expert 5K+
P: 6,596
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'
>>>
Thanks. That makes a lot of sense, now that I think about it. COM interfaces are usually specified as 'case insensitive'.
The attributes were not available through item.__dict__.
They are probably implemented as property()s. As in:
Expand|Select|Wrap|Line Numbers
  1. #
  2.     def _get_CaptureDriverIndex(self):
  3.         return self.GetCOMProp('CaptureDriverIndex')
  4.     def _set_CaptureDriverIndex(self, CaptureDriverIndex):
  5.         self.SetCOMProp('CaptureDriverIndex', CaptureDriverIndex)
  6.     CaptureDriverIndex = property(_get_CaptureDriverIndex, _set_CaptureDriverIndex)
Just in case you are interested.
Oct 2 '07 #8

P: 28
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.
Oct 2 '07 #9

bartonc
Expert 5K+
P: 6,596
Thanks guys.
You are quite welcome.
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.
Again, very interesting results regarding the case of the attribute name.
Thanks for keeping us up-to-date on this issue.
Oct 2 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.