public void ComDeptContext_SmokeTest() { using (ComDeptContext context = new ComDeptContext()) { Fund fund = new Fund() { Company = new Company() { CompanyName = "TestCom", CompanyPreName = "TestCom1, TestCom2" }, Amount = 12.2M, Comment = "Test", DueAmount = 10, FundStatus = "通过", FundType = new FundType() { FundTypeName = "TestType" }, ProjectFields = new ProjectField[] { new ProjectField() { FieldName = "出口资金", FieldValue = "12" }, new ProjectField() { FieldName = "进口金额", FieldValue = "52" } }, Year = DateTime.Now }; context.Funds.Add(fund); context.SaveChanges(); } }
public Result ImportFunds(string excelFileName) { Result result = new Result(true); // file exists? if (!File.Exists(excelFileName)) { return(new Result(false, "Excel 文件不存在!")); } try { ExcelQueryFactory excel = new ExcelQueryFactory(excelFileName); List <Row> rows = (from a in excel.Worksheet("资金") select a).ToList(); if (rows.Count == 0) { return(new Result(false, "Excel 文件没有资金数据!")); } int countCompaniesImprted = 0; int countFundImported = 0; int countFundUpdated = 0; int countFundTypesImported = 0; using (ComDeptContext db = new ComDeptContext()) { string[] companyNames = db.Companies.Select(x => x.CompanyName.Trim()).ToArray(); IEnumerable <Company> companiesNeedAdd = rows.Where(x => !companyNames.Contains(x["企业名称"].ToString().Trim())) .Select(y => y["企业名称"].ToString().Trim()) .Distinct() .Select(z => new Company() { CompanyName = z }); db.Companies.AddRange(companiesNeedAdd); countCompaniesImprted = companiesNeedAdd.Count(); string[] fundTypeNames = db.FundTypes.Select(x => x.FundTypeName.Trim()).ToArray(); IEnumerable <FundType> fundTypesNeedAdd = rows.Where(x => !fundTypeNames.Contains(x["项目类别"].ToString().Trim())) .Select(y => y["项目类别"].ToString().Trim()) .Distinct() .Select(z => new FundType() { FundTypeName = z }); db.FundTypes.AddRange(fundTypesNeedAdd); countFundTypesImported = fundTypesNeedAdd.Count(); db.SaveChanges(); foreach (var row in rows) { string companyName = row["企业名称"].ToString().Trim(); Company company = db.Companies.SingleOrDefault(x => x.CompanyName.Trim() == companyName); string fundTypeName = row["项目类别"].ToString().Trim(); FundType fundType = db.FundTypes.SingleOrDefault(x => x.FundTypeName.Trim() == fundTypeName); Fund newFund = new Fund() { Amount = Convert.ToDecimal(row["申报金额"]), Comment = row["备注"].ToString(), ComGuid = company.ComGuid, Company = company, DueAmount = Convert.ToDecimal(row["尾款"]), FundStatus = row["申请状态"].ToString(), FundType = fundType, FundTypeGuid = fundType.FundTypeGuid, Year = Convert.ToDateTime(row["年度"].ToString() + "-1-1") }; IEnumerable <string> projectColumns = row.ColumnNames.Where(x => x.StartsWith("project_")); newFund.ProjectFields = new List <ProjectField>(); foreach (string colName in projectColumns) { newFund.ProjectFields.Add(new ProjectField() { FieldName = colName.Replace("project_", ""), FieldValue = row[colName].ToString() }); } db.Funds.Attach(newFund); if (db.Funds.SingleOrDefault(x => x.Year == newFund.Year && x.FundType.FundTypeGuid == newFund.FundType.FundTypeGuid && x.Company.ComGuid == newFund.Company.ComGuid) != null) { db.Entry(newFund).State = EntityState.Modified; countFundUpdated++; } else { db.Entry(newFund).State = EntityState.Added; countFundImported++; } } db.SaveChanges(); } result = new Result <Dictionary <string, string> >(true, new Dictionary <string, string>() { { "专项资金数据导入数据", countFundImported.ToString() }, { "专项资金数据更新数据", countFundUpdated.ToString() }, { "企业数据", countCompaniesImprted.ToString() }, { "项目类别", countFundTypesImported.ToString() } }); } catch (Exception ex) { result = new Result <Exception>(false, ex, ex.Message); } return(result); }