.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> C# >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Working with Microsoft Office Interop Objects using C# 4.0

Posted By:Dhananjay Kumar       Posted Date: December 08, 2009    Points: 25    Category: C#    URL: http://www.dotnetspark.com  
 

Working with Microsoft Office Interop Objects using C# 4.0

This article will show, how Microsoft office InterOp objects can be access and use in C#. I will be using dynamic type feature of c# 4.0

  1. To create and open an Excel Application in VS2010
  2. To display list in Active Excel Application.
  3. To Create and open a word document in VS2010.
  4. To create an icon in word document that will link to selected part of Excel worksheet.

Step 1:

1.gif

Step 2:

Add reference of Microsoft.Office.Interop.Excel and Microsoft.Office.Interop.Word

2.gif

Step 3:

Add Name Spaces in Program
using Word = Microsoft.Office.Interop.Word;
using
Excel = Microsoft.Office.Interop.Excel; Step 4: Create a class called Author. This class is having two properties of dynamic type.

Author class

public class Authors
{
  public dynamic Name { get; set; }
  public dynamic NumberOfArticles { get; set; }

}


Step 5:

Create a static method to create and return list of authors.

static dynamic ListofAuthor()
{
  List<Authors> _listAuthor = new List<Authors>
  {
  new Authors(){Name =" Mahesh Chand", NumberOfArticles = 700 },
 new Authors () { Name = " Mike Gold", NumberOfArticles = 200},
 new Authors () {Name =" Patra " , NumberOfArticles = 50},
 new Authors () { Name = "Mamta M ", NumberOfArticles = 38},
 new Authors (){ Name = "ShivPrasad K", NumberOfArticles = 80},
 new Authors () { Name = "Praveen Masood", NumberOfArticles = 190},
 new Authors (){ Name =" Rekha S " , NumberOfArticles = 5}
  };
  return _listAuthor;

}
Step 6:

Displaying in Excel application

Create a static method to create and open Excel sheet. After opening the Excel Sheet, list of author will get inserted in the Excel sheet.

static void  DisplayAuthorsinExcel(dynamic authorlist)
  {
  dynamic _excelApplication = new Excel.Application();
  _excelApplication.Visible = true;
  _excelApplication.Workbooks.Add();
  Excel._Worksheet worksheet = _excelApplication.ActiveSheet;
  worksheet.Cells[1, "A"] = "Name";
  worksheet.Cells[1, "C"] = " Number of Articles";
  var row = 1;
  foreach (var r in authorlist)
  {
  row++;
  worksheet.Cells[row, "A"] = r.Name;
  worksheet.Cells[row, "C"] = r.NumberOfArticles;
  }
  worksheet.Columns[1].AutoFit();
  worksheet.Columns[3].AutoFit();
  }

  }
Explanation

  1. Type of input parameter is dynamic.
  2. Instance of an Excel application is getting created and assigned to the reference of type dynamic.
  3. Then one work book is been added to the instance of Excel application.
  4. Active work sheet is been assigned to work sheet.
  5. We are enumerating to the entire list and adding the Authors in the Excel sheet to display.

3.gif

Step 7:

Press F5 to debug and run the application.

4.gif

So, successfully we are able to display Authors list in Excel application using C#4.0 features.

Step 8:

Create a word document with Icon Link to selected column of Excel work sheet.

  1. Add this line of code at the bottom on DisplayAuthorsinExcelApplication() static method. Below code will copy name of all the authors.

    worksheet.get_Range("A1:A8").Copy();
     

  2. Create a static method which will create and open a word document. This document will have an icon. This icon will link to selected portion of active Excel application.

    static void CreateWordDocument()
    {
     var  _wordApplication = new Word.Application();
     _wordApplication.Visible = true;
     _wordApplication.Documents.Add();
     _wordApplication.Selection.PasteSpecial(Link: true, DisplayAsIcon: true);

    }

Step 9:

Press F5 to run the application. First one Excel sheet will open with the selected values then a word document will open. This document will have an icon that will link to selected part of active excel application.

5.gif

6.gif

7.gif

8.gif


For Reference the complete code is as below,

Program.cs

using System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Text;
using
Word = Microsoft.Office.Interop.Word;
using
Excel = Microsoft.Office.Interop.Excel; namespace OfficeInterOpaccess
{
  class Program
  {
  static void Main(string[] args)
  {
  dynamic d = ListofAuthor();
  foreach ( dynamic k in d)
  {
  Console.WriteLine(k.Name);
  }
  DisplayAuthorsinExcel(d);
   CreateWordDocument();
     Console.Read();
  }
  static dynamic ListofAuthor()
  {
  List<Authors> _listAuthor = new List<Authors>
  {
  new Authors(){Name =" Mahesh Chand", NumberOfArticles = 700 },
  new Authors () { Name = " Mike Gold", NumberOfArticles = 200},
  new Authors () {Name =" Patra " , NumberOfArticles = 50},
  new Authors () { Name = "Mamta M ", NumberOfArticles = 38},
  new Authors (){ Name = "ShivPrasad K", NumberOfArticles = 80},
  new Authors () { Name = "Praveen Masood", NumberOfArticles = 190},
  new Authors (){ Name =" Rekha S " , NumberOfArticles = 5}
  };
  return _listAuthor;
  }
   static void  DisplayAuthorsinExcel(dynamic authorlist)
  {
  dynamic _excelApplication = new Excel.Application();
  _excelApplication.Visible = true;
  _excelApplication.Workbooks.Add();
  Excel._Worksheet worksheet = _excelApplication.ActiveSheet;
  worksheet.Cells[1, "A"] = "Name";
  worksheet.Cells[1, "C"] = " Number of Articles";
  var row = 1;
  foreach (var r in authorlist)
  {
   row++;
  worksheet.Cells[row, "A"] = r.Name;
  worksheet.Cells[row, "C"] = r.NumberOfArticles;
  }
  worksheet.Columns[1].AutoFit();
  worksheet.Columns[3].AutoFit();
  worksheet.get_Range("A1:A8").Copy();
  }
  static void CreateWordDocument()
  {
  var  _wordApplication = new Word.Application();
  _wordApplication.Visible = true;
  _wordApplication.Documents.Add();
  _wordApplication.Selection.PasteSpecial(Link: true, DisplayAsIcon: true);
  }
  } 
  public class Authors
  {
  public dynamic Name { get; set; }
  public dynamic NumberOfArticles { get; set; }
  }
}
Conclusion:

In this article, we saw; how to work with office interop objects in C# 4.0. We created and opened an Excel application then displayed list of authors in the active worksheet of active Excel application. Then we opened and created a Word document, which were having an icon to link to selected area of Excel sheet. We used dynamic type for all the purpose. Download attached Source for better understanding.

Thanks for reading. Happy Coding.


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend