By ArunGG
INTRODUCTION:
The Interoperability services make it very easy to work with COM Capable Applications such as Word and Excel. This article reveals using Excel from a managed application. Excel is the spreadsheet component of Microsoft Office 2000. The majority of Excel programmatic functionality is exposed through Automation via the type library Excel9.olb. The intention of this article is to express that a managed application can interrelate with Excel as a COM server.
The first step is to create a reference in our project to Excel 9.0 Objects Library.
By using Tlbimp tool we can generate Excel.dll.
TlbImp Excel9.olb Excel.dll
By adding Excel.dll to our program we can use the functionality of the Excel.
Now let us see in detail how to create an Excel Spreadsheet? & Set values to the cell using C#. The codes for Creating, make visible, add a new workbook and to set a value for cell in the Excel file is shown below.
1. CREATING NEW EXCEL.APPLICATION:
Application exc = new Application();
if (exc == null) {
Console.WriteLine("ERROR: EXCEL couldn't be started");
return 0;
}
2. TO MAKE APPLICATION VISIBLE:
exc.set_Visible(0, true);
3. TO GET THE WORKBOOKS COLLECTION:
Workbooks workbooks = exc.Workbooks;
3. TO ADD A NEW WORKBOOK:
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet, 0);
4. TO GET THE WORKSHEETS COLLECTION:
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
if (worksheet == null) {
Console.WriteLine ("ERROR in worksheet == null");
}
5. TO SET THE VALUE FOR CELL:
Range range1 = worksheet.get_Range("C1", Missing.Value);
if (range1 == null) {
Console.WriteLine ("ERROR: range == null");
}
const int nCells = 1;
Object[] args1 = new Object[1];
args1[0] = nCells;
range1.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, range1, args1);
The Example:
using System;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel;
class Excel {
public static int Main() {
Application exc = new Application();
if (exc == null) {
Console.WriteLine("ERROR: EXCEL couldn't be started!");
return 0;
}
exc.set_Visible(0, true);
Workbooks workbooks = exc.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet, 0);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
if (worksheet == null) {
Console.WriteLine ("ERROR: worksheet == null");
}
Range range1 = worksheet.get_Range("C1", Missing.Value);
if (range1 == null) {
Console.WriteLine ("ERROR: range == null");
}
const int nCells = 1;
Object[] args1 = new Object[1];
args1[0] = nCells;
range1.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,range1, args1);
return 100;
}
}
Now let us observe how to send a single dimension array to Excel:
It is similar to set the value for the cell. Only change is we use array as args2[0] = array2.
const int nCell = 5;
Range range2 = worksheet.get_Range("A1", "E1");
int[] array2 = new int [nCell];
for (int i=0; i < array2.GetLength(0); i++) {
array2[i] = i+1;
}
Object[] args2 = new Object[1];
args2[0] = array2;
range2.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, range2, args2);
The OutPut:
Conclusion:
With the help of TlbImp.exe tool we can generate .NET assembly from Type library files and we can use that functionality of Type library file in C#.
