Read Excel files in .NET – The DataReader way

Introduction

A few years ago, an application I was working had a requirement to import an excel spreadsheet into SQL.  I had written tons of office interop code in Delphi and C#, but all of them always created spreadsheets from data, never really the other way around.

My 1st stab at doing it (the lazy way), was to create a SQL 2000 DTS package which imported the spreadsheet into a SQL Table and then executed the package from code (SQL 2000 implementation).  This needed the DTS libraries installed on each of the target machines the application would be used on as well as special DB privileges for each of the users allowing them to execute the DTS package.  Not ideal.

So when I got around to converting the system from Delphi to .NET, I decided to give the Excel reading thing a go. I mean, .NET is awesome….right?  It must have some really cool libraries for doing this kind of thing….and I have also never really been a big fan of using COM / Office interop for working with Office either and an Excel worksheet looks just like a SQL table too doesn’t it?

So I wanted to read the excel sheet like i would when using a DataReader.

      
  var recordList = new List<MyRecordStructure>();

  using (var reader = db.ExecuteReader(System.Data.CommandType.Text, 
      "Select * from SomeTable"))
  {
    while (reader.Read())
    {
      var rec = new MyRecordStructure()
      {
        Field1 = DBHelper.GetDecimal(reader, "ActualPrice", 0),
        Field2 = DBHelper.GetDecimal(reader, "ModelVestPrice", 0),
        Field3 = DBHelper.GetDecimal(reader, "ModelPrice", 0),
      };
      recordList.Add(rec);
    }
  }

After some google searching, I came across this awesome little library called Excel Data Reader which can be downloaded here.

 

Using the Code

First of all, you need to define your class which maps to your spreadsheet.

Id	Code	Date	Price	Discount
1	ABC	01-Mar-03	100.49	10.49
2	DEF	01-Mar-03	100.49	10.49
3	GHI	01-Apr-03	100.74	10.74
4	JKL	01-Apr-03	100.74	10.74
5	MNO	01-May-03	100.99	10.99

  

  ///<summary>
  /// Maps to the actual record in the excel sheet    
  ///</summary>
  public class MyRecordStructure   
  {
    public int Id { get; set; }
    public string Code { get; set; }
    public DateTime Date { get; set; }
    public Decimal Price { get; set; }
    public Decimal Discount { get; set; }
  }

Now all you need is a Stream with an Excel file:

 
  FileStream stream = File.Open(@"C:\File.xlsx", FileMode.Open, FileAccess.Read);

Then you add your boilerplate DataReader code and let the Excel Data Reader library perform all the magic (please remember to add a reference to the library you have downloaded)

 
  var recordList = new List<MyRecordStructure>();

  using (var reader = ExcelReaderFactory.CreateOpenXmlReader(stream))
  {
    while (reader.Read())
    {
      if (skipped)
      {
        var rec = new MyRecordStructure ()
        {
          Id = reader[0].ToInt(),
          Code = reader[1].ToString(),
          Date = reader[2].ToDateTime(),
          Price = reader[3].ToDecimal(),
          Discount = reader[4].ToDecimal()
        };
        recordList.Add(rec);
      }
    }
  }

Conclusion

So if you pressed for time and need to read data from an Excel sheet, try the Excel Data Reader library as it is really easy to use and Excel is not required on the Target machine.  It also supports reading excel files from ’97 – 2007.