Post
Ask Question

Get All Sheets From Excel And Fill Data Of Selected Excel Sheet In DataGridView

Step 1: Create new widows form application.

Step 2: Create windows form with button, label, combobox and datagridview as below.

 

Step 3: Write following code.

  1. using System;  
  2.   
  3. using System.Data;  
  4. using System.Data.OleDb;  
  5.   
  6. using System.Windows.Forms;  
  7.   
  8. namespace ExcelDemo  
  9.   
  10. {  
  11.   
  12.     public partial class Form1: Form  
  13.   
  14.     {  
  15.   
  16.         OleDbConnection OleDbcon;  
  17.   
  18.         public Form1()  
  19.   
  20.         {  
  21.   
  22.             InitializeComponent();  
  23.   
  24.         }  
  25.   
  26.         private void button1_Click(object sender, EventArgs e)  
  27.   
  28.         {  
  29.   
  30.             OpenFileDialog openFileDialog = new OpenFileDialog();  
  31.   
  32.             openFileDialog.Filter = "Excel Files|*.xls;*.xlsx";  
  33.   
  34.             openFileDialog.ShowDialog();  
  35.   
  36.             if (!string.IsNullOrEmpty(openFileDialog.FileName))  
  37.   
  38.             {  
  39.   
  40.                 OleDbcon = new OleDbConnection(@ "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + openFileDialog.FileName + ";Extended Properties=Excel 12.0;");  
  41.   
  42.                 OleDbcon.Open();  
  43.   
  44.                 DataTable dt = OleDbcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
  45.   
  46.                 OleDbcon.Close();  
  47.   
  48.                 comboBox1.Items.Clear();  
  49.   
  50.                 for (int i = 0; i < dt.Rows.Count; i++)  
  51.   
  52.                 {  
  53.   
  54.                     String sheetName = dt.Rows[i]["TABLE_NAME"].ToString();  
  55.   
  56.                     sheetName = sheetName.Substring(0, sheetName.Length - 1);  
  57.   
  58.                     comboBox1.Items.Add(sheetName);  
  59.   
  60.                 }  
  61.   
  62.             }  
  63.   
  64.         }  
  65.   
  66.         private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)  
  67.   
  68.         {  
  69.   
  70.             OleDbDataAdapter oledbDa = new OleDbDataAdapter("Select * from [" + comboBox1.Text + "$]", OleDbcon);  
  71.   
  72.             DataTable dt = new DataTable();  
  73.   
  74.             oledbDa.Fill(dt);  
  75.   
  76.             dataGridView1.DataSource = dt;  
  77.   
  78.         }  
  79.   
  80.         private void button2_Click(object sender, EventArgs e)  
  81.   
  82.         {  
  83.   
  84.             OpenFileDialog OpenFileDialog = new OpenFileDialog();  
  85.   
  86.             OpenFileDialog.ShowDialog();  
  87.   
  88.             string path = OpenFileDialog.FileName;  
  89.   
  90.         }  
  91.   
  92.     }  
  93.   
  94. }  

Step 4: Run application. And click on browse button.

 

Step 5: From open file dialog select excel file. When file is selected, all sheet names in file are filled in combobox.

 

Step 6: Select any sheet name from combobox. Then data from that selected sheet is filled in datagridview. The first row of excel sheet is considered as column header.