(Visual Studio 2010) How To Import Data From Excel Sheet To DataGridView in C# Windows Application – Microsoft Excel 2010

(Visual Studio 2010) How To Import Data From Excel Sheet To DataGridView in C# Windows Application – Microsoft Excel 2010

This guide shows How To Import Data From Excel Sheet To DataGridView in C# Windows Application. All Excel rows are imported to DataGridView when you click import button.

First you need to import .NET excel library (microsoft.office.interop.excel).

To Add (microsoft.office.interop.excel) Reference follow the following steps :

Step 1. Right-click on “References” and select “Add Reference”.

Step 2. Select the “.NET” tab.

Step 3. Look for Microsoft.Office.Interop.Excel. Select it and click okay.

Source Code

            _Application importExcelToDataGridViewAppl;
            _Workbook importExcelToDataGridViewWorkbook;
            _Worksheet importExcelToDataGridViewWorksheet;
            Range importExcelToDataGridViewRange;
            try
            {
                importExcelToDataGridViewAppl = new Microsoft.Office.Interop.Excel.Application();
                OpenFileDialog importExcelToDataGridViewFileDialog = new OpenFileDialog();
                //Filter Excel Files Only
                importExcelToDataGridViewFileDialog.Filter = "Excel Files | *.xls;*.xlsx;*.xlsm";
                //Dialog Title
                importExcelToDataGridViewFileDialog.Title = "Import Excel File";
                if (importExcelToDataGridViewFileDialog.ShowDialog() == DialogResult.OK)
                {
                    importExcelToDataGridViewWorkbook = importExcelToDataGridViewAppl.Workbooks.Open(importExcelToDataGridViewFileDialog.FileName);
                    importExcelToDataGridViewWorksheet = importExcelToDataGridViewWorkbook.Sheets.get_Item(1);
                    importExcelToDataGridViewRange = importExcelToDataGridViewWorksheet.UsedRange;

                    //Get Excel Rows
                    for (int excelRows = 2; excelRows <= importExcelToDataGridViewRange.Rows.Count; excelRows++)
                    {
                        //Get image path from fifth column
                        //Image is also converted to byte array using a method "imageToByteArray(img)"
                        Image img = Image.FromFile(importExcelToDataGridViewRange.Cells[excelRows, 5].value);
                        dataGridView1.Rows.Add(importExcelToDataGridViewRange.Cells[excelRows, 1].value, importExcelToDataGridViewRange.Cells[excelRows, 2].value, importExcelToDataGridViewRange.Cells[excelRows, 3].value, importExcelToDataGridViewRange.Cells[excelRows, 4].value, importExcelToDataGridViewRange.Cells[excelRows, 5].value, imageToByteArray(img));
                    }
                }

            }
            catch (Exception exError)
            {
                MessageBox.Show(exError.StackTrace);

            }
            finally {
                //importExcelToDataGridViewWorkbook.Close();
                //importExcelToDataGridViewAppl.Quit();
            }