Reading Data From Excelsheet in DataGridView

[Binod Kumar1]

This code will read data from excelsheet in gridview......take a submit button and take a datagrid....and use below code...its works fine....

private void btnsubmit_Click(object sender, EventArgs e)
        {
 
                Excel.Application appExl;
                Excel.Workbook workbook;
                Excel.Worksheet NwSheet;
                Excel.Range ShtRange;
                appExl = new Excel.ApplicationClass();
                workbook = appExl.Workbooks.Open(("C:\\data.xlsx"), Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
 
                int Cnum = 0;
                int Rnum = 0;
 
                ShtRange = NwSheet.UsedRange;
                DataTable dt = new DataTable();
                dt.Columns.Add("name");
                dt.Columns.Add("address");
               // dt.Columns.Add("Status");
                dt.Columns.Add("Phone");
                for (Rnum = 1; Rnum <= ShtRange.Rows.Count; Rnum++)
                {
                    DataRow dr = dt.NewRow();
                    for (Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
                    {
                        dr[Cnum - 1] = (ShtRange.Cells[Rnum, Cnum] as Excel.Range).Value2.ToString();
                    }
                    dt.Rows.Add(dr);
                    dt.AcceptChanges();
                }
                workbook.Close(true, Missing.Value, Missing.Value);
                appExl.Quit();
 
 
                //Session["data"] = dt;
                dataGridView1 .DataSource = dt;
                //dataGridView1.DataBind();
 
            }

[ranjithrajr]

hi buddy.. yours code works good.. U r awesome.. But i need to know one more thing.. pls help.. how to display contents of different sheets in excel file.. for example my excel file contains sheet1,sheet2,sheet3.. i need to display contents of any sheet i want by selecting the desired sheet.

[SioSio]

This code can select a sheet.
Set two buttons, one Combobox and one DatagridView on the form.
Click Button1 to set the sheet name of the excel file in the Combobox1.
Select a sheet from the combobox1 and click Button2, and the values of the selected sheet will be displayed in the DatagridView.

public static class Global
    {
        public static string excelName;
    }
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
            Global.excelName = System.AppDomain.CurrentDomain.BaseDirectory + "\\sample.xlsx";
            dataGridView1.AutoGenerateColumns = false;
        }
 
        void Button1Click(object sender, EventArgs e)
        {
            Excel.Application mExcel;
            mExcel = new Excel.Application();
            try
            {
                Excel.Workbook mWorkbook;
                mExcel.Visible =false;
                // excel open
                mWorkbook = (Excel.Workbook)(mExcel.Workbooks.Open(
                    Global.excelName
                ));
                try
                {
                    foreach (Microsoft.Office.Interop.Excel.Worksheet sh in mWorkbook.Sheets)
                    {
                        //ComboBox
                        comboBox1.Items.Add(sh.Name);
                    }
                    comboBox1.SelectedIndex = 0;
                }
                finally
                {
                    // close book
                    mWorkbook.Close(false);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(mWorkbook);
                }
            }
            finally
            {
                if (null != mExcel)
                {
                    try
                    {
                        mExcel.DisplayAlerts = false;
                        mExcel.Quit();
                    }
                    finally
                    {
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel);
                    }
                }
            }
        }
 
        // set to datagridview from excel sheet
        void Button2Click(object sender, EventArgs e)
        {
            // application object
            Excel.Application mExcel;
            // create instance
            mExcel = new Excel.Application();
            try
            {
                // book object
                Excel.Workbook mWorkbook;
                // No display Excel
                mExcel.Visible = false;
                // open
                mWorkbook = (Excel.Workbook)(mExcel.Workbooks.Open(
                    Global.excelName
                ));
                try
                {
                    Excel.Worksheet SheetSample; // Worksheet object
                    SheetSample = (Excel.Worksheet)mWorkbook.Sheets[comboBox1.SelectedIndex+1];
                    try
                    {
                        var rowCount = SheetSample.UsedRange.Rows.Count;
                        var columnCount = SheetSample.UsedRange.Columns.Count;
                        dataGridView1.ColumnCount = columnCount;
                        Microsoft.Office.Interop.Excel.Range xlCells = null;
                        Microsoft.Office.Interop.Excel.Range xlRange = null;
                        xlCells = SheetSample.Cells;
                        try
                        {
                            for(int c = 1; c <= columnCount; c++)
                            {
                                xlRange = (Microsoft.Office.Interop.Excel.Range)xlCells[1, c];
                                dataGridView1.Columns[c-1].HeaderText = Convert.ToString(xlRange.Value2);
                            }
                            for(int r = 2; r <= rowCount; r++)
                            {
                                string[] stringArray = new string[columnCount];
                                for(int c = 1; c <= columnCount; c++)
                                {
                                    xlRange = (Microsoft.Office.Interop.Excel.Range)xlCells[r, c];
                                    stringArray[c-1] = Convert.ToString(xlRange.Value2);
                                }
                                dataGridView1.Rows.Add(stringArray);
                            }
                        }
                        finally
                        {
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlCells);
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange);
                        }
                    }
                    finally
                    {
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(SheetSample);
                    }
                }
                finally
                {
                    // close the book
                    mWorkbook.Close(false);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(mWorkbook);
                }
            }
            finally
            {
                if (null != mExcel)
                {
                    try
                    {
                        mExcel.DisplayAlerts = false;
                        mExcel.Quit();
                    }
                    finally
                    {
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel);
                    }
                }
            }
        }
    }

[Cezar]

I used Spire.XLS for .NET to expert data from Excel sheet to DataGridView, and it worked like a charm.

Compared with Microsoft.Office.Interop.Excel, the biggest advantage of Spire.XLS is that there is no need to installed MS Office on computers.

//Create a new workbook
Workbook workbook = new Workbook();       
//Load an excel file
workbook.LoadFromFile(@”C:\Users\Administrator\Desktop\data.xlsx”);          
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Export data from excel to datagridview 
this.dataGridView1.DataSource = sheet.ExportDataTable();