public FileResult ExcelExport(string UserName, DateTime Date, int show) { if (Date == null) { return(null); } var fileName = "跑批" + Date.ToString("yyyyMMdd"); string file = Server.MapPath("/template") + "\\CheckUser.xls"; var export = new ExcelExport(); var workbook = export.GetWorkbook(file); //var CellStyle = export.CellStyle(workbook); // var UserList = this.Entity.Users.ToList(); List <CheckUserModel> UsersList = new List <CheckUserModel>(); int uid = 0; #region 商户汇总 if (true) { DataTable table = new DataTable(); DataRow row = null; var query = this.Entity.CheckUser.AsQueryable(); if (!UserName.IsNullOrEmpty()) { uid = this.Entity.Users.Where(o => o.UserName == UserName).Select(o => o.Id).FirstOrDefault(); query = query.Where(o => o.UId == uid); } if (show == 1) { query = query.Where(o => o.NowMoney != o.CheckMoney || o.NowFrozen != o.CheckFrozen); } var CheckUserList = query.Where(o => o.TaskDate == Date).ToList(); var uids = CheckUserList.Select(o => o.UId).ToList(); UsersList = this.Entity.Users.Where(o => uids.Contains(o.Id)) .Select(o => new CheckUserModel() { Id = o.Id, TrueName = o.TrueName, UserName = o.UserName }).ToList(); // 创建 datatable table.Columns.Add(new DataColumn("商户", typeof(string))); table.Columns.Add(new DataColumn("手机号", typeof(string))); table.Columns.Add(new DataColumn("原始余额", typeof(decimal))); table.Columns.Add(new DataColumn("原始冻结金额", typeof(decimal))); table.Columns.Add(new DataColumn("模拟余额", typeof(decimal))); table.Columns.Add(new DataColumn("模拟冻结金额", typeof(decimal))); table.Columns.Add(new DataColumn("记录余额", typeof(decimal))); table.Columns.Add(new DataColumn("记录冻结金额", typeof(decimal))); table.Columns.Add(new DataColumn("差额余额", typeof(decimal))); table.Columns.Add(new DataColumn("差额冻结金额", typeof(decimal))); // 填充数据 foreach (var item in CheckUserList) { var u = UsersList.FirstOrDefault(o => o.Id == item.UId); row = table.NewRow(); row[0] = u.TrueName; row[1] = u.UserName; row[2] = item.PastMoney.ToString("n2"); row[3] = item.PastFrozen.ToString("n2"); row[4] = item.CheckMoney.ToString("n2"); row[5] = item.CheckFrozen.ToString("n2"); row[6] = item.NowMoney.ToString("n2"); row[7] = item.NowFrozen.ToString("n2"); row[8] = (item.CheckMoney - item.NowMoney).ToString("n2"); row[9] = (item.CheckFrozen - item.NowFrozen).ToString("n2"); table.Rows.Add(row); } var Sheet = workbook.GetSheetAt(0); Sheet.GetRow(0).GetCell(0).SetCellValue(Date.ToString("yyyy-MM-dd")); int Befor = 3; foreach (DataRow item in table.Rows) { var dataRow = Sheet.CreateRow(Befor); foreach (DataColumn column in table.Columns) { var newCell = dataRow.CreateCell(column.Ordinal); var drValue = item[column].ToString(); newCell = export.Assign(newCell, column.DataType.ToString(), drValue); } Befor++; } } #endregion #region 跑批过程 if (true) { DataTable table = new DataTable(); DataRow row = null; var CheckUserMoneyList = this.Entity.CheckUserMoney.Where(o => o.TaskDate == Date).OrderBy(o => o.Progress).ToList(); // 创建 datatable table.Columns.Add(new DataColumn("商户", typeof(string))); table.Columns.Add(new DataColumn("手机号", typeof(string))); table.Columns.Add(new DataColumn("操作", typeof(string))); table.Columns.Add(new DataColumn("变动余额", typeof(decimal))); table.Columns.Add(new DataColumn("变动冻结金额", typeof(decimal))); table.Columns.Add(new DataColumn("运算操作金额", typeof(decimal))); table.Columns.Add(new DataColumn("记录操作金额", typeof(decimal))); table.Columns.Add(new DataColumn("差额", typeof(decimal))); // 填充数据 foreach (var item in CheckUserMoneyList) { var u = UsersList.FirstOrNew(o => o.Id == item.UId); row = table.NewRow(); row[0] = u.TrueName; row[1] = u.UserName; row[2] = CheckExtensions.GetProgressName(item.Progress); row[3] = item.ChangeMoney.ToString("n2"); row[4] = item.ChangeFrozen.ToString("n2"); row[5] = item.ProgressMoney.ToString("n2"); row[6] = item.RecordMoney.ToString("n2"); row[7] = (item.ProgressMoney - item.RecordMoney).ToString("n2"); table.Rows.Add(row); } var Sheet = workbook.GetSheetAt(1); Sheet.GetRow(0).GetCell(0).SetCellValue(Date.ToString("yyyy-MM-dd")); int Befor = 2; foreach (DataRow item in table.Rows) { var dataRow = Sheet.CreateRow(Befor); foreach (DataColumn column in table.Columns) { var newCell = dataRow.CreateCell(column.Ordinal); var drValue = item[column].ToString(); newCell = export.Assign(newCell, column.DataType.ToString(), drValue); } Befor++; } } #endregion #region 异常订单 if (true) { DataTable table = new DataTable(); DataRow row = null; var CheckResultList = this.Entity.CheckResult.Where(o => o.TaskDate == Date).OrderByDescending(o => o.Id).ToList(); // 创建 datatable table.Columns.Add(new DataColumn("商户", typeof(string))); table.Columns.Add(new DataColumn("手机号", typeof(string))); table.Columns.Add(new DataColumn("订单号", typeof(string))); table.Columns.Add(new DataColumn("金额", typeof(decimal))); table.Columns.Add(new DataColumn("操作", typeof(string))); table.Columns.Add(new DataColumn("异常内容", typeof(string))); // 填充数据 foreach (var item in CheckResultList) { var u = UsersList.FirstOrNew(o => o.Id == item.UId); row = table.NewRow(); row[0] = u.TrueName; row[1] = u.UserName; row[2] = item.TNum; row[3] = item.Amount.ToString("n2"); row[4] = CheckExtensions.GetProgressName(item.CheckType); row[5] = item.CheckMsg; table.Rows.Add(row); } var Sheet = workbook.GetSheetAt(2); Sheet.GetRow(0).GetCell(0).SetCellValue(Date.ToString("yyyy-MM-dd")); int Befor = 2; foreach (DataRow item in table.Rows) { var dataRow = Sheet.CreateRow(Befor); foreach (DataColumn column in table.Columns) { var newCell = dataRow.CreateCell(column.Ordinal); var drValue = item[column].ToString(); newCell = export.Assign(newCell, column.DataType.ToString(), drValue); } Befor++; } } #endregion return(ExportExcelBase(workbook, fileName)); }