Import Excel file to Windows Form DataGridView and save into database using C# and VB.Net

Hii Appache,

Please refer below code.

Database

I have made use of the following table Customers with the schema as follows. 

You can download the database table SQL by clicking the download link below.

Download SQL file

The Excel File

The Form

Namespaces

C#

using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using ClosedXML.Excel;

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Data
Imports ClosedXML.Excel

Code

C#

private void OnOpen(object sender, EventArgs e)
{
    openFileDialog1.ShowDialog();
}

DataTable dt = null;
private void OnFileOk(object sende, CancelEventArgs e)
{
    string filepath = openFileDialog1.FileName;
    using (XLWorkbook workbook = new XLWorkbook(filepath))
    {
        IXLWorksheet worksheet = workbook.Worksheet(1);
        dt = new DataTable();
        bool firstRow = true;
        foreach (IXLRow row in worksheet.Rows())
        {
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    i++;
                }
            }
            dataGridView1.DataSource = dt;
        }
    }
}

private void OnInsert(object sender, EventArgs e)
{           
    foreach (DataRow row in dt.Rows)
    {
        string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constring))
        {
            using (SqlCommand cmd = new SqlCommand("INSERT INTO Customers VALUES(@Name, @Country)", con))
            {
                cmd.Parameters.AddWithValue("@Name", row["Name"].ToString());
                cmd.Parameters.AddWithValue("@Country", row["Country"].ToString());
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}
}

VB.Net

Private Sub OnOpen(ByVal sender As Object, ByVal e As EventArgs)
    OpenFileDialog1.ShowDialog()
End Sub

Private dt As DataTable = Nothing
Private Sub OnFileOk(ByVal sende As Object, ByVal e As CancelEventArgs)
    Dim filepath As String = OpenFileDialog1.FileName
    Using workbook As XLWorkbook = New XLWorkbook(filepath)
        Dim worksheet As IXLWorksheet = workbook.Worksheet(1)
        dt = New DataTable()
        Dim firstRow As Boolean = True
        For Each row As IXLRow In worksheet.Rows()
            If firstRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                Next
                firstRow = False
            Else
                dt.Rows.Add()
                Dim i As Integer = 0

                For Each cell As IXLCell In row.Cells()
                    dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                    i += 1
                Next
            End If
            DataGridView1.DataSource = dt
        Next
    End Using
End Sub

Private Sub OnInsert(ByVal sender As Object, ByVal e As EventArgs)
    For Each row As DataRow In dt.Rows
        Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(constring)
            Using cmd As SqlCommand = New SqlCommand("INSERT INTO Customers VALUES(@Name, @Country)", con)
                cmd.Parameters.AddWithValue("@Name", row("Name").ToString())
                cmd.Parameters.AddWithValue("@Country", row("Country").ToString())
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    Next
End Sub

Screenshots

The DataGridView

Inserted records