Open Excel workbooks in VB.NET Solutions

In some forums I frequent (more precisely lurking around) the question “How can we open Excel workbooks in .NET solutions?” is raised from time to time. Usually it does not refer to how to read data from closed Excel workbooks rather how to open and work with workbooks in VB.NET and in a similar manner as we can do in Excel.

The possible approaches, at least for me, to answer the question are the following:

• Using the .NET WebBrowser Control
• Using the Office Framer Control
• Using the Spreadsheet Component in Office Web Components (OWC)
• Using FarPoint Spread for Windows Forms

Which approach to use is due to how much access the users should have to Excel’s built-in features and how much to work with the data. In some cases as little as possible while in other scenarios as much as possible. To say which approach is the best to use is subject to the actual situation and what the components actually can do.

.NET WebBrowser Control
Using the the .NET WebBrowser Control is similar but not identical to open Excel workbooks through Internet Explorer. It requires that Excel is available in order to open the workbooks.

The following case is a simple example. The below image shows the output:

The following code is used to achieve the above:

The major drawback with this component is that we don’t have access to the Worksheet menu bar and that we need to write code to access functions in the other commandbars. The right click menu in the worksheet space is available when using this control. 

To create a similar solution with classic VB please see (please note that it does not work with Excel 2007):
How to use the WebBrowser control in Visual Basic to open an Office document  

Office Framer Control (aka DsoFramer Control)
MSFT has made available an ActiveX control for hosting Office documents. The KB Article Visual C++ ActiveX Control for hosting Office documents in Visual Basic or HTML provides us with additional information about it and a link to download it.  This control also requires that Excel is available in order to open workbooks.

The package that is available for download includes samples for classic VB, VB.NET and the Web. Since the source files are included we can change the code and compile our own version(s) of it.

Instead of creating my own sample I here use the included VB.NET sample. The following screenshot shows how Excel 2007 looks like (I prefer to have the Ribbon minimized and also to have the formula bar in hidden mode):

Compared with .NET WebControl the Worksheet menu bar is available and it can also handle several open Excel workbooks. The right click menu in the worksheet space is also available with this control. 

To enable Registration-Free COM for the Office Framer Control within the Visual Studio .NET 2005 project, simply right-click the unmanaged project reference, select Properties, and then on the properties panel for the reference, set “Isolated” to “True”. This will tell Visual Studio to generate a COM registration manifest for the application with the information necessary to activate and launch the unmanaged control. 

Spreadsheet Component
This component does not requires that Excel is available. On the other hand we cannot require that it will be a full substitute for Excel.

It offer some basic functions and therefore the component is a good candidate to only show data from Excel workbooks or when the users may need to change a limited amount of data. One native good tool is the AutoFilter.

The case I have set up here is very simple. The following image shows how we can manually set the data source (here a workbook with 2003 XML Spreadsheet file format is used) for a worksheet in design mode: 

The next image shows how it looks when we are using the spreadsheet component. It also shows that we can edit functions in run mode: 

For an introduction to the component with classic VB please see:
Introduction to the OWC Spreadsheet Control
Use The OWC Spreadsheet in MS VB 6.0
The Spreadsheet Control as datasource to the Chart Control

FairPoint Spread for Windows Forms
This is the ultimated .NET spreadsheet component. FarPoint Spread does not require having Excel available but can open and work with native Excel workbooks. 

In my opinion its simple the best component for spreadsheeting outside Excel and other dedicated spreadsheet tools (which is also reflected in the price list). For more information please see FarPoint Spread where you also can download a trial version of it. 

To sum up
- It exist two types of controls that we can use. Those controls that requires Excel to be installed and controls without this requirement.
- Among the two controls that require Excel to be installed the Office Frame Control is the best option.
- Among the two other controls the FarPoint Spread is the best control. But since it’s a very expensive the Spreadsheet Component may therefore be more attractive (free).

Finally, it’s the actual business needs that stipulate which control to use and not.

Kind regards,
Dennis

---

Possibly related posts: (automatically generated)

• The bridge over trouble water
• Export data from DataGridView to Excel