How to Read Excel file and Import data from Excel to SQL Server in C#

First off, you should create a new Windows Forms Application, then drag and drop Label, TextBox, Button, Combobox and DataGridView controls from your Visual Studio Toolbox to your winform.

Next, You can design a simple UI Winform that allows you to select the excel file, then read data from excel sheet to DataGridView and Import data from excel file to SQL Server in c# as shown below.

Right click on your project, then select Manage Nuget Packages.

Install the following NuGet packages:

• ExcelDataReader (for reading Excel files)
• ExcelDataReader.DataSet (for reading Excel files into a DataSet)
• Z.Dapper (for ORM operations with SQL Server)

You can also install these packages via NuGet Package Manager Console:

Install-Package ExcelDataReader
Install-Package ExcelDataReader.DataSet
Install-Package Z.Dapper

ExcelDataReader

Lightweight and fast library written in C# for reading Microsoft Excel files (2.0-2007). Using ExcelDataReader you can read excel file in c# without oledb.

ExcelDataReader.DataSet

ExcelDataReader extension for reading Microsoft Excel files into System.Data.DataSet.

Z.Dapper.Plus

It's a high efficient Bulk Actions (Insert, Update, Delete, and Merge) for .NET Support: SQL Server, SQL Azure, SQL Compact, MySQL, SQLite and more..

To play the demo, you should create a new database, then run the sql script below to create the Customer table.

CREATE TABLE [dbo].[Customers](
	[CustomerID] [nchar](5) NOT NULL,
	[CompanyName] [nvarchar](40) NOT NULL,
	[ContactName] [nvarchar](30) NULL,
	[ContactTitle] [nvarchar](30) NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[Phone] [nvarchar](24) NULL,
	[Fax] [nvarchar](24) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

You should create a Customer class with fields corresponding to the columns in the sql table. I'm using the Customers table exported to the excel file from the Northwind database. Customer class helps you map data between customer tables and customer objects.

public class Customer
{
    public string CustomerID { get; set; }
    public string CompanyName { get; set; }
    public string ContactTitle { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
    public string Phone { get; set; }
    public string Fax { get; set; }
    public string PostalCode { get; set; }
    public string Region { get; set; }
}

Next, Open your form code behind, then declare tables variable with the data type as shown below. This variable helps you store all data in excel. Each corresponding Sheet is a DataTable.

DataTableCollection tables;

How to Read Excel file in C# Windows Application?

Add the click event handler to the Browse button allows you to select an excel file.

// c# read excel file
private void btnBrowse_Click(object sender, EventArgs e)
{
    using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx" })
    {
        if (ofd.ShowDialog() == DialogResult.OK)
        {
            txtPath.Text = ofd.FileName;
            using (var stream = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read))
            {
                using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
                {
                    DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
                    {
                        ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                        {
                            UseHeaderRow = true
                        }
                    });
                    tables = result.Tables;
                    cboSheet.Items.Clear();
                    foreach (DataTable table in tables)
                        cboSheet.Items.Add(table.TableName);
                }
            }
        }
    }
}

How do you read data of an Excel file using C#?

We will read all sheets in excel file, then read data from excel sheet using c#. You can add sheet name to the Combobox. The resulting variable is a DataSet containing all the Sheets read from the excel file. Remember to clear all items of the Combobox before adding new item.

I'm using the ExcelDataReader  library to read excel file (xls/xlsx). This is the best way to read excel file in c#.

In other words, Import excel to datatable in c# windows application or c# read excel file into datatable without oledb.

By default, ExcelDataReader supports c# read excel file into datatable. So, you should convert from DataTable to List if you want to use List.

Next, Add the SelectedIndexChanged event handler to the Combobox that allows you to select a worksheet, then read data from excel sheet to the DataGridView as the following c# code.

private void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
{
    DataTable dt = tables[cboSheet.SelectedItem.ToString()];
    if (dt != null)
    {
        List<Customer> list = new List<Customer>();
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            Customer obj = new Customer();
            obj.CustomerID = dt.Rows[i]["CustomerID"].ToString();
            obj.CompanyName = dt.Rows[i]["CompanyName"].ToString();
            obj.ContactTitle = dt.Rows[i]["ContactTitle"].ToString();
            obj.City = dt.Rows[i]["City"].ToString();
            obj.Address = dt.Rows[i]["Address"].ToString();
            obj.Country = dt.Rows[i]["Country"].ToString();
            obj.Fax = dt.Rows[i]["Fax"].ToString();
            obj.Phone = dt.Rows[i]["Phone"].ToString();
            obj.PostalCode = dt.Rows[i]["PostalCode"].ToString();
            obj.Region = dt.Rows[i]["Region"].ToString();
            list.Add(obj);
        }
        customerBindingSource.DataSource = list;
    }
}

You need to add a BindingSource to the DataGridView. Next, Read data from excel sheet using c#, then map the data from the DataTable to the list customer object.

Finally, Initialize the data source for DataGridView as the following c# code. As you see, you can easily import excel to DataGridView in C#. Using ExcelDataReader is the best /fastest way to read an Excel Sheet into a DataTable.

How to Import data from Excel to SQL Server using C#?

To import data from excel file to sql server database, you can use the BulkInsert method of Dapper Plus library.

// c# import data into sql server
public void Insert(List<Customer> list)
{
    DapperPlusManager.Entity<Customer>().Table("Customers");
    using (IDbConnection db = new SqlConnection("Server=.;Database=dbtest;User Id=sa;Password=123@qaz;"))
    {
        db.BulkInsert(list);
    }
}

Using BulkInsert method is the best way to import large amounts of data into sql server. BulkInsert only execute the command once, so the execution speed will be many times faster than inserting each record into the database.

Import excel to Sql Server C# Winform

Adding a click event handler to the Import button allows you to import excel to sql server c# winform as shown below.

// c# import excel to sql server database
private void btnImport_Click(object sender, EventArgs e)
{
    try
    {
        Insert(customerBindingSource.DataSource as List<Customer>);
        MessageBox.Show("Finished !");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Through this post, I hope you can find a solution on how to read excel file in c# windows application and import data from excel to sql server using c# windows application.

You can do the same as above to import data from excel to sql server using asp.net mvc or import data from excel to sql server using entity framework.

VIDEO TUTORIAL