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); } } }