コード例 #1
0
        // Добавить купон / дивиденд
        private void ribbon_btnDividendClicked()
        {
            // выбрать лист с дивидендами
            MSExcel.SetExcelSheet(this.Application.ActiveWorkbook, ExcelDividendName);

            if (IsDividendActiveSheet(this.Application.ActiveWorkbook))
            {
                if (this.Application.Selection != null)
                {
                    Excel.Range cells = this.Application.Selection;

                    Excel.Worksheet DividendSheet = this.Application.ActiveWorkbook.ActiveSheet;

                    string ticker  = "";
                    string account = "";
                    string name    = "";

                    if (cells.Row > 1)
                    {
                        ticker  = DividendSheet.Cells[cells.Row, 2].Text;
                        account = DividendSheet.Cells[cells.Row, 1].Text;
                        name    = DividendSheet.Cells[cells.Row, 3].Text;
                    }

                    FormDividend dlg1 = new FormDividend();
                    dlg1.thisAddIn = this;
                    dlg1.ticker    = ticker;
                    dlg1.seektxt   = name;
                    dlg1.account   = account;

                    if ((dlg1.ShowDialog() == DialogResult.OK) && (dlg1.summa > 0))
                    {
                        // добавляем строку в excel
                        int max_rows    = DividendSheet.UsedRange.Rows.Count;
                        int max_columns = DividendSheet.UsedRange.Columns.Count;

                        max_rows++;

                        DividendSheet.Cells[max_rows, 1] = dlg1.account;
                        DividendSheet.Cells[max_rows, 2] = dlg1.ticker;
                        DividendSheet.Cells[max_rows, 3] = dlg1.name;
                        if (dlg1.operation == FormDividend.DividendType.Cupon)
                        {
                            DividendSheet.Cells[max_rows, 4] = "Купон";
                        }
                        else
                        {
                            DividendSheet.Cells[max_rows, 4] = "Дивиденд";
                        }
                        DividendSheet.Cells[max_rows, 5]             = DateTime.Today;
                        DividendSheet.Cells[max_rows, 6]             = dlg1.summa;
                        DividendSheet.Cells[max_rows, 7]             = dlg1.currency;
                        DividendSheet.Cells[max_rows, 8].FormulaR1C1 = DividendSheet.Cells[max_rows - 1, 8].FormulaR1C1;
                        DividendSheet.Cells[max_rows, 9].FormulaR1C1 = DividendSheet.Cells[max_rows - 1, 9].FormulaR1C1;

                        MSExcel.RangeBold(DividendSheet.Range[DividendSheet.Cells[max_rows, 1], DividendSheet.Cells[max_rows, max_columns]], false);
                        MSExcel.RangeBorder(DividendSheet.Range[DividendSheet.Cells[max_rows, 1], DividendSheet.Cells[max_rows, max_columns]]);

                        dlg1.Dispose();
                    }
                }
            }
        }
