WinForm Read Excel File and load data into DataGridView

In this post, I have shown how to read an Excel File using PIA(Primary InterOp Assemblies) and load its sheet data into a DataGridView Control. Microsoft.Office.InterOp.Excel namespace contains classes and interfaces to communicate with COM technology based Excel application.

Assumptions 

1. Data begins at cell A1. 
2. First row is header row.
3. There is no blank row in the data table.
4. Excel application is already installed to invoke it through C# client application.
5. Microsoft.Office.Interop.Excel namespace is used in the code. Add its Reference in the Visual Studio. 
6. Load button remains disabled unless and until excel file is not selected.

The image above shows Windows Form in which the user can select an Excel file by clicking the Select button.  By clicking the load button, user can load data of excel sheet in data grid view control. All the seats names of the excel file are displayed within the list box.  When the user selects a seat name and clicks on the Load button, the data for that excel sheet appears within the Datagridview control.

C# Code:

using System;
using System.Windows.Forms;
using MyExcel = Microsoft.Office.Interop.Excel;

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

        private void SelectButton_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Title = "Select file.";
            ofd.Filter = "Excel File(*.xls;*.xlsx)|*.xls;*.xlsx";
            ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                FilePathTextBox.Text = ofd.FileName;
                LoadButton.Enabled = true;
                ListSheets();
            }
            
        }

        private void ListSheets()
        {
            string xlfile = FilePathTextBox.Text;
            //create Excel App
            MyExcel.Application xlApp = new MyExcel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("Excel Application is not installed.");
                return;
            }
            xlApp.Visible = false;
            //create Workbook
            MyExcel.Workbook wkbk = xlApp.Workbooks.Open(xlfile);
            foreach (MyExcel.Worksheet sht in wkbk.Worksheets)
            {
                SheetListBox.Items.Add(sht.Name);
            }
            wkbk.Close(false);
            xlApp.Quit();
        }

        private void LoadButton_Click(object sender, EventArgs e)
        {
            string xlfile = FilePathTextBox.Text;
            //create Excel App
            MyExcel.Application xlApp = new MyExcel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("Excel Application is not installed.");
                return;
            }
            xlApp.Visible = true;
            //create Workbook
            MyExcel.Workbook wkbk = xlApp.Workbooks.Open(xlfile);
            MyExcel.Worksheet sht = new MyExcel.Worksheet();
            sht = wkbk.Worksheets[SheetListBox.SelectedItem.ToString()];
            sht.Activate();
            MyExcel.Range rng = sht.Range["A1"].CurrentRegion;
            int rows = rng.Rows.Count;
            int cols = rng.Columns.Count;
            DataGridView1.Columns.Clear();
            for (int c = 0; c < cols; c++)
            {
                string header1 = rng.Cells[1, c + 1].Value;
                DataGridView1.Columns.Add(header1.Trim(), header1);
            }
           
            for (int row = 0; row < rows; row++)
            {
                DataGridView1.Rows.Add();
                for (int col = 0; col < cols; col++)
                {
                    DataGridView1.Rows[row].Cells[col].Value = rng.Cells[row+1, col+1].Value;
                }
            }
            DataGridView1.Rows.RemoveAt(0);
            wkbk.Close(false);
            xlApp.Quit();
        }
    }
}

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

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