void FillProfit(Excel.Worksheet ProfitSheet, int Row, string ticker, ProfitTicker item, Excel.Worksheet HistorySheet) { if ((ProfitSheet != null) && (Row > 1) && (ticker != null) && (ticker != "")) { if ((item == null) || (item.Count == 0)) //-V3024 { // позиции из "доходности" нет в портфелях или кол-во равно 0 - обнуляем (распродал) ProfitSheet.Cells[Row, 5] = ""; ProfitSheet.Cells[Row, 9] = ""; ProfitSheet.Cells[Row, 14] = ""; } else { ProfitSheet.Cells[Row, 1] = ticker; ProfitSheet.Cells[Row, 2] = item.name; ProfitSheet.Cells[Row, 4] = item.InstrumentTypeName; if (item.Count > 0) { if (item.IsCurrency) { ProfitSheet.Cells[Row, 9] = item.Count; } else { ProfitSheet.Cells[Row, 5] = item.Count; } } if (item.Nominal > 0) { ProfitSheet.Cells[Row, 6] = item.Nominal; } ProfitSheet.Cells[Row, 7] = item.currency; if ((item.Summa > 0) && (!item.IsCurrency)) { ProfitSheet.Cells[Row, 9] = item.Summa; } if (item.MarketSumma > 0) { ProfitSheet.Cells[Row, 14] = item.MarketSumma; } if (Row > 10) { // копируем формулы из предыдущей строки ProfitSheet.Cells[Row, 8].FormulaR1C1 = ProfitSheet.Cells[Row - 1, 8].FormulaR1C1; ProfitSheet.Cells[Row, 10].FormulaR1C1 = ProfitSheet.Cells[Row - 1, 10].FormulaR1C1; ProfitSheet.Cells[Row, 11].FormulaR1C1 = ProfitSheet.Cells[Row - 1, 11].FormulaR1C1; ProfitSheet.Cells[Row, 12].FormulaR1C1 = ProfitSheet.Cells[Row - 1, 12].FormulaR1C1; ProfitSheet.Cells[Row, 13].FormulaR1C1 = ProfitSheet.Cells[Row - 1, 13].FormulaR1C1; ProfitSheet.Cells[Row, 15].FormulaR1C1 = ProfitSheet.Cells[Row - 1, 15].FormulaR1C1; ProfitSheet.Cells[Row, 16].FormulaR1C1 = ProfitSheet.Cells[Row - 1, 16].FormulaR1C1; ProfitSheet.Cells[Row, 17].FormulaR1C1 = ProfitSheet.Cells[Row - 1, 17].FormulaR1C1; ProfitSheet.Cells[Row, 18].FormulaR1C1 = ProfitSheet.Cells[Row - 1, 18].FormulaR1C1; ProfitSheet.Cells[Row, 19].FormulaR1C1 = ProfitSheet.Cells[Row - 1, 19].FormulaR1C1; ProfitSheet.Cells[Row, 20].FormulaR1C1 = ProfitSheet.Cells[Row - 1, 20].FormulaR1C1; ProfitSheet.Cells[Row, 21].FormulaR1C1 = ProfitSheet.Cells[Row - 1, 21].FormulaR1C1; } // дополняем историю if (HistorySheet != null) { int max_rows_history = HistorySheet.UsedRange.Rows.Count; DateTime date = DateTime.Today; int _count = 0; for (int j = 2; j <= max_rows_history; j++) { if (ticker == MSExcel.GetRangeValue <string>(HistorySheet.Cells[j, 1])) { if (date == MSExcel.GetRangeValue <DateTime>(HistorySheet.Cells[j, 2])) { _count = j; break; } } } if (_count == 0) { _count = max_rows_history + 1; } if (_count > 1) { HistorySheet.Cells[_count, 1] = ticker; HistorySheet.Cells[_count, 2] = date; if (item.IsCurrency) { HistorySheet.Cells[_count, 3] = 1; } else { HistorySheet.Cells[_count, 3] = 0; } HistorySheet.Cells[_count, 4] = ProfitSheet.Cells[Row, 5]; HistorySheet.Cells[_count, 5] = ProfitSheet.Cells[Row, 7]; HistorySheet.Cells[_count, 6] = ProfitSheet.Cells[Row, 8]; HistorySheet.Cells[_count, 7] = ProfitSheet.Cells[Row, 9]; HistorySheet.Cells[_count, 8] = ProfitSheet.Cells[Row, 10]; HistorySheet.Cells[_count, 9] = ProfitSheet.Cells[Row, 12]; HistorySheet.Cells[_count, 10] = ProfitSheet.Cells[Row, 14]; HistorySheet.Cells[_count, 11] = ProfitSheet.Cells[Row, 15]; } } } } }
// Обновить все данные, связанные с котировками private void ribbon_btnProfitClicked() { ribbon_btnRefreshAllClicked(); if ((this.Application != null) && (this.Application.ActiveWorkbook != null)) { Excel.Worksheet ProfitSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, ExcelProfitName, sh => { }); Excel.Worksheet TinkoffSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, ExcelTinkoffName, sh => { }); Excel.Worksheet HistorySheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, ExcelHistoryName, sh => { }); Excel.Worksheet BrokerSheet = null; if (HistorySheet == null) { // лист не найден - добавляем HistorySheet = (Excel.Worksheet) this.Application.ActiveWorkbook.Sheets.Add(); HistorySheet.Name = ExcelHistoryName; HistorySheet.Activate(); // заголовок int _count = 1; HistorySheet.Cells[_count, 1] = "Ticker"; HistorySheet.Cells[_count, 2] = "Date"; HistorySheet.Cells[_count, 3] = "IsCurrency"; HistorySheet.Cells[_count, 4] = "Count"; HistorySheet.Cells[_count, 5] = "Valute"; HistorySheet.Cells[_count, 6] = "Price"; HistorySheet.Cells[_count, 7] = "Summa"; HistorySheet.Cells[_count, 8] = "Dividend"; HistorySheet.Cells[_count, 9] = "MarketPrice"; HistorySheet.Cells[_count, 10] = "MarketSumma"; HistorySheet.Cells[_count, 11] = "Curs"; } if (ProfitSheet != null) { Dictionary <string, ProfitTicker> Profits = new Dictionary <string, ProfitTicker>(); int max_rows; int max_columns; string ticker; this.Application.StatusBar = $"Собираем портфели с листов данного excel-файла"; // портфель Тинькофф if (TinkoffSheet != null) { max_rows = TinkoffSheet.UsedRange.Rows.Count; for (int i = 2; i <= max_rows; i++) { ticker = TinkoffSheet.Cells[i, 2].Text; if ((ticker != null) && (ticker != "")) { double value; ProfitTicker item = null; if (Profits.ContainsKey(ticker)) { item = Profits[ticker]; } else { item = new ProfitTicker(); item.ticker = ticker; Profits.Add(ticker, item); } item.name = TinkoffSheet.Cells[i, 3].Text; item.InstrumentTypeName = TinkoffSheet.Cells[i, 4].Text; if (TinkoffSheet.Cells[i, 5].Value is double) { value = TinkoffSheet.Cells[i, 5].Value; } else { double.TryParse(TinkoffSheet.Cells[i, 5].Text, out value); } item.Count += value; item.currency = TinkoffSheet.Cells[i, 6].Text; if (TinkoffSheet.Cells[i, 8].Value is double) { value = TinkoffSheet.Cells[i, 8].Value; } else { double.TryParse(TinkoffSheet.Cells[i, 8].Text, out value); } item.Summa += value; if (TinkoffSheet.Cells[i, 14].Value is double) { value = TinkoffSheet.Cells[i, 14].Value; } else { double.TryParse(TinkoffSheet.Cells[i, 14].Text, out value); } item.MarketSumma += value; } } } foreach (var broker in BrokersName) { // находим лист BrokerSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, broker, sh => { }); // портфель брокера if (BrokerSheet != null) { max_rows = BrokerSheet.UsedRange.Rows.Count; for (int i = 2; i <= max_rows; i++) { ticker = BrokerSheet.Cells[i, 2].Text; if ((ticker != null) && (ticker != "")) { double value; ProfitTicker item = null; if (Profits.ContainsKey(ticker)) { item = Profits[ticker]; } else { item = new ProfitTicker(); item.ticker = ticker; Profits.Add(ticker, item); } item.name = BrokerSheet.Cells[i, 3].Text; item.InstrumentTypeName = BrokerSheet.Cells[i, 4].Text; if (BrokerSheet.Cells[i, 7].Value is double) { value = BrokerSheet.Cells[i, 7].Value; } else { double.TryParse(BrokerSheet.Cells[i, 7].Text, out value); } item.Count += value; if (BrokerSheet.Cells[i, 8].Value is double) { value = BrokerSheet.Cells[i, 8].Value; } else { double.TryParse(BrokerSheet.Cells[i, 8].Text, out value); } item.Nominal = value; item.currency = BrokerSheet.Cells[i, 9].Text; if (BrokerSheet.Cells[i, 11].Value is double) { value = BrokerSheet.Cells[i, 11].Value; } else { double.TryParse(BrokerSheet.Cells[i, 11].Text, out value); } item.Summa += value; if (BrokerSheet.Cells[i, 17].Value is double) { value = BrokerSheet.Cells[i, 17].Value; } else { double.TryParse(BrokerSheet.Cells[i, 17].Text, out value); } item.MarketSumma += value; } } } } ProfitSheet.Activate(); max_rows = ProfitSheet.UsedRange.Rows.Count; int count_rows = 1; this.Application.StatusBar = $"Заполняем лист {ExcelProfitName}"; // перебираем строки в excel: если есть в портфелях - заполняем, если нет - обнуляем for (int i = 2; i <= max_rows; i++) { ticker = ProfitSheet.Cells[i, 1].Text; if ((ticker == null) || (ticker == "")) { break; } count_rows++; if (Profits.ContainsKey(ticker)) { Profits[ticker].IsFound = true; FillProfit(ProfitSheet, count_rows, ticker, Profits[ticker], HistorySheet); } else { FillProfit(ProfitSheet, count_rows, ticker, null, HistorySheet); } this.Application.StatusBar = $"Заполняем лист {ExcelProfitName} - {ticker}"; } // все, что не найдено - добавляем в конец таблицы foreach (var item in Profits.Values) { if ((!item.IsFound) && (item.Count != 0)) //-V3024 { ticker = item.ticker; count_rows++; FillProfit(ProfitSheet, count_rows, ticker, item, HistorySheet); if (this.Application != null) { this.Application.StatusBar = $"Заполняем лист {ExcelProfitName} - {ticker}"; } } } // итоги count_rows++; ProfitSheet.Cells[count_rows, 5].FormulaR1C1 = $"=SUBTOTAL(9,R[{2- count_rows}]C:R[-1]C)"; ProfitSheet.Cells[count_rows, 17].FormulaR1C1 = $"=SUBTOTAL(9,R[{2 - count_rows}]C:R[-1]C)"; max_columns = ProfitSheet.UsedRange.Columns.Count; if (max_rows > count_rows) { ProfitSheet.Range[ProfitSheet.Cells[count_rows + 1, 1], ProfitSheet.Cells[max_rows, max_columns]].ClearContents(); } max_rows = count_rows; // оформляем MSExcel.RangeBold(ProfitSheet.Range[ProfitSheet.Cells[1, 1], ProfitSheet.Cells[max_rows, max_columns]], false); MSExcel.RangeBold(ProfitSheet.Range[ProfitSheet.Cells[1, 1], ProfitSheet.Cells[1, max_columns]], true); MSExcel.RangeBold(ProfitSheet.Range[ProfitSheet.Cells[max_rows, 1], ProfitSheet.Cells[max_rows, max_columns]], true); MSExcel.RangeWrapText(ProfitSheet.Range[ProfitSheet.Cells[1, 1], ProfitSheet.Cells[1, max_columns]]); MSExcel.RangeBorder(ProfitSheet.Range[ProfitSheet.Cells[1, 1], ProfitSheet.Cells[max_rows, max_columns]]); MSExcel.RangeAutoFilter(ProfitSheet.Cells); MSExcel.RangeVerticalAlignment(ProfitSheet.Cells, Excel.XlVAlign.xlVAlignCenter); // обновляем семейный бюджет string FamilyBudgetFile = MSExcel.GetNamedRangeValue <string>(this.Application.ActiveWorkbook, "FamilyBudgetFile"); string FamilyBudgetInvestField = MSExcel.GetNamedRangeValue <string>(this.Application.ActiveWorkbook, "FamilyBudgetInvest"); string FamilyBudgetToInvestField = MSExcel.GetNamedRangeValue <string>(this.Application.ActiveWorkbook, "FamilyBudgetToInvest"); if ((FamilyBudgetFile != "") && (FamilyBudgetInvestField != "") && (FamilyBudgetToInvestField != "") && File.Exists(FamilyBudgetFile)) { Excel.Workbook curWorkbook = this.Application.ActiveWorkbook; // открываем файл семейного бюджета Excel.Workbook familyWorkbook = this.Application.Workbooks.Open(FamilyBudgetFile); if (familyWorkbook != null) { double value; // считываем затраты на инвестиции value = MSExcel.GetNamedRangeValue <double>(familyWorkbook, FamilyBudgetToInvestField); MSExcel.SetNamedRangeValue <double>(curWorkbook, FamilyBudgetToInvestField, value); if (System.Windows.Forms.MessageBox.Show("Обновить результат инвестиций в семейном бюджете ?", "Внимание!", System.Windows.Forms.MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes) { // заполняем результат инвестиций value = MSExcel.GetNamedRangeValue <double>(curWorkbook, FamilyBudgetInvestField); MSExcel.SetNamedRangeValue <double>(familyWorkbook, FamilyBudgetInvestField, value); familyWorkbook.RefreshAll(); } familyWorkbook.Save(); familyWorkbook.Close(); } if (curWorkbook != null) { curWorkbook.Activate(); } } } this.Application.ActiveWorkbook.RefreshAll(); } if (this.Application != null) { this.Application.StatusBar = false; } }