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();
}
// Обновляем курсы валют на панели и на вкладке 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));
}
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);
}
}
/// <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);
}
}
// Добавить купон / дивиденд
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();
}
}
}
}
// Заполняем лист с портфелем Тинькофф
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;
}
}
// Обновляем котировки на листах с портфелями других брокеров
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;
}
}
// Заполняем лист со списком ценных бумаг
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;
}
}
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;
}
}
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();
}
}
}
}
