【asp.net】將GridView數(shù)據(jù)導(dǎo)出Excel

概要：

中午睡了一會，醒來的時候看到老師叫我去辦公室，需求是這樣的，把excel表中的每個同學(xué)，判斷圖片目錄中是否有對應(yīng)的照片（圖片的名字用的學(xué)號或身份證號碼）

沒有對應(yīng)圖片的學(xué)生記錄，存入自己的數(shù)據(jù)表中或直接輸出，最后下載成Excel

于是回去后他把Excel和照片發(fā)給我

正文開始：

　雖然沒接觸過Excel的數(shù)據(jù)導(dǎo)入和將GridView數(shù)據(jù)導(dǎo)出Excel，在網(wǎng)上查找了很多資料，最后匯總成功實現(xiàn)。

這是第一次寫自己的博客并與大家分享。

我也是查了百度學(xué)來的，詳細地址：

http://jingyan.baidu.com/article/47a29f24003521c0142399dc.html

2.將圖片目錄所有圖片對應(yīng)的名稱導(dǎo)入另外一張表(image表)，圖片有些多并且如何能達到高效遍歷目錄文件，于是又去查百度了！地址如下：

http://blog.csdn.net/love_rrr/article/details/7779403

http://www.survivalescaperooms.com/xdesigner/archive/2006/12/08/586177.html

代碼如下：

#region 聲明WIN32API函數(shù)以及結(jié)構(gòu) **************************************    [Serializable,    System.Runtime.InteropServices.StructLayout      (System.Runtime.InteropServices.LayoutKind.Sequential,      CharSet = System.Runtime.InteropServices.CharSet.Auto      ),    System.Runtime.InteropServices.BestFitMapping(false)]    PRivate struct WIN32_FIND_DATA    {        public int dwFileAttributes;        public int ftCreationTime_dwLowDateTime;        public int ftCreationTime_dwHighDateTime;        public int ftLastaccessTime_dwLowDateTime;        public int ftLastAccessTime_dwHighDateTime;        public int ftLastWriteTime_dwLowDateTime;        public int ftLastWriteTime_dwHighDateTime;        public int nFileSizeHigh;        public int nFileSizeLow;        public int dwReserved0;        public int dwReserved1;        [System.Runtime.InteropServices.MarshalAs          (System.Runtime.InteropServices.UnmanagedType.ByValTStr,          SizeConst = 260)]        public string cFileName;        [System.Runtime.InteropServices.MarshalAs          (System.Runtime.InteropServices.UnmanagedType.ByValTStr,          SizeConst = 14)]        public string cAlternateFileName;    }    [System.Runtime.InteropServices.DllImport      ("kernel32.dll",      CharSet = System.Runtime.InteropServices.CharSet.Auto,      SetLastError = true)]    private static extern IntPtr FindFirstFile(string pFileName, ref WIN32_FIND_DATA pFindFileData);    [System.Runtime.InteropServices.DllImport      ("kernel32.dll",      CharSet = System.Runtime.InteropServices.CharSet.Auto,      SetLastError = true)]    private static extern bool FindNextFile(IntPtr hndFindFile, ref WIN32_FIND_DATA lpFindFileData);    [System.Runtime.InteropServices.DllImport("kernel32.dll", SetLastError = true)]    private static extern bool FindClose(IntPtr hndFindFile);       #endregion    //具體方法函數(shù)     Stack<string> m_scopes = new Stack<string>();    private static readonly IntPtr INVALID_HANDLE_VALUE = new IntPtr(-1);    WIN32_FIND_DATA FindFileData;    private System.IntPtr hFind = INVALID_HANDLE_VALUE;    void FindFileInDir(string rootDir)    {        string path = rootDir;        start:        new FileIOPermission(FileIOPermissionAccess.PathDiscovery, Path.Combine(path, ".")).Demand();        if (path[path.Length - 1] != '//')        {            path = path + "http://";        }        Response.Write("文件夾為："+path+"<br>");        hFind = FindFirstFile(Path.Combine(path,"*"), ref FindFileData);        if(hFind!=INVALID_HANDLE_VALUE)        {            do            {                if (FindFileData.cFileName.Equals(@".") || FindFileData.cFileName.Equals(@".."))                    continue;                if ((FindFileData.dwFileAttributes & 0x10) != 0)                {                    m_scopes.Push(Path.Combine(path, FindFileData.cFileName));                }                else                {                    Response.Write(FindFileData.cFileName+"<br>");                }            }            while (FindNextFile(hFind, ref FindFileData));                    }        FindClose(hFind);        if (m_scopes.Count > 0)        {            path = m_scopes.Pop();            goto start;        }    }//調(diào)用方法如下： FindFileInDir(@"D:/images/images");　//絕對路徑

