protected void btnimport_Click1(object sender, ImageClickEventArgs e)
{
try
{
//string FilePath = "D:\\upload";
//bool blSucces = false;
string filename = string.Empty;
//string[] allowdFile = { ".pdf" };
////Here we are allowing only pdf file so verifying selected file pdf or not
//string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName);
//bool isValidFile = allowdFile.Contains(FileExt);
//if (!isValidFile)
//{
// lblMsg.ForeColor = System.Drawing.Color.Red;
// lblMsg.Text = "Please upload only exl ";
//}
if (fileuploademp.HasFile)
{
//string[] allowdFile = { ".xlsx" };
//Get file name of selected file
filename = System.IO.Path.GetFileName(fileuploademp.FileName);
fileuploademp.PostedFile.SaveAs(Server.MapPath("~/Uploads/" + filename));
ArrayList alist = new ArrayList();
string connString = "";
string strFileType = Path.GetExtension(fileuploademp.FileName).ToLower();
string fileBasePath = Server.MapPath("~/Uploads/");
string fileName = Path.GetFileName(this.fileuploademp.FileName);
string fullFilePath = fileBasePath + fileName;
//Connection String to Excel Workbook
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullFilePath +
";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullFilePath +
";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
}
if (fileuploademp.HasFile)
{
//string query = "select student,rollno,course from [sheet1$]";
string query = "select student,rollno,course from [sheet1$]";
using (OleDbConnection conn = new OleDbConnection(connString))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
FillInDB(ds);
Session["griddata"] = ds.Tables[0];
GridViewImport.DataSource = Session["griddata"];
GridViewImport.DataBind();
//OleDbCommand command = new OleDbCommand(query, conn);
//// Create DbDataReader to Data Worksheet
//using (DbDataReader dr = command.ExecuteReader())
//{
// // SQL Server Connection String
// Connect con = new Connect();
// string sqlConnectionString = WebConfigurationManager.ConnectionStrings["con"].ToString();
// // Bulk Copy to SQL Server
// using (System.Data.SqlClient.SqlBulkCopy bulkCopy =
// new System.Data.SqlClient.SqlBulkCopy(sqlConnectionString))
// {
// bulkCopy.DestinationTableName = "tdatamigrationtable";
// bulkCopy.WriteToServer(dr);
// lblmsg.Text = "The data has been exported succefuly from Excel to SQL";
// }
//}
}
}
// Create DbDataReader to Data Worksheet
Employee obj_emp = new Employee();
// obj_emp.importdatafromexcel("~/Uploads/" + filename);
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message.ToString();
}
}
protected void btnimport_Click1(object sender, ImageClickEventArgs e)
{
try
{
//string FilePath = "D:\\upload";
//bool blSucces = false;
string filename = string.Empty;
//string[] allowdFile = { ".pdf" };
////Here we are allowing only pdf file so verifying selected file pdf or not
//string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName);
//bool isValidFile = allowdFile.Contains(FileExt);
//if (!isValidFile)
//{
// lblMsg.ForeColor = System.Drawing.Color.Red;
// lblMsg.Text = "Please upload only exl ";
//}
if (fileuploademp.HasFile)
{
//string[] allowdFile = { ".xlsx" };
//Get file name of selected file
filename = System.IO.Path.GetFileName(fileuploademp.FileName);
fileuploademp.PostedFile.SaveAs(Server.MapPath("~/Uploads/" + filename));
ArrayList alist = new ArrayList();
string connString = "";
string strFileType = Path.GetExtension(fileuploademp.FileName).ToLower();
string fileBasePath = Server.MapPath("~/Uploads/");
string fileName = Path.GetFileName(this.fileuploademp.FileName);
string fullFilePath = fileBasePath + fileName;
//Connection String to Excel Workbook
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullFilePath +
";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullFilePath +
";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
}
if (fileuploademp.HasFile)
{
//string query = "select student,rollno,course from [sheet1$]";
string query = "select EmployeeID,Name,Competency,Location,Vertical,CustomerName,ProjectName,DeliveryManager,AccountCategory from [sheet1$]";
using (OleDbConnection conn = new OleDbConnection(connString))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
FillInDB(ds);
Validate_UploadedData();
Session["griddata"] = ds.Tables[0];
GridViewImport.DataSource = Session["griddata"];
GridViewImport.DataBind();
}
}
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message.ToString();
}
}
