Here We Will Discuss About How to Count No Of Records In A Excel Sheet Using Asp.Net C#
Design Page:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td class="c1">
</td>
<td align="center" valign="top" bgcolor="#f8efeb">
<table border="0" cellspacing="0" cellpadding="1">
<tr>
<td align="left" class="s6" height="25" valign="middle">
File
</td>
<td align="center" class="s6" valign="middle">
:
</td>
<td align="left" valign="middle">
<asp:FileUpload ID="fleUpload" runat="server" />
</td>
</tr>
</table>
</td>
<td class="c2">
</td>
</tr>
<tr>
<td class="c1">
</td>
<td align="center" style="height: 10px" valign="top" bgcolor="#f8efeb">
</td>
<td class="c2">
</td>
</tr>
<tr>
<td class="c1">
</td>
<td align="center" valign="top" bgcolor="#f8efeb">
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<table border="0" cellspacing="0" cellpadding="1">
<tr>
<td class="save">
</td>
<td align="left">
<asp:Button ID="btnSAVE" Width="45px" runat="server" Text="Count" OnClick="btnSAVE_Click" />
</td>
</tr>
</table>
</td>
<td style="width: 10px;">
</td>
<td>
<table border="0" cellspacing="0" cellpadding="1">
<tr>
<td class="cancel">
</td>
<td align="left">
<asp:Button ID="btnCancel" runat="server" Width="48px" Text="Cancel" OnClick="btnCancel_Click" />
</td>
</tr>
</table>
</td>
</tr>
</table>
<br />
<asp:Label ID="lblMessage" runat="server" ForeColor="Red"></asp:Label>
</td>
<td>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Code Page:
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.OleDb;
using System.IO;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSAVE_Click(object sender, EventArgs e)
{
#region Upload Excel
try
{
string strDocument = fleUpload.PostedFile.FileName;
strDocument = DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Hour.ToString() + "_" + DateTime.Now.Minute.ToString() + "_" + DateTime.Now.Second.ToString() + strDocument.Substring(strDocument.LastIndexOf("\\") + 1);
string strAbsolutePathOfFile = Server.MapPath("Excel/" + strDocument);
fleUpload.PostedFile.SaveAs(strAbsolutePathOfFile);
string ext = Path.GetExtension(strDocument);
if (ext == ".xlsx")
{
#region Count No Of Records
string connectionString;
string count = "";
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strAbsolutePathOfFile + ";Mode=ReadWrite;Extended Properties=\"Excel 12.0;HDR=NO\"";
string SQL = "SELECT COUNT (*) FROM [Sheet1$]";
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = new OleDbCommand(SQL, conn))
{
using (OleDbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
count = reader[0].ToString();
lblMessage.Text = " There are " + count + " Records In The Excel Sheet";
}
}
conn.Close();
}
#endregion
}
else
{
lblMessage.Text = "Please Enter Xlsx File Only";
}
}
catch (Exception ex)
{
ClientScript.RegisterClientScriptBlock(Page.GetType(), "alert", "alert('" + ex.Message + ".');", true);
lblMessage.Text = "" + ex.Message + "";
return;
}
#endregion
}
After Selecting The File Using File Upload Control And After Clicking the Count Button . The Process Would Count No Of Records In The Uploaded Excel File
Design Page:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td class="c1">
</td>
<td align="center" valign="top" bgcolor="#f8efeb">
<table border="0" cellspacing="0" cellpadding="1">
<tr>
<td align="left" class="s6" height="25" valign="middle">
File
</td>
<td align="center" class="s6" valign="middle">
:
</td>
<td align="left" valign="middle">
<asp:FileUpload ID="fleUpload" runat="server" />
</td>
</tr>
</table>
</td>
<td class="c2">
</td>
</tr>
<tr>
<td class="c1">
</td>
<td align="center" style="height: 10px" valign="top" bgcolor="#f8efeb">
</td>
<td class="c2">
</td>
</tr>
<tr>
<td class="c1">
</td>
<td align="center" valign="top" bgcolor="#f8efeb">
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<table border="0" cellspacing="0" cellpadding="1">
<tr>
<td class="save">
</td>
<td align="left">
<asp:Button ID="btnSAVE" Width="45px" runat="server" Text="Count" OnClick="btnSAVE_Click" />
</td>
</tr>
</table>
</td>
<td style="width: 10px;">
</td>
<td>
<table border="0" cellspacing="0" cellpadding="1">
<tr>
<td class="cancel">
</td>
<td align="left">
<asp:Button ID="btnCancel" runat="server" Width="48px" Text="Cancel" OnClick="btnCancel_Click" />
</td>
</tr>
</table>
</td>
</tr>
</table>
<br />
<asp:Label ID="lblMessage" runat="server" ForeColor="Red"></asp:Label>
</td>
<td>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Code Page:
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.OleDb;
using System.IO;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSAVE_Click(object sender, EventArgs e)
{
#region Upload Excel
try
{
string strDocument = fleUpload.PostedFile.FileName;
strDocument = DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Hour.ToString() + "_" + DateTime.Now.Minute.ToString() + "_" + DateTime.Now.Second.ToString() + strDocument.Substring(strDocument.LastIndexOf("\\") + 1);
string strAbsolutePathOfFile = Server.MapPath("Excel/" + strDocument);
fleUpload.PostedFile.SaveAs(strAbsolutePathOfFile);
string ext = Path.GetExtension(strDocument);
if (ext == ".xlsx")
{
#region Count No Of Records
string connectionString;
string count = "";
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strAbsolutePathOfFile + ";Mode=ReadWrite;Extended Properties=\"Excel 12.0;HDR=NO\"";
string SQL = "SELECT COUNT (*) FROM [Sheet1$]";
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = new OleDbCommand(SQL, conn))
{
using (OleDbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
count = reader[0].ToString();
lblMessage.Text = " There are " + count + " Records In The Excel Sheet";
}
}
conn.Close();
}
#endregion
}
else
{
lblMessage.Text = "Please Enter Xlsx File Only";
}
}
catch (Exception ex)
{
ClientScript.RegisterClientScriptBlock(Page.GetType(), "alert", "alert('" + ex.Message + ".');", true);
lblMessage.Text = "" + ex.Message + "";
return;
}
#endregion
}
After Selecting The File Using File Upload Control And After Clicking the Count Button . The Process Would Count No Of Records In The Uploaded Excel File
