导出EXCEL

二、绑定服务器控件

protected void Button2_Click(object sender, System.EventArgs e)
        {
            //定义文档类型、字符编码
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "UTF-8";
            Response.AppendHeader("Content-Disposition", "attachment;filename=ReportProfit.xls");
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            Response.ContentType = "application/ms-excel";
            this.EnableViewState = false;
            System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
            //　定义一个输入流
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

            this.gridview.RenderControl(oHtmlTextWriter);
            //this 表示输出本页，你也可以绑定datagrid,或其他支持obj.RenderControl()属性的控件
            Response.Write(oStringWriter.ToString());
            Response.End();
        }

 

 

二、输出页面上的html中的table

function MoneyDebtForHostsQuery() {
    $("#hostcontent").html("loading");
    var url1 = "MoneyDebtForHosts.aspx";
    var data1 = "action=query&i_salername=" + escape($("#qSaler").val());
    //alert(data1);return;
    $.ajax({ url: url1, data: data1, type: "post", cache: false, error: function (XMLHttpRequest, textStatus) { alert(XMLHttpRequest.status + " " + textStatus); },
        success: function (msg) {
            msg = eval(msg);
            if (msg.Message != "OK") {
                alert(msg.Message);
            } else {

                var rs = msg.Records;
                var str = [];
                if (rs.length > 0) {
                    var options = "<option value=' '>全部</option>";
                    var trs = ['<table id="contentTable" border=0 cellspacing=1 cellspadding=0 ><tr id="head"><td id="rUSERID">商家ID</td><td id="rNICKNAME" >商家昵称</td><td id="rINALL" >税前总收入</td><td id="rTAX" >税率</td><td id="rPAYSUM0" >总到帐金额</td><td id="rPROFIT" >税后利润</td><td id="rPAYSUM0THISYEAR" >今年总到帐金额</td><td id="rPROFITTHISYEAR" >今年税后利润</td> <td id="rBAL" >商家余额</td><td id="rPAYSUM1" >预加金额</td> <td id="rSALERNAME" >市场人员</td><td>操作</td></tr>'];
                    for (var i = 0; i < rs.length; i++) {
                        var t = rs[i];
                        trs.push('<tr>');
                        trs.push('<td style="height:20px;">' + t.USERID + '</td>');
                        trs.push('<td>' + t.NICKNAME + '</td>');
                        trs.push('<td>' + t.INALL + '</td>');
                        trs.push('<td>' + t.TAX + '</td>');
                        trs.push('<td>' + t.PAYSUM0 + '</td>');
                        trs.push('<td>' + t.PROFIT + '</td>');
                       
                       
                        var tthisy = parseFloat(t.PAYSUM0THISYEAR);
                        tthisy = tthisy<0?0:tthisy;
                        trs.push('<td>' + tthisy + '</td>');
                        trs.push('<td>' + t.PROFITTHISYEAR + '</td>');
                       
                        trs.push('<td>' + t.BAL + '</td>');
                        trs.push('<td>' + t.PAYSUM1 + '</td>');
                        trs.push('<td>' + t.SALERNAME + '</td>');
                        trs.push('<td><a href="MoneyDebtForArrs.aspx?Hostid='+t.HOSTID+'&Userid='+t.USERID+'" target="mainFrame" class="link3">查看</a></td>');
                        trs.push('</tr>');
                        if (t.SALERNAME != "" && options.indexOf(">" + t.SALERNAME + "<") == -1)
                            options += "<option value='" + t.SALERNAME + "'>" + t.SALERNAME + "</option>";
                    };
                    trs.push('</table>');
                    $("#hostcontent").html(trs.join("")) ;
                    $("#TextBox1").val(escape(trs.join("").replace("<td>操作</td>","").replace(/\<a[^\^]*?\<\/a\>/g,""))) ;
                    

                    if (!salerInputAdded) {
                        $("#qSaler").html(options);
                        salerInputAdded = true;
                    }
                } else {
                }

            }
        }
    });
}

 

<asp:TextBox ID="TextBox1" runat="server" style="display:none"></asp:TextBox>

 

思路：将前台输出的table赋值给隐藏的服务器控件TextBox ，后台取TextBox值，进行输出

private void Excel(string s)//s即为前台的table表
    {
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "UTF-8";
        Response.AppendHeader("Content-Disposition", "attachment;filename=ReportDebt.xls");
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
        Response.ContentType = "application/ms-excel";
        this.EnableViewState = false;
        Response.Write(s);
        Response.End();
    }

