How get the data from excel sheet

Question

Please share the code how get data from excel using c#.net framework.

Anoop Kumar Sharma · Answer

Hi Bala, You can use libaries such as ClosedXML to read the data from the excel. I already wrote an article in which you can see the step by step implementation of ClosedXML in ASP.Net MVC using C# .Net Framework. Please check the below article: https://www.c-sharpcorner.com/article/export-and-import-excel-file-using-closedxml-in-asp-net-mvc/ I hope this will help you.

Sachin Singh · Answer

follow this OleDbConnection x; protected void Page_Load(object sender, EventArgs e) { // Create the connection object string read = System.IO.Path.GetFullPath(Server.MapPath( "~/empdetail.xlsx" )); if (Path.GetExtension(read) == ".xls" ) { x = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + read + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"" ); } else if (Path.GetExtension(read) == ".xlsx" ) { x = new OleDbConnection(@ "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + read + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';" ); } try { // Open connection x.Open(); // Create OleDbCommand object and select data from worksheet Sheet1 OleDbCommand cmd = new OleDbCommand( "SELECT * FROM [Sheet1$]" , x); // Create new OleDbDataAdapter OleDbDataAdapter oleda = new OleDbDataAdapter(); oleda.SelectCommand = cmd; // Create a DataSet which will hold the data extracted from the worksheet. DataSet ds = new DataSet(); // Fill the DataSet from the data extracted from the worksheet. oleda.Fill(ds, "Employees" ); // Bind the data to the GridView GridView1.DataSource = ds.Tables[0].DefaultView; GridView1.DataBind(); } catch { } finally { // Close connection x.Close(); } }

Jignesh Kumar · Answer

Hi Bala, Please refer below articles which will help you out, https://www.c-sharpcorner.com/UploadFile/a20beb/get-data-from-single-sheet-of-excel-file-in-Asp-Net991/ https://www.aspsnippets.com/Articles/Read-Excel-File-data-in-ASPNet.aspx If you are using Windows application, https://www.c-sharpcorner.com/UploadFile/6b8651/read-excel-file-in-windows-application-using-C-Sharp/

Jayraj Chhaya · Answer

using System.Data; using System.Data.OleDb; namespace Data_Migration_Process_Creator { class Class1 { private DataTable GetDataTable(string sql, string connectionString) { DataTable dt = null; using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); using (OleDbCommand cmd = new OleDbCommand(sql, conn)) { using (OleDbDataReader rdr = cmd.ExecuteReader()) { dt.Load(rdr); return dt; } } } } private void GetExcel() { string fullPathToExcel = " "; //ie C:\Temp\YourExcel.xls string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=yes'", fullPathToExcel); DataTable dt = GetDataTable("SELECT * from [SheetName$]", connString); foreach (DataRow dr in dt.Rows) { //Do what you need to do with your data here } } } }

Leon D · Answer

You can use Free Spire.XLS library to read data from an Excel sheet. https://www.e-iceblue.com/Download/download-excel-for-net-free.html Read data in an Excel sheet to a DataTable in C#: Workbook workbook = new Workbook(); workbook.LoadFromFile(”Data.xlsx”); Worksheet sheet = workbook.Worksheets[0]; DataTable dt = sheet.ExportDataTable(); Read data of a specific cell in C#: Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; string value = sheet.Range[ "A3" ].Text;

Muhammad Imran Ansari · Answer

Check the following link: https://www.c-sharpcorner.com/UploadFile/6b8651/read-excel-file-in-windows-application-using-C-Sharp/