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 GetIndexFromColumnNameTest() { 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.GetAdressFromColumnName(table, "Bil"); } }
public static JArray GetJonsArrayFromFile(string fileName) { var encoding = Encoding.GetEncoding(28591); JArray jsonArray; using (StreamReader stream = new StreamReader(TestsHelper.GetAssemblyFile(fileName), encoding, true)) { jsonArray = JArray.Parse(stream.ReadToEnd()); } return(jsonArray); }
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 Test2() { //Get MovementsModel JArray JsonmodementsViewModels; Encoding encoding = Encoding.GetEncoding(28591); using (StreamReader stream = new StreamReader(TestsHelper.GetAssemblyFile("TransactionViewModelArray.json"), encoding, true)) { JsonmodementsViewModels = JArray.Parse(stream.ReadToEnd()); } var movementsViewModels = new List <MovementsViewModel>(); foreach (var item in JsonmodementsViewModels) { movementsViewModels.Add(new ModelConverter().JsonToMovementsViewModels(item)); } // Get Categories JArray JsonCategoryList; using (StreamReader stream = new StreamReader(TestsHelper.GetAssemblyFile("CategoriesArray.json"), encoding, true)) { JsonCategoryList = JArray.Parse(stream.ReadToEnd()); } List <string> categoryListTemp = new List <string>(); foreach (var item in JsonCategoryList) { categoryListTemp.Add(item.ToString()); } IEnumerable <string> categoryList = categoryListTemp; var noko = ModelOperation.AverageforCategory(movementsViewModels, "Mat", null, 6, true); var noko1 = ModelOperation.AverageforCategory(movementsViewModels, "Mat", 2018, null, true); var noko2 = ModelOperation.AverageforCategory(movementsViewModels, "Mat", 2018, 6, true); noko.Should().BeGreaterThan(0); }
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); } }
public void Test1() { ExcelWorksheet workSheet; ExcelWorksheet workSheet2; using (Stream AccountMovmentStream = TestsHelper.GetAssemblyFile("Transactions.xlsx")) { workSheet = ExcelHelpers.GetExcelWorksheet(AccountMovmentStream, "Felles"); } using (Stream SubCategoriesStream = TestsHelper.GetAssemblyFile("Categories.xlsx")) { workSheet2 = ExcelHelpers.GetExcelWorksheet(SubCategoriesStream); } var workSheet2Table = workSheet2.Tables.FirstOrDefault(); var workSheetTable = workSheet.Tables.FirstOrDefault(); var subCategoriesjArray = ExcelConverter.GetJsonFromTable(workSheet2Table); var accountMovmentjArray = ExcelConverter.GetJsonFromTable(workSheetTable); List <SubCategory> categorisModel = ModelConverter.GetCategoriesFromJarray(subCategoriesjArray); IEnumerable <string> categoryList = categorisModel.Select(cat => cat.Category).Distinct(); List <AccountMovement> accountMovements = ModelConverter.GetAccountMovmentsFromJarray(accountMovmentjArray); var modementsViewModels = ModelConverter.CreateMovementsViewModels(accountMovements, categorisModel, "Felles"); var jsonModementsViewModels = JArray.Parse(JsonConvert.SerializeObject(modementsViewModels)); jsonModementsViewModels.Should().NotBeNullOrEmpty(); // var filename = "Budget Cashflow Temp"; //var path = string.Concat(@"h:\temp\"); //Directory.CreateDirectory(path); //var filePath = Path.Combine(path, string.Concat(filename, ".xlsx")); ////excelPkg?.SaveAs(new FileInfo(filePath)); //File.Exists(filePath).Should().BeTrue(); }