Here I’ll show you how to do validations in Excel sheet while importing records. You could follow these steps
1. Create a table for import the records to database
2. Upload excel sheet to the server folder
3. Fetch records from that excel sheet
4. Store records to DataTable
5. Validate data
6. Insert records to the table
7. Display the records to GridView
So first we have to create a table for import the records to database.
Create Table script:
CREATE TABLE [dbo].[EmpImport](
[EmployeeID] [varchar](10) NULL,
[Name] [varchar](50) NULL,
[Designation] [varchar](50) NULL,
[DateOfBirth] [varchar](10) NULL,
[City] [varchar](50) NULL
) ON [PRIMARY]
Designer source code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="CSV" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Excel sheet validation</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FlUploadcsv" runat="server" />
<asp:Button ID="btnIpload" runat="server" Text="Import" OnClick="btnIpload_Click" />
<br />
<asp:GridView ID="gvEmployee" runat="server" width="100%"> <HeaderStyle BackColor="#89A0FE" />
</asp:GridView>
</div>
</form>
</body>
</html>
Here we have to use following namespaces
using System.IO;
using System.Data.OleDb;
Code behind:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
public partial class CSV : System.Web.UI.Page
{
SqlConnection conn = new SqlConnection("Data Source=Jitendra;Initial Catalog=Demo;Integrated Security=True");
DataSet ds;
DataTable Dt;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
private void ImporttoDatatable()
{
try
{
if (FlUploadcsv.HasFile)
{
string FileName = FlUploadcsv.FileName;
string path = string.Concat(Server.MapPath("~/Document/" + FlUploadcsv.FileName));
FlUploadcsv.PostedFile.SaveAs(path);
OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
ds = new DataSet();
objAdapter1.Fill(ds);
Dt = ds.Tables[0];
}
}
catch (Exception ex)
{
}
}
private void CheckData()
{
try
{
for (int i = 0; i < Dt.Rows.Count; i++)
{
if (Dt.Rows[i][0].ToString() == "")
{
int RowNo = i + 2;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Employee ID in row " + RowNo + "');", true);
return;
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
if (Dt.Rows[i][1].ToString() == "")
{
int RowNo = i + 2;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Name in row " + RowNo + "');", true);
return;
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
if (Dt.Rows[i][2].ToString() == "")
{
int RowNo = i + 2;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Designation in row " + RowNo + "');", true);
return;
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
string date= DateTime.Parse(Dt.Rows[i][3].ToString()).ToString("dd/MM/yyyy");
if (!ValidateDate(date))
{
int RowNo = i + 2;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Wrong Date format in row " + RowNo + "');", true);
return;
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
if (Dt.Rows[i][4].ToString() == "")
{
int RowNo = i + 2;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please Enter City in Row " + RowNo + "');", true);
return;
}
}
}
catch (Exception ex)
{
}
}
private bool ValidateDate(string date)
{
try
{
string[] dateParts = date.Split('/');
DateTime testDate = new DateTime(Convert.ToInt32(dateParts[2]), Convert.ToInt32(dateParts[1]), Convert.ToInt32(dateParts[0]));
return true;
}
catch
{
return false;
}
}
private void InsertData()
{
for (int i = 0; i < Dt.Rows.Count; i++)
{
DataRow row = Dt.Rows[i];
int columnCount = Dt.Columns.Count;
string[] columns = new string[columnCount];
for (int j = 0; j < columnCount; j++)
{
columns[j] = row[j].ToString();
}
conn.Open();
string sql = "INSERT INTO EmpImport(EmployeeID,Name,Designation,DateOfBirth,City)";
sql += "VALUES('" + columns[0] + "','" + columns[1] + "','" + columns[2] + "',Convert(varchar(10),'" + columns[3] + "',103),'" + columns[4] + "')";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
protected void btnIpload_Click(object sender, EventArgs e)
{
ImporttoDatatable();
CheckData();
InsertData();
BindGrid();
}
private void BindGrid()
{
DataSet ds = new DataSet();
conn.Open();
string cmdstr = "Select * from EmpImport";
SqlDataAdapter adp = new SqlDataAdapter(cmdstr,conn);
adp.Fill(ds);
gvEmployee.DataSource = ds;
gvEmployee.DataBind();
ds.Dispose();
conn.Close();
}
}
Note:
While running this demo maybe you will get error something like
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
So you could download and install 2007 Office System Driver: Data Connectivity Components. Download link is given below.
http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734
No comments:
Post a Comment