This is a showcase review for our sponsors at The Code Project. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.
The IMSL® C# Library saves development time. For more information, or to request a free evaluation click here! You may also RESERVE YOUR 20% SAVINGS and buy later!
Table of Contents
Microsoft first released the Visual Studio Tools for Office (VSTO) for the Office 2003 product family and the .NET Framework version 1.1. The concept allows .NET developers to create applications that combine the features of Microsoft Office and .NET using the Office Suite as a development platform. However, usage was not straightforward and deployment was very difficult, largely due to misunderstood .NET security settings.
In November 2005, Microsoft released the .NET Framework version 2.0 and a variety of updated tools. One of these tools was Microsoft Visual Studio 2005, made available in several varieties including the Team Studio Edition. An updated version of VSTO, VSTO 2005, was designed to overcome many of the challenges users faced when using the first version and was included in the Team Studio Edition of Visual Studio 2005.
This document focuses on the combination of VSTO 2005 and Visual Studio 2005 Team Edition. In November 2006, along with the Microsoft Office 2007 suite of products, a new version of the Visual Studio Tools for Office, VSTO 2005 SE (second edition), was released. Excel 2003 is the target environment due to its wide adoption; however, Excel 2007 is capable of running the example project developed in this paper. The next release of Visual Studio, codenamed Orcas, is rumored to contain the VSTO tools in the Professional version and will not require the upgrade to one of the Team Editions. This update will include more functionality to leverage the Office 2007 interface elements.
The core component for developing Excel applications in conjunction with the .NET Framework is Visual Studio 2005 Team Studio Edition. Other versions of Visual Studio 2005 do not include the Visual Studio 2005 Tools for Office. When installing the Team Studio Edition, be sure to either do a complete install or check the box for VSTO components.
This example works with Excel 2003, a component of Office 2003, and Office 2003 Service Pack 1 must be installed to support VSTO. In addition, the Visual Studio 2005 Tools for Office Runtime must be installed (VSTOR.exe). If all components are not properly installed, when you first try to create an Office project you may be greeted with the following error message:
If greeted with the error message, follow the link and instructions to install all required components and updates.
Visual Studio also provides the option to install support for several different languages. We will be calling the IMSL C# Numerical Library, but as a .NET assembly with language-independent metadata, it can be used equally as easily from VB.NET, C++ or J#. Visual Basic is easier to work with when writing Office applications because it simplifies the calling of the Office object model; thus this language is used for the examples. It is certainly possible to create these examples using C# or other .NET languages, but the dearth of documentation and complex method signatures create a challenge. To follow along the examples as written, you need to have VB.NET support installed.
All of the examples use the Excel Workbook template to call the IMSL C# Numerical Library from inside an Excel workbook. There are several other ways to integrate Excel with an external managed .NET library, including the creation of add-ins. The conceptual difference is that the workbook model described here is referred to as a “document-level project” while an add-in is an “application-level project.”
At the document level, all customization is unique to one or more sheets contained in a single workbook, but at the application level, custom Excel functions would be available to all workbooks. The end results may contain the same kinds of functionality discussed in this document, but the interface and development are variations on the theme. For more information on application-level add-ins, please refer to the copious online documentation.
To create a new project, the first step is to open the dialog in Visual Studio 2005. For this example, we want to create a new Visual Basic project for an Excel Workbook. There are several ways to do this that include choosing Create Project… from the opening summary page or selecting the New Project… option from the File menu. On the system used in this example, the user is configured as a C# developer, so the default project selections are Visual C#. To create a Visual Basic .NET project, we have to browse under Other Languages to the Visual Basic templates and click on the Office section to find that several templates are available. We want to allow users within an Excel Workbook context to use IMSL functionality, which is accomplished by creating an Excel Workbook project, so select that template as shown in the following screenshot:
Name the new project ExcelWorkbookIMSL and click OK to bring up the next step in the new project wizard. For future projects, you may already have an existing workbook created; however, here we shall start with a brand new workbook as shown in the next image:
At this stage, you should see an empty Excel workbook; it usually contains three blank worksheets. In the Solution Explorer, you will find “code behind” Visual Basic (*.vb) files for each sheet and for the workbook as a whole. All these empty source files created from the templates exist in the solution at this point, but are blank except for empty Startup and Shutdown methods. Here are images of the blank work book and source code files:
Once the project is created, we need to add a reference to the IMSL C# Numerical Library assembly. In a typical Visual Studio project, the Solution Explorer contains a References heading that can be right-clicked to add a reference to other projects or assemblies. For this Excel Workbook project, you need to right-click on the project heading itself to display the Add Reference… dialog as shown in the next screenshot:
The next dialog that appears allows you to browse to the IMSL C# assembly imslcs.dll; the default installation directory is C:\Program Files\VNI\IMSLCS4.0.2\bin. Once added, we can confirm the library is available by opening one of the Visual Basic source files (for example, by right-clicking on Sheet1.vb in the Solution Explorer and selecting View code) and adding an imports line. Code completion, as shown in the following image, should be active if the IMSL Library has been successfully added as a reference.
This confirmation with Imports is valuable, but is not used in the examples. All examples use the full class names, including the namespace, to create inclusive code snippets. As an external .NET reference, a copy of the imslcs.dll assembly should be found in the bin\Debug or bin\Release directory with the spreadsheet object. If imslcs.dll has been added to the Global Assembly Cache, a local copy may not exist in the project, but a copy is required when deploying the application to other computers. For more information on deployment, see the Deployment section of this document. With a blank project and the IMSL C# Numerical Library available, we are ready to start building examples.
Examples in this document are presented in order of increasing complexity. Each one is written in Visual Basic, although it is possible to create them in C# or other .NET languages as discussed previously. A single project is used throughout, adding additional components and code behind methods as needed, but they are independent and later examples do not rely on code from earlier ones.
One of the simplest things we could do with the IMSL C# Numerical Library is to minimize the interaction with the spreadsheet objects and hard code variables. To begin, we need to provide a means for the user to initiate the action. There is a variety of ways to allow a user to interact with the sheet, but to avoid additional complexity, button controls are used in these examples.
To view the spreadsheet and open the Visual Studio Toolbox, select the ExcelWorkbookIMSL.xls tab. If the Toolbox is not visible, you can drag it open from the left edge (usually) or press Ctrl-Alt-x to open the window. Select the Button control and drag in the spreadsheet to create it. Upon creation, it is called Button1 and has that name as its text property. To be more descriptive, with the button selected, scroll down in its Properties view on the lower right and change the name via the Text property field to Erf(0.2). This name will make more sense in a minute. See the following image for the current status inside Visual Studio 2005:
Next, we can have Visual Studio generate some code to allow us to fill in the blanks and take some action when the user clicks the button. Double-click the button and the view changes to the code behind Sheet1.vb with a blank Button1_Click method. This is the method executed when a user clicks the button at runtime. For this example, we return the value of the error function evaluated at 0.2. Fill in this method so that it reads as follows:
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim result As Double
result = Imsl.Math.Sfun.Erf(0.2)
Me.Application.ActiveCell.Value = result
End Sub
In detail, this code creates a Double named result and then calls the shared (static) method Erf in the Sfun class of the Imsl.Math namespace. The output is then stored in the active cell. To test this code, the Start Without Debugging command automatically builds the solution and starts Excel. You can do this by pressing Ctrl+F5 or by selecting the Start Without Debugging option under the Debug menu item on the menu bar. You should then see Excel with your new button. Click the button to see the result filled in the active cell. Screenshots follow:
This example shows one straightforward way to embed IMSL C# Library functionality in an Excel worksheet. The code behind the button interaction could be significantly more complex, referencing almost anything available in the .NET Framework using the base class libraries or external assemblies like the IMSL C# Numerical Library. Interaction with the spreadsheet object was limited to writing a scalar output value to the active cell, but the following examples expand on that interaction and the types of data being passed back and forth.
The next exercise takes the previous example to the next level. The input argument for the Erf method is retrieved from the spreadsheet and output is returned to a specific cell. Interaction with specific cells in the sheet is accomplished using Named Ranges. The advantage of a Named Range is that the cells appear to the user as any other cell, but are accessible by name in the code behind for the developer. Building off the same project we have already created, select cell A4 and right-click. Select the Manage Named Ranges… option to bring up a dialog. The current range is listed in the “Refers to” box at the bottom, while the top box awaits your typing a name. Enter ErfInput and select Add followed by OK. Repeat the process for B4, naming it ErfOutput as seen in the following screenshot:
These cells are shaded with colors in the following images to distinguish them from the others in the sheet. Of course, such coloring is optional, but important cells will often be highlighted or labeled in some manner in larger workbook applications.
To allow user interaction, another button control is added below these two cells and named Compute Erf(). This button is created and edited as before. Double-clicking the button again brings up the empty callback method for this button’s action. Fill that method in with the following code:
Private Sub Button2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.Click
Dim result As Double
Dim input As Double
input = ErfInput.Value
result = Imsl.Math.Sfun.Erf(input)
ErfOutput.Value = result
End Sub
...named range ErfInput. As you enter the above code, code complete reveals a large number of properties and methods available for this object because it implements the Range interface. For this case, we just need to retrieve its Value property. The value is treated as a Double, but it is actually returned as an Object. Returning an Object is a general implementation because a cell may contain anything from text to formulas. You should include error checking in a deployed application, since an exception will be thrown if we try to cast text into a Double type, as shown below:
Here we have entered the text “a” into the ErfInput range, which cannot be cast into a Double type. While the details section of the exception is descriptive enough, a cleaner approach would be to catch this conversion error in the code and then report the problem cleanly in a MessageBox, for example.
Moving on, assuming the input variable is now a valid Double value, the Erf method is called and the result returned by setting the Value property for the ErfOutput named range. A successful run is shown below:
With this example, we allow the user of the spreadsheet to enter a value in cell A4 and evaluate the value of the error function, returning the result in cell B5 with a click of a button. A single value is used as input to a static method for this example. The next example expands to accepting an array of values as input into an IMSL C# Library class.
This example increases complexity another notch by accepting an array as input. Further, the array may be any length, as the size is retrieved in a Named Range while the values are located below that cell. The IMSL Library function used is the SignTest class. The previous example used a Shared (static) method in the Sfun class, but SignTest requires a constructor and a follow-up method call. However, the code behind supports this more complex object structure and is not limited to static method calls or even using one class or reference library at a time.
Continuing to work with the ExcelWorkbookIMSL.xls spreadsheet, follow the same instructions above for creating a named range to name cell A8 SignTestCount and cell B8 SignTestOutput. The cells in column A below A8 are used as input. For flexibility, enter the formula =COUNT(A9:A1000) into cell A8 to allow us to enter almost one thousand values. In this example, we are only using a handful of values, but this is an example of a general formula that could be used. The cells A9 through A27 are filled in with values from the SignTest example in the IMSL C# Numerical Library documentation, as shown in the screenshot. Another button control is created, named Sign Test, and its Click method contains the following code:
Private Sub Button3_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button3.Click
Dim count As Integer
Dim i As Integer
Dim result As Double
Dim st As Imsl.Stat.SignTest
count = SignTestCount.Value
Dim x(count) As Double
Dim values(count, 1) As Object
Dim inputRange As Excel.Range
inputRange = Me.Range(SignTestCount.Offset(1, 0), _
SignTestCount.Offset(count, 0))
values = inputRange.Value( _
Excel.XlRangeValueDataType.xlRangeValueDefault)
For i = 1 To count
x(i) = values(i, 1)
Next
st = New Imsl.Stat.SignTest(x)
result = st.Compute()
SignTestOutput.Value = result
End Sub
This is the most complex code shown so far, but much of it is very similar to the previous examples while the rest includes instructions on retrieving an array from a range of cells. The first section declares the variables in use, including the SignTest class instance st. Notice that the number of cells, count, is collected from the named range SignTestCount.Value and that this variable is used to declare the size of the x array.
We then define a new Range object and use the Offset method to define its endpoints relative to the SignTestCount cell, expecting the input values to be listed directly below this cell. To retrieve the contents of this range properly, the values variable is declared as a two-dimensional Object array and its Value property is passed the option argument XlRangeValueDataType.xlRangeValueDefault. For more details, refer to the MSDN documentation for NamedRange.Value.
With this call, we are able to parse the values array of Objects as Double values and fill the x array. The values array cannot be passed directly to the SignTest object since the constructor expects a one-dimensional array of Doubles. Once we loop through the array to fill x, the SignTest object is instantiated and its Compute method is called. The result is returned to the SignTestOutput named range. The result of running this example is shown below (for formatting reasons, the columns are split in half):
For this example, we demonstrate matrix multiplication, something that is not typically performed in a spreadsheet and yet is used regularly in many areas of numerical analysis. From the IMSL C# Library, the overloaded Matrix.Multiply static method accepts one-dimensional and two-dimensional arrays of Double values and returns an appropriately sized Double array containing the product of the matrix-matrix, vectormatrix or matrix-vector combination. Named ranges are used again to define the input variables, but their size is determined at runtime instead of requiring a counting cell as in the SignTest example previously. Defining a range of cells as a named range is done the same way as single cells: highlight the area and right-click, selecting Manage Named Ranges… from the list.
Since the output range depends on the sizes of the input matrices, we use the active cell for the location of the top left corner of the output. The code in the button’s callback method is as follows:
Private Sub Button4_Click(yVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button4.Click
Dim objectMatrixA(,) As Object = MatrixA.Value
Dim objectMatrixB(,) As Object = MatrixB.Value
Dim colA As Integer = objectMatrixA.GetLength(0)
Dim rowA As Integer = objectMatrixA.GetLength(1)
Dim colB As Integer = objectMatrixB.GetLength(0)
Dim rowB As Integer = objectMatrixB.GetLength(1)
Dim localMatrixA(colA - 1, rowA - 1) As Double
Dim localMatrixB(colB - 1, rowB - 1) As Double
Dim Result(,) As Double
Dim i, j As Integer
For i = 1 To colA
For j = 1 To rowA
LocalMatrixA(i - 1, j - 1) = objectMatrixA(i, j)
Next
Next
For i = 1 To colB
For j = 1 To rowB
localMatrixB(i - 1, j - 1) = objectMatrixB(i, j)
Next
Next
Result = Imsl.Math.Matrix.Multiply(localMatrixA, localMatrixB)
Dim outputRange As Excel.Range
outputRange = Me.Range(Application.ActiveCell, _
Application.ActiveCell.Offset(colA - 1, rowB - 1))
outputRange.Value = Result
End Sub
There are many differences in this code segment compared to the previous examples, revealing the myriad ways one can interact with the cells on an Excel spreadsheet at this level. To begin, the values of the MatrixA and MatrixB named ranges are retrieved with calls to their Value property and stored in Object(,) arrays. The localMatrix_ arrays are declared as Doubles with sizes determined from their counterparts. Note the many offsets of -1 that arise to account for the differences in handling array indices. Once the loops fill the Double arrays, the Matrix.Multiply method is called, storing the answer in Result. We define a new Range, outputRange, with a size defined by the number of columns of A by the number of rows of B (per matrix multiplication rules). Output to the active cell in the sheet is accomplished by setting the Value property of this range to the Result.
Screenshots of an example are shown below. The input range MatrixA is shaded orange; MatrixB is yellow. Note that even though the second matrix and the output range are one-dimensional, the generalized code using two-dimensional arrays works without issue. The active cell is highlighted in the first image and filled in after clicking the MatMul button:
This example works for input matrices of any size. Further, the input and output of a two-dimensional data set has many applications beyond just matrix operations. There are numerous functions in the IMSL Library that rely on such data.
While Microsoft Excel offers a variety of charting functionalities along with an easy-to-use interface for creating charts, the end results are sometimes poorly applied for quantitative applications. In these cases, a straightforward graph created programmatically may provide a better solution. The IMSL C# Numerical Library contains charting components with an object-oriented programming interface and these charts can be added to a worksheet using the same programming model as the previous examples.
One adds .NET user interface components like the button controls to an Excel worksheet by dragging them from the toolbox window and dropping them on the sheet. To work with the IMSL C# Library charting components in this manner, a component must first be added to the Visual Studio Toolbox. Follow these steps to add the PanelChart component to the Toolbox:
- Right-click on the Toolbox and select Choose Items…
- Under the .NET Framework Components tab click Browse…
- Locate the imslcs.dll assembly.
Alternatively, you can drag the DLL and drop it onto the Toolbox. To add a Chart to the workbook, locate the PanelChart object on the Toolbox (it may be in the Common Controls section) and select it by single-clicking. Note that you must have the project in Design View. Then move the cursor over to the worksheet area and drag to define the boundaries of the chart. It will appear as a blank white rectangular region.
Programmatically, the properties of the created PanelChart object are accessed in the code behind the Excel spreadsheet in exactly the same way as in a traditional .NET Windows Forms desktop application. For this VB.NET example, consider plotting a simple one-dimensional array like the one we used for the SignTest example. The following code segment plots an array x as a colored line.
Dim chart As Imsl.Chart2D.Chart
Dim axis As Imsl.Chart2D.AxisXY
Dim data As Imsl.Chart2D.Data
chart = Imsl_Chart2D_PanelChart1.Chart
axis = New Imsl.Chart2D.AxisXY(chart)
data = New Imsl.Chart2D.Data(axis, x)
data.LineColor = Color.BlueViolet
Imsl_Chart2D_PanelChart1.Refresh()
The first few lines of the code define the objects in the chart hierarchy: a Chart holds Axis objects, which hold Data objects. The chart reference is obtained from the embedded PanelChart object’s Chart property. The axis and data objects are then defined and the x variable from the SignTest example appears here. The Line Color property is set as an example and then the Refresh method is called to redraw the PanelChart inside the spreadsheet. A screenshot of the end result is shown below:
This is a simple example, but any of the charting features of the IMSL C# Numerical Libraries would be similarly accessible. These features include bar, pie, high-low-close, contour, heatma and dendrogram charts, as well as others.
Deployment of VSTO applications was a challenge for many developers in the .NET Framework version 1.1, but has been simplified in the 2005 update. There is detailed documentation available online, but we can summarize what is required to deploy the project created in this paper to a separate computer from the development machine.
There are a number of prerequisites for the target deployment computer, including the .NET Framework 2.0 and the Visual Studio Tools for Office runtime. For a link to these downloads, please refer to the link in the previous paragraph. An installed copy Microsoft Excel 2003 or newer is required, along with the ExcelWorkbookIMSL.xls spreadsheet, ExcelWorkbookIMSL.dll assembly and IMSL C# Library imslcs.dll assembly with its accompanying license management DLL.
Once these components (found in the bin\Debug or bin\Release directories) are installed on a client computer and the VSTO runtime is installed, the document and assembly must be granted “full trust.” This can be done by granting trust to individual assemblies or to the entire folder. There are several ways to configure permissions, but the most direct approach is using the Microsoft .NET Framework 2.0 Configuration graphical user interface. This tool is accessed under Administrative Tools in the Control Panel. See Appendix A of this document for a copy of the steps involved or refer to the instructions on MSDN.
Please note that while this tool was included with the .NET 1.1 runtime redistributable package, it is included only with the .NET 2.0 SDK. For developers, this makes sense, but many end users will not require the SDK installation. The suggested method to configure security policies in the absence of the Configuration GUI tool is with the Code Access Security Policy tool (caspol). While not as easy to use as the graphical version, wide-scale deployments will typically be handled by IT staff that will be versed in the use of Microsoft’s command line tools.
Once the appropriate folder has been granted full trust with one of these methods, a user on the local machine is able to start Excel by opening the ExcelWorkbookIMSL.xls spreadsheet. Clicking on any of the buttons (with the active cell positioned appropriately) should result in expected execution. In the case where security settings have not been properly configured, the user receives an error message such as the following:
While this error may also indicate that the assembly has not been copied properly, security settings are often to blame. The details shown are not much help to the average end user, unfortunately, so it is important to thoroughly test deployment of VSTO solutions before wide-scale distribution.
To help your packaging of a VSTO application, Microsoft also offers the ability to use the “Publish Wizard.” Start the wizard by selecting the project in the Solution Explorer, right-clicking and then choosing Publish… from the menu. For this example, we can deploy to the local disk by filling in the URL as shown below:
You will find that this has created an ExcelWorkbookIMSL.application file and placed a copy of the ExcelWorkbookIMSL.xls file in the specified directory. There is also a subfolder named ExcelWorkbookIMSL_1.0.0.0 that contains the assemblies, the spreadsheet and the manifest files. While this wizard helps to extract the required components and create manifests, you will still need to grant the appropriate permissions on each computer.
There are many other options for deploying your Office solutions that are documented by Microsoft. The various models are beyond the scope of this document, but for your reference the following links direct you to some of them:
Similar resources are available should you choose the Application-level Add-in model for developing your Office solution incorporating the IMSL C# Numerical Library.
We have shown how you can develop and deploy an Excel-based application that calls the IMSL C# Numerical Library through the Visual Studio Tools for Office interface. Once all of the required components are installed, interactions are as easy as adding code behind the spreadsheet using any .NET language and calling the IMSL C# assembly like any other reference. Converting spreadsheet cell areas to arrays and back to cell areas is accomplished using Named Ranges. Charting components can be added as if they were any other .NET component. Finally, deployment requires an understanding of security settings.
End-user applications can be distributed following the steps outlined on the Microsoft Developers Network (MSDN). For additional information, consult any of the various hyperlinks in this document, many of which lead to the MSDN web pages. Help is also available for your IMSL-based applications from Visual Numerics’ Expert Consulting Services should you require additional expertise or resources.
The following steps are copied verbatim from MSDN for your reference in case that link is not accessible or the page has moved.
To grant full trust to an assembly or folder on your local computer
-
In Control Panel, open Administrative Tools.
-
Run Microsoft .NET Framework 2.0 Configuration.
Note
There might be several similar tools with names that start with Microsoft .NET Framework. Make sure that the configuration tool you use matches your version of the runtime.
-
In the treeview on the left side, expand .NET Framework 2.0 Configuration, expand My Computer, expand Runtime Security Policy, expand User, expand Code Groups, expand All_Code and then expand VSTOProjects.
Note
If you have not compiled a Visual Studio Tools for Office project before, you will not have the VSTOProjects folder. You can add the new code group to the All_Code root node, or you can compile a Visual Studio Tools for Office project to have the VSTOProjects folder created automatically.
-
On the right is the VSTOProjects Code Group description, which has a Tasks section at the bottom of the page. In the Tasks section, click Add a Child Code Group.
The Create Code Group wizard starts.
-
Select Create a new code group and enter a name and description that will help you identify the project. Click Next.
-
In the Choose the condition type for this code group list, click URL.
-
In the URL box, type the full path to the assembly or the path to the bin folder of the project followed by an asterisk (for example, c:\<path>\ExcelApplication1.dll or c:\<path>\ExcelApplication1\bin\*).
-
Click Next.
Note
If you type the path to the bin folder, all assemblies in that folder and all its subfolders are granted full trust on your computer. Make sure that no unauthorized people have access to folders that are fully trusted, or someone could put a malicious assembly in the folder and the assembly would run with full trust.
Caution
Do not grant permissions to an entire hard disk (such as C:\*) or to general folders such as My Documents, because you might grant permissions to cached assemblies from the Internet or from e-mail messages. Only grant permissions to specific project folders that contain assemblies you know are safe to execute.
-
Select Use existing permission set and then select FullTrust from the list.
-
Click Next.
-
Click Finish.
Using the Code Access Security Policy Tool (Caspol.exe)
You can also grant full trust to a folder from a command prompt by using the Code Access Security Policy tool (Caspol.exe). For more information about Caspol.exe, see Code Access Security Policy Tool (Caspol.exe).
You can grant trust to a folder on your local computer at the User level with normal user permissions. To grant trust to a network location, you must have administrator privileges and change the security policy at the Machine level. The Machine policy level acts independently of the User policy level, and the machine policy level does not grant full trust to the Intranet zone even if the User policy does. The policy levels must agree.
Tip
Type the commands manually. Copying and pasting the commands into the command prompt might result in Unknown Option errors.
To grant trust to a local folder
Type the following command in the Visual Studio Command Prompt:
caspol -u -ag All_Code –url C:\<FolderName>\<FolderName>\* FullTrust -n "<Name>" -d "<Description>"
For more information, see How to: Add Code Groups Using Caspol.exe.
Learn More
The IMSL® C# Library saves development time. For more information, or to request a free evaluation click here! You may also RESERVE YOUR 20% SAVINGS and buy later!
