【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> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <asp:Button ID="Button2" runat="server" Text="查询" OnClick="Button2_Click" style="height: 21px" /> <asp:Button ID="Button1" runat="server" Text="下载EXCEL" onclick="Button1_Click" /> <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上展示良好的分页效果完成，开始编写的数据库语句数据出现重复，最后修改的时候数据库语句反复出现错误跟着提示，才使数据无重复效果，如果有比较好的语句可以教下我，谢谢！(这里因为数据重复花了不少时间)
数据确实有点多，剩下的图片由于太多了，在QQ上传文件接收又慢，最后拿了老师发了我30多张图片我做下测试。
数据得到了良好的展示！
4.最后一步是最关键：将GridView数据导出Excel，在这里我又用到了一个开源控件MyXls.SL2
protected void Button1_Click(object sender, EventArgs e)
{
string strsql = " with page as (select *,Row_number() OVER (ORDER BY UserID asc)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";
Dal.DataBase db = new Dal.DataBase();
ExcelDown exceldown = new ExcelDown();
exceldown.downExcel(Response, db.GetDataTable(strsql), "page" + DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day + "-" + DateTime.Now.Hour + "-" + DateTime.Now.Minute + "-" + DateTime.Now.Second + "-" + DateTime.Now.Millisecond, Server.MapPath("~/Excel"));
}
总结：这次任务完成，并且也学到了将GridView数据导出Excel，自己是从大一下学期开始接触C#学了一段时间后开始学习asp.net,当时什么都不懂，老师就给我一些小案例去做，开始百度找的学习视频都是很模糊，看的时候很少敲代码导致后来太久不用了就忘记了，学习效率非常低，，接下来的日子里每天都是看别人写的博客和看一些项目案例视频学习敲代码并且自己把做好的小模块、知识点整理起来等要用的时候就复制粘贴，也得到了老师和学长的帮助，很感谢他们，写博客就是想认识多一些人交流学习，保持学习的状态!
完整代码
ExcelDown.cs
namespace Dal
{
public class ExcelDown
{
public void downExcel(HttpResponse response, DataTable dt, string fileName, string path)
{
XlsDocument xls = new XlsDocument();//新建一个
xls.FileName = fileName + ".xls";
Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");
//填充表头
try
{
foreach (DataColumn col in dt.Columns)
{
sheet.Cells.Add(1, col.Ordinal + 1, col.ColumnName);
}
//填充内容
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
sheet.Cells.Add(i + 2, j + 1, dt.Rows[i][j].ToString());
}
}
#region
using (MemoryStream ms = new MemoryStream())
{
xls.Save(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
xls = null;
// HttpResponse response = System.Web.HttpContext.Current.Response;
response.Clear();
response.Charset = "UTF-8";
response.ContentType = "application/vnd-excel";//"application/vnd.ms-excel";
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + fileName + ".xls"));
//System.Web.HttpContext.Current.Response.WriteFile(fi.FullName);
byte[] data = ms.ToArray();
System.Web.HttpContext.Current.Response.BinaryWrite(data);
}
#endregion
}
finally
{
sheet = null;
xls = null;
}
}
}
}
PageDal.cs
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();
}
page.aspx
public partial class Page : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) { bind();
// FindFileInDir(@"D:\images\images"); //绝对路径 只用一次用完记得注释
}
}
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
bind();
}
public void bind()
{
new PageDal().BindPagerPage(GridView1, AspNetPager1);
}
protected void Button1_Click(object sender, EventArgs e)
{
string strsql = " with page as (select *,Row_number() OVER (ORDER BY UserID asc)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";
Dal.DataBase db = new Dal.DataBase();
ExcelDown exceldown = new ExcelDown();
exceldown.downExcel(Response, db.GetDataTable(strsql), "Page" + DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day + "-" + DateTime.Now.Hour + "-" + DateTime.Now.Minute + "-" + DateTime.Now.Second + "-" + DateTime.Now.Millisecond, Server.MapPath("~/Excel"));
}
protected void Button2_Click(object sender, EventArgs e)
{
// bind();//点击查询
}
#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>");
string[] str =FindFileData.cFileName.Split('.');
DataBase db = new DataBase();
string comstr = "insert into Image(imageName) values(@ImageName)";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@ImageName", SqlDbType.VarChar, 256);
param[0].Value = str[0];
db.ExecuteSql(comstr, param);
}
}
while (FindNextFile(hFind, ref FindFileData));
}
FindClose(hFind);
if (m_scopes.Count > 0)
{
path = m_scopes.Pop();
goto start;
}
}
}
