/// <summary>
///
/// </summary>
/// <param name="movementsModel"></param>
/// <param name="wsSheet"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static ExcelTable 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 endTableCellAdress = ExcelHelpers.AddRowAndColumnToCellAddress(_startCell, movementsModel.Count, excelColumns.Count - 1);
// Create Excel table Header
var excelTable = CreateExcelTable(wsSheet, tableName, excelColumns, _startCell, endTableCellAdress);
for (int row = 0; row < movementsModel.Count; row++)
{
for (int column = 0; column < excelColumns.Count; column++)
{
//Get Property name value
var propertyValue = ModelConverter.GetPropertyValue(movementsModel[row], excelColumns[column]);
string tableCellAdress = ExcelHelpers.AddRowAndColumnToCellAddress(_startCell, row + 1, column);
excelTable.WorkSheet.Cells[tableCellAdress].Value = propertyValue;
}
}
excelTable.WorkSheet.Cells[wsSheet.Dimension.Address].AutoFitColumns();
return(excelTable);
}
/// <summary>
///
/// </summary>
/// <param name="wsSheet"></param>
/// <param name="excelPackage"></param>
/// <param name="movementsModel"></param>
/// <param name="categories"></param>
/// <param name="sheetYear"></param>
/// <param name="sheetTableName"></param>
/// <param name="justExtrations"></param>
/// <param name="startCell"></param>
/// <returns></returns>
public static List <ExcelTable> CreateExcelMonthSummaryTableFromMovementsViewModel(ExcelWorksheet wsSheet, List <MovementsViewModel> movementsModel, IEnumerable <string> categories,
int sheetYear = 0, string sheetTableName = null, bool justExtrations = true, string startCell = null)
{
int minYear;
int maxYear;
if (sheetYear > 0)
{
minYear = sheetYear;
maxYear = sheetYear;
}
else
{
minYear = movementsModel.Min(mov => mov.DateTime.Year);
maxYear = movementsModel.Max(mov => mov.DateTime.Year);
}
IEnumerable <string> newColumns = new[] { "Month", "Total" };
categories = justExtrations ? ModelOperation.GetExtractionCategories(categories, movementsModel) : ModelOperation.GetIncomsCategories(categories, movementsModel);
categories = categories.OrderBy(c => c);
// and the new columns to the category
categories = newColumns.Concat(categories);
var categoriesUpdated = categories as string[] ?? categories.ToArray();
string startTableCell = startCell ?? _startCell;
// Create Excel table Header
var endTableCellAddress = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, 12, categoriesUpdated.Count() - 1);
var tableName = sheetTableName ?? "Tanble-";
List <ExcelTable> excelTables = new List <ExcelTable>();
for (int year = minYear; year <= maxYear; year++)
{
//give table Name
tableName = string.Concat(tableName, year);
//calculate Table sizes
endTableCellAddress = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, 12, categoriesUpdated.Count() - 1);
var excelTable = CreateExcelTable(wsSheet, tableName, categoriesUpdated, startTableCell, endTableCellAddress, true);
// Set Excel table content
for (int month = 1; month <= 12; month++)
{
for (int i = 0; i < categoriesUpdated.Length; i++)
{
switch (categoriesUpdated[i])
{
case "Month":
var monthName = string.Concat(DateTimeFormatInfo.CurrentInfo.GetMonthName(month));
excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, month, i)].Value = monthName;
break;
case "Total":
double totalCategory = ModelConverter.CategoriesMonthYearTotal(movementsModel, year, month, justExtrations);
excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, month, i)].Value = totalCategory;
break;
default:
//Get summ for category
var tablecell = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, month, i);
double totalCategory1 = ModelOperation.GetTotalforCategory(movementsModel, categoriesUpdated[i], year, month, justExtrations);
excelTable.WorkSheet.Cells[tablecell].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
//add value tu excel cell
wsSheet.Cells[tablecell].Value = totalCategory1;
//AddExcelCellValue(row, tableStartColumn, totalCategory1, wsSheet);
break;
}
}
}
startTableCell = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, 12 + 5, 0);
excelTable.WorkSheet.Cells[wsSheet.Dimension.Address].AutoFitColumns();
excelTables.Add(excelTable);
}
return(excelTables);
}
//
public static void UpdateBudgetCashFlow(ExcelPackage excelPackage, List <MovementsViewModel> movementsViewModels, List <string> categoriesArray, int year)
{
ExcelTable yearBudgetTable = null;
ExcelTable yearExpensesTable = null;
if (year == 0)
{
year = DateTime.Today.Year;
}
// Create Cashflow
var expensesWSheet = excelPackage.Workbook.Worksheets["Expenses details"];
// add year categoiers Table
try
{
var yearExpensesTables = ExcelServices.CreateExcelMonthSummaryTableFromMovementsViewModel(expensesWSheet, movementsViewModels, categoriesArray, year, "YearExpenses", true, "B38");
yearExpensesTable = yearExpensesTables.FirstOrDefault();
}
catch (Exception e)
{
throw new Exception("Creating Year expensesTable Sheet. Error message : " + e.Message);
}
// add year incoms categoiers
try
{
var yearIncomsTables = ExcelServices.CreateExcelMonthSummaryTableFromMovementsViewModel(expensesWSheet, movementsViewModels, categoriesArray, year, "YearIncoms", false, "B54");
}
catch (Exception e)
{
throw new Exception("Problem Creating Year IncomsTable Sheet. Error message : " + e.Message);
}
// update Year table
Dictionary <string, string> categoriesAddressWithTotals = null;
Dictionary <string, string> categoriesAddress = null;
//Add sub total and total to list to get them from budget table
try
{
var categoryListWithTotals = Helpers.AddItemsToIenumeration(categoriesArray, new List <string>()
{
"Sub Total", "Total"
});
yearBudgetTable = expensesWSheet.Tables["Year_budget"];
var listWithTotals = categoryListWithTotals as string[] ?? categoryListWithTotals.ToArray();
if (yearBudgetTable != null)
{
categoriesAddressWithTotals = ExcelHelpers.GetNamesAdress(listWithTotals, yearBudgetTable);
}
//Get address to expenses table
categoriesAddress = ExcelServices.GetColumnsNameAdress(listWithTotals, yearExpensesTable);
}
catch (Exception e)
{
throw new Exception("Cant get Info from table from 'Expenses details' sheet. Error message : " + e.Message);
}
//Update year excel table
try
{
var yearWSheet = excelPackage.Workbook.Worksheets["Year summary"];
var tblOperatingExpensesTable = yearWSheet.Tables["tblOperatingExpenses"];
string keyCellValue = null;
if (categoriesAddressWithTotals != null)
{
if (categoriesAddressWithTotals.TryGetValue("Total", out keyCellValue))
{
ExcelServices.UpdateTableValues(tblOperatingExpensesTable, "BUDGET", keyCellValue);
}
}
if (categoriesAddress != null)
{
if (categoriesAddress.TryGetValue("Total", out keyCellValue))
{
ExcelServices.UpdateTableValues(tblOperatingExpensesTable, "ACTUAL", keyCellValue);
}
}
}
catch (Exception e)
{
throw new Exception("Cant tables in 'Year summary' Table values. Error message : " + e.Message);
}
Dictionary <string, string> monthBudgetCategoriesAddress = null;
Dictionary <string, string> monthExpensesCategoriesAddress = null;
try
{
// get address to Month budget table
var categoriesWithoutIncome = Helpers.DeleteItemsfromIenumeration(categoriesArray, new List <string>()
{
"Åse", "Matias"
});
monthBudgetCategoriesAddress = ExcelServices.GetColumnsNameAdress(categoriesWithoutIncome, yearBudgetTable);
monthExpensesCategoriesAddress = ExcelServices.GetColumnsNameAdress(categoriesWithoutIncome, yearExpensesTable);
}
catch (Exception e)
{
throw new Exception("Cant get tables info from 'Expenses details' sheet to update Class table. Error message : " + e.Message);
}
//update month Table with the categories summary
try
{
var monthWSheet = excelPackage.Workbook.Worksheets["Monthly summary"];
var tblOperatingExpenses7Table = monthWSheet.Tables["tblOperatingExpenses7"];
ExcelServices.UpdateClassesTableValues(monthBudgetCategoriesAddress, monthExpensesCategoriesAddress, tblOperatingExpenses7Table);
}
catch (Exception e)
{
throw new Exception("Cant update tblOperatingExpenses7 in 'Monthly summary' sheet. Error message : " + e.Message);
}
//return excelPackage;
}
public static void UpdateClassesTableValues(Dictionary <string, string> BudgetCategoriesAddressdictionary, Dictionary <string, string> ExpenseCategoriesAddressdictionary, ExcelTable excelTable)
{
//TODO check cell Value
var date = (double)excelTable.WorkSheet.Cells["G1"].Value;
var monthToFilter = DateTime.FromOADate(date).Month;
// Get cell address
var operatingAdress = ExcelHelpers.GetAdressFromColumnName(excelTable, "OPERATING");
var budgetAdress = ExcelHelpers.GetAdressFromColumnName(excelTable, "BUDGET");
var actualAdress = ExcelHelpers.GetAdressFromColumnName(excelTable, "ACTUAL");
var categories = BudgetCategoriesAddressdictionary.Where(ct => ExpenseCategoriesAddressdictionary.ContainsKey(ct.Key)).Select(ct => ct.Key).ToList();
var tableElement = excelTable.TableXml.DocumentElement;
//tableElement.Attributes["ref"].Value = rng.Address;
var ref1 = tableElement.Attributes["ref"].Value;
var columnNode = tableElement["tableColumns"];
//columnNode.Attributes["count"].Value = rng.End.Column.ToString();
var count1 = columnNode.Attributes["count"].Value;
for (int row = 0; row < categories.Count; row++)
{
//TODO Update formula =HLOOKUP([@OPERATING];'Expenses details'!$E$22:$AC$34;MONTH($G$1)+1;FALSE)
string budgetCellAdress = BudgetCategoriesAddressdictionary[categories[row]];
string actualCellAdress = ExpenseCategoriesAddressdictionary[categories[row]];
string newBudgetCell = $"OFFSET({budgetCellAdress},MONTH($G$1),0)";
string newActualCell = $"OFFSET({actualCellAdress},MONTH($G$1),0)";
excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(operatingAdress, row + 1, 0)].Value = categories[row];
excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(budgetAdress, row + 1, 0)].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(budgetAdress, row + 1, 0)].Formula = newBudgetCell;
excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(actualAdress, row + 1, 0)].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(actualAdress, row + 1, 0)].Formula = newActualCell;
}
}
/// <summary>
///
/// </summary>
/// <param name="wsSheet"></param>
/// <param name="startRow"></param>
/// <param name="movementsModel"></param>
/// <param name="categories"></param>
/// <param name="year"></param>
/// <param name="justExtrations"></param>
/// <returns></returns>
public static ExcelTable CreateCategoriesMonthsAveragetest(ExcelWorksheet wsSheet, int startRow, List <MovementsViewModel> movementsModel, IEnumerable <string> categories,
int year = 0, bool justExtrations = true)
{
categories = justExtrations ? ModelOperation.GetExtractionCategories(categories, movementsModel) : ModelOperation.GetIncomsCategories(categories, movementsModel);
var newAverageColumn = new List <string>();
var startTableCell = ExcelHelpers.AddRowAndColumnToCellAddress(_startCell, startRow, 0);
categories = categories.OrderBy(c => c);
var columns = new[] { "Month" };
if (year > 0)
{
newAverageColumn.Add($"Year({year})");
}
else
{
newAverageColumn.Add("Years");
var years = movementsModel.Select(mov => mov.DateTime.Year).Distinct();
newAverageColumn.AddRange(years.Select(selectedYear => $"Year({selectedYear})"));
}
var newExcelColumn = columns.Concat(categories);
// Calculate size of the table
var excelColumns = newExcelColumn as string[] ?? newExcelColumn.ToArray();
var endTableCell = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, 12, excelColumns.Count() - 1);
//give table Name
var tableName = "CategoriesMonthAverage1";
// Add table Headers
var excelTable = CreateExcelTable(wsSheet, tableName, excelColumns, startTableCell, endTableCell, true);
for (int month = 1; month <= 12; month++)
{
for (int column = 0; column < excelColumns.Count(); column++)
{
var tableCell = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, month, column);
if (excelColumns[column] == "Month")
{
var monthName = string.Concat(DateTimeFormatInfo.CurrentInfo.GetMonthName(month));
excelTable.WorkSheet.Cells[tableCell].Value = monthName;
}
else
{
excelTable.WorkSheet.Cells[tableCell].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
var average = ModelOperation.AverageforCategory(movementsModel, excelColumns[column], year, month, justExtrations);
excelTable.WorkSheet.Cells[tableCell].Value = average;
}
}
}
return(excelTable);
}
/// <summary>
///
/// </summary>
/// <param name="movementsModel"></param>
/// <param name="wsSheet"></param>
/// <param name="categories"></param>
/// <param name="year"></param>
/// <param name="month"></param>
/// <param name="justExtrations"></param>
/// <returns></returns>
public static ExcelTable CreateAverageForYearMonthDay(List <MovementsViewModel> movementsModel, ExcelWorksheet wsSheet, IEnumerable <string> categories,
int year = 0, int?month = 0, bool justExtrations = true)
{
categories = justExtrations ? ModelOperation.GetExtractionCategories(categories, movementsModel) : ModelOperation.GetIncomsCategories(categories, movementsModel);
categories = categories.OrderBy(c => c);
var columns = new[] { "Type" };
var newAverageColumn = new List <string>();
//to get the month average we need to specify if is jus one year or all the years
IEnumerable <int> years = null;
if (year > 0)
{
newAverageColumn.Add($"Year({year})");
}
else
{
newAverageColumn.Add("Years");
years = movementsModel.Select(mov => mov.DateTime.Year).Distinct();
newAverageColumn.AddRange(years.Select(selectedYear => $"Year({selectedYear})"));
}
newAverageColumn.Add("Month");
newAverageColumn.Add("Day");
var newExcelColumn = columns.Concat(categories);
// Calculate size of the table
var excelColumns = newExcelColumn as string[] ?? newExcelColumn.ToArray();
string endTableCell = ExcelHelpers.AddRowAndColumnToCellAddress(_startCell, newAverageColumn.Count(), excelColumns.Count() - 1);
//give table Name
var tableName = "CategoriesYearMonthDayAverage";
// Add table Headers
var excelTable = CreateExcelTable(wsSheet, tableName, excelColumns, _startCell, endTableCell, true);
for (int row = 0; row < newAverageColumn.Count(); row++)
{
var rowValue = newAverageColumn[row];
for (int column = 0; column < excelColumns.Count(); column++)
{
var category = excelColumns[column];
string cellAdress = ExcelHelpers.AddRowAndColumnToCellAddress(_startCell, row + 1, column);
double categoryAverage = 0;
if (category == "Type")
{
excelTable.WorkSheet.Cells[cellAdress].Value = newAverageColumn[row];
}
else
{
if (rowValue == "Years")
{
categoryAverage = ModelOperation.AverageforCategory(movementsModel, category, null, null, justExtrations);
}
else if (rowValue == "Month")
{
categoryAverage = years != null?ModelOperation.AverageforCategory(movementsModel, category, null, 0, justExtrations) :
ModelOperation.AverageforCategory(movementsModel, category, year, 0, justExtrations);
}
else
{
if (years != null)
{
foreach (var selectedYear in years)
{
if (rowValue == $"Year({selectedYear})")
{
categoryAverage = ModelOperation.GetTotalforCategory(movementsModel, category, selectedYear, null, justExtrations);
}
}
}
else
{
categoryAverage = ModelOperation.GetTotalforCategory(movementsModel, category, year, null, justExtrations);
}
}
excelTable.WorkSheet.Cells[cellAdress].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
excelTable.WorkSheet.Cells[cellAdress].Value = categoryAverage;
}
}
}
return(excelTable);
}
