473,847 Members | 1,489 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Python and Excel

28 New Member
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
9 3841
bartonc
6,596 Recognized Expert Expert
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
2,851 Recognized Expert Moderator Specialist
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(dataL ist[0]))
3.0
>>>
Oct 1 '07 #3
bartonc
6,596 Recognized Expert Expert
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(dataL ist[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
2,851 Recognized Expert Moderator Specialist
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
6,596 Recognized Expert Expert
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
2,851 Recognized Expert Moderator Specialist
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
6,596 Recognized Expert Expert
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
fahadqureshi
28 New Member
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
6,596 Recognized Expert Expert
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

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

Similar topics

13
35574
by: Allison Bailey | last post by:
Hi Folks, I'm a brand new Python programmer, so please point me in the right direction if this is not the best forum for this question.... I would like to open an existing MS Excel spreadsheet and extract information from specific worksheets and cells. I'm not really sure how to get started with this process. I ran the COM Makepy utility from my PythonWin (IDE from ActiveSTate),
1
2560
by: Mark Carter | last post by:
I use Office 2000, and I have recently upgraded to python 2.3 and win32all-155.exe The following code worked in python 2.2: xlApp = Dispatch("Excel.Application") xlApp.Visible = 1 xlApp.Workbooks.Add() xlApp.ActiveWorkbook.SaveAs(sys.path + '\\temp.xls') wb = xlApp.Workbooks("temp.xls")
5
2076
by: mbbx6spp | last post by:
Hi All, I already searched this newsgroup and google groups to see if I could find a Python equivalent to Perl's Template::Extract, but didn't find anything leading to a Python module that had similar functionality. I am a big fan of Python as an OO language and use it for many system admin utilities, webdev and even MS Excel AddIn development, but I have found that Perl's Template::Extract is so invaluable to my web scraping utilities...
3
28587
by: zxo102 | last post by:
Hi there, I need your help for python <--> excel. I want to paste selected cells (range) to different location on the same sheet in Excel through python. I have tried it for a while but could not figure it out. Here is my sample code: import win32com.client xl=win32com.client.Dispatch("Excel.Application") xl.Visible=1 wb = xl.Workbooks.Add( )
1
2710
by: zxo102 | last post by:
Hi there, I am trying to put data including Chinese Characters into Excel through python. But I got some problems. Here is my sample code: ################################################## # import win32com.client xlapp = win32com.client.DispatchEx("Excel.Application") xlbook = xlapp.Workbooks.Open("test.xls") sht = xlbook.Worksheets("Sheet1")
15
4128
by: John Machin | last post by:
I am pleased to announce a new general release (0.5.2) of xlrd, a Python package for extracting data from Microsoft Excel spreadsheets. CHANGES: * Book and sheet objects can now be pickled and unpickled. Instead of reading a large spreadsheet multiple times, consider pickling it once and loading the saved pickle; can be much faster. * Now works with Python 2.1. Backporting to Python 2.1 was partially
4
14554
by: e.h.doxtator | last post by:
All I'm a Python newbie, and I'm just getting to the wonders of COM programming. I am trying to programmatically do the following: 1. Activate Excel 2. Add a Workbook 3. Add a Worksheet 4. Populate the new Worksheet 5. Repeat steps 3,4 while there is data.
0
1266
by: dan84 | last post by:
I write my code : #inizializzazioni varie che tralascio #mi connetto al mio db mysql e recupero i dati che volgio inserire nel file excel conn = MySQLdb.connect(host = "XXX", port = XXX, user = "XXX", passwd = "XXX", db= "XXX") cursor = conn.cursor() cursor.execute("SELECT * FROM XXX")
0
936
by: fordie1000 | last post by:
Hi, Just wondering if there is a module similar to the 'win32com' one on windows for interacting with excel via python on a Mac? Thanks,
20
3591
by: Marin Brkic | last post by:
Hello all, please, let me apologize in advance. English is not my first language (not even my second one), so excuse any errors with which I'm about to embarass myself in front of the general public. Second, I'm relatively new to python, so sorry if this seems like a stupid question. I'm trying to find a way to write data to excel cells (or to be more specific to an .xls file), let's say for the sake of argument, data readen from a...
0
9892
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9734
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10991
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10653
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10718
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9490
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5725
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5915
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4540
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.