public void createMonthYearSummaryExcel()
{
ExcelWorksheet workSheet;
ExcelWorksheet workSheet2;
using (Stream AccountMovmentStream = GetAssemblyFile("Transactions.xlsx"))
{
workSheet = ExcelServices.GetExcelWorksheet(AccountMovmentStream, "Felles");
}
using (Stream SubCategoriesStream = GetAssemblyFile("Categories.xlsx"))
{
workSheet2 = ExcelServices.GetExcelWorksheet(SubCategoriesStream);
}
var subCategoriesjArray = JArray.Parse(ExcelServices.GetJsonFromTable(workSheet2));
var accountMovmentjArray = JArray.Parse(ExcelServices.GetJsonFromTable(workSheet));
List <SubCategory> categorisModel = ModelClassServices.GetSubCategoriesFromJarray(subCategoriesjArray);
IEnumerable <string> categoryList = categorisModel.Select(cat => cat.Category).Distinct();
List <AccountMovement> accountMovements = ModelClassServices.GetAccountMovmentsFromJarray(accountMovmentjArray);
var modementsViewModels = ModelClassServices.CreateMovementsViewModels(accountMovements, categorisModel, "Felles");
var excelPkg = new ExcelPackage();
try
{
//Add excel sheet
ExcelWorksheet wsSheet = excelPkg.Workbook.Worksheets.Add("MonthResume");
//Add Table Title
ExcelServices.AddSheetHeading(wsSheet, "TableName");
// Add "input" and "output" headet to Excel table
//ExcelServices.AddTableHeadings(wsSheet, new[] { "col1", "col2", "col3" }, subCategoriesjArray.Count+ accountMovmentjArray.Count);
//Add transactions to excel Sheet
ExcelServices.CreateExcelMonthSummaryTableFromMovementsViewModel(modementsViewModels, wsSheet, categoryList, 0, null, true);
//ExcelServices.CreateExcelTableFromMovementsViewModel(modementsViewModels, wsSheet, "TableName", categoryList);
}
catch (Exception e)
{
var noko = e.Message;
}
var filename = "MonthResumeTests";
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();
}
public void createMovementsExcel()
{
Stream SubCategoriesStream = GetAssemblyFile("Categories.xlsx");
Stream AccountMovmentStream = GetAssemblyFile("Transactions.xlsx");
ExcelWorksheet workSheet = ExcelServices.GetExcelWorksheet(AccountMovmentStream, "Felles");
ExcelWorksheet workSheet2 = ExcelServices.GetExcelWorksheet(SubCategoriesStream);
var subCategoriesjArray = JArray.Parse(ExcelServices.GetJsonFromTable(workSheet2));
var accountMovmentjArray = JArray.Parse(ExcelServices.GetJsonFromTable(workSheet));
List <SubCategory> subCategories = ModelClassServices.GetSubCategoriesFromJarray(subCategoriesjArray);
List <AccountMovement> accountMovements = ModelClassServices.GetAccountMovmentsFromJarray(accountMovmentjArray);
var modementsViewModels = ModelClassServices.CreateMovementsViewModels(accountMovements, subCategories, "Felles");
var excelPkg = new ExcelPackage();
try
{
ExcelWorksheet wsSheet = excelPkg.Workbook.Worksheets.Add("fellesResum");
//Add Table Title
new ExcelServices();
ExcelServices.AddSheetHeading(wsSheet, "TableName");
// Add "input" and "output" headet to Excel table
//ExcelServices.AddTableHeadings(wsSheet, new[] { "col1", "col2", "col3" }, subCategoriesjArray.Count+ accountMovmentjArray.Count);
//Add DMN Table to excel Sheet
ExcelServices.CreateExcelTableFromMovementsViewModel(modementsViewModels, wsSheet, "TableName");
}
catch (Exception e)
{
var noko = e.Message;
}
var filename = "MovementsTests";
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();
}
public void GetSubCategoryTest()
{
JArray JsonmodementsViewModels;
Encoding encoding = Encoding.GetEncoding(28591);
using (StreamReader stream = new StreamReader(GetAssemblyFile("TransactionViewModelArray.json"), encoding, true))
{
JsonmodementsViewModels = JArray.Parse(stream.ReadToEnd());
}
Stream SubCategoriesStream = GetAssemblyFile("Categories.xlsx");
Stream AccountMovmentStream = GetAssemblyFile("Transactions.xlsx");
ExcelWorksheet workSheet = ExcelServices.GetExcelWorksheet(AccountMovmentStream, "Felles");
ExcelWorksheet workSheet2 = ExcelServices.GetExcelWorksheet(SubCategoriesStream);
var subCategoriesjArray = JArray.Parse(ExcelServices.GetJsonFromTable(workSheet2));
var accountMovmentjArray = JArray.Parse(ExcelServices.GetJsonFromTable(workSheet));
List <SubCategory> subCategories = ModelClassServices.GetSubCategoriesFromJarray(subCategoriesjArray);
var Modelviews = new ModelViewBuilder().AddTextToMovemnt(new List <string> {
"arg tur", "arg tur", "argentina tur", "tur argentina", "argentina tur"
});
var movementsViewModels = new List <MovementsViewModel>();
foreach (var item in JsonmodementsViewModels)
{
movementsViewModels.Add(new ModelClassServices().JsonToMovementsViewModels(item));
}
List <MovementsViewModel> newList = new List <MovementsViewModel>();
foreach (var movment in Modelviews)
{
newList.Add(ModelClassServices.UpdateMovementViewModelWithSubCategory(subCategories, movment));
}
newList.All(mv => !string.IsNullOrEmpty(mv.Category)).Should().BeTrue();
}
public static void CreateExcelTableFromMovementsViewModel(List <MovementsViewModel> movementsModel, ExcelWorksheet wsSheet, string tableName)
{
//Get the list of Column that want to be created in the table
var excelColumns = MovementsViewModel.excelColumns;
// Calculate size of the table
var endRow = _startRow + movementsModel.Count + 1;
var endColum = _startColumn + excelColumns.Count;
// Create Excel table Header
CreateExcelTableHeader(wsSheet, tableName, excelColumns, _startRow, endRow, _startColumn, endColum);
// Set Excel table content
var tableStartColumn = _startColumn;
var row = _startRow + 1;
foreach (var movement in movementsModel)
{
// account Movements
foreach (var propertyName in excelColumns)
{
//Get Property name value
var propertyValue = ModelClassServices.GetPropertyValue(movement, propertyName);
//add value tu excel cell
wsSheet.Cells[row, tableStartColumn].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell(propertyName);
AddExcelCellValue(row, tableStartColumn, propertyValue, wsSheet);
tableStartColumn++;
}
tableStartColumn = _startColumn;
row++;
}
//var noko = dict2.Keys.Except(dict.Keys);
//var noko2 = dict.Keys.Except(dict2.Keys);
wsSheet.Cells[wsSheet.Dimension.Address].AutoFitColumns();
}
public void ParseJsonToModelClassTest()
{
var resourceFileNema = "SubCategories.json";
var resourcePath = $"ExcelClient.Tests.TestData.{resourceFileNema}";
var assembly = Assembly.GetExecutingAssembly();
Stream resourceAsStream = assembly.GetManifestResourceStream(resourcePath);
string json;
JArray jArray;
using (StreamReader r = new StreamReader(resourceAsStream))
{
json = r.ReadToEnd();
jArray = JArray.Parse(json);
}
foreach (var item in jArray)
{
var noko = new ModelClassServices().JsonToSubCategory(item);
}
json.Should().NotBeNull();
}
public static void CreateExcelMonthSummaryTableFromMovementsViewModel(List <MovementsViewModel> movementsModel, ExcelWorksheet wsSheet, IEnumerable <string> categories,
int sheetYear = 0, string sheetTableName = null, bool justExtrations = true)
{
int minYear;
int maxYear;
IEnumerable <string> TemExcelColumn;
if (sheetYear > 0)
{
minYear = sheetYear;
maxYear = sheetYear;
//add month ant total column to Ienumeration
TemExcelColumn = new[] { "Month", "Total" };
}
else
{
minYear = movementsModel.Min(mov => mov.DateTime.Year);
maxYear = movementsModel.Max(mov => mov.DateTime.Year);
//add month column to Ienumeration
TemExcelColumn = new[] { "Month" };
}
if (justExtrations)
{
categories = ModelClassServices.GetExtractionCategories(categories, movementsModel);
}
else
{
categories = ModelClassServices.GetIncomsCategories(categories, movementsModel);
}
categories = categories.OrderBy(c => c);
// and the new columns to the category
var newExcelColumn = TemExcelColumn.Concat(categories);
// Calculate size of the table
var endRow = _startRow + 12;
var endColum = _startColumn + newExcelColumn.Count();
// Create Excel table Header
int startRow = _startRow;
int startColumn = _startColumn;
//var row = _startRow + 1;
var row = _startRow;
for (int year = minYear; year <= maxYear; year++)
{
//give table Name
var tableName = sheetTableName ?? string.Concat("Table-", year);
// add Headers to table
CreateExcelTableHeader(wsSheet, tableName, newExcelColumn, startRow, endRow, _startColumn, endColum, true);
var tableStartColumn = _startColumn;
row++;
// Set Excel table content
for (int month = 1; month <= 12; month++)
{
var monthName = string.Concat(DateTimeFormatInfo.CurrentInfo.GetMonthName(month));
AddExcelCellValue(row, tableStartColumn, monthName, wsSheet);
if (sheetYear > 0)
{
//Get summ for category
double totalCategory = ModelClassServices.CategoriesMonthYearTotal(movementsModel, year, month, justExtrations);
AddExcelCellValue(row, tableStartColumn + 1, totalCategory, wsSheet);
}
foreach (var category in newExcelColumn)
{
if (category != "Month" && category != "Total")
{
//Get summ for category
double totalCategory = ModelClassServices.TotalforCategory(movementsModel, category, year, month, justExtrations);
wsSheet.Cells[row, tableStartColumn].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
//add value tu excel cell
AddExcelCellValue(row, tableStartColumn, totalCategory, wsSheet);
}
tableStartColumn++;
}
tableStartColumn = _startColumn;
row++;
}
row = row + 2;
startRow = row;
endRow = row + 12;
}
//var noko = dict2.Keys.Except(dict.Keys);
//var noko2 = dict.Keys.Except(dict2.Keys);
wsSheet.Cells[wsSheet.Dimension.Address].AutoFitColumns();
}
public static void CreateCategoriesAverage(List <MovementsViewModel> movementsModel, ExcelWorksheet wsSheet, IEnumerable <string> categories,
int year = 0, int month1 = 0, bool justExtrations = true)
{
if (justExtrations)
{
categories = ModelClassServices.GetExtractionCategories(categories, movementsModel);
}
else
{
categories = ModelClassServices.GetIncomsCategories(categories, movementsModel);
}
categories = categories.OrderBy(c => c);
var columns = new[] { "Type" };
var newAverageColumn = new[] { $"Year({year})", "Day" };
var newExcelColumn = columns.Concat(categories);
// Calculate size of the table
var endRow = _startRow + newAverageColumn.Count();
var endColum = _startColumn + newExcelColumn.Count();
// Create Excel table Header
int startRow = _startRow;
int startColumn = _startColumn;
var row = _startRow;
var tableStartColumn = _startColumn;
// Set Excel table content
//give table Name
var tableName = "CategoriesAverage";
// Add table Headers
CreateExcelTableHeader(wsSheet, tableName, newExcelColumn, startRow, endRow, _startColumn, endColum, true);
row++;
foreach (var item in newAverageColumn)
{
foreach (var category in newExcelColumn)
{
if (category == "Type")
{
AddExcelCellValue(row, tableStartColumn, item, wsSheet);
}
else
{
double categoryAverage = 0;
if (item == $"Year({year})")
{
categoryAverage = ModelClassServices.AverageforCategory(movementsModel, category, year, null, justExtrations);
}
if (item == $"Day")
{
categoryAverage = ModelClassServices.AverageforCategory(movementsModel, category, null, null, justExtrations);
}
wsSheet.Cells[row, tableStartColumn].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
AddExcelCellValue(row, tableStartColumn, categoryAverage, wsSheet);
}
tableStartColumn++;
}
tableStartColumn = _startColumn;
row++;
}
row = row + 3;
// Calculate size of the table
endRow = row + 12;
endColum = _startColumn + newExcelColumn.Count();
// Create Excel table Header
startRow = row;
startColumn = _startColumn;
tableStartColumn = _startColumn;
// Set Excel table content
//give table Name
tableName = "CategoriesMonthAverage";
// Add table Headers
CreateExcelTableHeader(wsSheet, tableName, newExcelColumn, startRow, endRow, _startColumn, endColum, true);
row++;
tableStartColumn = _startColumn;
for (int month = 1; month <= 12; month++)
{
var monthName = string.Concat(DateTimeFormatInfo.CurrentInfo.GetMonthName(month));
AddExcelCellValue(row, tableStartColumn, monthName, wsSheet);
foreach (var category in newExcelColumn)
{
double categoryAverage = 0;
if (category == "Type")
{
tableStartColumn++;
continue;
}
else
{
categoryAverage = ModelClassServices.AverageforCategory(movementsModel, category, null, month, justExtrations);
wsSheet.Cells[row, tableStartColumn].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
AddExcelCellValue(row, tableStartColumn, categoryAverage, wsSheet);
tableStartColumn++;
}
}
tableStartColumn = _startColumn;
row++;
}
}
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();
}
