asp.net MVC 导出查询结果到Excel
首先在View视图中有一表单form,导出按钮<input class="btn export" type="button" value="导出" />,在js写入点击导出按钮的代码,如下:
$(".export").click(function () { window.location.href = "/Statis/ExportExecel?data=" + $("form").serialize(); }
控制器下导出功能代码:
public FileResult ExportExecel(SearchInfo info) { //获取list数据 var data = GetSearchList(info).ToList(); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //貌似这里可以设置各种样式字体颜色背景等,但是不是很方便,这里就不设置了 //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); 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("申请人"); //....N行 //将数据逐步写入sheet1各个行 for (int i = 0; i < data.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(data[i].printname.ToString()); rowtemp.CreateCell(1).SetCellValue(data[i].copynumber.ToString()); rowtemp.CreateCell(2).SetCellValue(data[i].pagernumber.ToString()); rowtemp.CreateCell(3).SetCellValue(data[i].printmode.ToString()); rowtemp.CreateCell(4).SetCellValue(data[i].printcolor.ToString()); rowtemp.CreateCell(5).SetCellValue(data[i].printpagersize.ToString()); rowtemp.CreateCell(6).SetCellValue(data[i].printingtype.ToString()); rowtemp.CreateCell(7).SetCellValue(data[i].addtime.ToString()); rowtemp.CreateCell(8).SetCellValue(data[i].User.realname.ToString()); //....N行 } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); DateTime dt = DateTime.Now; string dateTime = dt.ToString("yyyyMMddHHmmssfff"); string fileName = "查询结果" + dateTime + ".xls"; return File(ms, "application/vnd.ms-excel", fileName); } public IQueryable<userprint> GetSearchList(SearchInfo info) { var stat = from p in db.userprints select p; if (info.parentdepname != null) { stat = stat.Where(s => s.User.parentdepname ==info.parentdepname); } if (info.depname != null) { stat = stat.Where(s => s.User.depname == info.depname); } if (!string.IsNullOrEmpty(info.printname)) { stat = stat.Where(s => s.printname.Contains(info.printname)); } if (info.printcolor != null) { stat = stat.Where(s => s.printcolor == info.printcolor); } if (info.printingtype != null) { stat = stat.Where(s => s.printingtype == info.printingtype); } if (info.checkupstate != null) { stat = stat.Where(s => s.checkupstate == (info.checkupstate == 1) ? true : false); } if (info.printstate != null) { stat = stat.Where(s => s.printstate == (info.printstate == 1) ? true : false); } if (!string.IsNullOrEmpty(info.userrealname)) { stat = stat.Where(p => p.User.realname == info.userrealname); } if (info.begintime != null) { var start = info.begintime; var end = info.endtime == null ? DateTime.Now : info.endtime; stat = stat.Where(p => p.addtime > start && p.addtime < end); } //if (Request.IsAjaxRequest()) //{ //} return stat; }
这样就完成了从数据库中查询数据,然后导出到excel
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。