public ActionResult SaveAndReadFile() { Result <int> result = new Result <int>(); List <int> list = new List <int>(); try { List <Epm_TzSupMatManagement> supList = new List <Epm_TzSupMatManagement>(); HttpPostedFileBase file = Request.Files[0]; var fileName = file.FileName; //判断目录是否存在,不存在创建 if (!Directory.Exists(ImportOrExportPath)) { DirectoryInfo directoryInfo = new DirectoryInfo(ImportOrExportPath); directoryInfo.Create(); } //将文件重新命名,保证唯一性 string pathFile = ImportOrExportPath + "success_" + DateTime.Now.ToString("yyyyMMddHHmmss") + Path.GetExtension(fileName); //将上传的文件保存 file.SaveAs(pathFile); //方式一:创建数据表 DataTable dt = ExcelHelperNew.ExcelToTable(pathFile); for (int i = 0; i < dt.Rows.Count; i++) { //供应商名称、电话、联系人、地址 string supplierName = dt.Rows[i]["供应商名称*"].ToString(); if (string.IsNullOrEmpty(supplierName)) { list.Add(i); continue; } string supplierTel = dt.Rows[i]["供应商电话"].ToString(); string supplierContacts = dt.Rows[i]["供应商联系人"].ToString(); string supplierAddress = dt.Rows[i]["供应商地址*"].ToString(); if (string.IsNullOrEmpty(supplierAddress)) { list.Add(i); continue; } //甲供物资种类、规格、品名、单价 string materialCategory = dt.Rows[i]["物资种类*"].ToString(); if (string.IsNullOrEmpty(materialCategory)) { list.Add(i); continue; } string specification = dt.Rows[i]["规格*"].ToString(); if (string.IsNullOrEmpty(specification)) { list.Add(i); continue; } string productName = dt.Rows[i]["品名*"].ToString(); if (string.IsNullOrEmpty(productName)) { list.Add(i); continue; } string unitePrice = dt.Rows[i]["单价*"].ToString(); if (string.IsNullOrEmpty(unitePrice)) { list.Add(i); continue; } //数据转换model Epm_TzSupMatManagement model = new Epm_TzSupMatManagement(); model.SupplierName = supplierName; model.SupplierTel = supplierTel; model.SupplierContacts = supplierContacts; model.SupplierAddress = supplierAddress; model.MaterialCategory = materialCategory; model.Specification = specification; model.ProductName = productName; model.UnitePrice = unitePrice.ToDecimalReq(); using (ClientSiteClientProxy proxy = new ClientSiteClientProxy(ProxyEx(Request))) { //根据供应商名称查询对应供应商,获取ID Result <Base_Company> resultModel = proxy.GetCompanyByName(supplierName); //关联供应商ID if (resultModel.Flag == EResultFlag.Success && resultModel.Data != null) { var supplierId = resultModel.Data.Id; //批量做重复物资判断(同一供应商、同一物资、同一规格、同一品名的物资只能存在一条数据) var modelInfo = proxy.GetTzSupMatManagementModelBy(supplierId, model.MaterialCategory, model.ProductName, model.Specification); if (modelInfo.Flag == EResultFlag.Success && modelInfo.Data != null) { list.Add(i); continue; } else { model.SupplierId = supplierId; supList.Add(model); } } else { list.Add(i); continue; } } } if (supList.Count > 0) { using (ClientSiteClientProxy proxy = new ClientSiteClientProxy(ProxyEx(Request))) { var rows = proxy.AddRangeTzSupMatManagement(supList); result.Data = rows.Data; result.Flag = EResultFlag.Success; } } string errorPath = ""; //导入数据异常时,记录错误信息进行导出,方便客户下次修改,再次执行导入。 if (list.Count > 0) { //待导出文件路径,将文件重新命名,保证唯一性 errorPath = ImportOrExportPath + "failure_" + DateTime.Now.ToString("yyyyMMddHHmmss") + Path.GetExtension(fileName); //导出失败的Excel文档 ExcelHelperNew.TableToExcel(dt, errorPath, list); } return(Json(new { Data = result.Data, Flag = result.Flag, failureCount = list.Count, //导入失败条数 successCount = dt.Rows.Count - list.Count, //导入成功条数 download = errorPath, //失败文件下载链接 fileName = Path.GetFileName(errorPath) })); } catch (Exception ex) { result.Data = -1; result.Flag = EResultFlag.Failure; result.Exception = new ExceptionEx(ex, "导入程序异常:" + ex.Message); } return(Json(result.ToResultView())); }
public ActionResult ExportToExcel(string time = "", string name = "") { var pathUrl = ConfigurationManager.AppSettings["ImportOrExportPath"]; var suss = false; if (string.IsNullOrEmpty(time)) { return(Json(suss)); } WeeklyPathView weeklyPathView = new WeeklyPathView(); using (ClientSiteClientProxy proxy = new ClientSiteClientProxy(ProxyEx(Request))) { var query = proxy.GetProjectCountWeekly((int)ProjectType.All, time); var weeklyquery = proxy.GetProjectWeekly((int)ProjectType.All, time, (int)ProjectStateType.NewProjectCount, 1, 10000); if (query.Data != null && weeklyquery.Data != null) { #region 所有项目统计 var projectCount2 = query.Data.Where(t => t.Type == (int)ProjectType.NewAdd); //项目新建 var projectSum3 = query.Data; //汇总 var projectSum1 = weeklyquery.Data; //项目汇总详情 #endregion #region 新增项目统计表 var projectSum7 = weeklyquery.Data.Where(t => t.Type == (int)ProjectType.NewAdd && t.StateType == (int)ProjectStateType.NewProjectCount); //新增项目汇总 var projectSum2 = weeklyquery.Data.Where(t => t.Type == (int)ProjectType.NewAdd && t.StateType == (int)ProjectStateType.UnfinishedDesign); //未完成设计 var projectSum8 = weeklyquery.Data.Where(t => t.Type == (int)ProjectType.NewAdd && t.StateType == (int)ProjectStateType.CompletedNotOperational); //完工未投运 var projectSum4 = weeklyquery.Data.Where(t => t.Type == (int)ProjectType.NewAdd && t.StateType == (int)ProjectStateType.UnderConstruction); //正在施工 var projectSum5 = weeklyquery.Data.Where(t => t.Type == (int)ProjectType.NewAdd && t.StateType == (int)ProjectStateType.RetrofitProjectSummary); //未完成招标 var projectSum6 = weeklyquery.Data.Where(t => t.Type == (int)ProjectType.NewAdd && t.StateType == (int)ProjectStateType.Construction); //在建 #endregion #region 改造项目统计表 var projectCount = query.Data.Where(t => t.Type == (int)ProjectType.Modify); //改造项目汇总表 var projectSum11 = weeklyquery.Data.Where(t => t.Type == (int)ProjectType.Modify && t.StateType == (int)ProjectStateType.RetrofitProjectSummary); //改造项目汇总 var projectSum12 = weeklyquery.Data.Where(t => t.Type == (int)ProjectType.Modify && t.StateType == (int)ProjectStateType.NotStarted); //未开工 var projectSum13 = weeklyquery.Data.Where(t => t.Type == (int)ProjectType.Modify && t.StateType == (int)ProjectStateType.CompletedNotOperational); //完工未投运 var projectSum16 = weeklyquery.Data.Where(t => t.Type == (int)ProjectType.Modify && t.StateType == (int)ProjectStateType.Construction); //在建 var projectSum14 = weeklyquery.Data.Where(t => t.Type == (int)ProjectType.Modify && t.StateType == (int)ProjectStateType.UnderConstruction);; //正在施工 #endregion WeeklyView weeklyView = new WeeklyView(); weeklyView.projectCounts = projectSum3 == null ? new List <Epm_ProjectCountWeekly>(): projectSum3.ToList(); weeklyView.projectCounts1 = projectCount2 == null ? new List <Epm_ProjectCountWeekly>() : projectCount2.ToList(); weeklyView.projectCounts2 = projectCount2 == null ? new List <Epm_ProjectCountWeekly>() : projectCount2.ToList();; weeklyView.projectViews = projectSum1 == null ? new List <Epm_ProjectWeekly>() : projectSum1.ToList(); weeklyView.projectViews2 = projectSum2 == null ? new List <Epm_ProjectWeekly>() : projectSum2.ToList(); weeklyView.projectViews4 = projectSum4 == null ? new List <Epm_ProjectWeekly>() : projectSum4.ToList(); weeklyView.projectViews5 = projectSum5 == null ? new List <Epm_ProjectWeekly>() : projectSum5.ToList(); weeklyView.projectViews7 = projectSum7 == null ? new List <Epm_ProjectWeekly>() : projectSum7.ToList(); weeklyView.projectViews8 = projectSum8 == null ? new List <Epm_ProjectWeekly>() : projectSum8.ToList(); weeklyView.projectViews11 = projectSum11 == null ? new List <Epm_ProjectWeekly>() : projectSum11.ToList(); weeklyView.projectViews12 = projectSum12 == null ? new List <Epm_ProjectWeekly>() : projectSum12.ToList(); weeklyView.projectViews13 = projectSum13 == null ? new List <Epm_ProjectWeekly>() : projectSum13.ToList(); weeklyView.projectViews14 = projectSum14 == null ? new List <Epm_ProjectWeekly>() : projectSum14.ToList(); weeklyView.projectViews16 = projectSum16 == null ? new List <Epm_ProjectWeekly>() : projectSum16.ToList(); weeklyView.Title = time + "周报.xls"; suss = ExcelHelperNew.ExportForExecl(weeklyView); weeklyPathView.Title = time + "周报.xls"; weeklyPathView.Path = pathUrl + weeklyPathView.Title; weeklyPathView.suss = true; } else { weeklyPathView.suss = false; } } return(Json(weeklyPathView)); }