3．（再次看了下需求）按照需求把Page2表中的每個同學(xué)，判斷image表中是否有對應(yīng)的照片

沒有對應(yīng)圖片的學(xué)生記錄，輸出到GridView，最后下載成Excel

頁面布局

代碼如下：

<div style="width:100%">           查詢條件：<asp:DropDownList ID="DropDownList1" runat="server">        <asp:ListItem Value="0" Text="=查詢所有="></asp:ListItem>        <asp:ListItem Value="1" Text="=身份證ID查詢="></asp:ListItem>        <asp:ListItem Value="2" Text="=姓名查詢="></asp:ListItem>         </asp:DropDownList>    &nbsp;&nbsp;&nbsp;&nbsp;    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>    &nbsp;&nbsp;&nbsp;&nbsp;<asp:Button ID="Button2" runat="server" Text="查詢" OnClick="Button2_Click" style="height: 21px" />&nbsp;&nbsp;&nbsp;&nbsp;<asp:Button ID="Button1" runat="server" Text="下載EXCEL" onclick="Button1_Click" />&nbsp;&nbsp;&nbsp;&nbsp;        <br />    <br />    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"  Width="100%" PageSize="15">    <Columns>    <asp:TemplateField HeaderText="序號">    <ItemTemplate><%#Eval("RowNum") %></ItemTemplate>    </asp:TemplateField>    <asp:TemplateField HeaderText="用戶ID">    <ItemTemplate><%#Eval("UserID") %></ItemTemplate>    </asp:TemplateField>    <asp:TemplateField HeaderText="用戶姓名">    <ItemTemplate><%#Eval("Name") %></ItemTemplate>    </asp:TemplateField>    <asp:TemplateField HeaderText="身份證號">    <ItemTemplate><%#Eval("PassCardID") %></ItemTemplate>    </asp:TemplateField>        <asp:TemplateField HeaderText="性別">    <ItemTemplate><%#Eval("Sex") %></ItemTemplate>    </asp:TemplateField>        <asp:TemplateField HeaderText="學(xué)院">    <ItemTemplate><%#Eval("College") %></ItemTemplate>    </asp:TemplateField>        <asp:TemplateField HeaderText="班級名稱">    <ItemTemplate><%#Eval("ClassName") %></ItemTemplate>    </asp:TemplateField>        <asp:TemplateField HeaderText="年級">    <ItemTemplate><%#Eval("ClassID") %></ItemTemplate>    </asp:TemplateField>           </Columns>    </asp:GridView><br />    <webdiyer:AspNetPager ID="AspNetPager1" runat="server" FirstPageText="首頁"         LastPageText="尾頁" NextPageText="下一頁" PrevPageText="上一頁" PageSize="20"         onpagechanged="AspNetPager1_PageChanged">    </webdiyer:AspNetPager>    <div>            </div></div>

后臺代碼如下：

public void BindPagerPage(GridView gv, AspNetPager pager){string sql = "with page as (select *,Row_number() OVER (ORDER BY UserID desc)as RowNum from Page2 where not exists  (select imagename from dbo.[image] where Page2.UserID=dbo.[image].imagename or Page2.PassCardID = dbo.[image].imagename) )select * from page where RowNum>{0} and RowNum<{1}";sql = string.Format(sql, (pager.CurrentPageIndex - 1) * pager.PageSize, (pager.CurrentPageIndex - 1) * pager.PageSize + pager.PageSize);pager.RecordCount = int.Parse(new DataBase().ExecuteValue("with page as(select Count(*) as RowNum from Page2 where not exists  (select imagename from dbo.[image] where Page2.UserID=dbo.[image].imagename or Page2.PassCardID = dbo.[image].imagename) )select RowNum from page"));gv.DataSource = new DataBase().GetDataTable(sql);gv.DataBind();}

因為用到了AspNetPager開源分頁控件,想在GridView上展示良好的分頁效果完成，開始編寫的數(shù)據(jù)庫語句數(shù)據(jù)出現(xiàn)重復(fù)，最后修改的時候數(shù)據(jù)庫語句反復(fù)出現(xiàn)錯誤跟著提示，才使數(shù)據(jù)無重復(fù)效果，如果有比較好的語句可以教下我，謝謝！(這里因為數(shù)據(jù)重復(fù)花了不少時間)