First need to add the reference "Microsoft ADO
Ext. 2.8". You can easily add it from COM components.
Add an open Dialog box control on form
Put the following code on Browser button click
events…..
private void
button1_Click_1(object sender,
EventArgs e)
{
OpenFileDialog fdlg = new OpenFileDialog();
fdlg.Title = "Select file";
fdlg.InitialDirectory = @"c:\";
fdlg.FileName = txtFileName.Text;
fdlg.Filter = "Excel Sheet(*.xls)|*.xls|All
Files(*.*)|*.*";
fdlg.FilterIndex = 1;
fdlg.RestoreDirectory = true;
if (fdlg.ShowDialog() ==
DialogResult.OK)
{
txtFileName.Text = fdlg.FileName;
Import();
Application.DoEvents();
}
}
This will filter only Excel file from your Machine.
This Excel file can contains more than one Sheet. You need to add another form
to all excel sheets name so that user can select any one excel sheet which he
want to import.
Write the following code on Page Load even of this
form
private void Select_Tables_Load(object
sender, EventArgs e)
{
if (!DataTables)
{
if (Tables !=
null)
{
for (int tables = 0; tables <
Tables.Length; tables++)
{
try
{
ListViewItem lv = new ListViewItem();
lv.Text = Tables[tables].ToString();
lv.Tag = tables;
lstViewTables.Items.Add(lv);
}
catch (Exception
ex)
{ }
}
}
}
else
{
if (dtTable.Rows.Count>0)
{
for (int tables = 0; tables <
dtTable.Rows.Count; tables++)
{
try
{
ListViewItem lv = new ListViewItem();
lv.Text = dtTable.Rows[tables][0].ToString();
lv.Tag = dtTable.Rows[tables][0];
lstViewTables.Items.Add(lv);
}
catch (Exception ex)
{ }
}
}
}
}
By the following function we can find the total
sheets in Excel file.
public static string[] GetTableExcel(string
strFileName)
{
string[] strTables =
new string[100];
Catalog
oCatlog = new Catalog();
ADOX.Table oTable = new
ADOX.Table();
ADODB.Connection oConn = new
ADODB.Connection();
oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " +
strFileName + "; Extended Properties =
\"Excel 8.0;HDR=Yes;IMEX=1\";", "", "",
0);
oCatlog.ActiveConnection = oConn;
if (oCatlog.Tables.Count > 0)
{
int item = 0;
foreach (ADOX.Table
tab in oCatlog.Tables)
{
if (tab.Type == "TABLE")
{
strTables[item] = tab.Name;
item++;
}
}
}
return strTables;
}
Following function return a dataset so that you can bind it from Data Grid View
easily.
public static DataTable GetDataTableExcel(string strFileName, string
Table)
{
System.Data.OleDb.OleDbConnection
conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " +
strFileName + "; Extended Properties =
\"Excel 8.0;HDR=Yes;IMEX=1\";");
conn.Open();
string strQuery =
"SELECT * FROM [" + Table +
"]";
System.Data.OleDb.OleDbDataAdapter
adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
System.Data.DataSet ds = new
System.Data.DataSet();
adapter.Fill(ds);
return ds.Tables[0];
}
To see the complete source code you can download
the zip file.