private void AddChartsHeader()
{
var cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Charts"
},
UserEnteredFormat = new CellFormat()
{
BackgroundColor = GetSheetsColor(),
TextFormat = new TextFormat()
{
FontSize = 18,
Bold = true,
},
VerticalAlignment = "MIDDLE",
HorizontalAlignment = "CENTER"
}
});
_googleSheetService.AddRow(cells);
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Category"
},
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetAllBorders(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Spent"
},
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetAllBorders(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
HorizontalAlignment = "RIGHT",
}
});
_googleSheetService.AddRow(cells);
_googleSheetService.MergeRange(new GridRange()
{
StartRowIndex = 0,
EndRowIndex = 1,
StartColumnIndex = 0,
EndColumnIndex = 7
});
}
private void AddCategoryRow(CategoryDto category)
{
var cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=Spending!{GoogleSheetService.GetCellAddress(0, category.RowIndex)}"
},
UserEnteredFormat = new CellFormat()
{
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
Borders = GoogleSheetService.GetAllBorders(),
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=Spending!{GoogleSheetService.GetCellAddress(2, category.RowIndex + 2)}"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
Borders = GoogleSheetService.GetAllBorders(),
}
});
_googleSheetService.AddRow(cells);
}
public ChartsSheetService(BudgetDto budget, ILogger log)
{
_budget = budget ?? throw new ArgumentNullException(nameof(budget));
_log = log;
int rowsNumber = 5 + _budget.Categories.Count + 54;
int colsNumber = 7;
_googleSheetService = new GoogleSheetService("Charts", 2, rowsNumber, colsNumber, 0, 0, true, GetSheetsColor());
_googleSheetService.SetRowHeight(0, 50);
_googleSheetService.AddProtectedRanges(0, 2, 1, 3 + _budget.Categories.Count);
}
public DashboardSheetService(BudgetDto budget)
{
_budget = budget ?? throw new ArgumentNullException(nameof(budget));
int rowsNumber = 3 + (_budget.Categories.Count * 2) + _budget.Subcategories.Count;
int colsNumber = 4;
_googleSheetService = new GoogleSheetService("Dashboard", 0, rowsNumber, colsNumber, 0, 2, true, GetSheetsColor());
_subcategoryRows = new List <Model.DTOs.GoogleSpreadsheet.DashboardSubcategoryRowDto>();
_googleSheetService.SetRowHeight(0, 50);
_googleSheetService.AddProtectedRanges(0, 4, 1, rowsNumber);
}
private CellFormat CategoryHeaderFormat()
{
return(new CellFormat()
{
TextFormat = new TextFormat()
{
FontSize = 12,
FontFamily = "Verdana",
Bold = true
},
Borders = GoogleSheetService.GeTopAndBottomMediumBorder(),
VerticalAlignment = "MIDDLE",
HorizontalAlignment = "CENTER"
});
}
public SpendingSheetService(BudgetDto budget)
{
_budget = budget;
int rowsNumber = 16 + (_budget.Incomes.Count + 4) + (_budget.Categories.Count * 4) + _budget.Subcategories.Count;
int colsNumber = 6 + DateTime.DaysInMonth(_budget.Month.Year, _budget.Month.Month);
_googleSheetService = new GoogleSheetService("Spending", 1, rowsNumber, colsNumber, 5, 0, false, GetSheetsColor());
_subcategoriesRowsIndexes = new List <int>();
_subcategoriesRows = new List <CellData>();
_googleSheetService.SetRowHeight(0, 50);
_googleSheetService.SetColumnWidth(0, 180);
_googleSheetService.SetColumnWidth(1, 110);
_googleSheetService.SetColumnWidth(2, 110);
_googleSheetService.SetColumnWidth(3, 110);
_googleSheetService.SetColumnWidth(4, 100);
}
private void AddCategoriesTable(IList <CategoryDto> categories, IList <SubcategoryDto> subcategories)
{
foreach (var category in categories)
{
AddCategoryRow(category);
}
var cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Sum:"
},
UserEnteredFormat = new CellFormat()
{
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
Borders = GoogleSheetService.GetTopMediumBorder(),
HorizontalAlignment = "RIGHT",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=SUM({GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow - categories.Count)}:{GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow - 1)})"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
Borders = GoogleSheetService.GetTopMediumBorder(),
}
});
_googleSheetService.AddRow(cells);
_googleSheetService.AddEmptyRow();
_googleSheetService.AddEmptyRow();
}
private void AddCategoryHeader(CategoryDto category)
{
var cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=Spending!{GoogleSheetService.GetCellAddress(0, category.RowIndex)}"
},
UserEnteredFormat = CategoryHeaderFormat()
});
_googleSheetService.SetRowHeight(_googleSheetService.CurrentRow, 30);
_googleSheetService.MergeRange(new GridRange()
{
StartRowIndex = _googleSheetService.CurrentRow,
EndRowIndex = _googleSheetService.CurrentRow + 1,
StartColumnIndex = 0,
EndColumnIndex = 4
});
_googleSheetService.AddRow(cells);
}
private void AddCategorySection(CategoryDto category, IEnumerable <SubcategoryDto> subcategories)
{
AddCategoryHeader(category);
foreach (var sub in subcategories)
{
var subCells = new List <CellData>();
subCells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=Spending!{GoogleSheetService.GetCellAddress(0, sub.RowIndex)}"
},
UserEnteredFormat = new CellFormat()
{
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
subCells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=Spending!{GoogleSheetService.GetCellAddress(1, sub.RowIndex)}"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
subCells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=Spending!{GoogleSheetService.GetCellAddress(2, sub.RowIndex)}"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
subCells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=Spending!{GoogleSheetService.GetCellAddress(3, sub.RowIndex)}"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
_subcategoryRows.Add(new Model.DTOs.GoogleSpreadsheet.DashboardSubcategoryRowDto(sub.Id, sub.CategoryId, _googleSheetService.CurrentRow));
_googleSheetService.AddRow(subCells);
}
}
private void AddSummarySection()
{
var cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Total sum:"
},
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetTopMediumBorder(),
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=Spending!{GoogleSheetService.GetCellAddress(1, 13)}"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
Borders = GoogleSheetService.GetTopMediumBorder(),
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=Spending!{GoogleSheetService.GetCellAddress(2, 13)}"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
Borders = GoogleSheetService.GetTopMediumBorder(),
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=Spending!{GoogleSheetService.GetCellAddress(3, 13)}"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
Borders = GoogleSheetService.GetTopMediumBorder(),
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
_googleSheetService.AddRow(cells);
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Subcategory"
},
UserEnteredFormat = new CellFormat()
{
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
VerticalAlignment = "TOP",
Borders = GoogleSheetService.GetTopBorder(),
WrapStrategy = "WRAP",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Planned spending"
},
UserEnteredFormat = new CellFormat()
{
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
VerticalAlignment = "TOP",
HorizontalAlignment = "RIGHT",
Borders = GoogleSheetService.GetTopBorder(),
WrapStrategy = "WRAP",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Actual spending"
},
UserEnteredFormat = new CellFormat()
{
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
VerticalAlignment = "TOP",
HorizontalAlignment = "RIGHT",
Borders = GoogleSheetService.GetTopBorder(),
WrapStrategy = "WRAP",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Difference"
},
UserEnteredFormat = new CellFormat()
{
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
VerticalAlignment = "TOP",
HorizontalAlignment = "RIGHT",
Borders = GoogleSheetService.GetTopBorder(),
WrapStrategy = "WRAP",
}
});
_googleSheetService.AddRow(cells);
}
private void AddIncomesSection(IList <IncomeDto> incomes)
{
AddIncomeHeader();
foreach (var income in incomes)
{
var cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = income.Name
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
NumberValue = Convert.ToDouble(income.Amount)
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
}
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=SUM({GoogleSheetService.GetCellAddress(6, _googleSheetService.CurrentRow)}:{GoogleSheetService.GetCellAddress(6 + DateTime.DaysInMonth(_budget.Month.Year, _budget.Month.Month) - 1, _googleSheetService.CurrentRow)})"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
}
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"={GoogleSheetService.GetCellAddress(2, _googleSheetService.CurrentRow)}-{GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow)}"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
}
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=IFERROR({GoogleSheetService.GetCellAddress(2, _googleSheetService.CurrentRow)}/{GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow)}; \"-\")"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "PERCENT",
Pattern = "0.00%"
},
HorizontalAlignment = "CENTER",
}
});
cells.Add(new CellData());
for (int i = 0; i < DateTime.DaysInMonth(_budget.Month.Year, _budget.Month.Month); i++)
{
cells.Add(new CellData()
{
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
}
}
});
}
_googleSheetService.AddRow(cells);
}
_googleSheetService.AddEmptyRow();
}
private void AddTotalSumRow()
{
var emptyCellWithTopBorder = new CellData()
{
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetTopBorder(),
}
};
//Add Total sum row
var cells = new List <CellData>();
cells.Add(emptyCellWithTopBorder);
cells.Add(emptyCellWithTopBorder);
cells.Add(emptyCellWithTopBorder);
cells.Add(emptyCellWithTopBorder);
cells.Add(emptyCellWithTopBorder);
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Total sum:",
},
UserEnteredFormat = new CellFormat()
{
TextFormat = new TextFormat()
{
FontFamily = "Verdana",
Bold = true,
},
HorizontalAlignment = "RIGHT",
Borders = GoogleSheetService.GetTopBorder(),
}
});
for (int i = 0; i < DateTime.DaysInMonth(_budget.Month.Year, _budget.Month.Month); i++)
{
int COLUMN_SHIFT = 6;
string formula = "=SUM(";
foreach (var subcategoryIndex in _subcategoriesRowsIndexes)
{
string address = GoogleSheetService.GetCellAddress(i + COLUMN_SHIFT, subcategoryIndex);
if (!_subcategoriesRowsIndexes.Last().Equals(subcategoryIndex))
{
formula += $"{address}+";
}
else
{
formula += $"{address})";
}
}
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = formula
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
Borders = GoogleSheetService.GetTopBorder(),
}
});
}
_googleSheetService.AddRow(cells);
}
private void AddCategoryHeader(string categoryName, int subcategoriesCount)
{
var cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = categoryName
},
UserEnteredFormat = SubHeaderFormat(),
});
_budget.Categories.FirstOrDefault(x => x.Name.ToLower() == categoryName.ToLower()).RowIndex = _googleSheetService.CurrentRow;
_googleSheetService.AddRow(cells);
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Name"
},
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetTopBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
WrapStrategy = "WRAP",
VerticalAlignment = "MIDDLE",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Planned spending"
},
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetTopBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
WrapStrategy = "WRAP",
HorizontalAlignment = "RIGHT",
VerticalAlignment = "MIDDLE",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Actual spending"
},
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetTopBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
WrapStrategy = "WRAP",
HorizontalAlignment = "RIGHT",
VerticalAlignment = "MIDDLE",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Difference"
},
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetTopBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
WrapStrategy = "WRAP",
HorizontalAlignment = "RIGHT",
VerticalAlignment = "MIDDLE",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Realization"
},
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetTopBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
WrapStrategy = "WRAP",
HorizontalAlignment = "CENTER",
VerticalAlignment = "MIDDLE",
}
});
cells.Add(new CellData());
for (int i = 0; i < DateTime.DaysInMonth(_budget.Month.Year, _budget.Month.Month); i++)
{
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=SUM({GoogleSheetService.GetCellAddress(6 + i, _googleSheetService.CurrentRow + 2)}:{GoogleSheetService.GetCellAddress(6 + i, _googleSheetService.CurrentRow + 2 + subcategoriesCount - 1)})"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
Borders = GoogleSheetService.GetTopBorder(),
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
VerticalAlignment = "MIDDLE",
}
});
}
_googleSheetService.AddRow(cells);
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Sum:"
},
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetBottomBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
VerticalAlignment = "MIDDLE",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=SUM({GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow + 1)}:{GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow + subcategoriesCount)})"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
Borders = GoogleSheetService.GetBottomBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
VerticalAlignment = "MIDDLE",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=SUM({GoogleSheetService.GetCellAddress(2, _googleSheetService.CurrentRow + 1)}:{GoogleSheetService.GetCellAddress(2, _googleSheetService.CurrentRow + subcategoriesCount)})"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
Borders = GoogleSheetService.GetBottomBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
VerticalAlignment = "MIDDLE",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"={GoogleSheetService.GetCellAddress(2, _googleSheetService.CurrentRow)}-{GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow)}"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
Borders = GoogleSheetService.GetBottomBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
VerticalAlignment = "MIDDLE",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=IFERROR({GoogleSheetService.GetCellAddress(2, _googleSheetService.CurrentRow)}/{GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow)}; \"-\")"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "PERCENT",
Pattern = "0.00%"
},
Borders = GoogleSheetService.GetBottomBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
HorizontalAlignment = "CENTER",
VerticalAlignment = "MIDDLE",
}
});
cells.Add(new CellData());
DateTime columnDate = new DateTime(_budget.Month.Year, _budget.Month.Month, 1);
for (int i = 0; i < DateTime.DaysInMonth(_budget.Month.Year, _budget.Month.Month); i++)
{
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = $"{columnDate.ToString("yyyy-MM-dd")}"
},
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetBottomBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
HorizontalAlignment = "CENTER",
VerticalAlignment = "MIDDLE",
}
});
columnDate = columnDate.AddDays(1);
}
_googleSheetService.AddRow(cells);
}
private void AddSpendingHeader()
{
var cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Spending"
},
UserEnteredFormat = new CellFormat()
{
BackgroundColor = GetSheetsColor(),
TextFormat = new TextFormat()
{
FontSize = 18,
Bold = true,
FontFamily = "Verdana",
},
VerticalAlignment = "MIDDLE",
HorizontalAlignment = "CENTER"
}
});
_googleSheetService.AddRow(cells);
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Budget Plan"
},
UserEnteredFormat = SubHeaderFormat(),
});
_googleSheetService.AddRow(cells);
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Planned income"
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = "=B13"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting()
}
});
_googleSheetService.AddRow(cells);
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Planned spending"
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = "=B14"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
_googleSheetService.AddRow(cells);
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "To allocate"
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = "=B3-B4"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
_googleSheetService.AddRow(cells);
_googleSheetService.AddEmptyRow();
//Budget Realisation
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Budget Realization"
},
UserEnteredFormat = SubHeaderFormat(),
});
_googleSheetService.AddRow(cells);
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Actual income"
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = "=C13"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
_googleSheetService.AddRow(cells);
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Actual spending"
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = "=C14"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
_googleSheetService.AddRow(cells);
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Still available"
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = "=B8-B9"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
_googleSheetService.AddRow(cells);
_googleSheetService.AddEmptyRow();
//Header table
cells = new List <CellData>();
cells.Add(new CellData());
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Planned"
},
UserEnteredFormat = new CellFormat()
{
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
HorizontalAlignment = "RIGHT",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Actual"
},
UserEnteredFormat = new CellFormat()
{
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
HorizontalAlignment = "RIGHT",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Difference"
},
UserEnteredFormat = new CellFormat()
{
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
HorizontalAlignment = "RIGHT",
}
});
_googleSheetService.AddRow(cells);
//Income sum:
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Income sum:"
},
UserEnteredFormat = new CellFormat()
{
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
HorizontalAlignment = "RIGHT"
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"={GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow + 5)}"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"={GoogleSheetService.GetCellAddress(2, _googleSheetService.CurrentRow + 5)}"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"={GoogleSheetService.GetCellAddress(3, _googleSheetService.CurrentRow + 5)}"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
}
}
});
_googleSheetService.AddRow(cells);
//Spending sum:
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Spending sum:"
},
UserEnteredFormat = new CellFormat()
{
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
HorizontalAlignment = "RIGHT"
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
NumberValue = 0
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
NumberValue = 0
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = "=C14-B14"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
}
}
});
cells.ForEach(x => _subcategoriesRows.Add(x));
_googleSheetService.AddRow(cells);
_googleSheetService.AddEmptyRow();
_googleSheetService.MergeRange(new GridRange()
{
StartRowIndex = 0,
EndRowIndex = 1,
StartColumnIndex = 0,
EndColumnIndex = 5
});
}
private void AddDashboardHeader()
{
var cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Dashboard"
},
UserEnteredFormat = new CellFormat()
{
BackgroundColor = GetSheetsColor(),
TextFormat = new TextFormat()
{
FontSize = 18,
Bold = true,
},
VerticalAlignment = "MIDDLE",
HorizontalAlignment = "CENTER"
}
});
_googleSheetService.AddRow(cells);
cells = new List <CellData>();
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Subcategory"
},
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetBottomMediumBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
VerticalAlignment = "BOTTOM",
WrapStrategy = "WRAP",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Planned spending"
},
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetBottomMediumBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
VerticalAlignment = "BOTTOM",
HorizontalAlignment = "RIGHT",
WrapStrategy = "WRAP",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Actual spending"
},
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetBottomMediumBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
VerticalAlignment = "BOTTOM",
HorizontalAlignment = "RIGHT",
WrapStrategy = "WRAP",
}
});
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Difference"
},
UserEnteredFormat = new CellFormat()
{
Borders = GoogleSheetService.GetBottomMediumBorder(),
TextFormat = GoogleSheetService.GetDefaultTableHeaderFormatting(),
VerticalAlignment = "BOTTOM",
HorizontalAlignment = "RIGHT",
WrapStrategy = "WRAP",
}
});
_googleSheetService.AddRow(cells);
_googleSheetService.MergeRange(new GridRange()
{
StartRowIndex = 0,
EndRowIndex = 1,
StartColumnIndex = 0,
EndColumnIndex = 4
});
}
private void AddCategorySection(string categoryName, IEnumerable <SubcategoryDto> subcategories, bool isLast = false)
{
AddCategoryHeader(categoryName, subcategories.Count());
foreach (var sub in subcategories)
{
var subCells = new List <CellData>();
subCells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = sub.Name
},
UserEnteredFormat = new CellFormat()
{
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
subCells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
NumberValue = Convert.ToDouble(sub.Amount)
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
subCells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=SUM({GoogleSheetService.GetCellAddress(6, _googleSheetService.CurrentRow)}:{GoogleSheetService.GetCellAddress(6 + DateTime.DaysInMonth(_budget.Month.Year, _budget.Month.Month) - 1, _googleSheetService.CurrentRow)})"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
subCells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"={GoogleSheetService.GetCellAddress(2, _googleSheetService.CurrentRow)}-{GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow)}"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
subCells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"=IFERROR({GoogleSheetService.GetCellAddress(2, _googleSheetService.CurrentRow)}/{GoogleSheetService.GetCellAddress(1, _googleSheetService.CurrentRow)}; \"-\")"
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "PERCENT",
Pattern = "0.00%"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
HorizontalAlignment = "CENTER",
}
});
subCells.Add(new CellData());
for (int i = 0; i < DateTime.DaysInMonth(_budget.Month.Year, _budget.Month.Month); i++)
{
subCells.Add(new CellData()
{
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY"
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
}
});
}
sub.RowIndex = _googleSheetService.CurrentRow;
_subcategoriesRowsIndexes.Add(_googleSheetService.CurrentRow);
_googleSheetService.AddRow(subCells);
}
List <string> listOfPlannedAddresses = new List <string>();
List <string> listOfActualAddresses = new List <string>();
foreach (var index in _subcategoriesRowsIndexes)
{
listOfPlannedAddresses.Add(GoogleSheetService.GetCellAddress(1, index));
listOfActualAddresses.Add(GoogleSheetService.GetCellAddress(2, index));
}
_subcategoriesRows.ElementAt(1).UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"={string.Join('+', listOfPlannedAddresses)}"
};
_subcategoriesRows.ElementAt(2).UserEnteredValue = new ExtendedValue()
{
FormulaValue = $"={string.Join('+', listOfActualAddresses)}"
};
if (isLast == false)
{
_googleSheetService.AddEmptyRow();
}
}
private void AddCumulativeSumRow()
{
var emptyCellWithTopBorder = new CellData()
{
UserEnteredFormat = new CellFormat()
{
}
};
//Add Cumulative sum row
var cells = new List <CellData>();
cells.Add(emptyCellWithTopBorder);
cells.Add(emptyCellWithTopBorder);
cells.Add(emptyCellWithTopBorder);
cells.Add(emptyCellWithTopBorder);
cells.Add(emptyCellWithTopBorder);
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Cumulative sum:",
},
UserEnteredFormat = new CellFormat()
{
TextFormat = new TextFormat()
{
FontFamily = "Verdana",
Bold = true,
},
HorizontalAlignment = "RIGHT",
WrapStrategy = "WRAP",
}
});
for (int i = 0; i < DateTime.DaysInMonth(_budget.Month.Year, _budget.Month.Month); i++)
{
int COLUMN_SHIFT = 6;
string formula = "";
if (i == 0)
{
formula = $"={GoogleSheetService.GetCellAddress(i + COLUMN_SHIFT, _googleSheetService.CurrentRow - 1)}";
}
else
{
formula = $"={GoogleSheetService.GetCellAddress(i + COLUMN_SHIFT - 1, _googleSheetService.CurrentRow)}+{GoogleSheetService.GetCellAddress(i + COLUMN_SHIFT, _googleSheetService.CurrentRow - 1)}";
}
cells.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
FormulaValue = formula,
},
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "CURRENCY",
},
TextFormat = GoogleSheetService.GetDefaultCellFormatting(),
VerticalAlignment = "MIDDLE",
}
});
}
_googleSheetService.AddRow(cells);
}
