有两种方式导出数据,都可以实现Gridview导出excel,如果Gridview是分页的,解决了只能导出当前页的问题
第一种
例: 在A.aspx页面上 放入Gridview 在导出excel的button的单击事件里不做处理只调转到另外一个页面,这么做是为了导出所有数据不止是当前页的
例如
protected void Button1_Click(object sender, EventArgs e)
{
Response.Redirect("B.aspx?startTime=" + startTime + "&endTime=" + endTime + "&userID=" + userID);
}
在B页面的aspx页面里 拖入一个Gridview,把不需要的属性都可以不设置,这里的Gridview就是为了承载数据用的 注意这里是AllowPaging="false"
例如:
<asp:GridView ID="gvUserInfo" runat="server" AutoGenerateColumns="False"
EmptyDataText="还没有任何信息" Width="100%" AllowPaging="false"
Font-Size="12px" HeaderStyle-BackColor="#e9f3d6"
PagerStyle-BackColor="#e9f3d6" HeaderStyle-ForeColor="#5c9948">
<Columns>
<asp:BoundField HeaderText="用户名" DataField="userName" />
<asp:BoundField HeaderText="编号" DataField="userCode" />
<asp:BoundField HeaderText="年龄" DataField="userAge" />
</Columns>
</asp:GridView>
再在B的页面的.cs编写方法方法 如下
private void Export()
{
//request接收传过来的参数
string startTime = Request["StartTime"]; ;
string endTime = Request["EndTime"];
string userID = Request["UserID"];
//业务逻辑处理
//IList<UserInfo> list = new List<UserInfo>();
//list = blldao.GetUserInfolist(userID);
//返回结果集
//gvUserInfo.DataSource = list;
//gvUserInfo.DataBind();
//下边就是导出的部分
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
//下面这行很重要, attachment 参数表示作为附件下载,您可以改成 online在线打开
//filename=FileFlow.xls 指定输出文件的名称,注意其扩展名和指定文件类型相符,可以为:.doc .xls .txt .htm
Response.AppendHeader("Content-Disposition", "attachment;filename=1.xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
//Response.ContentType指定文件类型 可以为application/ms-excel、application/ms-word、application/ms-txt、application/ms-html 或其他浏览器可直接支持文档
Response.ContentType = "application/ms-excel";
}
此方法在Page_Load里运行
protected void Page_Load(object sender, EventArgs e)
{
Export();
}
还有另外一种方法是不需要中间处理页面的
需要添加AppLibrary.dll 这个包在我的资源里边,方法如下 此方法在导出按钮里调用即可
public void OutDataToExcel(System.Web.UI.WebControls.GridView gridview, IList<UserInfo> list)
{
HttpContext hc = HttpContext.Current;
ArrayList al = new ArrayList();
int count = gridview.Columns.Count;
for (int i = 0; i < count; i++)
{
string t = gridview.Columns[i].HeaderText;
al.Add(t);
}
AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument();
doc.FileName = DateTime.Now.ToString("yyyyMMddHHMMss") + ".xls";
string SheetName = string.Empty;
SheetName = "数据查询";
AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(SheetName);
AppLibrary.WriteExcel.Cells cells = sheet.Cells;
#region 第一个样式
AppLibrary.WriteExcel.XF XFstyle = doc.NewXF();//添加样式的
XFstyle.HorizontalAlignment = AppLibrary.WriteExcel.HorizontalAlignments.Centered;
XFstyle.Font.FontName = "宋体";//字体
XFstyle.UseMisc = true;
XFstyle.TextDirection = AppLibrary.WriteExcel.TextDirections.LeftToRight;//文本位置
XFstyle.Font.Bold = false;//加粗
#region 边框线的样式
XFstyle.BottomLineStyle = 1;
XFstyle.LeftLineStyle = 1;
XFstyle.TopLineStyle = 1;
XFstyle.RightLineStyle = 1;
#endregion
XFstyle.UseBorder = true;
XFstyle.PatternBackgroundColor = AppLibrary.WriteExcel.Colors.Blue;
XFstyle.PatternColor = AppLibrary.WriteExcel.Colors.White;
XFstyle.Pattern = 1;
#region 宽度
AppLibrary.WriteExcel.ColumnInfo colInfo = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);
colInfo.ColumnIndexStart = 0;
colInfo.ColumnIndexEnd = 4;
colInfo.Width = 10 * 256;
sheet.AddColumnInfo(colInfo);
AppLibrary.WriteExcel.ColumnInfo colInfo1 = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);
colInfo1.ColumnIndexStart = 5;
colInfo1.ColumnIndexEnd = 7;
colInfo1.Width = 15 * 256;
sheet.AddColumnInfo(colInfo1);
AppLibrary.WriteExcel.ColumnInfo colInfo2 = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);
colInfo2.ColumnIndexStart = 8;
colInfo2.ColumnIndexEnd = 9;
colInfo2.Width = 15 * 256;
sheet.AddColumnInfo(colInfo2);
AppLibrary.WriteExcel.ColumnInfo colInfo3 = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);
colInfo3.ColumnIndexStart = 12;
colInfo3.ColumnIndexEnd = 12;
colInfo3.Width = 15 * 256;
sheet.AddColumnInfo(colInfo3);
AppLibrary.WriteExcel.ColumnInfo colInfo4 = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);
colInfo4.ColumnIndexStart = 13;
colInfo4.ColumnIndexEnd = 13;
colInfo4.Width = 50 * 256;
sheet.AddColumnInfo(colInfo4);
AppLibrary.WriteExcel.ColumnInfo colInfo5 = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);
colInfo5.ColumnIndexStart = 14;
colInfo5.ColumnIndexEnd = 14;
colInfo5.Width = 15 * 256;
sheet.AddColumnInfo(colInfo5);
AppLibrary.WriteExcel.ColumnInfo colInfo6 = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);
colInfo6.ColumnIndexStart = 15;
colInfo6.ColumnIndexEnd = 15;
colInfo6.Width = 50 * 256;
sheet.AddColumnInfo(colInfo6);
#endregion
#endregion
#region 添加表头
int idx = 1;
for (int i = 0; i < al.Count; i++)
{
cells.Add(1, idx, al[i], XFstyle);
idx++;
}
#endregion
int f = 2;//从第二行开始填充数据
foreach (UserInfo m in list)
{
cells.Add(f, 1, m.userName, XFstyle);
cells.Add(f, 2, m.userCode, XFstyle);
cells.Add(f, 3, m.userAge, XFstyle);
cells.Add(f, 5, float.Parse(m.sHTMoney).ToString("f0"), XFstyle);
cells.Add(f, 6, m.PayResult == "000" ? "成功" : "失败", XFstyle);
cells.Add(f, 7, m.CreateTime.ToString(), XFstyle);
f++;
}
doc.Send();
hc.Response.Flush();
hc.Response.End();
}