/// <summary> /// Read FM file /// </summary> /// <param name="fmFilePath">The full path of the FM file.</param> /// <returns>The main Ric list.</returns> private List <string> GetMainRic(string fmFilePath) { List <string> ricList = new List <string>(); using (ExcelApp app = new ExcelApp(false, false)) { if (!File.Exists(fmFilePath)) { Logger.Log(string.Format("Can't find the FM file in the path {0} .", fmFilePath)); return(ricList); } var workbook = ExcelUtil.CreateOrOpenExcelFile(app, fmFilePath); Worksheet worksheet = workbook.Worksheets[1] as Worksheet; string ric = null; int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 5, 3, ExcelLineWriter.Direction.Down)) { ric = reader.ReadLineCellText(); while (!string.IsNullOrEmpty(ric)) { ricList.Add(ric); ric = reader.ReadLineCellText(); } } workbook.Close(false, workbook.FullName, false); } return(ricList); }
private void ReadFile(List <WrtQuaNotHK> list, string path) { using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, path); Worksheet worksheet = workbook.Worksheets[1] as Worksheet; int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 5, 7, ExcelLineWriter.Direction.Right)) { string code = string.Empty; string listingDate = string.Empty; string issuePrice = string.Empty; string furtherIssueSize = string.Empty; for (int i = 1; i <= lastUsedRow; i++) { listingDate = reader.ReadLineCellText(); if (listingDate != null && listingDate.Trim() != "") { if (NextWorkDay().ToString("M/d/yyyy").Equals(listingDate.Trim())) { reader.PlaceNext(reader.Row, 2); code = reader.ReadLineCellText(); reader.PlaceNext(reader.Row, 8); issuePrice = reader.ReadLineCellText(); reader.PlaceNext(reader.Row, 9); furtherIssueSize = reader.ReadLineCellText().Replace(",", ""); if (code == null || issuePrice == null || furtherIssueSize == null) { string msg = String.Format("value (row,clo)=({0},{1}) is null!", reader.Row, reader.Col); Logger.Log(msg, Logger.LogType.Error); MessageBox.Show(msg); continue; } WrtQuaNotHK wrt = new WrtQuaNotHK(); wrt.LogicalKey = list.Count + 1; wrt.SecondaryID = code.Trim(); wrt.ISIN = GetISIN(wrt.SecondaryID); wrt.SecondaryIDType = "HONG KONG CODE"; wrt.EHIssueQuantity = "N"; wrt.IssueQuantity = GetIssueQuantity(wrt.SecondaryID, furtherIssueSize); wrt.Action = "I"; wrt.Note1Type = "O"; wrt.TranchePrice = issuePrice.Replace("HKD", "").Trim(); wrt.Note1 = string.Format("Further issue with issue price HKD {0} on {1}.", issuePrice.Replace("HKD", "").Trim(), NextWorkDay().ToString("dd-MMM-yyyy")); wrt.TrancheListingDate = DateTime.Parse(listingDate).ToString("dd-MMM-yyyy"); list.Add(wrt); } } reader.PlaceNext(reader.Row + 1, 7); } } } }
private void FillInListTemplate(List <IssueAssetAddTemplate> listIAATemplate) { using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, hkQAAddFileName); Worksheet worksheet = workbook.Worksheets[1] as Worksheet; int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 2, 1, ExcelLineWriter.Direction.Right)) { string ric = string.Empty; string warrantIssueQuantity = string.Empty; //18 string tranche = string.Empty; //17 string trancheListingAate = string.Empty; //16 for (int i = 1; i <= lastUsedRow; i++) { ric = reader.ReadLineCellText(); if (ric == null || ric.Trim() == "") { continue; } foreach (var item in listIAATemplate) { if (!(item.HongKongCode.Trim() + ".HK").Equals(ric.Trim())) { continue; } reader.PlaceNext(reader.Row, 16); trancheListingAate = DateTime.Parse(reader.ReadLineCellText()).ToString("dd-MMM-yyyy"); reader.PlaceNext(reader.Row, 17); tranche = reader.ReadLineCellText(); reader.PlaceNext(reader.Row, 18); warrantIssueQuantity = reader.ReadLineValue2(); if (trancheListingAate == null || tranche == null || warrantIssueQuantity == null) { string msg = String.Format("value (row,clo)=({0},{1}) is null!", reader.Row, reader.Col); Logger.Log(msg, Logger.LogType.Error); MessageBox.Show(msg); continue; } item.TrancheListingDate = trancheListingAate; item.TranchePrice = tranche; item.WarrantIssueQuantity = warrantIssueQuantity; } reader.PlaceNext(reader.Row + 1, 1); } } } }
private void GetHongKongCodeToList(List <IssueAssetAddTemplate> listIAATemplate) { using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, hkIAAddFileName); Worksheet worksheet = workbook.Worksheets[1] as Worksheet; int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 2, 1, ExcelLineWriter.Direction.Right)) { string ric = string.Empty; string type = string.Empty; string category = string.Empty; string rcsAssrtClass = string.Empty; string warrantIssue = string.Empty; for (int i = 1; i <= lastUsedRow; i++) { ric = reader.ReadLineCellText(); reader.PlaceNext(reader.Row, 2); type = reader.ReadLineCellText(); reader.PlaceNext(reader.Row, 3); category = reader.ReadLineCellText(); reader.PlaceNext(reader.Row, 4); rcsAssrtClass = reader.ReadLineCellText(); reader.PlaceNext(reader.Row, 5); warrantIssue = reader.ReadLineCellText(); if (ric == null || type == null || category == null || rcsAssrtClass == null || warrantIssue == null) { string msg = String.Format("value (row,clo)=({0},{1}) is null!", reader.Row, reader.Col); Logger.Log(msg, Logger.LogType.Error); MessageBox.Show(msg); continue; } //if (string.IsNullOrWhiteSpace(ric)) // continue; IssueAssetAddTemplate iaat = new IssueAssetAddTemplate(); iaat.HongKongCode = ric; iaat.Type = type; iaat.Category = category; iaat.RcsAssetClass = rcsAssrtClass; iaat.WarrantIssuer = warrantIssue; listIAATemplate.Add(iaat); reader.PlaceNext(reader.Row + 1, 1); } } } }
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 generateTXTFile(Workbook warGuideBWorkbook) { Worksheet insertV3Sheet = ExcelUtil.GetWorksheet(configObj.WorksheetWarrantGuideBInsertV3, warGuideBWorkbook); if (insertV3Sheet == null) { LogMessage(string.Format("There's no worksheet {0} in workbook {1}", configObj.WorksheetWarrantGuideBInsertV3, warGuideBWorkbook.FullName)); } int lastUsedRow = insertV3Sheet.UsedRange.Row + insertV3Sheet.UsedRange.Rows.Count - 1; var sb = new StringBuilder(); sb.AppendLine("HKSE;29-JUN-29;7:1;TPS;"); sb.AppendLine("ROW80_5;ROW80_6;ROW80_7;ROW80_8;ROW80_9;ROW80_10;ROW80_11;ROW80_12;ROW80_13;ROW80_14;ROW80_15;ROW80_16;ROW80_17;ROW80_18;ROW80_19;ROW80_20;ROW80_21;ROW80_22;ROW80_23;ROW80_24;ROW80_25;"); sb.Append("HK/WTS06;"); using (var reader = new ExcelLineWriter(insertV3Sheet, 86, 1, ExcelLineWriter.Direction.Right)) { string line = string.Empty; int pageNum = -1; while (reader.Row <= lastUsedRow) { line += reader.ReadLineCellText().PadRight(9); line += reader.ReadLineCellText().PadRight(16); line += reader.ReadLineCellText().PadLeft(9); line += reader.ReadLineCellText().PadLeft(8); line += reader.ReadLineCellText().PadRight(10); line += reader.ReadLineCellText().PadLeft(9); line += reader.ReadLineCellText().PadLeft(8); line += ";"; sb.Append(line); line = string.Empty; if (reader.Row % 20 == 5) { pageNum = reader.Row / 20 + 1; sb.AppendLine(string.Format(" PREV <HK/WTS{0}> / NEXT <HK/WTS{1}>;", (pageNum - 1).ToString("D2"), (pageNum + 1).ToString("D2"))); line = string.Format("HK/WTS{0};", (pageNum + 1).ToString("D2")); } reader.PlaceNext(reader.Row + 1, 1); } } using (var sw = new StreamWriter(Path.Combine(configObj.WarrantGuideBTxtFileDir, "war_guideB.txt"), false, Encoding.UTF8)) { sw.WriteLine(sb.ToString()); sw.AutoFlush = true; sw.Close(); AddResult("war_guideB.txt", Path.Combine(configObj.WarrantGuideBTxtFileDir, "war_guideB.txt"), "file"); } }
public void getNameMap() { using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, NAME_MAP_FILE_PATH); var worksheet = (Worksheet)workbook.Worksheets[1]; int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right)) { while (reader.Row <= lastUsedRow) { string englishOrigName = reader.ReadLineCellText().ToUpper(); if (!englishNameMap.ContainsKey(englishOrigName)) { englishNameMap.Add(englishOrigName, new HKBrokerPageNameMap()); englishNameMap[englishOrigName].longName = reader.ReadLineCellText().ToUpper(); englishNameMap[englishOrigName].shortName = reader.ReadLineCellText().ToUpper(); } else { reader.PlaceNext(reader.Row, reader.Col + 2); } reader.MoveNext(); string chineseOrigName = reader.ReadLineCellText().ToUpper(); if (!chineseNameMap.ContainsKey(chineseOrigName)) { chineseNameMap.Add(chineseOrigName, new HKBrokerPageNameMap()); chineseNameMap[chineseOrigName].longName = reader.ReadLineCellText().ToUpper(); chineseNameMap[chineseOrigName].shortName = reader.ReadLineCellText().ToUpper(); } reader.PlaceNext(reader.Row + 1, 1); } } workbook.Close(false, workbook.FullName, false); } }
public TradingInfo GetTradingInfoFromMain01_10File(int pageNum, int lineNumEachPage) { TradingInfo tradingInfo = new TradingInfo(); tradingInfo.StockList = new List <StockInfo>(); using (ExcelApp excelApp = new ExcelApp(false, false)) { Workbook orOpenExcelFile = ExcelUtil.CreateOrOpenExcelFile(excelApp, configObj.HKMAIN01_10_Workbook); Worksheet worksheet = ExcelUtil.GetWorksheet(this.configObj.HKMAIN01_10_Worksheet, orOpenExcelFile); if (worksheet == null) { LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", (object)configObj.HKMAIN01_10_Worksheet, (object)orOpenExcelFile.Name)); } using ( ExcelLineWriter excelLineWriter = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right)) { tradingInfo.DateStr = ExcelUtil.GetRange(1, 1, worksheet).Text.ToString(); excelLineWriter.PlaceNext(4, 1); while (excelLineWriter.Row < pageNum * lineNumEachPage + 4) { if (ExcelUtil.GetRange(excelLineWriter.Row, 1, worksheet).Text != null) { string str = ExcelUtil.GetRange(excelLineWriter.Row, 1, worksheet).Text.ToString().Trim(); if (str.StartsWith("<")) { tradingInfo.StockList.Add(new StockInfo() { Ric = excelLineWriter.ReadLineCellText(), StockName = excelLineWriter.ReadLineCellText(), Shares = excelLineWriter.ReadLineCellText(), Turnover = excelLineWriter.ReadLineCellText() }); excelLineWriter.PlaceNext(excelLineWriter.Row + 1, 1); } else if (str != string.Empty) { tradingInfo.DesignatedSecuritiesRecordingSum = ExcelUtil.GetRange(excelLineWriter.Row, 1, worksheet).Text.ToString().Trim(); tradingInfo.DesignatedSharesShortSoldSum = ExcelUtil.GetRange(excelLineWriter.Row + 1, 1, worksheet).Text.ToString().Trim(); tradingInfo.DesignatedShortSellTurnoverShares = ExcelUtil.GetRange(excelLineWriter.Row + 2, 1, worksheet).Text.ToString().Trim(); tradingInfo.DesignatedShortSellTurnoverValue = ExcelUtil.GetRange(excelLineWriter.Row + 3, 1, worksheet).Text.ToString().Trim(); if (ExcelUtil.GetRange(excelLineWriter.Row + 4, 1, worksheet).Text != null) { tradingInfo.HKDTurnoverValue = ExcelUtil.GetRange(excelLineWriter.Row + 4, 1, worksheet).Text.ToString().Trim(); } excelLineWriter.PlaceNext(excelLineWriter.Row + 5, 1); } else { excelLineWriter.PlaceNext(excelLineWriter.Row + 1, 1); } } else { excelLineWriter.PlaceNext(excelLineWriter.Row + 1, 1); } } orOpenExcelFile.Close((object)false, (object)configObj.HKMAIN01_10_Workbook, (object)false); } } return(tradingInfo); }
private List <string> GetRicFromFile(List <string> list) { List <string> listRic = new List <string>(); string filePath = string.Empty; if (list == null || list.Count == 0) { string msg = string.Format("no download file in this email ."); Logger.Log(msg, Logger.LogType.Warning); return(null); } foreach (var item in list) { filePath = item; break; } using (Ric.Util.ExcelApp app = new Ric.Util.ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, filePath); var worksheet = (Worksheet)workbook.Worksheets[1]; int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down)) { while (reader.Row <= lastUsedRow) { string key = reader.ReadLineCellText(); if (key.Equals("CHANGE")) { reader.PlaceNext(reader.Row + 11, reader.Col); } if (key.Equals("DROP")) { reader.PlaceNext(reader.Row + 8, reader.Col); } if (key.Equals("RIC")) { int lastUsedCol = worksheet.UsedRange.Columns.Count; string value = string.Empty; string ricSS = string.Empty; using (ExcelLineWriter readerCol = new ExcelLineWriter(worksheet, reader.Row - 1, reader.Col + 1, ExcelLineWriter.Direction.Right)) { while (readerCol.Col <= lastUsedCol) { value = readerCol.ReadLineCellText(); if (!string.IsNullOrEmpty(value) && !listRic.Contains(value)) { listRic.Add(value); } if (value.EndsWith(".SS") && value.StartsWith("6")) { ricSS = string.Format("{0}.SH", value.Substring(0, value.Length - 3)); if (!string.IsNullOrEmpty(ricSS) && !listRic.Contains(ricSS)) { listRic.Add(ricSS); } } } } } } reader.PlaceNext(reader.Row, 1); } workbook.Close(false, workbook.FullName, false); } return(listRic); }
private void updateRicAddDicFromDownloadFile() { string downloadFilePath = string.Empty; if (Directory.Exists(configObj.DOWNLOAD_FILE_DIR)) { string[] files = Directory.GetFiles(configObj.DOWNLOAD_FILE_DIR, "DownloadRicChangeEvents*.xls", SearchOption.TopDirectoryOnly); if (files.Length != 0) { if (files.Length > 1) { LogMessage("More than one downloadRicChangeEvents*.xls file found, please have a check, there should be only one download file. "); } else { downloadFilePath = files[0]; } //foreach (string file in files) //{ // string[] arr = file.Split('_'); // if (DateTime.ParseExact(arr[1],"yyyyMMdd",null).Equals(DateTime.Parse(configObj.DATE))) // { // downloadFilePath = file; // } //} } } if (downloadFilePath == string.Empty) { LogMessage("There's no \"DownloadRicChangeEvents\" file under foler " + configObj.DOWNLOAD_FILE_DIR); } else { using (ExcelApp downloadApp = new ExcelApp(false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(downloadApp, downloadFilePath); var worksheet = (Worksheet)workbook.Worksheets[2]; if (worksheet == null) { LogMessage("There's no sheet2 in the excel file " + workbook.FullName); } int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 2, 1, ExcelLineWriter.Direction.Right)) { while (reader.Row <= lastUsedRow) { Range changeTypeRange = ExcelUtil.GetRange(reader.Row, 4, worksheet); Range ricNowRange = ExcelUtil.GetRange(reader.Row, 3, worksheet); if (changeTypeRange.Text != null && changeTypeRange.Text.ToString().Trim().ToUpper() == "ADD" && ricNowRange.Text != null) { string ricNow = ricNowRange.Text.ToString().Trim(); if (addRicDic.ContainsKey(ricNow)) { addRicDic[ricNow].EffectiveDate = reader.ReadLineCellText(); //Effective Date addRicDic[ricNow].RicWas = reader.ReadLineCellText(); //Ric Was addRicDic[ricNow].RicNow = reader.ReadLineCellText(); //Ric Now addRicDic[ricNow].ChangeType = reader.ReadLineCellText(); //Change Type addRicDic[ricNow].Country = reader.ReadLineCellText(); //Country addRicDic[ricNow].Exchanges = reader.ReadLineCellText(); //Exchanges addRicDic[ricNow].AssetClass = reader.ReadLineCellText(); //Asset Class addRicDic[ricNow].DescriptionWas = reader.ReadLineCellText(); addRicDic[ricNow].DescriptionNow = reader.ReadLineCellText(); addRicDic[ricNow].SummaryOfChange = reader.ReadLineCellText(); addRicDic[ricNow].ISINWas = reader.ReadLineCellText(); //addRicDic[ricNow].ISINNow = reader.GetCellText(); reader.ReadLineCellText(); addRicDic[ricNow].SecondMarketId = reader.ReadLineCellText(); addRicDic[ricNow].SecondMarketWas = reader.ReadLineCellText(); addRicDic[ricNow].SecondMarketNow = reader.ReadLineCellText(); addRicDic[ricNow].RicSeqId = reader.ReadLineCellText(); } //reader.PlaceNext(reader.Row + 1, 1); } reader.PlaceNext(reader.Row + 1, 1); } } } } }
// /// <summary> /// Get trading information from the HK-MAIN01-10.xls file. /// </summary> /// <param name="pageNum">the page number to copy.</param> /// <param name="lineNumEachPage">the line number of each page to copy.</param> /// <returns>the Main01_10 trading News Information.</returns> public TradingInfo GetTradingInfoFromMain01_10File(int pageNum, int lineNumEachPage) { TradingInfo tradingInfo = new TradingInfo(); tradingInfo.StockList = new List <StockInfo>(); //Open HK-MAIN01-10.xls using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.HKMAIN01_10_CONFIG.WORKBOOK_PATH); var worksheet = ExcelUtil.GetWorksheet(configObj.HKMAIN01_10_CONFIG.WORKSHEET_NAME, workbook); if (worksheet == null) { logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.HKMAIN01_10_CONFIG.WORKSHEET_NAME, workbook.Name)); } using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right)) { tradingInfo.DateStr = ExcelUtil.GetRange(1, 1, worksheet).Text.ToString(); reader.PlaceNext(4, 1); //Range range = ExcelUtil.GetRange(reader.Row, 1, worksheet); while ((reader.Row < (pageNum * lineNumEachPage) + 11)) { if (ExcelUtil.GetRange(reader.Row, 1, worksheet).Text != null) { string firstColText = ExcelUtil.GetRange(reader.Row, 1, worksheet).Text.ToString().Trim(); if (firstColText.StartsWith("<")) { StockInfo stockInfo = new StockInfo(); stockInfo.Ric = reader.ReadLineCellText(); stockInfo.StockName = reader.ReadLineCellText(); stockInfo.Shares = reader.ReadLineCellText(); stockInfo.Turnover = reader.ReadLineCellText(); tradingInfo.StockList.Add(stockInfo); reader.PlaceNext(reader.Row + 1, 1); } else { if (firstColText != string.Empty) { tradingInfo.DesignatedSecuritiesRecordingSum = ExcelUtil.GetRange(reader.Row, 1, worksheet).Text.ToString().Trim(); tradingInfo.DesignatedSharesShortSoldSum = ExcelUtil.GetRange(reader.Row + 1, 1, worksheet).Text.ToString().Trim(); tradingInfo.DesignatedShortSellTurnoverShares = ExcelUtil.GetRange(reader.Row + 2, 1, worksheet).Text.ToString().Trim(); tradingInfo.DesignatedShortSellTurnoverValue = ExcelUtil.GetRange(reader.Row + 3, 1, worksheet).Text.ToString().Trim(); if (ExcelUtil.GetRange(reader.Row + 4, 1, worksheet).Text != null) { tradingInfo.HKDTurnoverValue = ExcelUtil.GetRange(reader.Row + 4, 1, worksheet).Text.ToString().Trim(); } reader.PlaceNext(reader.Row + 5, 1); } else { reader.PlaceNext(reader.Row + 1, 1); } } } else { reader.PlaceNext(reader.Row + 1, 1); } } workbook.Close(false, configObj.HKMAIN01_10_CONFIG.WORKBOOK_PATH, false); } } return(tradingInfo); }