How to create Excel file in VB.Net

By: Rajesh P.S.

Automation to Excel empowers users to execute a variety of tasks, including the creation of new workbooks, data population, and chart generation. By utilizing COM interop, developers can use the power of Excel programmatically in VB.Net.

COM interop

The code example demonstrates the use of COM interop to establish a connection with Excel and automate the process of creating a new workbook. This involves using the appropriate classes, properties, and methods provided by the Excel object model to create and manipulate the workbook.

Excel interop assemblies

It's recommended to ensure that the necessary references and imports are included in the VB.Net project to access the Excel interop assemblies. This allows for seamless integration with Excel and access to its functionalities.

By following the code example and having Excel installed on your system, you can programmatically create Excel files, customize their content, and use the full capabilities of Excel within your VB.Net application.

Excel Library

In order to access the object model from Visual VB.NET, you have to add the Microsoft Excel 12.0 Object Library to your current project.

Create a new project and add a Command Button to your VB.Net Form.

How to use COM Interop to Create an Excel Spreadsheet

Form the following images you can find how to add Excel reference library in your VB.Net project.

Select reference dialogue from Project menu.

Select Microsoft Excel 12.0 Object Library and click OK button

How to create an Excel Document Programmatically

First we have to initialize the Excel application Object in your VB.Net application.

Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()

Before creating new Excel Workbook, you should check whether Excel is installed in your system.

If xlApp Is Nothing Then MessageBox.Show("Excel is not properly installed!!") Return End If

Then create new Workbook

Dim xlWorkBook As Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue)

After creating the new Workbook, next step is to write content to worksheet

xlWorkSheet = xlWorkBook.Sheets("sheet1") xlWorkSheet.Cells(1, 1) = "Sheet 1 content"

In the above code we write the data in the Sheet1, If you want to write data in sheet 2 then you should code like this.

xlWorkSheet = xlWorkBook.Sheets("sheet2") xlWorkSheet.Cells(1, 1) = "Sheet 2 content"

Save Excel file (SaveAs() method)t

After write the content to the cell, next step is to save the excel file in your system.

xlWorkBook.SaveAs("d:\csharp-Excel.xls");

How to properly clean up Excel interop objects

How to create an Excel Document in VB.Net , How to properly release Excel COM objects VB.Net

Finally, we have to properly clean up Excel interop objects or release Excel COM objects. Here we write a function to clean up the Excel Application, Excel Workbook and Excel Worksheet Objects.

Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub

Creating an Excel Spreadsheet Programmatically

Copy and paste the following source code in your VB.Net project file.

Full Source VB.NET

Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application() If xlApp Is Nothing Then MessageBox.Show("Excel is not properly installed!!") Return End If Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = xlWorkBook.Sheets("sheet1") xlWorkSheet.Cells(1, 1) = "Sheet 1 content" xlWorkBook.SaveAs("d:\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, _ Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue) xlWorkBook.Close(True, misValue, misValue) xlApp.Quit() releaseObject(xlWorkSheet) releaseObject(xlWorkBook) releaseObject(xlApp) MessageBox.Show("Excel file created , you can find the file d:\csharp-Excel.xls") End Sub Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub End Class

Conclusion

Through automation and COM interop, developers can utilize the capabilities of Excel within VB.Net to create, manipulate, and enhance Excel files programmatically. However, it's essential to have Excel installed on the system and properly handle any potential exceptions for the code to run successfully.

Next > How to open or read an existing Excel file in VB.NET

Related Topics

More Related Topics.....