效果图：
前台使用了gridview控件：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="E_CKBB.aspx.cs" Inherits="DRP.sales.E_CKBB" %>
<!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>
<meta http-equiv='content-type' content='application/ms-excel;charset=GB2312' />
<script src="../js/jquery-1.4.1.js" type="text/javascript"></script>
<script src="../js/formValidator-4.1.1.js" type="text/javascript"></script>
<script src="../js/formValidatorRegex.js" type="text/javascript"></script>
<script src="../js/DateTimeMask.js" type="text/javascript"></script>
<script src="../js/datepicker/WdatePicker.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function() {
//开始时间
$("#txtzdrq1").focus(function() { WdatePicker({ skin: 'ext', dateFmt: 'yyyy-M-d H:mm:ss', maxDate: '%y-%M-%d', oncleared: function() { $(this).blur(); }, onpicked: function() { $(this).blur(); } }) });
//结束时间
$("#txtzdrq2").focus(function() { WdatePicker({ skin: 'ext', dateFmt: 'yyyy-M-d H:mm:ss', maxDate: '%y-%M-%d', oncleared: function() { $(this).blur(); }, onpicked: function() { $(this).blur(); } }) });
});
</script>
</head>
<body>
<form runat="server">
<div>
<table>
<tr algin="center">
<td>
选择时间：
</td>
<td>
<asp:DropDownList runat="server" Width="175px" Height="22px">
<asp:ListItem Value="1">付款时间</asp:ListItem>
<asp:ListItem Value="2">发货时间</asp:ListItem>
</asp:DropDownList>
</td>
<td>
收货单位：
</td>
<td>
<asp:TextBox runat="server"></asp:TextBox>
</td>
<td>
仓库名称：
</td>
<td>
<asp:TextBox runat="server"></asp:TextBox>
</td>
<td>
参考单号：
</td>
<td>
<asp:TextBox runat="server"></asp:TextBox>
</td>
</tr>
<tr algin="center" >
<td>
商品名称：
</td>
<td>
<asp:TextBox runat="server"></asp:TextBox>
</td>
<td>
商品代码：
</td>
<td>
<asp:TextBox runat="server"></asp:TextBox>
</td>
<td>
开始日期
</td>
<td>
<asp:TextBox runat="server"></asp:TextBox>
</td>
<td>
截止日期：
</td>
<td>
<asp:TextBox runat="server"></asp:TextBox>
</td>
<td>
<asp:Button runat="server" Text="查询" onclick="Button1_Click"/>
</td>
<td>
<asp:Button runat="server" Text="导出execl"
onclick="Button2_Click" />
</td>
</tr>
</table>
<asp:GridView runat="server" AutoGenerateColumns="False"
PageSize="200" AllowPaging="True" HorizontalAlign="NotSet" Width="1600px"
onpageindexchanging="gevCKBB_PageIndexChanging">
<HeaderStyle HorizontalAlign="Center" BackColor="SkyBlue"></HeaderStyle>
<Columns>
<asp:TemplateField HeaderText="序号">
<ItemTemplate>
<%#Container.DataItemIndex+1%>
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" Height=""></ItemStyle>
</asp:TemplateField>
<asp:BoundField DataField="DJBH" HeaderText="销售单号" />
<asp:BoundField DataField="CKDH" HeaderText="订单单号" />
<asp:BoundField DataField="SHDW" HeaderText="收货单位" />
<asp:BoundField DataField="HPDM" HeaderText="货品代码" />
<asp:BoundField DataField="HPMC" HeaderText="货品名称" />
<asp:BoundField DataField="SL" HeaderText="数量" />
<asp:BoundField DataField="CKMC" HeaderText="仓库名称" />
<asp:BoundField DataField="ZFJE" HeaderText="实付金额" />
<asp:BoundField DataField="ZCB" HeaderText="总成本" />
<asp:BoundField DataField="MLR" HeaderText="毛利润" />
<asp:BoundField DataField="PayTime" HeaderText="付款时间" />
<asp:BoundField DataField="FHSJ" HeaderText="发货时间" />
</Columns>
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<PagerTemplate>
<br />
<asp:Label runat="server" Text='<%#"第" + (((GridView)Container.NamingContainer).PageIndex + 1) + "页/共" + (((GridView)Container.NamingContainer).PageCount) + "页" %> '></asp:Label>
<asp:LinkButton runat="Server" Text="首页" Enabled='<%# ((GridView)Container.NamingContainer).PageIndex != 0 %>'
CommandName="Page" CommandArgument="First"></asp:LinkButton>
<asp:LinkButton runat="server" Text="上一页" Enabled='<%# ((GridView)Container.NamingContainer).PageIndex != 0 %>'
CommandName="Page" CommandArgument="Prev"></asp:LinkButton>
<asp:LinkButton runat="Server" Text="下一页" Enabled='<%# ((GridView)Container.NamingContainer).PageIndex != (((GridView)Container.NamingContainer).PageCount - 1) %>'
CommandName="Page" CommandArgument="Next"></asp:LinkButton>
<asp:LinkButton runat="Server" Text="尾页" Enabled='<%# ((GridView)Container.NamingContainer).PageIndex != (((GridView)Container.NamingContainer).PageCount - 1) %>'
CommandName="Page" CommandArgument="Last"></asp:LinkButton>
</PagerTemplate>
</asp:GridView>
</div>
</form>
</body>
</html>
后台代码：
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.IO;
namespace DRP.sales
{
public partial class E_CKBB : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
txtzdrq1.Text = DateTime.Now.AddDays(-6).ToString("yyyy-M-d 00:00:00");
txtzdrq2.Text = DateTime.Now.ToString("yyyy-M-d HH:mm:ss");
this.gevCKBB.Visible = false;
}
}
public void BindList()
{
string sql = "";
decimal EMAIL1 = 0.0m;
DataTable dt = null;
if (DropDownList1.SelectedValue == "1")
{
sql = @"SELECT zb.djbh AS 单据编号 ,zb.ddbh AS 参考单号,kh.khmc AS 收货单位,
mxb.hpdm AS 货品代码,hp.hpmc AS 货品名称, mxb.sl AS 数量 ,ck.ckmc AS 仓库名称,
mxb.jexj AS 实付金额,mxb.stockjg*mxb.sl as 总成本,his.pay_time AS 付款时间, tz.dzsj AS 发货时间
FROM dbo.tb_s_xsfhtzdzb zb
INNER JOIN tb_s_xsfhmxb mxb ON zb.djbh=mxb.djbh
LEFT JOIN dbo.v_m_khdm kh ON kh.khdm=zb.dhdw
LEFT JOIN dbo.TBTrade_History his ON his.tid=zb.ddbh
left join tb_i_kctz tz on tz.djbh=zb.djbh AND tz.hpdm=mxb.hpdm
INNER JOIN dbo.tb_m_ckdm ck ON ck.ckdm=zb.fhck
LEFT JOIN dbo.tb_m_hpdm hp ON hp.hpdm=tz.hpdm
WHERE 1=1 AND
ck.ckmc IN('深圳爱库零售仓','深圳不良品仓','深圳爱库批发仓','北京批发仓库') AND zb.djzt='4'
and kh.khmc like '%" + shdw.Text.Trim().ToString() + "%' and ck.ckmc like '%" + ckmc.Text.Trim().ToString() + "%' and zb.ddbh like '%" + ckdh.Text.Trim().ToString() + "%' and hp.hpmc like '%" + spmc.Text.Trim().ToString() + "%' and mxb.hpdm like '%" + hpdm.Text.Trim().ToString() + "%' and his.pay_time between '" + txtzdrq1.Text.Trim() + "' and '" + txtzdrq2.Text.Trim() + "'";
}
if (DropDownList1.SelectedValue == "2")
{
sql = @"SELECT zb.djbh AS 单据编号 ,zb.ddbh AS 参考单号,kh.khmc AS 收货单位,
mxb.hpdm AS 货品代码,hp.hpmc AS 货品名称, mxb.sl AS 数量 ,ck.ckmc AS 仓库名称,
mxb.jexj AS 实付金额,mxb.stockjg*mxb.sl as 总成本,his.pay_time AS 付款时间, tz.dzsj AS 发货时间
FROM dbo.tb_s_xsfhtzdzb zb
INNER JOIN tb_s_xsfhmxb mxb ON zb.djbh=mxb.djbh
LEFT JOIN dbo.v_m_khdm kh ON kh.khdm=zb.dhdw
LEFT JOIN dbo.TBTrade_History his ON his.tid=zb.ddbh
left join tb_i_kctz tz on tz.djbh=zb.djbh AND tz.hpdm=mxb.hpdm
INNER JOIN dbo.tb_m_ckdm ck ON ck.ckdm=zb.fhck
LEFT JOIN dbo.tb_m_hpdm hp ON hp.hpdm=tz.hpdm
WHERE 1=1 AND
ck.ckmc IN('深圳爱库零售仓','深圳不良品仓','深圳爱库批发仓','北京批发仓库') AND zb.djzt='4'
and kh.khmc like '%" + shdw.Text.Trim().ToString() + "%' and ck.ckmc like '%" + ckmc.Text.Trim().ToString() + "%' and zb.ddbh like '%" + ckdh.Text.Trim().ToString() + "%' and hp.hpmc like '%" + spmc.Text.Trim().ToString() + "%' and mxb.hpdm like '%" + hpdm.Text.Trim().ToString() + "%' and tz.dzsj between '" + txtzdrq1.Text.Trim() + "' and '" + txtzdrq2.Text.Trim() + "'";
}
dt = IFACE.DBUtility.DbHelperSQL.QueryDT(sql);
if (dt != null && dt.Rows.Count > 0)
{
List<CKBB> CK = new List<CKBB>();
foreach (DataRow dr in dt.Rows)
{
CKBB BB = new CKBB();
BB.DJBH = dr[0].ToString();
BB.CKDH = dr[1].ToString();
BB.SHDW = dr[2].ToString();
BB.HPDM = dr[3].ToString();
BB.HPMC = dr[4].ToString();
BB.SL = int.Parse(dr[5].ToString());
BB.CKMC = dr[6].ToString();
BB.ZFJE = decimal.Parse(dr[7].ToString());
BB.ZCB =decimal.Parse(dr[8].ToString());
BB.MLR = BB.ZFJE - BB.ZCB;//毛利润
BB.PayTime = DateTime.Parse(dr[9].ToString());
BB.FHSJ = DateTime.Parse(dr[10].ToString());
CK.Add(BB);
}
gevCKBB.DataSource = CK;
gevCKBB.DataBind();
}
else
{
this.gevCKBB.Visible = false;
}
}
//定义一个类
public class CKBB
{
public string DJBH { get; set; }//单据编号
public string CKDH { get; set; }//参考单号
public string SHDW { get; set; }//收货单位
public string HPDM { get; set; }//货品代码
public string HPMC { get; set; }//货品名称
public int SL { get; set; }//数量
public string CKMC { get; set; }//仓库名称
public decimal ZFJE { get; set; }//支付金额
public decimal ZCB { get; set; }//总成本
public decimal MLR { get; set; }//毛利润
public DateTime PayTime { get; set; }//付款时间
public DateTime FHSJ { get; set; }//发货时间
}
protected void Button1_Click(object sender, EventArgs e)
{
this.gevCKBB.Visible = true;
if (txtzdrq1.Text == "*")
{
txtzdrq1.Text = "";
}
if (txtzdrq2.Text == "*")
{
txtzdrq2.Text = "";
}
if (txtzdrq1.Text != "")
{
if (Convert.ToDateTime(txtzdrq1.Text) > DateTime.Now)
{
Response.Write("<script>alert('开始日期不能大于当前日期')</script>");
return;
}
}
if (txtzdrq2.Text != "" && txtzdrq2.Text != "")
{
if (Convert.ToDateTime(txtzdrq1.Text) > Convert.ToDateTime(txtzdrq2.Text))
{
Response.Write("<script>alert('开始日期不能大于结束日期')</script>");
return;
}
}
BindList();
}
protected void gevCKBB_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gevCKBB.PageIndex = e.NewPageIndex;
BindList();
}
//导出execl数据
protected void Button2_Click(object sender, EventArgs e)
{
//导出全部数据，取消分页
gevCKBB.AllowPaging = false;
gevCKBB.ShowFooter = false;
BindList();
Response.Clear();
Response.Buffer = true;
Response.Charset = "utf-8";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("深圳发货报表" + System.DateTime.Now.Date.ToString("yyyyMMdd")) + ".xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");//设置输出流为简体中文
Response.ContentType = "application/ms-excel";//设置输出文件类型为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);
ClearControls(gevCKBB);
this.gevCKBB.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
//还原分页显示
gevCKBB.AllowPaging = true;
gevCKBB.ShowFooter = true;
BindList();
}
/// <summary>
/// 清除控件中的所有控件，以便导出Excel
/// </summary>
/// <param name="control"></param>
private void ClearControls(Control control)
{
for (int i = control.Controls.Count - 1; i >= 0; i--)
{
ClearControls(control.Controls[i]);
}
if (!(control is TableCell))
{
if (control.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
try
{
literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control, null);
}
catch
{
}
control.Parent.Controls.Remove(control);
}
else if (control.GetType().GetProperty("Text") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control, null);
control.Parent.Controls.Remove(control);
}
}
return;
}
/// <summary>
/// 这个重写是必须的
/// </summary>
/// <param name="control"></param>
public override void VerifyRenderingInServerForm(Control control) { }
}
}