I am new to C# and .NET... I found a code on www.aspsnnipets.com about how to import excel file data to windows forms datagridview using C#. Due to some errors I had to change it a little bit. But now when I click on the OPEN button on OpenFileDialog I am getting an unhandled exception saying "The connectionString property has not been initialized". How to resolve it because I need this utility in my project. The whole code is as follows:
private void btnImp_Click(object sender, EventArgs e)
{
var dlg = new OpenFileDialog();
if (dlg.ShowDialog(this) != DialogResult.OK)
return;
string fileName;
string fileLocation;
fileName = dlg.FileName;
fileLocation = Pth.GetDirectoryName(fileName);
openFileDialog1_FileOk(fileName, fileLocation);
}
private void openFileDialog1_FileOk(string filenme, string filelocation)
{
string filePath = filename;
string extension = filelocation;
string header = rbHeaderYes.Checked ? "YES" : "NO";
string conStr, sheetName;
conStr = string.Empty;
switch (extension)
{
case ".xls": //Excel 97-03
conStr = string.Format(Excel03ConString, filePath, header);
break;
case ".xlsx": //Excel 07
conStr = string.Format(Excel07ConString, filePath, header);
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;
}
}
}
}