Reading Excel Files in .net Using LinqToExcel

planetsI’m sure at some point you’ve needed to read some Excel files in an application. You’ve probably even used the Office Interop libraries (using something like: new Excel.Application();). Below I’ll show a much easier way that makes use of a data model to read an Excel file using LinqToExcel. If you’re looking for the complete code, scroll to the end of the page.

  1. Start a new solutions / project. In my example I’m just using a console app.
  2. Make sure you save your solution before running step 3, or you’ll get a “The current environment doesn’t have a solution open.” error.
  3. In the Package Manager Console, at the “PM>” prompt, enter “Install-Package LinqToExcel” and press enter. When complete, it should read: “Successfully added ‘LinqToExcel 1.10.1’ to…”
  4. Add these lines to the very top of your code (if not already there):
    using System;
    using System.Linq;
    using LinqToExcel;

    using LinqToExcel.Attributes;
  5. Add your data model. I’m using an Excel spreadsheet that contains the planets sizes and masses (taken from this sweet NASA page). Why? Because it’s really cool.
    xls1
    My model looks like:

    internal class Planet
     {
     [ExcelColumn("Planetary Body")]
     public string Name { get; set; }
    
    [ExcelColumn("Mass (1024kg)")]
     public double Mass { get; set; }
    
    [ExcelColumn("Diameter (km)")]
     public int Diameter { get; set; }
     }
  6. Notice how the ExcelColumn attribute contains the formatted name of the column (with spaces, etc.). Make sure these match to your Excel columns.
  7. Now in your Main method, let’s add some code to read the Excel file (replacing the below w/ your file path, model name and sheet name (“Planets” in the below):
    var excel = new ExcelQueryFactory(@"F:\cbitting\Planets.xlsx")
    {
     DatabaseEngine = LinqToExcel.Domain.DatabaseEngine.Ace,
     TrimSpaces = LinqToExcel.Query.TrimSpacesType.Both,
     UsePersistentConnection = true,
     ReadOnly = true
     };
    var planets = from p in excel.Worksheet<Planet>("Planets")
     select p;
  8. Now let’s add something to see what data this retrieved:
    foreach (Planet planet in planets)
     {
     Console.WriteLine(planet.Name + " Mass: " + planet.Mass);
     }
    Console.ReadLine();
  9. Your results should be the data in your Excel file.

You can filter the data in the Linq statement using something like:

var planets = from p in excel.Worksheet<Planet>("Planets")
 where p.Diameter > 5000
 select p;

Or order the data using:

 foreach (Planet planet in planets.OrderByDescending(p => p.Diameter))

This is a very simple example of this powerful method of reading Excel spreadsheets. https://github.com/paulyoder/LinqToExcel has many more examples and documentation.

If you’re looking to use this on a machine without Office installed, I believe you need https://www.microsoft.com/en-au/download/details.aspx?id=13255 to enable ACE.

Below is the entire code from this example:

using System;
using System.Linq;
using LinqToExcel;
using LinqToExcel.Attributes;

namespace cSharpExcelRead
{
    internal class Planet
    {
        [ExcelColumn("Planetary Body")]
        public string Name { get; set; }

        [ExcelColumn("Mass (1024kg)")]
        public double Mass { get; set; }

        [ExcelColumn("Diameter (km)")]
        public int Diameter { get; set; }
    }

    internal class Program
    {
        private static void Main(string[] args)
        {
            var excel = new ExcelQueryFactory(@"F:\cbitting\Planets.xlsx")

            {
                DatabaseEngine = LinqToExcel.Domain.DatabaseEngine.Ace,
                TrimSpaces = LinqToExcel.Query.TrimSpacesType.Both,
                UsePersistentConnection = true,
                ReadOnly = true
            };

            var planets = from p in excel.Worksheet<Planet>("Planets")
                          select p;

            foreach (Planet planet in planets.OrderByDescending(p => p.Diameter))
            {
                Console.WriteLine(planet.Name + " Mass: " + planet.Mass);
            }

            Console.ReadLine();
        }
    }
}
Reading Excel Files in .net Using LinqToExcel

4 thoughts on “Reading Excel Files in .net Using LinqToExcel

  1. Hiren Nayee says:

    hi i am trying to import excel data using LinqToExcel in my asp.net mvc project but i am importing the one excel.excel have the 2 column 1 is Itemcode and 2 is Quantity.Quantity column store the one row data is 0.8 then upload excel value is replace “0.8” to “0”. not getting propare value.this is below code i am used.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s