コード例 #2
0
ファイル: TinkoffPortfolio.cs プロジェクト: sprokushev/CSharp
        // Заполняем лист с портфелем Тинькофф
        private void ribbon_btnTinkoffClicked()
        {
            if ((this.Application != null) && (this.Application.ActiveWorkbook != null))
            {
                // Проверяем наличие нужного листа
                Excel.Worksheet TinkoffSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, ExcelTinkoffName, sh => { });;

                if (TinkoffSheet == null)
                {
                    // лист не найден - добавляем
                    TinkoffSheet      = (Excel.Worksheet) this.Application.ActiveWorkbook.Sheets.Add();
                    TinkoffSheet.Name = ExcelTinkoffName;
                }

                if (TinkoffSheet != null)
                {
                    this.Application.StatusBar = "Загружаем курсы валют";

                    LoadValuteCurs().GetAwaiter().GetResult();

                    this.Application.StatusBar = "Загружаем список ценных бумаг из портфеля Тинькофф";

                    LoadTinkoff().GetAwaiter().GetResult();

                    this.Application.StatusBar = $"Заполняем портфель Тинькофф  на листе {ExcelTinkoffName}";

                    // заполняем лист
                    int _count = 0;
                    TinkoffSheet.Activate();
                    TinkoffSheet.Cells.ClearContents();

                    // заголовок
                    _count++;
                    TinkoffSheet.Cells[_count, 1]  = "Счет";
                    TinkoffSheet.Cells[_count, 2]  = "Тикер";
                    TinkoffSheet.Cells[_count, 3]  = "Ценная бумага";
                    TinkoffSheet.Cells[_count, 4]  = "Тип";
                    TinkoffSheet.Cells[_count, 5]  = "Кол-во";
                    TinkoffSheet.Cells[_count, 6]  = "Валюта";
                    TinkoffSheet.Cells[_count, 7]  = "Цена покупки (средняя)";
                    TinkoffSheet.Cells[_count, 8]  = "Стоимость покупки (средняя)";
                    TinkoffSheet.Cells[_count, 9]  = "Дата котировки";
                    TinkoffSheet.Cells[_count, 10] = "Котировка в валюте";
                    TinkoffSheet.Cells[_count, 11] = "Изменение котировки";
                    TinkoffSheet.Cells[_count, 12] = "Курс к рублю";
                    TinkoffSheet.Cells[_count, 13] = "Котировка в рублях";
                    TinkoffSheet.Cells[_count, 14] = "Рыночная стоимость в валюте";
                    TinkoffSheet.Cells[_count, 15] = "Рыночная стоимость в рублях";

                    // перебираю наличные
                    foreach (var account in TinkoffCurrencyAccounts)
                    {
                        foreach (var pos in account.Value.currencies)
                        {
                            _count++;
                            TinkoffSheet.Cells[_count, 1]  = account.Key;
                            TinkoffSheet.Cells[_count, 2]  = pos.ticker;
                            TinkoffSheet.Cells[_count, 3]  = pos.name;
                            TinkoffSheet.Cells[_count, 4]  = pos.InstrumentTypeName;
                            TinkoffSheet.Cells[_count, 5]  = pos.Count;
                            TinkoffSheet.Cells[_count, 6]  = pos.currency;
                            TinkoffSheet.Cells[_count, 7]  = pos.Price;
                            TinkoffSheet.Cells[_count, 8]  = pos.Summa;
                            TinkoffSheet.Cells[_count, 9]  = DateTime.Today;
                            TinkoffSheet.Cells[_count, 10] = pos.MarketPrice;
                            TinkoffSheet.Cells[_count, 11] = pos.MarketPrice - pos.Price;
                            TinkoffSheet.Cells[_count, 12] = pos.ValuteCurs;
                            TinkoffSheet.Cells[_count, 13] = pos.RubPrice;
                            TinkoffSheet.Cells[_count, 14] = pos.MarketSumma;
                            TinkoffSheet.Cells[_count, 15] = pos.RubSumma;
                        }
                    }

                    // перебираю ценные бумаги
                    foreach (var account in TinkoffPaperAccounts)
                    {
                        foreach (var pos in account.Value.positions)
                        {
                            if (pos.instrumentType == "Currency")
                            {
                                continue;
                            }
                            if (pos.averagePositionPrice == null)
                            {
                                continue;
                            }

                            _count++;
                            TinkoffSheet.Cells[_count, 1]  = account.Key;
                            TinkoffSheet.Cells[_count, 2]  = pos.ticker;
                            TinkoffSheet.Cells[_count, 3]  = pos.name;
                            TinkoffSheet.Cells[_count, 4]  = pos.InstrumentTypeName;
                            TinkoffSheet.Cells[_count, 5]  = pos.Count;
                            TinkoffSheet.Cells[_count, 6]  = pos.currency;
                            TinkoffSheet.Cells[_count, 7]  = pos.Price;
                            TinkoffSheet.Cells[_count, 8]  = pos.Summa;
                            TinkoffSheet.Cells[_count, 9]  = DateTime.Today;
                            TinkoffSheet.Cells[_count, 10] = pos.MarketPrice;
                            TinkoffSheet.Cells[_count, 11] = pos.MarketPrice - pos.Price;
                            TinkoffSheet.Cells[_count, 12] = pos.ValuteCurs;
                            TinkoffSheet.Cells[_count, 13] = pos.RubPrice;
                            TinkoffSheet.Cells[_count, 14] = pos.MarketSumma;
                            TinkoffSheet.Cells[_count, 15] = pos.RubSumma;
                        }
                    }

                    // итоги
                    //TinkoffSheet.Cells[_footer_row, 5].FormulaR1C1 = $"=SUBTOTAL(9,R[{-stocks_rows}]C:R[-1]C)";
                    //TinkoffSheet.Cells[_footer_row, 14].FormulaR1C1 = $"=SUBTOTAL(9,R[{-all_rows}]C:R[-1]C)";

                    // оформляем
                    int max_columns = TinkoffSheet.UsedRange.Columns.Count;
                    int max_rows    = TinkoffSheet.UsedRange.Rows.Count;
                    MSExcel.RangeBold(TinkoffSheet.Range[TinkoffSheet.Cells[1, 1], TinkoffSheet.Cells[max_rows, max_columns]], false);
                    MSExcel.RangeBold(TinkoffSheet.Range[TinkoffSheet.Cells[1, 1], TinkoffSheet.Cells[1, max_columns]], true);
                    MSExcel.RangeWrapText(TinkoffSheet.Range[TinkoffSheet.Cells[1, 1], TinkoffSheet.Cells[1, max_columns]]);
                    MSExcel.RangeBorder(TinkoffSheet.Range[TinkoffSheet.Cells[1, 1], TinkoffSheet.Cells[max_rows, max_columns]]);
                    MSExcel.RangeAutoFilter(TinkoffSheet.Cells);
                    MSExcel.RangeVerticalAlignment(TinkoffSheet.Cells, Excel.XlVAlign.xlVAlignCenter);
                }
            }

            if (this.Application != null)
            {
                this.Application.StatusBar = false;
            }
        }
