Creating an Excel Spreadsheet Programmatically using VB.NET

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 VB.NET. 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
   
   Dim exc As New Application ()
   If exc Is Nothing Then
   Console.WriteLine("ERROR: EXCEL couldn't be started")
   Return 0
   End If
    
2. TO MAKE APPLICATION VISIBLE:
   
   exc.set_Visible(0, true)
    
3. TO GET THE WORKBOOKS COLLECTION:
   
   Dim workbooks As Workbooks = exc.Workbooks
   Dim workbook As _Workbook = workbooks.Add
   XlWBATemplate.xlWBATWorksheet, 0)
    
4. TO GET THE WORKSHEETS COLLECTION
   
   Dim worksheet As _Worksheet = CType(sheets.get_Item(1), _Worksheet)
   If worksheet Is Nothing Then
   Console.WriteLine("ERROR in worksheet == null")
   End If
    
5. TO SET THE VALUE FOR CELL

Dim range1 As Range = worksheet.get_Range("C1", Missing.Value)
If range1 Is Nothing Then
Console.WriteLine("ERROR: range == null")
End If
Const nCells As Integer = 1
Dim args1(1) As [Object]
args1(0) = nCells
range1.GetType().InvokeMember("Value", BindingFlags.SetProperty, Nothing, range1, args1)

The Example

Imports System
Imports System.Reflection ' For Missing.Value and BindingFlags
Imports System.Runtime.InteropServices ' For COMException
Imports Excel
Class AutoExcel
Public Shared Function Main() As Integer
Dim exc As New Application
If exc Is Nothing Then
Console.WriteLine("ERROR: EXCEL couldn't be started!")
Return 0
End If
exc.set_Visible(0, True)
Dim workbooks As Workbooks = exc.Workbooks
Dim workbook As _Workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet, 0)
Dim sheets As Sheets = workbook.Worksheets
Dim worksheet As _Worksheet = CType(sheets.get_Item(1), _Worksheet)
If worksheet Is Nothing Then
Console.WriteLine("ERROR: worksheet == null")
End If
Dim range1 As Range = worksheet.get_Range("C1", Missing.Value)
If range1 Is Nothing Then
Console.WriteLine("ERROR: range == null")
End If
Const nCells As Integer = 1
Dim args1(1) As [Object]
args1(0) = nCells
range1.GetType().InvokeMember("Value", BindingFlags.SetProperty, Nothing, range1, args1)
Return 100
End Function 'Main
End Class 'AutoExcel

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 nCell As Integer = 5
Dim range2 As Range = worksheet.get_Range("A1", "E1")
Dim array2(nCell) As Integer
Dim i As Integer
For i = 0 To (array2.GetLength(0)) - 1
array2(i) = i + 1
Next i 
Dim args2(1) As [Object]
args2(0) = array2
range2.GetType().InvokeMember("Value", BindingFlags.SetProperty, Nothing, range2, args2)
End Function '__unknown

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 VB.NET.