Microsoft Excel interfaces

It is possible to connect to the data of an Excel file by using the Excel-interface of the Publisher. This can be used with multiple sheets, and it is possible to both read from and write to Excel sheets. The file we will use in this example looks like this:

The Microsoft Excel file
The Microsoft Excel file

Step 1: Create the data source

You must first make a connection to your Excel file using data sources. Connecting your Excel file can be done by giving the Berkeley Studio the location of the file, as shown below. In the example, we use an Microsoft Excel file called ‘myxmltest.xlsx’.

Connecting to the Excel file
Connecting to the Excel file

Step 2: Creating a data structure

If you want to import complex data like an Excel sheet, you need a data structure. If you are not familiar with data sets in the Berkeley Studio, we recommend you take a look at datasets.

We defined a simple graph as shown below:

Defining a graph for the Excel connection
Defining a graph for the Excel connection

The graph is called gmyexceltest, with one node, namely ‘sheet1’, and two variables, namely ‘mycolumn1’ and ‘mycolumn2’. These names are the same as in the Excel file, just to make the example more understandable. You can of course choose any name you want in your own model.

Step 3: Connecting

To make the connection, take the following steps:

  1. Open the [ACTIONS > External data] dialog;
  2. Select Complex data type;
  3. Give a name, myexceldata in this example;
  4. Select the right data type, this is the graph (gmyexceltest in this example);
  5. Select the connection type, the one defined in the data sources (myexceltest in this example);
  6. Choose whether you want to read or write the Excel file;
  7. Select the key you want to use to match the Excel and the dataset;
  8. Select the right sheet of the Excel file.

Note that the key should be a column in your Excel file with unique values. These values will be used to match the data in your data set with the Excel file when you write data back to Excel.

Now you can link your model data to a Excel column. You can do so by dragging one of them onto the other. When this is done, a lightning bolt icon will appear as is shown in the picture below.

Connected Excel file
Connected Excel file

Press [OK], and you are done!