数据导出

最新推荐文章于 2024-03-18 17:00:35 发布

原创最新推荐文章于 2024-03-18 17:00:35 发布 · 242 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MVC实现数据导出

在一些网页或者一个软件上，会提供数据导出的功能，
下面一个导出操作，可以全部数据导出也可以多个条件筛选想要的数据导出；
需要用到一个引用：NPOI

mvc控制器方法：
  public ActionResult ExportToExcel(string ClientNumber, string Productphone, 
                    string ClientName, string UnitlPhone, string Handlers, 
                    string Note, int EmployeeID, string SearchKeyWord)
        {
           
            #region 查询导出数据
            var listClient = (from tbClient in myModels.S_Client //客户
                              join tbSupplier in myModels.S_Supplier on tbClient.SupplierID equals 								tbSupplier.SupplierID into joinDeptEmp //供应商
                              join tbEmployee in myModels.S_Employee on tbClient.EmployeeID equals 								tbEmployee.EmployeeID into joinDeptEmp1//业务员
                              join tbColl in myModels.B_Collection on tbClient.CollectionID equals 								tbColl.CollectionID into joinDeptEmp2 //收款
                              join tblint in myModels.B_Lnitialreceipt on tbClient.LnitialreceiptID 								equals tblint.LnitialreceiptID into joinDeptEmp3 //期初
                              //左连接
                              //例如：tbClient 表为左 tbSupplier 表为右
                              from tbSupplier in joinDeptEmp.DefaultIfEmpty()
                              from tbEmployee in joinDeptEmp1.DefaultIfEmpty()
                              from tbColl in joinDeptEmp2.DefaultIfEmpty()
                              from tblint in joinDeptEmp3.DefaultIfEmpty()
                              //倒叙排序   //orderby排序 descending倒叙 
                              orderby tbClient.ClientID descending
                              select new Client
                              {
                                  #region 查询的字段（数据）
                                  ClientID = tbClient.ClientID,                   //客户ID
                                  SupplierID = tbSupplier.SupplierID,             //供应商ID
                                  EmployeeID = tbEmployee.EmployeeID,             //员工ID
                                  LnitialreceiptID = tbColl.CollectionID,         //收款ID
                                  Numder = tbClient.ClientNumber.Trim(),          //客户编号
                                  Name = tbClient.ClientName,                     //客户名称
                                  Phone = tbClient.Contactphone,                  //联系电话  
                                  UnitpPhone = tbClient.ClientUnitphone,          //单位电话
                                  Contact = tbClient.Clientcontact,               //联系人
                                  Address = tbClient.Contactaddress,              //联系地址               
                                  Note = tbClient.ClientNote,                     //备注
                                  State = tbClient.CientState,         //状态
                                  Correlation = tbSupplier != null ? tbSupplier.SupplierName : "",          
                                  Associatedclerk = tbEmployee != null ? tbEmployee.EmpName : "",           
                                  money = tbColl.CollAccount,
                                  #endregion
                              }).ToList();
            #endregion
            #region
            //可进行筛选导出数据
            //客户编号  
            if (!string.IsNullOrEmpty(ClientNumber))
            {
                listClient = listClient.Where(a => a.Numder.Contains(ClientNumber)).ToList();
            }
            // 客户名称
            if (!string.IsNullOrEmpty(ClientName))
            {
                listClient = listClient.Where(a => a.Name.Contains(ClientName)).ToList();
            }
            //联系电话
            if (!string.IsNullOrEmpty(Productphone))
            {
                listClient = listClient.Where(a => a.Phone.Contains(Productphone)).ToList();
            }
            //单位电话
            if (!string.IsNullOrEmpty(UnitlPhone))
            {
                listClient = listClient.Where(a => a.UnitpPhone.Contains(UnitlPhone)).ToList();
            }
            //联系人
            if (!string.IsNullOrEmpty(Handlers))
            {
                listClient = listClient.Where(a => a.Contact.Contains(Handlers)).ToList();
            }
            //备注
            if (!string.IsNullOrEmpty(Note))
            {
                listClient = listClient.Where(a => a.Note.Contains(Note)).ToList();
            }
            //关联业务员
            if (EmployeeID > 0)
            {
                listClient = listClient.Where(m => m.EmployeeID == EmployeeID).ToList();
            }
            //综合查询
            if (!string.IsNullOrEmpty(SearchKeyWord))
            {
                listClient = listClient.Where(m => m.Name.Contains(SearchKeyWord.Trim()) ||
                             m.Numder.Contains(SearchKeyWord) || m.Contact.Contains(SearchKeyWord) ||
                             m.Phone.Contains(SearchKeyWord) || m.UnitpPhone.Contains(SearchKeyWord) ||
                             m.Address.Contains(SearchKeyWord) || 								 							  m.Note.Contains(SearchKeyWord)).ToList();
            }
            #endregion
            //将查询出来的数据转化为对象列表的格式
            List<Client> listExmaince = listClient.ToList();
            //创建工作簿Excel
            HSSFWorkbook excelBook = new HSSFWorkbook();
            //为工作簿创建工作表并命名
            NPOI.SS.UserModel.ISheet sheet1 = excelBook.CreateSheet("客户信息");
            //编写表头
            //(1)表头 //创建第一行  //创建标题并设置字段
            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("关联业务员");
            row1.CreateCell(9).SetCellValue("备注");
            row1.CreateCell(10).SetCellValue("状态");
            //（2） 创建数据行
            for (int i = 0; i < listClient.Count(); i++)
            {
                //创建行
                NPOI.SS.UserModel.IRow RoeTemp = sheet1.CreateRow(i + 1);
                RoeTemp.CreateCell(0).SetCellValue(listExmaince[i].Numder);
                RoeTemp.CreateCell(1).SetCellValue(listExmaince[i].Name);
                RoeTemp.CreateCell(2).SetCellValue(listExmaince[i].money.ToString());
                RoeTemp.CreateCell(3).SetCellValue(listExmaince[i].Contact);
                RoeTemp.CreateCell(4).SetCellValue(listExmaince[i].Phone);
                RoeTemp.CreateCell(5).SetCellValue(listExmaince[i].UnitpPhone);
                RoeTemp.CreateCell(6).SetCellValue(listExmaince[i].Address);
                RoeTemp.CreateCell(7).SetCellValue(listExmaince[i].Correlation);
                RoeTemp.CreateCell(8).SetCellValue(listExmaince[i].Associatedclerk);
                RoeTemp.CreateCell(9).SetCellValue(listExmaince[i].Note);
                RoeTemp.CreateCell(10).SetCellValue(listExmaince[i].State.ToString());
            }
            //文件命名
            var fileName = "客户信息" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";
            //将Excel 表格转化为IO流 输出MemoryStream
            MemoryStream bookStream = new MemoryStream();
            //文件写入流（向流中写入字节序列）
            excelBook.Write(bookStream);
            //输出之前调用Seek（偏移量，游标位置) 把0位置指定为开始位置
            bookStream.Seek(0, SeekOrigin.Begin);
            //return返回 File表示文件类型
            return File(bookStream, "application/vnd.ms-excel", fileName);
        }

