Automating Windows Applications with win32com - PowerPoint PPT Presentation

Loading...

PPT – Automating Windows Applications with win32com PowerPoint presentation | free to view - id: 974b7-YzE2Y



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Automating Windows Applications with win32com

Description:

Make a computer do it. Why access existing ... Make an Excel spreadsheet from ... Make a Word document from a database 5 min. Run Python commands ... – PowerPoint PPT presentation

Number of Views:4620
Avg rating:3.0/5.0
Slides: 134
Provided by: royh5
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Automating Windows Applications with win32com


1
Automating Windows Applications with win32com
  • Roy H. Han
  • Python Conference 2008
  • Chicago

2
Next thirty minutes Intro
  • Why 3 min
  • Setup your computer 3 min
  • Icebreaker 7 min
  • Applications via COM 15 min
  • Overview tutorial projects 2 min

3
Why access existing applications through Python?
  • When your clients say,
  • I wish (my application) could do
  • I dont have time to learn (your application)
  • I am more familiar using (my application)

People will use your work if it integrates
with applications with which they are comfortable
4
Why access existing applications through Python?
  • Usability
  • People will use your work if it integrates with
  • applications with which they are comfortable.
  • Shoulders of Giants
  • Combine features from multiple applications.
  • Less Clicking and Typing
  • Make a computer do it.

5
Make your computer do it
  • Create a report from a database
  • Process email
  • Extract data from an application file
  • Populate a database

6
Icebreaker Your expectations
  • While you are setting up your laptops, lets go
    around the room and introduce ourselves
  • (Thirty seconds each)
  • Your name
  • A little about yourself
  • A problem you hope to solve using what you learn
    from this tutorial

7
Setup your computer
  • Install win32com
  • http//sourceforge.net/projects/pywin32/
  • Extract tutorial code package
  • Launch IDLE
  • import sys sys.path.append(ryourCodePath)
  • import os os.chdir(ryourCodePath)
  • Import win32com
  • from win32com.client import Dispatch

8
Which applications can we access through Python?
  • Applications with COM interfaces
  • using Mark Hammonds
  • Python for Windows Extensions
  • http//sourceforge.net/projects/pywin32/

9
What do we need to automate an application?
  • ProgID
  • To dispatch the application
  • COM documentation
  • To use methods from the application

10
How do I know if my target application has a COM
interface?
  • Find the ProgID corresponding to your target
    application
  • Start gt Run gt regedit.exe gt Edit gt Find gt Excel
  • Try connecting to your target application
  • from win32com.client import Dispatch
  • Examine your target application using a COM
    browser
  • /Python25/Lib/site-packages/win32com/client/combr
    owse.py

11
How do I know if my target application has a COM
interface?
  • Find the ProgID corresponding to your target
    application
  • Start gt Run gt regedit.exe gt Edit gt Find gt Excel
  • VersionIndependentProgID Excel.Application
  • Try connecting to your target application
  • from win32com.client import Dispatch
  • Dispatch(Excel.Application)
  • Examine your target application using a COM
    browser
  • /Python25/Lib/site-packages/win32com/client/combr
    owse.py
  • Microsoft Excel 9.0 Object Library

12
Which applications can we access through COM?
  • Get a partial list of ProgIDs using WBEM
  • Pros Fast
  • Cons Partial
  • Cycle through the registry and try to dispatch
    each ProgID using a script
  • Pros Complete
  • Cons Very Slow

13
Get a partial list of ProgIDsusing WBEM
  • Filename getProgIDsFromWBEM.py
  • Dispatch WBEM
  • Extract progIDs
  • Record results
  • Adapted from
  • http//win32com.goermezer.de/content/view/154/18
    9/

14
Get a partial list of ProgIDsusing WBEM
  • Filename getProgIDsFromWBEM.py
  • Dispatch WBEM
  • wbem Dispatch('WbemScripting.SWbemLocator')
  • wbemServer wbem.ConnectServer('.',
    'root\cimv2')
  • Extract progIDs
  • sql 'SELECT ProgID FROM Win32_ProgIDSpecificatio
    n'
  • results wbemServer.ExecQuery(sql)
  • progIDs x.ProgID for x in results
    progIDs.sort()
  • Record results
  • outputFile open('progIDs_partialFromWBEM.txt',
    'wt')
  • outputFile.write('\n'.join(progIDs))
  • outputFile.close()

15
Cycle through the registry and try to dispatch
each ProgID
  • Filename getProgIDsFromRegistry.py
  • Import other modules
  • Initialize list of keys of search
  • While we have keysToSearch,
  • Get key
  • For each subkey,
  • Extract progIDs
  • Recurse children
  • Print categories
  • Sort progIDs
  • Try to open each progID in a separate process

16
Cycle through the registry and try to dispatch
each ProgID
  • Filename getProgIDsFromRegistry.py
  • Import other modules
  • from _winreg import
  • Initialize list of keys of search
  • keysToSearch
  • HKEY_CLASSES_ROOT,
  • OpenKey(HKEY_CURRENT_USER, 'Software'),
  • OpenKey(HKEY_LOCAL_MACHINE, 'Software'),
  • categories set() progIDs set()

17
Cycle through the registry and try to dispatch
each ProgID
  • While we have keysToSearch,
  • while keysToSearch
  • Get key
  • key keysToSearch.pop() count_subkeys
    QueryInfoKey(key)0
  • For each subkey,
  • for index in xrange(count_subkeys)
  • subkeyName EnumKey(key, index)
  • try subkey OpenKey(key, subkeyName)
  • except WindowsError print 'Could not
    access registry key', subkeyName
  • subkeyNameLower subkeyName.lower()
  • Extract progIDs
  • if 'progid' in subkeyNameLower
  • categories.add(subkeyNameLower)
  • if 'open' in subkeyNameLower
  • count_values QueryInfoKey(subkey
    )0
  • for valueIndex in xrange(1,
    count_values)
  • progIDs.add(EnumValue(subkey,
    index)0)
  • else progIDs.add(EnumValue(subkey,
    0)1)
  • Recurse children

