Hi tripurarijha1...,
Check the below sample code.
For reading excel i have used ClosedXML. You can refer below article for more details.
Database
I have made use of the following table Customers with the schema as follows. CustomerId is an Auto-Increment (Identity) column.
The Excel File
HTML
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnupload" runat="server" Text="Upload" OnClick="OnUpload" />
Namespaces
C#
using System.Data;
using ClosedXML.Excel;
VB.Net
Imports System.Data
Imports ClosedXML.Excel
Code
C#
protected void OnUpload(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
if (FileUpload1.FileName.Contains(".xlsx"))
{
try
{
DataTable dt = new DataTable();
using (XLWorkbook workBook = new XLWorkbook(FileUpload1.PostedFile.InputStream))
{
IXLWorksheet workSheet = workBook.Worksheet(1);
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++;
}
}
}
}
foreach (DataRow dr in dt.Rows)
{
SqlWebMethod method = new SqlWebMethod();
string command = "INSERT INTO Customers (Name,Country) VALUES ('" + dr["Name"].ToString() + "','" + dr["Country"].ToString() + "')";
method.executesql(command);
}
}
catch (Exception)
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "Alert", "<script>alert('Showing some error. Please Upload again.');</script>", false);
}
}
else
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "Alert", "<script>alert('File extension is incorrect');</script>", false);
}
}
else
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "Alert", "<script>alert('Please Select File');</script>", false);
}
}
VB.Net
Protected Sub OnUpload(ByVal sender As Object, ByVal e As EventArgs)
If FileUpload1.HasFile Then
If FileUpload1.FileName.Contains(".xlsx") Then
Try
Dim dt As DataTable = New DataTable()
Using workBook As XLWorkbook = New XLWorkbook(FileUpload1.PostedFile.InputStream)
Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
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
Next
End Using
For Each dr As DataRow In dt.Rows
Dim method As SqlWebMethod = New SqlWebMethod()
Dim command As String = "INSERT INTO Customers (Name,Country) VALUES ('" & dr("Name").ToString() & "','" + dr("Country").ToString() & "')"
method.executesql(command)
Next
Catch ex As Exception
ScriptManager.RegisterStartupScript(Me, GetType(Page), "Alert", "<script>alert('Showing some error. Please Upload again.');</script>", False)
End Try
Else
ScriptManager.RegisterStartupScript(Me, GetType(Page), "Alert", "<script>alert('File extension is incorrect');</script>", False)
End If
Else
ScriptManager.RegisterStartupScript(Me, GetType(Page), "Alert", "<script>alert('Please Select File');</script>", False)
End If
End Sub
SqlWebMethod
C#
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
/// <summary>
/// Summary description for SqlWebMethod
/// </summary>
public class SqlWebMethod
{
// run simple command
[WebMethod]
public void executesql(string cmd)
{
using (SqlConnection con = new SqlConnection())
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
con.ConnectionString = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand execmd = new SqlCommand(cmd, con);
try
{
execmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally { execmd.Dispose(); con.Close(); }
}
}
[WebMethod]
//return ds value
public DataSet getds(string cmd)
{
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection())
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
con.ConnectionString = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter adp = new SqlDataAdapter(cmd, con);
adp.SelectCommand.ExecuteNonQuery();
adp.Fill(ds);
con.Close();
return ds;
}
}
[WebMethod]
// return bool to validate data value
public bool valdatedata(string cmd)
{
//SqlDataReader dr;
using (SqlConnection con = new SqlConnection())
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
con.ConnectionString = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand execmd = new SqlCommand(cmd, con);
dr = execmd.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.Read())
{
execmd.Dispose();
dr.Close();
return true;
}
else
{
execmd.Dispose();
dr.Close();
return false;
}
}
}
public SqlDataReader dr;
[WebMethod]
public IDataReader getdr(string cmd)
{
SqlConnection con = new SqlConnection();
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
con.ConnectionString = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand execmd = new SqlCommand(cmd, con);
dr = execmd.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.Read())
{
return dr;
}
else
{
return null;
}
}
}
public string getsinglevallue(string cmd)
{
string value;
SqlConnection con = new SqlConnection();
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
con.ConnectionString = ConfigurationManager.ConnectionStrings["cs"].ConnectionString;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand execmd = new SqlCommand(cmd, con);
dr = execmd.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.Read())
{
value = dr[0].ToString();
}
else
{
value = "";
}
}
if (con.State == ConnectionState.Open)
{
con.Close();
}
dr.Close();
return value;
}
}
VB.Net
Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services
Public Class SqlWebMethod
<WebMethod>
Public Sub executesql(ByVal cmd As String)
Using con As SqlConnection = New SqlConnection()
If con.State = ConnectionState.Open Then
con.Close()
End If
con.ConnectionString = ConfigurationManager.ConnectionStrings("cs").ConnectionString
If con.State = ConnectionState.Closed Then
con.Open()
End If
Dim execmd As SqlCommand = New SqlCommand(cmd, con)
Try
execmd.ExecuteNonQuery()
Catch ex As Exception
Finally
execmd.Dispose()
con.Close()
End Try
End Using
End Sub
<WebMethod>
Public Function getds(ByVal cmd As String) As DataSet
Dim ds As DataSet = New DataSet()
Using con As SqlConnection = New SqlConnection()
If con.State = ConnectionState.Open Then
con.Close()
End If
con.ConnectionString = ConfigurationManager.ConnectionStrings("cs").ConnectionString
If con.State = ConnectionState.Closed Then
con.Open()
End If
Dim adp As SqlDataAdapter = New SqlDataAdapter(cmd, con)
adp.SelectCommand.ExecuteNonQuery()
adp.Fill(ds)
con.Close()
Return ds
End Using
End Function
<WebMethod>
Public Function valdatedata(ByVal cmd As String) As Boolean
Using con As SqlConnection = New SqlConnection()
If con.State = ConnectionState.Open Then
con.Close()
End If
con.ConnectionString = ConfigurationManager.ConnectionStrings("cs").ConnectionString
If con.State = ConnectionState.Closed Then
con.Open()
End If
Dim execmd As SqlCommand = New SqlCommand(cmd, con)
dr = execmd.ExecuteReader(CommandBehavior.CloseConnection)
If dr.Read() Then
execmd.Dispose()
dr.Close()
Return True
Else
execmd.Dispose()
dr.Close()
Return False
End If
End Using
End Function
Public dr As SqlDataReader
<WebMethod>
Public Function getdr(ByVal cmd As String) As IDataReader
Dim con As SqlConnection = New SqlConnection()
If True Then
If con.State = ConnectionState.Open Then
con.Close()
End If
con.ConnectionString = ConfigurationManager.ConnectionStrings("cs").ConnectionString
If con.State = ConnectionState.Closed Then
con.Open()
End If
Dim execmd As SqlCommand = New SqlCommand(cmd, con)
dr = execmd.ExecuteReader(CommandBehavior.CloseConnection)
If dr.Read() Then
Return dr
Else
Return Nothing
End If
End If
End Function
Public Function getsinglevallue(ByVal cmd As String) As String
Dim value As String
Dim con As SqlConnection = New SqlConnection()
If True Then
If con.State = ConnectionState.Open Then
con.Close()
End If
con.ConnectionString = ConfigurationManager.ConnectionStrings("cs").ConnectionString
If con.State = ConnectionState.Closed Then
con.Open()
End If
Dim execmd As SqlCommand = New SqlCommand(cmd, con)
dr = execmd.ExecuteReader(CommandBehavior.CloseConnection)
If dr.Read() Then
value = dr(0).ToString()
Else
value = ""
End If
End If
If con.State = ConnectionState.Open Then
con.Close()
End If
dr.Close()
Return value
End Function
End Class
Screenshot
Records after insert
