Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

August 14, 2009

Using Linq with Microsoft Word and Excel

Some of the collections in the Microsoft Office object models implement IEnumerable. The IEnumerable interface provides the ability to perform a for/each against the collection. With .NET 3.5, a Cast extension method of IEnumerable allows you to work with these collections using Linq.

Microsoft Word

For example, say you want to bind the set of open Word document names in a ComboBox.

First, set a reference to the desired version of the Microsoft Word Object Library from the COM tab of the Add Reference dialog. The resulting reference appears as Microsoft.Office.Interop.Word.

In C#:

// Add to the top of the code file
using Word = Microsoft.Office.Interop.Word;

// Add to a subroutine
Word.Application Wd;
Word.Document doc;
Word.Document doc2;
object missingValue = Missing.Value;

// Start Word and get Application object
Wd = new Word.Application();

// Define documents
doc = Wd.Documents.Add(ref missingValue,ref missingValue,
ref missingValue,ref missingValue );
doc2 = Wd.Documents.Add(ref missingValue, ref missingValue,
ref missingValue, ref missingValue);

// Use Linq to access the document names.
var query = from d in Wd.Documents.Cast<Word.Document>()
select d.Name;
comboBox1.DataSource = query.ToList();

// Or use Lambda expressions
var query2 = Wd.Documents.Cast<Word.Document>().Select(d=> d.Name);
comboBox1.DataSource = query2.ToList();

// Close
doc.Close(ref missingValue, ref missingValue, ref missingValue);
doc = null;
doc2.Close(ref missingValue, ref missingValue, ref missingValue);
doc2 = null;
Wd.Quit(ref missingValue, ref missingValue, ref missingValue);

// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect() ;

In VB:

‘ Add to the top of the code file
Imports Word = Microsoft.Office.Interop.Word

‘ Add to a subroutine
Dim Wd As Word.Application
Dim doc As Word.Document
Dim doc2 As Word.Document

‘ Start Word and get Application object
Wd = New Word.Application

‘ Define documents
doc = Wd.Documents.Add
doc2 = Wd.Documents.Add

‘ Use Linq to access the document names.
Dim query = From d In Wd.Documents.Cast(Of Word.Document)() _
Select d.Name
ComboBox1.DataSource = query.ToList

‘ Or use Lambda expressions
Dim query2 = Wd.Documents.Cast(Of Word.Document) _
.Select(Function(d) d.Name)
ComboBox1.DataSource = query2.ToList

‘ Close
doc.Close()
doc = Nothing
doc2.Close()
doc2 = Nothing
Wd.Quit()

‘ Clean up
‘ NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()

In both of these examples, the code starts Word, creates two Word documents, uses either Linq or a Lambda expression to define a query and then binds the resulting set of document names to a Combo Box.

Notice the missingValue variable in the C# code that is not in the VB code. VB supports default parameters, but C# does not. So any time a parameter is defined for a Word method, C# must provide it. VB will use the default parameter values.

NOTE: A new feature in C# 4.0 (Visual Studio 2010) allows for default parameters in C# as well, dramatically simplifying the C# code that interacts with Word or Excel.

As another example, the following code retrieves all of the words from the defined Word document.

In C#:

var query = from w in doc.Words.Cast<Word.Range>()
select w.Text;
comboBox1.DataSource = query.ToList();

In VB:

Dim query = From w In doc.Words.Cast(Of Word.Range)() _
Select w.Text
ComboBox1.DataSource = query3.ToList

This code retrieves all of the words in the document defined by the doc variable. Instead of selecting the list of words, you could use any Linq feature such as finding only a specific set of words that match a criteria or counting the number of occurrences of a given word.

Microsoft Excel

This technique works with Excel as well. Say you want to bind the list of spreadsheets in an Excel workbook.

First, set a reference to the desired version of the Microsoft Excel Object Library from the COM tab of the Add Reference dialog. The resulting reference appears as Microsoft.Office.Interop.Excel.

