Download multiple excel sheet using NPOI.dll

Download latest NPOI.dll and add into your project reference.


using NPOI.HSSF.UserModel;

 public partial class WebForm1 : System.Web.UI.Page
    {
        string Connstring = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            GetData();
        }

        private void GetData()
        {
            DataSet ds = new DataSet();
            string[] tableNames;
            using (SqlConnection con = new SqlConnection(Connstring))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "ExcelDownload";

                    SqlParameter param = new SqlParameter("@tableNames", SqlDbType.VarChar,50);
                    param.Direction = ParameterDirection.Output;
                    param.Value = 50;
                    cmd.Parameters.Add(param);


                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        string strTableNames = Convert.ToString(cmd.Parameters["@tableNames"].Value);
                        tableNames = strTableNames.Split(',');
                    }

                }
            }

            writeDataToExcelFile(ds, tableNames);
        }
        private void writeDataToExcelFile(DataSet ds, string[] tableNames)
        {
            for (int i = 0; i < tableNames.Length; i++)
            {
                ds.Tables[i].TableName = tableNames[i];
            }
            var workbook = new HSSFWorkbook();
            string filename = "MultiplesheetDownloadDemo";
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                DataTable dt = ds.Tables[i];
                if (dt.Rows.Count > 0)
                {
                    var Sheet = workbook.CreateSheet(ds.Tables[i].TableName);

                    int cols = 0;
                    var myRow = Sheet.CreateRow(0);
                    var style1 = workbook.CreateCellStyle();

                    // cell background
                    style1.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Violet.Index;

                    // font color
                    var font1 = workbook.CreateFont();
                    font1.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
                    style1.SetFont(font1);


                    foreach (DataColumn column in dt.Columns)
                    {
                        var Cell = myRow.CreateCell(cols);
                        Cell.SetCellValue(column.ColumnName);
                        Cell.CellStyle = style1;
                        cols++;
                    }


                    for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
                    {
                        myRow = Sheet.CreateRow(rowNum + 1);
                        for (int cellNum = 0; cellNum < dt.Columns.Count; cellNum++)
                        {
                            var Cell = myRow.CreateCell(cellNum);
                            Cell.SetCellValue(Convert.ToString(dt.Rows[rowNum][cellNum]));
                        }

                    }
                }
            }
            using (var buffer = new MemoryStream())
            {
                workbook.Write(buffer);

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename + ".xls"));
                Response.Clear();
                Response.BinaryWrite(buffer.GetBuffer());
                Response.End();
            }


        }
    }

Stored Procedure:

ALTER PROC ExcelDownload
(
@tableNames VARCHAR(500) OUTPUT
)
AS
BEGIN
SET @tableNames = 'tblTable1,tblTable2'
SELECT * FROM dbo.tblTable1
SELECT * FROM dbo.tblTable2
END

Insert Stored Procedure Results Into Table

CREATE PROCEDURE GetList
AS
BEGIN
SELECT
ListName = 'MyList'
,ListNumber = 1
END
GO

-- this table will house our results
CREATE TABLE #List
(
ListName VARCHAR(25),
ListNumber INT
)

-- finally, execute and insert into our table
INSERT INTO #List
(
ListName,
ListNumber
)
EXEC dbo.GetList

select * from #List

MVC application life cycle

Any web application has two main execution steps first understanding the request and depending on the type of the request sending out appropriate response. MVC application life cycle is not different it has two main phases first creating the request object and second sending our response to the browser.

Creating Response object: - The request object creation has four major steps. Below is the detail explanation of the same.

Step 1 Fill route: - MVC requests are mapped to route tables which in turn specify which controller and action to be invoked. So if the request is the first request the first thing is to fill the route table with routes collection. This filling of route table happens in the global.asax file.

Step 2 Fetch route:- Depending on the URL sent "UrlRoutingModule" searches the route table to create "RouteData" object which has the details of which controller and action to invoke.

Step 3 Request context created: - The "RouteData" object is used to create the "RequestContext" object.

Step 4 Controller instance created: - This request object is sent to "MvcHandler" instance to create the controller class instance. Once the controller class object is created it calls the "Execute" method of the controller class.

