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