使用Excel表格下载,还是要先下载NPOI插件,下载安装后,才能直接下载Excel表格。
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using EnglishProject.Model;
using NPOI.HSSF.UserModel;
namespace EnglishProject.Back
{
public partial class down : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string keyword = "";
string sql = $"select * from entryUser ";
if (Request.QueryString["keyword"] != null)
{
keyword = Request.QueryString["keyword"].ToString();
}
if (!string.IsNullOrEmpty(keyword))
{
sql += $" where realname like '%{keyword}%' ";
}
DataTable list = DBHelper.getDataTable(sql);
//将查询出来的数据转化为对象列表的格式
HSSFWorkbook excelBook = new HSSFWorkbook(); //创建工作簿Excel
NPOI.SS.UserModel.ISheet sheet1 = excelBook.CreateSheet("英语四六级报考详情信息");//为工作簿创建工作表并命名
//编写工作表 (1)表头 (2)数据:list
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);//创建第一行
row1.CreateCell(0).SetCellValue("ID"); //创建其他列并赋值( 根据具体数据写代码...)
row1.CreateCell(1).SetCellValue("用户名");
row1.CreateCell(2).SetCellValue("姓名");
row1.CreateCell(3).SetCellValue("学校");
row1.CreateCell(4).SetCellValue("专业");
row1.CreateCell(5).SetCellValue("年级");
row1.CreateCell(6).SetCellValue("性别");
row1.CreateCell(7).SetCellValue("民族");
row1.CreateCell(8).SetCellValue("手机号");
row1.CreateCell(9).SetCellValue("报考类型");
row1.CreateCell(10).SetCellValue("报名结果");
row1.CreateCell(11).SetCellValue("创建时间");
//...根据数据库表字段而定...
for (int i=0;i<list.Rows.Count;i++)
{
//创建行( 根据具体数据写代码...)
NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1);
rowTemp.CreateCell(0).SetCellValue(list.Rows[i]["id"].ToString());//对应表字段
rowTemp.CreateCell(1).SetCellValue(list.Rows[i]["username"].ToString());
rowTemp.CreateCell(2).SetCellValue(list.Rows[i]["realname"].ToString());
rowTemp.CreateCell(3).SetCellValue(list.Rows[i]["school"].ToString());
rowTemp.CreateCell(4).SetCellValue(list.Rows[i]["speciality"].ToString());
rowTemp.CreateCell(5).SetCellValue(list.Rows[i]["grade"].ToString());
rowTemp.CreateCell(6).SetCellValue(list.Rows[i]["sex"].ToString());
rowTemp.CreateCell(7).SetCellValue(list.Rows[i]["nation"].ToString());
rowTemp.CreateCell(8).SetCellValue(list.Rows[i]["phone"].ToString());
rowTemp.CreateCell(9).SetCellValue(list.Rows[i]["type"].ToString());
rowTemp.CreateCell(10).SetCellValue(String.IsNullOrEmpty(list.Rows[i]["type"].ToString()) ? "未报名" : "报名成功");
rowTemp.CreateCell(11).SetCellValue(list.Rows[i]["createtime"].ToString());
}
var fileName = "英语四六级报考表" + ".xls";//文件名
//将Excel表格转化为流,输出
MemoryStream bookStream = new MemoryStream();//创建文件流
excelBook.Write(bookStream); //文件写入流(向流中写入字节序列)
bookStream.Seek(0, SeekOrigin.Begin);//输出之前调用Seek,把0位置指定为开始位置
Response.AddHeader("Content-Disposition", string.Format("attachment; filename="+ fileName, HttpUtility.UrlEncode(fileName + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
Response.BinaryWrite(bookStream.ToArray());
Response.End();
bookStream.Close();
bookStream.Dispose();
}
}
}