Creating Response object: - This phase has two steps executing the action and finally sending the response as a result to the view.

Step 5 Execute Action: - The "ControllerActionInvoker" determines which action to executed and executes the action.

Step 6 Result sent: - The action method executes and creates the type of result which can be a view result , file result , JSON result etc.

So in all there are six broad steps which get executed in MVC application life cycle.

Regular expression

http://rubular.com/

Delegate, MultiDelegate with an example

1.       What is Delegate:

A delegate is a type safe function pointer. That is it holds a reference (Pointer) to a function.

The signature of the delegate must match on the signature of the function, the delegate points to; otherwise you get a compiler error. This is the reason delegates are called as a type safe function pointers.

A delegate is similar to a class. You can create an instance of it, and when you do so, you pass in the function name as a parameter to the delegate constructor, and it to this function the delegate will point to.

Note: Delegate syntax is similar to method syntax with a delegate keyword.

Example:

public delegate void HelloDelegateSamp1(string strmsg);

    class Program

    {

        static void Main(string[] args)

        {

            HelloDelegateSamp1 s = new HelloDelegateSamp1(Hello);

            s("Welcome to delegate");

            Console.Read();

        }

        public static void Hello(string Message)

        {

            Console.WriteLine(Message);

        }

    }

We can pass n number of parameters/ Parameters is not required.

Without parameter: s();

N number of parameters: s(string strmsg, int age);

2.       Why we need delegate example-1

The following example is not a delegate. It is a simple example. But we will not reuse of Employee class in future. We can go for the delegate if we reuse the employee class.

Here we are promoted to employee with their experience. But some other company different criteria will use to promote employee. At this time this employee class does not help. We should change the logic. But without change the logic in employee (We have hard coded the logic here) class we can promote employee in different criteria using Delegate.

class Program

 {

  static void Main(string[] args)

   {

 List<Employee> emplist = new List<Employee>();

 emplist.Add(new Employee { ID = 101, Name = "GK", Salary = 5000, Experience = 5 });

 emplist.Add(new Employee { ID = 101, Name = "Len", Salary = 6000, Experience = 6 });

 emplist.Add(new Employee { ID = 101, Name = "Alex", Salary = 4000, Experience = 4 });

 emplist.Add(new Employee { ID = 101, Name = "Mani", Salary = 7000, Experience = 7 });

 emplist.Add(new Employee { ID = 101, Name = "Saj", Salary = 3000, Experience = 3 });

    Employee.promoteEmployee(emplist);

    Console.Read();

    }

 }

    class Employee

    {

        public int ID { get; set; }

        public string Name { get; set; }

        public int Salary { get; set; }

        public int Experience { get; set; }

        public static void promoteEmployee(List<Employee> emplist)

        {

            foreach (Employee emp in emplist)

            {

                if (emp.Experience >= 5)

                {

                    Console.WriteLine(emp.Name);

                }

            }

        }

    }

3.       Why we need delegate example-2.

class Program

 {

  static void Main(string[] args)

   {

 List<Employee> emplist = new List<Employee>();

 emplist.Add(new Employee { ID = 101, Name = "GK", Salary = 5000, Experience = 5 });

 emplist.Add(new Employee { ID = 101, Name = "Len", Salary = 6000, Experience = 6 });

 emplist.Add(new Employee { ID = 101, Name = "Alex", Salary = 4000, Experience = 4 });

 emplist.Add(new Employee { ID = 101, Name = "Mani", Salary = 7000, Experience = 7 });

 emplist.Add(new Employee { ID = 101, Name = "Saj", Salary = 3000, Experience = 3 });

   //Create a instance of Delegate , Promote is method which has return type is bool

    IsPromotoble isPromotoble = new IsPromotoble(Promote);

   //  Employee.promoteEmployee(emplist, emp => emp.Experience >= 5);

   Employee.promoteEmployee(emplist, isPromotoble);

   Console.Read();

  }

       

//Delegate is reference to this method

        public static bool Promote(Employee emp)

        {

            if (emp.Experience >= 5)

            {

                return true;

            }

            else

            {

                return false;

            }

        }

}

    

//Delegate Declaration

delegate bool IsPromotoble(Employee employee);

