Build Office-Based Solutions Using WPF, WCF, And LINQ

Andrew Whitechapel has written an article in the December 2007 number of MSDN Magazine (Build Office-Based Solutions Using WPF, WCF, And LINQ) 

The article is interesting in many ways:

• The case in the article use Visual Studio 2008 and .NET Framework 3.5, i e the latest version of the .NET platform.  
• It shows how to implement the latest new technologies (WPF, WCF and LINQ) in VSTO solutions for Office.
• It also shows the complexity involved to create such solutions as mentioned in the article.
• I find the article to be well written and I hope Andrew will be able to write more articles. 

If You don’t know what WPF, WCF and LINQ stands for then the following sources can hopefully shed some light:

• Windows Presentation Foundation (WPF)
• Windows Communication Foundation (WCF)
• Language Integrated Query (LINQ)

However, I cannot disregard from the gap between what VS 2008 offer and what Excel as a development platform offer. For me it’s quite obvious that MSFT must upgrade Excel’s development environment in the next coming version. After all, Excel as a development platform is still a too important platform to be neglected. 

Kind regards,
Dennis

Access running instances of Excel in VB

When automating Excel from VB we can either create new instances or access already running instances. In different scenarios different methods may be needed in order to get workable solutions.

In the first scenario we may need to access a running instance and if not we need to create a new one as the following sample shows:

This is a rather simple method but it does the work and we can handle the situation if any error is raised.

In the second case we use a slightly different approach as the below code shows. The System.Diagnostic.Process class represents a Windows process. The sample uses the GetProcessesByName property to check if any Excel session is running or not. This approach is less ‘brutal’ then the above and offer also more options as it use the System.Diagnostic.Process (see also the other samples below):
 

In the third scenario another approach is used which is based on BindToMoniker. Unlike the above methods it works ‘botttom up’ (in the Excel’s object model) as it gets an interface pointer identified by the specified moniker, here the workbook file. The method provides the same functionality as the GetObject method in VB 6.    

In some scenarios we may want to start a new Excel session, allow the users to work with one or more workbooks and finally end the process. The following sample shows how we can achieve it:
 

Remark:
I have not tested how it works when using the Dual Core Processor and instantiate new Windows processes.

The final sample shows how we can get information for all running Excel instances with the System.Diagnostic.Process class:

I have made the class module available as a textfile which can viewed or saved from here. 

Kind regards,
Dennis

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. 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):

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

Office Open XML – OOXML

One of the items on my To-do list is to learn more about RegExpression. Another item is Office Open XML (OOXML). Whenever I decide to teach me more about a specific topic I usually start to “google” to locate relevant online resources. If the subject is very interesting I also try to locate books on the subject as I still like to read printed books.

The file format XML has been in focus for the last couple of years. The main reason is due to its platforms independence. Of course, whenever a “new kid is in town” it turns quickly to be a buzzword and XML is no exception.

In order to understand and to work with Office Open XML it requires that we got the basic knowledge of XML.

Open XML has been widely discussed for the last couple of months. Mainly because of MSFT’s intention to make it to an ISO standard file format. Except for that, it’s the XML file format to work with MS Office’s files (with and without MS Office suite) which also explain why it’s named as Office Open XML or for short OOXML. 

In the end of 2005 I read the book “Office 2003 XML” from O’Reilly which helped me to get a kick start with what Excel 2003 can do. Important to know is that it’s not the same file format which Excel 2007 use but Excel 2007 can read the older XML file format.

Most Office users view the Office suite as tool set to get the work done. Since this is the primarily task with Office it’s difficult to disagree on that. But when discussing the OOXML file format we need to view Office as general-purpose interfaces to information.

OOXML files can be created without Excel and other platforms like Visual Studio.NET, SQL Server, SharePoint Server et al as well as other non-MSFT platforms & tools can read and update OOXML files. In this context the real power of the OOXML file format is exposed and the OOXML files become more of ‘containers’ for information then just Excel’s files.

Before moving on with the online resources I would like to highlight Stephane Rodriquez excellent work. He is ‘the man’ when it comes to Excel’s file formats and I’m very impressed by his strong commitment to the field. 

Check out the following excellent work by Stephane:

• OOXML is defective by design (article)
• Office 2007 .bin file format (article)
• xlsgen – Home of Stephane’s commercial tool ‘xlsgen’ and it also contains all (?) information about Excel’s object model COM interface.

At present I have found the following online resources for OOXML to be of high interest:

• Wikipedia – A general good starting point and easy introduction.
• OpenXMLDeveloper.org – The home of OOXML
• Open XML Explained – A PDF file of the book that gives a basic introduction to OOXML 
• Brian Jones: Open XML Formats  – The SpreadsheetML section

I will  get back on the subject next year but right now I just want to learn more about it. 

Does anyone else have information about additional useful online resources and perhaps about other books about OOXML? 

Kind regards,
Dennis

.NET Co Library – Part V

To read previously posts about the .NET Co Library please see the following entries:

• Part I
• Part II
• Part III
• Part IV

Based on input from some testers I decided to implement a dedicated Windows form to only insert code into modules with. However, all other ideas and suggestions may only be considered to be implemented in future updates of .NET Co Library.

Insert code
In one of the previously blogpost (.NET Co Library – Part II) the main form for managing code (delete, update and insert code) was presented. Now I have added a new option to insert code in present code modules and also to add code to new added code modules (class and general modules).

The following image shows the new added options in the menus:

Based on which main group that is selected the Windows form is populated with the related subgroups. The following image shows the form when “Insert Snippet Code” has been selected:

Since I have had (and still have) issues with making Windows forms modeless in the VB Editor I have solved it by setting its property TopMost to True (Actually, the problem is that Excel crash when closing it due to the modeless status). The drawback with this solution is that the Windows Forms always stay at the front even if Excel is not the active program.

It always seems to be a conflict between design of the UI and the wanted functions. In my experience there is also necessary to make a compromise in order to get a workable solution. In this context I have made it possible to expand each row so the whole code for an entry can be viewed by clicking on the row in the Code column. But in order to restore the row heights I was forced to add a button (Reset Row height) which reset all rows height. The following image shows expanded code rows:

Another “issue” to decide on was if only single entries or multiply entries can be selected. I decided to go with the later as the following image shows:

The final aspect I considered was to how to solve if there is a larger group of entries how could we locate the wanted code in an easy way. I solved it by using a built-in Filter functions in the grid. It use the extra row below the fieldnames as the following image tries to show:

Finally, the Export button export all code to a RTF file. At present I’m pleased with it but I may in an upcoming update make it possible to export selected code to a RTF file.

That’s it! I will now set focus to write the complete helpfile for this utility.

Kind regards,
Dennis