Exemplo n.º 1
0
        public void FillListPapersFromExcel()
        {
            if (ListPapersChanged)
            {
                // Проверяем наличие нужного листа
                Excel.Worksheet ExcelListSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, ExcelListName, sh => { });
                if (ExcelListSheet != null)
                {
                    int max_rows = ExcelListSheet.UsedRange.Rows.Count;
                    ListPapers = new List <Paper>();

                    for (int _count = 2; _count <= max_rows; _count++)
                    //try
                    {
                        if (ExcelListSheet.Cells[_count, ExcelList_columnTicker].Text != "")
                        {
                            ListPapers.Add(new Paper()
                            {
                                ticker             = ExcelListSheet.Cells[_count, ExcelList_columnTicker].Text,
                                InstrumentTypeName = ExcelListSheet.Cells[_count, 2].Text,
                                name     = ExcelListSheet.Cells[_count, 3].Text,
                                lot      = ExcelListSheet.Cells[_count, 4].Value,
                                currency = ExcelListSheet.Cells[_count, 5].Text
                            });
                        }
                    }
                    //catch { }

                    ListPapersChanged = false;
                }
            }
        }
        public void ClearSettings()
        {
            Logging.DebugCaller(MethodBase.GetCurrentMethod(), string.Format(@"Выполнена команда: {0}", @"CRU-CLEAR_SETTINGS"));

            reinitialize();

            MSExcel.Clear();
        }
Exemplo n.º 3
0
        // Обновляем курсы валют на панели и на вкладке List
        private void ribbon_btnRefreshValuteCursClicked()
        {
            if (this.Application != null)
            {
                this.Application.StatusBar = "Загружаем курсы валют";
            }

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

            if (Ribbon != null)
            {
                // обновляем курс на панели
                Ribbon.lbUSD.Label = $"USD={Currencies.GetValuteCursByTicker("USD")} руб.";
                Ribbon.lbEUR.Label = $"EUR={Currencies.GetValuteCursByTicker("EUR")} руб.";
            }

            if ((this.Application != null) && (this.Application.ActiveWorkbook != null))
            {
                this.Application.StatusBar = $"Обновляем курсы валют на вкладке {ExcelListName}";

                // Находим лист List
                Excel.Worksheet ExcelListSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, ExcelListName, sh => { });

                if ((ExcelListSheet != null) && (ExcelList_columnValuteCurs != 0) && (ExcelList_columnTicker != 0))
                {
                    // обновляем курс на вкладке List
                    int max_columns = ExcelListSheet.UsedRange.Columns.Count;
                    int max_rows    = ExcelListSheet.UsedRange.Rows.Count;

                    foreach (var item in Currencies.payload.instruments)
                    {
                        for (int i = 1; i <= max_rows; i++)
                        {
                            if (ExcelListSheet.Cells[i, ExcelList_columnTicker].Text == item.ticker)
                            {
                                ExcelListSheet.Cells[i, ExcelList_columnValuteCurs] = item.ValuteCurs;
                                break;
                            }
                        }
                    }
                }

                this.Application.StatusBar = false;
            }
        }
        public void Import()
        {
            string command = @"CRU-IMPORT";

            try {
                reinitialize();

                MSExcel.Clear();
                clear();

                MSExcel.Import(@"settings.xls", Settings.MSExcel.FORMAT.HEAP);

                flash();
            } catch (System.Exception e) {
                Logging.AcEditorWriteException(e, command);

                Logging.ExceptionCaller(MethodBase.GetCurrentMethod(), e);
            }

            Logging.DebugCaller(MethodBase.GetCurrentMethod(), string.Format(@"Выполнена команда: {0}", command));
        }
Exemplo n.º 5
0
        private void listBoxFileSettings_SelectedIndexChanged(object sender, EventArgs ev)
        {
            TreeNode nodeBlock
            , nodeEntity;
            int iRow = -1;

            try {
                MSExcel.Clear();
                MSExcel.Import(m_listBoxFileSettings.SelectedItem.ToString(), MSExcel.FORMAT.HEAP);

                m_treeViewBlockDefinition.Nodes.Clear();

                foreach (string nameBlock in MSExcel.s_dictBlock.Keys)
                {
                    nodeBlock = m_treeViewBlockDefinition.Nodes.Add(nameBlock);

                    nodeBlock.Tag     = nameBlock;
                    nodeBlock.Checked = true;

                    foreach (KeyValuePair <KEY_ENTITY, EntityParser.ProxyEntity> pair in MSExcel.s_dictBlock[nameBlock].m_dictEntityParser)
                    {
                        nodeEntity = nodeBlock.Nodes.Add(pair.Value.m_name);

                        nodeEntity.Tag     = pair.Key;
                        nodeEntity.Checked = true;
                    }

                    foreach (MSExcel.POINT3D placement in MSExcel.s_dictBlock[nameBlock].m_ListReference)
                    {
                        iRow = m_clbBlockReferences.Items.Add(string.Format(@"{0} ({1})"
                                                                            , nameBlock, placement.ToString()), true);
                    }
                }

                //m_treeViewBlockDefinition.TopNode.Expand();
                m_treeViewBlockDefinition.SelectedNode = m_treeViewBlockDefinition.Nodes[0];
            } catch (Exception e) {
                Logging.ExceptionCaller(MethodBase.GetCurrentMethod(), e);
            }
        }
