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
- To create and open an Excel Application in VS2010
- To display list in Active Excel Application.
- To Create and open a word document in VS2010.
- 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
-
Type of input parameter is dynamic.
-
Instance of an Excel application is getting created and assigned to the reference of type dynamic.
-
Then one work book is been added to the instance of Excel application.
-
Active work sheet is been assigned to work sheet.
-
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.
-
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();
-
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. Happy Coding.