18
Cycle through the registry and try to dispatch
each ProgID
  • Print categories
  • print 'Categories ' ', '.join(categories)
  • Sort progIDs
  • progIDs list(progIDs)
  • progIDs.sort()
  • Try to open each progID in a separate process
  • import killableprocess Courtesy of Peter
    Astrand and Benjamin Smedberg
  • progIDs_dispatchable
  • outputFile open('progIDs_completeFromRegistry.tx
    t', 'wt')
  • for progID in progIDs
  • print 'd/d s' (progIDs.index(progID),
    len(progIDs), progID),
  • returnCode killableprocess.call('python
    dispatch.py ' progID, timeout30)
  • if not returnCode
  • print 'yes'
  • outputFile.write(progID '\n')
  • outputFile.flush()
  • else print 'no'

19
Cycle through the registry and try to dispatch
each ProgID
  • Filename dispatch.py
  • Import modules
  • Try to dispatch the specified ProgID
  • If we have an error, exit with a negative value

20
Cycle through the registry and try to dispatch
each ProgID
  • Filename dispatch.py
  • Import modules
  • from win32com.client import Dispatch
  • import sys, pywintypes
  • Try to dispatch the specified ProgID
  • try x Dispatch(sys.argv1) del x
  • If we have an error, exit with a negative value
  • except pywintypes.com_error sys.exit(-1)

21
ProgIDs for some applications
  • Skype4COM.Skype
  • Excel.Application
  • Word.Application
  • Lotus.NotesSession
  • MAPI.Session
  • Outlook.Application
  • Vim.Application
  • Matlab.Application
  • SAPI.SpVoice
  • SAPI.SpSharedRecognizer
  • Speech.VoiceCommand
  • Python.Interpreter
  • Access.Application
  • GoogleEarth.ApplicationGE
  • EsriGeoprocessing.GpDispatch
  • AcroExch.App
  • WMPlayer.OCX
  • PowerPoint.Application
  • Nero.Nero
  • Visio.Application
  • Eudora.EuApplication
  • InternetExplorer.Application
  • AutoCAD.Application
  • WbemScripting.SWbemLocator
  • WScript.Shell
  • OmniPage14
  • Intuit.Quicken
  • Bloomberg.Data

22
How do we know what methods and attributes are
available?
  • Find COM documentation for the target application
  • Run makepy
  • /Python25/Lib/site-packages/win32com/client/makep
    y.py
  • See hints (CTRL-space in IDLE, Tab in IPython)
  • You can use program constants
  • Your COM interface runs a little faster
  • Examine your target application using a COM
    browser
  • /Python25/Lib/site-packages/win32com/client/combr
    owse.py

23
Review What do we need to automate an
application?
  • ProgID
  • To dispatch the application
  • COM documentation
  • To use methods from the application

24
Mini Projects Tutorial Format
  • My demo
  • We pseudocode
  • We code through IDLE
  • Your demo

25
Basics Mail Events Add-Ins More
  • Make an Excel spreadsheet from a database
  • Update a database using an Excel spreadsheet
  • Make a Word document from a database
  • Run Python commands through Vim
  • Get contacts from Outlook

26
Basics Mail Events Add-Ins More
  • Update a database using Outlook
  • Reply to email with Outlook
  • Reply to email with Lotus Notes

27
Basics Mail Events Add-Ins More
  • Make a toolbar button in Excel
  • Run Python commands from an Excel spreadsheet
  • Update a database using an Excel spreadsheet in
    real-time

28
Basics Mail Events Add-Ins More
  • Write an add-in for Excel
  • Write an add-in for Word
  • Write an add-in for Outlook

29
Basics Mail Events Add-Ins More
  • Speak using SpVoice
  • Call using Skype
  • Convert a document to PDF
  • Do calculations in Matlab
  • Clip images from shapefiles through ArcGIS
  • Extract data from an Access database
  • Move your mouse

30
Next thirty minutes Basics
  • Make an Excel spreadsheet from a database
    10 min
  • Update a database using an Excel spreadsheet
    5 min
  • Make a Word document from a database
    5 min
  • Run Python commands through Vim
    5 min
  • Get contacts from Outlook
    5 min

31
Make an Excel spreadsheet from a database
  • Get results from database
  • Dispatch Excel
  • Put results into cells
  • Format cells
  • Release Excel to prevent hanging instances

32
Make an Excel spreadsheet from a database
  • Get results from database
  • from model_database import Model
  • database Model()
  • results database.seeInvoices()

33
Make an Excel spreadsheet from a database
  • Dispatch Excel
  • from win32com.client import Dispatch, constants
  • excel Dispatch('Excel.Application')
  • excel.Visible 1
  • excel.WindowState constants.xlMaximized
  • excel.Workbooks.Add()

34
Make an Excel spreadsheet from a database
  • Make shortcut
  • sheet excel.ActiveSheet
  • Add title
  • cell sheet.Cells(1, 1)
  • cell.Value 'Invoices'
  • cell.Font.Size 20
  • cell.Font.Bold True

35
Make an Excel spreadsheet from a database
  • Put results into cells
  • def drawText(row, items, isBold False)
  • column 1
  • for item in items
  • cell sheet.Cells(row, column)
  • cell.Value item
  • cell.Font.Bold isBold
  • column 1
  • return row 1
  • row 3
  • for x in results row drawText(row, x)

36
Make an Excel spreadsheet from a database
  • Put results into cells
  • formulaString 'sum(cdcd)' (3, row - 1)
  • items '', 'Total', formulaString
  • row drawText(row, items, True)
  • Insert header
  • sheet.Rows(3).Insert(constants.xlDown)
  • drawText(3, 'Invoice ', 'Date', 'Amount',
    'City', 'Paid', True)

37
Make an Excel spreadsheet from a database
  • Format cells
  • sheet.Columns(2).NumberFormat 'mmm d, yyyy'
  • sheet.Columns(3).NumberFormat '0.00'
  • sheet.Columns(5).NumberFormat '0.00'
  • sheet.Columns(4).AutoFit()
  • sheet.Rows(3).HorizontalAlignment
    constants.xlCenter
  • Center title
  • sheet.Range('A1E1').Merge()
  • sheet.Rows(1).HorizontalAlignment
    constants.xlCenter
  • sheet.Name 'Invoices
  • Release Excel to prevent hanging instances
  • del excel

38
Update a databaseusing an Excel spreadsheet
  • Dispatch Excel
  • Load rows from spreadsheet
  • Update entries in database
  • Release Excel

39
Update a databaseusing an Excel spreadsheet
  • Dispatch Excel
  • excel Dispatch(Excel.Application)

40
Update a databaseusing an Excel spreadsheet
  • Load line from spreadsheet
  • line excel.ActiveSheet.Rows(7).Value05
  • line list(line)
  • Note that dates are returned as PyTime
  • date line1
  • date
  • Convert PyTime to datetime
  • import datetime
  • line1 datetime.date.fromtimestamp(int(date))

41
Update a databaseusing an Excel spreadsheet
  • Update entries in database
  • database.editInvoice(line)
  • Get results from database
  • invoiceNumber line0
  • database.seeInvoice(invoiceNumber)
  • Release Excel to prevent hanging instances
  • del excel

42
Make a Word documentfrom a database
  • Dispatch Word
  • Add cities to document
  • Release Word

43
Make a Word documentfrom a database
  • Dispatch Word
  • word Dispatch(Word.Application)
  • word.Visible 1
  • word.Documents.Add()
  • document word.ActiveDocument
  • Add cities to document
  • cities list(set(x3 for x in results))
  • cities.sort()
  • for x in cities
  • document.Content.InsertAfter(x \n)
  • Release Word
  • del word

44
Run Python commands from Vim
  • Connect to Vim
  • Send keys to Vim
  • Get current line from Vim
  • Connect to Python
  • Execute and evaluate line in Python
  • Create Vim proxy using wxPython and wxGlade

45
Run Python commands from Vim
  • Connect to Vim
  • vim Dispatch('Vim.Application')
  • Send keys to Vim
  • vim.SendKeys(ac 6)
  • Get current line from Vim
  • line vim.Eval(getline(1))
  • Release Vim
  • del vim

46
Run Python commands from Vim
  • Connect to Python
  • python Dispatch('Python.Interpreter')
  • Execute line
  • python.Exec(line)
  • Evaluate
  • python.Eval(c)
  • Release Python
  • del python
  • You can use wxPython wxGlade to
  • build a Vim interface to Python

47
Run Python commands from Vim with wxPython
wxGlade
  • wx.EVT_CHAR(self.text_ctrl, self.onKey)
  • def onKey(self, event)
  • Get the most recent key
  • recentKey chr(event.GetKeyCode())
  • self.text_ctrl.SetValue(recentKey)
  • Send key to Vim
  • vim.SendKeys(recentKey)

48
Run Python commands from Vim with wxPython
wxGlade
  • def onClick(self, event)
  • Get current line from Vim
  • line vim.Eval('getline(".")')
  • Send line to Python
  • python.Exec(line)

49
Run Python commands from Vim with wxPython
wxGlade
  • import wx
  • from win32com.client import Dispatch
  • vim Dispatch('Vim.Application')
  • python Dispatch('Python.Interpreter')

50
Run Python commands from Vim with wxPython
wxGlade
  • class MyFrame(wx.Frame)
  • def __init__(self, args, kwds)
  • begin wxGlade MyFrame.__init__
  • kwds"style" wx.DEFAULT_FRAME_STYLE
  • wx.Frame.__init__(self, args, kwds)
  • self.text_ctrl wx.TextCtrl(self, -1,
    "", stylewx.TE_PROCESS_ENTERwx.TE_PROCESS_TAB)
  • self.button wx.Button(self, -1, "Run")
  • self.__set_properties()
  • self.__do_layout()
  • self.Bind(wx.EVT_BUTTON, self.onClick,
    self.button)
  • end wxGlade
  • wx.EVT_CHAR(self.text_ctrl, self.onKey)

51
Run Python commands from Vim with wxPython
wxGlade
  • def __set_properties(self)
  • begin wxGlade MyFrame.__set_properties
  • self.SetTitle("Python from Vim")
  • end wxGlade
  • def __do_layout(self)
  • begin wxGlade MyFrame.__do_layout
  • sizer_1 wx.BoxSizer(wx.HORIZONTAL)
  • sizer_1.Add(self.text_ctrl, 1, wx.EXPAND,
    0)
  • sizer_1.Add(self.button, 0, 0, 0)
  • self.SetSizer(sizer_1)
  • sizer_1.Fit(self)
  • self.Layout()
  • end wxGlade

52
Run Python commands from Vim with wxPython
wxGlade
  • def onKey(self, event)
  • Get the most recent key
  • recentKey chr(event.GetKeyCode())
  • self.text_ctrl.SetValue(recentKey)
  • Send key to Vim
  • vim.SendKeys(recentKey)
  • def onClick(self, event)
  • Get current line from Vim
  • line vim.Eval('getline(".")')
  • Send line to Python
  • python.Exec(line)

53
Run Python commands from Vim with wxPython
wxGlade
  • if __name__ "__main__"
  • app wx.PySimpleApp(0)
  • wx.InitAllImageHandlers()
  • frame_1 MyFrame(None, -1, "")
  • app.SetTopWindow(frame_1)
  • frame_1.Show()
  • app.MainLoop()

54
Get contacts from Outlook
  • Connect to Outlook
  • Open contacts folder
  • Extract contacts
  • Release Outlook

55
Get contacts from Outlook
  • Connect to Outlook
  • outlook Dispatch(Outlook.Application)
  • ns outlook.GetNamespace('MAPI')
  • ns.Logon()
  • Open contacts folder
  • x constants.olFolderContacts
  • folder ns.GetDefaultFolder(x)

56
Get contacts from Outlook
  • Extract contacts
  • items folder.Items
  • count Items.Count
  • for index in xrange(1, count 1)
  • item itemsindex
  • value item.FullName, item.EmailAddress
  • print s (s) value
  • Release Outlook
  • ns.Logoff()
  • del outlook

57
Explore handles
  • import win32gui
  • def callback(handle, results)
  • klass win32gui.GetClassName(handle)
  • title win32gui.GetWindowText(handle)
  • results.append((title, klass))
  • def showChildren(handle)
  • results
  • win32gui.EnumChildWindows(handle, callback,
    results)
  • results.sort()
  • for x in results print x
  • showChildren(0)

58
Explore dialog boxes
  • klass '32770'
  • title 'Microsoft Outlook'
  • handle_dialog win32gui.FindWindow(klass, title)
  • if handle_dialog showChildren(handle_dialog)
  • else print The dialog is not open

59
Interact with dialog boxes
  • def clickHandle(handle)
  • win32gui.SendMessage(handle,
    win32con.WM_ACTIVATE, win32con.MA_ACTIVATE, 0)
  • win32gui.SendMessage(handle,
    win32con.BM_CLICK, 0, 0)
  • h handle_dialog
  • k 'Button
  • t 'Allow access for'
  • handle_button win32gui.FindWindowEx(h, 0, k, t)
  • clickHandle(handle_button)

60
Bypass the Outlook security dialog box
  • Thanks to Samy http//www.lucidx.com/outsmart.tx
    t
  • Thanks to Misha Verplak http//mail.python.org/p
    ipermail/python-win32/2002-December/000635.html
  • import win32gui, win32con, win32api
  • def clickHandle(h)
  • win32gui.SendMessage(h, win32con.WM_ACTIVATE,
    win32con.MA_ACTIVATE, 0)
  • win32gui.SendMessage(h, win32con.BM_CLICK, 0,
    0)

61
Bypass the Outlook security dialog box
  • def autoclick()
  • Get h_dialog
  • h_dialog win32gui.FindWindow('32770',
    'Microsoft Outlook')
  • if not h_dialog return
  • Click Allow
  • h_button win32gui.FindWindowEx(h_dialog, 0,
    'Button', 'Allow access for')
  • clickHandle(h_button)

62
Bypass the Outlook security dialog box
  • Select 10 minutes
  • h_combobox win32gui.FindWindowEx(h_dialog,
    0, 'ComboBox', '')
  • win32gui.SendMessage(h_combobox,
    win32con.CB_SELECTSTRING, -1, '10 minutes')
  • Save mouse position
  • x, y win32api.GetCursorPos()

63
Bypass the Outlook security dialog box
  • For security, Outlook ignores mouse events
  • unless you click in the area of the dialog
  • coords win32gui.GetWindowRect(h_dialog)
  • x1, y1 coords2
  • win32api.SetCursorPos((x1 10, y1 10))
  • method win32api.mouse_event
  • method(win32con.MOUSEEVENTF_LEFTDOWN,0,0,0,0)
  • method(win32con.MOUSEEVENTF_LEFTUP,0,0,0,0)

64
Bypass the Outlook security dialog box
  • Click Yes
  • h_button_yes win32gui.FindWindowEx(h_dialog,
    0, 'Button', 'Yes')
  • clickHandle(h_button_yes)
  • Restore mouse position
  • win32api.SetCursorPos(x, y)

65
Bypass the Outlook security dialog box
  • import threading, time
  • class Autoclicker(threading.Thread)
  • def run(self)
  • while True
  • time.sleep(1)
  • try autoclick()
  • except pass
  • if __name__ '__main__'
  • autoclicker Autoclicker()
  • autoclicker.start()

66
Catching COM errors
  • import pywintypes
  • try
  • Your COM code
  • pass
  • except pywintypes.com_error
  • Your COM error handling
  • pass

67
Next thirty minutes Mail
  • Update a database using Outlook
    10 min
  • Reply to email using Outlook
    10 min
  • Reply to email using Lotus Notes
    10 min

68
Update a database using Outlook
  • Connect to Outlook
  • Open inbox folder
  • Append inbox subfolders recursively
  • For each folder,
  • For each message,
  • If the subject of the message matches,
  • Update database
  • Release Outlook

69
Update a database using Outlook
  • Connect to Outlook
  • outlook Dispatch(Outlook.Application)
  • ns outlook.GetNamespace('MAPI')
  • ns.Logon()

70
Update a database using Outlook
  • Open inbox folder
  • x constants.olFolderInbox
  • inbox ns.GetDefaultFolder(x)
  • Examine a message
  • messages inbox.Items
  • message messages1
  • print message.SenderName
  • print message.SentOn.Format()

71
Update a database using Outlook
  • Append inbox subfolders recursively
  • def getSubFolders(parentFolder)
  • count parentFolder.Folders.Count subFolders
  • if count
  • for whichFolder in xrange(1, count 1)
  • subFolder folderwhichFolder
  • subFolders.append(subFolder)
  • subFolders.extend(getSubfolders(subFolder
    ))
  • return subFolders
  • Append inbox subfolders recursively
  • folders inbox
  • folders.extend(getSubFolders(inbox))

72
Update a database using Outlook
  • For each folder,
  • for folder in folders
  • For each message,
  • message folder.Items.GetFirst()
  • while message
  • If the subject of the message matches,
  • if message.Subject Add Me
  • Update database
  • database.addText(message.Body)
  • Get next message
  • message messages.GetNext()
  • Release Outlook
  • ns.Logoff() del outlook

73
Reply to email with Outlook
  • Connect to Outlook
  • Open inbox folder
  • For each message,
  • Extract query terms from subject
  • Execute query
  • Write email with attachments
  • Release Outlook

74
Reply to email with Outlook
  • Connect to Outlook
  • outlook Dispatch(Outlook.Application)
  • ns outlook.GetNamespace('MAPI')
  • ns.Logon()
  • Open inbox folder
  • x constants.olFolderInbox
  • inbox ns.GetDefaultFolder(x)

75
Reply to email with Outlook
  • Prepare regular expression
  • import re pattern_query re.compile(\?(.))
  • Prepare writeReply
  • def writeReply(toWhom, subject)
  • write(toWhom, Re subject, Hi)
  • Prepare move
  • def move(destinationFolder, message)
  • message.Move(destinationFolder)

76
Reply to email with Outlook
  • def write(to, subj, body, attachments )
  • x constants.olMailItem
  • m outlook.CreateItem(x)
  • m.To to m.Subject subj m.Body body
  • for attachment in attachments
  • p attachment, constants.olByValue, 1
  • m.Attachments.Add(p)
  • message.Send()

77
Reply to email with Outlook
  • For each message,
  • messages inbox.Items
  • m messages.GetFirst()
  • while m
  • Test the subject for a query
  • match pattern_query.match(m.Subject)
  • If we have a match, reply
  • if match writeReply(m.SenderName, m.Subject)
  • Get next message
  • m messages.GetNext()
  • ns.Logoff() del a Release Outlook

78
Reply to email with Lotus Notes
  • Connect to Lotus Notes
  • Open inbox folder
  • For each message,
  • Extract query terms from subject
  • Execute query
  • Write email with attachments
  • Release Lotus Notes

79
Reply to email with Lotus Notes
  • Connect to Lotus Notes
  • import pywintypes
  • notes Dispatch('Lotus.NotesSession')
  • try
  • notes.Initialize(dbPassword)
  • notesDB notes.GetDatabase(dbHost, dbName)
  • Open inbox folder
  • inbox notesDB.GetView('(Inbox)')
  • except pywintypes.com_error raise

80
Reply to email with Lotus Notes
  • def write(to, subj, htmlBody, attachments )
  • notes.ConvertMime False Prevent rich text
  • m notesDB.CreateDocument()
  • m.ReplaceItemValue('SendTo', to)
  • m.ReplaceItemValue('Subject', subj)
  • stream notes.CreateStream()
  • stream.WriteText(htmlBody)
  • m_body m.CreateMIMEEntity()
  • strHead 'text/html charsetutf-8
  • parcel stream, strHead, constants.ENC_NONE
  • m_body.SetContentFromText(parcel)

81
Reply to email with Lotus Notes
  • Add attachments
  • for index in xrange(len(attachments))
  • attach attachmentsindex
  • if not attach continue
  • strID 'attachmentd' index
  • m_attach m.CreateRichTextItem(strID)
  • x constants.EMBED_ATTACHMENT
  • m_attach.EmbedObject(x, '', attach, None)
  • message.Send(False)
  • notes.ConvertMime True Restore setting

82
Reply to email with Lotus Notes
  • def move(destinationFolderName, message)
  • message.PutInFolder(destinationFolderName)
  • message.RemoveFromFolder('(Inbox)')
  • Prepare writeReply
  • def writeReply(toWhom, subject)
  • html 'lthtmlgtltbodygtHeylt/bodygtlt/htmlgt
  • write(toWhom, Re subject, html)

83
Reply to email with Lotus Notes
  • For each message,
  • m inbox.GetFirstDocument()
  • while m
  • fromWhom m.GetItemValue('From')0.strip()
  • subj m.GetItemValue('Subject')0.strip()
  • Test the subject for a query
  • match pattern_query.match(subj)
  • If we have a match, reply
  • if match writeReply(fromWhom, subj)
  • Get next message
  • m inbox.GetNextDocument(m)

84
Next thirty minutes Events
  • Make a toolbar button in Excel
    10 min
  • Update a database
  • from an Excel spreadsheet in real-time
    10 min
  • Run Python commands from an Excel spreadsheet
    10 min

85
Make a toolbar button in Excel
  • Dispatch Excel
  • excel Dispatch(Excel.Application)
  • Add a toolbar
  • toolbar excel.CommandBars.Add(
  • Name myToolbar',
  • Position constants.msoBarTop,
  • MenuBar constants.msoBarTypeNormal,
  • Temporary True)
  • toolbar.Visible True

86
Make a toolbar button in Excel
  • Define button handler
  • class ButtonEvent(object)
  • def OnClick(self, button, cancel)
  • import win32ui
  • import win32con
  • win32ui.MessageBox('Welcome',
  • 'Message', win32con.MB_OK)
  • return cancel

87
Make a toolbar button in Excel
  • Add a button to the toolbar
  • button toolbar.Controls.Add(1)
  • button DispatchWithEvents(button,
  • ButtonEvent)
  • button.Width 34
  • button.Caption Welcome
  • button.Style constants.msoButtonCaption

88
Debugging using win32traceutil
  • import win32traceutil
  • Run PythonWin
  • gt Tools gt Trace Collector Debugging Tool
  • before trying the next statement
  • print welcome

89
Update database from an Excel spreadsheet in
real-time
  • Define event handler
  • Dispatch Excel with event handler
  • Listen for events
  • If we have an event,
  • Get row and update database using row
  • Give feedback in a cell of the row
  • Adapted from /Python25/Lib/site-packages/win32co
    m/test/testMSOfficeEvents.py
  • Courtesy of Mark Hammond

90
Update database from an Excel spreadsheet in
real-time
  • Define event handler
  • class EventManager(object)
  • def OnSheetBeforeDoubleClick(self, sheet,
  • target, cancel)
  • print Updating database
  • Update database here
  • pass

91
Update database from an Excel spreadsheet in
real-time
  • Dispatch Excel with event handler
  • from win32com.client import DispatchWithEvents
  • excel DispatchWithEvents('Excel.Application',
    EventManager)
  • excel.Visible 1

92
Update database from an Excel spreadsheet in
real-time
  • Listen for events
  • import threading, pythoncom
  • stopEvent threading.Event()
  • while True
  • pythoncom.PumpWaitingMessages()
  • Necessary so that python doesn't hog CPU
  • stopEvent.wait(.2)
  • if stopEvent.isSet()
  • stopEvent.clear()
  • break

93
Run Python commandsfrom an Excel spreadsheet
  • Import other modules
  • Define event handler
  • Dispatch Python
  • Dispatch Excel with event handler
  • Listen for events
  • Prevent hanging references

94
Run Python commandsfrom an Excel spreadsheet
  • Import other modules
  • from win32com.client import Dispatch,
    DispatchWithEvents, constants
  • from pywintypes import com_error
  • Define event handler
  • class EventManager(object)
  • User double-clicks a cell
  • def OnSheetBeforeDoubleClick(self, sheet,
    target, cancel)
  • inputCell excel.ActiveCell
  • inputValue str(inputCell.Value)
  • if inputValue
  • outputCell excel.ActiveSheet.Cells(i
    nputCell.Row, inputCell.Column 1)
  • Try executing it
  • try python.Exec(inputValue)
  • except com_error, err
    outputCell.Value str(err)

95
Run Python commandsfrom an Excel spreadsheet
  • Dispatch Python
  • python Dispatch('Python.Interpreter')
  • Dispatch Excel with event handler
  • excel DispatchWithEvents('Excel.Application',
    EventManager)
  • excel.Visible 1
  • excel.Workbooks.Add()
  • excel.ActiveSheet.Cells(1, 1).Value 'Commands'
  • excel.ActiveSheet.Columns('A').ColumnWidth 20
  • excel.ActiveSheet.Cells(1, 2).Value 'Errors'
  • excel.ActiveSheet.Columns('B').ColumnWidth 60
  • excel.ActiveSheet.Columns('B').WrapText True
  • excel.ActiveSheet.Columns('B').HorizontalAlignment
    constants.xlLeft
  • excel.ActiveCell.Offset(2).Activate()

96
Run Python commandsfrom an Excel spreadsheet
  • Listen for events
  • import threading, pythoncom
  • stopEvent threading.Event()
  • while True
  • pythoncom.PumpWaitingMessages()
  • Necessary so that python doesn't hog CPU
  • stopEvent.wait(.2)
  • if stopEvent.isSet()
  • stopEvent.clear()
  • break
  • Prevent hanging references
  • del excel
  • del python

97
Next ten minutes Add-ins
  • Write an add-in for Excel
    8 min
  • Write an add-in for Word
    1 min
  • Write an add-in for Outlook
    1 min

98
How to write an add-in
  • Find a CLSID for your add-in
  • Cut and paste the add-in class definition
  • Define the behavior for your add-in by overriding
    methods in the class definition
  • Register your add-in

99
Write an add-in for Excel
  • Bring COM modules into Python
  • Define add-in class
  • Register add-in
  • Courtesy of Eric Koome

100
Write an add-in for Excel
  • Import other modules
  • from win32com import universal
  • from win32com.client import gencache,
    DispatchWithEvents, constants
  • import pythoncom, sys
  • Bring COM modules into Python
  • gencache.EnsureModule('00020813-0000-0000-C000-00
    0000000046', 0, 1, 3, bForDemandTrue) Excel 9
  • gencache.EnsureModule('2DF8D04C-5BFA-101B-BDE5-00
    AA0044DE52', 0, 2, 1, bForDemandTrue) Office
    9
  • universal.RegisterInterfaces('AC0714F2-3D04-11D1-
    AE7D-00A0C90F26F4', 0, 1, 0, '_IDTExtensibility2
    ')

101
Write an add-in for Excel
  • Define button handler
  • class ButtonEvent(object)
  • def OnClick(self, button, cancel)
  • import win32ui, win32con
  • win32ui.MessageBox('Welcome', 'Message',
    win32con.MB_OK)
  • return cancel
  • Define add-in class
  • class Addin(object)
  • _com_interfaces_ '_IDTExtensibility2'
  • _public_methods_
  • _reg_clsctx_ pythoncom.CLSCTX_INPROC_SERVER
  • _reg_clsid_ '2F1E606F-2A7B-46F9-AF6A-267C00
    36C348'
  • _reg_progid_ 'Python.Test.Addin'
  • _reg_policy_spec_ 'win32com.server.policy.Ev
    entHandlerPolicy'
  • def __init__(self) import win32traceutil
    self.application None

102
Write an add-in for Excel
  • def OnConnection(self, application, connectMode,
    addin, custom)
  • print 'OnConnection', application,
    connectMode, addin, custom
  • try
  • self.application application
  • myToolbar self.application.CommandBa
    rs.Add(Name 'Run Toolbar', Position
    constants.msoBarTop, MenuBar constants.msoBarTyp
    eNormal, Temporary True)
  • myButton myToolbar.Controls.Add(1)
  • self.myButton DispatchWithEvents(myB
    utton, ButtonEvent)
  • self.myButton.Width '34'
  • self.myButton.Caption 'Run'
  • self.myButton.Style
    constants.msoButtonCaption
  • myToolbar.Visible True

103
Write an add-in for Excel
  • except pythoncom.com_error, (hr, msg,
    exc, arg)
  • print 'The Excel call failed with
    code d s' (hr, msg)
  • if exc is None print 'There is no
    extended error information'
  • else
  • wcode, source, text, helpFile,
    helpId, scode exc
  • print 'The source of the error
    is', source
  • print 'The error message is',
    text
  • print 'More info can be found in
    s (idd)' (helpFile, helpId)
  • def OnDisconnection(self, mode, custom)
  • print 'OnDisconnection'
  • self.application.CommandBars('Run
    Toolbar').Delete
  • del self.application
  • def OnAddInsUpdate(self, custom) print
    'OnAddInsUpdate', custom
  • def OnStartupComplete(self, custom) print
    'OnStartupComplete', custom
  • def OnBeginShutdown(self, custom) print
    'OnBeginShutdown', custom

104
Write an add-in for Excel
  • Register add-in
  • def RegisterAddin(klass)
  • import _winreg
  • key _winreg.CreateKey(_winreg.HKEY_CURRENT_U
    SER, 'Software\\Microsoft\\Office\\Excel\\Addins')
  • subkey _winreg.CreateKey(key,
    klass._reg_progid_)
  • _winreg.SetValueEx(subkey, 'CommandLineSafe',
    0, _winreg.REG_DWORD, 0)
  • _winreg.SetValueEx(subkey, 'LoadBehavior', 0,
    _winreg.REG_DWORD, 3)
  • _winreg.SetValueEx(subkey, 'Description', 0,
    _winreg.REG_SZ, 'Run Toolbar')
  • _winreg.SetValueEx(subkey, 'FriendlyName', 0,
    _winreg.REG_SZ, 'Runs the contents of the cell in
    Python')

105
Write an add-in for Excel
  • Unregister add-in
  • def UnregisterAddin(klass)
  • import _winreg
  • try _winreg.DeleteKey(_winreg.HKEY_CURRENT_US
    ER, 'Software\\Microsoft\\Office\\Excel\\Addins\\'
    klass._reg_progid_)
  • except WindowsError pass
  • if __name__ '__main__'
  • import win32com.server.register
  • win32com.server.register.UseCommandLine(Addin)
  • if '--unregister' in sys.argv
  • UnregisterAddin(Addin)
  • else RegisterAddin(Addin)

106
Getting a CLSID for your add-in
  • import pywintypes
  • clsid pywintypes.CreateGuid()
  • class Addin(object)
  • _reg_clsid_ '2F1E606F-2A7B-46F9-AF6A-267C00
    36C348'

107
Write an add-in for WordWrite an add-in for
Outlook
  • Register your add-in in the Word subdirectory
  • 'Software\\Microsoft\\Office\\Word\\Addins
  • And similarly for Outlook
  • 'Software\\Microsoft\\Office\\Outlook\\Addins'

108
Next thirty minutes More
  • Speak using SpVoice
    4 min
  • Call using Skype
    5 min
  • Convert a document to PDF
    5 min
  • Clip images from shapefiles through ArcGIS
    5 min
  • Do calculations in Matlab
    5 min
  • Extract data from an Access database
    3 min
  • Move your mouse
    3 min

109
Speak using SpVoice
  • Connect to SpVoice
  • speech Dispatch('SAPI.SpVoice')
  • Speak
  • speech.Speak('hey')

110
Call using Skype
  • skype Dispatch('Skype4COM.Skype')
  • skype.Client.Start()
  • skype.PlaceCall(18004664411') 1 800 GOOG 411
  • speech Dispatch('SAPI.SpVoice')
  • import time time.sleep(5)
  • speech.Speak(Chicago Illinois)

111
Convert a document to PDF
  • NOSAVE -1 PDSAVEFULL 1
  • avdoc Dispatch('AcroExch.AVDoc')
  • doc avdoc.Open(sourcePath, 'doc2pdf')
  • pddoc avdoc.GetPDDoc()
  • pddoc.Save(PDSAVEFULL, destinationPath)
  • pddoc.Close() del pddoc
  • avdoc.Close(NOSAVE) del avdoc
  • Courtesy of Justin Ezequiel

112
Do calculations in Matlab
  • Dispatch Matlab
  • matlab Dispatch(Matlab.Application)
  • Execute commands
  • matlab.Execute(a 1 2 3 4 5 6 7 8)
  • real, imag matlab.GetFullMatrix('a', 'base')
  • Import numpy
  • array(real)

113
Clip images from shapefiles using ArcGIS
  • Dispatch ArcGIS
  • gp Dispatch(EsriGeoprocessing.GpDispatch')
  • rows gp.SearchCursor(path_points_shapefile)
  • Prepare to clip
  • import Image
  • length_x_halved 10 length_y_halved 10
  • def makeString_rectangle(x, y)
  • return 's s s s' (x length_x_halved,
  • y - length_y_halved, x length_x_halved,
  • y length_y_halved)

114
Clip images from shapefiles using ArcGIS
  • Get row
  • row rows.next()
  • Get coordinates
  • point row.shape.GetPart() x, y point.X,
    point.Y
  • Make rectangle
  • string_rectangle makeString_rectangle(x, y)
  • Clip image around point
  • try gp.Clip_management(path_image,
    string_rectangle,
  • path_temporary)
  • except pywintypes.com_error print
    gp.GetMessages()
  • Open image
  • image Image.open(path_temporary)

115
Extract datafrom an Access database
  • Use ADODB.Connection
  • Refer to http//www.ecp.cc/

116
Move your mouse
  • import random, win32api
  • def jiggle()
  • Get random motion
  • choices (1,0), (-1,0), (0,1), (0,-1)
  • motion random.choice(choices)
  • Get cursor position
  • oldPosition win32api.GetCursorPos()
  • Set cursor position
  • newX oldPosition0 motion0
  • newY oldPosition1 motion1
  • win32api.SetCursorPos((newX, newY))

117
Click (Simple)
  • DOWN win32con.MOUSEEVENTF_LEFTDOWN
  • UP win32con.MOUSEEVENTF_LEFTUP
  • def click(x, y)
  • win32api.SetCursorPos((x, y))
  • win32api.mouse_event(DOWN,0,0,0,0)
  • win32api.mouse_event(UP,0,0,0,0)

118
Type (Simple)
  • def type(vk, scan)
  • win32api.keybd_event(vk, scan, 0, 0)
  • Keycodes
  • http//msdn2.microsoft.com/en-us/library/ms64630
    4(VS.85).aspx

119
Click and Type (Complex)
  • Courtesy of Daniel F
  • http//mail.python.org/pipermail/python-win32/20
    05-April/003131.html
  • from ctypes import
  • PUL POINTER(c_ulong)
  • class KeyBdInput(Structure) _fields_ ('wVk',
    c_ushort), ('wScan', c_ushort), ('dwFlags',
    c_ulong), ('time', c_ulong), ('dwExtraInfo',
    PUL)
  • class HardwareInput(Structure) _fields_
    ('uMsg', c_ulong), ('wParamL', c_short),
    ('wParamH', c_ushort)
  • class MouseInput(Structure) _fields_ ('dx',
    c_long), ('dy', c_long), ('mouseData', c_ulong),
    ('dwFlags', c_ulong), ('time',c_ulong),
    ('dwExtraInfo', PUL)
  • class Input_I(Union) _fields_ ('ki',
    KeyBdInput), ('mi', MouseInput), ('hi',
    HardwareInput)
  • class Input(Structure) _fields_ ('type',
    c_ulong), ('ii', Input_I)
  • class POINT(Structure) _fields_ ('x',
    c_ulong), ('y', c_ulong)

120
Click and Type (Complex)
  • def Click(x, y)
  • orig POINT()
  • windll.user32.GetCursorPos(byref(orig))
  • windll.user32.SetCursorPos(x,y)
  • FInputs Input 2
  • extra c_ulong(0)
  • ii_ Input_I()
  • ii_.mi MouseInput( 0, 0, 0, 2, 0,
    pointer(extra) )
  • ii2_ Input_I()
  • ii2_.mi MouseInput( 0, 0, 0, 4, 0,
    pointer(extra) )
  • x FInputs( ( 0, ii_ ), ( 0, ii2_ ) )
  • windll.user32.SendInput(2, pointer(x),
    sizeof(x0))
  • return orig.x, orig.y

121
Click and Type (Complex)
  • def Type(wVk, wScan)
  • FInputs Input 1
  • extra c_ulong(0)
  • ii_ Input_I()
  • ii_.ki KeyBdInput( wVk, wScan, 0, 0,
    pointer(extra) )
  • x FInputs( ( 1, ii_ ) )
  • windll.user32.SendInput(1, pointer(x),
    sizeof(x0))
  • return windll.kernel32.GetLastError()

122
Reference win32com
  • Dispatch
  • from win32com.client import Dispatch
  • Dispatch with event handling
  • from win32com.client import DispatchWithEvents
  • Catch COM errors
  • from pywintypes import com_error
  • try pass
  • except com_error pass
  • PythonWin gt Tools gt Trace Collector Debugging
    Tool
  • import win32traceutil print hey

123
Reference Microsoft Excel
  • Connect
  • excelApplication win32com.client.Dispatch('Excel
    .Application')
  • Make it visible
  • excelApplication.Visible 1
  • Adjust the application window
  • excelApplication.WindowState win32com.client.con
    stants.xlNormal
  • excelApplication.WindowState win32com.client.con
    stants.xlMinimized
  • excelApplication.WindowState win32com.client.con
    stants.xlMaximized
  • Move the application window
  • excelApplication.Application.Top 50
  • excelApplication.Application.Left 50
  • Resize the application window
  • excelApplication.Application.Width 500
  • excelApplication.Application.Height 500
  • Set the number of sheets in a new workbook
  • excelApplication.SheetsInNewWorkbook 1
  • Add a workbook
  • excelApplication.Workbooks.Add()

124
Reference Microsoft Excel
  • Write a value to a cell
  • row 1 column 2 text 'Welcome'
  • cell excelApplication.ActiveSheet.Cells(row,
    column)
  • cell.Value text cell.Font.Size size
    cell.Font.Bold isBold
  • Read a value from a cell
  • print cell.Value
  • Add a worksheet after the current worksheet
  • excelApplication.Worksheets.Add(After
    excelApplication.ActiveSheet)
  • Choosing rows and columns
  • rowObject excelApplication.ActiveSheet.Rows(1)
  • columnObject excelApplication.ActiveSheet.Column
    s('A')
  • Merge cells in a range
  • excelApplication.ActiveSheet.Range(A1B3).Merge(
    )
  • Format dates
  • cell.NumberFormat 'm/d/yyyy'
  • Wrap text
  • rowObject.WrapText True
  • Autofit cells
  • rowObject.AutoFit()

125
Reference Microsoft Excel
  • Align cells
  • cell.VerticalAlignment win32com.client.constants
    .xlVAlignTop
  • cell.HorizontalAlignment win32com.client.constan
    ts.xlRight
  • Change page orientation
  • sheet excelApplication.ActiveSheet
  • sheet.PageSetup.Orientation xlLandscape
  • sheet.PageSetup.PaperSize xlPaperLegal
  • Change margins
  • margin excelApplication.InchesToPoints(0.5)
  • sheet.PageSetup.TopMargin margin
  • Change headers
  • sheet.PageSetup.RightHeader 'A\nD\nT\nPage
    P/N'
  • Hide columns
  • hiddenColumns 'A', 'D'
  • for hiddenColumn in hiddenColumns
    columns(hiddenColumn).Hidden True

126
Reference Lotus Notes
  • Import other modules
  • from win32com.client import Dispatch
  • import pywintypes
  • Interface for Lotus Notes
  • class Store(object)
  • Constructor
  • def __init__(self, databaseHost,
    databaseName, databasePassword)
  • Connect
  • self.notesSession Dispatch('Lotus.NotesS
    ession')
  • try
  • self.notesSession.Initialize(databaseP
    assword)
  • self.notesDatabase
    self.notesSession.GetDatabase(databaseHost,
    databaseName)
  • self.inbox self.notesDatabase.GetVie
    w('(Inbox)')
  • except pywintypes.com_error raise
    ConnectionError
  • Set constants
  • self.ENC_NONE win32com.client.constants.
    ENC_NONE

127
Reference Lotus Notes
  • Write
  • def write(self, toWhom, subject, body,
    attachments )
  • Don't convert MIME to rich text
  • self.notesSession.ConvertMime False
  • Prepare message
  • message self.notesDatabase.CreateDocumen
    t()
  • message.ReplaceItemValue('SendTo',
    toWhom)
  • message.ReplaceItemValue('Subject',
    subject)
  • stream self.notesSession.CreateStream()
  • stream.WriteText(body)
  • message_body message.CreateMIMEEntity()
  • message_body.SetContentFromText(stream,
    'text/html charsetutf-8', self.ENC_NONE)
  • Add attachments
  • for index in xrange(len(attachments))
  • attachment attachmentsindex
  • if not attachment continue
  • message_attachment
    message.CreateRichTextItem('attachmentd'
    index)
  • message_attachment.EmbedObject(self.EM
    BED_ATTACHMENT, '', attachment, None)

128
Reference Lotus Notes
  • Move
  • def move(self, destinationFolderName,
    document)
  • document.PutInFolder(destinationFolderName
    )
  • document.RemoveFromFolder('(Inbox)')
  • Queue
  • def clearQueues(self)
  • self.writes self.moves
  • def queueWrite(self, parcel)
  • self.writes.append(parcel)
  • def queueMove(self, parcel)
  • self.moves.append(parcel)
  • def processQueues(self)
  • for parcel in self.writes
    self.write(parcel)

129
Reference Microsoft Outlook
  • Connect to Microsoft Outlook using Dispatch
  • from win32com.client import Dispatch, constants
  • session win32com.client.Dispatch('Outlook.Applic
    ation')
  • namespace session.GetNamespace('MAPI')
  • namespace.Logon()
  • Browse folders
  • rootFolder namespace.Folders1.Folders
  • count rootFolder.Count
  • for whichFolder in xrange(1, count 1)
  • folder rootFolderwhichFolder
  • print folder.Name
  • Read mail
  • folder namespace.GetDefaultFolder(constants.olFo
    lderInbox)
  • messages folder.Items
  • message messagesmessageIndex
  • print message.Subject

130
Reference Microsoft Outlook
  • def write(to, subj, body, attachments )
  • x constants.olMailItem
  • m outlook.CreateItem(x)
  • m.To to m.Subject subj m.Body body
  • for attachment in attachments
  • p attachment, constants.olByValue, 1
  • m.Attachments.Add(p)
  • message.Send
  • def move(destinationFolderName, message)
  • message.Move(destinationFolderName)

131
Reference Databases and dates
  • import pyodbc
  • When inserting records, SQL server
  • only accepts dates in a certain format
  • now datetime.date.today().strftime('m/d/Y')
  • import sqlite3
  • self.connection sqlite3.connect(databasePath,
    detect_types sqlite3.PARSE_DECLTYPES)
  • sql 'CREATE TABLE invoices (invoiceNumber
    INTEGER PRIMARY KEY, invoiceDate DATE, amount
    REAL, description TEXT, isPaid INTEGER)'

132
Acknowledgments
  • Python!
  • Mark Hammond for developing win32com
  • Tutorial organizer Greg Lindstrom
  • Eric Koome for code on the Excel add-in
  • Python Conference 2008 organizers
  • All Python developers for being so helpful and
    willing to share what they have learned

133
Contact
  • See if someone has already solved it.
  • http//python.org/about/help
  • You can also ask me!
  • Roy H. Han
  • starsareblueandfaraway at gmail.com
  • (917) 566-7004
About PowerShow.com