DataGrid导出EXCEL方法

大家好，欢迎来到IT知识分享网。

DAL:
//产品信息导出——LPH
        public DataTable ExportRelease(string type)
        {

            string sql = “SELECT [ProductID],[BankName],[ProductName] ,[IsServiceEnt] ,[IsServiceTrade],[IsServiceIndividual],[IsServiceOT] , [ServiceOTDesc] ,[IsIndividual],[IsEnterpriseMF],[IsEnterpriseWS],[IsEnterpriseSR],[IsEnterpriseST],[IsEnterpriseOT],[EnterpriseOTDesc] ,[AgeLimit] ,[RegisterAddrType],[BusinessIncome] ,[TotalAssets] ,[NoGuaranty] ,[IsMortgageHS] ,[IsMortgageFB],[IsMortgageME] , [IsMortgageOT] ,[MortgageOTDesc] ,[IsHypoDR] ,[IsHypoWR],[IsHypoIP],[IsHypoAR],[IsHypoITP],[IsHypoOT],[HypoDesc],[IsGuaranteeInd] ,[IsGuaranteeEnt] ,[IsGuaranteeCom] ,[IsGuaranteeOT] ,[GuaranteeOTDesc] ,[LoanAmountUpper] ,[LoanAmountLower] , [LoanLimitUpper] ,[LoanLimitLower] ,[RepaymentType] ,[ReviewDate] ,[ProductDesc],[ProductDoc],[ProductURL] FROM [LoanProducts] where IsDeleted=0 and CheckStatus=3″;
                return base.SelectData(sql);
        }

BLL:
//产品信息导出——LPH
        protected DataTable ExportRelease(string type)
        {

            LP_CustomerDetailDAL dal = new LP_CustomerDetailDAL(CurrentTransaction);
            return dal.ExportRelease(type);
        }

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 TonkNet.BDA.LoanProduct.EL;
using System.Globalization;
using System.IO;
using System.Web.UI.WebControls.Adapters;
using System.Reflection;

public partial class Admin_BaseManager_Export_ExportMessage : BasePage
{

    protected void Page_Load(object sender, EventArgs e)
    {

    }

//产品信息导出
    protected void btnProduct_Click(object sender, EventArgs e)
    {

        System.Data.DataTable dt = (System.Data.DataTable)EventSubmit(“TonkNet.BDA.LoanProduct.BLL.LoanProduct.LP_CustomerDetailBLL”, “ExportRelease”, new object[] { “” });
        ProductToExcel(dt, “产品信息导出列表” + DateTime.Now.ToString(“yyyyMMddHHmmss”) + “.xls”);
    }

