Writing to a blank Excel file using Python

[fahadqureshi]

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:

from win32com.client import Dispatch
xlApp = Dispatch ("Excel.Application")
xlWb = xlApp.Workbooks.Open ("c:/fahad/new/file.xls")
xlSht = xlWb.Worksheets (1)
for row in range(200):
  k=k+1
  for col in range(4):
      if col==1:
        num1=xlSht.Cells(k,1).Value
      elif col==2:
        num2=xlSht.Cells(k,2).Value
      elif col==3:
        num3=xlSht.Cells(k,3).Value
      elif col==4:
        num4=xlSht.Cells(k,4).Value
 

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

[bartonc]

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.

[fahadqureshi]

recording an excel macro prints this out in visual basic

ActiveCell.Form ulaR1C1 = "5"
Range("A2").Sel ect
ActiveCell.Form ulaR1C1 = "10"
Range("A3").Sel ect
ActiveCell.Form ulaR1C1 = "15"
Range("A4").Sel ect
ActiveCell.Form ulaR1C1 = "20"
Range("B1").Sel ect

so what would be the correct way to call it in python. would i use the

[ghostdog74]

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:

from win32com.client import Dispatch
xlApp = Dispatch ("Excel.Application")
xlWb = xlApp.Workbooks.Open ("c:/fahad/new/file.xls")
xlSht = xlWb.Worksheets (1)
for row in range(200):
  k=k+1
  for col in range(4):
      if col==1:
        num1=xlSht.Cells(k,1).Value
      elif col==2:
        num2=xlSht.Cells(k,2).Value
      elif col==3:
        num3=xlSht.Cells(k,3).Value
      elif col==4:
        num4=xlSht.Cells(k,4).Value
 

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

num2=xlSht.Cells(k,2).Value
 

could be

xlSht.Cells(k,2).Value = num2
 

[bartonc]

try inverting the assignment

num2=xlSht.Cells(k,2).Value
 

could be

xlSht.Cells(k,2).Value = num2
 

This is, in fact, correct. Good show, GD. Thanks.

[ghostdog74]

This is, in fact, correct. Good show, GD. Thanks.

just lucky guess though. :)

[fahadqureshi]

Yep, inverting it did the trick.

This is the code if anyones interested.

for row in range(loads):
  k=k+1
  for col in range(6):
      if col==1:
        xlSht.Cells(k,1).Value=busname[f]
      elif col==2:
        xlSht.Cells(k,2).Value=busload[f]       
      elif col==3:
        xlSht.Cells(k,3).Value=mainload[f]
      elif col==4:
        xlSht.Cells(k,4).Value=difference[f]
      elif col==5:
        xlSht.Cells(k,5).Value=percent[f]
  f=f+1
 

Thanks again.

[rogerlew]

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.

import csv
fid=open('c:/fahad/new/file.xls','wb')
writer=csv.writer(fid)
for f, load in enumerate(loads):
    writer.writerow([ busname[f],busload[f],mainload[f],difference[f],percent[f] ] )
 
fid.close()
 

Excel will even recognize formulas

writer.writerow([ busname[f],
                  busload[f],
                  mainload[f],
                  difference[f],
                  percent[f],
                  r'=%g/%g'%(busload[f],mainload[f] ])
 

If loads is a list of dictionaries you could try something like:

for load in loads:
    writer.writerow([ load['busname'],
                      load['busload'],
                      load['mainload'],
                      load['difference'],
                      load['percent'] ])
 

Which I think is the most pythonic solution.

Just a friendly suggestion,
Roger

[bartonc]

Very nice, Roger. Thanks.

Then once the .xls file is created one could:

import os
os.startfile(r'c:/fahad/new/file.xls')

Which (I believe) works with either Excel or OpenOffice.