codekingpro/portable-devtools

1"""Excel IRTDServer implementation.2 3This module is a functional example of how to implement the IRTDServer interface4in python, using the pywin32 extensions. Further details, about this interface5and it can be found at:6     http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlrtdfaq.asp7"""8 9# Copyright (c) 2003-2004 by Chris Nilsson <chris@slort.org>10#11# By obtaining, using, and/or copying this software and/or its12# associated documentation, you agree that you have read, understood,13# and will comply with the following terms and conditions:14#15# Permission to use, copy, modify, and distribute this software and16# its associated documentation for any purpose and without fee is17# hereby granted, provided that the above copyright notice appears in18# all copies, and that both that copyright notice and this permission19# notice appear in supporting documentation, and that the name of20# Christopher Nilsson (the author) not be used in advertising or publicity21# pertaining to distribution of the software without specific, written22# prior permission.23#24# THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD25# TO THIS SOFTWARE, INCLUDING ALL IMPLIED WARRANTIES OF MERCHANT-26# ABILITY AND FITNESS.  IN NO EVENT SHALL THE AUTHOR27# BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY28# DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS,29# WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS30# ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE31# OF THIS SOFTWARE.32 33import datetime  # For the example classes...34import threading35 36import pythoncom37import win32com.client38from win32com import universal39from win32com.client import gencache40from win32com.server.exception import COMException41 42# Typelib info for version 10 - aka Excel XP.43# This is the minimum version of excel that we can work with as this is when44# Microsoft introduced these interfaces.45EXCEL_TLB_GUID = "{00020813-0000-0000-C000-000000000046}"46EXCEL_TLB_LCID = 047EXCEL_TLB_MAJOR = 148EXCEL_TLB_MINOR = 449 50# Import the excel typelib to make sure we've got early-binding going on.51# The "ByRef" parameters we use later won't work without this.52gencache.EnsureModule(EXCEL_TLB_GUID, EXCEL_TLB_LCID, EXCEL_TLB_MAJOR, EXCEL_TLB_MINOR)53 54# Tell pywin to import these extra interfaces.55# --56# QUESTION: Why? The interfaces seem to descend from IDispatch, so57# I'd have thought, for example, calling callback.UpdateNotify() (on the58# IRTDUpdateEvent callback excel gives us) would work without molestation.59# But the callback needs to be cast to a "real" IRTDUpdateEvent type. Hmm...60# This is where my small knowledge of the pywin framework / COM gets hazy.61# --62# Again, we feed in the Excel typelib as the source of these interfaces.63universal.RegisterInterfaces(64    EXCEL_TLB_GUID,65    EXCEL_TLB_LCID,66    EXCEL_TLB_MAJOR,67    EXCEL_TLB_MINOR,68    ["IRtdServer", "IRTDUpdateEvent"],69)70 71 72class ExcelRTDServer(object):73    """Base RTDServer class.74 75    Provides most of the features needed to implement the IRtdServer interface.76    Manages topic adding, removal, and packing up the values for excel.77 78    Shouldn't be instanciated directly.79 80    Instead, descendant classes should override the CreateTopic() method.81    Topic objects only need to provide a GetValue() function to play nice here.82    The values given need to be atomic (eg. string, int, float... etc).83 84    Also note: nothing has been done within this class to ensure that we get85    time to check our topics for updates. I've left that up to the subclass86    since the ways, and needs, of refreshing your topics will vary greatly. For87    example, the sample implementation uses a timer thread to wake itself up.88    Whichever way you choose to do it, your class needs to be able to wake up89    occaisionally, since excel will never call your class without being asked to90    first.91 92    Excel will communicate with our object in this order:93      1. Excel instanciates our object and calls ServerStart, providing us with94         an IRTDUpdateEvent callback object.95      2. Excel calls ConnectData when it wants to subscribe to a new "topic".96      3. When we have new data to provide, we call the UpdateNotify method of the97         callback object we were given.98      4. Excel calls our RefreshData method, and receives a 2d SafeArray (row-major)99         containing the Topic ids in the 1st dim, and the topic values in the100         2nd dim.101      5. When not needed anymore, Excel will call our DisconnectData to102         unsubscribe from a topic.103      6. When there are no more topics left, Excel will call our ServerTerminate104         method to kill us.105 106    Throughout, at undetermined periods, Excel will call our Heartbeat107    method to see if we're still alive. It must return a non-zero value, or108    we'll be killed.109 110    NOTE: By default, excel will at most call RefreshData once every 2 seconds.111          This is a setting that needs to be changed excel-side. To change this,112          you can set the throttle interval like this in the excel VBA object model:113            Application.RTD.ThrottleInterval = 1000 ' milliseconds114    """115 116    _com_interfaces_ = ["IRtdServer"]117    _public_methods_ = [118        "ConnectData",119        "DisconnectData",120        "Heartbeat",121        "RefreshData",122        "ServerStart",123        "ServerTerminate",124    ]125    _reg_clsctx_ = pythoncom.CLSCTX_INPROC_SERVER126    # _reg_clsid_ = "# subclass must provide this class attribute"127    # _reg_desc_ = "# subclass should provide this description"128    # _reg_progid_ = "# subclass must provide this class attribute"129 130    ALIVE = 1131    NOT_ALIVE = 0132 133    def __init__(self):134        """Constructor"""135        super(ExcelRTDServer, self).__init__()136        self.IsAlive = self.ALIVE137        self.__callback = None138        self.topics = {}139 140    def SignalExcel(self):141        """Use the callback we were given to tell excel new data is available."""142        if self.__callback is None:143            raise COMException(desc="Callback excel provided is Null")144        self.__callback.UpdateNotify()145 146    def ConnectData(self, TopicID, Strings, GetNewValues):147        """Creates a new topic out of the Strings excel gives us."""148        try:149            self.topics[TopicID] = self.CreateTopic(Strings)150        except Exception as why:151            raise COMException(desc=str(why))152        GetNewValues = True153        result = self.topics[TopicID]154        if result is None:155            result = "# %s: Waiting for update" % self.__class__.__name__156        else:157            result = result.GetValue()158 159        # fire out internal event...160        self.OnConnectData(TopicID)161 162        # GetNewValues as per interface is ByRef, so we need to pass it back too.163        return result, GetNewValues164 165    def DisconnectData(self, TopicID):166        """Deletes the given topic."""167        self.OnDisconnectData(TopicID)168 169        if TopicID in self.topics:170            self.topics[TopicID] = None171            del self.topics[TopicID]172 173    def Heartbeat(self):174        """Called by excel to see if we're still here."""175        return self.IsAlive176 177    def RefreshData(self, TopicCount):178        """Packs up the topic values. Called by excel when it's ready for an update.179 180        Needs to:181          * Return the current number of topics, via the "ByRef" TopicCount182          * Return a 2d SafeArray of the topic data.183            - 1st dim: topic numbers184            - 2nd dim: topic values185 186        We could do some caching, instead of repacking everytime...187        But this works for demonstration purposes."""188        TopicCount = len(self.topics)189        self.OnRefreshData()190 191        # Grow the lists, so we don't need a heap of calls to append()192        results = [[None] * TopicCount, [None] * TopicCount]193 194        # Excel expects a 2-dimensional array. The first dim contains the195        # topic numbers, and the second contains the values for the topics.196        # In true VBA style (yuck), we need to pack the array in row-major format,197        # which looks like:198        #   ( (topic_num1, topic_num2, ..., topic_numN), \199        #     (topic_val1, topic_val2, ..., topic_valN) )200        for idx, topicdata in enumerate(self.topics.items()):201            topicNum, topic = topicdata202            results[0][idx] = topicNum203            results[1][idx] = topic.GetValue()204 205        # TopicCount is meant to be passed to us ByRef, so return it as well, as per206        # the way pywin32 handles ByRef arguments.207        return tuple(results), TopicCount208 209    def ServerStart(self, CallbackObject):210        """Excel has just created us... We take its callback for later, and set up shop."""211        self.IsAlive = self.ALIVE212 213        if CallbackObject is None:214            raise COMException(desc="Excel did not provide a callback")215 216        # Need to "cast" the raw PyIDispatch object to the IRTDUpdateEvent interface217        IRTDUpdateEventKlass = win32com.client.CLSIDToClass.GetClass(218            "{A43788C1-D91B-11D3-8F39-00C04F3651B8}"219        )220        self.__callback = IRTDUpdateEventKlass(CallbackObject)221 222        self.OnServerStart()223 224        return self.IsAlive225 226    def ServerTerminate(self):227        """Called when excel no longer wants us."""228        self.IsAlive = self.NOT_ALIVE  # On next heartbeat, excel will free us229        self.OnServerTerminate()230 231    def CreateTopic(self, TopicStrings=None):232        """Topic factory method. Subclass must override.233 234        Topic objects need to provide:235          * GetValue() method which returns an atomic value.236 237        Will raise NotImplemented if not overridden.238        """239        raise NotImplemented("Subclass must implement")240 241    # Overridable class events...242    def OnConnectData(self, TopicID):243        """Called when a new topic has been created, at excel's request."""244        pass245 246    def OnDisconnectData(self, TopicID):247        """Called when a topic is about to be deleted, at excel's request."""248        pass249 250    def OnRefreshData(self):251        """Called when excel has requested all current topic data."""252        pass253 254    def OnServerStart(self):255        """Called when excel has instanciated us."""256        pass257 258    def OnServerTerminate(self):259        """Called when excel is about to destroy us."""260        pass261 262 263class RTDTopic(object):264    """Base RTD Topic.265    Only method required by our RTDServer implementation is GetValue().266    The others are more for convenience."""267 268    def __init__(self, TopicStrings):269        super(RTDTopic, self).__init__()270        self.TopicStrings = TopicStrings271        self.__currentValue = None272        self.__dirty = False273 274    def Update(self, sender):275        """Called by the RTD Server.276        Gives us a chance to check if our topic data needs to be277        changed (eg. check a file, quiz a database, etc)."""278        raise NotImplemented("subclass must implement")279 280    def Reset(self):281        """Call when this topic isn't considered "dirty" anymore."""282        self.__dirty = False283 284    def GetValue(self):285        return self.__currentValue286 287    def SetValue(self, value):288        self.__dirty = True289        self.__currentValue = value290 291    def HasChanged(self):292        return self.__dirty293 294 295# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=296 297######################################298# Example classes299######################################300 301 302class TimeServer(ExcelRTDServer):303    """Example Time RTD server.304 305    Sends time updates back to excel.306 307    example of use, in an excel sheet:308      =RTD("Python.RTD.TimeServer","","seconds","5")309 310    This will cause a timestamp string to fill the cell, and update its value311    every 5 seconds (or as close as possible depending on how busy excel is).312 313    The empty string parameter denotes the com server is running on the local314    machine. Otherwise, put in the hostname to look on. For more info315    on this, lookup the Excel help for its "RTD" worksheet function.316 317    Obviously, you'd want to wrap this kind of thing in a friendlier VBA318    function.319 320    Also, remember that the RTD function accepts a maximum of 28 arguments!321    If you want to pass more, you may need to concatenate arguments into one322    string, and have your topic parse them appropriately.323    """324 325    # win32com.server setup attributes...326    # Never copy the _reg_clsid_ value in your own classes!327    _reg_clsid_ = "{EA7F2CF1-11A2-45E4-B2D5-68E240DB8CB1}"328    _reg_progid_ = "Python.RTD.TimeServer"329    _reg_desc_ = "Python class implementing Excel IRTDServer -- feeds time"330 331    # other class attributes...332    INTERVAL = 0.5  # secs. Threaded timer will wake us up at this interval.333 334    def __init__(self):335        super(TimeServer, self).__init__()336 337        # Simply timer thread to ensure we get to update our topics, and338        # tell excel about any changes. This is a pretty basic and dirty way to339        # do this. Ideally, there should be some sort of waitable (eg. either win32340        # event, socket data event...) and be kicked off by that event triggering.341        # As soon as we set up shop here, we _must_ return control back to excel.342        # (ie. we can't block and do our own thing...)343        self.ticker = threading.Timer(self.INTERVAL, self.Update)344 345    def OnServerStart(self):346        self.ticker.start()347 348    def OnServerTerminate(self):349        if not self.ticker.finished.isSet():350            self.ticker.cancel()  # Cancel our wake-up thread. Excel has killed us.351 352    def Update(self):353        # Get our wake-up thread ready...354        self.ticker = threading.Timer(self.INTERVAL, self.Update)355        try:356            # Check if any of our topics have new info to pass on357            if len(self.topics):358                refresh = False359                for topic in self.topics.values():360                    topic.Update(self)361                    if topic.HasChanged():362                        refresh = True363                    topic.Reset()364 365                if refresh:366                    self.SignalExcel()367        finally:368            self.ticker.start()  # Make sure we get to run again369 370    def CreateTopic(self, TopicStrings=None):371        """Topic factory. Builds a TimeTopic object out of the given TopicStrings."""372        return TimeTopic(TopicStrings)373 374 375class TimeTopic(RTDTopic):376    """Example topic for example RTD server.377 378    Will accept some simple commands to alter how long to delay value updates.379 380    Commands:381      * seconds, delay_in_seconds382      * minutes, delay_in_minutes383      * hours, delay_in_hours384    """385 386    def __init__(self, TopicStrings):387        super(TimeTopic, self).__init__(TopicStrings)388        try:389            self.cmd, self.delay = self.TopicStrings390        except Exception as E:391            # We could simply return a "# ERROR" type string as the392            # topic value, but explosions like this should be able to get handled by393            # the VBA-side "On Error" stuff.394            raise ValueError("Invalid topic strings: %s" % str(TopicStrings))395 396        # self.cmd = str(self.cmd)397        self.delay = float(self.delay)398 399        # setup our initial value400        self.checkpoint = self.timestamp()401        self.SetValue(str(self.checkpoint))402 403    def timestamp(self):404        return datetime.datetime.now()405 406    def Update(self, sender):407        now = self.timestamp()408        delta = now - self.checkpoint409        refresh = False410        if self.cmd == "seconds":411            if delta.seconds >= self.delay:412                refresh = True413        elif self.cmd == "minutes":414            if delta.minutes >= self.delay:415                refresh = True416        elif self.cmd == "hours":417            if delta.hours >= self.delay:418                refresh = True419        else:420            self.SetValue("#Unknown command: " + self.cmd)421 422        if refresh:423            self.SetValue(str(now))424            self.checkpoint = now425 426 427if __name__ == "__main__":428    import win32com.server.register429 430    # Register/Unregister TimeServer example431    # eg. at the command line: excelrtd.py --register432    # Then type in an excel cell something like:433    # =RTD("Python.RTD.TimeServer","","seconds","5")434    win32com.server.register.UseCommandLine(TimeServer)435