Monday, June 30, 2014

LINQ standard query operators

If you are experience .NET developer then you might aware about LINQ and it’s advantage, Yes it’s a uniform query language to get and filter data from various data source.
Fine, but link is beyond than that when we combine various functions and operators associated with LINQ query. You believe or not, by using those functions we can make LINQ more robust data processing and manipulation query.
In this article we will see various functions and operators associated with LINQ and we will see how it makes simple of data processing operation.
We can categorize the operators in many categories. Let’s see few of them.

Aggregation:
Here we will understand various LINQ operators which will work on data , let’s start with aggregation . Here is few examples.

namespace ConsoleApp
{
    public class Program
    {
        public static void Main(string[] args)
        {
            //Example of aggregration

            int[] data = { 1, 2, 3, 4, 5 };
            //Sum of numbers
            Console.WriteLine(data.Sum());   //ans:15
            //Average of numbers
            Console.WriteLine(data.Average()); //Ans :3
            //Max of numbers
            Console.WriteLine(data.Max());  //Ans :5

            String[] strdata = {"1","two","three","four","five"};

            //Item with minimum length
            Console.WriteLine(strdata.Min());  //Ans :1

            //Item with minimum character(same as above)
            Console.WriteLine(strdata.Min(f=>f.Length));  //Ans:1

            //Items with maximum character
            Console.WriteLine(strdata.Max(f => f.Length)); //Ans :5
           
            Console.ReadLine();
        }
    }
}


Concatination:
Concatenation operation takes place when we want to combine more than one element. In this example we have used Concat function to combine two arrays. I believe, many developer don’t know the shortcut to combine two arrays, sometimes they use foreach or some kind of iterative statement to do same job.
using System;
using System.Collections.Generic;
using System.Linq;
    public class Program
    {
        public static void Main(string[] args)
        {
            //First array
            int[] data = { 1, 2, 3 };

            //Concatination with another array
            int[] result = data.Concat(new int[] { 4, 5, 6 }).ToArray();
            //result 1,2,3,4,5,6
        }
    }

Conversion:
The dictionary meaning of conversion is applicable here.  To convert one type to another type, we can use Cast parameterized function. In this example I am showing to convert from IEnumerable to list and array.
       public static void Main(string[] args)
        {
            object[] data = { "1", "2", "3", "4", "5" };

            //Cast to Ienumerable<string>
            IEnumerable<string> strEnum = data.Cast<string>();
            //Cast to List
            List<string> strList = data.Cast<string>().ToList();
            //Cast to Array
            string[] strArray = data.Cast<string>().ToArray();

        }

Element operation:
It’s very common in application that, sometime we need some direct element, direct in the sense we want to pick the element by it’s index or by query. There are many functions available , with which we can combine lambda expression to make our job simple.
public static void Main(string[] args)
        {
            int[] data = { 1, 2, 3, 4, 5 };
            data.ElementAt(10);   //Error
 data.ElementAtOrDefault(10);  //null
            data.First();  //1

            string[] name = {"sourav","Ram","Shyam"};
            //First three letter name
            name.First(m => m.Length == 3); // Ram

            //First name which is greater then 3
            name.First(m => m.Length > 3); //sourav

            //Last element of array
            name.Last();  //Shyam

            //Get one element
            name.Single(); //Exception. More than one element

            //Try to get single element if empty array then return null
            name.SingleOrDefault();  //Exception: More than one element

            //try to get item whose length is 10
            name.SingleOrDefault(m => m.Length == 10);  //null
        }

Equality:
Equality checking between list or some kind of collection is another common requirement in application. If we want to check whether the words are sequentially arrange or not ?we can use SequenceEqual function. Though ,many are there. Just I have mention the uncommon one.
public static void Main(string[] args)
        {
            //Equality checking.
            string[] words = { "sourav", "Ram", "Shyam" };

            //Check whether all items are present sequencially or not
            words.SequenceEqual(new[] { "sourav", "Ram", "Shyam" });  //Ans : True
           
            //Case is different
            words.SequenceEqual(new[] { "SOURAV", "RAM", "SHYAM" }); //False
           
            //Ignore cases in checking
            words.SequenceEqual(new[] { "SOURAV", "RAM", "SHYAM"}, StringComparer.OrdinalIgnoreCase); //True
        }


