Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

Archive for Microsoft Excel

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.
July 23, 2009

Writing Data from a DataTable to Excel

Filed under: C#,Data,Microsoft Excel,VB.NET @ 3:24 pm

As with most things in Visual Studio, there are many ways to export data from your .NET application to an Excel spreadsheet. This post covers one straightforward technique.

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.

Import the Microsoft.Office.Interop.Excel library in your code file. To keep the namespaces clear, consider using an alias.

In C#:

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

NOTE: C# also requires a reference to System.Reflection to use the Missing.Value field. This is required because C# 3.0 does not support optional parameters. Every parameter must have a value, so Missing.Value is used to provide a value for all unspecified optional parameters.

In VB:

Imports Excel = Microsoft.Office.Interop.Excel

The rest of the code is presented in VB and C# and then discussed below. It is provided in one big chunk to make it easier to copy/paste into your application.

In C#:

Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;
Excel.Range oRange;

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

// Set some properties
oXL.Visible = true;
oXL.DisplayAlerts = false;

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

// Get the active sheet
oSheet = (Excel.Worksheet)oWB.ActiveSheet ;
oSheet.Name = "Customers";

// Process the DataTable

// BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE

DataTable dt = Customers.RetrieveAsDataTable();

int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
    rowCount += 1;
    for (int i = 1; i < dt.Columns.Count+1; i++)
    {
        // Add the header the first time through
        if (rowCount==2)
        {
            oSheet.Cells[1, i] = dt.Columns[i – 1].ColumnName;
        }
        oSheet.Cells[rowCount, i] = dr[i – 1].ToString();
    }
}

// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
              oSheet.Cells[rowCount, dt.Columns.Count]);
oRange.EntireColumn.AutoFit();

// Save the sheet and close
oSheet = null;
oRange = null;
oWB.SaveAs("test.xls", Excel.XlFileFormat.xlWorkbookNormal,
    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
    Excel.XlSaveAsAccessMode.xlExclusive,
    Missing.Value, Missing.Value, Missing.Value,
    Missing.Value, Missing.Value);
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:

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range

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

‘ Set some properties
oXL.Visible = True
oXL.DisplayAlerts = False

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

‘ Get the active sheet
oSheet = DirectCast(oWB.ActiveSheet, Excel.Worksheet)
oSheet.Name = "Customers"

‘ Process the DataTable
BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE
Dim dt As Data.DataTable = Customers.RetrieveAsDataTable

‘ Create the data rows
Dim rowCount As Integer = 1
For Each dr As DataRow In dt.Rows
    rowCount += 1
    For i As Integer = 1 To dt.Columns.Count
        ‘ Add the header the first time through
        If rowCount = 2 Then
            oSheet.Cells(1, i) = dt.Columns(i – 1).ColumnName
        End If
        oSheet.Cells(rowCount, i) = dr.Item(i – 1).ToString
    Next
Next

‘ Resize the columns
oRange = oSheet.Range(oSheet.Cells(1, 1), _
          oSheet.Cells(rowCount, dt.Columns.Count))
oRange.EntireColumn.AutoFit()

‘ Save the sheet and close
oSheet = Nothing
oRange = Nothing
oWB.SaveAs("test.xls")
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()

NOTE: The VB code code above is set up to run with Option Strict ON.

The code begins by declaring the variables used in the code. Notice that in both C# and in VB this makes use of the namespace alias as shown at the very beginning of this post.

The code then starts Excel and makes it visible. If you want to create the spreadsheet without making Excel visible to the user, you could set the Visible to false instead.

A workbook is added, the active sheet of the workbook is referenced, and the sheet is given a name.

The DataTable is then processed. You can use any DataTable here. The code loops through the rows of the DataTable and for each row it loops through the columns. The first time through the columns it adds column headings.

To make the spreadsheet easier to read, the columns are then resized to show the contents of the cells. If you have lots of data in a cell, you may want to skip this step.

The workbook is then saved. Since no directory was specified, Excel will save the file to your My Documents folder. Notice the massive number of parameters in the C# code when calling the SaveAs (and Close) methods. This is required because C# does not have optional parameters. So the code must fill in each and every parameter in the call. VB does support optional parameters, so does not need to set the extra parameters.

NOTE: C# is getting optional parameters in .NET 4.0 (VS 2010).

Finally, the code is cleaned up. Since Excel is accessed through COM interop, the double garbage collection code is added to ensure Excel is correctly closed.  See this link for more information on why this specific garbage collection code is recommended.

Enjoy!

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