程序代码:<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="default.aspx.cs" Inherits="cangku._default" %>
<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="cc1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.">
<html xmlns="http://www." >
<head runat="server">
<title></title>
<style type="text/css">
.style1
{
font: normal small-caps bold 100% serif;
background: #FFFFFF;
text-align: left;
color: #0000FF;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
<ContentTemplate>
<div class="style1">
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
倉 庫 對 點 明 細<br />
<br />
開始時間:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<cc1:CalendarExtender ID="TextBox1_CalendarExtender" runat="server"
Enabled="True" Format="yyyy-MM-dd" TargetControlID="TextBox1">
</cc1:CalendarExtender>
<asp:DropDownList ID="DropDownList1" runat="server" Height="22px"
style="margin-top: 0px" Width="42px">
<asp:ListItem>00</asp:ListItem>
<asp:ListItem>01</asp:ListItem>
<asp:ListItem>02</asp:ListItem>
<asp:ListItem>03</asp:ListItem>
<asp:ListItem>04</asp:ListItem>
<asp:ListItem>05</asp:ListItem>
<asp:ListItem>06</asp:ListItem>
<asp:ListItem>07</asp:ListItem>
<asp:ListItem>08</asp:ListItem>
<asp:ListItem>09</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
<asp:ListItem>13</asp:ListItem>
<asp:ListItem>14</asp:ListItem>
<asp:ListItem>15</asp:ListItem>
<asp:ListItem>16</asp:ListItem>
<asp:ListItem>17</asp:ListItem>
<asp:ListItem>18</asp:ListItem>
<asp:ListItem>19</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>21</asp:ListItem>
<asp:ListItem>22</asp:ListItem>
<asp:ListItem>23</asp:ListItem>
</asp:DropDownList>
點<asp:DropDownList ID="DropDownList2" runat="server" Height="22px" Width="42px">
<asp:ListItem>00</asp:ListItem>
<asp:ListItem>01</asp:ListItem>
<asp:ListItem>02</asp:ListItem>
<asp:ListItem>03</asp:ListItem>
<asp:ListItem>04</asp:ListItem>
<asp:ListItem>05</asp:ListItem>
<asp:ListItem>06</asp:ListItem>
<asp:ListItem>07</asp:ListItem>
<asp:ListItem>08</asp:ListItem>
<asp:ListItem>09</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
<asp:ListItem>13</asp:ListItem>
<asp:ListItem>14</asp:ListItem>
<asp:ListItem>15</asp:ListItem>
<asp:ListItem>16</asp:ListItem>
<asp:ListItem>17</asp:ListItem>
<asp:ListItem>18</asp:ListItem>
<asp:ListItem>19</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>21</asp:ListItem>
<asp:ListItem>22</asp:ListItem>
<asp:ListItem>23</asp:ListItem>
<asp:ListItem>24</asp:ListItem>
<asp:ListItem>25</asp:ListItem>
<asp:ListItem>26</asp:ListItem>
<asp:ListItem>27</asp:ListItem>
<asp:ListItem>28</asp:ListItem>
<asp:ListItem>29</asp:ListItem>
<asp:ListItem>30</asp:ListItem>
<asp:ListItem>31</asp:ListItem>
<asp:ListItem>32</asp:ListItem>
<asp:ListItem>33</asp:ListItem>
<asp:ListItem>34</asp:ListItem>
<asp:ListItem>35</asp:ListItem>
<asp:ListItem>36</asp:ListItem>
<asp:ListItem>37</asp:ListItem>
<asp:ListItem>38</asp:ListItem>
<asp:ListItem>39</asp:ListItem>
<asp:ListItem>40</asp:ListItem>
<asp:ListItem>41</asp:ListItem>
<asp:ListItem>42</asp:ListItem>
<asp:ListItem>43</asp:ListItem>
<asp:ListItem>44</asp:ListItem>
<asp:ListItem>45</asp:ListItem>
<asp:ListItem>46</asp:ListItem>
<asp:ListItem>47</asp:ListItem>
<asp:ListItem>48</asp:ListItem>
<asp:ListItem>49</asp:ListItem>
<asp:ListItem>50</asp:ListItem>
<asp:ListItem>51</asp:ListItem>
<asp:ListItem>52</asp:ListItem>
<asp:ListItem>53</asp:ListItem>
<asp:ListItem>54</asp:ListItem>
<asp:ListItem>55</asp:ListItem>
<asp:ListItem>56</asp:ListItem>
<asp:ListItem>57</asp:ListItem>
<asp:ListItem>58</asp:ListItem>
<asp:ListItem>59</asp:ListItem>
</asp:DropDownList>
分
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="TextBox1" ErrorMessage="開始日期不能為空" Font-Bold="False"
BorderStyle="Dotted"></asp:RequiredFieldValidator>
<br />
<br />
結束時間:<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<cc1:CalendarExtender ID="TextBox2_CalendarExtender" runat="server"
Enabled="True" Format="yyyy-MM-dd" TargetControlID="TextBox2">
</cc1:CalendarExtender>
<asp:DropDownList ID="DropDownList3" runat="server" Height="22px" Width="42px">
<asp:ListItem>00</asp:ListItem>
<asp:ListItem>01</asp:ListItem>
<asp:ListItem>02</asp:ListItem>
<asp:ListItem>03</asp:ListItem>
<asp:ListItem>04</asp:ListItem>
<asp:ListItem>05</asp:ListItem>
<asp:ListItem>06</asp:ListItem>
<asp:ListItem>07</asp:ListItem>
<asp:ListItem>08</asp:ListItem>
<asp:ListItem>09</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
<asp:ListItem>13</asp:ListItem>
<asp:ListItem>14</asp:ListItem>
<asp:ListItem>15</asp:ListItem>
<asp:ListItem>16</asp:ListItem>
<asp:ListItem>17</asp:ListItem>
<asp:ListItem>18</asp:ListItem>
<asp:ListItem>19</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>21</asp:ListItem>
<asp:ListItem>22</asp:ListItem>
<asp:ListItem>23</asp:ListItem>
</asp:DropDownList>
點<asp:DropDownList ID="DropDownList4" runat="server" Height="22px" Width="42px">
<asp:ListItem>00</asp:ListItem>
<asp:ListItem>01</asp:ListItem>
<asp:ListItem>02</asp:ListItem>
<asp:ListItem>03</asp:ListItem>
<asp:ListItem>04</asp:ListItem>
<asp:ListItem>05</asp:ListItem>
<asp:ListItem>06</asp:ListItem>
<asp:ListItem>07</asp:ListItem>
<asp:ListItem>08</asp:ListItem>
<asp:ListItem>09</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
<asp:ListItem>13</asp:ListItem>
<asp:ListItem>14</asp:ListItem>
<asp:ListItem>15</asp:ListItem>
<asp:ListItem>16</asp:ListItem>
<asp:ListItem>17</asp:ListItem>
<asp:ListItem>18</asp:ListItem>
<asp:ListItem>19</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>21</asp:ListItem>
<asp:ListItem>22</asp:ListItem>
<asp:ListItem>23</asp:ListItem>
<asp:ListItem>24</asp:ListItem>
<asp:ListItem>25</asp:ListItem>
<asp:ListItem>26</asp:ListItem>
<asp:ListItem>27</asp:ListItem>
<asp:ListItem>28</asp:ListItem>
<asp:ListItem>29</asp:ListItem>
<asp:ListItem>30</asp:ListItem>
<asp:ListItem>31</asp:ListItem>
<asp:ListItem>32</asp:ListItem>
<asp:ListItem>33</asp:ListItem>
<asp:ListItem>34</asp:ListItem>
<asp:ListItem>35</asp:ListItem>
<asp:ListItem>36</asp:ListItem>
<asp:ListItem>37</asp:ListItem>
<asp:ListItem>38</asp:ListItem>
<asp:ListItem>39</asp:ListItem>
<asp:ListItem>40</asp:ListItem>
<asp:ListItem>41</asp:ListItem>
<asp:ListItem>42</asp:ListItem>
<asp:ListItem>43</asp:ListItem>
<asp:ListItem>44</asp:ListItem>
<asp:ListItem>45</asp:ListItem>
<asp:ListItem>46</asp:ListItem>
<asp:ListItem>47</asp:ListItem>
<asp:ListItem>48</asp:ListItem>
<asp:ListItem>49</asp:ListItem>
<asp:ListItem>50</asp:ListItem>
<asp:ListItem>51</asp:ListItem>
<asp:ListItem>52</asp:ListItem>
<asp:ListItem>53</asp:ListItem>
<asp:ListItem>54</asp:ListItem>
<asp:ListItem>55</asp:ListItem>
<asp:ListItem>56</asp:ListItem>
<asp:ListItem>57</asp:ListItem>
<asp:ListItem>58</asp:ListItem>
<asp:ListItem>59</asp:ListItem>
</asp:DropDownList>
分<br />
<br />
</div>
<br />
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="查 詢"
UseSubmitBehavior="False" />
<asp:Button ID="Button2" runat="server" Height="26px" onclick="Button2_Click"
Text="導出" Width="64px" />
<br />
<asp:UpdateProgress ID="UpdateProgress1" runat="server"
AssociatedUpdatePanelID="UpdatePanel1">
<ProgressTemplate>
<img alt="" src="a8.gif" style="width: 36px; height: 32px" /> 數據撈取中,請耐心等待....
</ProgressTemplate>
</asp:UpdateProgress>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px"
CellPadding="4" ForeColor="Black" GridLines="Vertical"
onselectedindexchanged="GridView1_SelectedIndexChanged">
<FooterStyle BackColor="#CCCC99" />
<RowStyle BackColor="#F7F7DE" />
<Columns>
<asp:BoundField DataField="f" HeaderText="箱號" />
<asp:BoundField DataField="i" HeaderText="流單" />
<asp:BoundField DataField="qty" HeaderText="數量" />
<asp:BoundField DataField="liaohao" HeaderText="料號" />
<asp:BoundField DataField="nam_item" HeaderText="品名" />
<asp:BoundField DataField="nam_items" HeaderText="商編" />
<asp:BoundField DataField="p_lot_branchno" HeaderText="支號" />
<asp:BoundField DataField="outlotno" HeaderText="出貨批號" />
<asp:BoundField DataField="rcv_dtm" HeaderText="時間" />
</Columns>
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<br />
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="Button1" EventName="Click" />
</Triggers>
</asp:UpdatePanel>
</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.Odbc;
using System.Data.OleDb;
using System.using System.Text;
using namespace cangku
{
public partial class _default : System.Web.UI.Page
{
public string con1 = "Provider=IBMDADB2;Database=mes;HOSTNAME=***********;PROTOCOL=TCPIP;PORT=50001;uid=********;pwd=**********";
protected void Page_Load(object sender, EventArgs e)
{
if (base.IsPostBack)
{
this.query_data();
}
}
public void query_data()
{
string one = DropDownList1.SelectedValue.ToString();
string two = DropDownList2.SelectedValue.ToString();
string three = DropDownList3.SelectedValue.ToString();
string four = DropDownList4.SelectedValue.ToString();
string time1 = TextBox1.Text + "-" + one + "." + two + ".00.000000";
string time2 = TextBox2.Text + "-" + three + "." + four + ".00.000000";
OleDbConnection myconnection = new OleDbConnection();
myconnection.ConnectionString = con1;
myconnection.Open();
string sq1="select DISTINCT(F),I,QTY,LIAOHAO,NAM_ITEM,NAM_ITEMS,OUTLOTNO,P_LOT_BRANCHNO,RCV_DTM from ";
string sq2=" (SELECT DISTINCT(F),I,QTY,LIAOHAO,NAM_ITEM,NAM_ITEMS,RCV_DTM FROM ";
string sq3=" (SELECT DISTINCT(RUNCARD_ID)AS F,(PACKNO)AS I,nam_item,nam_items,rcv_dtm,a.cod_item AS LIAOHAO FROM (SELECT runcard_id,packno,nam_item,nam_items,rcv_dtm,a.cod_item FROM ";
string sq4 = " BRPACKCONVERT AS A,BWBOTTOM AS B WHERE A.PACK_NO=B.PACKNO AND eventdatetime >='" + time1 + "'" + "AND eventdatetime <='" + time2 + "' )AS A ";
string sq5="LEFT JOIN (select lotno,P_LOT_BRANCHNO from fhlotoperation) B on A.RUNCARD_ID=B.LOTNO )AS D,(SELECT NUM_ORD AS J,pack_no AS O,cod_item,SUM(QTY)AS QTY FROM ";
string sq6 = "BRPACKCONVERT WHERE eventdatetime >='" + time1 + "'" + "AND eventdatetime <='" + time2 + "' GROUP BY NUM_ORD,COD_ITEM,PACK_NO)AS E WHERE D.F=E.J AND D.I=E.O ORDER BY I) AS A ";
string sq7 = "LEFT JOIN(select lotno,P_LOT_BRANCHNO from fhlotoperation) B on F=B.LOTNO ";
string sq8= "LEFT JOIN (SELECT OUTLOTNO,LOTNO FROM BWLOT ) C ON F=C.LOTNO order by f,i,rcv_dtm ";
//string sq1 = " select DISTINCT(PACKNO),runcard_id,a.cod_item,NAM_ITEM,NAM_ITEMS,P_LOT_BRANCHNO,OUTLOTNO,RCV_DTM from ";
// string sq2 = "(SELECT runcard_id,packno,nam_item,nam_items,rcv_dtm,a.cod_item FROM BRPACKCONVERT AS A,BWBOTTOM AS B WHERE A.PACK_NO=B.PACKNO AND eventdatetime >='" + time1 + "'" + "AND eventdatetime <='" + time2 + "') AS A ";
//string sq3 = "LEFT JOIN(select lotno,P_LOT_BRANCHNO from fhlotoperation) B on A.RUNCARD_ID=B.LOTNO ";
//string sq4 = "LEFT JOIN (SELECT OUTLOTNO,LOTNO FROM BWLOT ) C ON A.RUNCARD_ID=C.LOTNO ORDER BY PACKNO,RCV_DTM ";
sq1 = sq1 + sq2 + sq3 + sq4+sq5+sq6+sq7+sq8;
OleDbCommand mycmd = new OleDbCommand(sq1, myconnection);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = mycmd;
DataSet myds = new DataSet();
adapter.Fill(myds);
DataView dv = new DataView();
dv = myds.Tables[0].DefaultView;
myconnection.Close();
GridView1.DataSource = dv;
GridView1.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
query_data();
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
}
public static void Export(string Filename, GridView gridview, Page page)
{
page.Response.Clear();
page.Response.Charset = "big5";
page.Response.ContentType = "application/ms-excel";
page.Response.ContentEncoding = Encoding.GetEncoding("big5");
page.Response.AppendHeader("content-disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(Filename + DateTime.Now.ToShortDateString(), Encoding.UTF8) + ".xls\"");
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gridview.RenderControl(htw);
page.Response.Write(sw.ToString());
page.Response.End();
}
/// <summary>
/// 此方法必重写,否则会出错
/// </summary>
/// <param name="control"></param>
///
public override void VerifyRenderingInServerForm(Control control)
{
}
protected void Button2_Click(object sender, EventArgs e)
{
this.GridView1.AllowPaging = false;
this.GridView1.AllowSorting = false;
this.GridView1.DataBind();
Export("cangchu", this.GridView1, this.Page);
}
}
}全部代码都在这里,比较简单。我只是想用个有加载图标的!所以使用了UpdatePanel
程序代码:<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>//应该放这里
<asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
<ContentTemplate>
<div class="style1">
//<asp:ScriptManager ID="ScriptManager1" runat="server">
//</asp:ScriptManager>
倉 庫 對 點 明 細<br />
<br />
開始時間:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<cc1:CalendarExtender ID="TextBox1_CalendarExtender" runat="server"
Enabled="True" Format="yyyy-MM-dd" TargetControlID="TextBox1">
</cc1:CalendarExtender>
<asp:DropDownList ID="DropDownList1" runat="server" Height="22px"
style="margin-top: 0px" Width="42px">
<asp:ListItem>00</asp:ListItem>
<asp:ListItem>01</asp:ListItem>
</asp:DropDownList>
點<asp:DropDownList ID="DropDownList2" runat="server" Height="22px" Width="42px">
<asp:ListItem>00</asp:ListItem>
</asp:DropDownList>
分
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="TextBox1" ErrorMessage="開始日期不能為空" Font-Bold="False"
BorderStyle="Dotted"></asp:RequiredFieldValidator>
<br />
<br />
結束時間:<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<cc1:CalendarExtender ID="TextBox2_CalendarExtender" runat="server"
Enabled="True" Format="yyyy-MM-dd" TargetControlID="TextBox2">
</cc1:CalendarExtender>
<asp:DropDownList ID="DropDownList3" runat="server" Height="22px" Width="42px">
<asp:ListItem>00</asp:ListItem>
<asp:ListItem>01</asp:ListItem>
</asp:DropDownList>
點<asp:DropDownList ID="DropDownList4" runat="server" Height="22px" Width="42px">
<asp:ListItem>00</asp:ListItem>
<asp:ListItem>01</asp:ListItem>
</asp:DropDownList>
分<br />
<br />
</div>
<br />
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="查 詢"
UseSubmitBehavior="False" />
<asp:Button ID="Button2" runat="server" Height="26px" onclick="Button2_Click"
Text="導出" Width="64px" />
<br />
<asp:UpdateProgress ID="UpdateProgress1" runat="server"
AssociatedUpdatePanelID="UpdatePanel1">
<ProgressTemplate>
<img alt="" src="a8.gif" style="width: 36px; height: 32px" /> 數據撈取中,請耐心等待....
</ProgressTemplate>
</asp:UpdateProgress>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px"
CellPadding="4" ForeColor="Black" GridLines="Vertical"
onselectedindexchanged="GridView1_SelectedIndexChanged">
<FooterStyle BackColor="#CCCC99" />
<RowStyle BackColor="#F7F7DE" />
<Columns>
<asp:BoundField DataField="f" HeaderText="箱號" />
<asp:BoundField DataField="i" HeaderText="流單" />
<asp:BoundField DataField="qty" HeaderText="數量" />
<asp:BoundField DataField="liaohao" HeaderText="料號" />
<asp:BoundField DataField="nam_item" HeaderText="品名" />
<asp:BoundField DataField="nam_items" HeaderText="商編" />
<asp:BoundField DataField="p_lot_branchno" HeaderText="支號" />
<asp:BoundField DataField="outlotno" HeaderText="出貨批號" />
<asp:BoundField DataField="rcv_dtm" HeaderText="時間" />
</Columns>
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<br />
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="Button1" EventName="Click" />
</Triggers>
</asp:UpdatePanel>
</form>
</body>