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

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。