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