Hi All,
The code is working for .xlsx file but when I am choosing .xls file it is throwing error "External table is not in expected format"
I installed 2007 Office System Driver: Data Connectivity Components
https://www.microsoft.com/en-us/download/details.aspx?id=23734
I looked in various forums and changed connectionString with all possible ways but it didn't worked.
So what's the problem in here? Thank all
public partial class Form3 : Form
{
string connectionString;
private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
public Form3()
{
InitializeComponent();
connectionString = @"Data Source = C:\Users\JayV\Documents\EmailChecker.db; Version=3";
}
private void button2_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
}
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
string filePath = openFileDialog1.FileName;
string extension = Path.GetExtension(filePath);
string conStr, sheetName;
conStr = string.Empty;
switch (extension)
{
case ".xls": //Excel 97-03
conStr = string.Format(Excel03ConString, filePath, "YES");
break;
case ".xlsx": //Excel 07
conStr = string.Format(Excel07ConString, filePath, "YES");
break;
}
//Get the name of the First Sheet.
using (OleDbConnection con = new OleDbConnection(conStr))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = con;
con.Open();
DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
con.Close();
}
}
//Read Data from the First Sheet.
using (OleDbConnection con = new OleDbConnection(conStr))
{
using (OleDbCommand cmd = new OleDbCommand())
{
using (OleDbDataAdapter oda = new OleDbDataAdapter())
{
DataTable dt = new DataTable();
cmd.CommandText = "SELECT * From [" + sheetName + "]";
cmd.Connection = con;
con.Open();
oda.SelectCommand = cmd;
oda.Fill(dt);
con.Close();
//Populate DataGridView.
dataGridView1.DataSource = dt;
}
}
}
}
}