Adding Worksheets to an Excel Workbook
e.h.do...@accenture.com
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.
How do you add a Worksheet to a Workbook?
Thanks!
John Machin
To find out how to do things, you can:
(1) use the VBA help in Excel.
You would find (eventually):
"""
Add method as it applies to the Sheets and Worksheets objects.
Creates a new worksheet, chart, or macro sheet. The new worksheet
becomes the active sheet.
expression.Add(Before, After, Count, Type)
expression Required. An expression that returns one of the above
objects.
Before Optional Variant. An object that specifies the sheet before
which the new sheet is added.
After Optional Variant. An object that specifies the sheet after
which the new sheet is added.
Count Optional Variant. The number of sheets to be added. The default
value is one.
Type Optional Variant. Specifies the sheet type. Can be one of the
following XlSheetType constants: xlWorksheet, xlChart,
xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. If you are inserting a
sheet based on an existing template, specify the path to the template.
The default value is xlWorksheet.
Remarks
If Before and After are both omitted, the new sheet is inserted before
the active sheet.
"""
so,
your_handle.Worksheets.Add()
looks like what you need.
(2) Again in Excel, use the "record a macro" facility: turn on
recording, do your thing, stop recording, inspect the generated macro.
In this case, this gave
Sheets.Add
which you translate to
your_handle.Sheets.Add()
What's the difference between Sheets and Worksheets? I dunno. Try both.
Look in the Excel VBA help.
HTH,
John
Mark Elston
more for a recipe.
Just doing a Google search of python + excel I got the following which
make some good starting points:
http://www.markcarter.me.uk/computing/python/excel.html
http://mail.python.org/pipermail/python-list/2003-September/183367.html
http://mathieu.fenniak.net/plotting-in-excel-through-pythoncom/
There are lots of others.
Mark
greg.rb
from win32com.client import Dispatch
xlApp = Dispatch("Excel.Application")
xlApp.Visible=1 #show me excel
xlApp.Workbooks.Add() #add a workbook
for r in range(1,5): #put data into spreadsheet row/column
xlApp.Cells(r,r).Value=r
for r in range(1,5): #read data from sheet
print xlApp.Cells(r,r).Value
xlApp.Worksheets.Add()#add another sheet in same workbook.
for r in range(1,11): #put data into spreadsheet
xlApp.Cells(1,r).Value=r #first row this tome
#xlApp.ActiveWorkbook.Close()
#xlApp.Quit
Good luck.
Here is a free Excel Object Model Overview:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrconexcelobjectmodeloverview.asp
e.h.do...@accenture.com
wesley chun
> Date: Tues, Oct 10 2006 2:08 pm
>
> I'm a Python newbie, and I'm just getting to the wonders of COM
> programming.
welcome to Python!! i too, have (recently) been interested in COM
programming, so much so that i added some material on Microsoft Office
(Win32 COM Client) Programming to the 2nd ed of my book, "Core Python
Programming" (see link below). it's only introductory material, but i
think you may find it useful as i have, and shows you how to create
simple applications for Excel, Word, PowerPoint, and Outlook.
in addition to greg's code snippet, here's a snippet based on one from
the book (the code is under a CC license) -- it doesn't add a new
sheet, but does let you grab the "active" one (the one that is tabbed
and facing the user):
# based on excel.pyw in Core Python Programming, 2nd ed
from time import sleep
import win32com.client as win32
def excel():
xl = win32.gencache.EnsureDispatch('Excel.Application')
ss = xl.Workbooks.Add() # add a new spreadsheet/workbook
sh = ss.ActiveSheet # grab the active sheet of the workbook
xl.Visible = True # make Excel show up on the desktop
sleep(1)
sh.Cells(1,1).Value = 'Python-to-Excel Demo'
sleep(1)
for i in range(3, 8):
sh.Cells(i,1).Value = 'Line %d' % i
sleep(1)
sh.Cells(i+2,1).Value = "Th-th-th-that's all folks!"
sleep(5)
ss.Close(False) # close the workbook and don't save
xl.Application.Quit() # quit Excel
if __name__=='__main__':
excel()
hope this helps!
-wesley
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
"Core Python Programming", Prentice Hall, (c)2007,2001
http://corepython.com
wesley.j.chun :: wescpy-at-gmail.com
python training and technical consulting
cyberweb.consulting : silicon valley, ca
http://cyberwebconsulting.com
Fabian Braennstroem
just a small OT question coming from a linux openoffice
system...
Does there exist something similar for powerpoint? Would be
nice, if anybody can direct me to more examples...
Greetings!
Fabian
wesley chun
> system...
>
> nice, if anybody can direct me to more examples...
fabian,
see below for a PP example. you mentioned you were coming from a
linux OOo system... are you trying to do COM stuff with OOo? i'm not
familiar with it, but since they do support
some level of VB-like scripting, i don't believe it's out of the question.
one thing that i did forget to mention in my earlier message is that i
use static dispatch for these apps. if you did not go and run the
makepy utility, you would have to use dynamic dispatch, start your
apps with win32com.client.Dispatch(), or, using the same import
statement as below, win32.Dispatch().
anyway, here is pretty much the same script as i sent earlier but for
PowerPoint instead of Excel (the book has small examples for each of
Excel, Word, PowerPoint, and Outlook):
# based on ppoint.pyw in Core Python Programming, 2nd ed
from time import sleep
import win32com.client as win32
def ppoint():
ppoint = win32.gencache.EnsureDispatch('PowerPoint.Application')
pres = ppoint.Presentations.Add()
ppoint.Visible = True
s1 = pres.Slides.Add(1, win32.constants.ppLayoutText)
sleep(1)
s1a = s1.Shapes[0].TextFrame.TextRange
s1a.Text = 'Python-to-PowerPoint Demo'
sleep(1)
s1b = s1.Shapes[1].TextFrame.TextRange
for i in range(3, 8):
s1b.InsertAfter("Line %d\r\n" % i)
sleep(1)
s1b.InsertAfter("\r\nTh-th-th-that's all folks!")
sleep(5)
pres.Close()
ppoint.Quit()
if __name__=='__main__':
ppoint()
HTH!
-- wesley
Fabian Braennstroem
* wesley chun <wes...@gmail.com> wrote:
Thanks! I am not able to try it out yet, but as soon as I
get access to my windows machine, I'll give it a try.
Greetings!
Fabian
Tom Plunket
> welcome to Python!! i too, have (recently) been interested in COM
> programming, so much so that i added some material...
> from time import sleep
> import win32com.client as win32
>
> def excel():
> xl = win32.gencache.EnsureDispatch('Excel.Application')
> ss = xl.Workbooks.Add() # add a new spreadsheet/workbook
> sh = ss.ActiveSheet # grab the active sheet of the workbook
> xl.Visible = True # make Excel show up on the desktop
> sleep(1)
>
> sh.Cells(1,1).Value = 'Python-to-Excel Demo'
> sleep(1)
> for i in range(3, 8):
> sh.Cells(i,1).Value = 'Line %d' % i
> sleep(1)
> sh.Cells(i+2,1).Value = "Th-th-th-that's all folks!"
>
> sleep(5)
> ss.Close(False) # close the workbook and don't save
> xl.Application.Quit() # quit Excel
You've got a lot of sleep calls in there- did you find that things
behaved erratically without them? I haven't done any Office
automation with Python, but my DevStudio stuff has always worked a
treat without the sleep calls.
-tom!
wesley chun
> Date: Tues, Oct 17 2006 6:34 pm
>
> You've got a lot of sleep calls in there- did you find that things
> behaved erratically without them? I haven't done any Office
> automation with Python, but my DevStudio stuff has always worked a
> treat without the sleep calls.
sorry, i forgot to explain this in my previous post. the sleep()s are
not required and are used purely for demonstration purposes, to slow
things down so that you can see things happen live in the application.
it's no fun if Excel opens and everything is already there!
cheers,