public ActionResult MaterialImport(HttpPostedFileBase fileImport) { JsonObjectResult jsonObjectResult = new JsonObjectResult(); string sError = ""; string userName = string.Empty; if (User.Identity.IsAuthenticated) { userName = User.Identity.Name; } try { if (fileImport != null) { List<ImOrExMaterialDefine> list = null; List<AscmMaterialItem> listAscmMaterialItem = new List<AscmMaterialItem>(); using (Stream stream = fileImport.InputStream) { list = new List<ImOrExMaterialDefine>(); NPOI.SS.UserModel.IWorkbook wb = NPOI.SS.UserModel.WorkbookFactory.Create(stream); ISheet sheet = wb.GetSheet("Sheet1"); IEnumerator rows = sheet.GetRowEnumerator(); while (rows.MoveNext()) { NPOI.SS.UserModel.IRow row = (NPOI.SS.UserModel.IRow)rows.Current; if (row.RowNum != 0) { List<NPOI.SS.UserModel.ICell> iCellList = new List<NPOI.SS.UserModel.ICell>(); for (int i = 0; i < ImOrExMaterialDefine.GetList().Count; i++) { NPOI.SS.UserModel.ICell iCell = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK); iCellList.Add(iCell); } ImOrExMaterialDefine ImportMaterial = new ImOrExMaterialDefine(); if (iCellList[0].ToString() != null) ImportMaterial.materialDocnumber = iCellList[0].ToString().Trim(); if (iCellList[1].ToString() != null) ImportMaterial.wipSupplyType = iCellList[1].ToString().Trim(); if (iCellList[2].ToString() != null) ImportMaterial.materialDescription = iCellList[2].ToString().Trim(); if (iCellList[3].ToString() != null) ImportMaterial.zMtlCategoryStatus = iCellList[3].ToString().Trim(); if (iCellList[4].ToString() != null) ImportMaterial.dMtlCategoryStatus = iCellList[4].ToString().Trim(); if (iCellList[5].ToString() != null) ImportMaterial.wMtlCategoryStatus = iCellList[5].ToString().Trim(); ImportMaterial.rowNumber = row.RowNum + 1; list.Add(ImportMaterial); } } } if (list != null && list.Count > 0) { foreach (ImOrExMaterialDefine ImportMaterial in list) { object obj = YnDaoHelper.GetInstance().nHibernateHelper.GetObject("select count(*) from AscmMaterialItem where docNumber = '" + ImportMaterial.materialDocnumber + "'"); if (obj == null) throw new Exception("查询异常!"); int iCount = 0; if (int.TryParse(obj.ToString(), out iCount) && iCount > 0) { List<AscmMaterialItem> tempList = AscmMaterialItemService.GetInstance().GetList("from AscmMaterialItem where docNumber = '" + ImportMaterial.materialDocnumber + "'"); if (tempList != null && tempList.Count > 0) { foreach (AscmMaterialItem ascmMaterialItem in tempList) { if (ImportMaterial.zMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.inStock)) { ascmMaterialItem.zMtlCategoryStatus = MtlCategoryStatusDefine.inStock; } else if (ImportMaterial.zMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.mixStock)) { ascmMaterialItem.zMtlCategoryStatus = MtlCategoryStatusDefine.mixStock; } else if (ImportMaterial.zMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.preStock)) { ascmMaterialItem.zMtlCategoryStatus = MtlCategoryStatusDefine.preStock; } else if (string.IsNullOrEmpty(ImportMaterial.zMtlCategoryStatus)) { ascmMaterialItem.wMtlCategoryStatus = ""; } else if (!string.IsNullOrEmpty(ImportMaterial.zMtlCategoryStatus)) { sError += "物料编码[" + ascmMaterialItem.docNumber + "]总装备料形式书写不正确:" + ImportMaterial.zMtlCategoryStatus; continue; } if (ImportMaterial.dMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.inStock)) { ascmMaterialItem.dMtlCategoryStatus = MtlCategoryStatusDefine.inStock; } else if (ImportMaterial.dMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.mixStock)) { ascmMaterialItem.dMtlCategoryStatus = MtlCategoryStatusDefine.mixStock; } else if (ImportMaterial.dMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.preStock)) { ascmMaterialItem.dMtlCategoryStatus = MtlCategoryStatusDefine.preStock; } else if (string.IsNullOrEmpty(ImportMaterial.dMtlCategoryStatus)) { ascmMaterialItem.wMtlCategoryStatus = ""; } else if (!string.IsNullOrEmpty(ImportMaterial.dMtlCategoryStatus)) { sError += "物料编码[" + ascmMaterialItem.docNumber + "]电装备料形式书写不正确:" + ImportMaterial.dMtlCategoryStatus; continue; } if (ImportMaterial.wMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.inStock)) { ascmMaterialItem.wMtlCategoryStatus = MtlCategoryStatusDefine.inStock; } else if (ImportMaterial.wMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.mixStock)) { ascmMaterialItem.wMtlCategoryStatus = MtlCategoryStatusDefine.mixStock; } else if (ImportMaterial.wMtlCategoryStatus == MtlCategoryStatusDefine.DisplayText(MtlCategoryStatusDefine.preStock)) { ascmMaterialItem.wMtlCategoryStatus = MtlCategoryStatusDefine.preStock; } else if (string.IsNullOrEmpty(ImportMaterial.wMtlCategoryStatus)) { ascmMaterialItem.wMtlCategoryStatus = ""; } else if (!string.IsNullOrEmpty(ImportMaterial.wMtlCategoryStatus)) { sError += "物料编码[" + ascmMaterialItem.docNumber + "]其他备料形式书写不正确:" + ImportMaterial.wMtlCategoryStatus; continue; } ascmMaterialItem.modifyTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm"); ascmMaterialItem.modifyUser = userName; listAscmMaterialItem.Add(ascmMaterialItem); } } } else { sError +="未找到对应的物料编码:" + ImportMaterial.materialDocnumber+"<br />"; } } using (ITransaction tx = YnDaoHelper.GetInstance().nHibernateHelper.GetCurrentSession().BeginTransaction()) { try { if (listAscmMaterialItem != null && listAscmMaterialItem.Count > 0) { YnDaoHelper.GetInstance().nHibernateHelper.UpdateList(listAscmMaterialItem); } tx.Commit(); sError += "【成功更新" + listAscmMaterialItem.Count.ToString() + "条记录!】"; jsonObjectResult.message = sError; jsonObjectResult.result = true; } catch (Exception ex) { tx.Rollback(); sError += ex.Message; } } } } } catch (Exception ex) { jsonObjectResult.message += ex.Message; YnBaseClass2.Helper.LogHelper.GetLog().Error(ex.Message,ex); } string sReturn = JsonConvert.SerializeObject(jsonObjectResult); return Content(sReturn); }
public ActionResult MaterialExport(string queryWord, string queryType, string queryStarDocnumber, string queryEndDocnumber, string zStatus, string dStatus, string wStatus, string queryDescribe) { List<ImOrExMaterialDefine> list = new List<ImOrExMaterialDefine>(); NPOI.SS.UserModel.IWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(); try { ISheet sheet = wb.CreateSheet("Sheet1"); sheet.SetColumnWidth(0, 20 * 256); sheet.SetColumnWidth(1, 13 * 256); sheet.SetColumnWidth(2, 30 * 256); sheet.SetColumnWidth(3, 15 * 256); sheet.SetColumnWidth(4, 15 * 256); sheet.SetColumnWidth(5, 15 * 256); int iRow = 0; NPOI.SS.UserModel.IRow titleRow = sheet.CreateRow(0); titleRow.Height = 20 * 20; for (int i = 0; i < ImOrExMaterialDefine.GetList().Count; i++) { titleRow.CreateCell(i).SetCellValue(ImOrExMaterialDefine.DisplayText(ImOrExMaterialDefine.GetList()[i].ToString())); } YnBaseDal.YnPage ynPage = new YnBaseDal.YnPage(); List<AscmMaterialItem> listAscmMaterialItem = AscmMaterialItemService.GetInstance().GetList(ynPage, "", "", queryWord, queryType, queryStarDocnumber, queryEndDocnumber, zStatus, dStatus, wStatus, queryDescribe); if (listAscmMaterialItem != null && listAscmMaterialItem.Count > 0) { ImOrExMaterialDefine titleExportMaterial = new ImOrExMaterialDefine(); List<string> titleCols = ImOrExMaterialDefine.GetList(); titleExportMaterial.materialDocnumber = ImOrExMaterialDefine.DisplayText(titleCols[0]); titleExportMaterial.wipSupplyType = ImOrExMaterialDefine.DisplayText(titleCols[1]); titleExportMaterial.materialDescription = ImOrExMaterialDefine.DisplayText(titleCols[2]); titleExportMaterial.zMtlCategoryStatus = ImOrExMaterialDefine.DisplayText(titleCols[3]); titleExportMaterial.dMtlCategoryStatus = ImOrExMaterialDefine.DisplayText(titleCols[4]); titleExportMaterial.wMtlCategoryStatus = ImOrExMaterialDefine.DisplayText(titleCols[5]); titleExportMaterial.rowNumber = iRow++; list.Add(titleExportMaterial); foreach (AscmMaterialItem ascmMaterialItem in listAscmMaterialItem) { ImOrExMaterialDefine contentExportMaterial = new ImOrExMaterialDefine(); contentExportMaterial.materialDocnumber = ascmMaterialItem.docNumber; contentExportMaterial.wipSupplyType = AscmMaterialItem.WipSupplyTypeDefine.DisplayText(ascmMaterialItem.wipSupplyType); contentExportMaterial.materialDescription = ascmMaterialItem.description; contentExportMaterial.zMtlCategoryStatus = MtlCategoryStatusDefine.DisplayText(ascmMaterialItem.zMtlCategoryStatus); contentExportMaterial.dMtlCategoryStatus = MtlCategoryStatusDefine.DisplayText(ascmMaterialItem.dMtlCategoryStatus); contentExportMaterial.wMtlCategoryStatus = MtlCategoryStatusDefine.DisplayText(ascmMaterialItem.wMtlCategoryStatus); contentExportMaterial.rowNumber = iRow++; list.Add(contentExportMaterial); } } if (list != null && list.Count > 0) { foreach (ImOrExMaterialDefine ExportMaterial in list) { NPOI.SS.UserModel.IRow row = sheet.CreateRow(ExportMaterial.rowNumber); row.Height = 20 * 20; row.CreateCell(0).SetCellValue(ExportMaterial.materialDocnumber); row.CreateCell(1).SetCellValue(ExportMaterial.wipSupplyType); row.CreateCell(2).SetCellValue(ExportMaterial.materialDescription); row.CreateCell(3).SetCellValue(ExportMaterial.zMtlCategoryStatus); row.CreateCell(4).SetCellValue(ExportMaterial.dMtlCategoryStatus); row.CreateCell(5).SetCellValue(ExportMaterial.wMtlCategoryStatus); } } } catch (Exception ex) { throw ex; } byte[] buffer = new byte[] { }; using (System.IO.MemoryStream stream = new MemoryStream()) { wb.Write(stream); buffer = stream.GetBuffer(); } return File(buffer, "application/vnd.ms-excel", "备料形式维护.xls"); }