"""Excel IRTDServer implementation.
This module is a functional example of how to implement the IRTDServer interface
in python, using the pywin32 extensions. Further details, about this interface
and it can be found at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlrtdfaq.asp
"""
# Copyright (c) 2003-2004 by Chris Nilsson <chris@slort.org>
#
# By obtaining, using, and/or copying this software and/or its
# associated documentation, you agree that you have read, understood,
# and will comply with the following terms and conditions:
#
# Permission to use, copy, modify, and distribute this software and
# its associated documentation for any purpose and without fee is
# hereby granted, provided that the above copyright notice appears in
# all copies, and that both that copyright notice and this permission
# notice appear in supporting documentation, and that the name of
# Christopher Nilsson (the author) not be used in advertising or publicity
# pertaining to distribution of the software without specific, written
# prior permission.
#
# THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD
# TO THIS SOFTWARE, INCLUDING ALL IMPLIED WARRANTIES OF MERCHANT-
# ABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR
# BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY
# DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS,
# WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS
# ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE
# OF THIS SOFTWARE.
import pythoncom
import win32com.client
from win32com import universal
from win32com.client import gencache
from win32com.server.exception import COMException
import threading
import datetime # For the example classes...
# Typelib info for version 10 - aka Excel XP.
# This is the minimum version of excel that we can work with as this is when
# Microsoft introduced these interfaces.
EXCEL_TLB_GUID = '{00020813-0000-0000-C000-000000000046}'
EXCEL_TLB_LCID = 0
EXCEL_TLB_MAJOR = 1
EXCEL_TLB_MINOR = 4
# Import the excel typelib to make sure we've got early-binding going on.
# The "ByRef" parameters we use later won't work without this.
gencache.EnsureModule(EXCEL_TLB_GUID, EXCEL_TLB_LCID, \
EXCEL_TLB_MAJOR, EXCEL_TLB_MINOR)
# Tell pywin to import these extra interfaces.
# --
# QUESTION: Why? The interfaces seem to descend from IDispatch, so
# I'd have thought, for example, calling callback.UpdateNotify() (on the
# IRTDUpdateEvent callback excel gives us) would work without molestation.
# But the callback needs to be cast to a "real" IRTDUpdateEvent type. Hmm...
# This is where my small knowledge of the pywin framework / COM gets hazy.
# --
# Again, we feed in the Excel typelib as the source of these interfaces.
universal.RegisterInterfaces(EXCEL_TLB_GUID,
EXCEL_TLB_LCID, EXCEL_TLB_MAJOR, EXCEL_TLB_MINOR,
['IRtdServer','IRTDUpdateEvent'])
class ExcelRTDServer(object):
"""Base RTDServer class.
Provides most of the features needed to implement the IRtdServer interface.
Manages topic adding, removal, and packing up the values for excel.
Shouldn't be instanciated directly.
Instead, descendant classes should override the CreateTopic() method.
Topic objects only need to provide a GetValue() function to play nice here.
The values given need to be atomic (eg. string, int, float... etc).
Also note: nothing has been done within this class to ensure that we get
time to check our topics for updates. I've left that up to the subclass
since the ways, and needs, of refreshing your topics will vary greatly. For
example, the sample implementation uses a timer thread to wake itself up.
Whichever way you choose to do it, your class needs to be able to wake up
occaisionally, since excel will never call your class without being asked to
first.
Excel will communicate with our object in this order:
1. Excel instanciates our object and calls ServerStart, providing us with
an IRTDUpdateEvent callback object.
2. Excel calls ConnectData when it wants to subscribe to a new "topic".
3. When we have new data to provide, we call the UpdateNotify method of the
callback object we were given.
4. Excel calls our RefreshData method, and receives a 2d SafeArray (row-major)
containing the Topic ids in the 1st dim, and the topic values in the
2nd dim.
5. When not needed anymore, Excel will call our DisconnectData to
unsubscribe from a topic.
6. When there are no more topics left, Excel will call our ServerTerminate
method to kill us.
Throughout, at undetermined periods, Excel will call our Heartbeat
method to see if we're still alive. It must return a non-zero value, or
we'll be killed.
NOTE: By default, excel will at most call RefreshData once every 2 seconds.
This is a setting that needs to be changed excel-side. To change this,
you can set the throttle interval like this in the excel VBA object model:
Application.RTD.ThrottleInterval = 1000 ' milliseconds
"""
_com_interfaces_ = ['IRtdServer']
_public_methods_ = ['ConnectData','DisconnectData','Heartbeat',
'RefreshData','ServerStart','ServerTerminate']
_reg_clsctx_ = pythoncom.CLSCTX_INPROC_SERVER
#_reg_clsid_ = "# subclass must provide this class attribute"
#_reg_desc_ = "# subclass should provide this description"
#_reg_progid_ = "# subclass must provide this class attribute"
ALIVE = 1
NOT_ALIVE = 0
def __init__(self):
"""Constructor"""
super(ExcelRTDServer, self).__init__()
self.IsAlive = self.ALIVE
self.__callback = None
self.topics = {}
def SignalExcel(self):
"""Use the callback we were given to tell excel new data is available."""
if self.__callback is None:
raise COMException(desc="Callback excel provided is Null")
self.__callback.UpdateNotify()
def ConnectData(self, TopicID, Strings, GetNewValues):
"""Creates a new topic out of the Strings excel gives us."""
try:
self.topics[TopicID] = self.CreateTopic(Strings)
except Exception, why:
raise COMException(desc=str(why))
GetNewValues = True
result = self.topics[TopicID]
if result is None:
result = "# %s: Waiting for update" % self.__class__.__name__
else:
result = result.GetValue()
# fire out internal event...
self.OnConnectData(TopicID)
# GetNewValues as per interface is ByRef, so we need to pass it back too.
return result, GetNewValues
def DisconnectData(self, TopicID):
"""Deletes the given topic."""
self.OnDisconnectData(TopicID)
if TopicID in self.topics:
self.topics[TopicID] = None
del self.topics[TopicID]
def Heartbeat(self):
"""Called by excel to see if we're still here."""
return self.IsAlive
def RefreshData(self, TopicCount):
"""Packs up the topic values. Called by excel when it's ready for an update.
Needs to:
* Return the current number of topics, via the "ByRef" TopicCount
* Return a 2d SafeArray of the topic data.
- 1st dim: topic numbers
- 2nd dim: topic values
We could do some caching, instead of repacking everytime...
But this works for demonstration purposes."""
TopicCount = len(self.topics)
self.OnRefreshData()
# Grow the lists, so we don't need a heap of calls to append()
results = [[None] * TopicCount, [None] * TopicCount]
# Excel expects a 2-dimensional array. The first dim contains the
# topic numbers, and the second contains the values for the topics.
# In true VBA style (yuck), we need to pack the array in row-major format,
# which looks like:
# ( (topic_num1, topic_num2, ..., topic_numN), \
# (topic_val1, topic_val2, ..., topic_valN) )
for idx, topicdata in enumerate(self.topics.iteritems()):
topicNum, topic = topicdata
results[0][idx] = topicNum
results[1][idx] = topic.GetValue()
# TopicCount is meant to be passed to us ByRef, so return it as well, as per
# the way pywin32 handles ByRef arguments.
return tuple(results), TopicCount
def ServerStart(self, CallbackObject):
"""Excel has just created us... We take its callback for later, and set up shop."""
self.IsAlive = self.ALIVE
if CallbackObject is None:
raise COMException(desc='Excel did not provide a callback')
# Need to "cast" the raw PyIDispatch object to the IRTDUpdateEvent interface
IRTDUpdateEventKlass = win32com.client.CLSIDToClass.GetClass('{A43788C1-D91B-11D3-8F39-00C04F3651B8}')
self.__callback = IRTDUpdateEventKlass(CallbackObject)
self.OnServerStart()
return self.IsAlive
def ServerTerminate(self):
"""Called when excel no longer wants us."""
self.IsAlive = self.NOT_ALIVE # On next heartbeat, excel will free us
self.OnServerTerminate()
def CreateTopic(self, TopicStrings=None):
"""Topic factory method. Subclass must override.
Topic objects need to provide:
* GetValue() method which returns an atomic value.
Will raise NotImplemented if not overridden.
"""
raise NotImplemented('Subclass must implement')
# Overridable class events...
def OnConnectData(self, TopicID):
"""Called when a new topic has been created, at excel's request."""
pass
def OnDisconnectData(self, TopicID):
"""Called when a topic is about to be deleted, at excel's request."""
pass
def OnRefreshData(self):
"""Called when excel has requested all current topic data."""
pass
def OnServerStart(self):
"""Called when excel has instanciated us."""
pass
def OnServerTerminate(self):
"""Called when excel is about to destroy us."""
pass
class RTDTopic(object):
"""Base RTD Topic.
Only method required by our RTDServer implementation is GetValue().
The others are more for convenience."""
def __init__(self, TopicStrings):
super(RTDTopic, self).__init__()
self.TopicStrings = TopicStrings
self.__currentValue = None
self.__dirty = False
def Update(self, sender):
"""Called by the RTD Server.
Gives us a chance to check if our topic data needs to be
changed (eg. check a file, quiz a database, etc)."""
raise NotImplemented('subclass must implement')
def Reset(self):
"""Call when this topic isn't considered "dirty" anymore."""
self.__dirty = False
def GetValue(self):
return self.__currentValue
def SetValue(self, value):
self.__dirty = True
self.__currentValue = value
def HasChanged(self):
return self.__dirty
# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
######################################
# Example classes
######################################
class TimeServer(ExcelRTDServer):
"""Example Time RTD server.
Sends time updates back to excel.
example of use, in an excel sheet:
=RTD("Python.RTD.TimeServer","","seconds","5")
This will cause a timestamp string to fill the cell, and update its value
every 5 seconds (or as close as possible depending on how busy excel is).
The empty string parameter denotes the com server is running on the local
machine. Otherwise, put in the hostname to look on. For more info
on this, lookup the Excel help for its "RTD" worksheet function.
Obviously, you'd want to wrap this kind of thing in a friendlier VBA
function.
Also, remember that the RTD function accepts a maximum of 28 arguments!
If you want to pass more, you may need to concatenate arguments into one
string, and have your topic parse them appropriately.
"""
# win32com.server setup attributes...
# Never copy the _reg_clsid_ value in your own classes!
_reg_clsid_ = '{EA7F2CF1-11A2-45E4-B2D5-68E240DB8CB1}'
_reg_progid_ = 'Python.RTD.TimeServer'
_reg_desc_ = "Python class implementing Excel IRTDServer -- feeds time"
# other class attributes...
INTERVAL = 0.5 # secs. Threaded timer will wake us up at this interval.
def __init__(self):
super(TimeServer, self).__init__()
# Simply timer thread to ensure we get to update our topics, and
# tell excel about any changes. This is a pretty basic and dirty way to
# do this. Ideally, there should be some sort of waitable (eg. either win32
# event, socket data event...) and be kicked off by that event triggering.
# As soon as we set up shop here, we _must_ return control back to excel.
# (ie. we can't block and do our own thing...)
self.ticker = threading.Timer(self.INTERVAL, self.Update)
def OnServerStart(self):
self.ticker.start()
def OnServerTerminate(self):
if not self.ticker.finished.isSet():
self.ticker.cancel() # Cancel our wake-up thread. Excel has killed us.
def Update(self):
# Get our wake-up thread ready...
self.ticker = threading.Timer(self.INTERVAL, self.Update)
try:
# Check if any of our topics have new info to pass on
if len(self.topics):
refresh = False
for topic in self.topics.itervalues():
topic.Update(self)
if topic.HasChanged():
refresh = True
topic.Reset()
if refresh:
self.SignalExcel()
finally:
self.ticker.start() # Make sure we get to run again
def CreateTopic(self, TopicStrings=None):
"""Topic factory. Builds a TimeTopic object out of the given TopicStrings."""
return TimeTopic(TopicStrings)
class TimeTopic(RTDTopic):
"""Example topic for example RTD server.
Will accept some simple commands to alter how long to delay value updates.
Commands:
* seconds, delay_in_seconds
* minutes, delay_in_minutes
* hours, delay_in_hours
"""
def __init__(self, TopicStrings):
super(TimeTopic, self).__init__(TopicStrings)
try:
self.cmd, self.delay = self.TopicStrings
except Exception, E:
# We could simply return a "# ERROR" type string as the
# topic value, but explosions like this should be able to get handled by
# the VBA-side "On Error" stuff.
raise ValueError("Invalid topic strings: %s" % str(TopicStrings))
#self.cmd = str(self.cmd)
self.delay = float(self.delay)
# setup our initial value
self.checkpoint = self.timestamp()
self.SetValue(str(self.checkpoint))
def timestamp(self):
return datetime.datetime.now()
def Update(self, sender):
now = self.timestamp()
delta = now - self.checkpoint
refresh = False
if self.cmd == "seconds":
if delta.seconds >= self.delay:
refresh = True
elif self.cmd == "minutes":
if delta.minutes >= self.delay:
refresh = True
elif self.cmd == "hours":
if delta.hours >= self.delay:
refresh = True
else:
self.SetValue("#Unknown command: " + self.cmd)
if refresh:
self.SetValue(str(now))
self.checkpoint = now
if __name__ == "__main__":
import win32com.server.register
# Register/Unregister TimeServer example
# eg. at the command line: excelrtd.py --register
# Then type in an excel cell something like:
# =RTD("Python.RTD.TimeServer","","seconds","5")
win32com.server.register.UseCommandLine(TimeServer)
|