Windows Forms: Creating/Writing to an Excel file in VB.NET

Opening your Visual Studio, then create a new Windows Forms Application by selecting File=>New=>Project

Next, Select Visual Basic item, then select Windows Forms App (.NET Framework)

After you finish creating the project, you need to open your form designer, then drag Button and DataGridView controls from the Visual Studio toolbox into your form designer.

You can design a simple UI that allows you to display data in DataGridView, then export data from DataGridView to excel file in Visual Basic .NET

How to create an Excel file in VB.NET

This is a simple application help you create, write and save excel file in Windows Forms Application VB.NET using EPPlus library.

Right-clicking on your project, then select Manage Nuget Packages.

Selecting the Browse tab, then search and install EPPlus library to your project.

EPPlus is a third party library that helps you create and write data to excel file. It supports Non-Commercial and Commercial licenses

epplus license

To configure the license, you can open the App.config file, and then add a configuration license allowing you to use EPPlus as shown below.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <!--The license context used-->
    <add key="EPPlus:ExcelPackage.LicenseContext" value="NonCommercial" />
  </appSettings> 
</configuration>

Or you can also configure the license by writing code.

' If you are a commercial business and have purchased commercial licenses use the static property LicenseContext of the ExcelPackage class :
ExcelPackage.LicenseContext = LicenseContext.Commercial;

' If you use EPPlus in a noncommercial context
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

Next, Right-click on your project, then add an ADO.NET Entity Data Model to your project. We will use Entity Framework to retrieve data from Northwind database.

If you don't have the Northwind database you can view this post: How to download and restore Northwind database to SQL Server

Making a connection to the Northwind database, then select Suppliers table.

After you finish creating an ADO.NET Entity Data Model you will see the diagram as shown below.

Rebuilding your project, then click the arrow icon on the DataGridView and add a DataSource to your DataGridView control.

Opening your form designer, then double click on the form to add a Form_Load event handler allows you to retrieve supplier data from the Northwind database, then display data in DataGridView.

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Using db As NorthwindEntities = New NorthwindEntities()
        DataGridView.DataSource = db.Suppliers.ToList()
    End Using
End Sub

To use the entity framework to get data from the sql database, you should create a new instance of your model, then simply select the table you want to get data from database.

How to export data from datagridview to excel in vb.net

Finally, Add a click event handler to the Export button that allows you to export data from DataGridView to excel file in VB.NET

'how to Save excel file in vb.net
Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
    Using sfd As SaveFileDialog = New SaveFileDialog() With {.Filter = "Excel Workbook|*.xlsx"}
        Try
            If sfd.ShowDialog() = DialogResult.OK Then
               'epplus create excel file
                Dim fileInfo = New FileInfo(sfd.FileName)
                Using excelPackage As ExcelPackage = New ExcelPackage(fileInfo)
                    Dim workSheet As ExcelWorksheet = excelPackage.Workbook.Worksheets.Add("Suppliers")
                    workSheet.Cells.LoadFromCollection(Of Supplier)(TryCast(DataGridView.DataSource, List(Of Supplier)), True)
                    'epplus export to excel
                    excelPackage.Save()
                    MessageBox.Show("You have successfully exported your data to an excel file.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
                End Using
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Using
End Sub

You will need to use SaveFileDialog class to open a save file dialog, then use the ExcelPackage class to create an excel sheet.

Next, We will cast data from DataGridView to Supplier of List type and use the LoadFromCollection method to insert data to excel sheet. And don't forget to call the Save method to create and save data to excel file.

You can watch the video below to know how to create and write data to excel file in vb.net windows forms application

VIDEO TUTORIAL