Generic Way to Bind Enum With Different ASP.NET List Controls

In this post I am going to explain how we can bind a Enum with ASP.NET List Controls. These comprise of four different types of control, CheckBoxList, DropDownList, ListBox and RadioButtonList. This is one of the common requirements during development to bind a Enum with List Control and challenge is when we need to bind the both text and value.
Let’s consider we have the blow Enum. Now I am going to bind it with all the above mentioned type List Controls.



Well, One of the easiest way that we can implement is by Enum.GetNames(Type enumType)which retrieves the name list of enumberation  and Enum.GetValues(Type enumType), which returns the list of values for each names .

Once we have the Names and Values, we can easily bind with any ListControl by just iterating with them.

 

01	public void BindEnumToListControls(Type enumType, ListControl listcontrol)

02

{

03	string[] names;

04	Array values;

05	int countElements, upperBound, lowerBound;

06	names = Enum.GetNames(enumType);

07	values = Enum.GetValues(enumType);

08	for (countElements = 0; countElements <= names.Length - 1; countElements++)

09

{

10	listcontrol.Items.Add(new ListItem(names[countElements].ToString(), values.GetValue(countElements).ToString()));

11

}

12

}

 

You will have the below output



Now let’s have a quick look on how to call BindEnumToListControls() method, yeah very simple

 

1	protected void Page_Load(object sender, EventArgs e)

2

{

3	BindEnumToListControls(typeof(CustomerType), DropDownList1);

4	BindEnumToListControls(typeof(CustomerType), checkboxlist);

5	BindEnumToListControls(typeof(CustomerType), ListBox1);

6	BindEnumToListControls(typeof(CustomerType), RadioButtonList1);

7

}

You must be thinking why iterating through each and every names why not using LINQ instead of that.  Well, it’s time to optimized it .

The easiest way to do it with LINQ is to convert the enum into Dictionary and Set the DataSource of List. Why Dictionary ? Because we want to bind with both Key and Value Pair for Dropdown list.

 

1	public void BindEnumToListControls(Type enumType, ListControl listcontrol)

2

{

3	string [] names = Enum.GetNames(enumType);

4	List.DataSource = strNames.Select((key, value) =>

5	new { key, value }).ToDictionary(x => x.key, x => x.value + 1);

6	listcontrol.DataTextField = "Key";

7	listcontrol.DataValueField = "Value";

8	listcontrol.DataBind();

9

}

 

Above code will do the same as we have in the previous output.  But this approach certainly having a small problem . . If you look into the ViewSource of the above generated html and verify the Value Field for each and every control. All the value field are containing the sequential value.



In some certain case, Enum may contain the Value as well, so that time we need to bind the actual value instead of overriding the value while creating the Dictionary as shown in below snippet .



I was thinking for some different approach but which leads me using for-each only. Then I look for some solution, and found one very interesting post, which handles this in a smart way.

 

01	public void BindEnumToListControls(Type enumType, ListControl listcontrol)

02

{

03	string [] names = Enum.GetNames(enumType);

04	listcontrol.DataSource = Enum.GetValues(typeof(CustomerType)).Cast<Int32>()

05	.ToDictionary(currentItem =>

06	Enum.GetName(typeof(CustomerType), currentItem));

07	listcontrol.DataTextField = "Key";

08	listcontrol.DataValueField = "Value";

09	listcontrol.DataBind();

10

}

 

Now from the below image, you can see the Customer Type value is applied to the List elements.

Looking for simplest and handy  ways, here is two nice post by Suprotim Agarwal
How to Bind an ASP.NET DropDownList to an Enumeration with two lines of code
How to Bind An Enum Name and Value to an ASP.NET DropDownList

Cheers,
original post by
Abhijit Jana

Using ASP.NET 4.0 Chart Control With New Tooling Support for SQL Server CE

In this post I’m going to talk about  how we can use ASP.NET 4.0 Chart Control with SQL CE as back-end data base using Entity Framework. I will also show how Visual Studio 2010 SP1 provides new tooling supports for SQL Server CE 4.0. ASP.NET 4.0 introduced inbuilt chart controls features and Visual Studio 2010 SP1 Came up with nice tooling support for SQL Server CE. SQL CE is a free, embedded, lightweight  database engine that enables easy database storage. This does not required any installation and runs in memory.  Let’s see how we can place this together and create a small apps and deploy it using  new “Web Deployment Tool” which is available with Visual Studio 2010 SP1.

