private IPageOfItems <PostComment> getComments(IPageOfItems <PostCommentShell> postCommentShellList) { List <PostComment> comments = new List <PostComment>(postCommentShellList.Count()); foreach (PostCommentShell postCommentShell in postCommentShellList) { comments.Add(getComment(postCommentShell)); } return(new PageOfItems <PostComment>(comments, postCommentShellList.PageIndex, postCommentShellList.PageSize, postCommentShellList.TotalItemCount)); }
public static string PageState <T>(this HtmlHelper htmlHelper, IPageOfItems <T> pageOfAList, Func <string, string, string> localize) { if (pageOfAList != null && pageOfAList.TotalPageCount > 1) { return(string.Format( "<div class=\"pageState\">{0}</div>", string.Format( localize("PageStateFormat", "{0}-{1} of {2}"), (pageOfAList.PageIndex * pageOfAList.PageSize) + 1, (pageOfAList.PageIndex * pageOfAList.PageSize) + pageOfAList.Count(), pageOfAList.TotalItemCount ) )); } return(""); }
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(Card Card, EFPagingInfo <Card> p, DateTime?FInTime, DateTime?FOutTime, string Num0, string Num1, string Password) { if (!Card.State.IsNullOrEmpty()) { if (Card.State == 99) { Card.State = 0; } p.SqlWhere.Add(f => f.State == Card.State); } if (!Card.Auto.IsNullOrEmpty()) { if (Card.Auto == 99) { Card.Auto = 0; } p.SqlWhere.Add(f => f.Auto == Card.Auto); } if (!Card.AId.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.AId == Card.AId); } if (!Card.AdminId.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.AdminId == Card.AdminId); } if (!FInTime.IsNullOrEmpty() && !FOutTime.IsNullOrEmpty()) { // FOutTime = ((DateTime)FOutTime).AddDays(1); p.SqlWhere.Add(f => f.AddTime > FInTime && f.AddTime < FOutTime); } if (!Num0.IsNullOrEmpty() && !Num1.IsNullOrEmpty()) { long num0 = Int64.Parse(Num0); long num1 = Int64.Parse(Num1); num0 = num0 - 1000000000; num1 = num1 - 1000000000; p.SqlWhere.Add(f => f.Id >= num0 && f.Id <= num1); } p.PageSize = 100000; p.OrderByList.Add("Id", "DESC"); IPageOfItems <Card> cardList = Entity.Selects <Card>(p); if (cardList.Count() > 0) { string file = Server.MapPath("/template") + "\\card.xlsx"; ExcelPackage package = new ExcelPackage(new FileInfo(file), true); var sheet = package.Workbook.Worksheets[1]; var cells = sheet.Cells; int Rows = cardList.Count(); //设置数据开始行 int Befor = 2; sheet.InsertRow(Befor + 1, Rows - 1, Befor); int i = Befor; sheet.Row(i - 1).Height = 22;//设置行高 string[] State = new string[] { "已失效", "正常", "已使用" }; string[] Auto = new string[] { "自动", "保留" }; int maxCol = 0; //获取机构及管理员 IList <SysAgent> SAList = Entity.SysAgent.Where(n => n.State == 1).ToList(); IList <SysAdmin> AdminList = Entity.SysAdmin.Where(n => n.State == 1 && n.AgentId > 0).ToList(); foreach (var item in cardList) { string AName = "--"; if (item.AId > 0) { SysAgent SA = SAList.FirstOrNew(n => n.Id == item.AId); if (!SA.Name.IsNullOrEmpty()) { AName = SA.Name; } } string BName = "--"; if (item.AdminId > 0) { SysAdmin SAA = AdminList.FirstOrNew(n => n.Id == item.AdminId); if (!SAA.TrueName.IsNullOrEmpty()) { BName = SAA.TrueName; } } sheet.Row(i).Height = 20;//设置行高 //机构 cells["A" + i].Value = AName; //编号 cells["B" + i].Value = BName; //卡号 cells["C" + i].Value = item.Code; //密码 cells["D" + i].Value = item.PasWd; //时间 cells["E" + i].Value = item.AddTime; //状态 int state = (int)item.State; if (state > 2) { state = 0; } cells["F" + i].Value = State[state]; //保留 byte auto = item.Auto; if (auto > 2) { auto = 0; } cells["G" + i].Value = Auto[auto]; i++; } i--; maxCol = 7; //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 (!Password.IsNullOrEmpty()) { Response.BinaryWrite(package.GetAsByteArray(Password)); } else { 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(Orders Orders, EFPagingInfo <Orders> p, bool?IsShowSupAgent, int TimeType = 1) { if (IsShowSupAgent == null) { IsShowSupAgent = false; } p = this.Condition(Orders, p, IsShowSupAgent, TimeType); p.PageSize = 99999999; p.OrderByList.Add("Id", "DESC"); IPageOfItems <Orders> OrdersList = Entity.Selects <Orders>(p); if (OrdersList.Count() > 0) { #region 导出 string file = Server.MapPath("/template") + "\\finorders.xlsx"; ExcelPackage package = new ExcelPackage(new FileInfo(file), true); var sheet = package.Workbook.Worksheets[1]; var cells = sheet.Cells; int Rows = OrdersList.Count(); //设置数据开始行 int Befor = 2; sheet.InsertRow(Befor + 1, Rows - 1, Befor); int i = Befor; sheet.Row(i - 1).Height = 22;//设置行高 int maxCol = 0; //获取机构及管理员 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 <PayConfig> PayConfigList = Entity.PayConfig.Where(n => n.State == 1).ToList(); IList <SysAgent> SysAgentList = Entity.SysAgent.ToList(); foreach (var item in OrdersList) { #region MyRegion string AName = "--"; if (item.AId > 0) { SysAgent SA = SysAgentList.FirstOrNew(n => n.Id == item.Agent); if (!SA.Name.IsNullOrEmpty()) { AName = SA.Name; } } string BName = "--"; Users OU = UsersList.FirstOrNew(n => n.Id == item.UId); if (!OU.TrueName.IsNullOrEmpty()) { BName = OU.TrueName; } PayConfig PayConfig = PayConfigList.FirstOrNew(n => n.Id == item.PayWay); sheet.Row(i).Height = 22;//设置行高 //机构 cells["A" + i].Value = AName; //交易号 cells["B" + i].Value = item.TNum; //交易商户 cells["C" + i].Value = OU.NeekName + "[" + OU.TrueName + "]"; //交易类型 cells["D" + i].Value = item.GetTTName(); //交易内容 cells["E" + i].Value = item.GetPayNameWithTName(); //交易金额 cells["F" + i].Value = item.Amoney.ToString("f2"); //交易时间 cells["G" + i].Value = item.PayTime; //入账时间 cells["H" + i].Value = (item.InTimed != null?Convert.ToDateTime(item.InTimed).ToString():""); //支付方式 cells["I" + i].Value = PayConfig.Name; //订单状态 cells["J" + i].Value = item.GetState(); string temp = string.Empty; if (item.TType == 2 || item.TType == 5) { temp = "T+" + item.TrunType; } if (item.TType == 1 || item.TType == 7 || item.TType == 8 || item.TType == 9) { temp = "T+" + item.LagEntryDay; } //接收方式 cells["K" + i].Value = temp; double I = 0, K = 0, N = 0; decimal J = 0, L = 0, M = 0; string Is = ""; #region MyRegion if (item.TType == 1) { //银联卡支付订单 OrderRecharge OrderRecharge = Entity.OrderRecharge.FirstOrNew(n => n.OId == item.TNum); I = OrderRecharge.UserRate; //商户费率 J = OrderRecharge.Poundage; //手续费 K = OrderRecharge.SysRate; //第三方费率 L = ((decimal)OrderRecharge.SysRate * OrderRecharge.Amoney).Ceiling(); //第三方手续费 N = OrderRecharge.AgentPayGet; //分支机构提成 M = OrderRecharge.Poundage - (decimal)OrderRecharge.SysRate * OrderRecharge.Amoney - (decimal)OrderRecharge.AgentPayGet; //利润 } if (item.TType == 2) { //提现订单 OrderCash OrderCash = Entity.OrderCash.FirstOrNew(n => n.OId == item.TNum); I = 0; if (!OrderCash.ECash.IsNullOrEmpty() && !OrderCash.Cash.IsNullOrEmpty()) { Is = OrderCash.ECash.ToMoney() + "+" + (OrderCash.Cash * 100).ToString("F2") + "%"; } else if (!OrderCash.ECash.IsNullOrEmpty()) { Is = OrderCash.ECash.ToMoney(); } else if (!OrderCash.Cash.IsNullOrEmpty()) { Is = (OrderCash.Cash * 100).ToString("F2") + "%"; } else { Is = "0"; } J = (decimal)OrderCash.UserRate; //手续费 K = 0; //第三方费率 L = OrderCash.CashRate; //第三方手续费 N = OrderCash.AgentCashGet; //分支机构提成 M = (decimal)OrderCash.UserRate - OrderCash.CashRate - (decimal)OrderCash.AgentCashGet; //利润 } if (item.TType == 3) { //转帐订单 OrderTransfer OrderTransfer = Entity.OrderTransfer.FirstOrNew(n => n.OId == item.TNum); I = OrderTransfer.UserRate; //商户费率 J = OrderTransfer.Poundage; //手续费 K = OrderTransfer.SysRate; //第三方费率 L = ((decimal)OrderTransfer.SysRate * OrderTransfer.Amoney).Ceiling(); //第三方手续费 N = OrderTransfer.AgentPayGet; //分支机构提成 M = OrderTransfer.Amoney - OrderTransfer.PayMoney - L - (decimal)OrderTransfer.AgentPayGet; //利润 } if (item.TType == 5) { //付房租订单 OrderHouse OrderHouse = Entity.OrderHouse.FirstOrNew(n => n.OId == item.TNum); I = OrderHouse.UserRate + (double)OrderHouse.CashRate; //商户费率 J = OrderHouse.Poundage; //手续费 K = OrderHouse.SysRate; //第三方费率 L = ((decimal)OrderHouse.SysRate * OrderHouse.Amoney).Ceiling(); //第三方手续费 N = OrderHouse.AgentPayGet; //分支机构提成 M = OrderHouse.Amoney - OrderHouse.PayMoney - L - (decimal)OrderHouse.AgentPayGet; //利润 } if (item.TType == 6) { //升级订单 PayConfigOrder PayConfigOrder = Entity.PayConfigOrder.FirstOrNew(n => n.OId == item.TNum); I = 0; //商户费率 J = PayConfigOrder.Poundage; //手续费 K = PayConfigOrder.SysRate; //第三方费率 L = ((decimal)PayConfigOrder.SysRate * PayConfigOrder.Amoney).Ceiling(); //第三方手续费 N = PayConfigOrder.AgentGet; //分支机构提成 M = PayConfigOrder.Amoney - (decimal)PayConfigOrder.AgentGet; //利润 } if (item.TType == 7 || item.TType == 8 || item.TType == 9) { //当面付 OrderF2F OrderF2F = Entity.OrderF2F.FirstOrNew(n => n.OId == item.TNum); I = OrderF2F.UserRate; //商户费率 J = OrderF2F.Poundage; //手续费 K = OrderF2F.SysRate; //第三方费率 L = ((decimal)OrderF2F.SysRate * OrderF2F.Amoney).Ceiling(); //第三方手续费 N = OrderF2F.AgentPayGet; //分支机构提成 M = OrderF2F.Poundage - (decimal)OrderF2F.SysRate * OrderF2F.Amoney - (decimal)OrderF2F.AgentPayGet; //利润 } if (item.TType == 10) { //升级订单 DaiLiOrder DaiLiOrder = Entity.DaiLiOrder.FirstOrNew(n => n.OId == item.TNum); I = 0; //商户费率 J = 0; //手续费 K = 0; //第三方费率 L = 0; //第三方手续费 N = DaiLiOrder.AgentGet; //分支机构提成 M = DaiLiOrder.Amoney - (decimal)DaiLiOrder.AgentGet; //利润 } #endregion //用户费率9 if (item.TType == 2) { cells["L" + i].Value = Is; } else { cells["L" + i].Value = I; } //用户手续费10 cells["M" + i].Value = J; //支出费率11 系统费率 cells["N" + i].Value = K; //支出手续费12 cells["O" + i].Value = L; //利润13 cells["P" + i].Value = M; //结算金额14 cells["Q" + i].Value = N; i++; #endregion } sheet.Row(i).Height = 40;//设置行高 //交易总额 cells["F" + i].Formula = "SUM(F" + Befor + ":F" + (i - 1) + ")"; //用户手续费10 cells["M" + i].Formula = "SUM(M" + Befor + ":M" + (i - 1) + ")"; //支出手续费12 cells["O" + i].Formula = "SUM(O" + Befor + ":O" + (i - 1) + ")"; //利润13 cells["P" + i].Formula = "SUM(P" + Befor + ":P" + (i - 1) + ")"; //结算金额14 cells["Q" + i].Formula = "SUM(Q" + Befor + ":Q" + (i - 1) + ")"; i--; maxCol = 17; //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, 7, i, 7].Style.Numberformat.Format = "yyyy-mm-dd HH:mm:ss"; sheet.Cells[Befor, 8, i, 8].Style.Numberformat.Format = "yyyy-mm-dd HH:mm:ss"; sheet.Cells[Befor, 12, i, 12].Style.Numberformat.Format = "#0.00%"; sheet.Cells[Befor, 14, i, 14].Style.Numberformat.Format = "#0.00%"; i++; sheet.Cells[Befor, 6, i, 6].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; sheet.Cells[Befor, 13, i, 13].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; sheet.Cells[Befor, 15, i, 15].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; sheet.Cells[Befor, 16, i, 16].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; sheet.Cells[Befor, 17, i, 17].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"); #endregion } 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 PayCash(OrderCash OrderCash, EFPagingInfo <OrderCash> p, bool?IsShowSupAgent) { if (IsShowSupAgent == null) { IsShowSupAgent = false; } p.PageSize = 99999999; p.SqlWhere.Add(f => f.OrderState == 2); p.SqlWhere.Add(f => f.PayState == 1 || f.PayState == 2); if (OrderCash.AddTime.IsNullOrEmpty()) { OrderCash.AddTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")); } if (OrderCash.FTime.IsNullOrEmpty()) { OrderCash.FTime = DateTime.Now; } if (!OrderCash.Owner.IsNullOrEmpty()) { IList <Users> UList = Entity.Users.Where(n => n.TrueName.Contains(OrderCash.Owner) || n.NeekName.Contains(OrderCash.Owner) || n.UserName == OrderCash.Owner).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 (!OrderCash.Cash.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.Amoney >= OrderCash.Cash); } if (!OrderCash.ECash.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.Amoney <= OrderCash.ECash); } if (!OrderCash.OId.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.OId == OrderCash.OId); } if (!OrderCash.Agent.IsNullOrEmpty()) { //是否要显示下级 if ((bool)IsShowSupAgent) { SysAgent LowerLevelAgent = Entity.SysAgent.Where(s => s.Id == OrderCash.Agent).FirstOrNew(); IList <SysAgent> SysAgentList = LowerLevelAgent.GetSupAgent(Entity); IList <int> UID = new List <int>(); foreach (var s in SysAgentList) { UID.Add(s.Id); } p.SqlWhere.Add(f => UID.Contains(f.Agent)); } else { p.SqlWhere.Add(f => f.Agent == OrderCash.Agent); } } if (!OrderCash.FState.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.FState == (OrderCash.FState == 99 ? 0 : OrderCash.FState)); } if (!OrderCash.TrunType.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.TrunType == (OrderCash.TrunType == 99 ? 0 : OrderCash.TrunType)); } if (!OrderCash.AId.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.AId == OrderCash.AId); } if (!OrderCash.FId.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.FId == OrderCash.FId); } if (!OrderCash.AgentState.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.AgentState == OrderCash.AgentState); } if (!OrderCash.AddTime.IsNullOrEmpty() && !OrderCash.FTime.IsNullOrEmpty()) { // DateTime FTime = ((DateTime)OrderCash.FTime).AddDays(1); DateTime FTime = OrderCash.FTime.Value; p.SqlWhere.Add(f => f.AddTime > OrderCash.AddTime && f.AddTime < FTime); } p.OrderByList.Add("Id", "ASC"); IPageOfItems <OrderCash> OrderCashList = Entity.Selects <OrderCash>(p); TaskCash TaskCash = new TaskCash(); TaskCash.State = 0; TaskCash.AddTime = DateTime.Now; TaskCash.Total = OrderCashList.Count(); TaskCash.Success = 0; TaskCash.Fail = 0; Entity.TaskCash.AddObject(TaskCash); Entity.SaveChanges(); int suc = 0; int fa = 0; int same = 0; foreach (var O in OrderCashList) { if (O.PayState == 1 && O.OrderState == 2) { TaskCashInfo TaskCashInfo = Entity.TaskCashInfo.FirstOrDefault(n => n.OId == O.OId); if (TaskCashInfo == null) { TaskCashInfo = new TaskCashInfo(); TaskCashInfo.OId = O.OId; TaskCashInfo.TId = TaskCash.Id; TaskCashInfo.State = 0; TaskCashInfo.OState = 0; TaskCashInfo.NState = 0; TaskCashInfo.AddTime = DateTime.Now; Entity.TaskCashInfo.AddObject(TaskCashInfo); suc++; } else { same++; } } else { fa++; } } TaskCash.Total = suc; Entity.SaveChanges(); System.Web.HttpContext.Current.Response.AddHeader("content-type", "application/json"); Response.Write("{\"id\":" + TaskCash.Id + ",\"same\":" + same + ",\"success\":" + suc + ",\"fail\":" + fa + "}"); }
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 XLSDo(Orders Orders) { 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")); Orders.ETime = DateTime.Now; } EFPagingInfo <ApplyCredit> cp = new EFPagingInfo <ApplyCredit>(); EFPagingInfo <ApplyLoan> lp = new EFPagingInfo <ApplyLoan>(); if (!Orders.Agent.IsNullOrEmpty()) { cp.SqlWhere.Add(f => f.AgentId == Orders.Agent); lp.SqlWhere.Add(f => f.AgentId == Orders.Agent); } if (!Orders.STime.IsNullOrEmpty() && !Orders.ETime.IsNullOrEmpty()) { DateTime ETime = Orders.ETime; cp.SqlWhere.Add(f => f.PayTime > Orders.STime && f.PayTime < ETime); lp.SqlWhere.Add(f => f.PayTime > Orders.STime && f.PayTime < ETime); } cp.SqlWhere.Add(f => f.PayState == 1); lp.SqlWhere.Add(f => f.PayState == 1); cp.PageSize = 99999999; lp.PageSize = 99999999; cp.OrderByList.Add("Id", "DESC"); lp.OrderByList.Add("Id", "DESC"); IPageOfItems <ApplyCredit> ApplyCreditList = null; IPageOfItems <ApplyLoan> ApplyLoanList = null; if (Orders.TType.IsNullOrEmpty()) { ApplyCreditList = Entity.Selects <ApplyCredit>(cp); ApplyLoanList = Entity.Selects <ApplyLoan>(lp); } else if (Orders.TType == 1) { ApplyCreditList = Entity.Selects <ApplyCredit>(cp); } else if (Orders.TType == 2) { ApplyLoanList = Entity.Selects <ApplyLoan>(lp); } if (ApplyCreditList.Count() > 0 || ApplyLoanList.Count() > 0) { string file = Server.MapPath("/template") + "\\finhaodai.xlsx"; ExcelPackage package = new ExcelPackage(new FileInfo(file), true); var sheet = package.Workbook.Worksheets[1]; var cells = sheet.Cells; int Rows = ApplyCreditList.Count() + ApplyLoanList.Count(); //设置数据开始行 int Befor = 2; sheet.InsertRow(Befor + 1, Rows - 1, Befor); int i = Befor; sheet.Row(i - 1).Height = 22;//设置行高 int maxCol = 0; //获取机构及管理员 IList <SysAgent> SysAgentList = Entity.SysAgent.Where(n => n.State == 1).ToList(); foreach (var item in ApplyCreditList) { string AName = "--"; if (item.AId > 0) { SysAgent SA = SysAgentList.FirstOrNew(n => n.Id == item.AgentId); if (!SA.Name.IsNullOrEmpty()) { AName = SA.Name; } } sheet.Row(i).Height = 22;//设置行高 //信息编号1 cells["A" + i].Value = "X" + item.Id.ToString().PadLeft(8, '0'); //分支机构2 cells["B" + i].Value = AName; //信息类型3 cells["C" + i].Value = "信用卡申请"; //申请时间4 cells["D" + i].Value = item.AddTime; //售出金额5 cells["E" + i].Value = item.Amoney; //售出时间6 cells["F" + i].Value = item.PayTime; //利润7 cells["G" + i].Value = item.Amoney - item.AgentMoney; //结算金额8 cells["H" + i].Value = item.AgentMoney; i++; } foreach (var item in ApplyLoanList) { string AName = "--"; if (item.AId > 0) { SysAgent SA = SysAgentList.FirstOrNew(n => n.Id == item.AgentId); if (!SA.Name.IsNullOrEmpty()) { AName = SA.Name; } } sheet.Row(i).Height = 22;//设置行高 //信息编号1 cells["A" + i].Value = "D" + item.Id.ToString().PadLeft(8, '0'); //分支机构2 cells["B" + i].Value = AName; //信息类型3 cells["C" + i].Value = "贷款申请"; //申请时间4 cells["D" + i].Value = item.AddTime; //售出金额5 cells["E" + i].Value = item.Amoney; //售出时间6 cells["F" + i].Value = item.PayTime; //利润7 cells["G" + i].Value = item.Amoney - item.AgentMoney; //结算金额8 cells["H" + i].Value = item.AgentMoney; i++; } sheet.Row(i).Height = 40;//设置行高 //交易总额5 cells["E" + i].Formula = "SUM(E" + Befor + ":E" + (i - 1) + ")"; //利润7 cells["G" + i].Formula = "SUM(G" + Befor + ":G" + (i - 1) + ")"; //结算金额8 cells["H" + i].Formula = "SUM(H" + Befor + ":H" + (i - 1) + ")"; i--; maxCol = 8; //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, 4, i, 4].Style.Numberformat.Format = "yyyy-mm-dd HH:mm:ss"; sheet.Cells[Befor, 6, i, 6].Style.Numberformat.Format = "yyyy-mm-dd HH:mm:ss"; //sheet.Cells[Befor, 9, i, 9].Style.Numberformat.Format = "#0.00%"; //sheet.Cells[Befor, 11, i, 11].Style.Numberformat.Format = "#0.00%"; i++; sheet.Cells[Befor, 5, i, 5].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; sheet.Cells[Befor, 7, i, 7].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; 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("暂无符合条件数据"); } }