In C#:

// Add to the top of the code file
using Excel = Microsoft.Office.Interop.Excel;

// Add to a subroutine
Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;

// Start Excel and get Application object.
oXL = new Excel.Application();

// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);

// Get the active sheet and change its name
oSheet = (Excel.Worksheet)oWB.ActiveSheet ;
oSheet.Name = “Test”;

// Use Linq to access the spreadsheet names.
var query = from s in oXL.Worksheets.Cast<Excel.Worksheet>()
select s.Name;
comboBox1.DataSource = query.ToList();

// Or use Lambda expressions.
var query2 = oXL.Worksheets.Cast<Excel.Worksheet>()
.select(s => s.Name);
comboBox1.DataSource = query2.ToList();

// Close
oSheet = null;
oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
oXL.Quit();

// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();

In VB:

‘ Add to the top of the code file
Imports Excel = Microsoft.Office.Interop.Excel

‘ Add to a subroutine
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet

‘ Start Excel and get Application object.
oXL = New Excel.Application

‘ Get a new workbook.
oWB = oXL.Workbooks.Add

‘ Get the active sheet and change its name
oSheet = DirectCast(oWB.ActiveSheet, Excel.Worksheet)
oSheet.Name = “Test”

‘ Use Linq to access the spreadsheet names.
Dim query = From s In oXL.Worksheets.Cast(Of Excel.Worksheet)() _
Select s.Name
ComboBox1.DataSource = query.ToList

‘ Or use Lambda expressions
Dim query2 = oXL.Worksheets.Cast(Of Excel.Worksheet) _
.Select(Function(s) s.Name)
ComboBox1.DataSource = query2.ToList

‘ Close
oSheet = Nothing
oWB.Close()
oWB = Nothing
oXL.Quit()

‘ Clean up
‘ NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()

In both examples, the code starts Excel, changes the name of the active sheet, uses either Linq or a Lambda expression to define a query and then binds the resulting set of sheet names to a Combo Box.

Enjoy!

EDITED 11/16/09: Added information on setting the appropriate reference.

9 Comments

  1.   Greg Duncan — August 15, 2009 @ 10:38 am    Reply

    Nice! Thanks for posting this.

    I think you’ve just saved me hours of future hair-pulling-out… 🙂

  2.   Daniel Earwicker — August 17, 2009 @ 5:45 am    Reply

    Don’t overlook the OfType method as an alternative to Cast. The Cast method will throw if the conversion is not allowed, whereas OfType will simply filter non-matching objects out of the sequence. So the OfType method is a bit like the ‘as’ keyword.

  3.   DeborahK — August 17, 2009 @ 10:17 am    Reply

    Thanks for your suggestion, Daniel.

  4.   Robert Bravery — August 18, 2009 @ 4:05 am    Reply

    Fantastic Post. I’m assuming you’ve tested with office 2007.

  5.   DeborahK — August 18, 2009 @ 10:07 pm    Reply

    Hi Robert –

    Yes, I tested with Office 2007.

    Thanks for visiting my blog!

  6.   David Sutherland — September 14, 2009 @ 5:26 pm    Reply

    I just wanted to thank you for this post too. I was just starting to do Linq to Word for a project and this posting came riding to the rescue.

    Cheers;

    Dave

  7.   Darin — June 16, 2010 @ 2:05 pm    Reply

    Yep, it’s me
    Great post. I’d run into this casting problem a couple times and hadn’t seen a way around it.

    This makes linq a whole lot more helpful with Word and Excel.

    I’ve already found it incredibly handy for OpenXML work.

    Good stuff!

  8.   Jorja — August 13, 2011 @ 12:36 am    Reply

    Life is short, and this article saved vaualble time on this Earth.

  9.   Randi — August 14, 2011 @ 9:26 am    Reply

    Super informative wrintig; keep it up.

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2020 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs