Gather 'round everyone, it's story time.
Here's a great read. And the best part? This project does the reading for you! The purpose of this tutorial is to create a project which reads data from a Microsoft Excel spreadsheet.
Prerequisites: A pre-existing Excel file.
Objective: To create a project which displays proper use of the ReadExcelSpreadsheet component.
Estimated time to complete: 15-20 minutes
Step 1: Create a New Project and Import the Necessary Components
Create a new Decision Only project by selecting Decision Only in the main window which appears when you load Workflow Designer.
In the main model window of your project, click the Import Components window. The Add Library to Project window will appear:
Select Office.xdll from the list and click the Add button. Click the OK button to perform the importation.
Step 2: Edit Your Project
Drag-and-drop a new ReadExcelSpreadsheet component from the Office category of the project toolbox to the project model. The model should appear as below:
Connect the ReadExcelSpreadsheet component to the StartComponent and EndComponent as shown below:
The final step in the editing of the project model itself is to set output variables. Output variables dictate how and in what form data can be returned to the user from the project model. In this example, a variable must be created which will display properly the data taken from the Excel spreadsheet.
To set your output data, select Output Data in the Project Infobox on the left-hand side of the screen:
Click the Add button once to create one output variable. Name this output variable "Output" and set its type to "FileDataType." Then, click the Array checkbox to indicate the variable is an array.
Return to your project model by selecting the Model: Primary tab at the top of the screen.
Step 3: Editing the ReadExcelSpreadsheet Component
Right-click on the ReadExcelSpreadsheet component and select Edit Component. In the Edit Component window, select the Convert tab and enter "RetValue" in the Output Variable Name field:
This will create a temporary storage variable, called "RetValue" which will hold the data read in from the Excel spreadsheet.
Next, select the (no Category) tab:
Click the ... button to the right of the Query Rows field. The QueryRowsEditorForm window will appear:
Click the Import Excel File button, which will invoke the QueryRowsEditorImport window:
Click the Browse button to open the Open window. Select the Excel spreadsheet you wish data derived from and click the Open button.
Your new Excel file will appear in the Excel file textbox. Select the Use the sheet name field and enter a title for the Excel spreadsheet in the Sheet name textbox.
Click the OK button to import the Excel file. The Excel file will automatically populate the spaces in the QueryRowsEditorForm window:
Click the OK button to accept changes.
Click the OK button in the Edit Component window to return to your project model.
Step 4: Editing the End Component
Right-click on the EndComponent and select Edit Component. In the Edit Component window, click the ... button to the right of the Mapping property.
The Edit Data Mapping window will appear
Select the Create Value radio button. The following changes will occur in the Edit Data Mapping window:
Click the Add button to create a new output file entry. Click the OK button in the window which appears. Also click the OK buttons in the Edit Data Mapping and Edit Component windows to accept changes.
Step 5: Run and Test Your Project
Run your project by selecting the Debug button on the toolbar. This button appears as a bug with a green arrow over it.
Double-click on the blue Execute link, located at the top left corner of the window which appears.
The project will begin executing. When complete, the resulting output variable contents will be stored in the Execution Results window:
The data taken from the Excel spreadsheet can be viewed by closing the Execution Results window and selecting the Execution Log tab:
Finished!