To demonstrate the complete flow  we will do the following steps.

• Creating a New ASP.NET 4.0 Web Forms Application.
• Create a SQL Server CE Database with new tooling of Visual Studio 2010 SP1.
• Adding ASP.NET Chart Control to web forms.
• Building an EF model layer to attaching with ASP.NET 4.0 Chart Control.
• Using Same Model layer with ASP.NET GridView with Enabling  Edit / Delete support of records in SQL CE Data base.
• Reflecting the changes in Chart Controls after editing records from GridView.
• Dynamically changing ASP.NET 4.0 Chart Control Type.
• Deploy using Web Deployment Tool.

After end of the above mentioned exercises we will achieve  something like below :


So Let’s start. First  create a new ASP.NET 4.0 Web Application and navigate to Solution Explorer (Solution Navigator) . In the Project solution hierarchy Right click on the “App_Data” folder  and select   “Add  > New Item” menu command.



This will open “Add Items Dialogs”  and then choose “SQL Server Compact 4.0 Local Database”,  Give the name “Students.sdf” and click on “Add”



Above step will create an empty SQL Server Compact database for local data.



Double click on the “Student.sdf” it will brings up the “Server Explorer” with the same data base “Students.sdf” which we have already created. Visual Studio treats the “Students.sdf” as a new Data Base Connection.


Tip : In this situation if you want to add a new SQL Server CE Connection or any chance you are not able to view the connection for created sdf file or got disconnect,  you can simply brings it back by doing followings
1. Click on “Connect To Data Base Icon”


2.  This will brings up the “Add Connection Dialog” but default Data Source is set to “Microsoft SQL Server ( SqlClient) . Click on “Change..”



3. In “Change Data Source” dialog, select “Microsoft SQL Server Compact 4.0”  as Data Source and by it will have only single Data Provider “.NET Framework Data Provider for Microsoft SQL Server Compact 4.0”



on click of “Ok” it will navigate back to “Add Connection Window”.
4. There you can “Create a New SQL CE DB” or can browse  existing “SQL CE DB”



Above 4 steps talks about create a new connection and SQL CE DB from Server Explore or using Existing one.  But we have already created our “Students.sdf”. So lets back to action.
In Server Explorer , Expand Students.sdf and click on “Create Table”



This will brings up “Add New Table” window where you have to fill the Table name and corresponding column name.  Here We have used table name as “Student” with three column “Roll”, “Name”, “Marks“



On click of “Ok”, you will see a new table with name “Student” has been created under the table folder .


Let’s have a look how we can enter data into the created table. There is two ways by which we  achieve the same.  In Server Explorer, right click on the table. From the context menu you can select either of “New Query” option where you have to write simple SQL Statement to store records or else you can select “Show Tables Data” where enter data in different column. Very straight forward.



below screen shows how we can insert data in SQL CE table using “New Query” mode.



Well, by using any of them just entered few data in Student table.



We will be displaying above data in ASP.NET Chart control. So lets build the ASP.NET Web Forms with Chart Control
ASP.NET 4 introduced inbuilt chart control which you can find in the “Data” section of toolbox .



Drag and Drop the Chart control in web page and choose any of the  Chart Type from Chart control Smart Tag.













Now we have to choose data source for the Chart Control.  So, let create a EF Model to read the data from
SQL CE Data Base.
From Solution Explorer > Add > New Item, Select “ADO.NET Entity Data Model” and give the name “Students.edmx”



Then Select “Generate from Database” option from “Entity Data Model Wizard”



Continue with the Wizard, in the next screen, you have to provide connection and entity name.



Click on “Finish”, you can see the generated “edmx” file



Just Build the solution once, and back to web page with Chart Control where we stopped earlier.
Selectoption from from Chart Control Smart Tag.



This will launch the “Data Source Configuration Wizard” , Select “Entity” type and specify the name “StudentEntityDataSource” and click on OK.
























From the below screen select Named Connection and Default Container name. Both of these dropdown populate automatically as we have already created the entity model. In the next window, select all the column and Enable Automatic insert, updates and deletes.




