下载Excel

public class downCST : IHttpHandler, System.Web.SessionState.IRequiresSessionState
{

    public void ProcessRequest(HttpContext context)
    {

        context.Response.Expires = 0;   //0立即产生新的请求   -1 立即过期
        context.Response.Clear();   //清除缓冲区内容
        context.Response.Buffer = true;   //是否被缓冲 ，直至response.flush
        context.Response.Charset = "utf-8";
        context.Response.ContentEncoding = System.Text.Encoding.UTF8;
        context.Response.ContentType = "application/vnd.ms-<a href='http://www.it165.net/edu/ebg/' target='_blank' class='keylink'>excel</a>";  //内容类型
        //设定档名可为中文_#1

        string action = context.Request["action"].ToString();
        switch (action)
        {
            case "crm":
                crmLoad(context);
                break;
            default:
                break;
        }
    }

    ysdDB db = new ysdDB();

    /// <summary>
    /// 客户信息下载
    /// </summary>
    /// <param name="context"></param>
    public void crmLoad(HttpContext context)
    {

        string name = "客户信息" + DateTime.Now.ToString().Replace('-', ' ').Replace(':', ' ');
        string cstID = context.Request["downID"].ToString();
        context.Response.AddHeader("Content-Disposition", "attachment;filename="" + HttpUtility.UrlEncode(name, System.Text.Encoding.UTF8) + ".xls"");   //识别文件类型
        #region 条件查询
        System.Text.StringBuilder sql = new System.Text.StringBuilder();
        sql.Append("SELECT ysd_crm_cst.cstID,");

        sql.Append("ysd_crm_cst.CompanyName,    ");

        sql.Append("ysd_crm_cst.conName,      ");
        sql.Append("ysd_crm_cst.phone,       ");
        sql.Append("ysd_crm_cst.fax,         ");
        //sql.Append("ysd_crm_cst.entpCtgry,   ");
        //sql.Append("ysd_crm_cst.region,      ");
        sql.Append("Convert(varchar(10),(    SELECT dicName    FROM ysd_sys_Cate    WHERE dicID=entpCtgry),120) AS entpCtgryName,");
        sql.Append("Convert(varchar(10),(    SELECT dicName    FROM ysd_sys_Cate    WHERE dicID=region),120) AS regionName,      ");
        sql.Append("ysd_crm_cst.address,  ");
        sql.Append("ysd_crm_cst.email,    ");

        sql.Append("ysd_crm_cst.QQWW,     ");
        sql.Append("ysd_crm_cst.bankName, ");
        sql.Append("ysd_crm_cst.bankID,   ");

        sql.Append("ysd_crm_cst.entpSize, ");

        //sql.Append("ysd_crm_cst.cstCls,   ");
        //sql.Append("ysd_crm_cst.cstTyp,   ");
        //sql.Append("ysd_crm_cst.cstSts,   ");
        sql.Append("ysd_crm_cst.memo,     ");
        //sql.Append("ysd_crm_cst.optEmpID, ");
        sql.Append("Convert(varchar(10),(SELECT empName    FROM ysd_bas_Emp    WHERE empID=ownerEmpID),120) AS ownerEmpName,  ");
        sql.Append("Convert(varchar(10),(    SELECT dicName    FROM ysd_sys_Cate    WHERE dicID=cstCls),120) AS cstClsName,        ");
        sql.Append("Convert(varchar(10),(    SELECT dicName    FROM ysd_sys_Cate    WHERE dicID=cstTyp),120) AS cstTypName,        ");
        sql.Append("Convert(varchar(10),(    SELECT dicName    FROM ysd_sys_Cate    WHERE dicID=cstSts),120) AS cstStsName,        ");
        sql.Append("Convert(varchar(10),(    SELECT empName    FROM ysd_bas_Emp    WHERE empID=optEmpID),120) AS optEmpName,      ");
        sql.Append("Convert(varchar(10),ysd_crm_cst.optDt,120) AS optDt,                                                      ");
        //sql.Append("ysd_crm_cst.ownerEmpID,                                                                                   ");
        sql.Append("Convert(varchar(10),ysd_crm_cst.lstConDt,120) AS lstConDt,                                                ");
        sql.Append("Convert(varchar(10),(    SELECT dicName    FROM ysd_sys_Cate    WHERE dicID=flagPartner),120) AS flagPartner ");
        sql.Append("FROM ysd_crm_cst ");
        sql.Append(" WHERE (1=1)      ");
        sql.Append("AND flagDel=1 and ownerEmpID=" + context.Session["empID"].ToString() + "  and   cstID in  ");
        sql.Append(" (" + cstID.Trim(',') + ") ");
        System.Data.DataTable dt = db.GetData(sql.ToString());
        #endregion
        dt.Columns["cstID"].ColumnName = "编号";
        dt.Columns["CompanyName"].ColumnName = "公司名称";
        dt.Columns["conName"].ColumnName = "联系人";
        dt.Columns["phone"].ColumnName = "联系电话";
        dt.Columns["fax"].ColumnName = "传真";
        dt.Columns["entpCtgryName"].ColumnName = "客户行业";
        dt.Columns["regionName"].ColumnName = "地区";
        dt.Columns["address"].ColumnName = "详细地址";
        dt.Columns["email"].ColumnName = "邮箱";
        dt.Columns["QQWW"].ColumnName = "QQ";
        dt.Columns["bankName"].ColumnName = "开户银行";
        dt.Columns["bankID"].ColumnName = "银行帐号";
        dt.Columns["entpSize"].ColumnName = "企业规模";
        dt.Columns["ownerEmpName"].ColumnName = "拥有人";
        dt.Columns["cstClsName"].ColumnName = "客户等级";
        dt.Columns["cstTypName"].ColumnName = "客户类别";
        dt.Columns["cstStsName"].ColumnName = "客户阶段";
        dt.Columns["optEmpName"].ColumnName = "录入人";
        dt.Columns["optDt"].ColumnName = "录入时间";
        dt.Columns["lstConDt"].ColumnName = "最后联系时间";
        dt.Columns["flagPartner"].ColumnName = "是否正式客户";
     
        string ex = "";
        int tRowCount = dt.Rows.Count;
        int tColumnCount = dt.Columns.Count;


        //引用这三个xmlns
        ex += "<html xmlns:o='urn:schemas-microsoft-com:office:office'";
        ex += "xmlns:x='urn:schemas-microsoft-com:office:<a href="http://www.it165.net/edu/ebg/" target="_blank" class="keylink">excel</a>'";
        ex += "xmlns='http://www.w3.org/TR/REC-html40'>";

        ex += "<meta http-equiv=Content-Type content=text/html;charset=utf-8>";

        //在head中加入xml定义
        ex += "
 <head>";
        ex += "
 <xml>";
        ex += "
 <x:ExcelWorkbook>";
        ex += "
 <x:ExcelWorksheets>";
        ex += "
 <x:ExcelWorksheet>";
        //设定此Worksheet名称_#2
        ex += "
 <x:Name>" + name + "</x:Name>";

        //以下针对此工作表进行属性设定
        ex += "
 <x:WorksheetOptions> ";
        ex += "
 <x:FrozenNoSplit/>";

        //设定冻结行号_#3
        ex += "
 <x:SplitHorizontal>1</x:SplitHorizontal>";

        //设定起始行号(TopRowBottomPane)_#4
        ex += "
 <x:TopRowBottomPane>2</x:TopRowBottomPane>";
        ex += "
 <x:ActivePane>2</x:ActivePane>";
        ex += "
 </x:WorksheetOptions>";
        ex += "
 </xml>";
        ex += "
 </head>";

        ex += "
 <body>";
        ex += "<Table borderColor=black border=1>";
        ex += "
 <TR>";

        //塞入head
        for (int i = 0; i < tColumnCount; i++)
        {
            //设定head的背景色_#5
            ex += "
 <TD  bgcolor = #fff8dc>";
            ex += dt.Columns[i].ColumnName;
            ex += "
 </TD>";
        }

        ex += "
 </TR>";

        //塞入每一笔资料
        for (int j = 0; j < tRowCount; j++)
        {
            ex += "
 <TR>";
            for (int k = 0; k < tColumnCount; k++)
            {


                ex += "
 <TD>";
                ex += dt.Rows[j][k].ToString();
                ex += "
 </TD>";

            }
            ex += "
 </TR>";
        }

        ex += "</Table>";
        ex += "</body>";
        ex += "</html>";
        context.Response.Write(ex);
        context.Response.End();

    }
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}

                    转载于 忘了