Exemplo n.º 6
0
    /// <summary>
    /// 保存到服务器,读出Excel中原始数据数据
    /// </summary>
    /// <returns></returns>
    private DataTable SaveToServer()
    {
        if (Request.Files.Count > 0 && Request.Files[0].FileName != "")
        {
            HttpPostedFile postedFile = Request.Files[0];
            byte[]         buffer     = new byte[postedFile.ContentLength];
            postedFile.InputStream.Read(buffer, 0, postedFile.ContentLength);
            postedFile.InputStream.Close();

            string folderPath = Server.MapPath("~/Framework/SingleTable/upfiles/");
            string savePath   = folderPath + DateTime.Now.ToString("yyyy-MM-dd_HHmmss") + ".xls";

            FileStream pFileStream = null;
            try
            {
                pFileStream = new FileStream(savePath, FileMode.OpenOrCreate);
                pFileStream.Write(buffer, 0, buffer.Length);
            }
            catch
            {
            }
            finally
            {
                if (pFileStream != null)
                {
                    pFileStream.Close();
                }
            }

            MSExcel   msE = new MSExcel();
            DataTable dt  = msE.ExcelToDataTable(savePath);
            return(dt);
        }
        else
        {
            return(null);
        }
    }
Exemplo n.º 7
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();
                    }
                }
            }
        }
Exemplo n.º 8
0
        // Заполняем лист с портфелем Тинькофф
        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;
            }
        }
Exemplo n.º 9
0
        // Обновляем котировки на листах с портфелями других брокеров
        private void ribbon_btnRefreshBrokersClicked()
        {
            // Портфели брокеров
            Dictionary <string, Dictionary <string, Orderbook> > Brokers = new Dictionary <string, Dictionary <string, Orderbook> >();

            foreach (var broker in ThisAddIn.BrokersName)
            {
                if ((this.Application != null) && (this.Application.ActiveWorkbook != null))
                {
                    Excel.Worksheet BrokerSheet        = null;
                    int             max_columns        = 0;
                    int             max_rows           = 0;
                    int             TickerNameColumn   = 0;
                    int             LastDateNameColumn = 0;
                    int             PriceNameColumn    = 0;
                    int             NominalNameColumn  = 0;

                    // Находим нужный лист
                    BrokerSheet = MSExcel.GetExcelSheet(this.Application.ActiveWorkbook, broker,
                                                        sh =>
                    {
                        // Находим нужные колонки
                        max_columns = sh.UsedRange.Columns.Count;

                        for (int j = 1; j <= max_columns; j++)
                        {
                            string name = sh.Cells[1, j].Text;

                            if (name.ToLower() == TickerName.ToLower())
                            {
                                TickerNameColumn = j;
                            }
                            if (name.ToLower() == LastDateName.ToLower())
                            {
                                LastDateNameColumn = j;
                            }
                            if (name.ToLower() == PriceName.ToLower())
                            {
                                PriceNameColumn = j;
                            }
                            if (name.ToLower() == NominalName.ToLower())
                            {
                                NominalNameColumn = j;
                            }
                        }
                    }
                                                        );

                    Dictionary <string, Orderbook> BrokerPapers = new Dictionary <string, Orderbook>();

                    if ((BrokerSheet != null) && (TickerNameColumn != 0))
                    {
                        ribbon_btnRefreshValuteCursClicked();

                        BrokerSheet.Activate();
                        max_rows = BrokerSheet.UsedRange.Rows.Count;

                        List <string> Tickers = new List <string>();

                        // заполняем список тикеров
                        for (int _count = 2; _count <= max_rows; _count++)
                        {
                            string ticker = BrokerSheet.Cells[_count, TickerNameColumn].Text;

                            if ((!Tickers.Contains(ticker)) && (!Currencies.IsValuteByTicker(ticker)))
                            {
                                Tickers.Add(ticker);
                            }
                        }

                        // запрашиваем котировки
                        this.Application.StatusBar = $"Запрашиваем котировки ценных бумаг для портфеля {broker}";

                        LoadBroker(broker, Tickers, BrokerPapers).GetAwaiter().GetResult();

                        Brokers.Add(broker, BrokerPapers);
                    }

                    // заполняем поля в excel
                    this.Application.StatusBar = $"Заполняем котировки ценных бумаг для портфеля {broker} в excel";

                    if (BrokerSheet != null)
                    {
                        for (int _count = 2; _count <= max_rows; _count++)
                        {
                            string ticker = BrokerSheet.Cells[_count, TickerNameColumn].Text;

                            if (BrokerPapers.ContainsKey(ticker))
                            {
                                BrokerSheet.Cells[_count, LastDateNameColumn] = DateTime.Today;
                                if (PriceNameColumn != 0)
                                {
                                    BrokerSheet.Cells[_count, PriceNameColumn] = BrokerPapers[ticker].lastPrice;
                                }
                                if ((NominalNameColumn != 0) && (BrokerPapers[ticker].faceValue > 0))
                                {
                                    BrokerSheet.Cells[_count, NominalNameColumn] = BrokerPapers[ticker].faceValue;
                                }
                            }
                        }
                    }
                }
            }

            if (this.Application != null)
            {
                this.Application.StatusBar = false;
            }
        }
Exemplo n.º 10
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;
            }
        }
Exemplo n.º 11
0
        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];
                        }
                    }
                }
            }
        }
Exemplo n.º 12
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;
            }
        }
Exemplo n.º 13
0
        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();
                    }
                }
            }
        }