I just enabled the automatic insert, delete and update for future use with gridview. Click on Finish !
Once done with the data source configuration, provides the value for Chart Series Data Member .



That’s all. Run the application. You will get below output at first look.



Let’s make this stuff more interesting, First I will change the appearance to 3D, don’t worry. This is also inbuilt support !



On changes of above code snippet for the Chart control you will get below output.


Let me know bring one GridView in the seen so that you can see the Edit and update reflection to ASP.NET Chart Control that refers the same SQL Server CE Data Base.
I am referring the same Entity data source that used for Chart Control. This GridView also Enabled with Editing Rows that is the raised while creating the model I made the auto editable.



Below images is the first appearance after putting GridView.



Now, you can edit the records in grid and get the same reflection on Chart. Because  both are referring to same records.

01	<asp:UpdatePanel ID="UpdatePanel1" runat="server">

02	<ContentTemplate>

03	<table border="0" cellpadding="0" cellspacing="0">

04

<tr>

05

<td>

06

<div>

07	<asp:Chart ID="Chart1" runat="server" DataSourceID="StudentEntityDataSource" Palette="Chocolate"

08	BorderlineColor="Window">

09

<Series>

10	<asp:Series Name="Series1" XValueMember="Name" YValueMembers="Marks">

11	</asp:Series>

12

</Series>

13	<ChartAreas>

14	<asp:ChartArea Name="ChartArea1">

15	<Area3DStyle Rotation="15" Perspective="10" Enable3D="True" Inclination="15" IsRightAngleAxes="False"

16	WallWidth="1" IsClustered="False" />

17	</asp:ChartArea>

18	</ChartAreas>

19	<BorderSkin BackColor="Highlight" />

20	</asp:Chart>

21

</div>

22

<div>

23	<asp:Label ID="Label1" Text="Select Chart Type" runat="server" />

24	<asp:DropDownList ID="ddlCharType" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlCharType_SelectedIndexChanged">

25	</asp:DropDownList>

26

</div>

27

</td>

28	<td valign="top">

29	<asp:GridView ID="GridView1" runat="server" CellPadding="4" DataSourceID="StudentEntityDataSource"

30	ForeColor="#333333" GridLines="None">

31	<AlternatingRowStyle BackColor="White" />

32

<Columns>

33	<asp:CommandField ShowEditButton="True" ShowSelectButton="True" />

34	</Columns>

35	<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

36	<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

37	<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />

38	<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />

39	<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />

40	<SortedAscendingCellStyle BackColor="#FDF5AC" />

41	<SortedAscendingHeaderStyle BackColor="#4D0000" />

42	<SortedDescendingCellStyle BackColor="#FCF6C0" />

43	<SortedDescendingHeaderStyle BackColor="#820000" />

44	</asp:GridView>

45

</td>

46

</tr>

47

</table>

48	<asp:EntityDataSource ID="StudentEntityDataSource" runat="server" ConnectionString="name=StudentsEntities"

49	DefaultContainerName="StudentsEntities" EnableDelete="True" EnableFlattening="False"

50	EnableInsert="True" EnableUpdate="True" EntitySetName="Students">

51	</asp:EntityDataSource>

52	</ContentTemplate>

53	</asp:UpdatePanel>

In the above picture I have shown two scenarios. Upper section is the chart before updating records and bottom section after updating the records.  You can also see the snaps of updated  SQL CE Data base. Yes, very simple !
To make it more interesting let’s implement the dynamically change the Chart Type.



If you are wondering how to  implement this, here we go. Let’s put a dropdown control in the web forms.
All the Chart Type for ASP.NET 4.0 Chart control are defined in a public enum SeriesChartType.  So first bind the SeriesChartType enum value in dropdown.  You can read one of my post on Generic Way to Bind Enum With Different ASP.NET List Controls .

01	protected void Page_Load(object sender, EventArgs e)

02

{

03	if (!Page.IsPostBack)

04

{

05	BindEnumToListControls(typeof(SeriesChartType), ddlCharType);

06

}

07

}

08	/// <summary>

09	/// Binds the enum to list controls.

10	/// </summary>

11	/// <param name="enumType">Type of the enum.</param>

12	/// <param name="listcontrol">The listcontrol.</param>

