StreamBase Documentation

TIBCO StreamBase Add-in for Microsoft Excel Sample

Contents

Introduction
Prerequisites
Running these Samples on a Standalone Installation
Loading the Samples
Files
Running CEPDemo
Running the CEPPublishingDemo Sample
Running LiveViewSample
Setting the Excel Security Level

Introduction

This collection of samples demonstrates the publish and dequeue features of the TIBCO StreamBase® Add-in for Microsoft Excel.

The Excel Add-in lets you use a StreamBase application in combination with an Excel spreadsheet to process tuple data in real time, and display the data dynamically. For StreamBase, the Excel Add-in is bidirectional and can both receive data from a StreamBase or Live Datamart application and publish data to a running StreamBase application.

When using the Add-in with Live Datamart, the Add-in can receive data from a Live Datamart application but cannot send data from Excel directly to the application. However, you can publish data to a StreamBase application that is feeding a Live Datamart data table. The Excel Add-in is described in separate documentation provided with the Add-in kit.

Prerequisites

The Excel Add-in samples have the following prerequisites:

  • The StreamBase Excel Add-in is only supported on Microsoft Windows platforms.

  • To run these samples as installed, you must have either StreamBase Studio or StreamBase Server available to run locally on the same PC or at least accessible from your network (in the latter case, see the next section for an explanation on how to proceed).

  • Microsoft Excel 2007 (32- or 64-bit version) or later must be installed and ready to use.

  • To open the sample Excel file named CEPPublishingDemo.xlsm, your Excel macro security settings must be set to medium or lower. Before opening this workbook, first follow the steps in Setting the Excel Security Level.

  • The sample spreadsheets presume that StreamBase Server runs with the default StreamBase URI on localhost at port 10000. Make sure the application runs on the default port by selecting Window → Preferences → StreamBase Studio → Test/Debug. If the number in the Port number setting is anything other than 10000, set it to 10000. Similarly for LiveView the sample expects the application to use the default port of 10080.

    If StreamBase or Live Datamart server cannot be made to use the default port you can still use the samples by changing the DefaultServer server definition in the StreamBase Explorer pane in Excel to reflect the correct URI and port number.

  • Only one Excel Add-in can be registered for use on one PC at the same time. If you have two or more StreamBase installations, each with its own Excel Add-in, you might need to re-install the Excel Add-in for the StreamBase version you want to use. Re-installing re-registers the Excel Add-in as the currently active one for the PC. Similarly if you were using the legacy Excel Adapter, uninstall it before installing the Excel Add-in.

Running these Samples on a Standalone Installation

It is possible to install the Excel Add-in by itself on a PC without a StreamBase or Live Datamart installation. If you want to run the samples on such a machine, you must:

  • Have access to a remote StreamBase Server instance (or Live Datamart server instance) somewhere on your network available to run each sample's application file.

  • Edit the Excel Add-in's server definitions to specify the StreamBase URI and port of the remote server.

Loading the Samples

By default, the sample files are installed in the following default location:

C:\Users\sbuser\AppData\Local\Cloud Software Group, Inc.Excel Add-in\sample

TIBCO strongly recommends that you use a copy of the sample files, and that you do not try to directly open the sample files in the above location. You can copy the sample into StreamBase Studio using the top-level File → Import.

  1. Start StreamBase Studio.

  2. Select File → Import from Studio's top-level menu.

  3. In the General category, select Existing Projects into Workspace and click Next.

  4. In the next panel, use the Browse button beside the Select root directory field.

  5. Navigate to your Excel Add-in installation directory, then to its sample folder.

  6. Make sure the Copy projects into workspace check box is checked.

  7. Click Finish.

Files

The Excel Add-in installs three separate samples, each one showcasing a part of its capabilities:

  • A sample demonstrating how to stream data from a StreamBase application into an Excel worksheet includes the following files:

    • A simple StreamBase application, named ExcelCEPExample.sbapp (along with the accompanying .sblayout file).

    • A feed simulation file for this application, named ExcelCEPExampleData.sbfs. This file helps the application generate sample data to feed to the Excel spreadsheet.

    • An Excel 2007 workbook named CEPDemo.xlsx.

    The sample uses the ExcelCEPExample.sbapp application running on your StreamBase Server to feed data to the CEPDemo.xlsx spreadsheet in Excel, where the data is displayed both in numeric form and in a chart.

  • A sample demonstrating how to send data to StreamBase application from an Excel worksheet includes the following files:

    • A simple StreamBase application, named ExcelCEPPublishExample.sbapp (along with the accompanying .sblayout file).

    • An Excel 2007 workbook named CEPPublishingDemo.xlsm.

    The sample allows you to construct tuples in VBA code invoked from the CEPPublishingDemo.xlsm workbook, and to send these tuples to your StreamBase Server running the ExcelCEPPublishExample.sbapp application. When the application receives a tuple, it mirrors it back to an output stream which is being watched by the Excel workbook. The spreadsheet displays the mirrored tuple so you can easily ascertain that it has been successfully published.

  • A sample demonstrating how to stream data from a Live Datamart application into an Excel worksheet includes the following files:

    • An Excel 2007 workbook named LiveViewSample.xlsx.

    This sample doesn't come with its own Live Datamart application. Rather, it is designed to use the stock HelloLiveView sample application which ships with every version of StreamBase. The workbook sends queries to HelloLiveView tables to obtain different views into its data and display them in the spreadsheet's cells.

    Note

    Refer to the main Live Datamart documentation for instructions on how to load and run HelloLiveView.

