.NET ASP.NET最简单的DataTable数据生成Excel表格导出

如图：交易明细表PayExnum
导出交易明细为Excel，导出click事件

 protected void DaoChu_Click(object sender, EventArgs e)
{
    DataTable showpay = ViewState["showpayexnum"] as DataTable;//要导出的数据
    if (showpay.Rows.Count> 0)//判断要导出的数据是否为空
    {
        StringWriter sw = new StringWriter();
        sw.WriteLine("系统ID\t姓名\t订单编号\t支付金额\t支付类型\t支付交易号\t支付时间\t退款单号\t退款时间\t缴费状态\t认证状态");//表头
        foreach (DataRow dr in showpay.Rows)//循环数据
        {
            Member member = memberbll.GetModel(int.Parse(dr["MemID"].ToString()));//MemID会员ID，通过会员id查询会员
            string paytype = string.Empty;
            if (dr["PayType"].ToString() == "0")//PayType字段支付类型，0：支付宝；1：微信。
            {
                paytype = "支付宝";
            }
            else
            {
                paytype = "微信";
            }
            string costpay = string.Empty;
            if (dr["CostPay"].ToString() == "0")//CostPay字段缴费状态，0：未缴费；1：已缴费；2：已退款。
            {
                costpay = "未缴费";
            }
            else if(dr["CostPay"].ToString()=="1")
            {
                costpay = "已缴费";
            }
            else
            {
                costpay = "已退款";
            }
            string audit = string.Empty;
            if (member.Aduit == 0)//Aduit 会员表字段Aduit认证状态，0：未认证；1：已认证。
            {
                audit = "未认证";
            }
            else
            {
                audit = "已认证";
            }
            if (dr["RefundID"] == null || dr["Refundtime"] == null)//RefundID退款单号，Refundtime退款时间，为空的话写入空字符串
            {
                sw.WriteLine(member.MemID + "\t" + dr["MemName"] + "\t" +"=\""+ dr["OrderNo"] +"\""+ "\t" + dr["Money"] + "\t" + paytype + "\t" +"=\""+ dr["Paynumber"] +"\""+ "\t" +"=\""+ dr["Paytime"] +"\""+ "\t" + "" + "\t" + "" + "\t" + costpay + "\t" + audit);//如订单编号，交易号，退款单号和时间，输出到Excel显示########，拉大才显示全，所有用=“”括起来
            }
            else
            {
                sw.WriteLine(member.MemID + "\t" + dr["MemName"] + "\t" +"=\""+ dr["OrderNo"] +"\""+ "\t" + dr["Money"] + "\t" + paytype + "\t" +"=\""+ dr["Paynumber"] +"\""+ "\t" +"=\""+ dr["Paytime"] +"\""+ "\t" +"=\""+ dr["RefundID"] +"\""+ "\t" +"=\""+ dr["Refundtime"] +"\""+ "\t" + costpay + "\t" + audit);
            }
        }
        sw.Close();
        Response.AddHeader("Content-Disposition", "attachment; filename=交易明细.xls");//filename输出的文件名称，Excel文件后缀.xls,.xlsx等
        Response.ContentType = "application/ms-excel";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.Write(sw);
        Response.End();
    }
    else//没有数据弹框提示并刷新页面数据
    {
        helpclass.PageAlert("导出的数据为空！",Page);//helpclass帮助类调用弹框方法
        ViewState["where"] = "CostPay!=0"; ViewState["sid"] = 0; ViewState["aid"] = 0;//select查询条件
        NewMethod();//刷新页面数据
    }
}