13	public void BindEnumToListControls(Type enumType, ListControl listcontrol)

14

{

15	string[] names = Enum.GetNames(enumType);

16	listcontrol.DataSource = names.Select((key, value) =>

17	new { key, value }).ToDictionary(x => x.key, x => x.value + 1);

18	listcontrol.DataTextField = "Key";

19	listcontrol.DataValueField = "Value";

20	listcontrol.DataBind();

21

}

Change the Chart type on Drop Down Selection index change.

1	protected void ddlCharType_SelectedIndexChanged(object sender, EventArgs e)

2

{

3	this.Chart1.Series["Series1"].ChartType = (SeriesChartType)Enum.Parse(typeof(SeriesChartType), ddlCharType.SelectedItem.Text);

4

}

Finally, let have a look into the deployment. Why I am talking deployment over here ? Yes the only reason of SQL Server CE. As I said, we do not need any installation of SQL Server CE DB if we want to move our data base, but we have to provide some associated dll that helps ASP.NET engine to interact with the DB. Visual Studio 2010 SP1 introduced one new features “Web Deployment Tool”.  Right Click on the Solution file, and select “Add Deployable Dependencies…” .



This is only available for Razor and SQL Server CE.



Select “Select SQL Server Compact”  checkbox and “Click on Ok” . This will add all required assembiles automatcially in your solution and your are good to deploy without any problem



Well, that’s all. To summarizes what I have discussed is using ASP.NET 4.0 Chart Control with SQL Server CE 4.0. I have also discussed how we can use power of SQL Server CE Tool support which is introduced in Visual Server 2010 SP1 along with deployment using Web Deployment Tool.
Hope this will help you !

Cheers,
Original Post by,
Abhijit Jana

How to Create Excel file in ASP.NET C#

Here you will learn how to Create Excel file in ASP.NET using C#.
You can create Excel file using

1. OpenXML
2. Microsoft.Office.Interop.Excel

1. OpenXML

For more information about OpenXML http://excelpackage.codeplex.com/

1. For this you can create One Template file and One Source file.
   
   Here I create ErrorListtemplate.xlsx (Template file) and ErrorList.xlsx
    
2. You must add
    
   • ExcelPackage.dll
   • ExcelPackage.pdb
   • ExcelPackageXmlDocumentationFile.xml
   • GacReg.bat

Your .aspx file like:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="OpenXml.aspx.cs" Inherits="OpenXml" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml"><head runat="server">    <title></title></head>
<body>    <form id="form1" runat="server">  
  <div>     
   <asp:Button ID="btn_Excel" runat="server" Text="Excel"
            onclick="btn_Excel_Click" /> 
   </div>   
 </form></body>
</html>

your .cs file is like :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Reflection;
using System.IO;
using OfficeOpenXml;

public partial class OpenXml : System.Web.UI.Page
{
    DataTable Dt = new DataTable();
    object[] query;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
        }
    }

    private void GetRecoredForExcelfile()
    {
        using (OpenXmlDataDataContext db = new OpenXmlDataDataContext())
        {
            var info = from p in db.userinfos
                       select p;

            if (info != null)
            {
                query = info.ToArray();
                Dt = ConvertToDatatable(query);
            }

        }
    }

     /// <summary>    /// Convert Object Array to DataTable    /// </summary>    /// <param name="array"></param>    /// <returns></returns>   
 public static DataTable ConvertToDatatable(Object[] array)
    {

        PropertyInfo[] properties = array.GetType().GetElementType().GetProperties();
        DataTable dt = CreateDataTable(properties);
        if (array.Length != 0)
        {
            foreach (object o in array)
                FillData(properties, dt, o);
        }
        return dt;
    }
     #region Private Methods

    /// <summary>    /// Creates total column of datatable.    /// </summary>    /// <param name="properties"></param>    /// <returns></returns> 
   private static DataTable CreateDataTable(PropertyInfo[] properties)
    {
        DataTable dt = new DataTable();
        DataColumn dc = null;
        foreach (PropertyInfo pi in properties)
        {
            dc = new DataColumn();
            dc.ColumnName = pi.Name;
            dt.Columns.Add(dc);
        }
        return dt;
    }

    /// <summary>    /// Fills data in Datatable    /// </summary>    /// <param name="properties"></param>    /// <param name="dt"></param>      
      private static void FillData(PropertyInfo[] properties, DataTable dt, Object o)
    {
        DataRow dr = dt.NewRow();
        foreach (PropertyInfo pi in properties)
        {
            dr[pi.Name] = pi.GetValue(o, null);
        }
        dt.Rows.Add(dr);
    }    #endregion    protected void btn_Excel_Click(object sender, EventArgs e)
    {
        GetRecoredForExcelfile();
        string newFilePath = Server.MapPath("ExcelFile/ErrorList.xlsx");
        string templateFilePath = Server.MapPath("ExcelFile/ErrorListtemplate.xlsx");
        FileInfo newFile = new FileInfo(newFilePath);
        FileInfo template = new FileInfo(templateFilePath);
        using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
        {
            foreach (ExcelWorksheet aworksheet in xlPackage.Workbook.Worksheets)
            {
                aworksheet.Cell(1, 1).Value = aworksheet.Cell(1, 1).Value;
            }

            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets["Sheet1"];
            int startrow = 5;
            int row = 0;
            int col = 0;

            for (int j = 0; j < Dt.Columns.Count; j++)
            {
                col++;
                for (int i = 0; i < Dt.Rows.Count; i++)
                {
                    row = startrow + i;                  
                    ExcelCell cell = worksheet.Cell(row, col);
                    cell.Value = Dt.Rows[i][j].ToString();
                    xlPackage.Save();
                }
            }

        }
    }
}

