Excel 匯出,服務器文件資料下載

<%@ Page Language="C#" MasterPageFile="~/tmep/MasterPage.master" AutoEventWireup="true" CodeFile="valog.aspx.cs" Inherits="rfidsearch_valog" Title="VA Halo 測試LOG查詢" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder2" Runat="Server">
    <table align="center" width="98%">
        <tbody>
 <tr>
                <td align="left" class="title" style="width: 969px; height: 21px">
                    <font color="#ff0000"><strong>VA Halo 測試LOG查詢</strong><strong> --&gt;</strong></font>
                </td>
            </tr>
            <tr>
                <td style="width: 969px; height: 13px; text-align: left">
                    <hr color="#6391e0" size="2" /><strong><font></font></strong>
                    提示：文件名稱可模糊查詢！ </td>
            </tr>
            <tr>
                <td style="width: 969px; height: 13px; text-align: left">
                    <table id="TABLE1" style="width: 928px">
                        <tbody>
                            <tr>
                                <td align="right" style="width: 115px; height: 20px; text-align: right">
                                    測試工單：</td>
                                <td style="width: 16px; height: 20px; text-align: left">
                                    <asp:TextBox ID="txt_mo" runat="server" CssClass="txtTextBox" Width="160px"></asp:TextBox>
                                </td>
                                <td style="width: 96px; height: 20px; text-align: right">
                                    內控起始號：</td>
                                <td style="width: 134px; height: 20px; text-align: left">
                                    <asp:TextBox ID="txt_unit" runat="server" CssClass="txtTextBox" Width="140px"></asp:TextBox></td>
                                <td style="width: 11px; height: 20px; text-align: right">
                                    ~</td>
                                <td style="width: 49px; height: 20px; text-align: right">
                                    <asp:TextBox ID="txt_end" runat="server" CssClass="txtTextBox" Width="140px"></asp:TextBox></td>
                                <td style="width: 49px; height: 20px; text-align: right">
                                    站別：</td>
                                <td style="width: 113px; height: 20px; text-align: left">
                                    <asp:DropDownList ID="DDL_FOP" runat="server" CssClass="txtTextBox" Width="117px">                                     
                                    </asp:DropDownList></td>
                                <td colspan="1" style="width: 53px; height: 20px; text-align: left">
                                    <asp:ImageButton ID="search" runat="server" ImageUrl="~/images/searc.gif" OnClick="search_Click" /></td>
                            </tr>
                            <tr>
                                <td align="right" style="width: 115px; height: 20px; text-align: right">
                                    包裝工單:</td>
                                <td style="width: 16px; height: 20px; text-align: left">
                                    <asp:TextBox ID="txt_packmo" runat="server" CssClass="txtTextBox" Width="160px"></asp:TextBox></td>
                                <td style="width: 96px; height: 20px; text-align: right">
                                    出貨S/N起始號</td>
                                <td style="width: 134px; height: 20px; text-align: left">
                                    <asp:TextBox ID="txt_packstart" runat="server" CssClass="txtTextBox" Width="140px"></asp:TextBox></td>
                                <td style="width: 11px; height: 20px; text-align: right">
                                    ~</td>
                                <td style="width: 49px; height: 20px; text-align: right">
                                    <asp:TextBox ID="txt_packend" runat="server" CssClass="txtTextBox" Width="140px"></asp:TextBox></td>
                                <td style="width: 49px; height: 20px; text-align: right">
                                    狀態：</td>
                                <td style="width: 113px; height: 20px; text-align: left">
                                    <asp:DropDownList ID="ddl_result" runat="server" CssClass="txtTextBox" Width="117px">
                                    </asp:DropDownList></td>
                                <td colspan="1" style="width: 53px; height: 20px; text-align: left">
                                    <asp:ImageButton ID="ImageButton3" runat="server" ImageUrl="~/images/cancel.gif"
                                        OnClick="cancer_Click" /></td>
                            </tr>
                            <tr>
                                <td align="right" style="width: 115px; height: 20px; text-align: right">
                                    報表名稱：</td>
                                <td colspan="3" style="height: 20px; text-align: left">
                                    <asp:TextBox ID="txtflot" runat="server" CssClass="txtTextBox" Width="405px"></asp:TextBox></td>
                                <td style="width: 11px; height: 20px; text-align: right">
                                </td>
                                <td style="width: 49px; height: 20px; text-align: right">
                                </td>
                                <td style="width: 49px; height: 20px; text-align: right">
                                    </td>
                                <td style="width: 113px; height: 20px; text-align: left">
                                    </td>
                                <td colspan="1" style="width: 53px; height: 20px; text-align: left">
                                    <asp:ImageButton ID="excel" runat="server" ImageUrl="~/images/excel.gif" OnClick="excel_Click" /></td>
                            </tr>
                        </tbody>
                    </table>
                </td>
            </tr>
            <tr>
                <td style="width: 969px; height: 13px; text-align: center">
                    <hr color="#6391e0" size="2" />
                  <strong><font></font></strong></td>
            </tr>
            <tr>
                <td style="width: 969px; text-align: center">
                    <div id="" style="overflow-x: auto; width: 940px; height: 560px">
                        <table cellpadding="0" cellspacing="0" width="100%">
                            <tr>
                                <td id="tdtest">
                                    <asp:DataGrid ID="gvID" runat="server" AllowPaging="True" AutoGenerateColumns="False"
                                        CaptionAlign="Left"  HorizontalAlign="Left"  OnItemDataBound="gvID_ItemDataBound" OnPageIndexChanged="gvID_PageIndexChanged"
                                        PageSize="25" Width="100%">
                                        <Columns>
                                        <asp:HyperLinkColumn   DataNavigateUrlField="FPATH"
                                         
                                          DataNavigateUrlFormatString="http://172.24.84.7/{0}"
                                         DataTextField="FPATH" HeaderText="查看詳情"
                                          DataTextFormatString="{0}" Target="_blank" >
                                                <HeaderStyle Wrap="False" />
                                                <ItemStyle Wrap="False" />
                                          </asp:HyperLinkColumn>
                                          
                                            <asp:BoundColumn DataField="FLOT" HeaderText="測試工單" ReadOnly="true">
                                                <HeaderStyle Wrap="False" />
                                                <ItemStyle Wrap="False" />
                                            </asp:BoundColumn>
                                            <asp:BoundColumn DataField="FUNIT" HeaderText="內控" ReadOnly="true">
                                                <HeaderStyle Wrap="False" />
                                                <ItemStyle Wrap="False" />
                                            </asp:BoundColumn>
                                            <asp:BoundColumn DataField="FOP" HeaderText="站別" ReadOnly="true">
                                                <HeaderStyle Wrap="False" />
                                                <ItemStyle Wrap="False" />
                                            </asp:BoundColumn>
                                            <asp:BoundColumn DataField="FFILENAME" HeaderText="文件名" ReadOnly="true">
                                                <HeaderStyle Wrap="False" />
                                                <ItemStyle Wrap="False" />
                                            </asp:BoundColumn>
                                             <asp:BoundColumn DataField="FRESULT" HeaderText="狀態" ReadOnly="true">
                                                <HeaderStyle Wrap="False" />
                                                <ItemStyle Wrap="False" />
                                            </asp:BoundColumn>
                                            <asp:BoundColumn DataField="FLOTS" HeaderText="出貨工單" ReadOnly="true">
                                                <HeaderStyle Wrap="False" />
                                                <ItemStyle Wrap="False" />
                                            </asp:BoundColumn>
                                            <asp:BoundColumn DataField="FCUSTUNITNO" HeaderText="出貨SN" ReadOnly="true">
                                                <HeaderStyle Wrap="False" />
                                                <ItemStyle Wrap="False" />
                                            </asp:BoundColumn>
                                        </Columns>
                                        <HeaderStyle BackColor="#507CD1" BorderStyle="None" ForeColor="White" HorizontalAlign="Center"
                                            VerticalAlign="Middle" Wrap="False" />
                                        <ItemStyle BorderStyle="None" ForeColor="#000066" HorizontalAlign="Left" />
                                        <PagerStyle Visible="False" />
                                        <SelectedItemStyle Font-Bold="True" ForeColor="White" />
                                    </asp:DataGrid></td>
                            </tr>
                            <tr>
                                <td style="height: 16px; text-align: center">
                                    <asp:Label ID="lblError" runat="server" ForeColor="red" Text="無相關信息" Visible="false"></asp:Label>
                                </td>
                            </tr>
                            <tr>
                                <td id="tdPage" runat="server" align="right" style="height: 15px; text-align: center"
                                    width="100%">
                                    共<asp:Label ID="lblTotal" runat="server" ForeColor="Red"></asp:Label>筆資料,第
                                    <asp:Label ID="labCurrent" runat="server"></asp:Label>
                                    /&nbsp;
                                    <asp:Label ID="labPage" runat="server"></asp:Label>
                                    頁 &nbsp;
                                    <asp:LinkButton ID="lbtnFirst" runat="server" CommandName="first" OnClick="NavigateToPage"> 首頁 </asp:LinkButton>&nbsp;
                                    <asp:LinkButton ID="lbtnPrevious" runat="server" CommandName="prev" OnClick="NavigateToPage"> 上頁 </asp:LinkButton>&nbsp;
                                    <asp:LinkButton ID="lbtnNext" runat="server" CommandName="next" OnClick="NavigateToPage"> 下頁 </asp:LinkButton>&nbsp;
                                    <asp:LinkButton ID="lbtnLast" runat="server" CommandName="last" OnClick="NavigateToPage"> 尾頁 </asp:LinkButton>
                                    &nbsp;&nbsp;
                                </td>
                            </tr>
                        </table>
                    </div>
                </td>
            </tr>
        </tbody>
    </table>
