WinForm Import Excel Sheet Data Into DataGridView Control in C#

NOTE:-

CODE:

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Windows.Forms;

namespace ExcelDataFetcherAppWF
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void ImportButton_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            ofd.Filter = "Excel Files(*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm";
            ofd.Title = "Select an Excel file";
            ofd.Multiselect = false;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                if (ofd.FileName != String.Empty)
                {
                    string file = ofd.FileName;
                    string extn = Path.GetExtension(file);
                    DataSourcePathTextBox.Text = file;
                    getExcelSheets(file);
                }
            }
        }

        private void getExcelSheets(string filepath)
        {
            //NOTE: put single quote around Extended Properties
            string cstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0; HDR = YES'";
            try
            {
                using (OleDbConnection conn = new OleDbConnection(cstring))
                {
                    if (conn == null)
                    {
                        MessageBox.Show("Null");
                        return;
                    }
                    conn.Open();
                    // Get the data table containg the schema guid.
                    DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr["TABLE_NAME"].ToString().IndexOf("$") > 0)
                        {
                            SheetComboBox.Items.Add(dr["TABLE_NAME"].ToString());
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void getExcelData(string filepath)
        {
            //NOTE: put single quote around Extended Properties
            string cstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+filepath+";Extended Properties='Excel 12.0; HDR = YES'";
            using (OleDbConnection conn = new OleDbConnection(cstring))
            {
                string cmdText = "SELECT * FROM [" + SheetComboBox.SelectedItem + "]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmdText, conn))
                {
                    DataTable dataTable1 = new DataTable();
                    conn.Open();
                    adapter.Fill(dataTable1);
                    ReportDataGridView.DataSource = dataTable1;
                }
            }
        }

        private void LoadDataButton_Click(object sender, EventArgs e)
        {
            string file = DataSourcePathTextBox.Text;
            getExcelData(file);
        }
    }
}

© अजीत कुमार, सर्वाधिकार सुरक्षित।

इस आलेख को उद्धृत करते हुए इस लेख के लिंक का भी विवरण दें। इस आलेख को कॉपीराइट सूचना के साथ यथावत साझा करने की अनुमति है। कृपया इसे ऐसे स्थान पर साझा न करें जहाँ इसे देखने के लिए शुल्क देना पडे।