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

Form1.JPG

Put the following code on Browser button click events…..

  1. private void button1_Click_1(object sender, EventArgs e)
  2. {
  3. OpenFileDialog fdlg = new OpenFileDialog();
  4. fdlg.Title = "Select file";
  5. fdlg.InitialDirectory = @"c:\";
  6. fdlg.FileName = txtFileName.Text;
  7. fdlg.Filter = "Excel Sheet(*.xls)|*.xls|All Files(*.*)|*.*";
  8. fdlg.FilterIndex = 1;
  9. fdlg.RestoreDirectory = true;
  10. if (fdlg.ShowDialog() == DialogResult.OK)
  11. {
  12. txtFileName.Text = fdlg.FileName;
  13. Import();
  14. Application.DoEvents();
  15. }
  16. }
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

  1. private void Select_Tables_Load(object sender, EventArgs e)
  2. {
  3. if (!DataTables)
  4. {
  5. if (Tables != null)
  6. {
  7. for (int tables = 0; tables < Tables.Length; tables++)
  8. {
  9. try
  10. {
  11. ListViewItem lv = new ListViewItem();
  12. lv.Text = Tables[tables].ToString();
  13. lv.Tag = tables;
  14. lstViewTables.Items.Add(lv);
  15. }
  16. catch (Exception ex)
  17. { }
  18. }
  19. }
  20. }
  21. else
  22. {
  23. if (dtTable.Rows.Count>0)
  24. {
  25. for (int tables = 0; tables < dtTable.Rows.Count; tables++)
  26. {
  27. try
  28. {
  29. ListViewItem lv = new ListViewItem();
  30. lv.Text = dtTable.Rows[tables][0].ToString();
  31. lv.Tag = dtTable.Rows[tables][0];
  32. lstViewTables.Items.Add(lv);
  33. }
  34. catch (Exception ex)
  35. { }
  36. }
  37. }
  38. }
  39. }
By the following function we can find the total sheets in Excel file.
  1. public static string[] GetTableExcel(string strFileName)
  2. {
  3. string[] strTables = new string[100];
  4. Catalog oCatlog = new Catalog();
  5. ADOX.Table oTable = new ADOX.Table();
  6. ADODB.Connection oConn = new ADODB.Connection();
  7. oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0);
  8. oCatlog.ActiveConnection = oConn;
  9. if (oCatlog.Tables.Count > 0)
  10. {
  11. int item = 0;
  12. foreach (ADOX.Table tab in oCatlog.Tables)
  13. {
  14. if (tab.Type == "TABLE")
  15. {
  16. strTables[item] = tab.Name;
  17. item++;
  18. }
  19. }
  20. }
  21. return strTables;
  22. }

Form2.JPG

Following function return a dataset so that you can bind it from Data Grid View easily.
  1. public static DataTable GetDataTableExcel(string strFileName, string Table)
  2. {
  3. 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\";");
  4. conn.Open();
  5. string strQuery = "SELECT * FROM [" + Table + "]";
  6. System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
  7. System.Data.DataSet ds = new System.Data.DataSet();
  8. adapter.Fill(ds);
  9. return ds.Tables[0];
  10. }

Form3.JPG

To see the complete source code you can download the zip file.