2. Microsoft.Office.Interop.Excel
You must Add reference Microsoft Excel 12.0 Object Library from .NET COM .

Your .aspx code like:<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MicrosoftOfficeIntrupt.aspx.cs" Inherits="MicrosoftOfficeIntrupt" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">    <title></title></head>
<body>    <form id="form1" runat="server">  
  <div>     <asp:Button ID="btn_Excel" runat="server" Text="Excel"
            onclick="btn_Excel_Click" />   
 </div>  
  </form></body>
</html>

Your .cs file like:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Reflection;
using System.IO;

using Microsoft.Office.Interop.Excel;

public partial class MicrosoftOfficeIntrupt : System.Web.UI.Page
{
    System.Data.DataTable dtCustmer = new System.Data.DataTable();
     object[] query;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {

        }

    }

    private void GetRecoredForExcelfile()
    {
        using (OpenXmlDataDataContext db = new OpenXmlDataDataContext())
        {
            var info = from p in db.userinfos
                       select p;

            if (info != null)
            {
                query = info.ToArray();
                dtCustmer = ConvertToDatatable(query);
                //Session["dtlist"] =Dt;            }

        }
    }

    /// <summary>    /// Convert Object Array to DataTable    /// </summary>    /// <param name="array"></param>    /// <returns></returns>   
 public static System.Data.DataTable ConvertToDatatable(Object[] array)
    {

        PropertyInfo[] properties = array.GetType().GetElementType().GetProperties();
        System.Data.DataTable dt = CreateDataTable(properties);
        if (array.Length != 0)
        {
            foreach (object o in array)
                FillData(properties, dt, o);
        }
        return dt;
    }

    #region Private Methods

    /// <summary>    /// Creates total column of datatable.    /// </summary>    /// <param name="properties"></param>    /// <returns></returns> 
   private static System.Data.DataTable CreateDataTable(PropertyInfo[] properties)
    {
        System.Data.DataTable dt = new System.Data.DataTable();
        DataColumn dc = null;
        foreach (PropertyInfo pi in properties)
        {
            dc = new DataColumn();
            dc.ColumnName = pi.Name;
            //dc.DataType = pi.PropertyType;            dt.Columns.Add(dc);
        }
        return dt;
    }

    /// <summary>    /// Fills data in Datatable    /// </summary>    /// <param name="properties"></param>    /// <param name="dt"></param>        
    private static void FillData(PropertyInfo[] properties, System.Data.DataTable dt, Object o)
    {
        DataRow dr = dt.NewRow();
        foreach (PropertyInfo pi in properties)
        {
            dr[pi.Name] = pi.GetValue(o, null);
        }
        dt.Rows.Add(dr);
    }

    #endregion    protected void btn_Excel_Click(object sender, EventArgs e)

    {
        GetRecoredForExcelfile();
        string newFilePath = Server.MapPath("ExcelFile/OfficeErrorList.xlsx");       
            ApplicationClass objExcel = null;
            Workbooks objBooks = null;
            _Workbook objBook = null;
            Sheets objSheets = null;
            _Worksheet objSheet = null;
            Range objRange = null;
            int row = 1, col = 1;
            try                {
                //   System.Data.DataTable dtCustmer = GetAllCustomers();                   //System.Data.DataTable dtCustmer = Dt.Clone();                   objExcel = new ApplicationClass();
                   objBooks = objExcel.Workbooks;
                   objBook = objBooks.Add(XlWBATemplate.xlWBATWorksheet);
                    //Print column heading in the excel sheet                    int j = col;
                    foreach (DataColumn column in dtCustmer.Columns)
                        {
                            objSheets = objBook.Worksheets;
                            objSheet = (_Worksheet)objSheets.get_Item(1);
                            objRange = (Range)objSheet.Cells[row, j];
                            objRange.Value2 = column.ColumnName;
                           // objRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue);                            //objRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Maroon);                            j++;
                        }
                        row++;

                        int count = dtCustmer.Columns.Count;
                        foreach (DataRow dataRow in dtCustmer.Rows)
                        {
                            int k = col;
                            for (int i = 0; i < count; i++)
                            {
                                objRange = (Range)objSheet.Cells[row, k];
                                objRange.Value2 = dataRow[i].ToString();
                                k++;
                            }
                        row++;
                        }

                        //Save Excel document                        objSheet.Name = "Sample Sheet";
                        object objOpt = Missing.Value;
                        objBook.SaveAs(newFilePath, objOpt, objOpt, objOpt, objOpt, objOpt, XlSaveAsAccessMode.xlNoChange, objOpt, objOpt, objOpt, objOpt, objOpt);
                        objBook.Close(false, objOpt, objOpt);

                }
            catch                {
                }
            finally                {
                    objExcel = null;
                    objBooks = null;
                    objBook = null;
                    objSheets = null;
                    objSheet = null;
                    objRange = null;
                    ReleaseComObject(objExcel);
                    ReleaseComObject(objBooks);
                    ReleaseComObject(objBook);
                    ReleaseComObject(objSheets);
                    ReleaseComObject(objSheet);
                    ReleaseComObject(objRange);
                }

        }
     //Release COM objects from memory    public void ReleaseComObject(object reference)
    {
        try        {
            while (System.Runtime.InteropServices.Marshal.ReleaseComObject(reference) <= 0)
            {
            }
        }
        catch        {
        }
    }   
}