Partitioning:
This operation divides the whole set into two partitions and picks one of them. In this example we will see few functions combining with lambda expression.
public static void Main(string[] args)
        {
            string[] name = { "one","two", "three" };
           
            // take two elements from list 
  name.Take(2).ToList<string>();  // one two
           
            //Skip first one element in list
            name.Skip(1).ToList<string>(); //two three

            //Take eleents whose length is 3
            name.TakeWhile(f => f.Length == 3);  //one two

            //Skil element when length is less than 4
            name.SkipWhile(f => f.Length < 4); // three

            Console.ReadLine();
        }

Projection:
This operation needed when we want to take certain part from collection. Here , we are taking part of name array by length, by its index position. Have a look on below example.
public static void Main(string[] args)
        {
            string[] name = { "one","two", "three" };

            //Find lenght if all element.
            name.Select(f => f.Length);  // 3 3 4

            //print element with index
            name.Select((a, b) => b.ToString() + ":" + a);  // 0 : one 1: two 2:three

            //Print items nuber of times by it's index
            //For example one is in 0th position - so not printed
            //two is in 1st position - so printed one time
            //Three is in 2nd position - so printed 2 times
            name.SelectMany((a, b) => Enumerable.Repeat(a, b)).ToArray(); //two three three
            Console.ReadLine();
        }

Set based operation:
Set based operation is not very common in day to day programming life, but it’s useful to know overview of the functions. It’s very similar to normal math set operation. Just have a quick look on below example.
public static void Main(string[] args)
        {
            //set based operation
            string[] first = { "a", "b", "b" ,"c" };
            string[] second = { "c", "d", "e" };

            //Select unique element
            first.Distinct();  // a,b,c
           
            //Intersect -Take common
            first.Intersect(second);  // c

            //Union - Get all unique element
            first.Union(second);      //a b c d e

            //Except
            first.Except(second);     // a b

            Console.ReadLine();
        }

Filtering:
Filtering is very much essential in day to day programming activity. In this example we will see few useful filtering functions associated with lambda expression.
public static void Main(string[] args)
        {
            //Sorting
            string[] name = {"one","two","three" };

            //Order collection alphabetically- First character
            name.OrderBy(f => f).ToArray();  //one three two

            //arrange alphabetically on second character
            name.OrderBy(f => f[1]); // three one two

            //Order by length
            name.OrderBy(f => f.Length);  //one two three

            //Order by length descending
            name.OrderByDescending(f => f.Length);

            //First by length then Descending.
            name.OrderBy(f => f.Length).ThenByDescending(f => f); //two one three

            Console.ReadLine();
        }

Conclusion:-
In this article we have see various functions associated with LINQ and lambda expression. It’s good to know if those functions, to reduce the code amount and program complexity.

Wednesday, June 18, 2014

Convert Excel files to Html

Background
My work is related to financial processing. As you know, Excel file is very popular and widely used in processing date. Excel enables us to do mathematical calculation of the data stored inside it. And I use it a lot in my work. But when presenting the data, Excel is not a good solution for me. I need other file format to present the data to my users.  And Html is the format which I prefer. So I need some sort of means to convert Excel files to Html. I surfed on the Internet and found a free library – Spire.XLS that can meet my need. Check it here.

Introduction
Spire.XLS is a .NET component that enables you to create Excel files, edit existing Excel files and convert Excel files. In a word, using it you can manipulate Excel files very easily. Let’s create a very simple Excel file using it.

First, create a Workbook instance. A Workbook instance represents an Excel file.

Workbook workbook = new Workbook();

Then get the first Worksheet.

Worksheet sheet = workbook.Worksheets[0];

Set A1 in the first Worksheet to “TEST”.

sheet.Range["A1"].Text = "TEST";

At last, save the document.

workbook.SaveToFile("result.xlsx", FileFormat.Version2010);

 It is neat to create an Excel file using the library. Check the generated file here:
Convert to Html format
In the last section, I introduce you how to create an Excel file. In this part, I will introduce you how to convert Excel file to Html.
First, create a Workbook instance. A Workbook instance represents an Excel file.
Workbook workbook = new Workbook();

Load the sample file.
workbook.LoadFromFile("sample.xlsx");

Then get the first Worksheet.
Worksheet sheet = workbook.Worksheets[0];

Convert the first Worksheet to Html.
sheet.SaveToHtml("result.html");
Source file:
Screenshot of generated html file:



I hope this article can do you some help. Thanks.