コード例 #3
0
        // Обновить все данные, связанные с котировками
        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;
            }
        }
コード例 #4
0
        // Заполняем лист со списком ценных бумаг
        public void ribbon_btnMarketListClicked()
        {
            if ((this.Application != null) && (this.Application.ActiveWorkbook != null))
            {
                // Проверяем наличие нужного листа
                Excel.Worksheet ExcelListSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, ExcelListName, sh => { });

                if (ExcelListSheet == null)
                {
                    // лист не найден - добавляем
                    ExcelListSheet      = (Excel.Worksheet) this.Application.ActiveWorkbook.Sheets.Add();
                    ExcelListSheet.Name = ExcelListName;
                }

                if (ExcelListSheet != null)
                {
                    // заполняем лист
                    int _count = 0;
                    ExcelListSheet.Activate();
                    ExcelListSheet.Cells.ClearContents();

                    // заголовок
                    _count++;
                    ExcelListSheet.Cells[_count, ExcelList_columnTicker] = "Ticker";
                    ExcelListSheet.Cells[_count, 2] = "Type";
                    ExcelListSheet.Cells[_count, 3] = "Name";
                    ExcelListSheet.Cells[_count, 4] = "Lot";
                    ExcelListSheet.Cells[_count, 5] = "Currency";
                    ExcelListSheet.Cells[_count, ExcelList_columnValuteCurs] = "ValuteCurs";

                    this.Application.StatusBar = "Загружаем курсы валют";

                    LoadValuteCurs().GetAwaiter().GetResult();

                    this.Application.StatusBar = $"Заполняем список валют на листе {ExcelListName}";

                    // валюты
                    if ((Currencies != null) && (Currencies.status == "Ok") && (Currencies.payload != null) && (Currencies.payload.instruments != null))
                    {
                        foreach (var item in Currencies.payload.instruments)
                        {
                            if (item.ticker != "")
                            {
                                _count++;
                                ExcelListSheet.Cells[_count, ExcelList_columnTicker] = item.ticker;
                                ExcelListSheet.Cells[_count, 2] = item.InstrumentTypeName;
                                ExcelListSheet.Cells[_count, 3] = item.name;
                                ExcelListSheet.Cells[_count, 4] = item.lot;
                                ExcelListSheet.Cells[_count, 5] = item.currency;
                                ExcelListSheet.Cells[_count, ExcelList_columnValuteCurs] = item.ValuteCurs;
                            }
                        }
                    }

                    this.Application.StatusBar = "Загружаем список ценных бумаг"; //-V3008

                    LoadMarket().GetAwaiter().GetResult();

                    this.Application.StatusBar = $"Заполняем список ценных бумаг на листе {ExcelListName}";

                    // ценные бумаги
                    if (ListPapers != null)
                    {
                        foreach (var item in ListPapers)
                        {
                            _count++;
                            ExcelListSheet.Cells[_count, ExcelList_columnTicker] = item.ticker;
                            ExcelListSheet.Cells[_count, 2] = item.InstrumentTypeName;
                            ExcelListSheet.Cells[_count, 3] = item.name;
                            ExcelListSheet.Cells[_count, 4] = item.lot;
                            ExcelListSheet.Cells[_count, 5] = item.currency;
                        }
                    }

                    // оформляем
                    int max_columns = ExcelListSheet.UsedRange.Columns.Count;
                    int max_rows    = ExcelListSheet.UsedRange.Rows.Count;
                    MSExcel.RangeAutoFit(ExcelListSheet.Cells);
                    MSExcel.RangeAutoFilter(ExcelListSheet.Cells);
                    MSExcel.RangeBold((Excel.Range)ExcelListSheet.Range[ExcelListSheet.Cells[1, 1], ExcelListSheet.Cells[1, max_columns]], true);
                    MSExcel.RangeBorder((Excel.Range)ExcelListSheet.Range[ExcelListSheet.Cells[1, 1], ExcelListSheet.Cells[max_rows, max_columns]]);
                }
            }

            if (this.Application != null)
            {
                this.Application.StatusBar = false;
            }
        }
