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();
}
public async Task <IActionResult> Post(IFormFile transactions, IFormFile categories, int year = 0)
{
var filePathTemp = Path.GetTempFileName();
var filePath1 = Path.GetTempFileName();
var responseDictionary = new Dictionary <string, string>();
if (!IsFileValid(transactions) || !IsFileValid(categories))
{
return(BadRequest());
}
ExcelWorksheet transactionsWorkSheet;
ExcelWorksheet categoriesWorkSheet;
//Read Excel Files
try
{
transactionsWorkSheet = await ExcelHelpers.GetExcelWorkSheet(transactions, filePathTemp);
categoriesWorkSheet = await ExcelHelpers.GetExcelWorkSheet(categories, filePath1);
}
catch (Exception ex)
{
return(BadRequest("Can't read excel files"));
}
var transactionsTable = transactionsWorkSheet.Tables.FirstOrDefault();
var categoriestabTable = categoriesWorkSheet.Tables.FirstOrDefault();
//Get excel data in Json format easier to serialize to class
var accountMovmentjArray = ExcelConverter.GetJsonFromTable(transactionsTable);
var subCategoriesjArray = ExcelConverter.GetJsonFromTable(categoriestabTable);
// serialize Json to Class
List <AccountMovement> accountMovements = ModelConverter.GetAccountMovmentsFromJarray(accountMovmentjArray);
List <SubCategory> categorisModel = ModelConverter.GetCategoriesFromJarray(subCategoriesjArray);
IEnumerable <string> categoryList = categorisModel.Select(cat => cat.Category).Distinct();
//TODO Get acount Name from Excel or Input variable
var movementsViewModels = ModelConverter.CreateMovementsViewModels(accountMovements, categorisModel, "Felles");
//Create excel Sheet with the transaction updated with the keewords, categories, and subproject (is exists)
var categoriesArray = categoryList as string[] ?? categoryList.ToArray();
using (var stream = new MemoryStream())
using (var transactionUpdatePackage = new ExcelPackage(stream))
{
try
{
//Add Table Title
var transactionSheet = transactionUpdatePackage.Workbook.Worksheets.Add("Transactions");
ExcelServices.AddSheetHeading(transactionSheet, "Transactions and Categories");
//Add transactions to excel Sheet
var movementsViewExcelTable = ExcelServices.CreateExcelTableFromMovementsViewModel(movementsViewModels, transactionSheet, "Transactions");
}
catch (Exception e)
{
return(BadRequest("Error Creating transaction sheet. Error message : " + e.Message));
}
// Add Categories Average to excel
try
{
AddCategoriesAverage(year, transactionUpdatePackage, movementsViewModels, categoriesArray);
}
catch (Exception e)
{
return(BadRequest(" Error Creating Average sheet. Error message : " + e.Message));
}
// add month summaries to excel
try
{
var monthSummariesSheet = transactionUpdatePackage.Workbook.Worksheets.Add("MonthSummaries");
ExcelServices.CreateExcelMonthSummaryTableFromMovementsViewModel(monthSummariesSheet, movementsViewModels, categoriesArray, 0, null, true);
}
catch (Exception e)
{
return(BadRequest("Creating MonthSummary Sheet. Error message : " + e.Message));
}
try
{
var filename = "Transactions Update With Categories";
string contentRootPath = _hostingEnvironment.ContentRootPath;
var fullPath = Path.Combine(contentRootPath, "DataTemp", $"{filename}.xlsx");
transactionUpdatePackage.SaveAs(new FileInfo(fullPath));
}
catch (Exception e)
{
return(BadRequest("Transactions Update With Categories Can't be saved :" + e.Message));
}
}
//Next Excel File More details and cashflow + Chars
using (var cashflowExcelPkg = new ExcelPackage(GetAssemblyFile("Budget Cashflow.xlsx")))
{
try
{
UpdateBudgetCashFlow(cashflowExcelPkg, movementsViewModels, categoriesArray.ToList(), year);
}
catch (Exception)
{
return(BadRequest("Cant creat Cashflow Excel File"));
}
// Save Excel Package
try
{
var filename = $"Budget Cashflow ({year})";
string contentRootPath = _hostingEnvironment.ContentRootPath;
var fullPath = Path.Combine(contentRootPath, "DataTemp", $"{filename}.xlsx");
cashflowExcelPkg.SaveAs(new FileInfo(fullPath));
}
catch
{
return(BadRequest("Can't be saved"));
}
}
return(Ok(responseDictionary));
}
public async Task <IActionResult> PostTransaction(IFormFile transactions, int year = 0)
{
var filePathTemp = Path.GetTempFileName();
if (!IsFileValid(transactions))
{
return(BadRequest("Can't read transactions excel file"));
}
using (var stream = new FileStream(filePathTemp, FileMode.Create))
{
await transactions.CopyToAsync(stream);
List <MovementsViewModel> movementsViewModels = null;
List <string> categoryList;
using (var transacationAndCategories = new ExcelPackage(stream))
{
JArray jsonFromTable = null;
var worksheet = transacationAndCategories.Workbook.Worksheets.FirstOrDefault();
if (worksheet != null)
{
var excelTable = worksheet.Tables.FirstOrDefault();
jsonFromTable = ExcelConverter.GetJsonFromTable(excelTable);
}
if (jsonFromTable != null)
{
movementsViewModels = jsonFromTable.ToObject <List <MovementsViewModel> >();
//movementsViewModels = JsonConvert.DeserializeObject<List<MovementsViewModel>>(jsonFromTable?.ToString(), JsonServices.GetJsonSerializerSettings());
}
categoryList = ModelConverter.GetListOfCategories(movementsViewModels);
// Add Categories Average to excel
try
{
AddCategoriesAverage(year, transacationAndCategories, movementsViewModels, categoryList.ToArray());
}
catch (Exception e)
{
return(BadRequest(" Error Creating Average sheet. Error message : " + e.Message));
}
// add month summaries to excel
try
{
ExcelWorksheet monthSummariesSheet = transacationAndCategories.Workbook.Worksheets["MonthSummaries"];
if (monthSummariesSheet != null)
{
transacationAndCategories.Workbook.Worksheets.Delete("MonthSummaries");
monthSummariesSheet = transacationAndCategories.Workbook.Worksheets.Add("Month Summaries New");
}
else
{
monthSummariesSheet = transacationAndCategories.Workbook.Worksheets.Add("MonthSummaries");
}
ExcelServices.CreateExcelMonthSummaryTableFromMovementsViewModel(monthSummariesSheet, movementsViewModels, categoryList.ToArray(), 0, null, true);
}
catch (Exception e)
{
return(BadRequest("Creating MonthSummary Sheet. Error message : " + e.Message));
}
try
{
var filename = "Transactions Update With Categories (1)";
string contentRootPath = _hostingEnvironment.ContentRootPath;
var fullPath = Path.Combine(contentRootPath, "DataTemp", $"{filename}.xlsx");
transacationAndCategories.SaveAs(new FileInfo(fullPath));
}
catch (Exception e)
{
return(BadRequest("Transactions Update With Categories Can't be saved :" + e.Message));
}
}
using (var cashflowExcelPkg = new ExcelPackage(GetAssemblyFile("Budget Cashflow.xlsx")))
{
try
{
UpdateBudgetCashFlow(cashflowExcelPkg, movementsViewModels, categoryList, year);
}
catch (Exception)
{
return(BadRequest("Cant creat Cashflow Excel File"));
}
// Save Excel Package
try
{
var filename = "Budget Cashflow (1)";
string contentRootPath = _hostingEnvironment.ContentRootPath;
var fullPath = Path.Combine(contentRootPath, "DataTemp", $"{filename}.xlsx");
cashflowExcelPkg.SaveAs(new FileInfo(fullPath));
return(Ok("File " + filename + "cretated in :" + fullPath));
}
catch (Exception e)
{
return(BadRequest("Can't be saved :" + e.Message));
}
}
}
}