In
this tutorial I am going to explain how to import the MS Excel sheet records
into Sql server database in asp.net
Description:
In
the previous article I have explained change the text color in Image dynamically
and Different ways to disable auto-fill in browser for a textbox.
I
have a MS excel file which have records/details of employees. I want to import
all records from excel file into database table.
Implementation:
I
have a table in Tb_Employee
HTML Markup:
<table>
<tr><td>Upload Excel:</td><td><asp:FileUpload ID="FileUpload1" runat="server" /></td></tr>
<tr><td></td><td><asp:Button ID="btnupload" runat="server" Text="Upload"/></td></tr>
</table>ssss
Add
namespace
C#:
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Data;
VB:
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO
Imports System.Data
C#:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());
VB:
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())
Validate
the extension
C#:
protected bool IsValidExtension()
{
if (FileUpload1.HasFile)
{
string
FileExt = Path.GetExtension(FileUpload1.FileName);
FileExt = FileExt.ToLower();
if (FileExt != ".xls" && FileExt != ".xlsx")
{
Response.Write("<script>alert('Upload
Excel file only');</script>");
return false;
}
}
return true;
}
VB:
Protected Function
IsValidExtension() As Boolean
If FileUpload1.HasFile Then
Dim FileExt As String = Path.GetExtension(FileUpload1.FileName)
FileExt = FileExt.ToLower()
If FileExt <> ".xls" AndAlso FileExt <> ".xlsx" Then
Response.Write("<script>alert('Upload
Excel file only');</script>")
Return False
End If
End If
Return True
End Function
Upload the
Excel file
On
upload button click write the below given code:
C#:
protected void btnupload_Click(object sender, EventArgs e)
{
try
{
if
(IsValidExtension())
{
string filepath = Server.MapPath("~/upload/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(filepath);
OleDbConnection excelcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
" + "Data Source=" + filepath + ";Extended
Properties=Excel 12.0;");
excelcon.Open();
DataTable
ds = new DataTable();
OleDbDataAdapter oda = new OleDbDataAdapter("SELECT *FROM
[sheet1$]", excelcon);
oda.Fill(ds);
excelcon.Close();
con.Open();
SqlBulkCopy bulkcopy = new SqlBulkCopy(con);
bulkcopy.DestinationTableName =
"Tb_Employee";
bulkcopy.ColumnMappings.Add("Name", "Name");
bulkcopy.ColumnMappings.Add("Phone", "Phone");
bulkcopy.ColumnMappings.Add("Salary", "Salary");
bulkcopy.WriteToServer(ds);
con.Close();
}
}
catch(Exception ex)
{ }
}
VB:
Protected Sub
btnupload_Click(sender As Object, e As EventArgs) Handles btnupload.Click
Try
If IsValidExtension() Then
Dim filepath As String = Server.MapPath("~/upload/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
FileUpload1.SaveAs(filepath)
Dim excelcon As New OleDbConnection((Convert.ToString("Provider=Microsoft.ACE.OLEDB.12.0;
" + "Data Source=") & filepath) + ";Extended
Properties=Excel 12.0;")
excelcon.Open()
Dim ds As New DataTable()
Dim oda As New OleDbDataAdapter("SELECT *FROM
[sheet1$]", excelcon)
oda.Fill(ds)
excelcon.Close()
con.Open()
Dim bulkcopy As New SqlBulkCopy(con)
bulkcopy.DestinationTableName =
"Tb_Employee"
bulkcopy.ColumnMappings.Add("Name", "Name")
bulkcopy.ColumnMappings.Add("Phone", "Phone")
bulkcopy.ColumnMappings.Add("Salary", "Salary")
bulkcopy.WriteToServer(ds)
con.Close()
End If
Catch ex As Exception
End Try
End Sub
Build
and run the application. Test it by uploading the excel file.
Result:
In this article we have learn to how to import the MS Excel sheet records into Sql server database in asp.net using C# and VB.net. I hope you enjoyed this article.
What do you think about this article?
If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.
EmoticonEmoticon