Tuesday, May 4, 2010

A generic excel file Importer / Reader - Converting Excel rows into Strongly typed entity

Its my blog post after a long time ,
Its simply a class it can be used to map Excel columns to an entity fields by adding an attribute to the property

Eg: An excel file with two columns Name and Phone Number
Our Entity is

public class ExcelItem
{
[ExcelColumnIndex(Index=0)]
public string Name {get;set;}
[ExcelColumnIndex(Index=1)]
public string Phone {get;set;}
}
To get List <ExcelItem> containg the excel rows, simply
Importer<ExcelItem> _Importer = new Importer<ExcelItem>();
List<ExcelItem> data=_Importer.ParseExcel("FILE PATH", "sheet1");


ExcelImporter.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.OleDb;
namespace ExcelImporter
{
//3-may-2010 Priyan R
public class Importer where T : new()
{
public List ParseExcel(string filePath,string sheetName)
{
string tempFile, connectionString, query;
OleDbConnection con = null;
OleDbDataReader reader = null;
OleDbCommand command = null;
//
connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
filePath + @";Extended Properties=""Excel 8.0;HDR=YES;""";
query = "SELECT * FROM [" + sheetName + "$]";
//
var ret = new List();
try
{
con = new OleDbConnection(connectionString);
con.Open();
command = new OleDbCommand(query, con);
reader = command.ExecuteReader();
while (reader.Read())
{
var entry = new T();
foreach (var property in entry.GetType().GetProperties())
{
var attributes = property.GetCustomAttributes(false);
var attribute = property.GetCustomAttributes(false)
.Where(p => p.GetType() == typeof(ExcelColumnIndex)).FirstOrDefault(); ;
if (attribute != null)
{
property.SetValue(entry, reader[((ExcelColumnIndex)attribute).Index].ToString(), null);
}
}
ret.Add(entry);
}


}
catch
{
throw;
}
finally
{
if (con != null && con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return ret;
}
}
public class ExcelColumnIndex : Attribute
{
public int Index { get; set; }
}
}



Download sample application


1 comment:

Tittu said...

Nice post

With Regards,
http://www.entescrap.com