public void GenerateSCBFile(SCBChangeInfo changeInfo) { using (ExcelApp app = new ExcelApp(false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.GENERATED_SCB_FILE_PATH); var worksheet = workbook.Worksheets[1] as Worksheet; int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; int lastUsedCol = worksheet.UsedRange.Column + worksheet.UsedRange.Columns.Count - 1; ExcelUtil.GetRange(1, 1, lastUsedRow, lastUsedCol, worksheet).ClearContents(); using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right)) { writer.WriteLine("EventType"); writer.WriteLine("Function"); writer.WriteLine("FILE_CODE"); writer.WriteLine("ISIN"); writer.WriteLine("SEDOL"); writer.WriteLine("OLD TICKER"); writer.WriteLine("NEW TICKER"); writer.WriteLine("Old RIC"); writer.WriteLine("New RIC"); writer.WriteLine("MIC"); writer.WriteLine("EXCHANGE_CODE"); writer.WriteLine("EXCHANGE_NAME"); writer.WriteLine("OLD COMPANY_NAME"); writer.WriteLine("NEW COMPANY_NAME"); writer.WriteLine("ANNOUNCEMENT DATE"); writer.WriteLine("EFFECTIVE DATE"); writer.PlaceNext(2, 1); writer.WriteLine(changeInfo.EventType); writer.WriteLine(changeInfo.Function); writer.WriteLine(changeInfo.FileCode); writer.WriteLine(changeInfo.Isin); writer.WriteLine(changeInfo.Sedol); writer.WriteLine(changeInfo.OldTicker); writer.WriteLine(changeInfo.NewTicker); writer.WriteLine(changeInfo.OldRic); writer.WriteLine(changeInfo.NewRic); writer.WriteLine(changeInfo.Mic); writer.WriteLine(changeInfo.ExchangeCode); writer.WriteLine(changeInfo.ExchangeName); writer.WriteLine(changeInfo.OldCompName); writer.WriteLine(changeInfo.NewCompName); writer.WriteLine(changeInfo.AnnouncementDate); writer.WriteLine(changeInfo.EffectiveDate); } workbook.SaveCopyAs(GetSCBGenerateFilePath(configObj.GENERATED_SCB_FILE_PATH)); workbook.Close(false, workbook.FullName, false); } }
public SCBChangeInfo GetSCBChangeInfo() { SCBChangeInfo changeInfo = new SCBChangeInfo(); using (ExcelApp app = new ExcelApp(false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.SOURCE_FM_FILE_PATH); var worksheet = workbook.Worksheets[1] as Worksheet; int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; int i = lastUsedRow; while ((i <= lastUsedRow) && (i > 0)) { if (ExcelUtil.GetRange(i, 1, worksheet).Text != null) { string sA1 = ExcelUtil.GetRange(i, 1, worksheet).Text.ToString(); if (ExcelUtil.GetRange(i, 2, worksheet).Text == null) { LogMessage("The second column is null."); //return changeInfo; } string sA2 = ExcelUtil.GetRange(i, 2, worksheet).Text.ToString(); switch (sA1) { case "Exchange Symbol:": changeInfo.OldTicker = sA2; if (sA2 == string.Empty) { Logger.Log("Can't find the value for Old Ticker."); } break; case "Underlying RIC:": changeInfo.OldRic = sA2; if (sA2 == string.Empty) { Logger.Log("Can't find the value for Old Ric."); } break; case "Organisation Name (DIRNAME) (OLD):": changeInfo.OldCompName = sA2; if (sA2 == string.Empty) { Logger.Log("Can't find the value for Old Company Name."); } break; case "Organisation Name (DIRNAME) (NEW):": changeInfo.NewCompName = sA2; if (sA2 == string.Empty) { Logger.Log("Can't find the value for New Company Name."); } break; case "Effective Date:": changeInfo.EffectiveDate = sA2; if (sA2 == string.Empty) { Logger.Log("Can't find the value for Effective Date."); } break; default: break; } } i--; } changeInfo.AnnouncementDate = GetAnnouncementDateFromFile(configObj.SOURCE_FM_FILE_PATH); if (changeInfo.AnnouncementDate == string.Empty) { Logger.Log("Announcement date got from file name is invalid. Now it's empty."); } workbook.Close(false, workbook.FullName, false); } return(changeInfo); }
public void StartSCBDelistChangeNotificationJob() { SCBChangeInfo changeInfo = GetSCBChangeInfo(); GenerateSCBFile(changeInfo); }