c#中的分组求和方法
需求描述:
excel导入功能,对数据集根据银行流水号就行区分,相同银行流水号对应的交款金额进行累加求和,从而进行后续操作.
方法:
1.将目标结果集插入表中,利用sql进行后续操作
2.利用字典处理
3.利用循环处理
4.利用c#提供的分组求和方法进行处理(https://blog.csdn.net/hellosmu/article/details/84337101)
代码如下:
var groupList = pLIst.GroupBy(m => new { m.YHLSH, m.JKDH }).Select(a => new
{
YHLSH = a.Key.YHLSH,
JKDH = a.Key.JKDH,
JKJE = a.Sum(c => c.JKJE)
}).ToList();
foreach (var item in groupList)
{
string checkName = string.Empty;
string checkCode = ResourcesCode.C_7007;
var lsInfo = new ZJLSService().FindlsInfo(item.YHLSH);
var jkdInfo = mJKDService.FindJKDInfos(item.JKDH, base.CurrentRequestBank_Branch_Code);
if ((item.JKJE ?? 0) > (lsInfo.LSWQFJE ?? 0))
{
checkName = "银行流水号为" + "【" + item.YHLSH + "】的" + "本次交存金额大于流水未清分金额";
BusCheckBase.NewBusinessException(checkCode, checkName);
}
if (jkdInfo.RZWJJE < item.JKJE)
{
checkName = "银行流水号为" + "【" + item.YHLSH + "】的" + "本次交存金额大于交款单未交金额";
BusCheckBase.NewBusinessException(checkCode, checkName);
}
}
excel导入功能:利用npoi处理
代码:
controller文件
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web;
using System.Web.Http;
using Api.Models;
using Domain.ZJJG.YSSPF.BusinessModel;
using Domain.ZJJG.YSSPF.RequestDataModel;
using Service.Common;
using Service.ZJJG.YSSPF.ExcelInput;
using SOEI.Infrastructure;
using SOEI.Infrastructure.Helpers;
namespace Api.Controllers.ZJJG.ExcelInput
{
public class ExcelController : ApiController
{
ExcelInputService mGoodsService = new ExcelInputService();
/// <summary>
/// excel文件导入
/// </summary>
[HttpPost]
[Route("api/WJ/DR")]
public IServicesResult WJDR(VMZJJGServiceBase<Attachment_RDModel> p)
{
var info = new Attachment_BModel();
info.FJMC = p.RequestBody.FJMC;
info.FJNR = p.RequestBody.FJNR;
info.LRRBH = p.RequestBody.LRRBH;
info.LRRMC = p.RequestBody.LRRMC;
return JsonServicesResult.OK(mGoodsService.WJDR(info));
}
}
}
service文件:
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Dao.ZJJG.YSSPF.ZJLS;
using Domain.ZJJG.YSSPF;
using Domain.ZJJG.YSSPF.BusinessModel;
using Domain.ZJJG.YSSPF.BusinessModel.JK;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Persistance;
using Service.Common.RequestDataCheck;
using Service.Properties;
using Service.ZJJG.YSSPF.JK;
using SOEI.Infrastructure;
using SOEI.Infrastructure.Ioc;
namespace Service.ZJJG.YSSPF.ExcelInput
{
public class ExcelInputService
{
/// <summary>
/// excel文件导入
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public List<ZJRZ_BModel> WJDR(Attachment_BModel info)
{
List<ZJRZ_BModel> list = new List<ZJRZ_BModel>();
#region 处理excel 数据
var vBytes = info.FJNR;
var LRRBH = info.LRRBH;
var LRRMC = info.LRRMC;
using (var vMemoryStream = new MemoryStream(vBytes))
{
IWorkbook vWorkbook = NPOI.SS.UserModel.WorkbookFactory.Create(vMemoryStream);
if (!IsVersion(vWorkbook))
{
BusCheckBase.NewBusinessException(ResourcesCode.C_90002, "交款单EXCEL模板不正确");
}
return ImportExcel(vWorkbook, LRRBH, LRRMC);
}
#endregion
}
/// <summary>判断excel导入格式
/// </summary>
/// <param name="pHSSFWorkbook"></param>
/// <returns></returns>
private bool IsVersion(IWorkbook pHSSFWorkbook)
{
var vSheet = pHSSFWorkbook.GetSheetAt(0);
var vRow = vSheet.GetRow(0);
if (!"预 售 资 金 监 管 交 款 信 息".Equals(GetCellStringValue(vRow.GetCell(0))))
{
return false;
}
else
{
return true;
}
}
/// <summary> 获取单元格字符串值
/// </summary>
/// <param name="pCell"></param>
/// <returns></returns>
private string GetCellStringValue(ICell pCell)
{
if (pCell != null)
{
if (pCell.CellType == CellType.String)
return pCell.StringCellValue;
return pCell.ToString();
}
return null;
}
/// <summary> 导入
/// </summary>
/// <param name="pHSSFWorkbook"></param>
/// <returns></returns>
private List<ZJRZ_BModel> ImportExcel(IWorkbook pHSSFWorkbook, string LRRBH, string LRRMC)
{
var vResult = new List<ZJRZ_BModel>();
var vSheet = pHSSFWorkbook.GetSheetAt(0); //读取当前表数据
var vEffectiveRow = vSheet.GetRow(2); //读取当前行数据
ZJLSDao mDao = new ZJLSDao();
if (vEffectiveRow == null)
return vResult;
var vRowNum = vEffectiveRow.RowNum;
for (int i = vRowNum; i < vSheet.LastRowNum; i++)
{
var JkdxxInfo = new ZJRZ_BModel();
var vRow = vSheet.GetRow(i);
if (vRow == null)
{
break;
}
try
{
//序号
var XH = GetCellStringValue(vRow.GetCell(0, MissingCellPolicy.RETURN_BLANK_AS_NULL));
JkdxxInfo.XH = !string.IsNullOrEmpty(XH) ? XH : "";
//银行流水号
var YHLSH = GetCellStringValue(vRow.GetCell(1, MissingCellPolicy.RETURN_BLANK_AS_NULL));
JkdxxInfo.YHLSH = !string.IsNullOrEmpty(YHLSH) ? YHLSH : "";
//交款单号
var JKDH = GetCellStringValue(vRow.GetCell(2, MissingCellPolicy.RETURN_BLANK_AS_NULL));
JkdxxInfo.JKDH = !string.IsNullOrEmpty(JKDH) ? JKDH : "";
//合同登记号
var HTDJH = GetCellStringValue(vRow.GetCell(3, MissingCellPolicy.RETURN_BLANK_AS_NULL));
JkdxxInfo.HTDJH = !string.IsNullOrEmpty(HTDJH) ? HTDJH : "";
//资金来源
var ZJLY = GetCellStringValue(vRow.GetCell(4, MissingCellPolicy.RETURN_BLANK_AS_NULL));
if (ZJLY != "")
{
JkdxxInfo.ZJLY = mDao.FindRZLX(ZJLY);
}
else
{
JkdxxInfo.ZJLY = !string.IsNullOrEmpty(ZJLY) ? ZJLY : "";
}
//支付方式
var ZFFS = GetCellStringValue(vRow.GetCell(5, MissingCellPolicy.RETURN_BLANK_AS_NULL));
if (ZFFS != "")
{
JkdxxInfo.ZFFS = mDao.FindZFFS(ZFFS);
}
else
{
JkdxxInfo.ZFFS = !string.IsNullOrEmpty(ZFFS) ? ZFFS : "";
}
//交款金额(元)
var JKJE = GetCellStringValue(vRow.GetCell(6, MissingCellPolicy.RETURN_BLANK_AS_NULL));
if (!string.IsNullOrEmpty(JKJE))
{
JkdxxInfo.JKJE = decimal.Parse(JKJE);
}
else
{
JkdxxInfo.JKJE = 0;
}
//手续费
var SXF = GetCellStringValue(vRow.GetCell(7, MissingCellPolicy.RETURN_BLANK_AS_NULL));
JkdxxInfo.SXF = !string.IsNullOrEmpty(SXF) ? SXF : "";
//交款人名称
var JKRMC = GetCellStringValue(vRow.GetCell(8, MissingCellPolicy.RETURN_BLANK_AS_NULL));
JkdxxInfo.JKRMC = !string.IsNullOrEmpty(JKRMC) ? JKRMC : "";
//交款人账号
var JKRZH = GetCellStringValue(vRow.GetCell(9, MissingCellPolicy.RETURN_BLANK_AS_NULL));
JkdxxInfo.JKRZH = !string.IsNullOrEmpty(JKRZH) ? JKRZH : "";
//备注
var BZ = GetCellStringValue(vRow.GetCell(10, MissingCellPolicy.RETURN_BLANK_AS_NULL));
JkdxxInfo.BZ = !string.IsNullOrEmpty(BZ) ? BZ : "";
//录入人编号、录入人名称
JkdxxInfo.LRRBH = LRRBH;
JkdxxInfo.LRRMC = LRRMC;
vResult.Add(JkdxxInfo);
}
catch (Exception e)
{
}
}
ZJRZService zjrzbll = new ZJRZService();
zjrzbll.YHLSPLBA(vResult);
return vResult;
}
}
}
调用:
/// <summary>
/// excel调用
/// </summary>
/// <param name="pLIst"></param>
/// <returns></returns>
public string YHLSPLBA(List<ZJRZ_BModel> pLIst)
{
foreach (var info in pLIst)
{
if (YHLSBAJY(info) == false)
{
break;
}
}
var groupList = pLIst.GroupBy(m => new { m.YHLSH, m.JKJE, m.JKDH }).Select(a => new
{
YHLSH = a.Key.YHLSH,
JKDH = a.Key.JKDH,
JKJE = a.Sum(c => c.JKJE)
}).ToList();
foreach (var item in groupList)
{
string checkName = string.Empty;
string checkCode = ResourcesCode.C_7007;
var lsInfo = new ZJLSService().FindlsInfo(item.YHLSH);
var jkdInfo = mJKDService.FindJKDInfos(item.JKDH, base.CurrentRequestBank_Branch_Code);
if ((item.JKJE ?? 0) > (lsInfo.LSWQFJE ?? 0))
{
checkName = "银行流水号为" + "【" + item.YHLSH + "】的" + "本次交存金额大于流水未清分金额";
BusCheckBase.NewBusinessException(checkCode, checkName);
}
if (jkdInfo.RZWJJE < item.JKJE)
{
checkName = "银行流水号为" + "【" + item.YHLSH + "】的" + "本次交存金额大于交款单未交金额";
BusCheckBase.NewBusinessException(checkCode, checkName);
}
}
foreach (var info in pLIst)
{
YHLSBAZX(info);
}
return "";
}
逻辑处理:
/// <summary>
/// excel文件内容校验
/// </summary>
/// <param name="pInfo"></param>
/// <returns></returns>
public bool YHLSBAJY(ZJRZ_BModel pInfo)
{
#region 校验
#region 基本校验
if (pInfo == null)
{
BusCheckBase.NewBusinessException(ResourcesCode.C_4006);
}
string checkName = string.Empty;
string checkCode = ResourcesCode.C_7007;
checkName = "交款单号";
pInfo.JKDH
.BusCheckByteLength(1, 20, checkCode, CheckCommon.Format_IncorrectFormat(checkName));
checkName = "交款单号为" + "【" + pInfo.JKDH + "】的" + "交款金额";
pInfo.JKJE
.BusCheckNotNull(checkCode, CheckCommon.Format_NotNull(checkName))
.BusCheckSize(BusinessChecks.decimalMin_001, BusinessChecks.decimalMax, checkCode, CheckCommon.Format_IncorrectFormat(checkName))
.BusCheckFormat(RegexCommon.Money, checkCode, CheckCommon.Format_IncorrectFormat(checkName));
checkName = "交款单号为" + "【" + pInfo.JKDH + "】的" + "银行流水号";
pInfo.YHLSH
.BusCheckByteLength(1, 100, checkCode, CheckCommon.Format_IncorrectFormat(checkName));
checkName = "交款单号为" + "【" + pInfo.JKDH + "】的" + "合同登记号";
pInfo.FJBH
.BusCheckByteLength(0, 15, checkCode, CheckCommon.Format_IncorrectFormat(checkName));
checkName = "交款单号为" + "【" + pInfo.JKDH + "】的" + "交款人账号";
pInfo.JKRZH
.BusCheckByteLength(1, 100, checkCode, CheckCommon.Format_IncorrectFormat(checkName));
checkName = "交款单号为" + "【" + pInfo.JKDH + "】的" + "交款人名称";
pInfo.JKRMC
.BusCheckByteLength(1, 100, checkCode, CheckCommon.Format_IncorrectFormat(checkName));
#endregion
#region 业务校验
var jkdInfo = mJKDService.FindJKDInfos(pInfo.JKDH, base.CurrentRequestBank_Branch_Code);
if (jkdInfo == null)
{
checkName = "交款单号为" + "【" + pInfo.JKDH + "】的" + "交款单信息错误";
BusCheckBase.NewBusinessException(checkCode, checkName);
}
if (!CS_JK_JKDZT.KJK.DM.Equals(jkdInfo.JKDZTDM))
{
checkName = "交款单号为" + "【" + pInfo.JKDH + "】的" + "交款单非可交款状态";
BusCheckBase.NewBusinessException(checkCode, checkName);
}
交款单对应协议
//if (!jkdInfo.JGXYBABH.Equals(base.CurrentBusinessContext.CurrentJGXYBAInfo.JGXYBABH))
//{
// checkName = "交款单信息错误";
// BusCheckBase.NewBusinessException(checkCode, checkName);
//}
//判断交款单类型 和入账类型
if (jkdInfo.JKDLXDM.Equals("01") && !pInfo.ZJLY.Equals("01"))
{
checkName = "交款单号为" + "【" + pInfo.JKDH + "】的" + "入账类型和交款单类型不匹配";
BusCheckBase.NewBusinessException(checkCode, checkName);
}
//校验银行流水的有效性
string vMessage = "";
CheckEffectYHLSH(pInfo.YHLSH, ref vMessage);
if (!string.IsNullOrEmpty(vMessage))
{
if (vMessage == "银行流水不存在")
{
vMessage = "流水号" + pInfo.YHLSH + "不可清分入账";
BusCheckBase.NewBusinessException(checkCode, vMessage);
}
else
{
BusCheckBase.NewBusinessException(checkCode, "交款单号为" + "【" + pInfo.JKDH + "】的" + vMessage);
}
}
//判断附件是否存在
mAttachmentService.CheckAttachmentExist(pInfo.FJBH, ref vMessage);
if (!string.IsNullOrEmpty(vMessage))
BusCheckBase.NewBusinessException(checkCode, CheckCommon.Format_IncorrectFormat("交款单号为" + "【" + pInfo.JKDH + "】的" + vMessage));
var lsInfo = new ZJLSService().FindPage(1, 1, null,
pInfo.YHLSH,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null
);
if (lsInfo.Items.Count > 0)
{
//判断本次交存金额是否小于等于流水未清分金额
if ((pInfo.JKJE ?? 0) > (lsInfo.Items[0].LSWQFJE ?? 0))
{
checkName = "交款单号为" + "【" + pInfo.JKDH + "】的" + "本次交存金额大于流水未清分金额";
BusCheckBase.NewBusinessException(checkCode, checkName);
}
if (jkdInfo.RZWJJE < pInfo.JKJE)
{
checkName = "交款单号为" + "【" + pInfo.JKDH + "】的" + "本次交存金额大于交款单未交金额";
BusCheckBase.NewBusinessException(checkCode, checkName);
}
if (new JGYHZHRWService().IsBranchIimit("00004"))
{
string checkMsg = "交款单号为" + "【" + pInfo.JKDH + "】的" + "监管银行该业务已被限制,无法继续办理!";
BusCheckBase.NewBusinessException(ResourcesCode.C_90001, checkMsg);
}
}
#endregion
#endregion
return true;
}
/// <summary>
/// excel导入总金额逻辑判断
/// </summary>
/// <param name="JkzjzeInfo"></param>
/// <returns></returns>
//public bool YHJKJEZH(ZJRZ_BModel JkzjzeInfo)
public bool YHJKJEZH(ZJRZ_BModel JkzjzeInfo)
{
string checkName = string.Empty;
string checkCode = ResourcesCode.C_7007;
var lsInfo = new ZJLSService().FindlsInfo(JkzjzeInfo.YHLSH);
var jkdInfo = mJKDService.FindJKDInfos(JkzjzeInfo.JKDH, base.CurrentRequestBank_Branch_Code);
if ((JkzjzeInfo.JKJE ?? 0) > (lsInfo.LSWQFJE ?? 0))
{
checkName = "银行流水号为" + "【" + JkzjzeInfo.YHLSH + "】的" + "本次交存金额大于流水未清分金额";
BusCheckBase.NewBusinessException(checkCode, checkName);
}
if (jkdInfo.RZWJJE < JkzjzeInfo.JKJE)
{
checkName = "银行流水号为" + "【" + JkzjzeInfo.YHLSH + "】的" + "本次交存金额大于交款单未交金额";
BusCheckBase.NewBusinessException(checkCode, checkName);
}
return true;
}
/// <summary>
/// excel文件内容执行
/// </summary>
/// <param name="pInfo"></param>
/// <returns></returns>
public string YHLSBAZX(ZJRZ_BModel pInfo)
{
var lsInfo = new ZJLSService().FindlsInfo(pInfo.YHLSH);
var jkdInfo = mJKDService.FindJKDInfos(pInfo.JKDH, base.CurrentRequestBank_Branch_Code);
#region 业务操作
var dbM = pInfo.FillDataTo(new ZJRZ_DMInfo());
//dbM.ZFFS = lsInfo.Items[0].SZFSDM;
dbM.JKRQ = lsInfo.DZSJ.Value.ToString("yyyy-MM-dd HH:mm:ss");
dbM.DZRQ = lsInfo.DZSJ.Value.ToString("yyyy-MM-dd HH:mm:ss");
dbM.JGYHXLBH = base.CurrentRequestBank_Branch_Code;
dbM.RZZTDM = CS_JK_RZZT.ZC.DM;
dbM.HTBADJH = null == jkdInfo ? "" : jkdInfo.HTBADJH;
dbM.HTDJH = null == jkdInfo ? "" : jkdInfo.HTDJH;
#region Excel导入
dbM.JGXYBABH = null == jkdInfo ? "" : jkdInfo.JGXYBABH;
dbM.ZFFS = pInfo.ZFFS;
dbM.SXF = pInfo.SXF;
dbM.RZLXDM = pInfo.ZJLY;
dbM.JKRZH = pInfo.JKRZH;
dbM.JKRMC = pInfo.JKRMC;
dbM.LRRBH = pInfo.LRRBH;
dbM.LRRMC = pInfo.LRRMC;
dbM.BZ = pInfo.BZ;
#endregion
dbM.BBH = base.GetCurrentVersionNumber();
dbM.SJLY = ZJJGBase.CURRENT_SJLY;
//Bug #64790 孙龙
dbM.QFZT = "01";//未清分
decimal LSWQFJE = new ZJLSDao().Find_LSWQFJE(pInfo.YHLSH);
decimal _qf = LSWQFJE - (decimal)pInfo.JKJE;
if (_qf > 0)
{
dbM.QFZT = "02";//部分清分
}
else
{
dbM.QFZT = "03";//全部清分
}
string result_ba = mDao.Insert(dbM);
if (result_ba != "")
{
QFZDao mQFZTDao = new QFZDao();
mQFZTDao.QFZT_Update(pInfo.YHLSH, "01", dbM.QFZT);
}
return result_ba;
#endregion
}
在此记录,仅以保存。
明日有明日的春光,一朝有一朝的敞亮