In
this article I will explain how to Import data from Excel sheet with multiple worksheets
to multiple table.
In this article I will Import data from Two worksheets to Two Table in SQL database.
1.
Create a excel sheet with two worksheets, as given in example.
2.
Now create two same tables in SQL database.
3.
Keep
in mind, Column name must be same in SQL Table & Excel Sheets.
4.
Now write given code on button click event to import data from
excel sheet to database tables.
Excel Sheet
Example:
SQL Tables:
Employee Table Dept Table
ASP.NET code
to Import Data from Multiple Excel Sheets to Multiple Tables:
Add
Following Namespaces:
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using
System.Data.SqlClient;
Write code on Button Click:
protected void Button1_Click(object sender, EventArgs e)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
string excelCon = "";
string strFile = "C:\\Users\\Aman
Sharma\\Desktop\\Test.xlsx";
if (strFile.Trim().EndsWith(".xlsx"))
{
excelCon = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data
Source={0};Extended Properties=\"Excel 12.0
Xml;HDR=YES;IMEX=1\";", strFile);
}
else if (strFile.Trim().EndsWith(".xls"))
{
excelCon = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);
}
objConn = new OleDbConnection(excelCon);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
//ifthere is empty
sheet i.e. Sheet3 other dont decrease lenth by one.
for (int j = 0; j <
excelSheets.Length-1; j++)
{
OleDbCommand cmd = new OleDbCommand("Select * from [" + excelSheets [j]+ "]",objConn);
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(con);
if (j == 0)
{
sqlBulk.DestinationTableName = "Employee";
}
else if (j == 1)
{
sqlBulk.DestinationTableName = "Dept";
}
con.Open();
sqlBulk.WriteToServer(dReader);
con.Close();
}
objConn.Close();
}
