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