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