Creating an Excel Spreadsheet Programmatically

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;
   _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);

Example

using System;
using System.Reflection; // For Missing.Value and BindingFlags
using System.Runtime.InteropServices; // For COMException
using Excel;
class AutoExcel
{
    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);

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#.