How to Create Excel file in ASP.NET C#

0 comments

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

READ MORE >>

0 comments: