OpenFileDialog openDialog = new OpenFileDialog();
openDialog.Title = "Select file";
openDialog.InitialDirectory = @"c:\";
openDialog.Filter = "Excel Sheet(*.xlsx)|*.xlsx|All Files(*.*)|*.*";
openDialog.FilterIndex = 1;
openDialog.RestoreDirectory = true;
if (openDialog.ShowDialog() == DialogResult.OK)
{
if (openDialog.FileName != "")
{
strExcelPathName = openDialog.FileName;
cmbExcelSheet.DataSource = GetSheetNames(openDialog.FileName);
}
else
{
MessageBox.Show("chose Excel sheet path..", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
then..write this code in button Click Event to show in DataGridview....
if (System.IO.File.Exists(strExcelPathName))
{
string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""",strExcelPathName);
string query = String.Format("select * from [{0}$]",cmbExcelSheet.SelectedItem);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
DataTable dtView = dataSet.Tables[0];
if (dtView.Rows.Count > 0)
{
dgrdReciver.Rows.Clear();
dgrdReciver.Rows.Add(dtView.Rows.Count);
int i = 0;
foreach (DataRow drow in dtView.Rows)
{
dgrdReciver.Rows[i].Cells["Addressid"].Value = drow["ADRSID"];
dgrdReciver.Rows[i].Cells["ReciverName"].Value = drow["NAME"];
dgrdReciver.Rows[i].Cells["companyName"].Value = drow["COMPANY"];
dgrdReciver.Rows[i].Cells["RecvAddress"].Value = drow["ADDRESS"];
dgrdReciver.Rows[i].Cells["ReciverState"].Value = drow["STATE"];
dgrdReciver.Rows[i].Cells["ReciverCity"].Value = drow["CITY"];
dgrdReciver.Rows[i].Cells["RecvCountryCode"].Value = drow["CONTRY CODE"];
dgrdReciver.Rows[i].Cells["ReciverPostal"].Value = drow["POSTAL CODE"];
dgrdReciver.Rows[i].Cells["RecvContactNo"].Value = drow["CONTACT NO"];
i++;
}
}
}
else
{
MessageBox.Show("No File is Selected");
}