If you have some Error like

Exception from HRESULT: 0x800A03EC - Excel, .Net, SQL and Windows Server

Then you have to give iis or server permission

1. Login to the server as a administrator.
2. Go to "Start" -> "Run" and enter "taskmgr"
3. Go to the process tab in task manager and check "Show Processes from all
   users"
4. If there are any "Excel.exe" entries on the list, right click on the entry and select "End Process"
5. Close task manager.
6. Go to "Start" -> "Run" and enter "services.msc"
7. Stop the service automating Excel if it is running.
8. Go to "Start" -> "Run" and enter "dcomcnfg"
9. This will bring up the component services window, expand out "Console Root" -> "Computers" -> "DCOM Config"
   
   
    
10. Find "Microsoft Excel Application" in the list of components.
11. Right click on the entry and select "Properties"
12. Go to the "Identity" tab on the properties dialog.
13. Select "The interactive user."
    
    
14. Click the "OK" button.
15. Switch to the services console
16. Start the service automating Excel
17. Test you application again.

For more information http://www.hagrin.com/319/exception-hresult-0x800a03ec-excel-net-sql-and-windows-server-2008

And Add in configuration<identity impersonate="true" userName="yourusername" password="yourpassword"/>

You must add assembly in your web.config file
.<compilation debug="true" targetFramework="4.0">            
             <assemblies>                     
        <add assembly="microsoft.office.interop.excel, version=12.0.0.0, culture=neutral,                                 publickeytoken=71e9bce111e9429c"/>  
      <add assembly="DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>  
    </assemblies>     
    </compilation>

Thank you for reading this post. Please post your feedback, question, or comments about this post