</asp:Content>

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.IO;
public partial class rfidsearch_valog : System.Web.UI.Page
{
    dbconn db = new dbconn();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            db.dropdownlist("MISEMSDB02", DDL_FOP, "SELECT DISTINCT FOP FROM TBLTESTLOGBACKUP ORDER BY FOP", "FOP", "FOP");
            DDL_FOP.Items.Insert(0, "---ALL--- ");
            DDL_FOP.Items[0].Value = "";
            db.dropdownlist("MISEMSDB02", ddl_result, "SELECT DISTINCT FRESULT FROM TBLTESTLOGBACKUP ORDER BY FRESULT", "FRESULT", "FRESULT");
            ddl_result.Items.Insert(0, "---ALL--- ");
            ddl_result.Items[0].Value = "";
        }
    }
    private void Bind()
    {
        string strBind = " SELECT C.FLOT,C.FUNIT,C.FOP,C.FPATH,C.FFILENAME,C.FRESULT,A.FLOT FLOTS,A.FUNIT,A.FPACK,B.FID,B.FCUSTUNITNO,D.PNL_NM AS FBUILDER,C.FBDATE ";
        strBind += " FROM TBLMESPCBAPACKELEM@EMSDB01 A,TBLUNIT@EMSDB01 B,TBLTESTLOGBACKUP C,V_WEB_PNL D WHERE C.FUNIT=A.FUNIT(+) AND B.FID=A.FUNIT AND C.FBUILDER=D.PNL_NO";
         if(this.txt_mo.Text.Trim()!="")
         {
             strBind += " AND C.FLOT = '"+this.txt_mo.Text.Trim()+"'";
         }
        if(this.txt_unit.Text.Trim()!=""&&this.txt_end.Text.Trim ()!="")
        {
            if (this.txt_unit.Text.Trim().CompareTo(this.txt_end.Text.Trim()) > 0)
            {
                Response.Write("<script>alert('內控開始條碼不能大於結束條碼，請重新選擇！');window.history.back();</script>");
            }
            else
            {
                strBind += " AND C.FUNIT >= '" + this.txt_unit.Text.Trim() + "' AND C.FUNIT <= '" + this.txt_end.Text.Trim() + "'";
            }
        }
        if (this.txt_packstart.Text.Trim() != "" && this.txt_packend.Text.Trim() != "")
        {
            if (this.txt_packstart.Text.Trim().CompareTo(this.txt_packend.Text.Trim()) > 0)
            {
                Response.Write("<script>alert('出貨SN開始條碼不能大於結束條碼，請重新選擇！');window.history.back();</script>");
            }
            else
            {
                strBind += " AND B.FCUSTUNITNO >= '" + this.txt_packstart.Text.Trim() + "' AND B.FCUSTUNITNO <= '" + this.txt_packend.Text.Trim() + "'";
            }
        }
        if(this.txt_packmo.Text.Trim()!="")
        {
            strBind += " AND A.FLOT  = '" + this.txt_packmo.Text.Trim() + "'";
        }
        if (this.DDL_FOP.SelectedValue.Trim() != "")
        {
            strBind += " AND C.FOP  = '" + this.DDL_FOP.SelectedValue.Trim() + "'";
        }
        if (this.ddl_result.SelectedValue.Trim() != "")
        {
            strBind += " AND C.FRESULT  = '" + this.ddl_result.SelectedValue.Trim() + "'";
        }
        if (txtflot.Text.Trim() != "")
        {
            //strBind += " AND FLOT='" + txtflot.Text.Trim() + "'";
            strBind += " AND (C.FFILENAME like '%" + this.txtflot.Text.Replace("'", "''").Replace("%", "###").Trim().ToLower() + "%' OR C.FFILENAME like '%" + this.txtflot.Text.Replace("'", "''").Replace("%", "###").Trim().ToUpper() + "%') ";
        }
        strBind += " ORDER BY C.FBDATE DESC,C.FFILENAME ASC";
        //Response.Write(strBind);
        DataSet ds = db.ReturnDS("MISEMSDB02", strBind);
        if (ds.Tables[0].Rows.Count > 0)
        {
            this.lblError.Visible = false;
            lblTotal.Text = ds.Tables[0].Rows.Count.ToString();
        }
        else
        {
            this.lblError.Visible = true;
            lblTotal.Text = "0";
        }

        this.gvID.DataSource = ds.Tables[0].DefaultView;
        int nPage = 0;
        nPage = gvID.CurrentPageIndex;
        gvID.CurrentPageIndex = 0;
        gvID.DataBind();
        if ((nPage >= gvID.PageCount) && (gvID.PageCount > 0))
        {
            nPage = gvID.PageCount - 1;
        }
        this.gvID.CurrentPageIndex = nPage;
        this.gvID.DataBind();
        db.ShowStats(gvID, ds, labCurrent, labPage, lbtnFirst, lbtnNext, lbtnPrevious, lbtnLast);
    }
    protected void gvID_ItemDataBound(object sender, DataGridItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            e.Item.Attributes["onmouseover"] = "rowColor = this.style.backgroundColor;this.style.backgroundColor='#99cc00'";
            e.Item.Attributes["onmouseout"] = "this.style.backgroundColor= rowColor";
        }
    }
    protected void gvID_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
    {
        this.gvID.CurrentPageIndex = e.NewPageIndex;
        int CurentPage = Convert.ToInt32(gvID.CurrentPageIndex.ToString()) + 1;
    }
    public void NavigateToPage(object sender, System.EventArgs e)
    {
        string arg = ((LinkButton)sender).CommandName.ToString();
        switch (arg)
        {
            case "next":
                if (gvID.CurrentPageIndex < (gvID.PageCount - 1))
                    gvID.CurrentPageIndex += 1;
                break;
            case "prev":
                if (gvID.CurrentPageIndex > 0)
                    gvID.CurrentPageIndex -= 1;
                break;
            case "last":
                gvID.CurrentPageIndex = (gvID.PageCount - 1);
                break;
            case "first":
                gvID.CurrentPageIndex = 0;
                break;
        }
        Bind();
    }
    protected void search_Click(object sender, ImageClickEventArgs e)
    {
        this.gvID.CurrentPageIndex = 0;
        Bind();
    }

    private void ExportToExcelDG(string strFileName, DataGrid gv)
    {
        //attachment
        Response.ClearContent();
        Response.Charset = "UTF-8";
        Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
        Response.Write("<meta http-equiv=Content-Type content=text/html;charset=Big5>");
        Response.ContentEncoding = System.Text.Encoding.Default;
        Response.ContentType = "application/ms-excel";
        this.EnableViewState = false;
        System.IO.StringWriter sw = new System.IO.StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        gv.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();
    }

    protected void excel_Click(object sender, ImageClickEventArgs e)
    {
        
        string strBind = " SELECT C.FLOT 工單,C.FUNIT 內控,C.FOP 制程,C.FPATH 路徑,C.FFILENAME 檔案名稱,C.FRESULT 狀態,A.FLOT 出貨工單,B.FCUSTUNITNO 出貨條碼,D.PNL_NM AS FBUILDER,C.FBDATE 建立時間";
        strBind += " FROM TBLMESPCBAPACKELEM@EMSDB01 A,TBLUNIT@EMSDB01 B,TBLTESTLOGBACKUP C,V_WEB_PNL D WHERE C.FUNIT=A.FUNIT(+) AND B.FID=A.FUNIT AND C.FBUILDER=D.PNL_NO";
        if (this.txt_mo.Text.Trim() != "")
        {
            strBind += " AND C.FLOT = '" + this.txt_mo.Text.Trim() + "'";
        }
        if (this.txt_unit.Text.Trim() != "" && this.txt_end.Text.Trim() != "")
        {
            if (this.txt_unit.Text.Trim().CompareTo(this.txt_end.Text.Trim()) > 0)
            {
                Response.Write("<script>alert('內控開始條碼不能大於結束條碼，請重新選擇！');window.history.back();</script>");
            }
            else
            {
                strBind += " AND C.FUNIT >= '" + this.txt_unit.Text.Trim() + "' AND C.FUNIT <= '" + this.txt_end.Text.Trim() + "'";
            }
        }
        if (this.txt_packstart.Text.Trim() != "" && this.txt_packend.Text.Trim() != "")
        {
            if (this.txt_packstart.Text.Trim().CompareTo(this.txt_packend.Text.Trim()) > 0)
            {
                Response.Write("<script>alert('出貨SN開始條碼不能大於結束條碼，請重新選擇！');window.history.back();</script>");
            }
            else
            {
                strBind += " AND B.FCUSTUNITNO >= '" + this.txt_packstart.Text.Trim() + "' AND B.FCUSTUNITNO <= '" + this.txt_packend.Text.Trim() + "'";
            }
        }
        if (this.txt_packmo.Text.Trim() != "")
        {
            strBind += " AND A.FLOT  = '" + this.txt_packmo.Text.Trim() + "'";
        }
        if (this.DDL_FOP.SelectedValue.Trim() != "")
        {
            strBind += " AND C.FOP  = '" + this.DDL_FOP.SelectedValue.Trim() + "'";
        }
        if (this.ddl_result.SelectedValue.Trim() != "")
        {
            strBind += " AND C.FRESULT  = '" + this.ddl_result.SelectedValue.Trim() + "'";
        }
        if (txtflot.Text.Trim() != "")
        {
            //strBind += " AND FLOT='" + txtflot.Text.Trim() + "'";
            strBind += " AND (C.FFILENAME like '%" + this.txtflot.Text.Replace("'", "''").Replace("%", "###").Trim().ToLower() + "%' OR C.FFILENAME like '%" + this.txtflot.Text.Replace("'", "''").Replace("%", "###").Trim().ToUpper() + "%') ";
        }
        strBind += " ORDER BY C.FBDATE DESC,C.FFILENAME ASC";

        DataGrid gvDown = new DataGrid();
        DataSet ds = db.ReturnDS("MISEMSDB02", strBind);
        gvDown.DataSource = ds.Tables[0].DefaultView;
        gvDown.DataBind();
        gvDown.Dispose();

        ExportToExcelDG("list.xls", gvDown);

    }
    protected void lbtReturn_Click(object sender, EventArgs e)
    {
        StringBuilder script = new StringBuilder();
        script.Append("<script language='javascript'>");
        script.Append("window.location.href='valog.aspx';");
        script.Append("</script>");
        this.Response.Write(script.ToString());
    }
 
    protected void cancer_Click(object sender, ImageClickEventArgs e)
    {
        Response.Redirect("valog.aspx");
    }
}

