Using the Script Component

Use the Script component to create custom methods to include in OpenSpan Studio solutions. You can use C# to write methods. Note the following Script component requirements:

This example shows how to return a list of names of the worksheets in an Excel workbook. To access the worksheet and workbook objects for use in the method, the Microsoft.Office.Interop.Excel component must be added as a reference.

  1. Add a Script component to the automation. Right-click and select the Global tab to make sure the Script methods are accessible to all automations in the project.

  2. Browse the References property to open the Script References dialog.

  3. In the Script References dialog, click the GAC button. The GAC Assemblies dialog appears.


     

  4. Scroll to the Microsoft.Office.Interop.Excel assembly, highlight the assembly and then click OK to add the assembly for the script.

  5. Right-click on the References folder in the Solution Explorer and select Add Reference. The Add Reference dialog appears.

  6. Click the .NET tab, scroll to Microsoft.Office.Interop.Excel Component Name, and click Add. The component appears in the Selected projects and components grid.

  7. Click OK to continue. Microsoft.Office.Interop.Excel appears in the References folder in Solution Explorer.

  8. Double-click on the Script component. The Script Constructs dialog appears. Confirm that the language is C#.

  9. Click the Add Method button.

  10. Enter GetWorksheets for the method Name.

  11. Enter _Workbook excelWkBook for the Parameters. The _Workbook type is the COM object for Excel workbooks.

  12. Enter ArrayList as the result.

  13. Enter the following code:
     

ArrayList al = new ArrayList();

//Get Sheets collection representing all the worksheets

//in the workbook excelWkBook

Sheets excelSheets = excelWkBook.Worksheets;

//Create null Worksheet object excelWorksheet

Worksheet excelWorksheet = null;

//If the Sheets collection is not empty for the input Workbook, iterate

//through the sheets, get each sheet, and add the name of the sheet

//to the arraylist al

if (excelSheets!= null)

{

for (int i = 1; i<= excelSheets.Count; i++)

{

excelWorksheet = (Worksheet)excelSheets.get_Item((object)i);

al.Add(excelWorksheet.Name.ToString());

}

}

return al;

  1. Click the Validate button. If no error message is returned, click the Save Method button then click OK.

  2. Add a Windows form to the project and complete it as shown below:

  3. Add an automation and complete it as shown below:

  4. Save, debug, and build the project.

  5. If no build errors are encountered, enter the name (fully pathed) of a workbook with multiple worksheets in the text box on the Windows form and click Get Sheets. A list of worksheet names is returned, as shown here: