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);
}
}