Author Topic: RTD Functions  (Read 6188 times)

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 584
  • Karma: +14/-1
    • View Profile
RTD Functions
« on: May 09, 2010, 03:38:04 PM »
** For an up to date copy of this code go to the pyxll-examples github repo **
https://github.com/pyxll/pyxll-examples

** For Excel 2010 and later version you will need the exceltypes package **
https://github.com/pyxll/exceltypes

I've been playing around a little bit with real time data (RTD) functions, and thought I'd post something here in case anyone else was interested.

Using win32com and Chris Nilsson's example excelRTDServer.py (one of the win32com demos) it's pretty easy to get an RTD server working which you can then call from Excel with a formula like:

=RTD("Python.RTD.TimeServer","","seconds","5")

The only slight problems I have with this are:
  • the RTD function is not a very user friendly way to present a function to a user
  • registering the server requires access to the HKEY_CLASSES_ROOT, and most people shouldn't have that access

In VBA, it's possible to write a wrapper function around the RTD call to make it look like a normal worksheet function. Exactly the same thing is possible using PyXLL:


from pyxll import xl_func, GetActiveObject

@xl_func("int seconds: var")
def py_rtd_test(seconds):
    xl = win32com.client.Dispatch(GetActiveObject()).Application
    return xl.WorksheetFunction.RTD("Python.RTD.TimeServer", "", "seconds", str(seconds))


Now the user can enter a simpler formula and still get real time data back:

=py_rtd_test(5)

It's possible to register COM controls under HKEY_CURRENT_USER instead of HKEY_CLASSES_ROOT, it's just that win32com doesn't currently give you that choice. Luckily registering a COM control isn't actually that hard, you just have to know what values to put in the registry. Here's the function I used to register the server:


import win32con
import win32api
import pythoncom

def _register(cls):
    """register an inproc com server in hkey_current_user"""
    clsid_path = "Software\\Classes\\CLSID\\" + cls._reg_clsid_
    progid_path = "Software\\Classes\\" + cls._reg_progid_
    spec = cls.__module__ + "." + cls.__name__

    # register the class information
    win32api.RegSetValue(win32con.HKEY_CURRENT_USER, clsid_path, win32con.REG_SZ, cls._reg_desc_)
    win32api.RegSetValue(win32con.HKEY_CURRENT_USER, clsid_path + "\\ProgID", win32con.REG_SZ, cls._reg_progid_)
    win32api.RegSetValue(win32con.HKEY_CURRENT_USER, clsid_path + "\\PythonCOM", win32con.REG_SZ, spec)
    hkey = win32api.RegCreateKey(win32con.HKEY_CURRENT_USER, clsid_path + "\\InprocServer32")
    win32api.RegSetValueEx(hkey, None, None, win32con.REG_SZ, pythoncom.__file__)
    win32api.RegSetValueEx(hkey, "ThreadingModel", None, win32con.REG_SZ, "Both")

    # and add the progid
    win32api.RegSetValue(win32con.HKEY_CURRENT_USER, progid_path, win32con.REG_SZ, cls._reg_desc_)
    win32api.RegSetValue(win32con.HKEY_CURRENT_USER, progid_path + "\\CLSID", win32con.REG_SZ, cls._reg_clsid_)


I added a call to _register(win32com.demos.excelRTDServer.TimeServer) to my module imported by PyXLL so the server would get registered whenever the addin was loaded.

I've attached a really simple example that registers the win32com demo server and exposes it via a worksheet function. If you try this you should note that the excelRTDServer.py has the typelib version hardcoded to 1.4. To make it work with Excel 2003 I had to change EXCEL_TLB_MINOR to 5.

cheers,
Tony.



« Last Edit: June 11, 2014, 01:26:51 PM by Tony »

slava

  • Newbie
  • *
  • Posts: 3
  • Karma: +0/-0
    • View Profile
Re: RTD Functions
« Reply #1 on: November 30, 2013, 07:43:43 PM »
hello Tony,

can you confirm that RTD function 'py_rtd_test' won't automatically refresh values in cell in newer versions of Excel (after goolging for solutions). I am trying to replicate it and the only way cell value changes is when spreadsheet is recalculated (hit F9) but not pulled standalone.

Thanks,

slava

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 584
  • Karma: +14/-1
    • View Profile
Re: RTD Functions
« Reply #2 on: December 03, 2013, 12:42:18 AM »
Hi,

I don't know of any reasons why that code wouldn't work in newer versions of Excel. I think I was using 2010 when I wrote it originally.

Do you have automatic re-calc turned on? If you had it set to manual calc then you would have to refresh by pressing F9, but otherwise it should tick automatically.

Best regards,
Tony

cerberre

  • Newbie
  • *
  • Posts: 11
  • Karma: +0/-0
    • View Profile
Re: RTD Functions
« Reply #3 on: June 02, 2014, 11:49:27 AM »
I am using Excel 2010 and it does not refresh at all ( and I have auto calc on). Do you know why ?

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 584
  • Karma: +14/-1
    • View Profile
Re: RTD Functions
« Reply #4 on: June 02, 2014, 01:05:17 PM »
Do you get the calculated value when you first calculate the cell and recalculate the sheet?

Try adding some logging to the example code win32com.demos.excelRTDServer.TimeServer to check that it is running and publishing values.

Best regards,
Tony