Post
Ask Question

How to Create Excel file in ASP.NET 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>

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"

    Excel1.gif
     

  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."

    Excel2.gif

  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>



Similar Articles