public void ReadQuotes(DateTime fromDate, DateTime toDate)
{
using (ExcelClass xls = new ExcelClass())
{
try
{
_quotes = new Dictionary <DateTime, Quote>();
xls.OpenDocument(_quotesFileName, false);
string sDate = xls.GetCellStringValue(DateCol, FirstRow);
int i = FirstRow;
while (!string.IsNullOrEmpty(sDate))
{
DateTime date = StringFunctions.GetDate(sDate, DateFormat);
if (date > fromDate && date < toDate)
{
decimal open = StringFunctions.ParseDecimal(xls.GetCellStringValue(OpenCol, i));
decimal close = StringFunctions.ParseDecimal(xls.GetCellStringValue(CloseCol, i));
decimal high = StringFunctions.ParseDecimal(xls.GetCellStringValue(HighCol, i));
decimal low = StringFunctions.ParseDecimal(xls.GetCellStringValue(LowCol, i));
ulong volume = ulong.Parse(xls.GetCellStringValue(VolumeCol, i));
Quote quote = new Quote(date, open, close, high, low, volume, i);
_quotes.Add(date, quote);
}
i++;
sDate = xls.GetCellStringValue(DateCol, i);
}
}
finally
{
xls.CloseDocument(false);
}
}
}
private void ReadDeals(ref Dictionary <DateTime, Deal> someDeals, DateTime fromDate, DateTime toDate, string directionDealCol, string openDealCol, string reverseDealCol)
{
using (ExcelClass xls = new ExcelClass())
{
try
{
someDeals = new Dictionary <DateTime, Deal>();
xls.OpenDocument(_quotesFileName, false);
string sDate = xls.GetCellStringValue(DateCol, FirstRow);
int i = FirstRow;
Deal deal = null;
bool isFirstDeal = true;
while (!string.IsNullOrEmpty(sDate))
{
DateTime date = StringFunctions.GetDate(sDate, DateFormat);
if (date > fromDate && date < toDate)
{
string sDir = xls.GetCellStringValue(directionDealCol, i);
string sOpen = xls.GetCellStringValue(openDealCol, i);
string sReverse = xls.GetCellStringValue(reverseDealCol, i);
if (!string.IsNullOrEmpty(sDir) &&
!string.IsNullOrEmpty(sOpen) &&
!string.IsNullOrEmpty(sReverse))
{
decimal?reverse = StringFunctions.TryParseDecimal(sReverse);
if (isFirstDeal)
{
decimal?open = StringFunctions.TryParseDecimal(sOpen);
deal = new Deal(sDir, date.AddDays(-1), open);
deal.SetStopReverse(date, reverse);
isFirstDeal = false;
}
else
{
if (deal.IsSameDirection(sDir))
{
deal.SetStopReverse(date, reverse);
}
else
{
decimal?open = StringFunctions.TryParseDecimal(sOpen);
someDeals.Add(deal.OpenDate, deal);
deal = deal.Reverse(date.AddDays(-1), open);
deal.SetStopReverse(date, reverse);
}
}
}
}
i++;
sDate = xls.GetCellStringValue(DateCol, i);
}
}
finally
{
xls.CloseDocument(false);
}
}
}
private static List<Operation> GetOperations(string fileName)
{
List<Operation> opers = new List<Operation>();
ExcelClass xls = new ExcelClass();
try
{
int i = 1;
try
{
xls.OpenDocument(fileName, false);
string guid = xls.GetCellStringValue(1, 1);
string kod = xls.GetCellStringValue(2, 1);
string text = xls.GetCellStringValue(3, 1);
while (!string.IsNullOrEmpty(guid))
{
int kodI = int.Parse(kod);
opers.Add(new Operation(kodI, guid, text));
i++;
guid = xls.GetCellStringValue(1, i);
kod = xls.GetCellStringValue(2, i);
text = xls.GetCellStringValue(3, i);
}
}
finally
{
xls.CloseDocument();
}
_logger.WriteLine("Записано " + i + " позиций");
}
finally
{
xls.Dispose();
}
if (opers.Count <= 0)
{
return null;
}
return opers;
}
/// <summary>
/// Обработка Excel документов
/// </summary>
/// <param name="nameColName"></param>
/// <param name="titleColName"></param>
/// <param name="docColName"></param>
/// <param name="yearColName"></param>
public static void SelectXlsFiles(string nameColName, string titleColName, string docColName, string yearColName, ProgressBar bar, int type, Label status, Label nFiles)
{
_nameColName = nameColName;
_titleColName = titleColName;
_docColName = docColName;
_yearColName = yearColName;
string mess = "";
OpenFileDialog xlsBooks = new OpenFileDialog();
xlsBooks.Title = "Выберите файлы Excel с позициями";
xlsBooks.Multiselect = true;
xlsBooks.DefaultExt = "xlsx";
xlsBooks.Filter = "Файлы Excel (*.xls;*.xlsx)|*.xls;*.xlsx|All files (*.*)|*.*";
if (xlsBooks.ShowDialog() != DialogResult.OK)
return;
_userLog = new StreamWriter(_USER_LOG_FULL_PATH, false, Encoding.UTF8);
_userLog.WriteLine(DateTime.Now.ToLongTimeString());
_userLog.WriteLine("Начало работы");
_logger = new Logger();
_logger.WriteLine("----------------------------------------- NEW SESSION ----------------------------------------------");
ExcelClass xls = new ExcelClass();
try
{
bool cancel = false;
for (int i = 0; i < xlsBooks.FileNames.Length; i++)
{
status.Text = "Чтение файлов";
nFiles.Text = i.ToString();
Application.DoEvents();
List<Position> instruments = new List<Position>();
int iRow = _FIRST_ROW;
GroupElement group;
bool exist;
bool groupExist = false;
try
{
xls.OpenDocument(xlsBooks.FileNames[i], false);
_userLog.WriteLine("Открыт файл " + xlsBooks.FileNames[i]);
_logger.WriteLine("Открыт файл " + xlsBooks.FileNames[i]);
string shortFileName = GetShortFileName(xlsBooks.FileNames[i]);
_logger.WriteLine("Короткое имя группы - " + shortFileName);
string fullName = xls.GetCellStringValue("A", 1);
_logger.WriteLine("Полный путь группы - " + fullName);
group = new GroupElement(shortFileName, GetGroupParams(xls), fullName);
int oldId;
exist = GroupElement.Exist(group.Name, out oldId);
if (exist)
{
group = new GroupElement(oldId, group.Name, group.FullName);
groupExist = true;
_userLog.WriteLine("Группа уже существует. Найдена группа \"" + group.Name + "\"");
_logger.WriteLine("Группа уже существует. Найдена группа \"" + group.Name + "\"");
}
while (!xls.CellIsNullOrVoid(_nameColName, iRow) ||
!xls.CellIsNullOrVoid(_titleColName, iRow) ||
!xls.CellIsNullOrVoid(_docColName, iRow))
{
ProcessOneRow2(xls, iRow, ref mess, instruments, group,
shortFileName, type);
iRow++;
}
}
finally
{
xls.CloseDocument();
}
if (instruments.Count > 0)
{
if (!exist)
{
group.WriteToDb();
group.AddGeneralFolders(@"Справочники цеха 254\");
_userLog.WriteLine("Группа записана в БД");
_logger.WriteLine("Группа \"" + group.Name + "\" записана в БД");
}
}
bar.Maximum = instruments.Count;
bar.Value = 0;
status.Text = "Запись в БД";
foreach (Position pos in instruments)
{
int oldPosId;
if (Position.Exist(pos.Title, out oldPosId))
{
int dbGroupId = Position.GetGroupId(oldPosId);
if (group.Id != dbGroupId)
{
DialogResult result = MessageBox.Show("Позиция \"" + pos.Title + "\" уже существует и находится в другой группе (\"" + GroupElement.GetName(dbGroupId) + "\"). Перенести в группу из электронной таблицы (\"" + group.Name + "\") ?", "Внимание!", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
switch (result)
{
case DialogResult.Cancel:
cancel = true;
goto Cancel;
case DialogResult.Yes:
pos.SetNewGroup(oldPosId, group.Id);
break;
}
}
string message;
Dictionary<int, string> differentParams;
if (pos.IsSimilarPositionParams(oldPosId, out message, out differentParams))
{
_userLog.WriteLine(message);
_logger.WriteLine(message);
DialogResult result = MessageBox.Show(message + Environment.NewLine + "Обновить параметры?", "Внимание!", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
switch (result)
{
case DialogResult.Cancel:
cancel = true;
goto Cancel;
case DialogResult.Yes:
Position.EditParametrs(oldPosId, differentParams);
break;
}
}
}
else
{
string message;
pos.IsSimilarGroupParams(group.GetParamCodes(), out message);
_userLog.WriteLine(message);
pos.WriteToDb2();
}
bar.Increment(1);
Application.DoEvents();
}
_userLog.WriteLine("Записано " + instruments.Count + " позиций");
_userLog.Flush();
_userLog.WriteLine();
_logger.WriteLine("Записано " + instruments.Count + " позиций");
}
Cancel:
if (cancel)
{
_userLog.WriteLine("***Операция отменена пользователем");
_userLog.Flush();
_userLog.WriteLine();
_logger.WriteLine("***Операция отменена пользователем");
}
}
finally
{
xls.Dispose();
_userLog.Close();
}
Process.Start(_USER_LOG_FULL_PATH);
MessageBox.Show("Готово!");
}
private static void SetRow(Dictionary <string, string> kodes, string date, string name, string orientation, string buyCost, string stopCost, string profitLoss)
{
string file = GetFilename(name, kodes);
ExcelClass xls = new ExcelClass();
try
{
xls.OpenDocument(file, false);
try
{
string csvDate;
int iRow;
string lastNum = xls.GetCellStringValue(11, 1);
if (string.IsNullOrEmpty(lastNum))
{
iRow = 2;
csvDate = xls.GetCellStringValue(3, iRow);
while (!string.IsNullOrEmpty(csvDate))
{
iRow++;
csvDate = xls.GetCellStringValue(3, iRow);
}
iRow--;
}
else
{
iRow = int.Parse(lastNum) - 1;
}
csvDate = GetCsvDate(xls.GetCellStringValue(3, iRow));
while (true)
{
if (csvDate == date)
{
SetNumValue(xls, 11, iRow, orientation);
SetNumValue(xls, 12, iRow, buyCost);
SetNumValue(xls, 13, iRow, stopCost);
SetNumValue(xls, 14, iRow, profitLoss);
xls.SetCellValue(11, 1, iRow.ToString());
break;
}
iRow--;
if (iRow == 0)
{
MessageBox.Show("Нет такой даты (" + date + ") в " + name);
break;
}
csvDate = GetCsvDate(xls.GetCellStringValue(3, iRow));
}
}
finally
{
xls.CloseDocument(true);
}
}
finally
{
xls.Dispose();
}
}
private static void SetKodesList(Dictionary<string, string> dict)
{
OpenFileDialog xlsBooks = new OpenFileDialog
{
Title = "Выберите файл Excel с кодами",
DefaultExt = "xlsx",
Filter = "Файлы Excel (*.xls;*.xlsx)|*.xls;*.xlsx|All files (*.*)|*.*"
};
if (xlsBooks.ShowDialog() != DialogResult.OK)
return;
ExcelClass xls = new ExcelClass();
try
{
xls.OpenDocument(xlsBooks.FileName, false);
try
{
const int iCol = 1;
int iRow = 1;
string key = xls.GetCellStringValue(iCol, iRow);
while (!string.IsNullOrEmpty(key))
{
dict.Add(key, xls.GetCellStringValue(2, iRow));
iRow++;
key = xls.GetCellStringValue(iCol, iRow);
}
}
finally
{
xls.CloseDocument(false);
}
}
finally
{
xls.Dispose();
}
}
private static void ReadPage(string fileName, Dictionary <string, string> kodes)
{
ExcelClass xls = new ExcelClass();
try
{
xls.OpenDocument(fileName, false);
try
{
string date = GetDate(xls.GetCellStringValue(2, 2));
string fileDate = Path.GetFileNameWithoutExtension(fileName);
if (string.IsNullOrEmpty(date))
{
date = fileDate;
}
else
{
if (date != fileDate)
{
MessageBox.Show(date + '\n' + fileDate + '\n' + "Даты разные!");
}
}
int iRow = 9;
string name = xls.GetCellStringValue(2, iRow);
while (!string.IsNullOrEmpty(name))
{
string orientation = xls.GetCellStringValue(3, iRow);
if (kodes.ContainsKey(name))
{
string buyCost = xls.GetCellStringValue(4, iRow);
string stopCost = xls.GetCellStringValue(5, iRow);
string profitLoss = xls.GetCellStringValue(6, iRow);
SetRow(kodes, date, name, orientation, buyCost, stopCost, profitLoss);
}
else
{
if (orientation != "ЛОНГ" ||
orientation != "Л0НГ" ||
orientation != "шорт")
{
break;
}
MessageBox.Show(name);
}
iRow++;
name = xls.GetCellStringValue(2, iRow);
}
}
finally
{
xls.CloseDocument(false);
}
}
finally
{
xls.Dispose();
}
}
private void ReadDeals(ref Dictionary<DateTime, Deal> someDeals, DateTime fromDate, DateTime toDate, string directionDealCol, string openDealCol, string reverseDealCol)
{
using (ExcelClass xls = new ExcelClass())
{
try
{
someDeals = new Dictionary<DateTime, Deal>();
xls.OpenDocument(_quotesFileName, false);
string sDate = xls.GetCellStringValue(DateCol, FirstRow);
int i = FirstRow;
Deal deal = null;
bool isFirstDeal = true;
while (!string.IsNullOrEmpty(sDate))
{
DateTime date = StringFunctions.GetDate(sDate, DateFormat);
if (date > fromDate && date < toDate)
{
string sDir = xls.GetCellStringValue(directionDealCol, i);
string sOpen = xls.GetCellStringValue(openDealCol, i);
string sReverse = xls.GetCellStringValue(reverseDealCol, i);
if (!string.IsNullOrEmpty(sDir) &&
!string.IsNullOrEmpty(sOpen) &&
!string.IsNullOrEmpty(sReverse))
{
double? reverse = StringFunctions.TryParse(sReverse);
if (isFirstDeal)
{
double? open = StringFunctions.TryParse(sOpen);
deal = new Deal(sDir, date.AddDays(-1), open);
deal.SetStopReverse(date, reverse);
isFirstDeal = false;
}
else
{
if (deal.IsSameDirection(sDir))
{
deal.SetStopReverse(date, reverse);
}
else
{
double? open = StringFunctions.TryParse(sOpen);
someDeals.Add(deal.OpenDate, deal);
deal = deal.Reverse(date.AddDays(-1), open);
deal.SetStopReverse(date, reverse);
}
}
}
}
i++;
sDate = xls.GetCellStringValue(DateCol, i);
}
}
finally
{
xls.CloseDocument(false);
}
}
}
public void ReadQuotes(DateTime fromDate, DateTime toDate)
{
using (ExcelClass xls = new ExcelClass())
{
try
{
_quotes = new Dictionary <DateTime, Quote>();
xls.OpenDocument(_quotesFileName, false);
string sDate = xls.GetCellStringValue(DateCol, FirstRow);
int i = FirstRow;
while (!string.IsNullOrEmpty(sDate))
{
DateTime date = StringFunctions.GetDate(sDate, DateFormat);
if (date > fromDate && date < toDate)
{
double open = double.Parse(xls.GetCellStringValue(OpenCol, i));
double close = double.Parse(xls.GetCellStringValue(CloseCol, i));
double high = double.Parse(xls.GetCellStringValue(HighCol, i));
double low = double.Parse(xls.GetCellStringValue(LowCol, i));
ulong volume = ulong.Parse(xls.GetCellStringValue(VolumeCol, i));
Quote quote = new Quote(date, open, close, high, low, volume, i);
_quotes.Add(date, quote);
}
i++;
sDate = xls.GetCellStringValue(DateCol, i);
}
}
finally
{
xls.CloseDocument(false);
}
}
}
