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

This code will do; just don't bind the data into a "grvExcelData" (I guess that has to do something with the Grid view). Or even better; you could use the Microsoft.Office.Interop.Excel APIs — aquaraga
– aquaraga, Commented Apr 23, 2013 at 4:00
I believe he just want's to check on how to read excel files line by line and not reading the whole excel file and loading its value to the grid. — lem.mallari
– lem.mallari, Commented Apr 23, 2013 at 4:08

lem.mallari · Accepted Answer · 2014-05-06 07:31:54Z

18

Since Excel works with ranges you should first get the range of cells you would want to read. After that you can now browse through them using a 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 explanation on this code block can be found here.

edited May 6, 2014 at 7:31

answered Apr 23, 2013 at 4:07

lem.mallari

1,27512 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nick Over a year ago

Lem, can you please update the link you have provided...its not working anymore...Thank you

bunkerdive Over a year ago

Link is currently a 404.

Damith · Accepted Answer · 2013-04-23 04:07:35Z

7

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

answered Apr 23, 2013 at 4:07

Damith

63.2k13 gold badges106 silver badges154 bronze badges

Comments

Aljohn Yamaro · Accepted Answer · 2018-03-21 08:21:43Z

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

Paul · Accepted Answer · 2020-03-04 08:52:17Z

0

I tried the solution with OleDbConnection but it didn't work because I didn't have something installed. Then I found this solution here and it worked like a charm:

https://www.codeproject.com/Tips/801032/Csharp-How-To-Read-xlsx-Excel-File-With-Lines-of

There you can download a small file Excel.dll, add it to your project and browse the excel files cell by cell.

answered Mar 4, 2020 at 8:52

Paul

70010 silver badges15 bronze badges

Comments

Guilherme Bley · Accepted Answer · 2023-02-05 16:45:12Z

A simple way to convert data table to enumerable of an object is using Json methods.

Per example, with simple method, using only dotnet libraries, you can convert data table to list of especific objects.

        using System.Data;
    
        private static IEnumerable<T> ConvertDataTable<T>(DataTable dataTable)
        {
            if (dataTable is null ||
                !dataTable.AsEnumerable().Any())
            {
                return Enumerable.Empty<T>();
            }
    
            var data = dataTable.Rows.OfType<DataRow>()
                .Select(row => dataTable.Columns.OfType<DataColumn>()
                    .ToDictionary(col => col.ColumnName, c => row[c]));
            
            var jsonTextObject = System.Text.Json.JsonSerializer.Serialize(data);
    
            return System.Text.Json.JsonSerializer.Deserialize<IEnumerable<T>>(jsonTextObject)
                ?? Enumerable.Empty<T>();
        }

Collectives™ on Stack Overflow

read excel data line by line with c# .net

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related