Export data and update charts in Excel in real-time

Problem scenario:

I want to connect an RS-2322 port to MS Excel. The port will provide a stream of ASCII numeric characters to Excel. I want Excel to automatically receive and graph the ASCII data, sort of like an oscilloscope. I can control the format of the incoming ASCII data.

Requirements:

• Advanced Serial Data Logger Professional, Enterprise, or a trial version;
• Direct Excel Connection

It is assumed that:

You've prepared parser items for export.

You can work in Microsoft Excel, specifically create charts in an Excel file.

For this tutorial, all items were prepared in the previous part.

Also, you may read other examples:

• Write data to an XLS file without Microsoft Excel installed
• Send data to Excel through the DDE interface
• Export real-time data and draw charts in OpenOffice or LibreOffice Calc

Solution:

All parser items are now ready for export to the "Direct Excel Connection" plugin. Please, open the configuration window of the "Direct Excel Connection" plugin (fig.1) by selecting the module in a list and clicking the "Setup" button on the "Data export" page.

Fig.1. Charting in Excel. Excel connection.

Then select options in field #1 and field #2. These options will allow you to start Excel and display it on your desktop. Excel will be started automatically on program start.

Because we want to draw a chart in a real-time environment, then we should prepare an Excel file before the next step. In this example, we've created a diagram with three plots. Each plot is based on data from columns A, B, or C. We'll place FLOW1, VOLW1, and TEMP1 to columns, and the chart will use these values and automatically redraw every time. We'll use the last 30 values on the diagram only.

Fig.2. Charting in Excel. Excel file.

The figure above contains the following elements:

1. The data source for plots in this example;
2. Diagram;
3. Excel worksheet.

The Excel file, which we've created you can download here and use it in your work.

In the next tab, you should specify your workbook options as per your requirements (real-time charting).

Fig.3. Charting in Excel. Workbook options.

You should select the "Use a workbook from a file" option (fig.3 pos.1) to use an existing file and write data to this file. The path and name of this file you created before (fig.2) should specify in field #2. The option at the position 3 allows you to save all the written data when the program exits.

Fig.4. Charting in Excel. Worksheet options.

In the next tab, with the help of the option at the pos. 3, you should specify your worksheet number in the workbook. We've created the workbook, where the worksheet is first. Therefore we've specified this number on the "Worksheet" page.

The last "Binding" page (fig.5) is significant. The column description and column position that you require to be bound to the variable's names that are specified on this page.

Fig.5. Charting in Excel. Binding.

Any new items may be added by clicking the "Add item" button (Fig.5, pos. 7). Before adding an item, the program will ask you about an item description. You can type any characters here, which will help you to remember a variable's content. For this example, three variables with their corresponding descriptions have been added.

Each data export item has several properties:

1. Parser item name. It is a parser variable name, which you have created in the parser configuration. You may select a variable name from a drop-down box or type this name manually;
2. Filling mode. This mode instructs the plugin what to do with data. Suppose the number of cells for filling is exceeded. In that case, data is moved upwards (if the plugin places data on columns), or to the left (if the plugin places data on rows), and new data appears in the position that becomes unoccupied after data is moved. Thus, the FIFO filling method is used. In this example, we need the last 30 values. Therefore we've selected the "Move" method;
3. Filling orientation. In this example, we need to send data to Microsoft Excel by columns. Therefore we've selected the "Vertically" method;
4. Left top cell. You should specify the upper-left cell coordinates, starting from which data will be sent to Microsoft Excel. The format the coordinates are specified in should comply with the standard accepted in Microsoft Excel. For example, a record like A1 will mean the coordinates of the top-left cell on the worksheet;
5. Cells to fill count Here, you can specify the maximum number of cells in a column or in a row filled when data is sent to Microsoft Excel.

Click the "OK" button and close the Direct Excel Connection plugin configuration window and the "OK" button in the options window.

Okay, all settings have been completed, and we are ready to capture data to an Excel file.

If the data export module and the parser had been correctly set up, you should see real-time drawing in Microsoft Excel (fig.6).

Fig.6. Charting in Excel. Drawing.

The figure above contains the following elements:

1. Last 30 values of the parsed data;
2. Diagram with plots.

The Excel file with all values and plots you can download here.

Related articles:

• RS232 to Excel. Four methods to send data from RS232 to Excel Plugins: Various
• Write data to an XLS file without Microsoft Excel installed Plugins: Local database
• Send data to Excel through the DDE interface Plugins: DDE Server
• Export data and charting in Excel in real-time Plugins: Direct Excel Connection
• Write data to a CSV text file Plugins: Local database, DDE Server
• Export real-time data and charting in OpenOffice or LibreOffice Calc Plugins: Direct OpenOffice/LibreOffice Connection
• Write data to an XLS file without Excel Tags: advanced export, rich formatting, many options. Plugins: Excel Export Pro
• Write data to an XLS file without Excel Tags: variant #2. Plugins: Local database