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.

 
Next Recommended Reading Import and Export Data From Excel to Database