get data from excel using linq .net

You might want to read excel file and import the data from the excel into your .net application. Therefore you need a service that provide you this functionality. Here is the class that you can use it.

I suggest you to import the data and store into a DataTable and from there you can start to manipulate it. You can also use a linq to query the data. Is that simple and easy?

Initialization

PrivatePropertyExcel ppe;
DataTable currentExcelDataTable;

private void btnTestConnection_Click(object sender, EventArgs e)
{
try
{
if (openFileDialog1.ShowDialog() == DialogResult.OK && txtSheetName.Text != "")
{

ppe = new PrivatePropertyExcel(openFileDialog1.FileName, txtSheetName.Text);
currentExcelDataTable = ppe.ExcelProvider.GetDataTable();
gridSource.DataSource = currentExcelDataTable;
gridTarget.DataSource = null;


btnValidate.Enabled = true;
txtStatus.Text = "";
}
else
{
if (txtSheetName.Text == "")
{
MessageBox.Show("Please enter sheet name");
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}

Calling function

public List<string> GetPropertyType(int columnIndex)
{
List<string> propertyTypeList = new List<string>();

var propertyTypes =
(from e in ppe.ExcelProvider
where e.GetString(columnIndex).Trim() != ""
select e.GetString(columnIndex).Trim()).Distinct();

foreach (string pt in propertyTypes)
{
if (!propertyTypeList.Contains(pt))
{
propertyTypeList.Add(pt);
}
}

return propertyTypeList;
}

Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Collections;
using System.Data;

namespace ReplicatorServices
{
public class ExcelRow
{
List<object> columns;

public ExcelRow()
{
columns = new List<object>();
}

internal void AddColumn(object value)
{
columns.Add(value);
}

public object this[int index]
{
get { return columns[index]; }
}

public string GetString(int index)
{
if (columns[index] is DBNull)
{
return null;
}
return columns[index].ToString();
}

public int Count
{
get { return this.columns.Count; }
}
}

public class ExcelProvider:IEnumerable<ExcelRow>
{
private string sheet;
private string filePath;
private List<ExcelRow> rows;
private string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties= ""Excel 8.0;HDR=YES;""";

public ExcelProvider()
{
rows = new List<ExcelRow>();
}

public static ExcelProvider Create(string filePath, string sheet)
{
ExcelProvider provider = new ExcelProvider();
provider.sheet = sheet;
provider.filePath = filePath;
return provider;
}

public DataTable GetDataTable()
{
DataTable dt= new DataTable();
connectionString = string.Format(connectionString, filePath);
rows.Clear();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select DISTINCT * from [" + sheet + "$] ";
using (OleDbDataReader reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
}
}

return dt;
}

public DataTable GetDataTable(string sqlCommand)
{
DataTable dt = new DataTable();
connectionString = string.Format(connectionString, filePath);
rows.Clear();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqlCommand;
using (OleDbDataReader reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
}
}

return dt;
}
public List<string> GetColumnsName()
{
List<string> columnlist = new List<string>();
connectionString = string.Format(connectionString, filePath);
rows.Clear();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from [" + sheet + "$]";
using (OleDbDataReader reader = cmd.ExecuteReader())
{
for (int i = 0; i < reader.FieldCount; i++)
{
columnlist.Add(reader.GetName(i).Trim());
}
}
}
}

return columnlist;
}

private void Load()
{
connectionString = string.Format(connectionString, filePath);
rows.Clear();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from [" + sheet + "$]";
using (OleDbDataReader reader = cmd.ExecuteReader())
{

while (reader.Read())
{
ExcelRow newRow = new ExcelRow();
for(int count = 0; count < reader.FieldCount; count++) {
newRow.AddColumn(reader[count]);
}
rows.Add(newRow);
}
}
}
}
}

public IEnumerator<ExcelRow> GetEnumerator()
{
Load();
return rows.GetEnumerator();
}

IEnumerator IEnumerable.GetEnumerator()
{
Load();
return rows.GetEnumerator();
}

}
}