三、输出固定格式的字符串

 public void TaobaoDataExcel()
    {
        string i_date1 = Common.Request("qClientGetTimeBegin", "1900-1-1");
        string i_date2 = Common.Request("qClientGetTimeEnd", "1900-1-1");
        string i_placeid = Common.Request("qPLACEID", "-1");
        string i_shopname = Common.Request("qShopname", " ");
        string i_websitename = Common.Request("qWebsitename", " ");
        string i_webuserid = Common.Request("qWebuserid", " ");
        string i_pageIndex = Common.Request("i_pageIndex", "0");
        string i_pageSize = Common.Request("i_pageSize", "30");
        i_placeid = i_placeid == "" ? "-1" : i_placeid;

        OracleParameter p1 = new OracleParameter("i_date1", i_date1);
        OracleParameter p2 = new OracleParameter("i_date2", i_date2);
        OracleParameter p3 = new OracleParameter("i_placeid", i_placeid);
        OracleParameter p8 = new OracleParameter("i_shopname", i_shopname);
        OracleParameter p6 = new OracleParameter("i_websitename", i_websitename);
        OracleParameter p7 = new OracleParameter("i_webuserid", i_webuserid);
        OracleParameter p4 = new OracleParameter("i_pageIndex", i_pageIndex);
        OracleParameter p5 = new OracleParameter("i_pageSize", i_pageSize);
        OracleParameter[] paramList = new OracleParameter[] { p1, p2, p3, p8, p6, p7, p4, p5 };
        DataTable dt = DataOperator.GetDataWithCountAndCursorDT("MG_Report.TaobaoData", paramList);

        if (dt.Rows.Count > 60000)
        {
            Response.Write("数据超过6万，请增加删选条件");
            Response.End();
        }
        else
        {
            string s = "交易号\t商品标题\t店铺名称\t成交时间\t成交数量\t成交价格\t实际支付\t佣金比率\t佣金\t广告位\t网站\t会员\n";
            foreach (DataRow row in dt.Rows)
            {
                s += row["trade_id"].ToString() + "\t";
                s += row["item_title"].ToString() + "\t";
                s += row["shop_title"].ToString() + "\t";
                s += row["pay_time"].ToString() + "\t";
                s += row["item_num"].ToString() + "\t";
                s += row["pay_price"].ToString() + "\t";
                s += row["real_pay_fee"].ToString() + "\t";
                s += row["commission_rate"].ToString() + "\t";
                s += row["commission"].ToString() + "\t";
                s += row["outer_code"].ToString() + "\t";
                s += row["websitename"].ToString() + "\t";
                s += row["webuserid"].ToString() + "\t";
                s += "\n";
            }

            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "UTF-8";
            Response.AppendHeader("Content-Disposition", "attachment;filename=TaobaoData.xls");
            Response.ContentEncoding = System.Text.Encoding.Default;
            Response.ContentType = "application/ms-excel";
            this.EnableViewState = false;
            Response.Write(s);
            Response.End();
        }
    }

四、输出table数据流

/// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="m_DataSet"></param>
        /// <param name="fileName"></param>
        public void CreateExcel(DataSet m_DataSet, string fileName)
        {
            string strLine;
            FileStream objFileStream;
            StreamWriter objStreamWriter;
            Random nRandom = new Random(DateTime.Now.Millisecond);

            objFileStream = new FileStream(Server.MapPath(fileName), FileMode.OpenOrCreate, FileAccess.Write);
            objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode);

            if (m_DataSet != null)
            {
                if (m_DataSet.Tables.Count > 0)
                {
                    strLine = "";
                    for (int i = 0; i <= m_DataSet.Tables[0].Columns.Count - 1; i++)
                    {
                        strLine = strLine + m_DataSet.Tables[0].Columns[i].ToString() + Convert.ToChar(9);
                    }
                    objStreamWriter.WriteLine(strLine);

                    strLine = "";
                    for (int j = 0; j <= m_DataSet.Tables[0].Rows.Count - 1; j++)
                    {
                        for (int i = 0; i <= m_DataSet.Tables[0].Columns.Count - 1; i++)
                        {
                            strLine = strLine + m_DataSet.Tables[0].Rows[j][i].ToString() + Convert.ToChar(9);
                        }
                        objStreamWriter.WriteLine(strLine);
                        strLine = "";
                    }

                    objStreamWriter.Close();
                    objFileStream.Close();
                }
            }
        }