class Employee

    {

        public int ID { get; set; }

        public string Name { get; set; }

        public int Salary { get; set; }

        public int Experience { get; set; }

        public static void promoteEmployee(List<Employee> emplist, IsPromotoble isElligibleToPromotoble)

        {

            foreach (Employee emp in emplist)

            {

                if (isElligibleToPromotoble(emp))

                {

                    Console.WriteLine(emp.Name + " Promoted");

                }

            }

        }

    }

Here we can promote the employee without modify the logic in employee class using the delegate. We can reuse the Employee class which it does not have the hard code logic.

Here instead of the below code, we can use lamda expression.

//Create a instance of Delegate, Promote is method which has return type is bool

    IsPromotoble isPromotoble = new IsPromotoble(Promote);

//Delegate is reference to this method

        public static bool Promote(Employee emp)

        {

            if (emp.Experience >= 5)

            {

                return true;

            }

            else

            {

                return false;

            }

        }

}

lamda expression Example: instead of above code

 Employee.promoteEmployee(emplist, emp => emp.Experience >= 5);

4.  MultiCast Delegate

A multiple delegate is a delegate that has reference to the more than one function. When you invoke a multicast delegate, all the functions to the delegate is pointing to, are invoked.

There are to approaches to create a multicast delegate. Depending on the approach you use

+ or += to register a method  with  the delegate.

– or -= to register a method  with the delegate.

Note: A multicast delegate, invoke the methods in the invocation list, in the same order in which they are added.

If the delegate has a return type other than void and if the delegate is a multicast delegate, only the value of the last involved method will be returned. Along the same lines, if the delegate has an out parameter, the value of the output parameter, will be the value assigned by the last method.

Where do use multicast delegate?

Multicast delegate makes implementation of observer design pattern very simple. Observer pattern is also called as publish/ subscribe pattern.

Example 1,

public delegate void  SampleMulticastDelegate();

    class Program

    {

        static void Main(string[] args)

        {

            SampleMulticastDelegate del1, del2, del3, del4;

            del1 = new SampleMulticastDelegate(Method1);

            del2 = new SampleMulticastDelegate(Method2);

            del3 = new SampleMulticastDelegate(Method3);

            del4 = del1 + del2 + del3;

            //del4 = del1 + del2 + del3 - del2;

            del4();  //Here del4 holding reference of 3 method.

            Console.Read();

        }

        public static void Method1()

        {

            Console.WriteLine("Method one is invoked");

        }

        public static void Method2()

        {

            Console.WriteLine("Method two is invoked");

        }

        public static void Method3()

        {

            Console.WriteLine("Method three is invoked");

        }         

    }  

Example 2,

Here the same instance but register the multiple methods using += sign.

static void Main(string[] args)

        {

            SampleMulticastDelegate del = new SampleMulticastDelegate(Method1);

            del += Method2;

            del += Method3;

     del -= Method2;  // Remove method 2 using - sign

           

            del();

            Console.Read();

        }

Example 3,  Here the return the values is 2. Reason- only the value of the last involved method will be returned

public delegate int SampleMulticastDelegate();

    class Program

    {

        static void Main(string[] args)

        {

            SampleMulticastDelegate del = new SampleMulticastDelegate(Method1);

            del += Method2;

            int Delegateruturnval = del();

            Console.WriteLine("Delegate Return value is={0}", + Delegateruturnval);

            Console.Read();

        }

        public static int Method1()

        {

            return 1;

        }

        public static int Method2()

        {

            return 2;

        }

    }

Example 4, Here the return the values is 2. But we have used out parameter. Reason- only the value of the last involved method will be returned

public delegate void SampleMulticastDelegate(out int Integer);

    class Program

    {

        static void Main(string[] args)

        {

            SampleMulticastDelegate del = new SampleMulticastDelegate(Method1);

            del += Method2;

            int DelegateOutputvalue = -1;

            del(out DelegateOutputvalue);

            Console.WriteLine("Delegate Return value is={0}", +DelegateOutputvalue);

            Console.Read();

        }

        public static void Method1(out int number)

        {

            number = 1;

        }

        public static void Method2(out int number)

        {

            number = 2;

        }

    }