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

Writing to a blank Excel file using Python

P: 28
Hello,
I posted here before asking a question about reading excel. Now that the reading problem is solved i am having trouble on how to write to an excel file.

This was my code to read the excel file:

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. for row in range(200):
  6.   k=k+1
  7.   for col in range(4):
  8.       if col==1:
  9.         num1=xlSht.Cells(k,1).Value
  10.       elif col==2:
  11.         num2=xlSht.Cells(k,2).Value
  12.       elif col==3:
  13.         num3=xlSht.Cells(k,3).Value
  14.       elif col==4:
  15.         num4=xlSht.Cells(k,4).Value
  16.  
now i am trying to write arrays of numbers and text into respective excel rows and columns. i am pretty sure the code is similar to the reading code but am not sure how to do it.
also is there a way in python to see what commands can be used with a library. like i know for example in many applications if you type in ? and press enter it prints out all the different commands and what the do. so is there some sort of similar thing in python that prints out all the different commands that can be used with the excel module.
Thanks
Oct 3 '07 #1
Share this Question
Share on Google+
8 Replies


bartonc
Expert 5K+
P: 6,596
Hello,
I posted here before asking a question about reading excel. Now that the reading problem is solved i am having trouble on how to write to an excel file.
<snip>

now i am trying to write arrays of numbers and text into respective excel rows and columns. i am pretty sure the code is similar to the reading code but am not sure how to do it.
also is there a way in python to see what commands can be used with a library. like i know for example in many applications if you type in ? and press enter it prints out all the different commands and what the do. so is there some sort of similar thing in python that prints out all the different commands that can be used with the excel module.
Thanks
If you don't know Visual Basic, you can figure out the command to send by recording a macro of the task. Then read the macro text in the macro editor and translate the command to your Python program.
Oct 3 '07 #2

P: 28
recording an excel macro prints this out in visual basic

ActiveCell.FormulaR1C1 = "5"
Range("A2").Select
ActiveCell.FormulaR1C1 = "10"
Range("A3").Select
ActiveCell.FormulaR1C1 = "15"
Range("A4").Select
ActiveCell.FormulaR1C1 = "20"
Range("B1").Select

so what would be the correct way to call it in python. would i use the
Oct 3 '07 #3

Expert 100+
P: 511
Hello,
I posted here before asking a question about reading excel. Now that the reading problem is solved i am having trouble on how to write to an excel file.

This was my code to read the excel file:

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. for row in range(200):
  6.   k=k+1
  7.   for col in range(4):
  8.       if col==1:
  9.         num1=xlSht.Cells(k,1).Value
  10.       elif col==2:
  11.         num2=xlSht.Cells(k,2).Value
  12.       elif col==3:
  13.         num3=xlSht.Cells(k,3).Value
  14.       elif col==4:
  15.         num4=xlSht.Cells(k,4).Value
  16.  
now i am trying to write arrays of numbers and text into respective excel rows and columns. i am pretty sure the code is similar to the reading code but am not sure how to do it.
also is there a way in python to see what commands can be used with a library. like i know for example in many applications if you type in ? and press enter it prints out all the different commands and what the do. so is there some sort of similar thing in python that prints out all the different commands that can be used with the excel module.
Thanks
try inverting the assignment
Expand|Select|Wrap|Line Numbers
  1. num2=xlSht.Cells(k,2).Value
  2.  
could be
Expand|Select|Wrap|Line Numbers
  1. xlSht.Cells(k,2).Value = num2
  2.  
Oct 4 '07 #4

bartonc
Expert 5K+
P: 6,596
try inverting the assignment
Expand|Select|Wrap|Line Numbers
  1. num2=xlSht.Cells(k,2).Value
  2.  
could be
Expand|Select|Wrap|Line Numbers
  1. xlSht.Cells(k,2).Value = num2
  2.  
This is, in fact, correct. Good show, GD. Thanks.
Oct 4 '07 #5

Expert 100+
P: 511
This is, in fact, correct. Good show, GD. Thanks.
just lucky guess though. :)
Oct 4 '07 #6

P: 28
Yep, inverting it did the trick.

This is the code if anyones interested.

Expand|Select|Wrap|Line Numbers
  1. for row in range(loads):
  2.   k=k+1
  3.   for col in range(6):
  4.       if col==1:
  5.         xlSht.Cells(k,1).Value=busname[f]
  6.       elif col==2:
  7.         xlSht.Cells(k,2).Value=busload[f]       
  8.       elif col==3:
  9.         xlSht.Cells(k,3).Value=mainload[f]
  10.       elif col==4:
  11.         xlSht.Cells(k,4).Value=difference[f]
  12.       elif col==5:
  13.         xlSht.Cells(k,5).Value=percent[f]
  14.   f=f+1
  15.  
Thanks again.
Oct 4 '07 #7

P: 15
Excel has excellent CSV (comma separated values) support and the Python CSV module is standard and pretty easy to use. It might also make your code easier to modify. With your code if you want to insert an entry into column 3 for example you would have to swap a lot of stuff around.

Expand|Select|Wrap|Line Numbers
  1. import csv
  2. fid=open('c:/fahad/new/file.xls','wb')
  3. writer=csv.writer(fid)
  4. for f, load in enumerate(loads):
  5.     writer.writerow([ busname[f],busload[f],mainload[f],difference[f],percent[f] ] )
  6.  
  7. fid.close()
  8.  
Excel will even recognize formulas
Expand|Select|Wrap|Line Numbers
  1. writer.writerow([ busname[f],
  2.                   busload[f],
  3.                   mainload[f],
  4.                   difference[f],
  5.                   percent[f],
  6.                   r'=%g/%g'%(busload[f],mainload[f] ])
  7.  
If loads is a list of dictionaries you could try something like:
Expand|Select|Wrap|Line Numbers
  1. for load in loads:
  2.     writer.writerow([ load['busname'],
  3.                       load['busload'],
  4.                       load['mainload'],
  5.                       load['difference'],
  6.                       load['percent'] ])
  7.  
Which I think is the most pythonic solution.

Just a friendly suggestion,
Roger
Oct 5 '07 #8

bartonc
Expert 5K+
P: 6,596
Very nice, Roger. Thanks.

Then once the .xls file is created one could:
Expand|Select|Wrap|Line Numbers
  1. import os
  2. os.startfile(r'c:/fahad/new/file.xls')
Which (I believe) works with either Excel or OpenOffice.
Oct 5 '07 #9

Post your reply

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