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