    /// <summary>
    /// 把datatable产品信息导出到excel中——LPH
    /// </summary>
    /// <param name=”dt”>DataTable</param>
    /// <param name=”defaultFileName”>默认保存文件名</param>
    protected void ProductToExcel(System.Data.DataTable dt, string defaultFileName)
    {

        DataGrid dgd = new DataGrid();
        dgd.AutoGenerateColumns = false;

        //添加字段
        BoundColumn ProductID = new BoundColumn();
        BoundColumn BankName = new BoundColumn();
        BoundColumn ProductName = new BoundColumn();
        BoundColumn IsServiceEnt = new BoundColumn();
        BoundColumn IsServiceTrade = new BoundColumn();
        BoundColumn IsServiceIndividual = new BoundColumn();
        BoundColumn ServiceOTDesc = new BoundColumn();
        BoundColumn IsIndividual = new BoundColumn();
        BoundColumn IsEnterpriseMF = new BoundColumn();
        BoundColumn IsEnterpriseWS = new BoundColumn();
        BoundColumn IsEnterpriseSR = new BoundColumn();
        BoundColumn IsEnterpriseST = new BoundColumn();
        BoundColumn EnterpriseOTDesc = new BoundColumn();
        BoundColumn AgeLimit = new BoundColumn();
        BoundColumn RegisterAddrType = new BoundColumn();
        BoundColumn BusinessIncome = new BoundColumn();
        BoundColumn TotalAssets = new BoundColumn();
        BoundColumn NoGuaranty = new BoundColumn();
        BoundColumn IsMortgageHS = new BoundColumn();
        BoundColumn IsMortgageFB = new BoundColumn();
        BoundColumn IsMortgageME = new BoundColumn();
        BoundColumn MortgageOTDesc = new BoundColumn();
        BoundColumn IsHypoDR = new BoundColumn();
        BoundColumn IsHypoWR = new BoundColumn();
        BoundColumn IsHypoIP = new BoundColumn();
        BoundColumn IsHypoAR = new BoundColumn();
        BoundColumn IsHypoITP = new BoundColumn();
        BoundColumn HypoDesc = new BoundColumn();
        BoundColumn IsGuaranteeInd = new BoundColumn();
        BoundColumn IsGuaranteeEnt = new BoundColumn();
        BoundColumn IsGuaranteeCom = new BoundColumn();
        BoundColumn GuaranteeOTDesc = new BoundColumn();
        BoundColumn LoanAmountUpper = new BoundColumn();
        BoundColumn LoanAmountLower = new BoundColumn();
        BoundColumn LoanLimitUpper = new BoundColumn();
        BoundColumn LoanLimitLower = new BoundColumn();
        BoundColumn RepaymentType = new BoundColumn();
        BoundColumn ReviewDate = new BoundColumn();
        BoundColumn ProductDesc = new BoundColumn();
        BoundColumn ProductDoc = new BoundColumn();
        BoundColumn ProductURL = new BoundColumn();

        //将导出的excel表格上面的第五行隐藏掉
        ProductID.HeaderStyle.Height = 0;
        BankName.HeaderStyle.Height = 0;
        ProductName.HeaderStyle.Height = 0;
        IsServiceEnt.HeaderStyle.Height = 0;
        IsServiceTrade.HeaderStyle.Height = 0;
        IsServiceIndividual.HeaderStyle.Height = 0;
        ServiceOTDesc.HeaderStyle.Height = 0;
        IsIndividual.HeaderStyle.Height = 0;
        IsEnterpriseMF.HeaderStyle.Height = 0;
        IsEnterpriseWS.HeaderStyle.Height = 0;
        IsEnterpriseSR.HeaderStyle.Height = 0;
        IsEnterpriseST.HeaderStyle.Height = 0;
        EnterpriseOTDesc.HeaderStyle.Height = 0;
        AgeLimit.HeaderStyle.Height = 0;
        RegisterAddrType.HeaderStyle.Height = 0;
        BusinessIncome.HeaderStyle.Height = 0;
        TotalAssets.HeaderStyle.Height = 0;
        NoGuaranty.HeaderStyle.Height = 0;
        IsMortgageHS.HeaderStyle.Height = 0;
        IsMortgageFB.HeaderStyle.Height = 0;
        IsMortgageME.HeaderStyle.Height = 0;
        MortgageOTDesc.HeaderStyle.Height = 0;
        IsHypoDR.HeaderStyle.Height = 0;
        IsHypoWR.HeaderStyle.Height = 0;
        IsHypoIP.HeaderStyle.Height = 0;
        IsHypoAR.HeaderStyle.Height = 0;
        IsHypoITP.HeaderStyle.Height = 0;
        HypoDesc.HeaderStyle.Height = 0;
        IsGuaranteeInd.HeaderStyle.Height = 0;
        IsGuaranteeEnt.HeaderStyle.Height = 0;
        IsGuaranteeCom.HeaderStyle.Height = 0;
        GuaranteeOTDesc.HeaderStyle.Height = 0;
        LoanAmountUpper.HeaderStyle.Height = 0;
        LoanAmountLower.HeaderStyle.Height = 0;
        LoanLimitUpper.HeaderStyle.Height = 0;
        LoanLimitLower.HeaderStyle.Height = 0;
        RepaymentType.HeaderStyle.Height = 0;
        ReviewDate.HeaderStyle.Height = 0;
        ProductDesc.HeaderStyle.Height = 0;
        ProductDoc.HeaderStyle.Height = 0;
        ProductDoc.HeaderStyle.Height = 0;

        ProductID.DataField = “ProductID”;
        BankName.DataField = “BankName”;
        ProductName.DataField = “ProductName”;
        IsServiceEnt.DataField = “IsServiceEnt”;
        IsServiceTrade.DataField = “IsServiceTrade”;
        IsServiceIndividual.DataField = “IsServiceIndividual”;
        ServiceOTDesc.DataField = “ServiceOTDesc”;
        IsIndividual.DataField = “IsIndividual”;
        IsEnterpriseMF.DataField = “IsEnterpriseMF”;
        IsEnterpriseWS.DataField = “IsEnterpriseWS”;
        IsEnterpriseSR.DataField = “IsEnterpriseSR”;
        IsEnterpriseST.DataField = “IsEnterpriseST”;
        EnterpriseOTDesc.DataField = “EnterpriseOTDesc”;
        AgeLimit.DataField = “AgeLimit”;
        RegisterAddrType.DataField = “RegisterAddrType”;
        BusinessIncome.DataField = “BusinessIncome”;
        TotalAssets.DataField = “TotalAssets”;
        NoGuaranty.DataField = “NoGuaranty”;
        IsMortgageHS.DataField = “IsMortgageHS”;
        IsMortgageFB.DataField = “IsMortgageFB”;
        IsMortgageME.DataField = “IsMortgageME”;
        MortgageOTDesc.DataField = “MortgageOTDesc”;
        IsHypoDR.DataField = “IsHypoDR”;
        IsHypoWR.DataField = “IsHypoWR”;
        IsHypoIP.DataField = “IsHypoIP”;
        IsHypoAR.DataField = “IsHypoAR”;
        IsHypoITP.DataField = “IsHypoITP”;
        HypoDesc.DataField = “HypoDesc”;
        IsGuaranteeInd.DataField = “IsGuaranteeInd”;
        IsGuaranteeEnt.DataField = “IsGuaranteeEnt”;
        IsGuaranteeCom.DataField = “IsGuaranteeCom”;
        GuaranteeOTDesc.DataField = “GuaranteeOTDesc”;
        LoanAmountUpper.DataField = “LoanAmountUpper”;
        LoanAmountLower.DataField = “LoanAmountLower”;
        LoanLimitUpper.DataField = “LoanLimitUpper”;
        LoanLimitLower.DataField = “LoanLimitLower”;
        RepaymentType.DataField = “RepaymentType”;
        ReviewDate.DataField = “ReviewDate”;
        ProductDesc.DataField = “ProductDesc”;
        ProductDoc.DataField = “ProductDoc”;
        ProductURL.DataField = “ProductURL”;

        dgd.Columns.Add(ProductID);
        dgd.Columns.Add(BankName);
        dgd.Columns.Add(ProductName);
        dgd.Columns.Add(IsServiceEnt);
        dgd.Columns.Add(IsServiceTrade);
        dgd.Columns.Add(IsServiceIndividual);
        dgd.Columns.Add(ServiceOTDesc);
        dgd.Columns.Add(IsIndividual);
        dgd.Columns.Add(IsEnterpriseMF);
        dgd.Columns.Add(IsEnterpriseWS);
        dgd.Columns.Add(IsEnterpriseSR);
        dgd.Columns.Add(IsEnterpriseST);
        dgd.Columns.Add(EnterpriseOTDesc);
        dgd.Columns.Add(AgeLimit);
        dgd.Columns.Add(RegisterAddrType);
        dgd.Columns.Add(BusinessIncome);
        dgd.Columns.Add(TotalAssets);
        dgd.Columns.Add(NoGuaranty);
        dgd.Columns.Add(IsMortgageHS);
        dgd.Columns.Add(IsMortgageFB);
        dgd.Columns.Add(IsMortgageME);
        dgd.Columns.Add(MortgageOTDesc);
        dgd.Columns.Add(IsHypoDR);
        dgd.Columns.Add(IsHypoWR);
        dgd.Columns.Add(IsHypoIP);
        dgd.Columns.Add(IsHypoAR);
        dgd.Columns.Add(IsHypoITP);
        dgd.Columns.Add(HypoDesc);
        dgd.Columns.Add(IsGuaranteeInd);
        dgd.Columns.Add(IsGuaranteeEnt);
        dgd.Columns.Add(IsGuaranteeCom);
        dgd.Columns.Add(GuaranteeOTDesc);
        dgd.Columns.Add(LoanAmountUpper);
        dgd.Columns.Add(LoanAmountLower);
        dgd.Columns.Add(LoanLimitUpper);
        dgd.Columns.Add(LoanLimitLower);
        dgd.Columns.Add(RepaymentType);
        dgd.Columns.Add(ReviewDate);
        dgd.Columns.Add(ProductDesc);
        dgd.Columns.Add(ProductDoc);
        dgd.Columns.Add(ProductURL);

        dgd.DataSource = dt.DefaultView;
        dgd.DataBind();
        if (dgd.Items.Count == 0)
        {

            throw (new Exception(“there is no data in dateTable”));
        }
        else
        {

            //根据excel样式，用table来编写其样式
            string elxStr = “<table border=’1px’ style=’text-align:center;vertical-align :middle ;font-weight :bold ; border-collapse :collapse ;’><tr> <td rowspan=’4′>序号</td><td rowspan=’4′>银行名称</td> <td rowspan=’4′>产品名称</td> <td rowspan=’3′ colspan=’4′>业务实质</td> <td colspan=’6′>贷款主体</td> <td rowspan=’4′>企业成立最短年限(不限选0)</td> <td rowspan=’4′>企业注册地</td><td rowspan=’4′>营业收入(万元)</td><td rowspan=’4′>企业总资产(万元)</td><td colspan=’15’>担保方式</td><td rowspan=’3′ colspan=’2′>额度（万元）</td><td rowspan=’3′ colspan=’2′>贷款期限（月）</td><td rowspan=’4′>还款方式</td><td rowspan=’4′>审批时间</td><td rowspan=’4′>产品特点（50字以内，内容通俗易懂）</td><td rowspan=’4′>申请所需特殊文件（50字以内）</td><td rowspan=’4′>产品链接或热线电话</td></tr><tr><td rowspan=’3′>个人</td><td rowspan=’2′ colspan=’5′>企业类型</td><td rowspan=’3′>不需要</td><td colspan=’14’>需要</td></tr><tr><td colspan=’4′>抵押</td><td colspan=’6′>质押</td><td colspan=’4′>保证</td></tr><tr><td>企业流动资金贷款</td><td>贸易融资</td><td>个人经营性贷款</td><td>其他(请注明)</td><td>制造业</td><td>批发、零售</td><td>服务业</td><td>科技型</td><td>其他(请说明)</td><td>商业房产</td><td>厂房</td><td>机器设备</td><td>其他(请注明)</td><td>存单</td><td>仓单</td><td>保单</td><td>应收账款</td><td>知识产权</td><td>其他(请注明)</td><td>个人连带责任担保</td><td>企业担保</td><td>担保公司担保</td><td>其他(请注明)</td><td>下限</td><td>上限</td><td>下限</td><td>上限</td></tr></table>”;

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.Charset = “gb2312”;
            HttpContext.Current.Response.AppendHeader(“Content-Disposition”, “attachment;filename=” + defaultFileName);
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding(“GBK”);
            //HttpContext.Current.Response.ContentEncoding = Encoding.UTF7;
            //设置输出流为简体中文,试了GBK会出现乱码
            HttpContext.Current.Response.ContentType = “application/ms-excel”;
            //设置输出文件类型为excel文件。

            CultureInfo myCItrad = new CultureInfo(“ZH-CN”, true);
            StringWriter oStringWriter = new StringWriter(myCItrad);
            Response.Write(elxStr);
            HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
            dgd.RenderControl(oHtmlTextWriter);
            HttpContext.Current.Response.Write(oStringWriter.ToString());
            HttpContext.Current.Response.End();
        }
    }

}