Running CEPDemo

  • Running in StreamBase Studio:

    1. Open Excel and load the CEPDemo.xlsx spreadsheet. If you used File → Import, the spreadsheet is located in your Studio workspace, which by default is: 

      C:\Users\sbuser\Documents\StreamBase Studio 7.3 Workspace\ExcelAddInSamples

      The spreadsheet's cells are populated with #WAIT entries. This is the normal display when the spreadsheet cannot yet contact StreamBase Server.

    2. In StreamBase Studio, in the Package Explorer, double-click to open the ExcelCEPExample.sbapp application. Make sure the application is the currently active tab in the EventFlow Editor.

    3. Click the Run button. This opens the SB Test/Debug perspective and starts the application. Notice the Application Output tab displays tuples being emitted on the DataOut output stream.

    4. Switch back to Excel. The spreadsheet's cells now show data extracted from the DataOut output stream of the running application.

    5. When done, close the CEPDemo.xlsx workbook.

    6. Return to Studio and press F9 or click the Stop Running Application button.

  • Running from the command line:

    1. Open a StreamBase Command Prompt and navigate to your workspace copy of the Excel Add-in sample, as described above.

    2. Start StreamBase Server running the sample application with the following command: 

      start /b sbd ExcelCEPExample.sbapp

    3. Wait for a [listening] message from the server.

    4. Open Excel and load the CEPDemo.xlsx spreadsheet. Watch the spreadsheet's cells fill with values from the running StreamBase application.

    5. When done, close the workbook.

    6. Hit Ctrl+C in the Command Prompt window to close the server and exit the feed simulation.

Running the CEPPublishingDemo Sample

  • Running in StreamBase Studio:

    1. In StreamBase Studio, in the Package Explorer, double-click to open the ExcelCEPPublishExample.sbapp application. Make sure the application is the currently active tab in the EventFlow Editor.

    2. Click the Run button. This opens the SB Test/Debug perspective and starts the application. Notice the Application Output tab displays tuples being emitted on the DataOut output stream.

    3. Open Excel and load the CEPPublishingDemo.xlsm spreadsheet.

      Note

      You may need to enable macros for this workbook when prompted to do so by Excel -- refer to the Setting the Excel Security Level section for more information on macro security.

      The worksheet displays a Send Tuple To Server button. When you push this button, a tuple is constructed and sent to the running StreamBase application. The application in turn echoes this tuple back to an output stream, which is picked up by the worksheet and displayed below the button.

    4. When done, close the CEPPublishingDemo.xlsm workbook.

    5. Return to Studio and press F9 or click the Stop Running Application button.

  • Running from the command line:

    1. Open a StreamBase Command Prompt and navigate to your workspace copy of the Excel Add-in sample, as described above.

    2. Start StreamBase Server running the sample application with the following command: 

      start /b sbd ExcelCEPPublishExample.sbapp

    3. Wait for a [listening] message from the server.

    4. Open Excel and load the CEPPublishingDemo.xlsm workbook.

      Note

      You may need to enable macros for this workbook when prompted to do so by Excel -- refer to the Setting the Excel Security Level section for more information on macro security.

      The worksheet displays a Send Tuple To Server button. When you push this button, a tuple is constructed and sent to the running StreamBase application. The application in turn echoes this tuple back to an output stream, which is picked up by the worksheet and displayed below the button.

    5. When done, close the workbook.

    6. Hit Ctrl-C in the Command Prompt window to close the server and exit the feed simulation.

You can examine the VBA code invoked when the Send Tuple To Server button is clicked by pressing Alt+F11 in the worksheet, which brings up Excel's VB Code Window. The relevant code is in Sheet 1's CommandButton1_Click() function.

Running LiveViewSample

  • Running in StreamBase Studio:

    1. Open Excel and load the LiveViewSample.xlsx spreadsheet.

      The spreadsheet's cells are populated with #WAIT entries. This is the normal display when the worksheet cannot yet contact the LiveView server.

    2. In StreamBase Studio, launch the HelloLiveView sample as described in its README file.

    3. Switch back to Excel. The worksheet's cells now show data resulting from executing the queries sent to the Live Datamart application.

    4. When done, close the LiveViewSample.xlsx workbook.

    5. Return to Studio and press F9 or click the Stop Running Application button.

  • Running from the command line:

    1. Launch the HelloLiveView sample from a StreamBase Command Prompt as described in the README file.

    2. Wait for a [listening] message from the server.

    3. Open Excel and load the LiveViewSample.xlsx spreadsheet. Watch the spreadsheet's cells fill with values from the running Live Datamart application.

    4. When done, close the workbook.

    5. Hit Ctrl-C in the Command Prompt window to shut down the LiveView server.

Setting the Excel Security Level

Excel might display a dialog warning that macros in the spreadsheet are not digitally signed. This can occur if you open the sample macro spreadsheet CEPPublishingDemo.xslm. To resolve the problem, lower your Excel security settings as follows:

  1. Click OK to dismiss the error message.

  2. In Excel, select Tools → Macro → Security.

  3. In the Security dialog's Security Level tab, change the security level to Medium, and click OK.

  4. In Excel, select File → Save to save your settings. Exit and restart Excel.

Now each time you open one of the sample spreadsheets, Excel displays a different dialog with another security warning about macros. Click Enable Macros to dismiss the warning.