Objective

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


Step 2

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


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.

Step 7

Press F5 to debug and run the application.


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.





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 code for better understanding. Thanks for reading.

Advertisements

One thought on “Accessing Microsoft Office InterOP Objects using C# 4.0

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