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