private List <FMELWEntity> ReadFile(string filePath, List <FMELWEntity> listFMELW)
{
List <FMELWEntity> list = new List <FMELWEntity>();
if (!File.Exists(filePath))
{
string msg = string.Format("the file [{0}] is not exist.", filePath);
Logger.Log(msg, Logger.LogType.Error);
return(null);
}
try
{
using (ExcelApp eApp = new ExcelApp(false, false))
{
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(eApp, filePath);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (wSheet == null)
{
string msg = "Worksheet could not be created. Check that your office installation and project reference are correct!";
Logger.Log(msg, Logger.LogType.Error);
return(null);
}
//wSheet.Name = "FM";
int lastUsedRow = wSheet.UsedRange.Row + wSheet.UsedRange.Rows.Count - 1;//193=1+193-1
using (ExcelLineWriter reader = new ExcelLineWriter(wSheet, 2, 2, ExcelLineWriter.Direction.Right))
{
while (reader.Row <= lastUsedRow)
{
FMELWEntity fm = new FMELWEntity();
fm.ISIN = reader.ReadLineCellText();//web.StandardCongenial
reader.PlaceNext(reader.Row, 4);
fm.IssuingAuthority = reader.ReadLineValue2();
reader.PlaceNext(reader.Row + 1, 2);
if (!string.IsNullOrEmpty(fm.ISIN.Trim()) && !string.IsNullOrEmpty(fm.IssuingAuthority.Trim()))
{
list.Add(fm);
}
}
}
}
return(list);
}
catch (Exception ex)
{
string msg = string.Format("read xls file error :{0}", ex.ToString());
Logger.Log(msg);
return(null);
}
}
private void ExtractDataStepTwo(FMELWEntity fm)
{
string isu_nm = fm.IssuingAuthority.Trim();
string std_cd = fm.ISIN.Trim();
string strUrl = @"http://isin.krx.co.kr/srch/srch.do?method=srchPopup11";
string stdcd_type = "11";
string mod_del_cd = "";
string pershr_isu_prc = "";
string isu_shrs = "";
string strPostData = string.Format("stdcd_type={0}&std_cd={1}&mod_del_cd={2}&isu_nm={3}&pershr_isu_prc={4}&isu_shrs={5}", stdcd_type, std_cd, mod_del_cd, isu_nm, pershr_isu_prc, isu_shrs);
string strPageSource = string.Empty;
//string strReleaseForm = string.Empty;
try
{
AdvancedWebClient wc = new AdvancedWebClient();
HtmlDocument htc = new HtmlDocument();
strPageSource = WebClientUtil.GetPageSource(wc, strUrl, 300000, strPostData);
if (string.IsNullOrEmpty(strPageSource))
{
Logger.Log(string.Format("return response is null,when query ric:{0}", std_cd));
if (!listNoResponse.Contains(std_cd))
{
listNoResponse.Add(std_cd);
}
return;
}
htc.LoadHtml(strPageSource);
HtmlNodeCollection tables = htc.DocumentNode.SelectNodes(".//table");
if (tables.Count < 5)
{
Logger.Log(string.Format("tables.count<5,so missing data in the pageSource ,current ric:", isu_nm));
if (!listNoResponse.Contains(isu_nm))
{
listNoResponse.Add(isu_nm);
}
return;
}
HtmlNode table1 = tables[1];
HtmlNodeCollection trs1 = table1.SelectNodes(".//tr"); //11*2=22
HtmlNode table2 = tables[2];
HtmlNodeCollection trs2 = table2.SelectNodes(".//tr"); //0*2+1*1+2*1=4
HtmlNode table3 = tables[3];
HtmlNodeCollection trs3 = table3.SelectNodes(".//tr"); //3*1=3
if (trs1.Count < 11 || trs2.Count < 3 || trs3.Count < 3)
{
Logger.Log(string.Format("trs1.count is too small,so missing data in the pageSource ,current ric:", isu_nm));
if (!listNoResponse.Contains(isu_nm))
{
listNoResponse.Add(isu_nm);
}
return;
}
fm.ReleaseForm = FormatInnerText(trs1[10].SelectNodes(".//td")[0].InnerText);
//strReleaseForm = FormatInnerText(trs1[9].SelectNodes(".//td")[0].InnerText);
//if (!strReleaseForm.Equals("공모"))
//{
// return;
//}
//first table left on ELW website
fm.QuanityOfWarrants = FormatInnerText(trs1[6].SelectNodes(".//td")[0].InnerText); //quanity of warrants
fm.IssuePrice = FormatInnerText(trs1[7].SelectNodes(".//td")[0].InnerText); //issue price
//first table right on ELW website
fm.Ticker = FormatInnerText(trs1[1].SelectNodes(".//td")[1].InnerText); //ticker
fm.Issuer = FormatInnerText(trs1[2].SelectNodes(".//td")[1].InnerText); //issuer
fm.IssueDate = FormatInnerText(trs1[6].SelectNodes(".//td")[1].InnerText); //issuer date
fm.MatDate = FormatInnerText(trs1[7].SelectNodes(".//td")[1].InnerText); //mat date
fm.ConversionRatio = FormatInnerText(trs1[9].SelectNodes(".//td")[1].InnerText); //conversion ratio
//second table on ELW website
fm.XSovereignIssuingAuthority = FormatInnerText(trs2[0].SelectNodes(".//td")[1].InnerText); //X
fm.YStockIndexTypes = FormatInnerText(trs2[1].SelectNodes(".//td")[0].InnerText); //Y
//third table on ELW website
fm.KoreaWarrantName = FormatInnerText(trs3[0].SelectNodes(".//td")[0].InnerText);//korea warrant name
}
catch (Exception ex)
{
if (!listError.Contains(std_cd))
{
listError.Add(std_cd);
}
Logger.Log(string.Format("Error found in function: {0}. Exception message: {1}", "GetELWExtractEntityStepOne", ex.Message));
return;
}
}