public void CreteCashFlowFromMovements() { var MovementsExcelPkg = new ExcelPackage(GetAssemblyFile("Movements.xlsx")); var WoorSheet = MovementsExcelPkg.Workbook.Worksheets.FirstOrDefault(); var jsonFromTable = ExcelServices.GetJsonFromTable(WoorSheet); MovementsExcelPkg.Dispose(); List <MovementsViewModel> movementsViewModels = JsonConvert.DeserializeObject <List <MovementsViewModel> >(jsonFromTable, JsonServices.GetJsonSerializerSettings()); var categoryList = ModelClassServices.GetListOfCategories(movementsViewModels); //var movements = JsonConvert.DeserializeObject<List<MovementsViewModel>>(jsonFromTable, dateTimeConverter); var excelPkg = new ExcelPackage(GetAssemblyFile("Budget Cashflow.xlsx")); try { var ExpensesWSheet = excelPkg.Workbook.Worksheets["Expenses details"]; var year = 2019; //workSheet.Tables.Delete("YearExpenses"); // add year expenses categoiers ExcelServices.CreateYearExpensesTable(movementsViewModels, categoryList, year, ExpensesWSheet, "YearExpenses", "B38"); // add year incoms categoiers ExcelServices.CreateYearIncomsTable(movementsViewModels, categoryList, year, ExpensesWSheet, "YearIncoms", "B54"); // update Year table //Get Adress to budget table var categoryListWithTotals = Helpers.AddItemsToIenumeration(categoryList, new List <string>() { "Sub Total", "Total" }); var CategoriesAddressWithTotals = ExcelServices.GetColumnsNameAdress(categoryListWithTotals, ExpensesWSheet, "Year_budget"); //Get address to expenses table var CategoriesAddress = ExcelServices.GetColumnsNameAdress(categoryListWithTotals, ExpensesWSheet, "YearExpenses"); //Update year excel table var yearWSheet = excelPkg.Workbook.Worksheets["Year summary"]; ExcelServices.UpdateYearTableValues(CategoriesAddressWithTotals, year, yearWSheet, "tblOperatingExpenses", "BUDGET", "Total"); ExcelServices.UpdateYearTableValues(CategoriesAddress, year, yearWSheet, "tblOperatingExpenses", "ACTUAL", "Total"); // get address to Month budget table var categoriesWithoutIncome = Helpers.DeleteItemsfromIenumeration(categoryList, new List <string>() { "Åse", "Matias" }); var monthBudgetCategoriesAddress = ExcelServices.GetColumnsNameAdress(categoriesWithoutIncome, ExpensesWSheet, "Year_budget"); var monthExpensesCategoriesAddress = ExcelServices.GetColumnsNameAdress(categoriesWithoutIncome, ExpensesWSheet, "YearExpenses"); //update month Table with the categories summary var monthWSheet = excelPkg.Workbook.Worksheets["Monthly summary"]; ExcelServices.UpdateClassesTableValues(monthBudgetCategoriesAddress, monthExpensesCategoriesAddress, year, monthWSheet, "tblOperatingExpenses7"); var categoriesAverageWSheet = excelPkg.Workbook.Worksheets.Add("Categories Average"); new ExcelServices(); ExcelServices.CreateCategoriesAverage(movementsViewModels, categoriesAverageWSheet, categoryList, year, 0, true); } catch (Exception e) { var noko = e.Message; } var filename = "Budget Cashflow With Average 2019"; var path = string.Concat(@"h:\temp\"); Directory.CreateDirectory(path); var filePath = Path.Combine(path, string.Concat(filename, ".xlsx")); excelPkg?.SaveAs(new FileInfo(filePath)); excelPkg.Dispose(); File.Exists(filePath).Should().BeTrue(); }
public void UpdateClassesTableValues() { var streamFile = TestsHelper.GetAssemblyFile("Transactions Update With Categories.xlsx"); using (var cashflowExcelPkg = new ExcelPackage(streamFile)) { IEnumerable <string> categoryList = TestsHelper.GetCategoryList(); var expensesWSheet = cashflowExcelPkg.Workbook.Worksheets["Transactions"]; var transactions = expensesWSheet.Tables.FirstOrDefault(); var jsonArray = ExcelConverter.GetJsonFromTable(transactions); var categoriesAverageWorkSheet = cashflowExcelPkg.Workbook.Worksheets["Categories Average"]; var categoriesAverageWorkSheet1 = cashflowExcelPkg.Workbook.Worksheets["Categories Average1"]; if (categoriesAverageWorkSheet != null) { } jsonArray.Count.Should().Be(193); var noko = jsonArray.ToObject <List <MovementsViewModel> >(); List <MovementsViewModel> movementsViewModels = JsonConvert.DeserializeObject <List <MovementsViewModel> >(jsonArray.ToString(), JsonServices.GetJsonSerializerSettings()); movementsViewModels.Count.Should().Be(193); } }