//匯出

 protected void excel_Click(object sender, ImageClickEventArgs e)
    {
        string strBind = " SELECT A.FUNIT,A.FCUSTUNIT,B.FKEYID,A.FPACKINGSERIALNO CARTON, TO_NUMBER(SUBSTR(A.FPACKINGSERIALNO, 9, 3)) SEQNO";
        strBind += " FROM TBLMESPACKINGBYLOT A, TBLPCBAUNITKEYIDMAPPING B WHERE  A.FUNIT = B.FUNIT AND B.FKEYID NOT LIKE 'CW%'";
        strBind += " AND A.FLOTID ='" + txtflot.Text.Trim() + "' ";

        if (txtbegin.Text.Trim() != "" && txtend.Text.Trim() != "")
        {
            strBind += " AND A.FPACKINGSERIALNO >= '" + txtbegin.Text.Trim() + "' AND A.FPACKINGSERIALNO <='" + txtend.Text.Trim() + "' ";
        }
        string strE = "  SELECT DISTINCT XX.FUNIT ISN,XX.FCUSTUNIT SN,CHR(29)||XX.FKEYID MAC,C.FRESULT KEYID,XX.CARTON CARTON FROM TBLMESFTINFO C,(" + strBind + ")" + "XX ";
        strE += "  WHERE C.FUNIT = XX.FUNIT ORDER BY SEQNO ";
        //DataGrid gvID = new DataGrid();
        //DataSet ds = db.ReturnDS("EMSDB01", strE);
        //gvID.DataSource = ds.Tables[0].DefaultView;
        //gvID.DataBind();
        //gvID.Dispose();
        //ExportToExcelDG("ACout.xls", gvID);
        //導Excel
                    //Response.Clear();    
       // DAL obj = new DAL();    
                           // DAL.PR obj = new DAL.PR();
        DataTable dt = db.GetTable("EMSDB01", strE);
        //DataTable dt = obj.GetMatm(
               // txtflot.Text.Trim(),
               // txtbegin.Text.Trim(),
               // txtend.Text.Trim()
               //);

        Response.AddHeader("Content-Disposition", "attachment;filename=Export.xls");
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("big5");
        Response.ContentType = "application/vnd.ms-excel";

        System.IO.StringWriter tw = new System.IO.StringWriter();
        //此處是必須的，就如xml聲明碼一樣。
        tw.WriteLine("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=big5\">");
        tw.WriteLine("<table style=\"border-collapse: collapse\" bordercolor=\"#000000\" border=\"1\"  width=\"100%\" align=\"center\">");
        tw.WriteLine("<tr align=\"center\">");
        tw.WriteLine("<td>內控</td>");
        tw.WriteLine("<td>客戶序號</td>");
        tw.WriteLine("<td>MAC</td>");
        tw.WriteLine("<td>KEYID</td>");
        tw.WriteLine("<td>箱號</td>");
        tw.WriteLine("</tr>");
        foreach (DataRow dr in dt.Rows)
        {
            tw.WriteLine("<tr align=\"center\">");
            tw.WriteLine("<td>" + dr["ISN"].ToString() + "</td>");
            tw.WriteLine("<td>" + dr["SN"].ToString() + "</td>");
            tw.WriteLine("<td>" + dr["MAC"].ToString() + "</td>");
            tw.WriteLine("<td>" + dr["KEYID"].ToString() + "</td>");
            tw.WriteLine("<td>" + dr["CARTON"].ToString() + "</td>");
            tw.WriteLine("</tr>");
        }
        tw.WriteLine("</table>");
        Response.Write(tw);
        Response.End();
    }