protected void btnfileupload_Click(object sender, EventArgs e)
{
// lblmessage.Visible = false;
//SqlCommand cmd = new SqlCommand("SELECT * FROM FileNameTbl where Filename='" + File12.FileName + "'", con);
//SqlDataAdapter oleda = new SqlDataAdapter();
//oleda.SelectCommand = cmd;
ViewState["fl"] = File12.FileName;
//DataTable ds1 = new DataTable();
//oleda.Fill(ds1);
//if (ds1.Rows.Count == 0)
//{
if (File12.HasFile)
{
upfile = 0;
rejfile = 0;
ViewState["up"] = null;
ViewState["rej"] = null;
string FileName = Path.GetFileName(File12.PostedFile.FileName);
string Extension = Path.GetExtension(File12.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
FilePath = Server.MapPath(FolderPath + "ExelFile\\" + FileName);
File12.SaveAs(FilePath);
//SqlCommand cmd1 = new SqlCommand("Insert into FileNameTbl(Filename,Uploaddate)values('" + File12.FileName + "','" + DateTime.Now.ToShortDateString() + "')", con);
//con.Open();
//cmd1.ExecuteNonQuery();
//con.Close();
// fillgrid();
}
string FolderPath12 = ConfigurationManager.AppSettings["FolderPath"];
string FilePath12 = Server.MapPath(FolderPath12 + "ExelFile\\" + File12.FileName);
string SourceFilePath = FilePath12;
Session["storesourcefilepath"] = SourceFilePath;
// ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + SourceFilePath + ";Extended Properties=Excel 8.0;";
// String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=pricelist.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES""");
ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + SourceFilePath + ";Extended Properties=Excel 12.0 Xml;";
oledbConn = new OleDbConnection(ConnectionString);
if (oledbConn.State.ToString() != "Open")
{
oledbConn.Open();
}
//Get the Sheets in Excel WorkBoo
ConnectionString = String.Format(ConnectionString, FilePath);
OleDbConnection connExcel = new OleDbConnection(ConnectionString);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
cmdExcel.Connection = connExcel;
connExcel.Open();
//Bind the Sheets to DropDownList
ddlSheets.Items.Clear();
ddlSheets.Items.Add(new ListItem("--Select Sheet--", ""));
ddlSheets.DataSource = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
ddlSheets.DataTextField = "TABLE_NAME";
ddlSheets.DataValueField = "TABLE_NAME";
ddlSheets.DataBind();
ddlSheets.Items.Insert(0, "-Select-");
ddlSheets.Items[0].Value = "0";
connExcel.Close();
oledbConn.Close();
lblmessage.Visible = true;
lblmessage.Text = "File Uploaded Successfully.";
pnltransfer.Visible = true;
//}
//else
//{
// ddlSheets.Items.Clear();
// ddlSheets.Items.Insert(0, "-Select-");
// ddlSheets.Items[0].Value = "0";
// lblmessage.Text = "This FileName is already used.";
//}
}
protected void btnfileupload_Click(object sender, EventArgs e)
{
// Label1.Visible = false;
if (File12.HasFile)
{
try
{
string FileName = Path.GetFileName(File12.PostedFile.FileName);
string Extension = Path.GetExtension(File12.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
FilePath = Server.MapPath(FolderPath + "ExelFile\\" + FileName);
File12.SaveAs(FilePath);
string FolderPath12 = ConfigurationManager.AppSettings["FolderPath"];
string FilePath12 = Server.MapPath(FolderPath12 + "ExelFile\\" + File12.FileName);
string SourceFilePath = FilePath12;
ViewState["fn"] = File12.FileName;
Session["storesourcefilepath"] = SourceFilePath;
// ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + SourceFilePath + ";Extended Properties=Excel 8.0;";
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + SourceFilePath + ";Extended Properties=Excel 12.0;";
OleDbConnection oledbConn = new OleDbConnection(ConnectionString);
if (oledbConn.State.ToString() != "Open")
{
oledbConn.Open();
}
//****************
//Get the Sheets in Excel WorkBoo
ConnectionString = String.Format(ConnectionString, FilePath);
OleDbConnection connExcel = new OleDbConnection(ConnectionString);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
cmdExcel.Connection = connExcel;
if (connExcel.State.ToString() != "Open")
{
connExcel.Open();
}
//Bind the Sheets to DropDownList
ddlSheets.Items.Clear();
ddlSheets.Items.Add(new ListItem("--Select Sheet--", ""));
ddlSheets.DataSource = connExcel
.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
ddlSheets.DataTextField = "TABLE_NAME";
ddlSheets.DataValueField = "TABLE_NAME";
ddlSheets.DataBind();
connExcel.Close();
oledbConn.Close();
}
catch (Exception exp)
{
Label1.Visible = true;
Label1.Text = exp.ToString();
}
}
else
{
Label1.Visible = true;
Label1.Text = "Please Select the Excel file....";
}
}