コード例 #5
0
ファイル: Portfolio.cs プロジェクト: sprokushev/CSharp
        private void BrokerOperation(FormPortfolio.OperType operation)
        {
            if (IsBrokerActiveSheet(this.Application.ActiveWorkbook))
            {
                if (this.Application.Selection != null)
                {
                    Excel.Range cells = this.Application.Selection;

                    Excel.Worksheet BrokerSheet = this.Application.ActiveWorkbook.ActiveSheet;

                    string ticker  = "";
                    string account = "";
                    string name    = "";
                    if (cells.Row > 1)
                    {
                        ticker  = BrokerSheet.Cells[cells.Row, 2].Text;
                        account = BrokerSheet.Cells[cells.Row, 1].Text;
                        name    = BrokerSheet.Cells[cells.Row, 3].Text;
                    }

                    FormPortfolio dlg1 = new FormPortfolio();
                    dlg1.thisAddIn = this;
                    dlg1.ticker    = ticker;
                    dlg1.seektxt   = name;
                    dlg1.account   = account;
                    dlg1.operation = operation;

                    if ((dlg1.ShowDialog() == DialogResult.OK) && (dlg1.count > 0))
                    {
                        // добавляем строку в excel
                        int max_rows    = BrokerSheet.UsedRange.Rows.Count;
                        int max_columns = BrokerSheet.UsedRange.Columns.Count;

                        max_rows++;

                        BrokerSheet.Cells[max_rows, 1] = dlg1.account;
                        BrokerSheet.Cells[max_rows, 2] = dlg1.ticker;
                        BrokerSheet.Cells[max_rows, 3] = dlg1.name;
                        BrokerSheet.Cells[max_rows, 4] = dlg1.InstrumentTypeName;

                        switch (dlg1.operation)
                        {
                        case FormPortfolio.OperType.Buy:
                            BrokerSheet.Cells[max_rows, 5]  = "Покупка";
                            BrokerSheet.Cells[max_rows, 7]  = dlg1.count;
                            BrokerSheet.Cells[max_rows, 11] = dlg1.summa;
                            break;

                        case FormPortfolio.OperType.PlanBuy:
                            BrokerSheet.Cells[max_rows, 5]  = "ПЛАН";
                            BrokerSheet.Cells[max_rows, 7]  = dlg1.count;
                            BrokerSheet.Cells[max_rows, 11] = dlg1.summa;
                            break;

                        case FormPortfolio.OperType.Sell:
                        default:
                            BrokerSheet.Cells[max_rows, 5]  = "Продажа";
                            BrokerSheet.Cells[max_rows, 7]  = -dlg1.count;
                            BrokerSheet.Cells[max_rows, 11] = -dlg1.summa;
                            break;
                        }

                        BrokerSheet.Cells[max_rows, 6] = DateTime.Today;

                        if (dlg1.nominal > 0)
                        {
                            BrokerSheet.Cells[max_rows, 8] = dlg1.nominal;
                        }

                        BrokerSheet.Cells[max_rows, 9] = dlg1.currency;
                        BrokerSheet.Cells[max_rows, 10].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 10].FormulaR1C1;
                        BrokerSheet.Cells[max_rows, 13]             = dlg1.price;
                        BrokerSheet.Cells[max_rows, 14].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 14].FormulaR1C1;
                        BrokerSheet.Cells[max_rows, 15].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 15].FormulaR1C1;
                        BrokerSheet.Cells[max_rows, 16].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 16].FormulaR1C1;
                        BrokerSheet.Cells[max_rows, 17].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 17].FormulaR1C1;
                        BrokerSheet.Cells[max_rows, 18].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 18].FormulaR1C1;
                        BrokerSheet.Cells[max_rows, 19].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 19].FormulaR1C1;

                        MSExcel.RangeBold(BrokerSheet.Range[BrokerSheet.Cells[max_rows, 1], BrokerSheet.Cells[max_rows, max_columns]], false);
                        MSExcel.RangeBorder(BrokerSheet.Range[BrokerSheet.Cells[max_rows, 1], BrokerSheet.Cells[max_rows, max_columns]]);

                        dlg1.Dispose();
                    }
                }
            }
        }