read excel data line by line with c# .net

Question

Does anyone know how can I read an excel file line by line in c#.

I found this code which will return the data from excel and display a grindview in c#. However, I just was wandering how to possibly 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

You must 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();
        }