protected void imgButtonSearchProperty_Click(object sender, EventArgs e)
{
try
{
if (FileUpload1.HasFile)
{
lblmsg.Visible = false;
GridView2.DataSource = null;
GridView2.DataBind();
GridView2.Visible = false;
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
AppLog.Log("The name of the file is " + FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
AppLog.Log("The name of the extension is " + Extension);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
AppLog.Log("The name of the FolderPath is " + FolderPath);
string FilePath = Server.MapPath(FolderPath + "\\" + FileName);
AppLog.Log("The name of the FilePath is " + FilePath);
FileUpload1.SaveAs(FilePath);
Import_To_Grid(FilePath, Extension);
}
}
catch (Exception ex)
{
}
}
private void Import_To_Grid(string FilePath, string Extension)
{
try
{
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, 1);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
cmdExcel.Connection = connExcel;
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
//dSUploadInventory.Tables.Add(dtExcelSchema);
connExcel.Close();
//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
////To
string expression = string.Empty;
expression = "ISNULL(PropertyId,0)=0";
DataRow[] rows = dt.Select(expression);
if (rows.Length > 0)
{
foreach (DataRow row in rows)
{
row.Delete();
row.AcceptChanges();
}
}
////To Do
ViewState["ExcelData"] = dt;
GridView1.DataSource = dt;
GridView1.DataBind();
if (dt.Rows.Count > 0)
{
imgButtonSubmit.Visible = true;
}
}
catch (Exception ex)
{
}
}
The connection string that I am using in web.config file is :
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0};Extended Properties='Excel 8.0;HDR={1}'" />
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data
Source={0};Extended Properties='Excel 12.0;HDR={1}'" />
When I am trying to upload the excel using aforementioned code , I am getting the following error :
“External table is not in the expected format.”
I have added the connection string Help would be appreciated.
