Tuesday, January 15, 2013
Import data From Excel File to Data Table
protected void BtnUpload_Click(object sender, EventArgs e)
{
string[] strarr = { "commodity", "forecast type", "unit of measurement", "volume","profile type", "profilecomp", "profileloc", "year", "month", "date", "hour","time zone" };
string extension = System.IO.Path.GetExtension(FileUpload1.FileName).ToUpper();
if (FileUpload1.HasFile)
{
if (extension == ".XLS" || extension == ".XLSX")
{
try
{
string[] names = FileUpload1.FileName.Split('.');
string src = Server.MapPath("~/Upload/") + DateTime.Now.ToString("yyyyMMddhhmmss") + "." + names[names.Length - 1];
FileUpload1.SaveAs(src);
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + src + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Old string excelConnectionString = @"Provider= Microsoft.Jet.OLEDB.4.0;Data Source=" + src + "; Extended Properties='Excel 8.0;HDR=Yes;'";
OleDbConnection con = new OleDbConnection(excelConnectionString);
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", con);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = ds.Tables[0];
bool bl = true;
for (int i = 0; i < dt.Columns.Count; i++)
{
if (strarr[i] != dt.Columns[i].ColumnName.ToLower())
{
bl = false; break;
}
}
if (bl)
{
dt=getTextForGrid(dt);
ViewState["dt"] = dt;
bindGrid();
}
else
{
Common.alert("File Is Not a Well Formate collumn MisMatch...!", this);
}
}
else
{
Common.alert("please select only excel file", this);
}
}
catch (Exception ex)
{
Common.alert(ex.Message, this);
}
}
else {
Common.alert("Only select excel file",this);
}
}
else
{
Common.alert("select excel file",this);
}
}
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment