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("暂无符合条件数据");
}
}
