Read Excel data line by line using C # .net

Question

Does anyone know how I can read an Excel file line by line in C #.

I found this code that will return data from Excel and display grindview in C #. However, I just wandered around, how to read the data line by line on the server side instead?

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.OleDb; using System.IO; namespace site { public partial class pgTest : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnImport_Click(object sender, EventArgs e) { string connString = ""; string strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower(); string path = fileuploadExcel.PostedFile.FileName; //Connection String to Excel Workbook if (strFileType.Trim() == ".xls") { connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (strFileType.Trim() == ".xlsx") { connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } string query = "SELECT [username],[age],[phone] FROM [Sheet1$]"; OleDbConnection conn = new OleDbConnection(connString); if (conn.State == ConnectionState.Closed) conn.Open(); OleDbCommand cmd = new OleDbCommand(query, conn); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); grvExcelData.DataSource = ds.Tables[0]; grvExcelData.DataBind(); da.Dispose(); conn.Close(); conn.Dispose(); } } } 

Answer 1

you can use OleDbDataReader as below

 using (OleDbConnection connection = new OleDbConnection(connectionString)) { OleDbCommand command = new OleDbCommand(queryString, connection); connection.Open(); OleDbDataReader reader = command.ExecuteReader(); while (reader.Read()) { var val1= reader[0].ToString(); } reader.Close(); } 

Answer 2

Since Excel works with ranges, you must first get the range of cells that you want to read. After that, you can view them using the for loop. You can see an example below:

  Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\myexcel.xlsx"); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; for (int i = 1; i <= rowCount; i++) { for (int j = 1; j <= colCount; j++) { MessageBox.Show(xlRange.Cells[i, j].Value2.ToString()); } } 

A more detailed description of this code block can be found here .

Answer 3

You have to try this

  string connectionString = ""; string strFileType = "Type"; string path = @"C:\Users\UserName\Downloads\"; string filename = "filename.xls"; if (fielname.Contains(.xls)) { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + filename + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (fielname.Contains(.xlsx) { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + filename + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } string query = "SELECT * FROM [SheetName$]"; using (OleDbConnection connection = new OleDbConnection(connectionString)) { OleDbCommand command = new OleDbCommand(query, connection); connection.Open(); OleDbDataReader reader = command.ExecuteReader(); var lines = new List<string>(); while (reader.Read()) { var fieldCount = reader.FieldCount; var fieldIncrementor = 1; var fields = new List<string>(); while (fieldCount >= fieldIncrementor) { fields.Add(reader[fieldIncrementor - 1].ToString()); fieldIncrementor++; } lines.Add(string.Join("\t", fields)); } reader.Close(); }