Microsoft Excel Adapter Sample

Introduction

This sample demonstrates the StreamBase Adapter for Microsoft Excel. The Excel Adapter lets you use a StreamBase application in combination with an Excel spreadsheet to process tuple data in real time, and display the data dynamically. The Excel Adapter is bidirectional, and can both receive data from a StreamBase application or publish data to a running StreamBase application. The Excel Adapter is described in Excel External Adapter.

Prerequisites

The Excel Adapter sample has the following prerequisites:

To run this sample as installed, you must have either StreamBase Studio or StreamBase Server available to run locally on the same PC. In practice, this means this sample normally runs on a PC with StreamBase installed.
Microsoft Excel 2002 (Excel XP) or later must be installed and ready to use.
To open the sample Excel files other than rtd.xls, your Excel macro security settings must be set to medium or lower. Before opening these spreadsheets, 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.
Only one Excel Adapter 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 Adapter, you might need to re-install the Excel Adapter for the StreamBase version you want to use. Re-installing re-registers the Excel Adapter's DLL as the currently active one for the PC.

Importing This Sample into StreamBase Studio

In StreamBase Studio, import this sample with the following steps:

From the top menu, click File → Load StreamBase Sample.
Select the microsoft-excel sample from the External Adapters list.
Click OK.

StreamBase Studio creates a single project containing the sample files.

Sample Location

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

On Windows: C:\Program Files\StreamBase Systems\StreamBase.n.m\sample\microsoft-excel\
On UNIX: /opt/streambase/sample/microsoft-excel/

When you load the sample into StreamBase Studio, Studio copies the sample project's files to your Studio workspace. StreamBase Systems recommends that you use the workspace copy of the sample, especially on UNIX, where you may not have write access to /opt/streambase. In the default installation, the path to this sample in your Studio workspace is:

UNIX:       
  ~/streambase-studio-n.m-workspace/sample_microsoft-excel
Windows XP:
  C:\Documents and Settings\username\My Documents\
      StreamBase Studio n.m Workspace\sample_microsoft-excel
Windows 7 and Windows Vista:
  C:\Users\username\Documents\StreamBase Studio n.m Workspace\sample_microsoft-excel

This Sample's Files

The Microsoft Excel sample has the following files:

A simple StreamBase application, rtd.sbapp.
A feed simulation file for the application, rtd.sbfs.
A run.bat file that can be optionally used to run part of the sample from the command line.
Four Excel spreadsheet files that show different aspects of the Excel Adapter:
- rtd.xls demonstrates how to display tuples using both historical and filtered methods.
- rtd-with-pivot-table.xls demonstrates application output using the Excel pivot table method.
- rtd-publish.xls demonstrates publishing tuples from Excel to a StreamBase application.
- meta.xls demonstrates how to extract and display StreamBase metadata.
The rtd.xls and rtd-with-pivot-table.xls sample spreadsheets display data from each of the fields in the sample application's output stream. In the case of the publishing spreadsheet, rtd-publish.xls, a tuple originates in the spreadsheet, is published to the StreamBase sample application, and returns through the adapter to Excel.

Running the Sample in StreamBase Studio

Open Excel and load the rtd.xls spreadsheet. Click the Enable Macros button to continue loading the spreadsheet.

The spreadsheet's cells are populated with #N/A entries. This is the normal display when the spreadsheet cannot yet locate StreamBase Server.
In StreamBase Studio, in the Package Explorer, double-click to open the rtd.sbapp application. Make sure the application is the currently active tab in the EventFlow Editor.
Click the Run button. This opens the SB Test/Debug perspective and starts the application.
In the top left of the SB Test/Debug perspective, click to open the Feed Simulations tab.
The rtd.sbfs file is in the list of feed simulation files. Select this entry and click Run.
Switch back to Excel. The spreadsheet's cells now show data extracted from the output stream of the running application.
When done, close the rtd.xls spreadsheet, which automatically closes Excel. Keep the StreamBase application running in Studio and re-open Excel to try one or more of the other sample spreadsheets.
When done, return to Studio and press F9 or click the Stop Running Application button.

Running the Sample at the Command Line

This section describes how to run the sample using a Windows command prompt window. Be sure to use the StreamBase Command Prompt from the Start menu as described in the Test/Debug Guide, not the default command prompt.

Open a StreamBase Command Prompt and navigate to your workspace copy of the Excel adapter sample, as described above.
Start StreamBase Server running the sample application with the following command:
```
start /b sbd rtd.sbapp
```
Wait for a [listening] message from the server, then press Enter to restore the command prompt.
Run the sample's feed simulation file:
```
start sbfeedsim rtd.sbfs
```
This opens a new Command Prompt window running the sbfeedsim command.
Open Excel and load the rtd.xls spreadsheet. Click the Enable Macros button to continue loading the spreadsheet. Watch the spreadsheet's cells fill with values from the running StreamBase application.
When done, close the rtd.xls spreadsheet, which automatically closes Excel. Keep the StreamBase application running and re-open Excel to try one or more of the other sample spreadsheets.
When done, enter the following command in the Command Prompt window to close the server and exit the feed simulation:
```
sbadmin shutdown
```

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 are using Microsoft Excel 2002 (Excel XP) or if you open the sample spreadsheets other than rtd.xls using any supported version of Excel. To resolve the problem, lower your Excel security settings as follows:

Click OK to dismiss the error message.
In Excel, select Tools → Macro → Security.
In the Security dialog's Security Level tab, change the security level to Medium, and click OK.
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.