VSTO & .NET & Excel

November 16, 2007

Open Excel workbooks in VB.NET Solutions

Filed under: .NET & Excel, COM Add-ins — Dennis M Wallentin @ 9:16 pm

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:

net-webcontrol.png

The following code is used to achieve the above:

netwebcontrols-code.png

 

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. Unfortunately it’s no longer available at their support site. But you can download it from Microsoft Developer Support Office Framer Control Sample (KB 311765) 1.3. 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):

dsoframer.png

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: 

import-source.png

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: 

spreadsheetaction.png

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

About these ads

20 Comments »

  1. Dennis,

    Good start at a comparison of the options. FarPoint Spread for Windows Forms (and FarPoint Spread for Web Forms) is made by FarPoint Technologies, Inc. You have the web address correct but the spelling of the product is FarPoint Spread not FairPoint Spread. Thanks for considering us. While the component isn’t free, you can deploy the Excel import/export capability royalty free so it is a very affordable option. If you’d like screen shots to post, just let us know.

    –Bill Albing
    BillA@FarPointSpread.com
    Marketing Communications
    FarPoint Technologies, Inc.

    Comment by Bill Albing — November 17, 2007 @ 5:52 pm

  2. Bill,

    First of all, my sincerely apologize for not spelling the corporate name correctly. I have now updated the name in the blogpost.

    For me, as an independed “micro” company (myself only) it’s a lot of money, which is reflected in the blogpost.

    Actually, on my to-do list I have this product as it’s an attractive and interesting product.

    I’ve scheduled a blogpost that will cover it or at least make a presentation of it. Thanks for Your offer to provide me with screenshots.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — November 17, 2007 @ 6:43 pm

  3. Hello Dennis,

    You may want to take a look at SpreadsheetGear for .NET as well. We offer a 30 day fully functional free trial. Many of our customers are former FarPoint Spread or Formula One users. We’ll leave it up to you to discover why they have switched.

    Please call or email if you have any questions.

    Kind Regards,
    Joe Erickson
    Founder and CEO
    SpreadsheetGear LLC
    http://www.SpreadsheetGear.com
    (888) 774-3273 (toll free from the United States)
    (913) 390-4797

    Comment by Joe Erickson — November 19, 2007 @ 5:54 pm

  4. Hi Joe,

    Thanks for the heads up :) Honestly, I was not aware of Your product so it’s good to know it exist. I will download and test SpreadsheetGear.

    It looks like I will make a write up about both FarPoint Spread and SpreadsheetGear. At present I’m thinking of a series where they are compared in different areas.

    I find it interesting to see that this blogpost is read by vendors of strong competitive spreadsheets components.

    Please let me know if it exist some additional vendors of spreadsheets components.

    Two important aspects are:
    I’m not associated with any vendor so I’m 101 % neutral.

    I welcome a friendly and constructive discussions about spreadsheets components.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — November 19, 2007 @ 6:52 pm

  5. hello,

    I was wondering: what about chart support in FarPoint Spread / OWC Spreadsheet component ?

    Comment by Fabien — January 9, 2008 @ 12:25 pm

  6. Hi Fabien,

    The OWC Spreadsheet does not have any charting capability while the FarPoint Spread only has a minimum rudimentary cptions.

    The OWC package is shipped with a chart component which can be used.

    There also exist dedicated packages for charting like Dundas Charting and Chart FX.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — January 9, 2008 @ 1:03 pm

  7. Hi Dennis,

    I’m kind of a rookie in coding and developing a VB.Net application which needs to read data from excel files (without opening them) and exporting results to them. After reading some of your posts, I tried to use this example but problems with the COMs emerged. Even with the propper references and ‘Imports’ statements, when running the program crashed on the ‘.Add’ method, calling an ‘HRESULT’ error.
    Do you have an idea about what I might be doing wrong here ?

    Kind Regards,
    Nuno

    Comment by nuno — February 7, 2008 @ 7:42 pm

  8. Hi Nuno,

    Difficult to say without seeing the code in use.

    #1 Read data from closed workbooks
    The following article (written by me) may get You started:
    Populate Textboxes with ADO.NET from a closed workbook
    http://www.excelkb.com/article.aspx?id=10055&cNode=7T5V0N

    #2 Write data to closed workbooks.
    Let’s solve the #1 issue and then we can consider this issue.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — February 7, 2008 @ 10:26 pm

  9. Hi,

    If you want to read/write XLS/CSV/HTML/XLSX files from your .NET applications, you can use our .NET component (http://www.gemboxsoftware.com/GBSpreadsheet.htm). You can even use free version of our component (http://www.gemboxsoftware.com/GBSpreadsheetFree.htm) to make commercial software. Free version has 150 rows limit.

    Mario

    Comment by Mario — April 28, 2008 @ 3:44 pm

  10. Hi Mario,

    Thanks for the information and I will look into it.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — April 29, 2008 @ 11:16 am

  11. Hi,

    Isn’t there some more solutions for this problem??
    I tested Farpoint Spread for Web Forms. But, it doesn’t handle
    external cell reference which is a big minus for my company.

    I have also heard about Actuate E-spreadsheet, but it is based on Java.

    I haven’t found anything else.

    Comment by Ctan — July 10, 2008 @ 6:03 pm

  12. I’ve been looking fir a solution to this problem for some time and this article really laid out some options for me. Let me explain and maybe I can get some suggestions. I have an app in VB6 that has an OLE container which links to an existing Excel file. I then display several different charts from this excel file. Each chart being on their own tab in the Excel workbook. Now the user does not interact with the charts directly, they make selections in the GUI and with VB code I fill in the datarows on a worksheet that are linked to the charts. I also modify slightly parts of the chart like labels and hide and unhide certain lines. So i do this all though the Excel objects available in VB6. Whew! This has worked great up to Excel 2003. With the rollout of Excel 2007 the program still works but the charts are displayed really crappy. The lines and fonts are displayed in a very think way and sideways fonts are displayed horribly. Sometimes the charts are not displayed at all. So from doing some research this is a problem with the new object model not being fully compatible with OLE. I think i will have to move my app to VB.NET and I think that may be the road to take. My interaction with the Excel sheet is quite extensive so I’m looking for a component that i can drop in my app and follow the same coded logic through the Office object but just use a different component to display the changes. What are my options? Any suggestions are appreciated.

    Thanks
    AGP

    Comment by AGP — August 31, 2008 @ 9:14 am

  13. One other comment…when i load up the Excel file using your example it always wants to open it up as a regular file in Excel rather than in the browser. Is that because of the IE settings? Is there a way to force this to always open within the browser?

    AGP

    Comment by AGP — August 31, 2008 @ 10:30 am

  14. Hi Dennis,
    had to try this in anger today, in VB 2008 project. Found that the web browser methods does not work – it open the document, but as a new instance of the office app, not inside the eb browser. Also tried the active x control, and that was very poor, as it did not display the document corectly (very slow as well, but i guess thats par for the course).
    Might try one of those thrid party componets, but what a shame that with a MS flagship product there is no support and good way to embedd office documents – amazing really!

    Cheers
    Ross

    Comment by Ross — September 18, 2008 @ 12:43 pm

  15. Ross,

    Thanks for making Your comment here. I have not had the time to test it myself so it’s very good that You had did.

    In my own experience VS2008 does not always act as VS2005.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — September 18, 2008 @ 3:10 pm

  16. Hi Dennis,
    I’ve just tested it in VS2005, and it works fine! Strange that it shouldn’t work in 08! Not sure whats going on, will do a bit more digging as time permits.

    Comment by ross — September 19, 2008 @ 9:34 am

  17. Dennis,
    I tried and tested the options outlined here and most didn’t work or were costly. At least that’s how it applied in my case with my company. In the end I took someones suggestion and did something that was so simple that i didn’t even consider it at first. The method consists of writing some code that exports the charts to a an image, say a bitmap. this is readily available with Office automation. See this article that is old but still works http://support.microsoft.com/kb/163103. I then put an image or picture control on my form and load the bitmap from the hard disk into the image control. Some testing is required to get the right resolution and you are also left with bitmaps on the hard disk which your app will have to clean up. It works reasonably well for me although it takes a couple more seconds to render. The next step would be to copy the chart to memory instead of the hard disk and then assign the picture in memory to an image control. Any comments welcome on this method.

    AGP

    Comment by AGP — October 18, 2008 @ 3:55 pm

  18. AGO,

    Creating image files for charts is a well known approach and is also considered as robust.

    I cannot see how that correspond to the blogpost’s subject.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 27, 2008 @ 12:51 pm

  19. great! Anyone used spire.xls ? I feel it other easy way to read and write excel. maybe best excel component to .net. too powerful !!
    http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html

    Comment by harrypeter86 — July 8, 2010 @ 6:11 am

    • Hi,

      First time I hear about it. What’s Your experience with it?

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — July 8, 2010 @ 11:55 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 38 other followers

%d bloggers like this: