1.下载NPOI
（1）右击项目->选择
（2）安装NPOI
2.Service（这里以学籍列表为例）
有关学籍的字段可查看本人之前的文章：ASP.NET MVC+EasyUi 分页显示数据表格
public class XJService
{
//学籍信息列表
public List<XJ> FindAll()
{
List<XJ> xjs = new List<XJ>();
string sql = " SELECT id,xh,xm,xbm,nj,rxrq,xslb,yxsh,zydm,dsbh" +
" FROM xj" +
" order by xh";
SqlDataReader reader = SQLHelper.ExecuteReader(sql, null);
while (reader.Read())
{
XJ xj = new XJ();
xj.id = Convert.ToInt32(reader["id"]);
xj.xh = reader["xh"].ToString();
xj.xm = reader["xm"].ToString();
xj.xbm = Convert.ToInt32(reader["xbm"]);
xj.nj = reader["nj"].ToString();
xj.rxrq = (DateTime)reader["rxrq"];
xj.yxsh = reader["yxsh"].ToString();
xj.dsbh = reader["dsbh"].ToString();
xj.zydm = reader["zydm"].ToString();
xj.xslb = reader["xslb"].ToString();
Py_xs py_xs = py_xsService.FindByXsbh(xj.yxsh);
xj.py_xs = py_xs;
Py_rkjs py_rkjs = py_rkjsService.FindByJsbh(xj.dsbh);
xj.py_rkjs = py_rkjs;
Py_zy py_zy = py_zyService.FindByZydm(xj.zydm);
xj.py_zy = py_zy;
Dm_xslb dm_xslb = dm_xslbService.FindByXslbm(xj.xslb);
xj.dm_xslb = dm_xslb;
xjs.Add(xj);
}
reader.Close();
return xjs;
}
//根据id查找学籍信息
public XJ FindById(int id)
{
XJ xj = new XJ();
string sql = " SELECT id,xh,xm,xbm,nj,rxrq,xslb,yxsh,zydm,dsbh" +
" FROM xj" +
" where id=@id";
SqlParameter[] pms = { new SqlParameter("id",id)};
SqlDataReader reader = SQLHelper.ExecuteReader(sql, pms);
if (reader.Read())
{
xj.id = Convert.ToInt32(reader["id"]);
xj.xh = reader["xh"].ToString();
xj.xm = reader["xm"].ToString();
xj.xbm = Convert.ToInt32(reader["xbm"]);
xj.nj = reader["nj"].ToString();
xj.rxrq = (DateTime)reader["rxrq"];
xj.yxsh = reader["yxsh"].ToString();
xj.dsbh = reader["dsbh"].ToString();
xj.zydm = reader["zydm"].ToString();
xj.xslb = reader["xslb"].ToString();
Py_xs py_xs = py_xsService.FindByXsbh(xj.yxsh);
xj.py_xs = py_xs;
Py_rkjs py_rkjs = py_rkjsService.FindByJsbh(xj.dsbh);
xj.py_rkjs = py_rkjs;
Py_zy py_zy = py_zyService.FindByZydm(xj.zydm);
xj.py_zy = py_zy;
Dm_xslb dm_xslb = dm_xslbService.FindByXslbm(xj.xslb);
xj.dm_xslb = dm_xslb;
}
reader.Close();
return xj;
}
//多条件查询
public List<XJ> Search(XJ xj)
{
List<XJ> xjs = new List<XJ>();
//加上"where 1=1"恒等条件，就无须在后续判断sql中是否包含where关键字，直接拼接即可，减少了判断语句
string sql = " SELECT id,xh,xm,xbm,nj,rxrq,xslb,yxsh,zydm,dsbh " +
" FROM xj " +
" where 1=1 ";
//用count记录需要多少个参数
int count = 0;
if (xj.xh != null)
{
count++;
}
if (xj.xm != null)
{
count++;
}
if (xj.zydm != null)
{
count++;
}
if (xj.yxsh != null)
{
count++;
}
if (xj.xslb != null)
{
count++;
}
if (xj.nj != null)
{
count++;
}
SqlParameter[] pms = new SqlParameter[count];
//用index下标记录参数下标
int index = 0;
if (xj.xh != null)
{
sql += "and xj.xh like '%'+@xh+'%' ";
pms[index] = new SqlParameter("xh", xj.xh);
index++;
}
if (xj.xm != null)
{
sql += "and xj.xm like '%'+@xm+'%' ";
pms[index] = new SqlParameter("xm", xj.xm);
index++;
}
if (xj.zydm != null)
{
sql += "and xj.zydm=@zydm ";
pms[index] = new SqlParameter("zydm", xj.zydm);
index++;
}
if (xj.yxsh != null)
{
sql += "and xj.yxsh=@yxsh ";
pms[index] = new SqlParameter("yxsh", xj.yxsh);
index++;
}
if (xj.xslb != null)
{
sql += "and xj.xslb=@xslb ";
pms[index] = new SqlParameter("xslb", xj.xslb);
index++;
}
if (xj.nj != null)
{
sql += "and xj.nj=@nj ";
pms[index] = new SqlParameter("nj", xj.nj);
}
SqlDataReader reader = SQLHelper.ExecuteReader(sql, pms);
while (reader.Read())
{
XJ xj2 = new XJ();
xj2.id = Convert.ToInt32(reader["id"]);
xj2.xh = reader["xh"].ToString();
xj2.xm = reader["xm"].ToString();
xj2.xbm = Convert.ToInt32(reader["xbm"]);
xj2.nj = reader["nj"].ToString();
xj2.rxrq = (DateTime)reader["rxrq"];
xj2.yxsh = reader["yxsh"].ToString();
xj2.dsbh = reader["dsbh"].ToString();
xj2.zydm = reader["zydm"].ToString();
xj2.xslb = reader["xslb"].ToString();
Py_xs py_xs = py_xsService.FindByXsbh(xj2.yxsh);
xj2.py_xs = py_xs;
Py_rkjs py_rkjs = py_rkjsService.FindByJsbh(xj2.dsbh);
xj2.py_rkjs = py_rkjs;
Py_zy py_zy = py_zyService.FindByZydm(xj2.zydm);
xj2.py_zy = py_zy;
Dm_xslb dm_xslb = dm_xslbService.FindByXslbm(xj2.xslb);
xj2.dm_xslb = dm_xslb;
xjs.Add(xj2);
}
reader.Close();
return xjs;
}
}
3. View
搜索框中具体的值可以查看本人之前的文章：ASP.NET MVC+EasyUi 实现二级联动以及多条件查询
<!--搜索栏start-->
<div id="searchDiv" style="padding:3px">
<span>学号：</span>
<input id="xh" class="easyui-textbox" style="line-height:20px;border:1px solid #ccc;width:100px;">
<span>姓名：</span>
<input id="xm" class="easyui-textbox" style="line-height:20px;border:1px solid #ccc;width:100px;">
<span>年级：</span>
<select class="easyui-combobox" id="nj" style="width:100px;"></select>
<!--以下循环/XJ/Index封装的ViewBag数据-->
<span>院系：</span>
<select class="easyui-combobox" id="yxsh" style="width:120px;"></select>
<span>专业：</span>
<select class="easyui-combobox" id="zydm" style="width:120px;"></select>
<span>类别：</span>
<select class="easyui-combobox" id="xslb" style="width:120px;"></select>
<a href="#" class="easyui-linkbutton" iconCls="icon-search" onclick="doSearch()">搜索</a>
</div>
<!--搜索栏end-->
<!--导出Excel-->
<a id="daochuexcel" class="easyui-linkbutton" data-options="iconCls:'icon-redo'" style="float:right;position:relative;bottom:8px;margin-left:5px;" onclick="daochuexcel()">导出Excel</a>
编写点击“导出Excel“”后的事件代码
//导出表格
function daochuexcel() {
//获取页面选中的行
var rows = $('#dg').datagrid('getSelections');
//若未选中，按照查询条件导出
if (rows.length == 0) {
//获取查询条件框中的值
var xh = $('#xh').val();
var xm = $('#xm').val();
var nj = $('#nj').combobox("getValue");
var zydm = $('#zydm').combobox("getValue");
var xslb = $('#xslb').combobox("getValue");
var yxsh = $('#yxsh').combobox("getValue");
//编写url
url = "/XJ/Export?1=1";
//以下为拼接url的值，当有查询条件时才进行拼接
if (xh != '' && xh != null) {
url += "&xh=" + xh;
}
if (xm != '' && xm != null) {
url += "&xm=" + xm;
}
if (nj != 0 && nj != "--全部--") {
url += "&nj=" + nj;
}
if (zydm != 0 && zydm != "--全部--") {
url += "&zydm=" + zydm;
}
if (xslb != 0 && xslb != "--全部--") {
url += "&xslb=" + xslb;
}
if (yxsh != 0 && yxsh != "--全部--") {
url += "&yxsh=" + yxsh;
}
//调用拼接完成后的url
$('#daochuexcel').attr("href", url);
}
//若选中，导出选中数据
else {
var flag = confirm("确定导出选中的学籍信息吗?");
if (flag) {
//定义学籍信息数组
var ids = [];
//通过选中行，对数组进行添加值操作
for (var i = 0; i < rows.length; i++) {
ids.push(rows[i].id);
}
//将数组转换为数组，为的是将信息传至后台
var strify = JSON.stringify(ids);
$('#daochuexcel').attr("href", "/XJ/ChooseExport?strify=" + strify);
}
}
}
4. Controller
public class XJController : Controller
{
XJService xjService = new XJService();
//导出查询过后的数据
public ActionResult Export()
{
List<XJ> xjs = new List<XJ>();
//获取页面传递的查询条件参数
string xh = Request.Params["xh"];
string xm = Request.Params["xm"];
string zydm = Request.Params["zydm"];
string xslb = Request.Params["xslb"];
string yxsh = Request.Params["yxsh"];
string nj = Request.Params["nj"];
//若查询条件都为空，直接返回所有列表
if (xh == null && xm == null && zydm == null && xslb == null && yxsh == null && nj == null)
{
xjs = xjService.FindAll();
}
//若查询条件中有一个不为空，则调用查询方法
else
{
XJ xj = new XJ();
xj.xh = xh;
xj.xm = xm;
xj.zydm = zydm;
xj.xslb = xslb;
xj.yxsh = yxsh;
xj.nj = nj;
xjs = xjService.Search(xj);
}
//调用导出方法
return ExportExcel(xjs);
}
//导出选中数据
public ActionResult ChooseExport(string strify)
{
List<XJ> xjs = new List<XJ>();
//将"["和"]"用替换的方法去除
string newstr = strify.Replace("[", "");
newstr = newstr.Replace("]", "");
//用","将字符串分割成数组
string[] temp = newstr.Split(',');
//定义存放学籍信息id的整型数组
int[] ids = new int[temp.Length];
//用for循环将字符串数组中的元素转换成整型
for (int i = 0; i < temp.Length; i++)
{
ids[i] = Convert.ToInt32(temp[i]);
}
//根据学籍id查询学籍信息并添加至学籍列表
foreach (int id in ids)
{
XJ xj = xjService.FindById(id);
xjs.Add(xj);
}
return ExportExcel(xjs);
}
//导出学籍信息表格
public ActionResult ExportExcel(List<XJ> xjs)
{
//创建Excel对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();//创建Excel对象工作簿
#region 给导出的Excel设置表头
NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0);//给sheet添加第一行的头部标题
row1.CreateCell(0).SetCellValue("序号");
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("导师");
sheet.SetColumnWidth(5, 20 * 200);//设置入学日期列的宽度
sheet.SetColumnWidth(6, 20 * 200);
sheet.SetColumnWidth(7, 20 * 200);
sheet.SetColumnWidth(8, 20 * 200);
#endregion
#region 给sheet的每行添加数据
for (int i = 0; i < xjs.Count; i++)
{
NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1);//给sheet添加一行
row.CreateCell(0).SetCellValue(i + 1);
if (xjs[i].xh == null)
{
row.CreateCell(1).SetCellValue(0);
}
else
{
row.CreateCell(1).SetCellValue(xjs[i].xh);
}
if (xjs[i].xm == null)
{
row.CreateCell(2).SetCellValue(0);
}
else
{
row.CreateCell(2).SetCellValue(xjs[i].xm);
}
if (xjs[i].xbm == 0)
{
row.CreateCell(3).SetCellValue(0);
}
else
{
if (xjs[i].xbm == 1)
{
row.CreateCell(3).SetCellValue("男");
}
else
{
row.CreateCell(3).SetCellValue("女");
}
}
if (xjs[i].nj == null)
{
row.CreateCell(4).SetCellValue(0);
}
else
{
row.CreateCell(4).SetCellValue(xjs[i].nj);
}
if (xjs[i].rxrq == null)
{
row.CreateCell(5).SetCellValue(0);
}
else
{
row.CreateCell(5).SetCellValue(xjs[i].rxrq.ToShortDateString());
}
if (xjs[i].dm_xslb == null)
{
row.CreateCell(6).SetCellValue(0);
}
else
{
row.CreateCell(6).SetCellValue(xjs[i].dm_xslb.xslb);
}
if (xjs[i].py_xs == null)
{
row.CreateCell(7).SetCellValue(0);
}
else
{
row.CreateCell(7).SetCellValue(xjs[i].py_xs.xsmc);
}
if (xjs[i].py_zy == null)
{
row.CreateCell(8).SetCellValue(0);
}
else
{
row.CreateCell(8).SetCellValue(xjs[i].py_zy.zymc);
}
if (xjs[i].py_rkjs == null)
{
row.CreateCell(9).SetCellValue(0);
}
else
{
row.CreateCell(9).SetCellValue(xjs[i].py_rkjs.jsxm);
}
}
#endregion
//输出的文件名称
string fileName = "学籍信息.xls";
//把Excel转化为文件流，输出
MemoryStream BookStream = new MemoryStream();//定义文件流
book.Write(BookStream);//将工作薄写入文件流
BookStream.Seek(0, SeekOrigin.Begin);//输出之前调用Seek（偏移量，游标位置）方法：获取文件流的长度
return File(BookStream, "application/vnd.ms-excel", fileName); // 文件类型/文件名称/
}
}
说明：本人在页面调用该方法时使用的是a标签
扫一扫
1525
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
