public void WriteAllDeals() { using (ExcelClass xls = new ExcelClass()) { try { xls.OpenDocument(_quotesFileName, false); int i = FirstRow; foreach (KeyValuePair <DateTime, Deal> pair in _deals) { string sDate = xls.GetCellStringValue(DateCol, i); while (!string.IsNullOrEmpty(sDate)) { DateTime date = StringFunctions.GetDate(sDate, DateFormat); if (date.AddDays(-1) == pair.Key) { SetDealStops (xls, ref i, pair.Value, NewDirectionDealCol, NewOpenDealCol, NewReverseDealCol, NewProfitLostCol); break; } i++; sDate = xls.GetCellStringValue(DateCol, i); } } } finally { xls.CloseDocumentSave(); } } }
private static void SetRomanIssuer(LittleTableViewer props) { string shortFileName = TradeInstrument.GetIssuerCode(props.Instrument); string fullPath = SetFile(shortFileName); using (ExcelClass xls = new ExcelClass()) { xls.OpenDocument(fullPath, false); int firstFreeRow = int.Parse(xls.GetCellStringValue(1, 1)); xls.SetCellValue(9, firstFreeRow, Deal.GetDirection(props.IsLong)); xls.SetCellValue(10, firstFreeRow, props.OpenValue.ToString()); xls.SetCellValue(11, firstFreeRow, props.StopValue.ToString()); xls.SetCellValue(12, firstFreeRow, props.Profit.ToString()); xls.CloseDocumentSave(); } }
private static void SetQuote(Quote quote, string code) { string fullPath = SetFile(code); using (ExcelClass xls = new ExcelClass()) { xls.OpenDocument(fullPath, false); int firstFreeRow = int.Parse(xls.GetCellStringValue(1, 1)); xls.SetCellValue(1, firstFreeRow, quote.Date.ToShortDateString()); xls.SetCellValue(2, firstFreeRow, quote.Open.ToString()); xls.SetCellValue(3, firstFreeRow, quote.Close.ToString()); xls.SetCellValue(4, firstFreeRow, quote.Low.ToString()); xls.SetCellValue(5, firstFreeRow, quote.High.ToString()); xls.SetCellValue(6, firstFreeRow, quote.Volume.ToString()); xls.SetCellValue(7, firstFreeRow, quote.Lot.ToString()); xls.SetCellValue(1, 1, (++firstFreeRow).ToString()); xls.CloseDocumentSave(); } }
public void WriteSimpleDeals() { using (ExcelClass xls = new ExcelClass()) { try { _simpleDeals = new Dictionary <DateTime, Deal>(); xls.OpenDocument(_quotesFileName, false); string sDate = xls.GetCellStringValue(DateCol, FirstRow); int i = FirstRow; int iOpen = i; Deal deal = null; Deal romanDeal = null; bool isCurrentDeal = false; bool isFirstDeal = true; decimal? stop; decimal? lastStop = 0; DateTime prevDay = new DateTime(1, 1, 1); foreach (KeyValuePair <DateTime, Quote> quote in _quotes) { DateTime date = quote.Key; if (!isCurrentDeal) { if (!_deals.ContainsKey(prevDay)) { prevDay = date; continue; } romanDeal = _deals[prevDay]; isCurrentDeal = true; deal = new Deal(romanDeal.IsLong, romanDeal.OpenDate, romanDeal.OpenValue); } if (!isFirstDeal) { stop = romanDeal.GetStop(date); if (stop == null) { deal.Close(prevDay, lastStop); Quote quoteRow = _quotes[romanDeal.OpenDate]; int row = quoteRow.Row; SetDealStops (xls, ref row, deal, SimpleDirectionDealCol, SimpleOpenDealCol, SimpleReverseDealCol, SimpleProfitLostCol); _simpleDeals.Add(date, deal); isCurrentDeal = false; } else { deal.SetStopReverse(date, stop); if (deal.IsLong && quote.Value.Close < stop || !deal.IsLong && quote.Value.Close > stop) { Deal newDeal = deal.Reverse(date, quote.Value.Close); Quote quoteRow = _quotes[romanDeal.OpenDate]; int row = quoteRow.Row; SetDealStops (xls, ref row, deal, SimpleDirectionDealCol, SimpleOpenDealCol, SimpleReverseDealCol, SimpleProfitLostCol); _simpleDeals.Add(date, deal); deal = newDeal; isCurrentDeal = false; } } lastStop = stop; } prevDay = date; isFirstDeal = false; } } finally { xls.CloseDocumentSave(); } } }
private void AddToExcel(string[,] texts, string excelPath, string excelName) { ExcelClass xls = new ExcelClass(); try { xls.NewDocument(); string fullPath = Path.Combine(excelPath, excelName); xls.SaveDocument(fullPath); xls.OpenDocument(fullPath, false); for (int i = 0; i < texts.GetLength(0); i++) { for (int j = 0; j < texts.GetLength(1); j++) { xls.SetCellValue(j + 1, i + 1, texts[i, j].Trim()); } } xls.SetAutoFit("B:B"); xls.SetAutoFit("C:C"); xls.SetAutoFit("D:D"); xls.SetAutoFit("E:E"); xls.SetAutoFit("F:F"); } finally { xls.CloseDocumentSave(); xls.Dispose(); } }
private void bExcelExportTo_Click(object sender, EventArgs e) { SaveFileDialog xlsF = new SaveFileDialog(); xlsF.Title = "Выберите файлы Excel с позициями"; xlsF.DefaultExt = "xlsx"; xlsF.Filter = "Файлы Excel (*.xls;*.xlsx)|*.xls;*.xlsx|All files (*.*)|*.*"; if (xlsF.ShowDialog() != DialogResult.OK) return; ExcelClass xls = new ExcelClass(); try { xls.NewDocument(); xls.SaveDocument(xlsF.FileName); xls.OpenDocument(xlsF.FileName, false); try { string workshop = tbWorkshop.Text; List<string> tps = new List<string>(); Dictionary<string, string> param = new Dictionary<string, string>(); param.Add("T5_CE", workshop); SqlOracle.Sel( "select distinct t5_tp from table_5 where t5_ce = :T5_CE order by t5_tp", param, out tps); label16.Text = tps.Count.ToString(); int itp = 1; int rowN = 2; foreach (string tp in tps) { label10.Text = itp.ToString(); xls.SetCellValue("A", rowN, tp); List<int> opers = new List<int>(); param = new Dictionary<string, string>(); param.Add("T5_TP", tp + "%"); SqlOracle.Sel( "select distinct t5_no from table_5 where t5_tp like :T5_TP order by t5_no", param, out opers); label17.Text = opers.Count.ToString(); int ioper = 1; foreach (int oper in opers) { label11.Text = ioper.ToString(); xls.SetCellValue("B", rowN, oper.ToString()); List<int> pers = new List<int>(); param = new Dictionary<string, string>(); param.Add("T5_TP", tp + "%"); param.Add("T5_NO", oper.ToString()); SqlOracle.Sel( "select distinct t5_np from table_5 where t5_tp like :T5_TP and t5_no = :T5_NO order by t5_np", param, out pers); label18.Text = pers.Count.ToString(); int iper = 1; foreach (int per in pers) { label12.Text = iper.ToString(); xls.SetCellValue("C", rowN, per.ToString()); if (oper == 35 && per == 30) { } param = new Dictionary<string, string>(); param.Add("T5_TP", tp + "%"); param.Add("T5_NO", oper.ToString()); param.Add("T5_NP", per.ToString()); int btm; SqlOracle.Sel( "select distinct t5_bt from table_5 where t5_tp like :T5_TP and t5_no = :T5_NO and t5_np = :T5_NP", param, out btm); xls.SetCellValue("D", rowN, btm.ToString()); byte[] vo = SqlOracle.GetBytes( "select distinct t5_vo from table_5 where t5_tp like :T5_TP and t5_no = :T5_NO and t5_np = :T5_NP", param); if (vo.Length > 1) { for (int i = 0; i < vo.Length/2; i++) { int i1 = vo[i*2]; int i2 = vo[i*2 + 1]; if (i1 == 0 && i2 == 0) { continue; } string h1 = Convert.ToString(i1, 16); string h2 = Convert.ToString(i2, 16); if (h1.Length < 2) { h1 = "0" + h1; } string h = h2 + h1; int nn = int.Parse(h, NumberStyles.AllowHexSpecifier); param = new Dictionary<string, string>(); param.Add("T2", nn.ToString()); string pr; SqlOracle.Sel("select t2_r1 from table_2 where t2_nn = :T2", param, out pr); string name; SqlOracle.Sel("select t2_nm from table_2 where t2_nn = :T2", param, out name); string title; SqlOracle.Sel("select t2_oboz from table_2 where t2_nn = :T2", param, out title); int gr; SqlOracle.Sel("select t2_ng from table_2 where t2_nn = :T2", param, out gr); if (pr != null) { xls.SetCellValue("E", rowN, pr); xls.SetCellValue("F", rowN, name); xls.SetCellValue("G", rowN, title); xls.SetCellValue("H", rowN, gr.ToString()); xls.SetCellValue("I", rowN, nn.ToString()); } else { xls.SetCellValue("E", rowN, "Инструмент не найден в базе"); xls.SetCellValue("I", rowN, nn.ToString()); } rowN++; } } iper++; Application.DoEvents(); rowN++; } ioper++; rowN++; } itp++; rowN++; } } finally { xls.CloseDocumentSave(); } } finally { xls.Dispose(); } }
private static void SetXls(string url, string site, ImageClass imageClass, string origName) { string[] imaName = GetPictureDate(url).Split('.'); string fileName = ""; for (int i = 0; i < imaName.Length - 1; i++) { fileName += imaName[i] + '.'; } fileName += "xlsx"; string directory = Path.Combine(Application.StartupPath, new Uri(site).Host, "rAndreevLists"); if (!File.Exists(Path.Combine(directory, fileName))) { imageClass.RecognizeToExcel(directory, fileName); ExcelClass xls = new ExcelClass(); try { xls.OpenDocument(Path.Combine(directory, fileName), false); xls.AddPicture(origName, "H1"); } finally { xls.CloseDocumentSave(); xls.Dispose(); } } }
public void WriteSimpleDeals() { using (ExcelClass xls = new ExcelClass()) { try { _simpleDeals = new Dictionary<DateTime, Deal>(); xls.OpenDocument(_quotesFileName, false); string sDate = xls.GetCellStringValue(DateCol, FirstRow); int i = FirstRow; int iOpen = i; Deal deal = null; Deal romanDeal = null; bool isCurrentDeal = false; bool isFirstDeal = true; double? currentReverse = 0.0; double? stop; double? lastStop = 0; DateTime prevDay = new DateTime(1, 1, 1); foreach (KeyValuePair<DateTime, Quote> quote in _quotes) { DateTime date = quote.Key; if (!isCurrentDeal) { if (!_deals.ContainsKey(prevDay)) { prevDay = date; continue; } romanDeal = _deals[prevDay]; isCurrentDeal = true; deal = new Deal(romanDeal.IsLong, romanDeal.OpenDate, romanDeal.OpenValue); } if (!isFirstDeal) { stop = romanDeal.GetStop(date); if (stop == null) { deal.Close(prevDay, lastStop); Quote quoteRow = _quotes[romanDeal.OpenDate]; int row = quoteRow.Row; SetDealStops (xls, ref row, deal, SimpleDirectionDealCol, SimpleOpenDealCol, SimpleReverseDealCol, SimpleProfitLostCol); _simpleDeals.Add(date, deal); isCurrentDeal = false; } else { deal.SetStopReverse(date, stop); if (deal.IsLong && quote.Value.Close < stop || !deal.IsLong && quote.Value.Close > stop) { Deal newDeal = deal.Reverse(date, quote.Value.Close); Quote quoteRow = _quotes[romanDeal.OpenDate]; int row = quoteRow.Row; SetDealStops (xls, ref row, deal, SimpleDirectionDealCol, SimpleOpenDealCol, SimpleReverseDealCol, SimpleProfitLostCol); _simpleDeals.Add(date, deal); deal = newDeal; isCurrentDeal = false; } } lastStop = stop; } prevDay = date; isFirstDeal = false; } } finally { xls.CloseDocumentSave(); } } }
public void WriteAllDeals() { using (ExcelClass xls = new ExcelClass()) { try { xls.OpenDocument(_quotesFileName, false); int i = FirstRow; foreach (KeyValuePair<DateTime, Deal> pair in _deals) { string sDate = xls.GetCellStringValue(DateCol, i); while (!string.IsNullOrEmpty(sDate)) { DateTime date = StringFunctions.GetDate(sDate, DateFormat); if (date.AddDays(-1) == pair.Key) { SetDealStops (xls, ref i, pair.Value, NewDirectionDealCol, NewOpenDealCol, NewReverseDealCol, NewProfitLostCol); break; } i++; sDate = xls.GetCellStringValue(DateCol, i); } } } finally { xls.CloseDocumentSave(); } } }