External table is not in the expected format when XLS file read in window application (ASP.net c#
I want to read excel file data and show into grid in window application.I am getting External table is not in the expected format. is error again and again. how i can fix this error.
i have installed the ddl as per required.
1) Microsoft.Office.Interop.Excel
2) Microsoft.Office.Interop.Access.Dao
3) Interop.Microsoft.Office.Core
Also add into the namespace.
Code is given below:
private void btnCreateFolder_Click(object sender, EventArgs e)
{
string filePath = string.Empty;
string fileExt = string.Empty;
OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file
if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file choosen by the user
{
filePath = file.FileName; //get the path of the file
fileExt = Path.GetExtension(filePath); //get the file extension
if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0 || fileExt.CompareTo(".XLS") == 0 || fileExt.CompareTo(".XLSX") == 0 || fileExt.CompareTo(".csv") == 0 || fileExt.CompareTo(".CSV") == 0)
{
try
{
System.Data.DataTable dtExcel = new System.Data.DataTable();
dtExcel = ReadExcel(filePath, fileExt); //read excel file
dataGridView1.Visible = true;
dataGridView1.DataSource = dtExcel;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
else
{
MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error
}
}
}
public System.Data.DataTable ReadExcel(string fileName, string fileExt)
{
string conn = string.Empty;
System.Data.DataTable dtexcel = new System.Data.DataTable();
string Import_FileName = fileExt;
if (fileExt.CompareTo(".XLS") == 0 )
conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007 //comment By Nitin
else
conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007
using (OleDbConnection con = new OleDbConnection(conn))
{
try
{
OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1
oleAdpt.Fill(dtexcel); //fill excel data into dataTable
}
// catch { }
catch (Exception ex)
{
}
}
return dtexcel;
}