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

OpenXML
Microsoft.Office.Interop.Excel

1. OpenXML

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

For this you can create One Template file and One Source file.

Here I create ErrorListtemplate.xlsx (Template file) and ErrorList.xlsx
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">

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

    #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

Login to the server as a administrator.
Go to "Start" -> "Run" and enter "taskmgr"
Go to the process tab in task manager and check "Show Processes from all
users"
If there are any "Excel.exe" entries on the list, right click on the entry and select "End Process"
Close task manager.
Go to "Start" -> "Run" and enter "services.msc"
Stop the service automating Excel if it is running.
Go to "Start" -> "Run" and enter "dcomcnfg"
This will bring up the component services window, expand out "Console Root" -> "Computers" -> "DCOM Config"
Find "Microsoft Excel Application" in the list of components.
Right click on the entry and select "Properties"
Go to the "Identity" tab on the properties dialog.
Select "The interactive user."
Click the "OK" button.
Switch to the services console
Start the service automating Excel
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

Web Development - Dotnet with Cloud

Wednesday, December 7, 2011

How to Create Excel file in ASP.NET C#

No comments:

Popular Posts

About Me

Total Pageviews