How to Read Excel Data Using LINQ to ExcelProvider in .NET

Introduction

In this article I will show to how to read Excel work book data using LINQ to Excel provider in LINQ. The LINQ has many powerful ways to extend and access different type of data source. Let’s see how to implement this with Visual studio 2008 and .NET 3.5 Framework.

Implementation

Step 1

Let's create a simple excel sheet with few rows as looks like below,

Step 2

And then create a new console application using Visual studio 2008; project name is “LINQToExcel”. 

Step 3

Now add LINQToExcelProvider.cs in your project. You can download here Or I have included sample project end of this article you can download it.

Note:Thank SolidCoding

Step 4

If you are added LINQTOExcelProvider.cs, finally project structure looks like below, 

Step 5

Let's write code read the from source file and print screen.

C# Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LinqToExcel;

namespace LINQToExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            ExcelProvider provider = ExcelProvider.Create(@".......\LINQ\How to read Excel data usign LINQ Provider\Source.xls", "Sheet1");
            Console.WriteLine("{0}\t{1}\t{2}\t{3}", "ID", "Name", "Address1", "Address2");
            foreach (ExcelRow row in (from x in provider select x))
            {
                Console.WriteLine("{0}\t{1}\t{2}\t{3}", row.GetString(0),row.GetString(1),row.GetString(2),row.GetString(3));
            }

            Console.WriteLine("Done");
            Console.ReadLine();
        }
    }
}

Code Explanation

1.    The first line is creating object Provider using static Method for ExcelProvider.Create(). The Create method has to supply two arguments first one is source file path and second argument is sheet name.

ExcelProvider provider = ExcelProvider.Create(@".....\LINQ\How to read Excel data usign LINQ Provider\Source.xls", "Sheet1");

2.    Then I have print excel sheet data header in console using formatting

Console.WriteLine("{0}\t{1}\t{2}\t{3}", "ID", "Name", "Address1", "Address2");

3.    And then in the next line select data from the provider using LINQ and iterate each ExcelRow and print values from array in console

foreach (ExcelRow row in (from x in provider select x))
            {
                Console.WriteLine("{0}\t{1}\t{2}\t{3}", row.GetString(0),row.GetString(1),row.GetString(2),row.GetString(3));
            }

4.    Finally print done for indicates successfully finished.

Step 6

Now run application and see output will be like followings, 

Download Sample Project

Download source files -28 kb

Conclusion

Through this article you have learned how to read Excel work book data using LINQ to Excel Provider. This is made easy to access or read excel sheet data easily in .NET. Hopes help and thank you for reading.