【asp.net】将GridView数据导出Excel

概要：

中午睡了一会，醒来的时候看到老师叫我去办公室，需求是这样的，把excel表中的每个同学，判断图片目录中是否有对应的照片（图片的名字用的学号或身份证号码）

没有对应图片的学生记录，存入自己的数据表中或直接输出，最后下载成Excel

于是回去后他把Excel和照片发给我

正文开始：

　虽然没接触过Excel的数据导入和将GridView数据导出Excel，在网上查找了很多资料，最后汇总成功实现。

这是第一次写自己的博客并与大家分享。

我也是查了百度学来的，详细地址：

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

2.将图片目录所有图片对应的名称导入另外一张表(image表)，图片有些多并且如何能达到高效遍历目录文件，于是又去查百度了！地址如下：

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

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

代码如下：

#region 声明WIN32API函数以及结构 **************************************    [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    //具体方法函数     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 + "\\";        }        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;        }    }//调用方法如下： FindFileInDir(@"D:\images\images");　//绝对路径

3．（再次看了下需求）按照需求把Page2表中的每个同学，判断image表中是否有对应的照片

没有对应图片的学生记录，输出到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="学院">    <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上展示良好的分页效果完成，开始编写的数据库语句数据出现重复，最后修改的时候数据库语句反复出现错误跟着提示，才使数据无重复效果，如果有比较好的语句可以教下我，谢谢！(这里因为数据重复花了不少时间)