LinqToExcel Kullanımı - Excel ve CSV Verilerini LINQ İle Sorgulama

20.05.2018 Hikmet Okumuş 994 3 NuGet Package


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.


www.hikmetokumus.com


Örnek excel için Northwind veritabanında yer alan Products tablosundaki verileri kullanmaktayım. Excel içeriği aşağıdaki gibidir.

www.hikmetokumus.com


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; }
    }
}

www.hikmetokumus.com


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.

www.hikmetokumus.com

	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();
	}

www.hikmetokumus.com

www.hikmetokumus.com


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();
    }

www.hikmetokumus.com


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.

www.hikmetokumus.com


    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();
    }

www.hikmetokumus.com


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.

www.hikmetokumus.com

www.hikmetokumus.com

    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.

www.hikmetokumus.com

    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; }
    }    

www.hikmetokumus.com


Ç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();
    }

www.hikmetokumus.com


Ç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");
    }

www.hikmetokumus.com


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.

www.hikmetokumus.com

www.hikmetokumus.com

    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;
    }

www.hikmetokumus.com


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;
    }

www.hikmetokumus.com

www.hikmetokumus.com


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.

LinqToExcel


Başarılar dilerim.


Kullanıcı Yorumları


hikmet okumuş makale yorum
Ali Yılmaz
21.05.2018 07:21:32

Çok başarılı bir anlatım olmuş. Teşekkürler hocam 

hikmet okumuş makale yorum
Umut Sun
20.06.2018 11:26:29

Çok başarılı ve detaylı bir anlatım olmuş elinize sağlık teşekkürler FileHelpers paketinide deneyebilirsiniz onunda kendi içinde güzel kullanımları var csv,txt vs vs dosyalar için

hikmet okumuş makale yorum
Folyo kesim
13.08.2018 00:48:37

Elinize emeğinize sağlık. Sizi severek takıp ediyorum.

 

folyo kesim



Yorum