List of Exercises

Show Sheets of Excel File to DataTable in Winforms C#

	    
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Excel_file_into_Datatable
{
  public partial class Form1 : Form
  {
    OleDbConnection OledbConn;
    OleDbCommand OledbCmd;
    string connString = "";
    // https://www.microsoft.com/en-us/download/details.aspx?id=13255
    public Form1()
    {
      InitializeComponent();
    }

    private void read_excel_File_Click(object sender, EventArgs e)
    {
      OpenFileDialog opf = new OpenFileDialog();
      opf.Title = "Open Excel File.";
      opf.Filter = "Excel File(*.xlsx)|*.xlsx|Excel 97(*.xls)|*.xls";
      var result = opf.ShowDialog();
      if (result == DialogResult.OK)
      {
        System.IO.FileInfo finfo = new System.IO.FileInfo(opf.FileName);
        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + opf.FileName + ";Extended Properties='Excel 12.0;IMEX=1;'";
        var sheets = GetExcelSheetNames(opf.FileName);
        comboBox1.Items.AddRange(sheets);
        comboBox1.Enabled = true;
        comboBox1.Text = "Select Sheet";
      }
    }
    private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
    {
      DataTable tempTable = ReadFile(comboBox1.SelectedItem.ToString());
      dataGridView1.DataSource = tempTable;
    }
    private String[] GetExcelSheetNames(string excelFile)
    {
      System.Data.DataTable dt = null;

      try
      {
        OledbConn = new OleDbConnection(connString);
        // Open connection with the database.
        OledbConn.Open();
        // Get the data table containg the schema guid.
        dt = OledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if (dt == null)
        {
          return null;
        }

        String[] excelSheets = new String[dt.Rows.Count];
        int i = 0;

        // Add the sheet name to the string array.
        foreach (DataRow row in dt.Rows)
        {
          excelSheets[i] = row["TABLE_NAME"].ToString().Replace("$","");
          i++;
        }

        // Loop through all of the sheets if you want too...
        for (int j = 0; j < excelSheets.Length; j++)
        {
          // Query each excel sheet.
        }

        return excelSheets;
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        return null;
      }
      finally
      {
        // Clean up.
        if (OledbConn != null)
        {
          OledbConn.Close();
          OledbConn.Dispose();
        }
        if (dt != null)
        {
          dt.Dispose();
        }
      }
    }
    private DataTable ReadFile( string sheet)
    {
      try
      {
        OledbConn = new OleDbConnection(connString);
        DataTable schemaTable = new DataTable();
        OledbConn.Open();
        var MyCommand = new System.Data.OleDb.OleDbDataAdapter("Select * from [" + sheet + "$]", OledbConn);
        var dt = OledbConn.GetSchema("Tables");;
        MyCommand.TableMappings.Add("Table", "TestTable");
        var DtSet = new System.Data.DataSet();
        MyCommand.Fill(DtSet);

        OledbConn.Close();
        return DtSet.Tables[0];
      }
      catch (Exception ex)
      {
        throw ex;

      }
    }
  }
}

		
	 



Download the project of Visual Studio 2013 in DropBox Download


How to Read Sheet of Excel File into DataTable in C# Winforms


Video Thumbnail