Samples‎ > ‎MS Office‎ > ‎

Excel RTD Server Sample

The RTDServerSample class should be registered in the system using Server Manager application. In case with RTD server such registration is required so that Excel will connect to the server by its ProgID and COM will be able to map ProgID of your RTD server to its CLSID.

Therefore, the registration of the RTD server will include following steps:

  1. Create a JAR with the RTD server class and necessary classes and wrappers for COM classes and interfaces. The class path in the manifest file of this JAR should include all necessary libraries, such as jniwrap-3.x.jar, winpack-3.x.jar, comfyj-2.x.jar, slf4j-api-1.5.8.jar, slf4j-simple-1.5.8.jar, and a JAR file that contains native libraries and license.
  2. Register this JAR with the Server Manager application. The registration procedure is described in Guide: http://www.teamdev.com/downloads/comfyj/docs/ComfyJ-PGuide.html#AEN654. You don't need to include the necessary libraries to the class path in the Server Manager, because they are included in the class path of the manifest.

After the registration, you can try to connect to the RTD server from MS Excel. The example of RTD function call is given in the sample.

The complete IntelliJ Idea project (RTDServer.zip) that should demonstrate how to get this RTD server running is attached to the article. In this sample project the RTDSererRegistration.java sample demonstrates how to perform static and dynamic registrations of the server in a system.
import com.jniwrapper.Int32;
import com.jniwrapper.win32.automation.OleMessageLoop;
import com.jniwrapper.win32.automation.types.*;
import com.jniwrapper.win32.com.ComException;
import com.jniwrapper.win32.com.DispatchComServer;
import com.jniwrapper.win32.com.DispatchComServerFactory;
import com.jniwrapper.win32.com.server.CoClassMetaInfo;
import com.jniwrapper.win32.com.types.CLSID;
import com.jniwrapper.win32.excel.IRTDUpdateEvent;
import com.jniwrapper.win32.excel.IRtdServer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.*;

/**
 * The sample demonstrates how to create a RealTimeData server for using with Microsoft Excel.
 * This server simply provides a counter that is updated every 2 seconds on a worksheet.
 * The server accepts up to two topic strings. The first topic string can be AAA or BBB;
 * any other topic string is considered invalid.
 * The second string is a numeric value that represents how the return value should be incremented.
 * </p>
 * A sample RTD function call for the server:
 * =RTD("rtdserversample";;"AAA"; "10")
 */
public class RTDServerSample extends DispatchComServer implements IRtdServer {
    public static final CLSID COM_SERVER_CLSID = new CLSID("{137E7301-CDC5-4AA0-AA0B-FE7DE69FFB82}");
    public static final String PROG_ID = "RTDServerSample.1";
    public static final String VERSION_INDEPENDENT_PROG_ID = "RTDServerSample";
    public static final String COM_SERVER_DESCRIPTION = "Sample RTD server which allows to use its data in Excel.";

    private static final int STATUS_SUCCESS = 1;
    private static final int STATUS_FAILURE = 0;
    private static final Logger LOG = LoggerFactory.getLogger(RTDServerSample.class);
    private static final int UPDATE_TIMEOUT = 2000;

    private final long startupTime;
    private Map<Long, Topic> topics = new HashMap<Long, Topic>();

    private IRTDUpdateEvent callbackObject;
    private OleMessageLoop messageLoop;

    private boolean notificationThreadIsRunning;

    public RTDServerSample(CoClassMetaInfo coClassMetaInfo) {
        super(coClassMetaInfo);
        LOG.info("RTDServerSample.<init>");
        startupTime = System.currentTimeMillis();
        try {
            setUseCaseSensitiveNameComparison(false);
            setDispInterface(true);
        } catch (Exception e1) {
            LOG.error("", e1);
        }
        LOG.info("RTDServerSample.<init> done");
    }

    @Override
    public Int32 serverStart(IRTDUpdateEvent CallbackObject) throws ComException {
        LOG.info("RTDServerSample.serverStart");
        try {
            messageLoop = DispatchComServerFactory.getOleMessageLoop();
            callbackObject = (IRTDUpdateEvent) messageLoop.bindObject(CallbackObject);
            callbackObject.addRef();

            //Start notification thread
            notificationThreadIsRunning = true;
            Thread notificationThread = new Thread(new Runnable() {
                @Override
                public void run() {
                    LOG.info("Entering notification thread");
                    while (notificationThreadIsRunning) {
                        try {
                            Thread.sleep(UPDATE_TIMEOUT);
                            updateServerData();
                        } catch (Exception e) {
                            LOG.error("Failed to update server data ", e);
                        }
                    }
                    LOG.info("Notification thread shutdown");
                }
            });
            notificationThread.start();
            LOG.info("RTDServerSample.serverStart done");
        } catch (Exception e) {
            LOG.error("An error occurred while executing serverStart", e);
            return new Int32(STATUS_FAILURE);
        }
        return new Int32(STATUS_SUCCESS);
    }

    @Override
    public Variant connectData(Int32 topicID, SafeArray strings, VariantBool getNewValues) throws ComException {

        long topicIDValue = topicID.getValue();
        String topicString = ((Variant) (strings.get(0))).getBstrVal().getValue();
        String topicIncrement = ((Variant) (strings.get(1))).getBstrVal().getValue();
        LOG.info("RTDServerSample.connectData: topic id = " + topicIDValue + ", topic string = " + topicString + ", topic increment value = " + topicIncrement);

        Topic topic = new Topic(topicString, topicIncrement);

        topics.put(topicIDValue, topic);
        Variant returnValue = topic.getTopicValue();

        getNewValues.setBooleanValue(true);
        System.out.println("RTDServerSample.connectData done");
        return returnValue;
    }

