导出Excel表格

导出Excel表格

将表格数据导出为Excel表格，其实导出数据并不是很难。我自己在页面上也写了一个导出功能，就和大家一起讲一讲。

点页面上的导出按钮后，页面会发出提示询问是否要导出全部数据，如果不需要全部导出可以根据前面的复选框来进行筛选需要导出的数据。选择好自己需要导出的数据，点击确定就会把一个带有页面上选择好的数据的Excel表格下载到电脑上。

首先我们需要先写一个导出按钮的样式：

然后接下来就写按钮的方法来，实现这一功能：

function exporte() {
            //获取参数json
            var searchText = $("#searchText").val();
            var strTemp = '&strConditions=' + searchText;
            layer.confirm('您确定要导出全部商品信息？', { icon: 3, title: "提示" }, function (index) {
                layer.close(index);//关闭提示框
                //新标签页打开下载excel的url，下载excel文件
                window.open('/SystemBase/Supplier/ExportSupplierData?' + strTemp.substring(1, strTemp.length));
            });
        }

再写完控制器的代码就完成了：

public ActionResult ExportSupplierData(string strConditions)
        {
            var list = (from tbSupplier in myModel.S_Supplier
                        join tbSupplierType in myModel.S_SupplierType on tbSupplier.supplierTypeID equals tbSupplierType.supplierTypeID
                        join tbDistrict in myModel.D_District on tbSupplier.disID equals tbDistrict.disID
                        join tbSettlementMethod in myModel.S_SettlementMethod on tbSupplier.settlementMethodID equals tbSettlementMethod.settlementMethodID
                        select new SupplierVo
                        {
                            supplierID = tbSupplier.supplierID,//供货商ID
                            supplierTypeID = tbSupplier.supplierTypeID,//供货商类型ID
                            disID = tbSupplier.disID,//地区ID
                            settlementMethodID = tbSupplier.settlementMethodID,//结算方式ID
                            supplierName = tbSupplier.supplierName,//供货商名称
                            contactPerson = tbSupplier.contactPerson,//联系人
                            contactNumber = tbSupplier.contactNumber,//联系电话
                            initialPayable = tbSupplier.initialPayable,//期初应付
                            location = tbSupplier.location,//联系地址
                            defaultNo = tbSupplier.defaultNo,//默认供货商否
                            disabledNo = tbSupplier.disabledNo,//禁用否
                            annotation = tbSupplier.annotation,//备注
                            supplierType = tbSupplierType.supplierType,//供货商类型
                            disName = tbDistrict.disName,//地区
                            settlementMethod = tbSettlementMethod.settlementMethod,//结算方式
                        });
            if (!string.IsNullOrEmpty(strConditions))
            {
                list = list.Where(o => o.supplierName.Contains(strConditions) || o.contactPerson.Contains(strConditions) || o.contactNumber.Contains(strConditions) ||
                o.supplierType.Contains(strConditions) || o.disName.Contains(strConditions) || o.settlementMethod.Contains(strConditions) || o.location.Contains(strConditions));
            }
            List<SupplierVo> query = list.ToList();
            string templatePath = Server.MapPath("~/Document/SupplierDataTemplate.xls");
            if (!System.IO.File.Exists(templatePath))
            {
                return Content("导出失败，请联系网站管理员");
            }
            FileStream templateStream = System.IO.File.Open(templatePath, FileMode.Open);
            NPOI.HSSF.UserModel.HSSFWorkbook excelBookTemplate = new NPOI.HSSF.UserModel.HSSFWorkbook(templateStream);
            NPOI.SS.UserModel.ISheet sheet = excelBookTemplate.GetSheetAt(0);
            NPOI.SS.UserModel.ICellStyle style = excelBookTemplate.CreateCellStyle();
            NPOI.SS.UserModel.IRow rowTitle = sheet.GetRow(0);
            rowTitle.GetCell(0).SetCellValue("供货商信息" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            int index = 2;
            for (int i = 0; i < query.Count(); i++)
            {
                NPOI.SS.UserModel.IRow row = sheet.CreateRow(index);
                row.CreateCell(0).SetCellValue(i + 1);
                row.CreateCell(1).SetCellValue(query[i].supplierName.ToString());
                row.CreateCell(2).SetCellValue(query[i].supplierType.ToString());
                row.CreateCell(3).SetCellValue(query[i].disName.ToString());
                row.CreateCell(4).SetCellValue(query[i].contactPerson.ToString());
                row.CreateCell(5).SetCellValue(query[i].contactNumber.ToString());
                row.CreateCell(6).SetCellValue(query[i].initialPayable.ToString());
                row.CreateCell(7).SetCellValue(query[i].settlementMethod.ToString());
                row.CreateCell(8).SetCellValue(query[i].location.ToString());
                row.CreateCell(9).SetCellValue(query[i].defaultNo == true ? "是" : "否");
                row.CreateCell(10).SetCellValue(query[i].disabledNo == true ? "是" : "否");                row.CreateCell(11).SetCellValue(query[i].annotation == null ? "" : query[i].annotation.Trim());
                for (int j = 0; j < row.Cells.Count; j++)
                {
                    row.GetCell(j).CellStyle = style;
                }
                index++;
            }
            string fileName = "供货商信息" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";
            MemoryStream BookStream = new MemoryStream();
            excelBookTemplate.Write(BookStream);
            BookStream.Seek(0, SeekOrigin.Begin);
            return File(BookStream, "application/vnd.ms-excel", fileName);
        }