public static IPageOfItems <T> Since <T>(this IPageOfItems <T> pageOfItems, Func <T, DateTime> getCompareDateTime, DateTime?sinceDate) { if (sinceDate.HasValue) { return(new PageOfItems <T>(pageOfItems.Where(i => getCompareDateTime(i) > sinceDate.Value), pageOfItems.PageIndex, pageOfItems.PageSize, pageOfItems.TotalItemCount)); } return(pageOfItems); }
public ActionResult Index(QRCode QRCode, EFPagingInfo <QRCode> p, int IsFirst = 0) { if (IsFirst == 0) { PageOfItems <QRCode> QRCodeList1 = new PageOfItems <QRCode>(new List <QRCode>(), 0, 10, 0, new Hashtable()); ViewBag.QRCodeList = QRCodeList1; ViewBag.QRCode = QRCode; ViewBag.UsersList = new List <Users>(); return(View()); } if (!QRCode.UId.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.UId == QRCode.UId); p.PageSize = 99999; } if (!QRCode.Num.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.Num == QRCode.Num); } if (!QRCode.State.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.State == (QRCode.State == 99 ? 0 : QRCode.State)); } p.OrderByList.Add("Id", "DESC"); IPageOfItems <QRCode> QRCodeList = Entity.Selects <QRCode>(p); ViewBag.QRCodeList = QRCodeList; ViewBag.QRCode = QRCode; IList <int> Ids = new List <int>(); foreach (var P in QRCodeList.Where(n => n.UId > 0)) { Ids.Add(P.UId); } IList <Users> UsersList = new List <Users>(); if (Ids.Count() > 0 && QRCode.UId.IsNullOrEmpty()) { UsersList = Entity.Users.Where(n => Ids.Contains(n.Id)).ToList(); } ViewBag.UsersList = UsersList; return(View()); }
public void XLSDo(OrderHouse OrderHouse, EFPagingInfo <OrderHouse> p) { if (OrderHouse.AddTime.IsNullOrEmpty()) { OrderHouse.AddTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")); } if (OrderHouse.FTime.IsNullOrEmpty()) { //OrderHouse.FTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")); OrderHouse.FTime = DateTime.Now; } if (!OrderHouse.AddTime.IsNullOrEmpty() && !OrderHouse.FTime.IsNullOrEmpty()) { // OrderHouse.FTime = ((DateTime)OrderHouse.FTime).AddDays(1); p.SqlWhere.Add(f => f.AddTime > OrderHouse.AddTime && f.AddTime < OrderHouse.FTime); } //if (!OrderHouse.UId.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.UId == OrderHouse.UId); } if (!OrderHouse.HouseOwner.IsNullOrEmpty()) { IList <Users> UList = Entity.Users.Where(n => n.TrueName.Contains(OrderHouse.HouseOwner) || n.NeekName.Contains(OrderHouse.HouseOwner) || n.UserName == OrderHouse.HouseOwner).ToList(); List <int> UIds = new List <int>(); foreach (var pp in UList) { UIds.Add(pp.Id); } p.SqlWhere.Add(f => UIds.Contains(f.UId)); } if (!OrderHouse.OId.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.OId == OrderHouse.OId); } if (!OrderHouse.Agent.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.Agent == OrderHouse.Agent); } if (!OrderHouse.TrunType.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.TrunType == (OrderHouse.TrunType == 99 ? 0 : OrderHouse.TrunType)); } if (!OrderHouse.AId.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.AId == OrderHouse.AId); } if (!OrderHouse.FId.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.FId == OrderHouse.FId); } if (!OrderHouse.AgentState.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.AgentState == OrderHouse.AgentState); } p.SqlWhere.Add(f => f.OrderState == 2 && f.PayState == 1 && f.FState == 0); p.PageSize = 99999999; p.OrderByList.Add("Id", "DESC"); IPageOfItems <OrderHouse> OrderHouseList = Entity.Selects <OrderHouse>(p); if (OrderHouseList.Count() > 0) { //当前导出缩合表,民生跨行,工行,民生同行 int BankIdMS = 13; //民生ID int BankIdGS = 1; //工商Id string BankNameMS = Entity.BasicBank.FirstOrNew(n => n.Id == BankIdMS).Name; string BankNameGS = Entity.BasicBank.FirstOrNew(n => n.Id == BankIdGS).Name; //加载对应银行模板 string tempname = "bank.xlsx"; string file = Server.MapPath("/template") + "\\" + tempname; ExcelPackage package = new ExcelPackage(new FileInfo(file), true); //民生 IList <OrderHouse> ListMS = OrderHouseList.Where(n => n.Bank == BankNameMS).ToList(); //工商 IList <OrderHouse> ListGS = OrderHouseList.Where(n => n.Bank == BankNameGS).ToList(); //其它 IList <OrderHouse> ListOT = OrderHouseList.Where(n => n.Bank != BankNameMS && n.Bank != BankNameGS).ToList(); //设置数据开始行 int Befor = 0; int maxCol = 0;//最大列数 if (ListOT.Count > 0) { //加载模板第一张表 var sheet = package.Workbook.Worksheets[1]; var cells = sheet.Cells; decimal TotalMoney = 0; //计算总金额 int Rows = ListOT.Count(); Befor = 5; //民生他行从5开始 sheet.InsertRow(Befor + 1, Rows - 1, Befor); int i = Befor; foreach (var item in ListOT) { string zdlx = "2"; decimal Money = item.PayMoney; TotalMoney += Money; //制单类型 cells["A" + i].Value = zdlx.ToString(); //企业自制凭证号 cells["B" + i].Value = item.Id.ToString().PadLeft(8, '0'); //客户号 cells["C" + i].Value = "2200488356"; //预约标志 cells["D" + i].Value = "0"; //付款账号 cells["E" + i].Value = "613111800"; //交易金额 cells["F" + i].Value = Money; //收款账号 cells["G" + i].Value = item.CardNum; //收款人姓名 cells["H" + i].Value = item.HouseOwner; //收款账户类型 cells["I" + i].Value = "1"; //子客户号 cells["J" + i].Value = ""; //子付款账号 cells["K" + i].Value = ""; //子付款账户名 cells["L" + i].Value = ""; //子付款账户开户行名 cells["M" + i].Value = ""; //用途 cells["N" + i].Value = "房租"; //汇路 cells["O" + i].Value = Money > 50000 ? "7" : "6"; //是否通知收款人 cells["P" + i].Value = "0"; //手机 cells["Q" + i].Value = item.Mobile; //邮箱 cells["R" + i].Value = ""; //支付行号&支付行名称 cells["S" + i].Value = item.Bin + "&" + item.Deposit; maxCol = 19; i++; } i--; //审核方式(文件类型) cells["B1"].Value = "1"; //总金额 cells["B2"].Value = TotalMoney.ToString("F2"); //总交易数 cells["B3"].Value = Rows.ToString(); sheet.Cells[Befor, 6, i, 6].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; } if (ListMS.Count > 0) { //加载模板第二张表 var sheet = package.Workbook.Worksheets[2]; var cells = sheet.Cells; decimal TotalMoney = 0; //计算总金额 int Rows = ListMS.Count(); Befor = 9; //民生本行从9开始 sheet.InsertRow(Befor + 1, Rows - 1, Befor); int i = Befor; foreach (var item in ListMS) { decimal Money = item.PayMoney; TotalMoney += Money; //收款账号 cells["A" + i].Value = item.CardNum; //交易金额 cells["B" + i].Value = Money.ToString("F2"); //收款人姓名 cells["C" + i].Value = item.HouseOwner; //用途 cells["D" + i].Value = "349"; maxCol = 4; i++; } i--; //总金额 cells["B6"].Value = TotalMoney.ToString(); //总交易数 cells["B7"].Value = Rows.ToString(); } if (ListGS.Count > 0) { //加载模板第三张表 var sheet = package.Workbook.Worksheets[3]; var cells = sheet.Cells; decimal TotalMoney = 0; //计算总金额 int Rows = ListGS.Count(); Befor = 2; //工行本行从2开始 sheet.InsertRow(Befor + 1, Rows - 1, Befor); int i = Befor; IList <BasicProvince> PList = Entity.BasicProvince.Where(n => n.State == 1).ToList(); IList <BasicCity> CList = Entity.BasicCity.Where(n => n.State == 1).ToList(); foreach (var item in ListGS) { decimal Money = item.PayMoney; TotalMoney += Money; //币种 cells["A" + i].Value = "RMB"; //日期 cells["B" + i].Value = DateTime.Now.ToString("yyyyMMdd"); //明细标志 cells["C" + i].Value = ""; //顺序号 cells["D" + i].Value = i - Befor + 1; //付款账号开户行 cells["E" + i].Value = "工商银行"; //付款账号 cells["F" + i].Value = "4000051709100125887"; //付款账号名称 cells["G" + i].Value = "好付支付(深圳)有限公司"; //收款账号开户行 cells["H" + i].Value = "工商银行"; //收款账号省份 cells["I" + i].Value = PList.FirstOrNew(n => n.Id == item.Province).Name; //收款账号地市 cells["J" + i].Value = CList.FirstOrNew(n => n.Id == item.City).Name; //收款账号地区码 cells["K" + i].Value = ""; //收款账号 cells["L" + i].Value = item.CardNum; //收款账号名称 cells["M" + i].Value = item.HouseOwner; //金额 cells["N" + i].Value = Money.ToString("F2"); //汇款用途 cells["O" + i].Value = "房租"; //备注信息 cells["P" + i].Value = ""; //汇款方式 cells["Q" + i].Value = 1; //收款账户短信通知手机号码 cells["R" + i].Value = ""; //自定义序号 cells["S" + i].Value = item.Id; maxCol = 19; i++; } i--; } //无数据表删除 if (ListGS.Count == 0) { package.Workbook.Worksheets.Delete(package.Workbook.Worksheets[3]); } if (ListMS.Count == 0) { package.Workbook.Worksheets.Delete(package.Workbook.Worksheets[2]); } if (ListOT.Count == 0) { package.Workbook.Worksheets.Delete(package.Workbook.Worksheets[1]); } Response.BinaryWrite(package.GetAsByteArray());//输出 Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(10, 99) + ".xlsx"); } else { Response.Write("暂无符合条件数据"); } }
public void XLSDo(Orders Orders, EFPagingInfo <Orders> p) { if (Orders.STime.IsNullOrEmpty()) { Orders.STime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")); } if (Orders.ETime.IsNullOrEmpty()) { Orders.ETime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")); } p.SqlWhere.Add(f => f.PayState == 1); p.SqlWhere.Add(f => f.TState == 2); if (!Orders.TNum.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.TNum == Orders.TNum); } //if (!Orders.UId.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.UId == Orders.UId); } if (!Orders.TName.IsNullOrEmpty()) { IList <Users> UList = Entity.Users.Where(n => n.TrueName.Contains(Orders.TName) || n.NeekName.Contains(Orders.TName) || n.UserName == Orders.TName).ToList(); List <int> UIds = new List <int>(); foreach (var pp in UList) { UIds.Add(pp.Id); } p.SqlWhere.Add(f => UIds.Contains(f.UId)); } if (!Orders.TType.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.TType == Orders.TType); } if (!Orders.Agent.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.Agent == Orders.Agent); } if (!Orders.AgentState.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.AgentState == (Orders.AgentState == 99 ? 0 : Orders.AgentState)); } if (!Orders.STime.IsNullOrEmpty() && !Orders.ETime.IsNullOrEmpty()) { DateTime ETime = Orders.ETime; p.SqlWhere.Add(f => f.PayTime > Orders.STime && f.PayTime < ETime); } p.PageSize = 99999999; p.OrderByList.Add("Id", "DESC"); IPageOfItems <Orders> OrdersList = Entity.Selects <Orders>(p); IList <Orders> List = OrdersList.GroupBy(n => n.UId).Select(n => n.First()).ToList(); List <int> UId = new List <int>(); foreach (var pp in List) { UId.Add(pp.UId); } IList <Users> UsersList = Entity.Users.Where(n => n.State == 1 && UId.Contains(n.Id)).ToList(); IList <Orders> List1 = OrdersList.GroupBy(n => n.Agent).Select(n => n.First()).ToList(); List <int> AgentId = new List <int>(); foreach (var pp in List1) { AgentId.Add(pp.Agent); } if (this.TempData["SysAgentList"] == null) { Response.Write("当前页面数据已过期"); } this.TempData.Keep("SysAgentList"); IList <SysAgent> SysAgentList = this.TempData["SysAgentList"] as IList <SysAgent>;//Entity.SysAgent.Where(n => n.State == 1 && AgentId.Contains(n.Id)).ToList(); if (SysAgentList != null && SysAgentList.Count > 0) { //加载对应模板 string tempname = "order.xlsx"; string file = Server.MapPath("/template") + "\\" + tempname; ExcelPackage package = new ExcelPackage(new FileInfo(file), true); //设置数据开始行 int Befor = 2; int maxCol = 8;//最大列数 //加载模板第一张表 var sheet = package.Workbook.Worksheets[1]; var cells = sheet.Cells; decimal TotalMoney = 0; //计算总金额 double TotalPrice = 0; //计算总金额 int Rows = OrdersList.Count() + SysAgentList.Count(); sheet.InsertRow(Befor + 1, Rows - 1, Befor); int i = Befor; foreach (var item in SysAgentList) { IList <Orders> ListSub = OrdersList.Where(n => n.Agent == item.Id).ToList(); decimal AgentMoney = 0; //计算供应商金额 double AgentPrice = 0; //计算供应商金额 foreach (var s in ListSub) { sheet.Row(i).Height = 20;//设置行高 Users U = UsersList.FirstOrNew(n => n.Id == s.UId); //分支机构 cells["A" + i].Value = item.Name; //交易号 cells["B" + i].Value = s.TNum; //交易商户 cells["C" + i].Value = U.TrueName; //交易类型 cells["D" + i].Value = s.GetTTName(); //交易内容 cells["E" + i].Value = s.GetPayNameWithTName(); //交易金额 cells["F" + i].Value = s.Amoney; //交易时间 cells["G" + i].Value = s.PayTime; //结算金额 double agentmoney = s.GetAgentMoney(Entity); cells["H" + i].Value = agentmoney; AgentMoney += s.Amoney; AgentPrice += agentmoney; i++; } sheet.Row(i).Height = 28;//设置行高 //交易金额汇总 cells["F" + i].Value = AgentMoney; //结算金额汇总 cells["H" + i].Value = AgentPrice; Color bgColor = ColorTranslator.FromHtml("#DDDDDD"); Color fColor = ColorTranslator.FromHtml("#FF0000"); sheet.Cells[i, 1, i, maxCol].Style.Fill.PatternType = ExcelFillStyle.Solid; sheet.Cells[i, 1, i, maxCol].Style.Fill.BackgroundColor.SetColor(bgColor); sheet.Cells[i, 1, i, maxCol].Style.Font.Size = 16; sheet.Cells[i, 1, i, maxCol].Style.Font.Bold = true; sheet.Cells[i, 1, i, maxCol].Style.Font.Color.SetColor(fColor); TotalMoney += AgentMoney; TotalPrice += AgentPrice; i++; } Color bgColor_ = ColorTranslator.FromHtml("#7030A0"); Color fColor_ = ColorTranslator.FromHtml("#FFFFFF"); sheet.Cells[i, 1, i, maxCol].Style.Fill.PatternType = ExcelFillStyle.Solid; sheet.Cells[i, 1, i, maxCol].Style.Fill.BackgroundColor.SetColor(bgColor_); sheet.Cells[i, 1, i, maxCol].Style.Font.Size = 20; sheet.Cells[i, 1, i, maxCol].Style.Font.Bold = true; sheet.Cells[i, 1, i, maxCol].Style.Font.Color.SetColor(fColor_); sheet.Row(i).Height = 40;//设置行高 //交易金额汇总 cells["F" + i].Value = TotalMoney; //结算金额汇总 cells["H" + i].Value = TotalPrice; sheet.Cells[Befor, 1, i, maxCol].Style.Border.Left.Style = ExcelBorderStyle.Thin; sheet.Cells[Befor, 1, i, maxCol].Style.Border.Right.Style = ExcelBorderStyle.Thin; sheet.Cells[Befor, 1, i, maxCol].Style.Border.Top.Style = ExcelBorderStyle.Thin; sheet.Cells[Befor, 1, i, maxCol].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; sheet.Cells[Befor, 6, i, 6].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; sheet.Cells[Befor, 7, i, 7].Style.Numberformat.Format = "yyyy-mm-dd hh:mm"; sheet.Cells[Befor, 8, i, 8].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; Response.BinaryWrite(package.GetAsByteArray());//输出 Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(10, 99) + ".xlsx"); } else { Response.Write("暂无符合条件数据"); } }
public void XLSDo(ApplyLoan ApplyLoan, EFPagingInfo <ApplyLoan> p) { if (ApplyLoan.STime.IsNullOrEmpty()) { ApplyLoan.STime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")); } if (ApplyLoan.ETime.IsNullOrEmpty()) { //ApplyLoan.ETime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")); ApplyLoan.ETime = DateTime.Now; } if (!ApplyLoan.TrueName.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.TrueName == ApplyLoan.TrueName); } if (!ApplyLoan.Education.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.Education.Contains(ApplyLoan.Education)); } if (!ApplyLoan.HasSheBao.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.HasSheBao == (ApplyLoan.HasSheBao == 99 ? 0 : ApplyLoan.HasSheBao)); } if (!ApplyLoan.Marry.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.Marry == ApplyLoan.Marry); } if (!ApplyLoan.HasCar.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.HasCar == (ApplyLoan.HasCar == 99 ? 0 : ApplyLoan.HasCar)); } if (!ApplyLoan.House.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.House == ApplyLoan.House); } if (!ApplyLoan.HasCredit.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.HasCredit == ApplyLoan.HasCredit); } if (!ApplyLoan.State.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.State == ApplyLoan.State); } if (!ApplyLoan.AgentPay.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.AgentPay == (ApplyLoan.AgentPay == 99 ? 0 : ApplyLoan.AgentPay)); } if (!ApplyLoan.AgentAId.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.AgentAId == ApplyLoan.AgentAId); } if (!ApplyLoan.STime.IsNullOrEmpty() && !ApplyLoan.ETime.IsNullOrEmpty()) { DateTime ETime = ApplyLoan.ETime; p.SqlWhere.Add(f => f.PayTime > ApplyLoan.STime && f.PayTime < ETime); } p.SqlWhere.Add(f => f.PayState == 1); p.PageSize = 99999999; p.OrderByList.Add("Id", "DESC"); IPageOfItems <ApplyLoan> ApplyLoanList = Entity.Selects <ApplyLoan>(p); IList <ApplyLoan> List = ApplyLoanList.GroupBy(n => n.AgentId).Select(n => n.First()).ToList(); List <int> AgentId = new List <int>(); foreach (var pp in List) { AgentId.Add(pp.AgentId); } IList <SysAgent> SysAgentList = Entity.SysAgent.Where(n => n.State == 1 && AgentId.Contains(n.Id)).ToList(); if (SysAgentList.Count > 0) { //加载对应银行模板 string tempname = "AgentLoan.xlsx"; string file = Server.MapPath("/template") + "\\" + tempname; ExcelPackage package = new ExcelPackage(new FileInfo(file), true); //设置数据开始行 int Befor = 2; int maxCol = 6;//最大列数 //加载模板第一张表 var sheet = package.Workbook.Worksheets[1]; var cells = sheet.Cells; decimal TotalMoney = 0; //计算总金额 decimal TotalPrice = 0; //计算总金额 int Rows = ApplyLoanList.Count() + SysAgentList.Count(); sheet.InsertRow(Befor + 1, Rows - 1, Befor); int i = Befor; foreach (var item in SysAgentList) { IList <ApplyLoan> ListSub = ApplyLoanList.Where(n => n.AgentId == item.Id).ToList(); decimal AgentMoney = 0; //计算供应商金额 decimal AgentPrice = 0; //计算供应商金额 foreach (var s in ListSub) { sheet.Row(i).Height = 20;//设置行高 //分支机构 cells["A" + i].Value = item.Name; //编号 cells["B" + i].Value = s.Id; //姓名 cells["C" + i].Value = s.TrueName; //售出金额 cells["D" + i].Value = s.Amoney; //售出时间 cells["E" + i].Value = s.PayTime; //结算金额 cells["F" + i].Value = s.AgentMoney; AgentMoney += s.Amoney; AgentPrice += s.AgentMoney; i++; } sheet.Row(i).Height = 28;//设置行高 //交易金额汇总 cells["D" + i].Value = AgentMoney; //结算金额汇总 cells["F" + i].Value = AgentPrice; Color bgColor = ColorTranslator.FromHtml("#DDDDDD"); Color fColor = ColorTranslator.FromHtml("#FF0000"); sheet.Cells[i, 1, i, maxCol].Style.Fill.PatternType = ExcelFillStyle.Solid; sheet.Cells[i, 1, i, maxCol].Style.Fill.BackgroundColor.SetColor(bgColor); sheet.Cells[i, 1, i, maxCol].Style.Font.Size = 16; sheet.Cells[i, 1, i, maxCol].Style.Font.Bold = true; sheet.Cells[i, 1, i, maxCol].Style.Font.Color.SetColor(fColor); TotalMoney += AgentMoney; TotalPrice += AgentPrice; i++; } Color bgColor_ = ColorTranslator.FromHtml("#7030A0"); Color fColor_ = ColorTranslator.FromHtml("#FFFFFF"); sheet.Cells[i, 1, i, maxCol].Style.Fill.PatternType = ExcelFillStyle.Solid; sheet.Cells[i, 1, i, maxCol].Style.Fill.BackgroundColor.SetColor(bgColor_); sheet.Cells[i, 1, i, maxCol].Style.Font.Size = 20; sheet.Cells[i, 1, i, maxCol].Style.Font.Bold = true; sheet.Cells[i, 1, i, maxCol].Style.Font.Color.SetColor(fColor_); sheet.Row(i).Height = 40;//设置行高 //交易金额汇总 cells["D" + i].Value = TotalMoney; //结算金额汇总 cells["F" + i].Value = TotalPrice; sheet.Cells[Befor, 1, i, maxCol].Style.Border.Left.Style = ExcelBorderStyle.Thin; sheet.Cells[Befor, 1, i, maxCol].Style.Border.Right.Style = ExcelBorderStyle.Thin; sheet.Cells[Befor, 1, i, maxCol].Style.Border.Top.Style = ExcelBorderStyle.Thin; sheet.Cells[Befor, 1, i, maxCol].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; sheet.Cells[Befor, 4, i, 4].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; sheet.Cells[Befor, 5, i, 5].Style.Numberformat.Format = "yyyy-mm-dd hh:mm"; sheet.Cells[Befor, 6, i, 6].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; Response.BinaryWrite(package.GetAsByteArray());//输出 Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(10, 99) + ".xlsx"); } else { Response.Write("暂无符合条件数据"); } }
public void XLSDo(OrderCash OrderCash, EFPagingInfo <OrderCash> p, bool?IsShowSupAgent) { if (IsShowSupAgent == null) { IsShowSupAgent = false; } p = this.Condition(OrderCash, p, IsShowSupAgent); p.PageSize = 99999999; p.OrderByList.Add("Id", "ASC"); IPageOfItems <OrderCash> OrderCashList = Entity.Selects <OrderCash>(p); if (OrderCashList.Count() > 0) { //当前银行名称 //string thisBankName = Entity.BasicBank.FirstOrNew(n => n.Id == OrderCash.Id).Name; //当前导出缩合表,民生跨行,工行,民生同行 int BankIdMS = 13; //民生ID int BankIdGS = 1; //工商Id string BankNameMS = Entity.BasicBank.FirstOrNew(n => n.Id == BankIdMS).Name; string BankNameGS = Entity.BasicBank.FirstOrNew(n => n.Id == BankIdGS).Name; //加载对应银行模板 string tempname = "bank.xlsx"; string file = Server.MapPath("/template") + "\\" + tempname; ExcelPackage package = new ExcelPackage(new FileInfo(file), true); //民生 IList <OrderCash> ListMS = OrderCashList.Where(n => n.Bank == BankNameMS).ToList(); //工商 IList <OrderCash> ListGS = OrderCashList.Where(n => n.Bank == BankNameGS).ToList(); //其它 IList <OrderCash> ListOT = OrderCashList.Where(n => n.Bank != BankNameMS && n.Bank != BankNameGS).ToList(); //设置数据开始行 int Befor = 0; int maxCol = 0;//最大列数 if (ListOT.Count > 0) { //加载模板第一张表 var sheet = package.Workbook.Worksheets[1]; var cells = sheet.Cells; decimal TotalMoney = 0; //计算总金额 int Rows = ListOT.Count(); Befor = 5; //民生他行从5开始 sheet.InsertRow(Befor + 1, Rows - 1, Befor); int i = Befor; //sheet.Row(i - 1).Height = 18;//设置行高 foreach (var item in ListOT) { string zdlx = "2"; //sheet.Row(i).Height = 18;//设置行高 decimal Money = item.Amoney - (decimal)item.UserRate; TotalMoney += Money; //制单类型 cells["A" + i].Value = zdlx.ToString(); //企业自制凭证号 cells["B" + i].Value = item.Id.ToString().PadLeft(8, '0'); //客户号 cells["C" + i].Value = "2200488356"; //预约标志 cells["D" + i].Value = "0"; //付款账号 cells["E" + i].Value = "613111800"; //交易金额 cells["F" + i].Value = Money; //收款账号 cells["G" + i].Value = item.CardNum; //收款人姓名 cells["H" + i].Value = item.Owner; //收款账户类型 cells["I" + i].Value = "1"; //子客户号 cells["J" + i].Value = ""; //子付款账号 cells["K" + i].Value = ""; //子付款账户名 cells["L" + i].Value = ""; //子付款账户开户行名 cells["M" + i].Value = ""; //用途 cells["N" + i].Value = "提现"; //汇路 cells["O" + i].Value = Money > 50000 ? "7" : "6"; //是否通知收款人 cells["P" + i].Value = "0"; //手机 cells["Q" + i].Value = item.Mobile; //邮箱 cells["R" + i].Value = ""; //支付行号&支付行名称 cells["S" + i].Value = item.Bin + "&" + item.Deposit; maxCol = 19; i++; } i--; //审核方式(文件类型) cells["B1"].Value = "1"; //总金额 cells["B2"].Value = TotalMoney.ToString("F2"); //总交易数 cells["B3"].Value = Rows.ToString(); sheet.Cells[Befor, 6, i, 6].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; //cells["B" + (i + 2)].Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); //sheet.Cells[Befor, 1, i, maxCol].Style.Border.Left.Style = ExcelBorderStyle.Thin; //sheet.Cells[Befor, 1, i, maxCol].Style.Border.Right.Style = ExcelBorderStyle.Thin; //sheet.Cells[Befor, 1, i, maxCol].Style.Border.Top.Style = ExcelBorderStyle.Thin; //sheet.Cells[Befor, 1, i, maxCol].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; //sheet.Cells[Befor, 1, i, maxCol].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//居中 //sheet.Cells[Befor, 5, i, 5].Style.Numberformat.Format = "yyyy-mm-dd HH:mm:ss"; //for (int j = i + 1; j <= i + 2; j++) //{ // sheet.Row(j).Height = 20;//设置行高 //} } if (ListMS.Count > 0) { //加载模板第二张表 var sheet = package.Workbook.Worksheets[2]; var cells = sheet.Cells; decimal TotalMoney = 0; //计算总金额 int Rows = ListMS.Count(); Befor = 9; //民生本行从9开始 sheet.InsertRow(Befor + 1, Rows - 1, Befor); int i = Befor; foreach (var item in ListMS) { decimal Money = item.Amoney - (decimal)item.UserRate; TotalMoney += Money; //收款账号 cells["A" + i].Value = item.CardNum; //交易金额 cells["B" + i].Value = Money.ToString("F2"); //收款人姓名 cells["C" + i].Value = item.Owner; //用途 cells["D" + i].Value = "349"; maxCol = 4; i++; } i--; //总金额 cells["B6"].Value = TotalMoney.ToString(); //总交易数 cells["B7"].Value = Rows.ToString(); } if (ListGS.Count > 0) { //加载模板第三张表 var sheet = package.Workbook.Worksheets[3]; var cells = sheet.Cells; decimal TotalMoney = 0; //计算总金额 int Rows = ListGS.Count(); Befor = 2; //工行本行从2开始 sheet.InsertRow(Befor + 1, Rows - 1, Befor); int i = Befor; IList <BasicProvince> PList = Entity.BasicProvince.Where(n => n.State == 1).ToList(); IList <BasicCity> CList = Entity.BasicCity.Where(n => n.State == 1).ToList(); foreach (var item in ListGS) { decimal Money = item.Amoney - (decimal)item.UserRate; TotalMoney += Money; //币种 cells["A" + i].Value = "RMB"; //日期 cells["B" + i].Value = DateTime.Now.ToString("yyyyMMdd"); //明细标志 cells["C" + i].Value = ""; //顺序号 cells["D" + i].Value = i - Befor + 1; //付款账号开户行 cells["E" + i].Value = "工商银行"; //付款账号 cells["F" + i].Value = "4000051709100125887"; //付款账号名称 cells["G" + i].Value = "好付支付(深圳)有限公司"; //收款账号开户行 cells["H" + i].Value = "工商银行"; //收款账号省份 cells["I" + i].Value = PList.FirstOrNew(n => n.Id == item.Province).Name; //收款账号地市 cells["J" + i].Value = CList.FirstOrNew(n => n.Id == item.City).Name; //收款账号地区码 cells["K" + i].Value = ""; //收款账号 cells["L" + i].Value = item.CardNum; //收款账号名称 cells["M" + i].Value = item.Owner; //金额 cells["N" + i].Value = Money.ToString("F2"); //汇款用途 cells["O" + i].Value = "提现"; //备注信息 cells["P" + i].Value = ""; //汇款方式 cells["Q" + i].Value = 1; //收款账户短信通知手机号码 cells["R" + i].Value = ""; //自定义序号 cells["S" + i].Value = item.Id; maxCol = 19; i++; } i--; } //无数据表删除 if (ListGS.Count == 0) { package.Workbook.Worksheets.Delete(package.Workbook.Worksheets[3]); } if (ListMS.Count == 0) { package.Workbook.Worksheets.Delete(package.Workbook.Worksheets[2]); } if (ListOT.Count == 0) { package.Workbook.Worksheets.Delete(package.Workbook.Worksheets[1]); } Response.BinaryWrite(package.GetAsByteArray());//输出 Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(10, 99) + ".xlsx"); } else { Response.Write("暂无符合条件数据"); } }
public void Post() { string Data = DataObj.GetData(); if (Data.IsNullOrEmpty()) { DataObj.OutError("1000"); return; } JObject json = new JObject(); try { json = (JObject)JsonConvert.DeserializeObject(Data); } catch (Exception Ex) { Log.Write("[MsgUser]:", "【Data】" + Data, Ex); } if (json == null) { DataObj.OutError("1000"); return; } MsgUser MsgUser = new MsgUser(); MsgUser = JsonToObject.ConvertJsonToModel(MsgUser, json); Users baseUsers = Entity.Users.FirstOrDefault(n => n.Token == MsgUser.Token); if (baseUsers == null)//用户令牌不存在 { DataObj.OutError("2004"); return; } if (baseUsers.State != 1)//用户被锁定 { DataObj.OutError("2003"); return; } //if (baseUsers.CardStae != 2)//未实名认证 //{ // DataObj.OutError("2006"); // return; //} //if (baseUsers.MiBao != 1)//未设置支付密码 //{ // DataObj.OutError("2008"); // return; //} string uid = string.Format(",{0},", baseUsers.Id); EFPagingInfo <MsgUser> p = new EFPagingInfo <MsgUser>(); if (!MsgUser.Pg.IsNullOrEmpty()) { p.PageIndex = MsgUser.Pg; } if (!MsgUser.Pgs.IsNullOrEmpty()) { p.PageSize = MsgUser.Pgs; } //p.SqlWhere.Add(f => f.UId == baseUsers.Id || ( !f.DeleteUsers.Contains(uid) && f.SendUsers.Contains(uid)) );群发功能检索 p.SqlWhere.Add(f => f.UId == baseUsers.Id); p.SqlWhere.Add(f => f.State > 0 && f.AddTime > baseUsers.AddTime); p.OrderByList.Add("Id", "DESC"); IPageOfItems <MsgUser> List = Entity.Selects <MsgUser>(p); foreach (var P in List.Where(n => n.UId == 0)) { //处理已读 if (P.ReadUsers.IsNullOrEmpty()) { P.ReadUsers = string.Empty; } if (P.ReadUsers.IndexOf(uid) != -1) { P.State = 2; } P.Info = Utils.RemoveHtml(P.Info); P.Info = P.Info.Replace(" ", ""); } IList <MsgUser> iList = List.ToList(); StringBuilder sb = new StringBuilder(""); sb.Append("{"); sb.Append(List.PageToString()); sb.Append(","); sb.Append(iList.EntityToString()); sb.Append("}"); DataObj.Data = sb.ToString(); DataObj.Code = "0000"; DataObj.OutString(); }