private void LoadCashCodeYear(WorksheetBase ws, string cashCode, short yearNumber, short yearCount, int row, bool includeActivePeriods, bool includeOrderBook, bool includeTaxAccruals)
{
int startCol, curCol;
var cashData = dataContext.CashCodeValues(cashCode, yearNumber, includeActivePeriods, includeOrderBook, includeTaxAccruals);
startCol = 4 + ((yearCount - 1) * 13);
curCol = startCol;
foreach (Data.proc_FlowCashCodeValuesResult codeValue in cashData)
{
switch (reportMode)
{
case ReportMode.CashFlow:
ws.Cells[row, curCol].Value = codeValue.InvoiceValue;
break;
case ReportMode.Budget:
ws.Cells[row, curCol].Value = codeValue.InvoiceValue - codeValue.ForecastValue;
break;
}
curCol++;
}
ws.Cells[row, curCol].Formula = $"=SUM({Column(startCol)}{row}:{Column(curCol - 1)}{row})";
}
private void LoadTotals(WorksheetBase ws, CashType cashType, CategoryType categoryType)
{
var totals = dataContext.CategoriesByType(cashType, categoryType);
if (totals.Count() < 2)
{
return;
}
curRow += 2;
ws.Cells[curRow, 1].EntireRow.Locked = true;
if (!Greyscale)
{
ws.Cells[curRow, 1].EntireRow.Interior.Color = Color.GreenYellow;
}
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
ws.Cells[curRow, 1].Value = $"{totals.Select(s => s.CashType).First()} {Properties.Resources.TextTotals}";
ws.Cells[curRow, 1].Font.Bold = true;
ws.Cells[curRow, 1].Font.Underline = false;
foreach (Data.fnFlowCategoriesByTypeResult total in totals)
{
curRow++;
ws.Cells[curRow, 1].EntireRow.Locked = true;
ws.Cells[curRow, 1].Value = "=" + "\"" + total.CategoryCode + "\"";
ws.Cells[curRow, 2].Value = total.Category;
ws.Cells[curRow, 3].Value = "=" + "\"" + total.CategoryCode + "\"";
}
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;
}
void InitialiseWorksheet(WorksheetBase ws)
{
ws.Cells.Font.Size = 8;
ws.Range["A1"].Font.Size = 12;
ws.Range["A1"].Font.Bold = true;
ws.Range["A2"].Value2 = dataContext.CompanyName;
ws.Range["A2"].Font.Size = 10;
ws.Range["A2"].Font.Bold = true;
ws.Range["A3"].Value2 = Properties.Resources.TextDate;
ws.Range["A3"].EntireRow.Font.Size = 10;
ws.Range["A3"].EntireRow.Font.Bold = true;
ws.Range["B3"].Value2 = DateTime.Now.ToString("dd MMM HH:mm:ss");
//ws.Range["B3"].NumberFormat = "dd mmmm yyyy";
ws.Range["B2:B3"].HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
ws.Range["A4"].Value = Properties.Resources.TextCode;
ws.Range["B4"].Value = Properties.Resources.TextName;
ws.Range["A4"].EntireRow.Font.Size = 8;
ws.Range["A4"].EntireRow.Font.Bold = true;
ws.Range["A4"].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Range["A4"].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Range["A4"].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;
ws.Range["A4"].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
ws.Range["B4"].EntireColumn.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;
ws.Cells.Locked = false;
ws.Range["A:C"].EntireColumn.Locked = true;
ws.Range["1:4"].EntireRow.Locked = true;
}
void ClearWorksheet(WorksheetBase worksheet)
{
worksheet.UsedRange.ClearContents();
worksheet.UsedRange.ClearFormats();
worksheet.UsedRange.ClearComments();
worksheet.UsedRange.ClearNotes();
worksheet.UsedRange.Clear();
worksheet.Cells.Clear();
}
public static void NotifySheetLoaded(WorksheetBase sheet)
{
_sheeetCount--;
if (_sheeetCount == 0)
{
OnWorksheetsLoaded();
}
}
void SetActivePeriod(WorksheetBase ws, string title)
{
var activePeriod = dataContext.ActivePeriod;
ActiveYear = activePeriod.YearNumber;
ActiveMonth = activePeriod.MonthNumber;
ActiveDate = activePeriod.StartOn;
ws.Range["A1"].Value2 = string.Format(title, activePeriod.MonthName, activePeriod.Description);
}
int CategoryRow(WorksheetBase ws, string categoryCode)
{
int row = ws.Range["C1"].EntireColumn.Find(What: categoryCode, SearchDirection: Excel.XlSearchDirection.xlNext, MatchCase: true).Row;
while (ws.Cells[row, 3].Value != categoryCode)
{
row = ws.Range[$"C{++row}:C{curRow}"].Find(What: categoryCode, SearchDirection: Excel.XlSearchDirection.xlNext, MatchCase: true).Row;
}
return(row);
}
private void LoadExpression(WorksheetBase ws, string expressionTemplate)
{
string expression, category, categoryCode;
int pos;
Dictionary <string, int> categoryCodes = new Dictionary <string, int>();
const char RB = ']';
const char LB = '[';
expression = expressionTemplate;
pos = expression.IndexOf(LB, 0, expression.Length);
while (pos > 0)
{
pos++;
category = expression.Substring(pos, expression.IndexOf(RB, pos + 1) - pos);
if (!categoryCodes.ContainsKey(category))
{
categoryCode = dataContext.CategoryCodeFromName(category);
categoryCodes.Add(categoryCode, CategoryRow(ws, categoryCode));
expression = expression.Replace(category, categoryCode);
}
pos = expression.IndexOf(LB, pos, expression.Length - pos);
}
for (curCol = firstCol; curCol <= lastCol; curCol++)
{
string formula = expression;
foreach (var code in categoryCodes)
{
formula = formula.Replace($"{LB}{code.Key}{RB}", $"{Column(curCol)}{code.Value}");
}
try
{
ws.Cells[curRow, curCol].Formula = $"={formula}";
}
catch
{
ws.Cells[curRow, curCol].Formula = $"{formula}";
}
}
}
private void LoadExpressions(WorksheetBase ws)
{
var expressons = dataContext.CategoryExpressions;
if (expressons.Count() == 0)
{
return;
}
curRow += 2;
ws.Cells[curRow, 1].EntireRow.Locked = true;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
if (!Greyscale)
{
ws.Cells[curRow, 1].EntireRow.Interior.Color = Color.LimeGreen;
}
ws.Cells[curRow, 1].Value = Properties.Resources.TextAnalysis;
ws.Cells[curRow, 1].Font.Bold = true;
ws.Cells[curRow, 1].Font.Underline = false;
foreach (Data.vwCategoryExpression expression in expressons)
{
curRow++;
ws.Cells[curRow, 1].EntireRow.Locked = true;
ws.Cells[curRow, 1].Value = expression.Category;
ws.Cells[curRow, 3].Value = "=" + "\"" + expression.CategoryCode + "\"";
if (!Greyscale)
{
ws.Cells[curRow, 3].Font.Color = ws.Cells[curRow, 1].EntireRow.Cells.Interior.Color;
}
ws.Range[$"A{curRow}"].EntireRow.NumberFormat = Properties.Resources.FormatPercent;
ws.Range[$"D{curRow}"].EntireRow.FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlLess, "0").Font.Color = Color.Red;
LoadExpression(ws, expression.Expression);
}
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;
}
void InitialisePeriods(WorksheetBase ws)
{
var months = dataContext.MonthNames;
var years = dataContext.ActiveYears;
curRow = 3;
curCol = 4;
foreach (Data.vwActiveYear year in years)
{
ws.Cells[3, curCol].Value = $"{year.Description} ({year.CashStatus})";
curCol -= 1;
foreach (Data.vwMonth month in months)
{
curCol++;
ws.Cells[4, curCol].Value = month.MonthName;
if (year.YearNumber == ActiveYear && month.MonthNumber == ActiveMonth)
{
ws.Cells[1, curCol].EntireColumn.Cells.Interior.Color = Greyscale ? Color.LightGray : Color.Yellow;
periodCol = curCol;
}
ws.Columns[Column(curCol)].NumberFormat = Properties.Resources.FormatNumber;
ws.Cells[1, curCol].EntireColumn.ColumnWidth = 11;
}
curCol++;
ws.Cells[3, curCol].Value = year.Description;
ws.Cells[4, curCol].Value = Properties.Resources.TextTotals;
ws.Cells[4, curCol].EntireColumn.Cells.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;
ws.Cells[4, curCol].EntireColumn.Cells.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
ws.Cells[4, curCol].EntireColumn.Locked = true;
ws.Columns[Column(curCol)].NumberFormat = Properties.Resources.FormatNumber;
ws.Columns[Column(curCol)].EntireColumn.ColumnWidth = 11;
ws.Columns[Column(curCol)].EntireColumn.Font.Bold = true;
curCol++;
}
lastCol = curCol - 1;
}
private void LoadTotalsFormula(WorksheetBase ws)
{
int codeRow;
var categories = dataContext.CategoryTotals;
if (categories.Count() == 0)
{
return;
}
foreach (Data.vwCategoryTotal category in categories)
{
codeRow = CategoryRow(ws, category.CategoryCode);
var totalCodes = dataContext.CategoryTotalCodes(category.CategoryCode);
string formula, colName;
for (int curCol = firstCol; curCol <= lastCol; curCol++)
{
colName = Column(curCol);
formula = string.Empty;
foreach (string categoryCode in totalCodes)
{
if (formula.Length == 0)
{
formula = $"={colName}{CategoryRow(ws, categoryCode)}";
}
else
{
formula += $"+{colName}{CategoryRow(ws, categoryCode)}";
}
}
ws.Cells[codeRow, curCol].Formula = formula;
}
}
}
private void LoadCategories(WorksheetBase ws, CashType cashType, bool includeActivePeriods, bool includeOrderBook, bool includeTaxAccruals)
{
int startRow;
var categories = dataContext.Categories(cashType);
var years = dataContext.ActiveYears;
foreach (Data.fnFlowCategoryResult category in categories)
{
curRow += 2;
ws.Range[$"A{curRow - 1}:A{curRow}"].EntireRow.Locked = true;
startRow = curRow;
ws.Cells[curRow, 1].Value = category.Category;
ws.Cells[curRow, 1].Font.Bold = true;
ws.Cells[curRow, 1].Font.Underline = false;
if (!Greyscale)
{
ws.Range[ws.Cells[curRow, 1], ws.Cells[curRow, 2]].Interior.Color = Color.LightYellow;
}
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;
var cashCodes = dataContext.CashCodes(category.CategoryCode);
foreach (Data.fnFlowCategoryCashCodesResult cashCode in cashCodes)
{
curRow++;
ws.Cells[curRow, 1].Value = cashCode.CashCode;
ws.Cells[curRow, 2].Value = cashCode.CashDescription;
short yearCount = 0;
foreach (Data.vwActiveYear year in years)
{
++yearCount;
LoadCashCodeYear(ws, cashCode.CashCode, year.YearNumber, yearCount, curRow, includeActivePeriods, includeOrderBook, includeTaxAccruals);
}
}
curRow++;
ws.Cells[curRow, 1].Value = Properties.Resources.TextTotals;
ws.Cells[curRow, 1].EntireRow.Font.Bold = true;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
if (!Greyscale)
{
switch ((CashMode)category.CashModeCode)
{
case CashMode.Expense:
ws.Cells[curRow, 1].EntireRow.Cells.Interior.Color = Color.LightSalmon;
break;
case CashMode.Income:
ws.Cells[curRow, 1].EntireRow.Cells.Interior.Color = Color.CornflowerBlue;
break;
case CashMode.Neutral:
ws.Cells[curRow, 1].EntireRow.Cells.Interior.Color = Color.LightGray;
break;
}
}
if (!Greyscale)
{
ws.Cells[curRow, 3].Font.Color = ws.Cells[curRow, 1].EntireRow.Cells.Interior.Color;
}
ws.Cells[curRow, 3].Value = "=" + "\"" + category.CategoryCode + "\"";
for (int curCol = 4; curCol <= lastCol; curCol++)
{
string formula = $"=SUM({Column(curCol)}{startRow + 1}:{Column(curCol)}{curRow - 1})";
if ((CashMode)category.CashModeCode == CashMode.Expense)
{
formula += "*-1";
}
ws.Cells[curRow, curCol].Formula = formula;
}
ws.Cells[curRow, 1].EntireRow.Locked = true;
}
if (categories.Count() < 2)
{
return;
}
curRow += 2;
ws.Cells[curRow, 1].Value = Properties.Resources.TextSummary;
ws.Cells[curRow, 1].EntireRow.Locked = true;
ws.Cells[curRow, 1].EntireRow.Font.Bold = true;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
if (!Greyscale)
{
ws.Cells[curRow, 1].EntireRow.Cells.Interior.Color = Color.LightGreen;
}
startRow = curRow;
foreach (Data.fnFlowCategoryResult category in categories)
{
curRow++;
ws.Cells[curRow, 1].Value = category.CategoryCode;
ws.Cells[curRow, 2].Value = category.Category;
int catRow = CategoryRow(ws, category.CategoryCode);
for (int curX = 4; curX <= lastCol; curX++)
{
ws.Cells[curRow, curX].Formula = $"={Column(curX)}{catRow}";
}
ws.Cells[curRow, 1].EntireRow.Locked = true;
}
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;
curRow++;
ws.Cells[curRow, 1].Value = Properties.Resources.TextPeriodTotal;
ws.Cells[curRow, 1].EntireRow.Font.Bold = true;
for (int curCol = 4; curCol <= lastCol; curCol++)
{
ws.Cells[curRow, curCol].Formula = $"=SUM({Column(curCol)}{startRow + 1}:{Column(curCol)}{curRow - 1})";
}
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
ws.Cells[curRow, 1].EntireRow.Locked = true;
}
