public ActionResult Query(LotScrapQueryViewModel model) { if (ModelState.IsValid) { using (LotQueryServiceClient client = new LotQueryServiceClient()) { PagingConfig cfg = new PagingConfig() { OrderBy = "EditTime", Where = GetQueryCondition(model) }; MethodReturnResult <IList <LotTransactionScrap> > result = client.GetLotTransactionScrap(ref cfg); if (result.Code == 0) { ViewBag.PagingConfig = cfg; ViewBag.List = result.Data; } } } if (Request.IsAjaxRequest()) { return(PartialView("_ListPartial")); } else { return(View(model)); } }
public string GetQueryCondition(LotScrapQueryViewModel model) { StringBuilder where = new StringBuilder(); if (model != null) { where.Append(@" EXISTS ( From LotTransaction as p WHERE p.Key=self.Key.TransactionKey AND p.UndoFlag='0')"); if (!string.IsNullOrEmpty(model.OrderNumber)) { where.AppendFormat(@" {0} EXISTS( From LotTransaction as p WHERE p.Key=self.Key.TransactionKey AND p.OrderNumber='{1}')" , where.Length > 0 ? "AND" : string.Empty , model.OrderNumber); } if (!string.IsNullOrEmpty(model.RouteStepName)) { where.AppendFormat(@" {0} EXISTS( From LotTransaction as p WHERE p.Key=self.Key.TransactionKey AND p.RouteStepName='{1}')" , where.Length > 0 ? "AND" : string.Empty , model.RouteStepName); } if (!string.IsNullOrEmpty(model.ReasonCodeName)) { where.AppendFormat(" {0} Key.ReasonCodeName LIKE '{1}%'" , where.Length > 0 ? "AND" : string.Empty , model.ReasonCodeName); } if (!string.IsNullOrEmpty(model.RouteOperationName)) { where.AppendFormat(" {0} RouteOperationName LIKE '{1}%'" , where.Length > 0 ? "AND" : string.Empty , model.RouteOperationName); } if (!string.IsNullOrEmpty(model.ResponsiblePerson)) { where.AppendFormat(" {0} ResponsiblePerson LIKE '{1}%'" , where.Length > 0 ? "AND" : string.Empty , model.ResponsiblePerson); } if (model.StartTime != null) { where.AppendFormat(" {0} EditTime >= '{1:yyyy-MM-dd HH:mm:ss}'" , where.Length > 0 ? "AND" : string.Empty , model.StartTime); } if (model.EndTime != null) { where.AppendFormat(" {0} EditTime <= '{1:yyyy-MM-dd HH:mm:ss}'" , where.Length > 0 ? "AND" : string.Empty , model.EndTime); } } return(where.ToString()); }
public async Task <ActionResult> ExportToExcel(LotScrapQueryViewModel model) { IList <LotTransactionScrap> lst = new List <LotTransactionScrap>(); using (LotQueryServiceClient client = new LotQueryServiceClient()) { await Task.Run(() => { PagingConfig cfg = new PagingConfig() { IsPaging = false, OrderBy = "EditTime", Where = GetQueryCondition(model) }; MethodReturnResult <IList <LotTransactionScrap> > result = client.GetLotTransactionScrap(ref cfg); if (result.Code == 0) { lst = result.Data; } }); } //创建工作薄。 IWorkbook wb = new HSSFWorkbook(); //设置EXCEL格式 ICellStyle style = wb.CreateCellStyle(); style.FillForegroundColor = 10; //有边框 style.BorderBottom = BorderStyle.THIN; style.BorderLeft = BorderStyle.THIN; style.BorderRight = BorderStyle.THIN; style.BorderTop = BorderStyle.THIN; IFont font = wb.CreateFont(); font.Boldweight = 10; style.SetFont(font); ICell cell = null; IRow row = null; ISheet ws = null; for (int j = 0; j < lst.Count; j++) { if (j % 65535 == 0) { ws = wb.CreateSheet(); row = ws.CreateRow(0); #region //列名 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(StringResource.ItemNo); //项目号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(WIPResources.StringResource.LotNumber); //批次号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("工单号"); //工单号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("工序"); //工序 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("线别"); //线别 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(WIPResources.StringResource.LotScrapViewModel_ScrapQuantity); //数量 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(WIPResources.StringResource.ReasonCodeCategoryName); //原因代码组 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(WIPResources.StringResource.ReasonCodeName); //原因代码 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(WIPResources.StringResource.ReasonDescription); //原因描述 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(WIPResources.StringResource.LotScrapViewModel_RouteOperationName); //责任工序 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(WIPResources.StringResource.LotScrapViewModel_ResponsiblePerson); //责任人 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(StringResource.Description); //描述 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("操作时间"); //操作时间 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue("操作人"); //操作人 #endregion font.Boldweight = 5; } LotTransactionScrap obj = lst[j]; LotTransaction transObj = null; LotTransactionHistory lotHisObj = null; using (LotQueryServiceClient client = new LotQueryServiceClient()) { MethodReturnResult <LotTransaction> result = client.GetTransaction(obj.Key.TransactionKey); if (result.Code == 0) { transObj = result.Data; } MethodReturnResult <LotTransactionHistory> result1 = client.GetLotTransactionHistory(obj.Key.TransactionKey); if (result1.Code == 0) { lotHisObj = result1.Data; } } row = ws.CreateRow(j + 1); #region //数据 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(j + 1); //项目号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(transObj.LotNumber); //批次号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(transObj.OrderNumber); //工单号 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(transObj.RouteStepName); //工序 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(lotHisObj != null ? lotHisObj.LineCode : string.Empty); //线别 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.Quantity); //数量 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.Key.ReasonCodeCategoryName); //原因代码组 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.Key.ReasonCodeName); //原因代码 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.Description); //原因描述 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.RouteOperationName); //责任工序 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.ResponsiblePerson); //责任人 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.Description); //描述 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.EditTime)); //操作时间 cell = row.CreateCell(row.Cells.Count); cell.CellStyle = style; cell.SetCellValue(obj.Editor); //操作人 #endregion } MemoryStream ms = new MemoryStream(); wb.Write(ms); ms.Flush(); ms.Position = 0; return(File(ms, "application/vnd.ms-excel", "LotScrapData.xls")); }
// //Get: /WIP/LotScrap/Query public ActionResult Query() { LotScrapQueryViewModel model = new LotScrapQueryViewModel(); return(Query(model)); }