Example 5: Excel File with Meta Information and Channel Data

Example 5: Excel File with Meta Information and Channel Data

The following example shows you how to create a DataPlugin to read the Excel file DataPluginExample5.xlsx. This workbook contains a worksheet with meta information and the channel data. Refer to DataPluginExample5.vbs to open the DataPlugin script file.

Data Format Description

The workbook contains information on the author and the title. The worksheet to be read in contains the description and the data from eight channels. The second row contains the name of the channels and the third row contains the corresponding units. The first and fourth row and the first column of the worksheet are empty. Then comes the channel data.

The following graphic illustrates this format:

Configuring a DataPlugin

1. Select Settings»Extensions»DataPlugins in DIAdem NAVIGATOR.

2. Click Create DataPlugin to configure a new DataPlugin.

3. A dialog box opens where you can define the parameters for the new DataPlugin.
   
   Enter the Name of the DataPlugin, for example, DataPluginExample5.
   
   Below Filename Extensions, enter a list of file types that the DataPlugin can access, for example, *.xlsx.

   Enter the Filename, including the path and the filename extension, to specify the script that DIAdem uses to connect the DataPlugin, for example, DataPluginExample5.vbs. Click ... to specify the script in a file selection dialog box. If you enter the name of a script that does not exist, DIAdem generates the script.

   
   Use Icon to specify the name, including the path and the filename extension, of the symbol file to which DIAdem links the DataPlugin, for example, DataPluginExample5.ico. Click ... to specify the symbol file in a file selection dialog box.

   Click Advanced and select File reader Spreadsheet reader to add a reference to a workbook, which is to be loaded, to the DataPlugin.

4. Click OK.

5. The new DataPlugin is included in the list of DataPlugins. Close the dialog box.

Note  You also can select Settings»Extensions»DataPlugins in DIAdem NAVIGATOR or in DIAdem SCRIPT to import, to export, to modify, or to delete an existing DataPlugin.

You can now start programming the DataPlugin.

Creating the DataPlugin

1. Open the DataPluginExample5.vbs script in the script editor.
   The script contains an example. USI uses the prepared procedure ReadStore to read the contents of the specified file File. The Workbook parameter contains the opened file, which you selected in the DataFinder.

2. Delete the contents of the ReadStore procedure until only the following lines remain:

Option Explicit
Sub ReadStore(Workbook)
End Sub

Providing Information about the Workbook

3. Read in the information which the workbook contains about author and title and transfer this information to the properties of the root object of the data set.

Call Root.Properties.Add("Title", Workbook.WorkbookInfo.Title)
Call Root.Properties.Add("Author", Workbook.WorkbookInfo.Author)

Specifying the Data Area in the Worksheet

4. Transfer the data area of the first worksheet to a CellBlock. The CellBlock makes it easier to access the data of the individual channels later. The data area starts at the fifth row of the second column of the worksheet.

Dim oCurrSheet: Set oCurrSheet = Workbook.Sheets(1)
Dim oCellBlock: Set oCellBlock = oCurrSheet.GetCellBlock(5,2)

Reading in Data

5. Before you read in the channel data create a new channel group to which you can assign these channels. The channel group should have the same name as the worksheet. Also transfer the index of the worksheet to the channel group property Description. 

Dim oNewGroup: Set oNewGroup = Root.ChannelGroups.Add(oCurrheet.Name)
Call oNewGroup.Properties.Add("Description", "Sheet_"&oCurrSheet.Index)

6. You can now read out channel descriptions and create the appropriate channels. Use GetCellValue to read out the contents of individual rows and the names and the units of the channels. Then use the channel data columnwise from the CellBlock. Use AddDirectAccessChannel to generate the channels according to the descriptions.

Dim i, Name, Unit, oNewChannel, DAChannel
For i = oCurrSheet.MinPosition.Column to oCurrSheet.MaxPosition.Column
  Name = oCurrSheet.GetCellValue(2,i)
  Unit = oCurrSheet.GetCellValue(3,i)

  Set oNewChannel = oCellBlock.Channels.Add(Name)
  Set DAChannel = oNewGroup.Channels.AddDirectAccessChannel(oNewChannel)
  Call DAChannel.Properties.Add("unit_string",Unit)  
Next

7. Save the DataPlugin.

Loading the Data

1. Select Settings»DataFinders»Properties»DataPlugins in DIAdem NAVIGATOR and enable the DataPluginExample5 DataPlugin.

2. Click OK to close the dialog boxes.

In the DataFinder you now can navigate in the DataPluginExample5.xlsx file and drag and drop the channels into the DIAdem Data Portal.
 

Overview of the Methods and Properties Used

Root.Properties

Properties.Add

Workbook.WorkbookInfo

WorkbookInfo.Title

WorkbookInfo.Author

Workbook.Sheets

Sheet.CellBlock

Root.Channelgroups

Channelgroups.Add

Sheet.MaxPosition

Position.Column

Sheet.GetCellValue

CellBlock.Channels

Channels.Add

Channels.AddDirectAccessChannel

DirectAccessChannel.Properties