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