Reading Data From Excelsheet in DataGridView

[Binod Kumar1]

This code will read data from excelsheet in gridview......t ake 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,s heet3.. 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.Offic e.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();