User1839833660 posted
Refer
Upload and read Excel file In Asp.Net
Write this method to find duplicates in datatable
public DataTable RemoveDuplicate(DataTable dt, string column)
{
Hashtable hash = new Hashtable();
ArrayList aList = new ArrayList();
foreach (DataRow row in dt.Rows)
{
if (hash.Contains(row[column]))
aList.Add(row);
else
hash.Add(row[column], string.Empty);
}
foreach (DataRow Row in aList)
dt.Rows.Remove(Row);
return dt;
}
And Change button_Click code to this
protected void btnUpload_Click(object sender, EventArgs e)
{
string connectionString ="";
if (FileUpload1.HasFile)
{
string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string fileLocation = Server.MapPath("~/App_Data/" + fileName);
FileUpload1.SaveAs(fileLocation);
//Check whether file extension is xls or xslx
if (fileExtension == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create OleDB Connection and OleDb Command
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName +"]";
dAdapter.SelectCommand = cmd;
dAdapter.Fill(dtExcelRecords);
dtExcelRecords = RemoveDuplicate(dtExcelRecords, "EmpID");
con.Close();
GridView1.DataSource = dtExcelRecords;
GridView1.DataBind();
}
}
