Import Excel
protected void btnImport_Click(object sender, EventArgs e)
{
string ExcelFilename = FileUpload1.PostedFile.FileName;
string ExcelFileNameonly = ExcelFilename.Substring(ExcelFilename.LastIndexOf("\\") + 1);
string FileExt = ExcelFilename.Substring(ExcelFilename.LastIndexOf(".") + 1);
string Filenamewithoutextn = ExcelFileNameonly.Remove(ExcelFileNameonly.LastIndexOf("."));
if (FileExt.ToLower() == "xls")
{
try
{
FileUpload1.PostedFile.SaveAs(Server.MapPath("Imported Files/") + Filenamewithoutextn + ".xls");
string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Imported Files/") + Filenamewithoutextn + ".xls;Extended Properties=Excel 8.0;";
OleDbConnection olecon = new OleDbConnection(strConnectionString);
int RecordCount = 0;
lblMessage.Visible = false;
lblError.Visible = false;
olecon.Open();
OleDbCommand cmd = olecon.CreateCommand();
cmd.CommandText = "SELECT * FROM [Sheet1$]";
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
OleDbDataReader dr = cmd.ExecuteReader();
sqlcon.Open();
string deletestr = "delete from employee";
SqlCommand sqlcmd = new SqlCommand(deletestr, sqlcon);
sqlcmd.ExecuteNonQuery();
while (dr.Read())
{
SqlCommand cmd1 = sqlcon.CreateCommand();
cmd1.CommandText = "INSERT INTO employee values ('" + dr[0] + "','" + dr[1] + "')";
cmd1.ExecuteNonQuery();
RecordCount++;
lblMessage.Visible = true;
lblMessage.Text = " Processed Record # " + RecordCount.ToString();
}
dr.Close();
olecon.Close();
BindGrid();
sqlcon.Close();
}
catch (Exception ex)
{
lblError.Visible = true;
lblError.Text = ex.Message;
}
}
}
private void BindGrid()
{
try
{
lblError.Visible = false;
SqlCommand cmd2 = sqlcon.CreateCommand();
cmd2.CommandText = "SELECT * FROM employee order by emp_id";
DataSet sds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(cmd2);
sda.Fill(sds, "employee");
MyGrid.DataSource = sds.Tables["employee"].DefaultView;
MyGrid.DataBind();
}
catch (Exception ex)
{
lblError.Visible = true;
lblError.Text = ex.Message;
}
}
protected void MyGrid_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
MyGrid.PageIndex = e.NewPageIndex;
BindGrid();
}