    @Override
    public SafeArray refreshData(Int32 topicCount) throws ComException {
        LOG.info("RTDServerSample.refreshData");

        int topicsCount = topics.size();

        topicCount.setValue(topicsCount);
        SafeArray result = createDataArray(topicsCount);
        Set<Long> topicIDs = topics.keySet();
        int elementCounter = 0;
        int[] indices = new int[2];
        for (Long topicID : topicIDs) {
            Topic topic = topics.get(topicID);
            indices[1] = elementCounter;

            indices[0] = 0;
            result.set(indices, new Variant(topicID.intValue()));
            indices[0] = 1;
            result.set(indices, topic.getTopicValue());
            elementCounter++;
        }
        LOG.info("RTDServerSample.refreshData done");
        return result;
    }

    @Override
    public void disconnectData(Int32 TopicID) throws ComException {
        LOG.info("RTDServerSample.disconnectData");
        long value = TopicID.getValue();
        topics.remove(value);
        LOG.info("RTDServerSample.disconnectData done");
    }

    @Override
    public Int32 heartbeat() throws ComException {
        LOG.info("RTDServerSample.heartbeat");
        return new Int32(STATUS_SUCCESS);
    }

    @Override
    public void serverTerminate() throws ComException {
        if (notificationThreadIsRunning) {
            notificationThreadIsRunning = false;
        }
        try {
            messageLoop.doInvokeAndWait(new Runnable() {
                @Override
                public void run() {
                    if (callbackObject != null && !callbackObject.isNull()) {
                        callbackObject.disconnect();
                        callbackObject.setAutoDelete(false);
                        callbackObject.release();
                    }
                }
            });
        } catch (Exception e) {
            LOG.error("Failed to disconnect or release callback object", e);
        }
    }

    private SafeArray createDataArray(int topicsCount) {
        SafeArrayBound[] dataArrayBounds = new SafeArrayBound[2];
        dataArrayBounds[0] = new SafeArrayBound(2);
        dataArrayBounds[1] = new SafeArrayBound(topicsCount);
        return new SafeArray(dataArrayBounds, Variant.class);
    }

    protected void updateServerData() throws ComException {
        long now = System.currentTimeMillis();
        long uptime = now - startupTime;
        LOG.info("RTDServerSample updating topics, server uptime = " + (uptime / 1000) + " seconds.");
        updateTopics();
        generateUpdateEvent();

    }

    private void generateUpdateEvent() {
        boolean callbackNotNull = callbackObject != null && !callbackObject.isNull();
        if (notificationThreadIsRunning && callbackNotNull) {
            try {
                messageLoop.doInvokeAndWait(new Runnable() {
                    @Override
                    public void run() {
                        callbackObject.updateNotify();
                        LOG.info("Callback.updateNotify() called");
                    }
                });
            } catch (Exception e) {
                LOG.error("Failed to call updateNotify()", e);
            }
        } else {
            LOG.error("Failed to generate update event: Notification thread is running = " + notificationThreadIsRunning + ", Callback is not null = " + callbackNotNull);
        }
    }

    private void updateTopics() {
        Set<Long> topicIDs = topics.keySet();
        for (Long topicID : topicIDs) {
            Topic topic = topics.get(topicID);
            topic.update();
        }
    }
}


import com.jniwrapper.win32.automation.types.Variant;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * A topic for RTDServerSample server. Contains two topic strings. The first topic string can be AAA or BBB;
 * any other topic string is considered invalid. The second string is a numeric value that represents how
 * the return value should be incremented.
 */
class Topic {
    private static final Logger LOG = LoggerFactory.getLogger(Topic.class);
    public static final String ERROR_MESSAGE = "Error";

    private long incrementValue;
    private String topicString;
    private long currentValue;
    private Variant topicValue;

    Topic(String topicString, String incrementValue) {
        LOG.info("Topic.<init>");
        setTopicString(topicString);
        setIncrementValue(incrementValue);
        currentValue = 0;
        update();
    }

    public void setTopicString(String topicString) {
        if (topicString.equalsIgnoreCase("AAA") || topicString.equalsIgnoreCase("BBB")) {
            this.topicString = topicString;
            LOG.info("Topic.setTopicString: topic string = " + topicString);
        } else {
            LOG.info("Topic.setTopicString: invalid topic string");
            this.topicString = ERROR_MESSAGE;
        }
    }

    public void setIncrementValue(String incrementValue) {
        try {
            this.incrementValue = Long.parseLong(incrementValue);
            LOG.info("Topic.setIncrementValue: increment value = " + incrementValue);
        } catch (NumberFormatException e) {
            LOG.info("Topic.setIncrementValue: invalid increment value");
            this.topicString = ERROR_MESSAGE;
        }
    }

    public void update() {
        LOG.info("Topic.update");
        currentValue += incrementValue;
        StringBuilder buffer = new StringBuilder(topicString);
        if (!topicString.equals(ERROR_MESSAGE)) {
            buffer.append(':').append(currentValue);
        }
        LOG.info("New topic value = " + buffer);
        topicValue = new Variant(buffer.toString());

    }

    public Variant getTopicValue() {
        return topicValue;
    }

    @Override
    public String toString() {
        StringBuilder buffer = new StringBuilder();
        buffer.append(topicString).append(':').append(currentValue);
        return buffer.toString();
    }
}

ċ
RTDServer.zip
(1889k)
Sergei Piletsky,
Feb 25, 2014, 3:40 AM
Comments