public void UpdateClassesTableValues() { var streamFile = TestsHelper.GetAssemblyFile("Budget Cashflow.xlsx"); using (var cashflowExcelPkg = new ExcelPackage(streamFile)) { IEnumerable <string> categoryList = TestsHelper.GetCategoryList(); var expensesWSheet = cashflowExcelPkg.Workbook.Worksheets["Expenses details"]; var yearBudgetTable = expensesWSheet.Tables["Year_budget"]; var yearExpensesTable = expensesWSheet.Tables["Table_2018"]; var monthWSheet = cashflowExcelPkg.Workbook.Worksheets["Monthly summary"]; var tblOperatingExpenses7Table = monthWSheet.Tables["tblOperatingExpenses7"]; Dictionary <string, string> monthBudgetCategoriesAddress = null; Dictionary <string, string> monthExpensesCategoriesAddress = null; // get address to Month budget table var categoriesWithoutIncome = Helpers.DeleteItemsfromIenumeration(categoryList, new List <string>() { "Åse", "Matias" }); monthBudgetCategoriesAddress = ExcelServices.GetColumnsNameAdress(categoriesWithoutIncome, yearBudgetTable); monthExpensesCategoriesAddress = ExcelServices.GetColumnsNameAdress(categoriesWithoutIncome, yearExpensesTable); ExcelServices.UpdateClassesTableValues(monthBudgetCategoriesAddress, monthExpensesCategoriesAddress, tblOperatingExpenses7Table); var saveExcel = TestsHelper.SaveExcrlPackage(cashflowExcelPkg, "UpdateClassesTableValues-Test"); saveExcel.Should().BeTrue(); } }
public void UpdateTableValuesActualTest() { Dictionary <string, string> CategoriesAddress = null; var streamFile = TestsHelper.GetAssemblyFile("Budget Cashflow.xlsx"); using (var cashflowExcelPkg = new ExcelPackage(streamFile)) { IEnumerable <string> categoryList = TestsHelper.GetCategoryList(); var categoryListWithTotals = Helpers.AddItemsToIenumeration(categoryList, new List <string>() { "Sub Total", "Total" }); var ExpensesWSheet = cashflowExcelPkg.Workbook.Worksheets["Expenses details"]; var yearBudgetTable = ExpensesWSheet.Tables["Table_2018"]; if (yearBudgetTable != null) { CategoriesAddress = ExcelHelpers.GetNamesAdress(categoryListWithTotals, yearBudgetTable); } var yearWSheet = cashflowExcelPkg.Workbook.Worksheets["Year summary"]; var excelTable = yearWSheet.Tables["tblOperatingExpenses"]; string keyCellValue = null; if (CategoriesAddress != null) { if (CategoriesAddress.TryGetValue("Total", out keyCellValue)) { ExcelServices.UpdateTableValues(excelTable, "ACTUAL", keyCellValue); } } var saveExcel = TestsHelper.SaveExcrlPackage(cashflowExcelPkg, "Update-Test2"); saveExcel.Should().BeTrue(); } }
public void CreateExcelMonthSummaryTableFromMovementsViewModelIncomeTest() { IEnumerable <string> categoryList = TestsHelper.GetCategoryList(); var movementsViewModels = TestsHelper.GetMovementsViewModels(); using (var stream = new MemoryStream()) using (var package = new ExcelPackage(stream)) { package.Workbook.Worksheets.Add("Sheet1"); var categoriesAverageWSheet = package.Workbook.Worksheets["Sheet1"]; var excelTable = ExcelServices.CreateCategoriesMonthsAveragetest(categoriesAverageWSheet, 13, movementsViewModels, categoryList, 2019, false); var table = package.Workbook.Worksheets.FirstOrDefault().Tables.FirstOrDefault(); var start = table.Address.Start.Address; var end = table.Address.End.Address; var noko = table.WorkSheet.Cells["B6"].Value = ""; //now test that it generated properly, such as: //var sheeDefault = package.Workbook.Worksheets.FirstOrDefault(); //if (sheeDefault != null) sheeDefault.Cells["D57"].Value.Should().Be(-55); var saveExcel = TestsHelper.SaveExcrlPackage(package, "Incomes-test"); saveExcel.Should().BeTrue(); } }
public void GetNamesAdressTest() { var streamFile = TestsHelper.GetAssemblyFile("Budget Cashflow.xlsx"); using (var cashflowExcelPkg = new ExcelPackage(streamFile)) { var expensesWSheet = cashflowExcelPkg.Workbook.Worksheets["Expenses details"]; var table = expensesWSheet.Tables["Year_budget"]; var noko = ExcelHelpers.GetNamesAdress(TestsHelper.GetCategoryList(), table); noko["Familly"].Should().Be("I22"); } }
public void CreateAverageForYearMonthDayTest() { IEnumerable <string> categoryList = TestsHelper.GetCategoryList(); var movementsViewModels = TestsHelper.GetMovementsViewModels(); using (var stream = new MemoryStream()) using (var package = new ExcelPackage(stream)) { package.Workbook.Worksheets.Add("Sheet1"); var categoriesAverageWSheet = package.Workbook.Worksheets["Sheet1"]; ExcelServices.CreateAverageForYearMonthDay(movementsViewModels, categoriesAverageWSheet, categoryList, 0, 0, true); //now test that it generated properly, such as: //var sheeDefault = package.Workbook.Worksheets.FirstOrDefault(); //if (sheeDefault != null) sheeDefault.Cells["D57"].Value.Should().Be(-55); var saveExcel = TestsHelper.SaveExcrlPackage(package, "CreateAverageForYearMonthDay-test"); saveExcel.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); } }