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.

Read more

Read Excel File Using c#

In this post we will tell you  how to read excel file using c#.

There are few step that should follow to read  a file. For this operation ,firstly we have to know about the area that has been used. For this purpose we will use “UsedRange”  property of the worksheet class. This will return last cell of the used area. And also most important method  “xlapp.workbook.open()”  for opening the excel file.

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 in the form as shown below:-

Step 4. Double click read button and paste below given coding:-

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;

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

            {

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

                {

                    str =Convert.ToString((range.Cells[rowcount, colcount] as Excel.Range).Value2);

                    MessageBox.Show(str);

                }

            }

            xlWorkBook.Close(true, null, null);

            xlApp.Quit();

            release(xlWorkSheet);

            release(xlWorkBook);

            release(xlApp);

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 your file’s cell will read one by one.

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

Result will be shown as:-

 Thank for reading post.
Enjoy

Read more