Show Data Of Excel File In The DataGridView

In this post , we will tell you how to show the data of excel file in the DataGridView.

There are a few steps that should be followed to show the data in DataGridView. In this, we will use new class DataTable. Firstly we store data in datatable and than show in the DataGridView by datatable.

Basically datatable is class that can store data in tabular form.

Step 1. Open visual stdio and open Window Form Application.

Stpep2 . Add reference to Micosoft library 12.0 object library. (Read more)

Step 3. Make a new form and add a button and dataGridView in the form as shown below:-

Step 4.  Double click “Read Data From Excel  file” button and paste the coding given below:-

Excel.Application xlApp;

            Excel.Workbook xlWorkBook;

            Excel.Worksheet xlWorkSheet;

            Excel.Range range;

            string str;

            int rowcount = 0;

            int colcount = 0;

            xlApp = new Excel.Application();

            xlWorkBook = xlApp.Workbooks.Open("create-Excel.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            range = xlWorkSheet.UsedRange;

         int   colcount1 = range.Columns.Count;

            string[] st = new string[colcount1 + 1];

  ////////////////////////////////////////////////////DATA STORING IN THE DATATABLE FROM EXCEL FILE ///////////////////////////

            DataTable dt = newDataTable();

            for (rowcount = 0; rowcount < range.Rows.Count; rowcount++)

            {

               

                for (colcount = 0; colcount < range.Columns.Count; colcount++)

                {

                    str = Convert.ToString((range.Cells[rowcount+1, colcount+1]).Value);

                    st[colcount] = str;

                    if (rowcount == 0)

                    {

                      

                        dt.Columns.Add(st[colcount], typeof(string));

                    }

    

                     

                }

                if(rowcount!=0)

                    dt.Rows.Add(st[0], st[1], st[2], st[3], st[4]);

              

            }

            ////////////////////////////////////////////////END OF THE SNIPPET////////////////

            xlWorkBook.Close(true, null, null);

            xlApp.Quit();

            release(xlWorkSheet);

            release(xlWorkBook);

            release(xlApp);

            dataGridView1.DataSource = dt;

Step 5. Now make a new class named as release as shown below:-

  private void release(object obj)

        {

            try

            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);

                obj = null;

            }

            catch (Exception ex)

            {

                obj = null;

                MessageBox.Show("Unable to release the Object " + ex.ToString());

            }

            finally

            {

                GC.Collect();

            }

        } 

Step 6. Now run project  and click “read data From Excel File” button, Result will be shown as below:-

Note. A reference should be added (using Excel=Microsoft.Office.Interop.Excel)

Thanks for reading post
Enjoy.