public ActionResult ExportTable(DateTime?timestart, DateTime?timeend, string DepartmentId, string CostItemCode) { if (Session["Power"] == null || Session["UserCode"] == null) { return(null); } else { string UserCode = Session["UserCode"].ToString(); //员工编号 string Power = Session["Power"].ToString(); //权限字符串 var list = CostTotalService.LoadEntities(a => Power.Contains(a.DepartmentId)); if (CostItemCode != null && CostItemCode != "") { list = list.Where(a => a.CostItemCode == CostItemCode); } if (timestart != null) { list = list.Where(a => a.BillDate >= timestart); } if (timeend != null) { list = list.Where(a => a.BillDate < timeend.GetValueOrDefault().AddDays(1)); } //生成Datatable DataTable dt = new DataTable(); dt.Columns.Add("单据类型"); dt.Columns.Add("单据编码"); dt.Columns.Add("单据日期"); dt.Columns.Add("制单人"); dt.Columns.Add("审核人"); dt.Columns.Add("费用编码"); dt.Columns.Add("费用项目"); dt.Columns.Add("金额"); dt.Columns.Add("备注"); foreach (var item in list) { DataRow dr = dt.NewRow(); dr["单据类型"] = item.billType; dr["单据编码"] = item.BillCode; dr["单据日期"] = item.BillDate; dr["制单人"] = item.MakePerson; dr["审核人"] = item.ExaminePerson; dr["费用编码"] = item.CostItemCode; dr["费用项目"] = item.CostItemName; dr["金额"] = item.AllAmount; dr["备注"] = item.Remark; dt.Rows.Add(dr); } MemoryStream file = NPIOHelper.RenderToMemory(dt, "sheet1"); Response.Charset = "UTF-8"; Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8"); Response.ContentType = "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet";// "application/ms-excel/msword"; Response.AddHeader("Content-Disposition", "attachment;fileName=费用项目明细表.xls"); Response.BinaryWrite(file.ToArray()); return(new EmptyResult()); } }
/// <summary> /// 导出交货单 /// </summary> /// <param name="billId"></param> /// <returns></returns> private MemoryStream ExportGiveBill(Guid billId, string TemplateUrl) { GiveBill bill = CurrentDBSession.GiveBillDal.LoadEntities(a => a.Id == billId).FirstOrDefault(); if (bill == null) { return(null); } //根据模板创建Datatable DataTable dt = ExcelHelp.ExcelToDT(TemplateUrl); foreach (var record in bill.Record) { DataRow dr = dt.NewRow(); dr["LBBillCode"] = bill.LBBillCode; dr["LBBillDate"] = bill.LBBillDate; dr["LBTaskBillCode"] = bill.LBTaskBillCode; dr["LBLine"] = bill.LBLine; dr["LBContacts"] = bill.LBContacts; dr["LBPhone"] = bill.LBPhone; dr["LBMailCode"] = bill.LBMailCode; dr["LBCustomerCode"] = bill.LBCustomerCode; dr["LBCustomerName"] = bill.LBCustomerName; dr["LBSendAddress"] = bill.LBSendAddress; dr["LBRemark"] = bill.LBRemark; dr["CreateDate"] = bill.CreateDate; dr["ChargePerson"] = bill.ChargePerson; dr["MakePerson"] = bill.MakePerson; dr["Warehouse"] = bill.Warehouse; dr["WarehouseId"] = bill.WarehouseId; dr["LoadGoodsType"] = bill.LoadGoodsType; dr["OutputType"] = bill.OutputType; dr["OutputTypeId"] = bill.OutputTypeId; dr["BusinessType"] = bill.BusinessType; dr["LineWay"] = bill.LineWay; dr["Remark"] = bill.Remark; dr["Department"] = bill.Department; dr["DepartmentId"] = bill.DepartmentId; dr["Company"] = bill.Company; dr["CompanyId"] = bill.CompanyId; //子表 dr["ItemCode"] = record.ItemCode; dr["ItemLocationId"] = record.ItemLocationId; dr["ItemBatch"] = record.ItemBatch; dr["Count"] = record.Count; dt.Rows.Add(dr); } return(NPIOHelper.RenderToMemory(dt, "sheet1")); }
/// <summary> /// 导出入库单 /// </summary> /// <param name="billId"></param> /// <param name="TemplateUrl"></param> /// <returns></returns> private MemoryStream ExportTransferBill(Guid billId, string TemplateUrl) { TransferBill bill = CurrentDBSession.TransferBillDal.LoadEntities(a => a.Id == billId).FirstOrDefault(); if (bill == null) { return(null); } DataTable dt = ExcelHelp.ExcelToDT(TemplateUrl); foreach (var record in bill.Record) { DataRow dr = dt.NewRow(); dr["LBBillCode"] = bill.LBBillCode; dr["LBBillDate"] = bill.LBBillDate; dr["LBFrom"] = bill.LBFrom; dr["LBMoveType"] = bill.LBMoveType; dr["LBRemark"] = bill.LBRemark; dr["MakePerson"] = bill.MakePerson; dr["CreateDate"] = bill.CreateDate; dr["ChargePerson"] = bill.ChargePerson; dr["Warehouse"] = bill.Warehouse; dr["WarehouseId"] = bill.WarehouseId; dr["LoadGoodsType"] = bill.LoadGoodsType; dr["InputType"] = bill.InputType; dr["InputTypeId"] = bill.InputTypeId; dr["BusinessType"] = bill.BusinessType; dr["Remark"] = bill.Remark; dr["Department"] = bill.Department; dr["DepartmentId"] = bill.DepartmentId; dr["Company"] = bill.Company; dr["CompanyId"] = bill.CompanyId; //子表 dr["ItemCode"] = record.ItemCode; dr["ItemLocationId"] = record.ItemLocationId; dr["ItemBatch"] = record.ItemBatch; dr["Count"] = record.Count; dt.Rows.Add(dr); } return(NPIOHelper.RenderToMemory(dt, "sheet1")); }
/// <summary> /// 导出Excel /// </summary> /// <returns></returns> public ActionResult ExportExcel(string DepartmentId, string WarehouseId, string ItemLocationId, string ItemCode, string InOutTypeId, string BillTypes, string ItemBatch, string LBBillCode, string BillCode, string timestart, string timeend) { int?WarehouseId1 = null; if (WarehouseId != "undefined") { WarehouseId1 = Convert.ToInt32(WarehouseId); } string[] temp = InOutTypeId.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries);//分割出入库类型id int?[] InOutTypeIds = new int?[temp.Length]; for (int i = 0; i < temp.Length; i++) { InOutTypeIds[i] = Convert.ToInt32(temp[i]); } string[] BillTypes1 = BillTypes.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries); //分割单据类型 DateTime?timestart1 = null; //开始时间 DateTime?timeend1 = null; //结束时间 if (timestart != "") { timestart1 = Convert.ToDateTime(timestart); } if (timeend != "") { timeend1 = Convert.ToDateTime(timeend).AddDays(1); } if (ItemLocationId == "") { ItemLocationId = null; } if (ItemCode == "") { ItemCode = null; } if (ItemBatch == "") { ItemBatch = null; } if (LBBillCode == "") { LBBillCode = null; } if (BillCode == "") { BillCode = null; } //获取全部数据 var list = RecordService.ExportExcel(DepartmentId, WarehouseId1, ItemLocationId, ItemCode, InOutTypeIds, BillTypes1, ItemBatch, LBBillCode, BillCode, timestart1, timeend1); //转换成DataTable DataTable dt = new DataTable(); dt.Columns.Add("单据类型"); dt.Columns.Add("系统单号"); dt.Columns.Add("客户单号"); dt.Columns.Add("物料编码"); dt.Columns.Add("物料名称"); dt.Columns.Add("仓库名称"); dt.Columns.Add("货位编码"); dt.Columns.Add("批次"); dt.Columns.Add("数量"); dt.Columns.Add("出入库类型"); foreach (var item in list) { DataRow dr = dt.NewRow(); dr["单据类型"] = ChangeTableType(item.MainTableType); dr["系统单号"] = item.BillCode; dr["客户单号"] = item.LBBillCode; dr["物料编码"] = item.ItemCode; dr["物料名称"] = item.ItemName; dr["仓库名称"] = item.Warehouse; dr["货位编码"] = item.ItemLocationId; dr["批次"] = item.ItemBatch; dr["数量"] = item.Count; dr["出入库类型"] = item.InOutTypeName; dt.Rows.Add(dr); } MemoryStream file = NPIOHelper.RenderToMemory(dt, "sheet1"); Response.Charset = "UTF-8"; Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8"); Response.ContentType = "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet";// "application/ms-excel/msword"; Response.AddHeader("Content-Disposition", "attachment;fileName=出入库记录.xls"); Response.BinaryWrite(file.ToArray()); return(new EmptyResult()); }
public ActionResult ExportTable(DateTime?timestart, DateTime?timeend, string DepartmentId) { if (Session["Power"] == null || Session["UserCode"] == null) { return(null); } else { string Power = Session["Power"].ToString();//权限字符串 //获取装卸费力资费明细 var list1 = LoadingAndLaborCostDetailService.LoadEntities(a => Power.Contains(a.DepartmentId)).ToList(); if (timestart != null) { list1 = list1.Where(a => a.ExaminaDate >= timestart).ToList(); } if (timeend != null) { list1 = list1.Where(a => a.ExaminaDate < timeend.GetValueOrDefault().AddDays(1)).ToList(); } //获取其他明细 var list2 = LoadingAndLaborCostDetailService.LoadEntities(a => Power.Contains(a.DepartmentId)).ToList(); if (timestart != null) { list2 = list2.Where(a => a.ExaminaDate >= timestart).ToList(); } if (timeend != null) { list2 = list2.Where(a => a.ExaminaDate < timeend.GetValueOrDefault().AddDays(1)).ToList(); } //合并list1 list2 提取有用字段 返回 var res = list1.Select(a => new { a.CostItemCode, a.CostItemName, a.AllAmount }).ToList(); var res2 = list2.Select(a => new { a.CostItemCode, a.CostItemName, a.AllAmount }).ToList(); //合并成一个list res.AddRange(res2); //分组求和 var temp = from a in res group a by new { a.CostItemCode, a.CostItemName } into g select new { g.Key.CostItemCode, g.Key.CostItemName, Price = g.Sum(a => a.AllAmount) }; var tempNew = temp.ToList().Select(a => new { a.CostItemCode, a.CostItemName, a.Price, Type = a.CostItemCode.Substring(0, 2) == "64" ? "成本" : "收入" }); //转换成DataTable DataTable dt = new DataTable(); dt.Columns.Add("科目编码"); dt.Columns.Add("科目名称"); dt.Columns.Add("科目分类"); dt.Columns.Add("发生金额"); foreach (var item in tempNew) { DataRow dr = dt.NewRow(); dr["科目编码"] = item.CostItemCode; dr["科目名称"] = item.CostItemName; dr["科目分类"] = item.Type; dr["发生金额"] = item.Price; dt.Rows.Add(dr); } MemoryStream file = NPIOHelper.RenderToMemory(dt, "sheet1"); Response.Charset = "UTF-8"; Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8"); Response.ContentType = "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet";// "application/ms-excel/msword"; Response.AddHeader("Content-Disposition", "attachment;fileName=费用项目汇总表.xls"); Response.BinaryWrite(file.ToArray()); return(new EmptyResult()); } }