Creating an Excel Spreadsheet and Adding Data to It Programmatically

INTRODUCTION:

The Interoperability services make it veryeasy to work with COM Capable Applications such as Word and Excel. Thisarticle reveals using Excel from a managed application. Excel is thespreadsheet component of Microsoft Office 2000. The majority of Excelprogrammatic functionality is exposed through Automation via the typelibrary Excel9.olb. The intention of this article is to express that amanaged 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 anExcel Spreadsheet? & Set values to the cell using C#. The codes forCreating, make visible, add a new workbook and to set a value for cellin 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 cangenerate .NET assembly from Type library files and we can use thatfunctionality of Type library file in C#.

Most Commented Articles :

• C# Tutorial For Beginners
• C# Operator Overloading
• Objects & Classes in C#
• C# Editable ListView
• C# – Static Members
• Drag and Drop Files with C#
• Using the Parallel Class in C# – Processing Data in Parallel across Multiple Processors or Cores
• Talk to SharePoint Through its Web Services
• ComboBox With Images
• GDI+ A Higher Level API