.NET 案例代码

#region 查询导出物料报销情况

        /// <summary>
        /// 查询物料报销明细
        /// </summary>
        //[Chaolj.Apps.Core.Utils.GlobalContext.CheckApi]
        public  FileResult QueryMaterialPayStatus(FormCollection formData)
        {
            string qryString = this.Request.Params["qryString"].SafeConvert().ToStr();
            DateTime beginDate = this.Request.Params["beginDate"].SafeConvert().ToDateTime().Date;
            DateTime endDate = this.Request.Params["endDate"].SafeConvert().ToDateTime().Date;

            #region 1. 查询数据
            List<ZTZS.DAL.ZSERP.vMaterialSheetDetail> list = new List<ZTZS.DAL.ZSERP.vMaterialSheetDetail>();  // 物料出入库明细清单

            // 1. 从老erp查询出相应的入库明细；
            using (var erpdb = ZTZS.DAL.ZSERP.ZSErpContext.Create())
            {
                var qry = erpdb.vMaterialSheetDetail.Where(p => p.Transdate >= beginDate && p.Transdate < endDate && (p.SheetTypeNo == "PR" || p.SheetTypeNo == "RI"));
                list = qry.ToList();
            }

            List<MaterialIsSettlement> listSettlement = new List<MaterialIsSettlement>();  // 已进入报销流程的单据；
            // 2. 从新erp判定哪些明细已经报销；
            using (var h3db2 = Chaolj.ZTBPM.DAL.H3Context.Create())
            using (var h3Db = ZTZS.DAL.H3.H3Context.Create())
            {
                // 2.1 获取当前所有单据
                var listBillNo = list.Select(m => m.SheetNo).Distinct().ToList();
                // 2.2 查询当前单据列表，所有已进入报销流程的单据；
                var billNos1 = h3db2.BD_ReceiveOrReturnOrder.Where(m => listBillNo.Contains(m.OrderNo)).Select(m => new MaterialIsSettlement { BillNo = m.OrderNo, InstanceId = m.INSTANCEID }).ToList();
                var billNos2 = h3Db.报销材料报销入库单.Where(m => listBillNo.Contains(m.单据编号)).Select(m => new MaterialIsSettlement { BillNo = m.单据编号, InstanceId = m.INSTANCEID }).ToList();

                // 2.3 用sql语句查询
                string strBillNos = "";
                for (int i = 0; i < listBillNo.Count(); i++)
                {
                    strBillNos += "'" + listBillNo[i] + "'";
                    if (i < listBillNo.Count - 1) strBillNos += ",";
                }
                string strWhere = setConditionByOr(strBillNos, 50, "单据编号");
                string sql = "SELECT * FROM h3.报销材料报销入库单 WHERE " + strWhere + "";
                var billNos3Model = h3Db.Database.SqlQuery<MaterialSheetInSettlement>(sql).ToList();
                var billNos3 = billNos3Model.Select(m => new MaterialIsSettlement { BillNo = m.单据编号, InstanceId = m.INSTANCEID }).ToList();

                string strWhere4 = setConditionByOr(strBillNos, 50, "入库单编号");
                string sql4 = "SELECT * FROM h3.材料零星报销入库单 WHERE " + strWhere4 + "";
                var billNos4Model = h3Db.Database.SqlQuery<MaterialSheetLinXinSettlement>(sql4);
                var billNos4 = billNos4Model.Select(m => new MaterialIsSettlement { BillNo = m.入库单编号, InstanceId = m.INSTANCEID }).ToList();

                listSettlement.AddRange(billNos1);
                listSettlement.AddRange(billNos2);
                listSettlement.AddRange(billNos3);
                listSettlement.AddRange(billNos4);

                listSettlement = listSettlement.Distinct().ToList();
            }
            // 3. 从支付中心判定，哪些数据已经支付、待支付等状态；
            List<string> listWorkflowId = new List<string>();
            listWorkflowId = listSettlement.Select(m => m.InstanceId).ToList();

            using (var erp = Chaolj.DAL.ERP.ERPContext2.Create())
            {
                // 已支付金额:FromTypeName in (材料_材料报销，材料_临星材料报销)
                var qryPay = erp.vFI_Order_Payment.Where(p => listWorkflowId.Contains(p.InstanceId) && (p.Status == 2 || p.Status == 4)).Select(m => new { InstanceId = m.InstanceId }).ToList(); // 状态为：已结算、已完成；
                listSettlement = (from a in listSettlement
                                    join p in qryPay on a.InstanceId equals p.InstanceId into ap
                                    from api in ap.DefaultIfEmpty()
                                    select new MaterialIsSettlement
                                    {
                                        BillNo = a.BillNo,
                                        InstanceId = a.InstanceId,
                                        PayStatus = (api == null || api.InstanceId == null) ? "未支付" : "已支付"
                                    }).ToList();
            }

            var result = (from a in list
                            join s in listSettlement on a.SheetNo equals s.BillNo into sa
                            from sai in sa.DefaultIfEmpty()
                            select new
                            {
                                Id = a.Id,
                                项目编码 = a.ProjectNo,
                                项目名称 = a.ProjectName,
                                供应商编码 = a.VenderCode,
                                供应商名称 = a.VenderName,
                                单据编码 = a.SheetNo,
                                Line = a.Line,
                                交易时间 = a.Transdate,
                                物料编码 = a.ItemNo,
                                类别 = a.ItemType,
                                大类 = a.ItemMainClass,
                                子类 = a.ItemSubClass,
                                FSB = a.FSB,
                                厂家 = a.Manufacturer,
                                规格 = a.Specification,
                                品牌 = a.Brand,
                                属性 = a.Property,
                                单位 = a.Uom,
                                数量 = a.Qty,
                                单价 = a.Price,
                                金额 = a.Amount,
                                描述 = a.Description,
                                单据状态 = a.Stat,
                                业务类型 = a.SheetTypeNo == "PR" ? "入库" : "退货",
                                报销状态 = (sai == null || sai.InstanceId == null) ? "未报销" : "已报销",
                                支付状态 = sai == null ? "" : sai.PayStatus
                            }).ToList();

            #endregion

            #region 2. 生成xls
            #region 1.列头定义
            List<string> lstTitle = new List<string>
                {
                    "项目编码",// 0
                    "项目名称", // 1
                    "供应商编码",// 2
                    "供应商名称", // 3
                    "单据编码",// 4
                    "交易时间",// 5
                    "物料编码",//6
                    "类别",// 7
                    "大类",// 8
                    "子类",// 9
                    "FSB",// 10
                    "厂家",// 11
                    "规格",// 12
                    "品牌",// 13
                    "属性",// 14
                    "单位",// 15
                    "数量",// 16
                    "单价",// 17
                    "金额",// 18
                    "描述",// 19
                    "单据状态", // 20
                    "业务类型", // 21
                    "报销状态", // 22
                    "支付状态",// 23

                };
            #endregion
            IWorkbook book = new HSSFWorkbook();
            ISheet sheet = book.CreateSheet("Sheet1");
            IRow rowTitle = sheet.CreateRow(0);
            ICellStyle style = book.CreateCellStyle();
            style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中  
            for (int i = 0; i < lstTitle.Count; i++)
            {
                rowTitle.CreateCell(i).SetCellValue(lstTitle[i]);
            }
            if (list != null)
            {
                result.OrderBy(p => p.单据编码);
                int start = 0;//记录同组开始行号
                int end = 0;//记录同组结束行号
                string temp = "";//记录上一行的值
                for (int i = 0; i < result.Count(); i++)
                {

                    IRow row = sheet.CreateRow(i + 1);
                    #region 单元格赋值
                    row.CreateCell(0).SetCellValue(result[i].项目编码);
                    row.CreateCell(1).SetCellValue(result[i].项目名称);
                    row.CreateCell(2).SetCellValue(result[i].供应商编码);
                    row.CreateCell(3).SetCellValue(result[i].供应商名称);
                    row.CreateCell(4).SetCellValue(result[i].单据编码);
                    row.CreateCell(5).SetCellValue(result[i].交易时间.ToString());
                    row.CreateCell(6).SetCellValue(result[i].物料编码);
                    row.CreateCell(7).SetCellValue(result[i].类别);
                    row.CreateCell(8).SetCellValue(result[i].大类);
                    row.CreateCell(9).SetCellValue(result[i].子类);
                    row.CreateCell(10).SetCellValue(result[i].FSB);
                    row.CreateCell(11).SetCellValue(result[i].厂家);
                    row.CreateCell(12).SetCellValue(result[i].规格);
                    row.CreateCell(13).SetCellValue(result[i].品牌);
                    row.CreateCell(14).SetCellValue(result[i].属性);
                    row.CreateCell(15).SetCellValue(result[i].单位);
                    row.CreateCell(16).SetCellValue(result[i].数量.ToString("f2"));
                    row.CreateCell(17).SetCellValue(result[i].单价.ToString("f4"));
                    row.CreateCell(18).SetCellValue(result[i].金额.ToString("f4"));
                    row.CreateCell(19).SetCellValue(result[i].描述);
                    row.CreateCell(20).SetCellValue(result[i].单据状态);
                    row.CreateCell(21).SetCellValue(result[i].业务类型);
                    row.CreateCell(22).SetCellValue(result[i].报销状态);
                    row.CreateCell(23).SetCellValue(result[i].支付状态);
                    #endregion

                    row.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).SetCellType(CellType.String);
                    var cellText = row.Cells[0].StringCellValue;//获取当前行 第1列的单元格的值

                    if (cellText == temp)//上下行相等，记录要合并的最后一行
                    {
                        end = i;
                    }
                    else//上下行不等，记录
                    {
                        if (start != end)
                        {
                            //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
                            //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                            CellRangeAddress region = new CellRangeAddress(start + 1, end + 1, 0, 0);
                            sheet.AddMergedRegion(region);
                        }
                        start = i;
                        end = i;
                        temp = cellText;
                    }
                }
            }
            #endregion

            #region 3. 格式化并保存
            for (int i = 0; i < 7; i++)
            {
                sheet.AutoSizeColumn(i);//i：根据标题的个数设置自动列宽
            }

            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            #endregion

            return File(ms, "application/vnd.ms-excel", "出入库物料报销明细.xls");
        }

        /// <summary>
        /// 查询单据报销明细
        /// </summary>
        //[Chaolj.Apps.Core.Utils.GlobalContext.CheckApi]
        public FileResult QueryMaterialSheetPayStatus(FormCollection formData)
        {
            string qryString = this.Request.Params["qryString"].SafeConvert().ToStr();
            DateTime beginDate = this.Request.Params["beginDate"].SafeConvert().ToDateTime().Date;
            DateTime endDate = this.Request.Params["endDate"].SafeConvert().ToDateTime().Date;

            #region 1. 查询数据
            List<ZTZS.DAL.ZSERP.MaterialSheet> list = new List<ZTZS.DAL.ZSERP.MaterialSheet>();  // 物料出入库明细清单

            // 1. 从老erp查询出相应的入库明细；
            using (var erpdb = ZTZS.DAL.ZSERP.ZSErpContext.Create())
            {
                var qry = erpdb.MaterialSheet.Where(p => p.Transdate >= beginDate && p.Transdate < endDate && p.Stat == "审核" && (p.SheetTypeNo == "PR" || p.SheetTypeNo == "RI"));
                list = qry.ToList();
            }

            List<MaterialIsSettlement> listSettlement = new List<MaterialIsSettlement>();  // 已进入报销流程的单据；
            // 2. 从新erp判定哪些明细已经报销；
            using (var h3db2 = Chaolj.ZTBPM.DAL.H3Context.Create())
            using (var h3Db = ZTZS.DAL.H3.H3Context.Create())
            {
                // 2.1 获取当前所有单据
                var listBillNo = list.Select(m => m.SheetNo).Distinct().ToList();
                // 2.2 查询当前单据列表，所有已进入报销流程的单据；
                var billNos1 = h3db2.BD_ReceiveOrReturnOrder.Where(m => listBillNo.Contains(m.OrderNo)).Select(m => new MaterialIsSettlement { BillNo = m.OrderNo, InstanceId = m.INSTANCEID }).ToList();
                var billNos2 = h3Db.报销材料报销入库单.Where(m => listBillNo.Contains(m.单据编号)).Select(m => new MaterialIsSettlement { BillNo = m.单据编号, InstanceId = m.INSTANCEID }).ToList();

                // 2.3 用sql语句查询
                string strBillNos = "";
                for (int i = 0; i < listBillNo.Count(); i++)
                {
                    strBillNos += "'" + listBillNo[i]+"'";
                    if (i < listBillNo.Count - 1) strBillNos += ",";
                }
                string strWhere = setConditionByOr(strBillNos, 50, "单据编号");
                string sql = "SELECT * FROM h3.报销材料报销入库单 WHERE "+strWhere+"";
                var  billNos3Model = h3Db.Database.SqlQuery<MaterialSheetInSettlement>(sql).ToList();
                var billNos3 = billNos3Model.Select(m => new MaterialIsSettlement { BillNo = m.单据编号, InstanceId = m.INSTANCEID }).ToList();

                string strWhere4 = setConditionByOr(strBillNos, 50, "入库单编号");
                string sql4 = "SELECT * FROM h3.材料零星报销入库单 WHERE " + strWhere4 + "";
                var billNos4Model = h3Db.Database.SqlQuery<MaterialSheetLinXinSettlement>(sql4);
                var billNos4 = billNos4Model.Select(m => new MaterialIsSettlement { BillNo = m.入库单编号, InstanceId = m.INSTANCEID }).ToList();

                listSettlement.AddRange(billNos1);
                listSettlement.AddRange(billNos2);
                listSettlement.AddRange(billNos3);
                listSettlement.AddRange(billNos4);

                listSettlement = listSettlement.Distinct().ToList();
            }
            // 3. 从支付中心判定，哪些数据已经支付、待支付等状态；
            List<string> listWorkflowId = new List<string>();
            listWorkflowId = listSettlement.Select(m => m.InstanceId).ToList();

            using (var erp = Chaolj.DAL.ERP.ERPContext2.Create())
            {
                // 已支付金额:FromTypeName in (材料_材料报销，材料_临星材料报销)
                var qryPay = erp.vFI_Order_Payment.Where(p => listWorkflowId.Contains(p.InstanceId) && (p.Status == 2 || p.Status == 4)).Select(m => new { InstanceId = m.InstanceId }).ToList(); // 状态为：已结算、已完成；
                listSettlement = (from a in listSettlement
                                  join p in qryPay on a.InstanceId equals p.InstanceId into ap
                                  from api in ap.DefaultIfEmpty()
                                  select new MaterialIsSettlement
                                  {
                                      BillNo = a.BillNo,
                                      InstanceId = a.InstanceId,
                                      PayStatus = (api == null || api.InstanceId == null) ? "未支付" : "已支付"
                                  }).ToList();
            }

            var result = (from a in list
                          join s in listSettlement on a.SheetNo equals s.BillNo into sa
                          from sai in sa.DefaultIfEmpty()
                          select new
                          {
                              Id = a.Id,
                              项目名称 = a.ProjectName,
                              供应商名称 = a.Vender,
                              单据编码 = a.SheetNo,
                              交易时间 = a.Transdate,
                              送货金额 = a.DeliveryAmount,
                              金额 = a.TotalAmount,
                              描述 = a.Description,
                              单据状态 = a.Stat,
                              业务类型 = a.SheetTypeNo == "PR" ? "入库" : "退货",
                              报销状态 = (sai == null || sai.InstanceId == null) ? "未报销" : "已报销",
                              支付状态 = sai == null ? "" : sai.PayStatus
                          }).ToList();

            #endregion

            #region 2. 生成xls
            #region 1.列头定义
            List<string> lstTitle = new List<string>
                    {
                        "项目名称", // 0
                        "供应商名称", // 1
                        "单据编码",// 2
                        "交易时间",// 3
                        "送货金额",// 4
                        "金额",// 5
                        "描述",// 6
                        "单据状态", // 7
                        "业务类型", // 8
                        "报销状态", // 9
                        "支付状态",// 10                        
                    };
            #endregion
            IWorkbook book = new HSSFWorkbook();
            ISheet sheet = book.CreateSheet("Sheet1");
            IRow rowTitle = sheet.CreateRow(0);
            ICellStyle style = book.CreateCellStyle();
            style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中  
            for (int i = 0; i < lstTitle.Count; i++)
            {
                rowTitle.CreateCell(i).SetCellValue(lstTitle[i]);
            }
            if (list != null)
            {
                result.OrderBy(p => p.单据编码);
                int start = 0;//记录同组开始行号
                int end = 0;//记录同组结束行号
                string temp = "";//记录上一行的值
                for (int i = 0; i < result.Count(); i++)
                {

                    IRow row = sheet.CreateRow(i + 1);
                    #region 单元格赋值
                    row.CreateCell(0).SetCellValue(result[i].项目名称);
                    row.CreateCell(1).SetCellValue(result[i].供应商名称);
                    row.CreateCell(2).SetCellValue(result[i].单据编码);
                    row.CreateCell(3).SetCellValue(result[i].交易时间.ToString());
                    row.CreateCell(4).SetCellValue(result[i].送货金额.Value.ToString("f2"));
                    row.CreateCell(5).SetCellValue(result[i].金额.ToString("f4"));
                    row.CreateCell(6).SetCellValue(result[i].描述);
                    row.CreateCell(7).SetCellValue(result[i].单据状态);
                    row.CreateCell(8).SetCellValue(result[i].业务类型);
                    row.CreateCell(9).SetCellValue(result[i].报销状态);
                    row.CreateCell(10).SetCellValue(result[i].支付状态);
                    #endregion

                    row.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).SetCellType(CellType.String);
                    var cellText = row.Cells[0].StringCellValue;//获取当前行 第1列的单元格的值

                    if (cellText == temp)//上下行相等，记录要合并的最后一行
                    {
                        end = i;
                    }
                    else//上下行不等，记录
                    {
                        if (start != end)
                        {
                            //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
                            //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                            CellRangeAddress region = new CellRangeAddress(start + 1, end + 1, 0, 0);
                            sheet.AddMergedRegion(region);
                        }
                        start = i;
                        end = i;
                        temp = cellText;
                    }
                }
            }
            #endregion

            #region 3. 格式化并保存
            for (int i = 0; i < 7; i++)
            {
                sheet.AutoSizeColumn(i);//i：根据标题的个数设置自动列宽
            }

            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            #endregion

            return File(ms, "application/vnd.ms-excel", "出入库单据报销明细.xls");
        }

        public string GetSqlIn(string sqlParam, string columnName)
        {
            int width = sqlParam.IndexOf("'", 1) - 1;
            string temp = string.Empty;
            for (int i = 0; i < sqlParam.Length; i += 1000 * (width + 3))
            {
                if (i + 1000 * (width + 3) - 1 < sqlParam.Length)
                {
                    temp = temp + sqlParam.Substring(i, 1000 * (width + 3) - 1)
                    + ") OR " + columnName + " IN (";
                }
                else
                {
                    temp = temp + sqlParam.Substring(i, sqlParam.Length - i);
                }
            }
            return temp;
        }

        /**
         * 解决Oracle where条件中in条件超过1000大小  列表中的最大表达式数为 1000
         * 把以逗号（','）分割的字符串分割成固定大小的多个组 
         * 把分割开的字符串用 or关键字拼接 然后查询
         * param：以逗号分隔的字符串
         * eachGroupNum：每组多少个数量
         * field：SQL语句中哪个字段查询
         */
        public String setConditionByOr(String param, int eachGroupNum, String field)
        {
            String[] paramArray = param.Split(',');

            int inArrayNum = (paramArray.Length % eachGroupNum == 0) ? paramArray.Length / eachGroupNum : paramArray.Length / eachGroupNum + 1;

            int m = 0;
            int b = 0;
            int n = eachGroupNum;
            String[] p = new String[inArrayNum];
            String[] sql = new String[inArrayNum];


            for (int k = 0; k < paramArray.Length; ++k) {
              if (b < inArrayNum) {
                p[b] = "";
                for (; m < n; ++m) {
                    if (m >= paramArray.Length) { break; }           
                    int temp = b;
                    String[] tmp = p; 
                    tmp[temp] = tmp[temp] + paramArray[m] + ",";
                }
                p[b] = p[b].Substring(0, p[b].LastIndexOf(","));


                sql[b] = field + " in (" + p[b] + ")";
                ++b;
                n += eachGroupNum;
              }
            }
            String condition = "";
            for (int j = 0; j < sql.Length; ++j) {
              condition = condition + sql[j] + " or ";
            }
            condition = condition.Substring(0, condition.LastIndexOf(" or "));
            return condition;
        }
        #endregion