页面JS代码

      function batauditB() {
            //提取查询条件
            var strSeachWhere = "";
            for (var key in SalestabTitles.config.where) {
                strSeachWhere += '&' + key + '=' + SalestabTitles.config.where[key];
            }
            //客户编号
            var ClientNumber = $("#ClientNumber").val();
            if (ClientNumber == undefined) {
                ClientNumber = "";
            }
            //客户名称
            var ClientName = $("#ClientName").val();
            if (ClientName == undefined) {
                ClientName = "";
            }
            //联系电话
            var Productphone = $("#Productphone").val();
            if (Productphone == undefined) {
                Productphone = "";
            }
            //单位电话
            var UnitlPhone = $("#UnitlPhone").val();
            if (UnitlPhone == undefined) {
                UnitlPhone = "";
            }
            //联系人
            var Handlers = $("#Handlers").val();
            if (Handlers == undefined) {
                Handlers = "";
            }
            //备注
            var Note = $("#Note").val();
            if (Note == undefined) {
                Note = "";
            }
            //关联业务员
            var EmployeeID = $("#EmployeeID").val();
            if (EmployeeID == "" || EmployeeID == undefined) {
                EmployeeID = 0;
            }
            var SearchKeyWord = $("#SearchKeyWorddddd").val()
            if (SearchKeyWord == undefined) {
                SearchKeyWord = "";
            }
            var strTemp = '&ClientNumber=' + ClientNumber + '&Productphone=' + Productphone + '&ClientName=' + ClientName + '&UnitlPhone=' + UnitlPhone + '&Handlers=' + Handlers + '&Note=' + Note + '&EmployeeID=' + EmployeeID + '&SearchKeyWord=' + SearchKeyWord;
             //判断是否符合导出的条件
             if (strSeachWhere == strTemp) {
                layer.confirm('您确定要导出' + SalestabTitles.config.page.count + '条客户信息', 
{ icon: 3, title: '提示' },
                    function (index) {
                        layer.close(index);
                        window.open('/Huitubeguest/Customersupplier/ExportToExcel?' + 					                      strTemp.substring(1, strTemp.length));
                    });
            }
            else {
                layer.msg("请查询出要导出的数据!", { icon: 0, skin: "layui-layer-molv" });
            }
        }

导出的操作就不进行操作了，至于上面的筛选条件，可根据实际的情况选择，可有可无，不需要的话去掉该筛选条件，以及对应的字段即可。