Merhaba arkadaşlar,
Sizlere Excel üzerinden LINQ ile veri okuyabilmemizi sağlayan LinqToExcel paketini örneklerle açıklamaya çalışacağım.
Kurulum için NuGet Package Manager üzerinden paketi indirebilirsiniz.
Örnek excel için Northwind veritabanında yer alan Products tablosundaki verileri kullanmaktayım. Excel içeriği aşağıdaki gibidir.
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.' Eğer bu hatayı alıyorsanız bilgisayarınıza Access Database Engine kurulumunu yapmanız gerekmektedir. Kurulum için aşağıdaki adresi ziyaret edebilirsiniz.
Access Database EngineÇalışma Sayfasındaki Verileri Sorgulama
İlk örneğimizde excel üzerinden yapılacak sorgulamaya göre sonuçlar Product sınıfına aktarılacaktır. Eğer Worksheet için bir isim belirtilmezse, varsayılan olarak Sheet1 değerine göre sorgulama yapacaktır. Bu şekilde çalıştırdığımızda bir hata ile karşılacağız.
using System;
using System.IO;
using System.Linq;
namespace LinqToExcel.Sample
{
class Program
{
static void Main(string[] args)
{
string filePath = Path.Combine(Environment.CurrentDirectory, "Data.xlsx");
var excel = new ExcelQueryFactory(filePath);
var products = from c in excel.Worksheet< Product>()
select c;
foreach (var product in products)
{
Console.WriteLine($"{product.ProductID} - {product.ProductName}");
}
Console.ReadLine();
}
}
public class Product
{
public int ProductID { get; set; }
public string ProductName { get; set; }
public int? SupplierID { get; set; }
public int? CategoryID { get; set; }
public string QuantityPerUnit { get; set; }
public decimal? UnitPrice { get; set; }
public short? UnitsInStock { get; set; }
public short? UnitsOnOrder { get; set; }
public short? ReorderLevel { get; set; }
public bool Discontinued { get; set; }
public int? Sample { get; set; }
public bool? IsTrue { get; set; }
}
}
Yukarıda söylediğim gibi, eğer çalışma sayfasının adını belirtmezsek, varsayılan olarak Sheet1 isimli bir sayfayı bulmaya çalışacaktır. Türkçe sürüm kullandığım için Sayfa1 isimli çalışma sayfasını dikkate almayacaktır. Worksheet parametresi olarak Safya1 değerini yazabiliriz, ama biz data ile uyumlu olması için Excel sayfa adını Products olarak değiştirelim ve uygulamadan worksheetName parametresini girerek çağıralım.
static void Main(string[] args)
{
string filePath = Path.Combine(Environment.CurrentDirectory, "Data.xlsx");
var excel = new ExcelQueryFactory(filePath);
var products = from c in excel.Worksheet< Product>("Products")
select c;
foreach (var product in products)
{
Console.WriteLine($"{product.ProductID} - {product.ProductName}");
}
Console.ReadLine();
}
Property İsimlerini Excel Kolon İsimleri İle Eşleştirmek
Excelde yer alan kolon isimleri, sizin sınıfınızda bulunan property isimleri ile uyum sağlamayabilir. Bu gibi bir durumda ExcelQueryFactory içerisinde yer alan AddMapping metodunu kullanarak eşleştirme yapabiliriz. İki kullanım şeklide aşağıdaki gibidir.
class Program
{
static void Main(string[] args)
{
string filePath = Path.Combine(Environment.CurrentDirectory, "Data.xlsx");
var excel = new ExcelQueryFactory(filePath);
excel.AddMapping< Product>(x => x.ID, "ProductID");
//excel.AddMapping("ID", "ProductID");
var products = from c in excel.Worksheet< Product>("Products")
select c;
foreach (var product in products)
{
Console.WriteLine($"{product.ID} - {product.ProductName}");
}
Console.ReadLine();
}
}
public class Product
{
public int ID { get; set; }
public string ProductName { get; set; }
public int? SupplierID { get; set; }
public int? CategoryID { get; set; }
public string QuantityPerUnit { get; set; }
public decimal? UnitPrice { get; set; }
public short? UnitsInStock { get; set; }
public short? UnitsOnOrder { get; set; }
public short? ReorderLevel { get; set; }
public bool Discontinued { get; set; }
public int? Sample { get; set; }
public bool? IsTrue { get; set; }
}
Aynı işlemi AddMapping metodunu kullanmak yerine Property için Attribute kullanarakta yapabiliriz. ExcelColumn Attribute ü AddMapping ile aynı amaca hizmet etmektedir.
public class Product
{
[ExcelColumn("ProductID")]
public int ID { get; set; }
}
LinqToExcel.Row Sınıfının Kullanımı
Worksheet metodu generic bir argument almaktadır. Eğer bir tip parametresi verilmezse, sonuçlar Row sınıfına set edilecektir. DataRow ile benzer şekilde çalışmaktadır. Erişmek istenilen kolon için kolon ismi ya da kolon index i kullanılabilir.
static void Main(string[] args)
{
string filePath = Path.Combine(Environment.CurrentDirectory, "Data.xlsx");
var excel = new ExcelQueryFactory(filePath);
var products = from c in excel.Worksheet("Products")
where c["CategoryID"] == "2"
select c;
foreach (var product in products)
{
Console.WriteLine($"{product["ProductID"]} - {product["ProductName"]}");
}
Console.ReadLine();
}
UnitPrice değeri 10 dan büyük olan kayıtları sorgulamak istediğimde eşitliğin sol tarafı string olduğu için hata alınacaktır. Bu gibi bir durumda Cast metodunu kullanarak tip dönüşümü yapabiliriz.
var excel = new ExcelQueryFactory(filePath);
var products = from c in excel.Worksheet("Products")
where c["UnitPrice"].Cast< int>() > 10
select c;
Başlığı Olmayan Excel Sayfasını Sorgulamak
Bunun için WorksheetNoHeader metodu kullanılmaktadır. Erişmek istenilen kolon için index numarası belirtilmelidir.
static void Main(string[] args)
{
string filePath = Path.Combine(Environment.CurrentDirectory, "Data.xlsx");
var excel = new ExcelQueryFactory(filePath);
var products = from c in excel.WorksheetNoHeader("Products")
where c[5].Cast< int>() > 10
select c;
foreach (var product in products)
{
Console.WriteLine($"{product[0]} - {product[1]}");
}
Console.ReadLine();
}
Bir Çalışma Sayfasındaki Belirli Bir Aralığı Sorgulamak
Aşağıdaki örnek görseldeki gibi sadece seçili alandaki dataları sorgulamak istediğimizde bu yönteki kullanabiliriz.
static void Main(string[] args)
{
string filePath = Path.Combine(Environment.CurrentDirectory, "Data.xlsx");
var excel = new ExcelQueryFactory(filePath);
var products = from c in excel.WorksheetRange< Product>("A1", "F12", "Products")
select c;
foreach (var product in products)
{
Console.WriteLine($"{product.ID} - {product.ProductName}");
}
Console.ReadLine();
}
Birde benzer işlemi yapan WorksheetRangeNoHeader metodu bulunmaktadır. Başlığı olmayan exceller için kullanılabilir. Generic bir tip parametresi almaz, sonuçları RowNoHeader modeli ile geri döner.
var products = from c in excel.WorksheetRangeNoHeader("A1", "F12", "Products")
select c;
Index Numarasına Göre Çalışma Sayfasını Sorgulamak
Excel içerisine 2 tane yeni sayfa ekledim. Sayfa görüntüleri aşağıdaki gibidir. Customer modelinin içeriğini worksheetIndex belirterek dolduralım. Bunun için Worksheet metodunu kullanacağız. Dikkat edilmesi gereken kısım, index sayfaların mevcuttaki dizilimine göre çalışmamaktadır. Öncelikle sayfa isimlerini alfabetik olarak sıralamakta, sıralama sonucuna göre verilen indexe karşılık gelen sayfanın datasını doldurmaktadır.
class Program
{
static void Main(string[] args)
{
string filePath = Path.Combine(Environment.CurrentDirectory, "Data.xlsx");
var excel = new ExcelQueryFactory(filePath);
var customers = from c in excel.Worksheet< Customer>(0)
select c;
}
}
public class Customer
{
[ExcelColumn("CustomerID")]
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
Index numarası 0 olan yani "ABC Customers" sayfasındaki datalar yüklenecektir. 1 dediğimizde "Customers" sayfasına ait datalar gelecektir. Index numarasını 2 olarak verdiğimizde, Generic olarak belirtilen tip içerisinde kolon - property ismi eşleşen bir değer varsa o değerleri set edecektir. Sayfadaki tüm kolonlar ile modelin tüm property lerinin eşleşmesi gibi bir zorunluluk yoktur.
Dönüşüm Kontrolleri
Customer sayfasına örnekteki gibi IsActive kolonunu ekledim. Bu kolon içerisinde; Y = true, N = false değerini ifade etmektedir. Normal yapıda bu datayı modele basmak için string ya da char tipinde bir property kullanarak öncelikle datayı doldurur, sonrasında kontrol yapıları ile true - false olduğuna karar verebiliriz. Ya da aşağıdaki gibi bir yöntemle bool tipindeki bir alana doğrudan datayı basabiliriz.
class Program
{
static void Main(string[] args)
{
string filePath = Path.Combine(Environment.CurrentDirectory, "Data.xlsx");
var excel = new ExcelQueryFactory(filePath);
excel.AddTransformation< Customer>(x => x.IsActive, value => value == "Y");
var customers = from c in excel.Worksheet< Customer>("Customers")
select c;
Console.ReadLine();
}
}
public class Customer
{
[ExcelColumn("CustomerID")]
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public bool IsActive { get; set; }
}
Çalışma Sayfa İsimlerini Sorgulamak
static void Main(string[] args)
{
string filePath = Path.Combine(Environment.CurrentDirectory, "Data.xlsx");
var excel = new ExcelQueryFactory(filePath);
var worksheetNames = excel.GetWorksheetNames();
}
Çalışma Sayfasına Ait Kolon İsimlerini Sorgulamak
static void Main(string[] args)
{
string filePath = Path.Combine(Environment.CurrentDirectory, "Data.xlsx");
var excel = new ExcelQueryFactory(filePath);
var columnNames = excel.GetColumnNames("Products");
}
Boşlukları Temizlemek
Excelde verilerin başında ya da sonunda boşluklar olabilir. Modele datayı set ettikten sonra bu durumu kontrol edebiliriz. Ya da TrimSpacesType ile bu kuralı en başında uygulatabiliriz.
static void Main(string[] args)
{
string filePath = Path.Combine(Environment.CurrentDirectory, "Data.xlsx");
var excel = new ExcelQueryFactory(filePath);
excel.TrimSpaces = Query.TrimSpacesType.Both;
var customers = from c in excel.Worksheet< Customer>("Customers")
select c;
}
CSV Dosyasını Okumak
Ekstra yapmamız gereken birşey yok, sadece dosya yolunu vermemiz yeterlidir.
static void Main(string[] args)
{
string filePath = Path.Combine(Environment.CurrentDirectory, "Data.csv");
var excel = new ExcelQueryFactory(filePath);
var customers = from c in excel.Worksheet< Customer>()
select c;
}
Kütüphane açık kaynak ve Github üzerinde yayınlanmaktadır. Repository linkine aşağıdan ulaşabilirsiniz. Başarılı bir kütüphane olmuş ve ciddi anlamda bizlere kolaylık sağlamaktadır. Dilerim sizlerede faydalı olur.
LinqToExcelBaşarılar dilerim.
