protected override void Start()
{
int type = GetType();
GetHolidays();
if (type == 1)
{
if (File.Exists(configObj.ResultFilePath))
{
File.Delete(configObj.ResultFilePath);
}
workbook = InitializeExcel(configObj.ResultFilePath);
workbook.Worksheets.Add(Type.Missing, workbook.Worksheets[1]);
DownloadFirstFile();
FillTemplate();
FillTemplate2();
ReadExcel();
FillExcel();
}
else
{
workbook = InitializeExcel(configObj.ResultFilePath);
DownloadSecondFile();
ReadExcel2();
FillExcel2();
}
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
worksheet.Activate();
workbook.SaveAs(workbook.FullName);
AddResult("Result file", workbook.FullName, "file");
workbook.Close();
app.Dispose();
}
protected override void Start()
{
DownloadFiles();
for (fileNb = 1; fileNb < 3; fileNb++)
{
FillTemplate();
}
app.Dispose();
}
private void GenerateELWDropFmFile()
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
string msg = "Excel could not be started. Check that your office installation and project reference correct!!!";
Logger.Log(msg, Logger.LogType.Error);
return;
}
try
{
if (string.IsNullOrEmpty(ELWDropELWFM1ELWFileBulkGenerate.filename))
{
ELWDropELWFM1ELWFileBulkGenerate.filename = "Korea FM for " + DateTime.Today.ToString("dd-MMM-yyyy", new CultureInfo("en-US")).Replace("-", " ") + " (Morning).xls";
}
string ipath = Path.Combine(configObj.FM, ELWDropELWFM1ELWFileBulkGenerate.filename); // "C:\\Korea_Auto\\ELW_FM\\ELW_Drop\\" + filename;
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, ipath);
Worksheet wSheet = wSheet = (Worksheet)wBook.Worksheets[1];
if (wSheet == null)
{
string msg = "Error found in PrintFurtherIssueToExcel :(WorkSheet could not be created. Check that your office installation and project reference correct!!!)";
Logger.Log(msg, Logger.LogType.Error);
return;
}
int startLine = 5;
while (wSheet.get_Range("C" + startLine, Type.Missing).Value2 != null && wSheet.get_Range("C" + startLine, Type.Missing).Value2.ToString().Trim() != string.Empty)
{
startLine++;
}
GenerateExcelFileTitle(wSheet, startLine, "common");
startLine = startLine + 7;
AppendDataToFile(wSheet, startLine, "common");
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
//AddResult(Path.GetFileNameWithoutExtension(ipath),ipath,"");
}
catch (Exception ex)
{
string msg = "Error found in _print_ELWFMDroppTemplate : " + ex.ToString();
Logger.Log(msg, Logger.LogType.Error);
return;
}
finally
{
excelApp.Dispose();
}
}
private void SetEikonExcelAble(ref ExcelApp excelApp, ref COMAddIn comAddIn)
{
if (excelApp == null)
{
return;
}
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
excelApp.ExcelAppInstance.DisplayAlerts = false;
if (comAddIn != null)
{
comAddIn.Connect = true;
}
excelApp.Dispose();
}
protected override void Start()
{
List <string> pages = new List <string>();
List <string> excelLinks = new List <string>();
try
{
ricOfficialCode = GetRicList();
ricExl = GetExlList();
//foreach (string pageToCheck in pagesToCheck)
//{
// FindPagesToSearch(ref pages, pageToCheck);
//}
FindPagesToSearch2(ref pages);
int i = 0;
foreach (string page in pages)
{
string tmpLink;
if ((tmpLink = FindExcelLink(page)) != null)
{
excelLinks.Add(tmpLink);
}
i++;
}
DownLoadExcels(excelLinks);
newValues = GetNewValues();
Thread.Sleep(5000);
LogMessage("start querying in gats...");
gatsValues = CallGats();
Thread.Sleep(5000);
CompareGatsWithNew();
Thread.Sleep(5000);
LogMessage("start sorting results...");
ReOrderResults();
Thread.Sleep(5000);
LogMessage("start generating csv file ...");
WriteResultsInCsv();
AddResult("bulk file", configObj.ResultFileName, "result file");
AddResult("bulk file folder", configObj.ResultFolderPath, "result folder");
}
catch
{
}
finally
{
app.Dispose();
}
}
private void GenerateELWFMSingleSearchTemplate_xls()
{
if (koreaList.Count > 0)
{
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
Logger.Log("Excel could not be started. Check that your office installation and project reference are correct!", Logger.LogType.Error);
return;
}
try
{
String filename = "Search by ISIN " + DateTime.Today.ToString("yyyy-MM-dd") + ".xls";
String ipath = configObj.Korea_SearchByISIN_GenerateFileConfig.WORKBOOK_PATH + filename;
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, ipath);
Worksheet wSheet = ExcelUtil.GetWorksheet(configObj.Korea_SearchByISIN_GenerateFileConfig.WORKSHEET_NAME, wBook);
if (wSheet == null)
{
Logger.Log("Worksheet could not be created. Check that your office installation and project reference are correct!", Logger.LogType.Error);
return;
}
CreateExcelTitle(wSheet);
int startLine = 2;
while (wSheet.get_Range("C" + startLine, Type.Missing).Value2 != null && wSheet.get_Range("C" + startLine, Type.Missing).Value2.ToString() != String.Empty)
{
startLine++;
}
LoopPrintKoreaListData(wSheet, startLine);
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
}
catch (Exception ex)
{
Logger.Log("Error found in GenerateELWFMSingleSearchTemplate_xls : " + ex.ToString(), Logger.LogType.Warning);
return;
}
finally
{
excelApp.Dispose();
}
}
}
private void GenerateXls(List <FMELWEntity> listFMELW, string strFilePath)
{
if (listFMELW == null || listFMELW.Count == 0)
{
Logger.Log("listFMELW is null or empty!", Logger.LogType.Warning);
return;
}
//System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
string msg = "Excel could not be started. Check that your office installation and project reference are correct !!!";
Logger.Log(msg, Logger.LogType.Error);
return;
}
try
{
string filePath = Path.Combine(strFilePath, string.Format("{0}To{1}_{2}", strStartDate, strEndDate, "ELW.xls"));
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, filePath);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (wSheet == null)
{
string msg = "Excel Worksheet could not be started. Check that your office installation and project reference are correct !!!";
Logger.Log(msg, Logger.LogType.Error);
return;
}
FillExcelTitle(wSheet);
FillExcelBody(wSheet, listFMELW);
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
AddResult("KoreaIPO", filePath, "ExtractXLS");
}
catch (Exception ex)
{
string msg = "Error found in NDA T&C file :" + ex.ToString();
Logger.Log(msg, Logger.LogType.Error);
}
finally
{
excelApp.Dispose();
}
}
private void GenerateFile(List <IssueAssetAddTemplate> listIAATemplate)
{
if (listIAATemplate == null || listIAATemplate.Count == 0)
{
string msg = string.Format("listIAATemplate is null or empty!");
Logger.Log(msg, Logger.LogType.Warning);
return;
}
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
string msg = "Excel could not be started. Check that your office installation and project reference are correct !!!";
Logger.Log(msg, Logger.LogType.Error);
return;
}
try
{
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, issueAssAddPath);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (wSheet == null)
{
string msg = "Excel Worksheet could not be started. Check that your office installation and project reference are correct !!!";
Logger.Log(msg, Logger.LogType.Error);
return;
}
FillExcelTitle(wSheet);
FillExcelBody(wSheet, listIAATemplate);
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
TaskResultList.Add(new TaskResultEntry("HKWarrantsReIssueHistory", "InitialIssue", issueAssAddPath));
}
catch (Exception ex)
{
string msg = "Error found in NDA T&C file :" + ex.ToString();
Logger.Log(msg, Logger.LogType.Error);
}
finally
{
excelApp.Dispose();
}
}
/// <summary>
/// Find all rics in xls file
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
private List <string> GetRicFromFile(string file)
{
var excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
throw new Exception("Excel could not be started.");
}
try
{
string fileName = Path.GetFileName(file);
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, file);
var wSheet = wBook.Worksheets[1] as Worksheet;
if (wSheet == null)
{
throw new Exception("Worksheet can not be accessed for file:" + fileName);
}
int col = 1;
while ((ExcelUtil.GetRange(1, col, wSheet).Text.ToString().Trim() != "RIC"))
{
col++;
}
int lastUsedRow = wSheet.UsedRange.Rows.Count;
var ric = new List <string>();
for (int i = 2; i <= lastUsedRow; i++)
{
ric.Add(ExcelUtil.GetRange(i, col, wSheet).Text.ToString().Trim());
}
return(ric);
}
catch
{
throw new Exception("Error found in reading ric list from " + Path.GetFileName(file));
}
finally
{
excelApp.Dispose();
}
}
/// <summary>
/// GenerateNewExcel
/// </summary>
/// <param name="filePath">Path</param>
/// <param name="listDR">DRExchangeEntity</param>
/// <param name="listEIKON">EIKONEntity</param>
/// <param name="name">Exchange's name</param>
private void GenerateNewExcel(string filePath, IEnumerable <DRExchangeEntity> listDR, List <EIKONEntity> listEIKON, string name)
{
fileName = filePath + "\\" + DateTime.Now.ToString("dd-MM-yyyy") + "-" + name + ".xls";
try
{
if (!Directory.Exists(Path.GetDirectoryName(fileName)))
{
Directory.CreateDirectory(Path.GetDirectoryName(fileName));
}
ExcelApp app = new ExcelApp(false, false);
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, fileName);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
((Range)worksheet.Rows[1, Type.Missing]).Font.Bold = System.Drawing.FontStyle.Bold;
worksheet.Cells[1, 1] = "ADRRIC";
worksheet.Cells[1, 2] = "DISPLAY_NAME";
worksheet.Cells[1, 3] = "RatioEIKON";
worksheet.Cells[1, 4] = "RatioDR";
int startline = 2;
foreach (var itemDR in listDR)
{
foreach (var itemEI in listEIKON.Where(itemEI => itemDR.SYMBOL.Trim() == itemEI.ADRRIC.Trim()))
{
worksheet.Cells[startline, 1] = itemEI.ADRRIC;
worksheet.Cells[startline, 2] = itemEI.DISPLAYNAME;
worksheet.Cells[startline, 3] = " " + itemEI.RatioFromEIKON;
worksheet.Cells[startline, 4] = " " + itemDR.RatioFromDRExchange;
if (itemEI.RatioFromEIKON.Trim() != itemDR.RatioFromDRExchange.Trim())
{
ExcelUtil.GetRange(startline, 3, worksheet).Interior.Color = System.Drawing.Color.FromArgb(0, 200, 0).ToArgb();
ExcelUtil.GetRange(startline, 4, worksheet).Interior.Color = System.Drawing.Color.FromArgb(0, 200, 0).ToArgb();
}
startline++;
}
}
app.ExcelAppInstance.AlertBeforeOverwriting = false;
workbook.Save();
workbook.Close();
app.Dispose();
}
catch (Exception ex)
{
Logger.Log(string.Format("Error found during task: {0}. Exception message: {1}", "Error generated by GenerateNewExcel", ex.Message));
}
}
private void ReadDataFromIndexListingList_xls()
{
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
Logger.Log("Excel Application could not be created! please check the referenec is correct!", Logger.LogType.Warning);
return;
}
try
{
string ipath = configObj.KoreaIndexReadFileConfig.WorkbookPath;
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, ipath);
Worksheet wSheet_KOSPI = ExcelUtil.GetWorksheet(configObj.KoreaIndexReadFileConfig.KospiSheetname, wBook);
Worksheet wSheet_KOSDAQ = ExcelUtil.GetWorksheet(configObj.KoreaIndexReadFileConfig.KosdaqSheetname, wBook);
Worksheet wSheet_KRX = ExcelUtil.GetWorksheet(configObj.KoreaIndexReadFileConfig.KrxSheetname, wBook);
if (wSheet_KOSPI == null || wSheet_KOSDAQ == null || wSheet_KRX == null)
{
Logger.Log("Excel Worksheet could not be created! please check the referenec is correct!", Logger.LogType.Warning);
return;
}
int startLine = 2;
startLine = GenerateKOSPIHash(wSheet_KOSPI, startLine);
startLine = GenerateKOSDAQHash(wSheet_KOSDAQ, startLine);
startLine = GenerateKRXHash(wSheet_KRX, startLine);
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
}
catch (Exception ex)
{
Logger.Log("Error found in ReadDataFromIndexListingList_xls : " + ex, Logger.LogType.Warning);
return;
}
finally
{
excelApp.Dispose();
}
}
private void GenerateIARI()
{
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
string msg = "Excel could not be started. Check that your office installation and project reference are correct !!!";
Logger.Log(msg, Logger.LogType.Error);
return;
}
try
{
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, issueAssetReIssue);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (wSheet == null)
{
string msg = "Excel Worksheet could not be started. Check that your office installation and project reference are correct !!!";
Logger.Log(msg, Logger.LogType.Error);
return;
}
FillExcelTitleIARI(wSheet);
FillExcelBodyIARI(wSheet, listQuaNot);
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
TaskResultList.Add(new TaskResultEntry("HKWarrantReIssueHistory ", "FutrueIssue", issueAssetReIssue));
}
catch (Exception ex)
{
string msg = "Error found Generate IARA file :" + ex.ToString();
Logger.Log(msg, Logger.LogType.Error);
}
finally
{
excelApp.Dispose();
}
}
protected override void Start()
{
StartOSETradeVolumeUpdatorJob();
app.Dispose();
}
/// <summary>
/// Close the Excel App in case of error raised
/// If not this function is last called to be sure everything was closed correctly for the next call to this task
/// </summary>
/// <returns>true if success</returns>
private bool CleanExit()
{
app.Dispose();
return(true);
}
private void PrintFurtherIssueToExcel(List <FurtherIssueModel> fList)
{
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
string msg = "Excel could not be started. Check that your office installation and project reference correct!!!";
Logger.Log(msg, Logger.LogType.Error);
return;
}
try
{
string filename = fList.Aggregate("KR FM (Further Issue) _ ", (current, item) => current + (item.New_Ric + ","));
filename = filename.Trim(new[] { ' ', ',' }) + "(wef " + Convert.ToDateTime(fList[0].Effective_Date).ToString("yyyy-MMM-dd", new CultureInfo("en-US")) + ").xls";
//rename file if the length of file name more than 218 chars
if (filename.Length > 218)
{
filename = "KR FM (Further Issue) _ (wef " + Convert.ToDateTime(fList[0].Effective_Date).ToString("yyyy-MMM-dd", new CultureInfo("en-US")) + ").xls";
}
string ipath = Path.Combine(configObj.FM_FurtherIssuer, filename);
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, ipath);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (wSheet == null)
{
string msg = "Error found in PrintFurtherIssueToExcel :(WorkSheet could not be created. Check that your office installation and project reference correct!!!)";
Logger.Log(msg, Logger.LogType.Error);
return;
}
GenerateExcelFileTitle(wSheet);
int startLine = 5;
foreach (var item in fList)
{
((Range)wSheet.Cells[startLine, 1]).NumberFormat = "@";
wSheet.Cells[startLine, 1] = item.Updated_Date;
((Range)wSheet.Cells[startLine, 2]).NumberFormat = "@";
wSheet.Cells[startLine, 2] = Convert.ToDateTime(item.Effective_Date).ToString("dd-MMM-yy", new CultureInfo("en-US"));
wSheet.Cells[startLine, 3] = item.Old_Ric;
wSheet.Cells[startLine, 4] = item.New_Ric;
wSheet.Cells[startLine, 5] = item.Old_Isin;
wSheet.Cells[startLine, 6] = item.New_Isin;
((Range)wSheet.Cells[startLine, 7]).NumberFormat = "@";
wSheet.Cells[startLine, 7] = item.Old_Ticker;
((Range)wSheet.Cells[startLine, 8]).NumberFormat = "@";
wSheet.Cells[startLine, 8] = item.New_Ticker;
wSheet.Cells[startLine, 9] = item.Old_Quanity;
wSheet.Cells[startLine, 10] = item.New_Quanity;
startLine++;
}
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
TaskResultList.Add(new TaskResultEntry(Path.GetFileNameWithoutExtension(ipath), "", ipath, creatMail(fList)));
}
catch (Exception ex)
{
string msg = "Error found in PrintFurtherIssueToExcel : " + ex.ToString();
Logger.Log(msg, Logger.LogType.Error);
}
finally
{
excelApp.Dispose();
}
}
private void ReadDataFromKSandKQListingEquityList_xls()
{
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
Logger.Log("Excel Application could not be created! please check the referenec is correct!", Logger.LogType.Warning);
return;
}
try
{
string ipath = configObj.KoreaKQorKsListReadFilePathConfig.WorkbookPath;
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, ipath);
Worksheet wSheet = ExcelUtil.GetWorksheet(configObj.KoreaKQorKsListReadFilePathConfig.WorksheetName, wBook);
if (wSheet == null)
{
Logger.Log("Excel Worksheet could not be created! please check the referenec is correct!", Logger.LogType.Warning);
return;
}
int startLine = 2;
while (wSheet.Range["A" + startLine, Type.Missing].Value2 != null)
{
if (wSheet.Range["A" + startLine, Type.Missing].Value2.ToString() != String.Empty)
{
KSorKQListingList listing = new KSorKQListingList();
if (wSheet.Range["A" + startLine, Type.Missing].Value2 != null)
{
listing.Ric = ((Range)wSheet.Cells[startLine, 1]).Value2.ToString().Trim();
}
if (wSheet.Range["B" + startLine, Type.Missing].Value2 != null)
{
listing.IDNDisplayName = ((Range)wSheet.Cells[startLine, 2]).Value2.ToString().Trim();
}
if (wSheet.Range["C" + startLine, Type.Missing].Value2 != null)
{
listing.ISIN = ((Range)wSheet.Cells[startLine, 3]).Value2.ToString().Trim();
}
String ticker;
if (listing.Ric.IndexOf('.') > 0 && listing.Ric.Length == 9)
{
ticker = listing.Ric.Split('.')[0].Trim().ToString();
kskqlistingHash.Add(ticker, listing);
startLine++;
}
else
{
startLine++;
}
}
}
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
}
catch (Exception ex)
{
Logger.Log("Error found in ReadDataFromEquityMasterfile_xls : " + ex, Logger.LogType.Warning);
return;
}
finally
{
excelApp.Dispose();
}
}
void ICommand.Execute(object parameter)
{
string folderName = String.Empty;
using (var fileDialog = new FolderBrowserDialog())
{
if (DialogResult.OK == fileDialog.ShowDialog())
{
folderName = fileDialog.SelectedPath;
}
}
var reportPath = Path.Combine(folderName, String.Format("RicReport_{0:MM-dd}.xlsx", DateTime.Now));
var app = new ExcelApp(true, false);
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, reportPath);
Worksheet worksheetFirst = workbook.Worksheets[1] as Worksheet;
worksheetFirst.Name = "general";
worksheetFirst.Range["B3", "D3"].Merge();
worksheetFirst.Cells[3, 2] = "ETI Ric Generator report";
worksheetFirst.Cells[5, 2] = "from:";
worksheetFirst.Cells[5, 3] = "to:";
worksheetFirst.Cells[6, 2] = _viewModel.StartDate.ToString("MM/dd/yy");
worksheetFirst.Cells[6, 3] = _viewModel.EndDate.ToString("MM/dd/yy");
((Range)worksheetFirst.Columns["B:C"]).ColumnWidth = 15;
workbook.Worksheets.Add(Type.Missing, workbook.Worksheets[1]);
Worksheet worksheetSecond = workbook.Worksheets[2] as Worksheet;
worksheetSecond.Name = _viewModel.MarketFilterIndex == 0 ? "All markets" : _viewModel.MarketFilter.Name;
((Range)worksheetSecond.Columns["A"]).ColumnWidth = 25;
((Range)worksheetSecond.Columns["B:C"]).ColumnWidth = 20;
((Range)worksheetSecond.Columns["D:G"]).ColumnWidth = 15;
worksheetSecond.Cells[1, 1] = "Name";
worksheetSecond.Cells[1, 2] = "Market";
worksheetSecond.Cells[1, 3] = "Developer";
worksheetSecond.Cells[1, 4] = "Success";
worksheetSecond.Cells[1, 5] = "Fails";
worksheetSecond.Cells[1, 6] = "Average time";
worksheetSecond.Cells[1, 7] = "Success percent";
int row = 2;
foreach (var reportTask in
(from newReport in _viewModel.FilteredReport
let successPercent = (newReport.Successed + newReport.Failed) == 0 ? 0 : ((float)newReport.Successed / (float)(newReport.Successed + newReport.Failed)) * 100.0
orderby successPercent descending, newReport.Successed descending
select newReport))
{
worksheetSecond.Cells[row, 1] = reportTask.Task.Name;
worksheetSecond.Cells[row, 2] = reportTask.Task.Market.Name;
worksheetSecond.Cells[row, 3] = reportTask.Task.Owner.Surname + " " + reportTask.Task.Owner.Familyname;
worksheetSecond.Cells[row, 4] = reportTask.Successed;
worksheetSecond.Cells[row, 5] = reportTask.Failed;
worksheetSecond.Cells[row, 6] = reportTask.AverageTime;
worksheetSecond.Cells[row, 7] = (reportTask.Successed + reportTask.Failed) == 0 ? 0 : ((float)reportTask.Successed / (float)(reportTask.Successed + reportTask.Failed)) * 100.0;
row++;
}
Range toTable = worksheetSecond.Range["A1", "G" + (row - 1)];
toTable.Worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, toTable, System.Type.Missing, XlYesNoGuess.xlYes, System.Type.Missing).Name = "Table1";
toTable.Select();
//toTable.Worksheet.ListObjects["Table1"].TableStyle = "TableStyleMedium15";
Range toCond = worksheetSecond.Range["G2", "G" + (row - 1)];
FormatCondition cond1 = (FormatCondition)toCond.FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlGreater, 66);
cond1.Interior.PatternColorIndex = Constants.xlAutomatic;
cond1.Interior.Color = ColorTranslator.ToWin32(Color.LawnGreen);
FormatCondition cond2 = (FormatCondition)toCond.FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlBetween, 34, 66);
cond2.Interior.PatternColorIndex = Constants.xlAutomatic;
cond2.Interior.Color = ColorTranslator.ToWin32(Color.Orange);
FormatCondition cond3 = (FormatCondition)toCond.FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlLess, 33);
cond3.Interior.PatternColorIndex = Constants.xlAutomatic;
cond3.Interior.Color = ColorTranslator.ToWin32(Color.Red);
//toTable.;
worksheetFirst.Activate();
workbook.Save();
workbook.Close();
app.Dispose();
}
/*-------------------------------------------------------test-----------------------------------------------*/
private void ControlExcel()
{
ExcelApp excelApp = new ExcelApp(false, false);
try
{
if (excelApp.ExcelAppInstance == null)
{
Logger.Log("", Logger.LogType.Error);
return;
}
String ipath = configObj.KoreaIndexOrignalFileConfig.WorkbookPath;
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, ipath);
int sheetCounts = wBook.Worksheets.Count;
for (var i = 1; i <= sheetCounts; i++)
{
Worksheet wSheet = (Worksheet)wBook.Worksheets[i];
String sheetname = wSheet.Name;
if (!sheetname.Contains("섹터"))
{
String chainRic = "";
String[] sname_arr = sheetname.Split('(');
String str_name = sname_arr[0].Trim();
String sname = str_name.Contains("KOSPI") ? str_name.Replace("KOSPI", "") : (str_name.Contains("KOSDAQ") ? str_name.Replace("KOSDAQ", "") : (str_name.Contains("KRX") ? str_name.Replace("KRX", "") : str_name));
String str_instrument = sname_arr[(sname_arr.Length - 1)].Trim(new[] { ' ', ')' });
switch (str_instrument)
{
case "변경종목":
wSheet.Name = "the change of " + sname;
break;
case "구성종목":
wSheet.Name = "the constituents of " + sname;
break;
}
String temp_str_name = String.Empty;
if (str_name.Contains("KOSPI") || str_name.Contains("코스피"))
{
temp_str_name = str_name.Contains("KOSPI") ? str_name.Replace("KOSPI", "").Trim().ToUpper().Replace(" ", "") : str_name.Replace("코스피", "").Trim().ToUpper().Replace(" ", "");
if (KOSPIHash.Contains(temp_str_name))
{
chainRic = ((IndexListingList)KOSPIHash[temp_str_name]).IndexChainRIC;
}
}
else if (str_name.Contains("KOSDAQ") || str_name.Contains("코스닥"))
{
temp_str_name = str_name.Contains("KOSDAQ") ? str_name.Replace("KOSDAQ", "").Trim().ToUpper().Replace(" ", "") : str_name.Replace("코스닥", "").Trim().ToUpper().Replace(" ", "");
if (KOSDAQHash.Contains(temp_str_name))
{
chainRic = ((IndexListingList)KOSDAQHash[temp_str_name]).IndexChainRIC;
}
}
else if (str_name.Contains("KRX"))
{
temp_str_name = str_name.Replace("KRX", "").Trim().ToUpper().Replace(" ", "");
if (KRXHash.Contains(temp_str_name))
{
chainRic = ((IndexListingList)KRXHash[temp_str_name]).IndexChainRIC;
}
}
else
{
temp_str_name = str_name.ToUpper().Replace(" ", "");
if (chainRic == string.Empty && KOSPIHash.Contains(temp_str_name))
{
chainRic = ((IndexListingList)KOSPIHash[temp_str_name]).IndexChainRIC;
}
else if (chainRic == string.Empty && KOSDAQHash.Contains(temp_str_name))
{
chainRic = ((IndexListingList)KOSDAQHash[temp_str_name]).IndexChainRIC;
}
else if (chainRic == string.Empty && KRXHash.Contains(temp_str_name))
{
chainRic = ((IndexListingList)KRXHash[temp_str_name]).IndexChainRIC;
}
else
{
chainRic = "There doesn't exists Index Chain RIC can match Key Word(sheet name) !";
}
}
int startLine = 4;
if (wSheet.Range["A" + startLine, Type.Missing].Value2 == null && wSheet.Range["B" + startLine, Type.Missing].Value2 != null)
{
((Range)wSheet.Columns["A", Type.Missing]).Delete(XlDeleteShiftDirection.xlShiftToLeft);
}
String title = wSheet.Range["A1", Type.Missing].Value2.ToString();
title = wSheet.Name + " " + chainRic;
wSheet.Cells[1, 1] = title;
String columnA3 = wSheet.Range["A" + (startLine - 1), Type.Missing].Value2 != null ? wSheet.Range["A" + (startLine - 1), Type.Missing].Value2.ToString() : null;
String columnB3 = wSheet.Range["B" + (startLine - 1), Type.Missing].Value2 != null ? wSheet.Range["B" + (startLine - 1), Type.Missing].Value2.ToString() : null;
String columnC3 = wSheet.Range["C" + (startLine - 1), Type.Missing].Value2 != null ? wSheet.Range["C" + (startLine - 1), Type.Missing].Value2.ToString() : null;
String columnD3 = wSheet.Range["D" + (startLine - 1), Type.Missing].Value2 != null ? wSheet.Range["D" + (startLine - 1), Type.Missing].Value2.ToString() : null;
if (columnA3 == "신규편입 종목" && columnC3 == "제외종목")
{
startLine = ModifyTheDataWithAddItemsAndDropItems_Worksheet(wSheet, startLine);
}
else if (columnA3 == "종목코드" && columnB3 == "종목명" && columnC3 == "시장구분" && (columnD3 == "유동비율(%)" || columnD3 == "유동비율"))
{
startLine = ModifyTheDataWithFreeFloatRate_Worksheet(wSheet, startLine);
}
else if (columnA3 == "종목코드" && columnB3 == "종목명")
{
startLine = ModifyTheDataWithLocalLanguageName_Worksheet(wSheet, startLine);
}
}
else
{
sheetname = sheetname.Replace("섹터", "Sector"); //KRX Sector(변경종목)
String chainRic = "";
String[] sname_arr = sheetname.Split('(');
String str_name = sname_arr[0].Trim();
String str_instrument = sname_arr[(sname_arr.Length - 1)].Trim(new[] { ' ', ')' });
switch (str_instrument)
{
case "변경종목":
wSheet.Name = "the change of " + str_name;
break;
case "구성종목":
wSheet.Name = "the constituents of " + str_name;
break;
}
int startLine = 3;
if (wSheet.Range["A" + startLine, Type.Missing].Value2 == null && wSheet.Range["B" + startLine, Type.Missing].Value2 != null && wSheet.Range["B" + startLine, Type.Missing].Value2.ToString().Trim() == "섹터구분")
{
((Range)wSheet.Columns["A", Type.Missing]).Delete(XlDeleteShiftDirection.xlShiftToLeft);
}
String columnA3 = wSheet.Range["A" + startLine, Type.Missing].Value2 != null ? wSheet.Range["A" + startLine, Type.Missing].Value2.ToString().Trim() : null;
String columnB3 = wSheet.Range["B" + startLine, Type.Missing].Value2 != null ? wSheet.Range["B" + startLine, Type.Missing].Value2.ToString().Trim() : null;
String columnC3 = wSheet.Range["C" + startLine, Type.Missing].Value2 != null ? wSheet.Range["C" + startLine, Type.Missing].Value2.ToString().Trim() : null;
String columnD3 = wSheet.Range["D" + startLine, Type.Missing].Value2 != null ? wSheet.Range["D" + startLine, Type.Missing].Value2.ToString().Trim() : null;
String columnE3 = wSheet.Range["E" + startLine, Type.Missing].Value2 != null ? wSheet.Range["E" + startLine, Type.Missing].Value2.ToString().Trim() : null;
if (columnA3 == "섹터구분" && columnB3 == "신규편입 종목" && columnD3 == "제외종목")
{
ModifyTheSectorDataWithAddItemsAndDropItems_Worksheet(wSheet, ref chainRic, ref startLine);
}
else if (columnA3 == "섹터구분" && columnB3 == "종목코드" && columnC3 == "종목명" && columnD3 == "시장구분" && (columnE3 == "유동비율(%)" || columnE3 == "유동비율"))
{
ModifyTheSectorDataWithFreeFloatRate_Worksheet(wSheet, ref chainRic, ref startLine);
}
}
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
}
}
catch (Exception ex)
{
Logger.Log("" + ex, Logger.LogType.Warning);
return;
}
finally
{
excelApp.Dispose();
}
}
private void GenerateFile()
{
ExcelApp excelApp = new ExcelApp(false, false);
try
{
string fileName = string.Format(KOREA_IPO_QC_FILE_NAME, DateTime.Today.ToString("yyyy-MM-dd"));
string filePath = Path.Combine(GetOutputFilePath(), fileName); //"C:\\Korea_Auto\\Equity_Warrant\\Name_Change\\" + filename;
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, filePath);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
GenerateExcelFileTitle(wSheet);
int row = 2;
foreach (var item in changedIpo)
{
string productionType = item.ProductionType.ToString();
wSheet.Cells[row, 1] = productionType;
((Range)wSheet.Cells[row, 2]).NumberFormat = "@";
wSheet.Cells[row, 2] = item.TickerFm;
((Range)wSheet.Cells[row, 3]).NumberFormat = "@";
wSheet.Cells[row, 3] = item.TickerProduct;
wSheet.Cells[row, 4] = item.IsTickerSame.ToString();
if (!item.IsTickerSame)
{
((Range)wSheet.Cells[row, 4]).Interior.Color = Color.Yellow;
}
wSheet.Cells[row, 5] = item.IsinFm;
wSheet.Cells[row, 6] = item.IsinProduct;
if (string.IsNullOrEmpty(item.IsinFm))
{
wSheet.Cells[row, 7] = "";
}
else
{
wSheet.Cells[row, 7] = item.IsIsinSame.ToString();
}
if (!item.IsIsinSame)
{
((Range)wSheet.Cells[row, 7]).Interior.Color = Color.Yellow;
}
((Range)wSheet.Cells[row, 8]).NumberFormat = "@";
wSheet.Cells[row, 8] = item.BcastRefFm;
((Range)wSheet.Cells[row, 9]).NumberFormat = "@";
wSheet.Cells[row, 9] = item.BcastRefProduct;
if (string.IsNullOrEmpty(item.BcastRefFm))
{
wSheet.Cells[row, 10] = "";
}
else
{
wSheet.Cells[row, 10] = item.IsBcastRefSame.ToString();
}
if (!item.IsBcastRefSame)
{
((Range)wSheet.Cells[row, 10]).Interior.Color = Color.Yellow;
}
row++;
}
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
AddResult(fileName, filePath, "Compared File");
Logger.Log("Generate FM file. Filepath is " + filePath);
}
catch (Exception ex)
{
string msg = "Error found in GenerateNameChangeFMFile() : \r\n" + ex.ToString();
Logger.Log(msg, Logger.LogType.Error);
}
finally
{
excelApp.Dispose();
}
}
private void GenerateFile()
{
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
string msg = "Excel could not be started. Check that your office installation and project reference are correct!";
excelApp.Dispose();
throw new Exception(msg);
}
try
{
string fileName = string.Format(KoreaIpoQcFileName, DateTime.Today.ToString("yyyy-MM-dd"));
string filePath = Path.Combine(GetOutputFilePath(), fileName); //"C:\\Korea_Auto\\Equity_Warrant\\Name_Change\\" + filename;
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, 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!";
throw new Exception(msg);
}
GenerateExcelFileTitle(wSheet);
int row = 2;
foreach (var item in changedIpo)
{
string productionType = item.ProductionType.ToString();
wSheet.Cells[row, 1] = productionType;
((Range)wSheet.Cells[row, 2]).NumberFormat = "@";
wSheet.Cells[row, 2] = item.TickerFm;
((Range)wSheet.Cells[row, 3]).NumberFormat = "@";
wSheet.Cells[row, 3] = item.TickerProduct;
wSheet.Cells[row, 4] = item.IsTickerSame.ToString();
if (!item.IsTickerSame)
{
((Range)wSheet.Cells[row, 4]).Interior.Color = Color.Yellow;
}
wSheet.Cells[row, 5] = item.IsinFm;
wSheet.Cells[row, 6] = item.IsinProduct;
if (string.IsNullOrEmpty(item.IsinFm))
{
wSheet.Cells[row, 7] = "";
}
else
{
wSheet.Cells[row, 7] = item.IsIsinSame.ToString();
}
if (!item.IsIsinSame)
{
((Range)wSheet.Cells[row, 7]).Interior.Color = Color.Yellow;
}
wSheet.Cells[row, 8] = item.IdnDisplayNameFm;
wSheet.Cells[row, 9] = item.IdnDisplayNameProduct;
wSheet.Cells[row, 10] = item.IsIdnDisplayNameSame.ToString();
if (!item.IsIdnDisplayNameSame)
{
((Range)wSheet.Cells[row, 10]).Interior.Color = Color.Yellow;
}
((Range)wSheet.Cells[row, 11]).NumberFormat = "@";
wSheet.Cells[row, 11] = item.BcastRefFm;
((Range)wSheet.Cells[row, 12]).NumberFormat = "@";
wSheet.Cells[row, 12] = item.BcastRefProduct;
if (string.IsNullOrEmpty(item.BcastRefFm))
{
wSheet.Cells[row, 13] = "";
}
else
{
wSheet.Cells[row, 13] = item.IsBcastRefSame.ToString();
}
if (!item.IsBcastRefSame)
{
((Range)wSheet.Cells[row, 13]).Interior.Color = Color.Yellow;
}
row++;
}
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
AddResult("Compared file", filePath, "file");
Logger.Log("Generate FM file. Filepath is " + filePath);
}
catch (Exception ex)
{
string msg = "Error found in GenerateNameChangeFMFile() : \r\n" + ex.ToString();
LogMessage(msg, Logger.LogType.Error);
}
finally
{
excelApp.Dispose();
}
}
private void GenerateFM()
{
if (announcementList.Count < 1)
{
Logger.Log("No new added equity record", Logger.LogType.Info);
return;
}
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
string msg = "Excel could not be started. Check that your office installation and project reference correct!!";
Logger.Log(msg, Logger.LogType.Error);
return;
}
string rics = CombineAllRics(announcementList);
string filename = string.Format("KR FM (PEO)_{0}.xls", rics);
string fmFile = Path.Combine(configObj.FM, filename);
try
{
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, fmFile);
Worksheet wSheet = ExcelUtil.GetWorksheet("Sheet1", wBook);
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;
}
int startLine = WriteFMHeader(wSheet, 3, true);
foreach (var item in announcementList)
{
WriteFMEquityItem(wSheet, startLine, item);
startLine++;
}
WriteFMFooter(wSheet, startLine + 1);
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
MailToSend mail = new MailToSend();
mail.ToReceiverList.AddRange(configObj.MailTo);
mail.MailSubject = Path.GetFileNameWithoutExtension(filename);
mail.CCReceiverList.AddRange(configObj.MailCC);
mail.AttachFileList.Add(fmFile);
mail.MailBody = "PEO:\t" + rics + "\t\r\n\r\n\r\n";
string signature = string.Join("\r\n", configObj.MailSignature.ToArray());
mail.MailBody += signature;
AddResult(filename, fmFile, "FM File");
}
catch (Exception ex)
{
Logger.Log("Error in GenerateFM(): " + ex.ToString(), Logger.LogType.Error);
}
finally
{
excelApp.Dispose();
}
}
private void GenerateXls(List <ELWExtractEntity> listELWEntity, string strOutPutPath)
{
//System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
string msg = "Excel could not be started. Check that your office installation and project reference are correct !!!";
Logger.Log(msg, Logger.LogType.Error);
}
try
{
string strFileName = DateTime.Today.ToString("yyyy-MM-dd") + "_ELW.xls";
string strFilePath = Path.Combine(strOutPutPath, strFileName);
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, strFilePath);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (wSheet == null)
{
string msg = "Excel Worksheet could not be started. Check that your office installation and project reference are correct !!!";
Logger.Log(msg, Logger.LogType.Error);
}
//first table left on ELW website
wSheet.Cells[1, 1] = "발행기관코드";
wSheet.Cells[1, 2] = "표준코드";
wSheet.Cells[1, 3] = "한글종목명";
wSheet.Cells[1, 4] = "금융상품구분";
wSheet.Cells[1, 5] = "상장여부";
wSheet.Cells[1, 6] = "상장일";
wSheet.Cells[1, 7] = "발행수량(워런트)";
wSheet.Cells[1, 8] = "발행단가";
wSheet.Cells[1, 9] = "발행통화";
wSheet.Cells[1, 10] = "발행형태";
wSheet.Cells[1, 11] = "표준/비표준";
//first table right on ELW website
wSheet.Cells[1, 12] = "발행기관명";
wSheet.Cells[1, 13] = "단축코드";
wSheet.Cells[1, 14] = "영문종목명";
wSheet.Cells[1, 15] = "발행회차(회)";
wSheet.Cells[1, 16] = "활성여부";
wSheet.Cells[1, 17] = "상장폐지일";
wSheet.Cells[1, 18] = "발행일";
wSheet.Cells[1, 19] = "만기일";
wSheet.Cells[1, 20] = "발행구분";
wSheet.Cells[1, 21] = "전환비율";
wSheet.Cells[1, 22] = "권리형태";
//second table on ELW website
wSheet.Cells[1, 23] = "기초자산종류";
wSheet.Cells[1, 24] = "주권발행기관";
wSheet.Cells[1, 25] = "주가지수종류";
wSheet.Cells[1, 26] = "기초자산기타";
//third table on ELW website
wSheet.Cells[1, 27] = "권리유형";
wSheet.Cells[1, 28] = "특이발행조건";
wSheet.Cells[1, 29] = "권리행사방식";
//fourth table on ELW website
wSheet.Cells[1, 30] = "CFI";
int startLine = 2;
foreach (var tmp in listELWEntity)
{
//first table left on ELW website
wSheet.Cells[startLine, 1] = tmp.IssuingAuthorityCongenial;
wSheet.Cells[startLine, 2] = tmp.StandardCongenial;
wSheet.Cells[startLine, 3] = tmp.KoreanProjectName;
wSheet.Cells[startLine, 4] = tmp.FinancialProducts;
wSheet.Cells[startLine, 5] = tmp.ListedOrNot;
wSheet.Cells[startLine, 6] = tmp.Listed;
wSheet.Cells[startLine, 7] = tmp.IssueNumber;
wSheet.Cells[startLine, 8] = tmp.ReleaseTheUnitPrice;
wSheet.Cells[startLine, 9] = tmp.Money;
wSheet.Cells[startLine, 10] = tmp.ReleaseForm;
wSheet.Cells[startLine, 11] = tmp.StandardNonStandard;
//first table left on ELW website
wSheet.Cells[startLine, 12] = tmp.IssuingAuthority;
wSheet.Cells[startLine, 13] = tmp.ShortenTheCongenial;
wSheet.Cells[startLine, 14] = tmp.TheProjectNameEnglish;
wSheet.Cells[startLine, 15] = tmp.ToIssue;
wSheet.Cells[startLine, 16] = tmp.WhetherTheActivity;
wSheet.Cells[startLine, 17] = tmp.ListedUntil;
wSheet.Cells[startLine, 18] = tmp.TheDate;
wSheet.Cells[startLine, 19] = tmp.TheExpirationOfThe;
wSheet.Cells[startLine, 20] = tmp.IssueToDistinguish;
wSheet.Cells[startLine, 21] = tmp.ConversionRatio;
wSheet.Cells[startLine, 22] = tmp.ThePowerForm;
//second table on ELW website
wSheet.Cells[startLine, 23] = tmp.UnderlyingAssetTypes;
wSheet.Cells[startLine, 24] = tmp.SovereignIssuingAuthority;
wSheet.Cells[startLine, 25] = tmp.StockIndexTypes;
wSheet.Cells[startLine, 26] = tmp.UnderlyingAssetGuitar;
//third table on ELW website
wSheet.Cells[startLine, 27] = tmp.TheRightType;
wSheet.Cells[startLine, 28] = tmp.TheIssueOfTheSpecialConditions;
wSheet.Cells[startLine, 29] = tmp.TheExerciseOfTheRightWay;
//fourth table on ELW website
wSheet.Cells[1, 30] = tmp.CFI;
startLine++;
}
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
AddResult("ELWExtractData", strFilePath, "XLS");
}
catch (Exception ex)
{
string msg = "Error found in NDA T&C file :" + ex.ToString();
Logger.Log(msg, Logger.LogType.Error);
}
finally
{
excelApp.Dispose();
}
}
private Hashtable ReadReferenceTableOfPlic()
{
Hashtable tableOfPlic = new Hashtable();
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
string msg = "Excel applcation could not be created ,please check your office installation is corrected !!";
Logger.Log(msg, Logger.LogType.Error);
throw (new Exception(msg));
}
try
{
string fpath = configObj.TagPilcFile;
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, fpath);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
//Worksheet wSheet = ExcelUtil.GetWorksheet("Sheet 1", wBook);
if (wSheet == null)
{
string msg = string.Format("worksheet{0} couldn't be found !!", "Issuer");
Logger.Log(msg, Logger.LogType.Error);
throw (new Exception(msg));
}
string expiryDate = null;
string newExpiryDateLong = DateTime.Now.ToString("ddMMMyy", new CultureInfo("en-US"));
string newExpiryDateShort = newExpiryDateLong.Substring(2).ToUpper();
for (int i = 2; ; i++)
{
string ric = ((Range)wSheet.Cells[i, 2]).Text.ToString();
if (!string.IsNullOrEmpty(ric))
{
PilcTemplate pilc = new PilcTemplate();
pilc.ExpiryDate = ((Range)wSheet.Cells[i, 12]).Text.ToString();
pilc.QACommonName = ((Range)wSheet.Cells[i, 5]).Text.ToString().ToUpper();
pilc.IACommonName = ((Range)wSheet.Cells[i, 11]).Text.ToString();
expiryDate = GetExpiryDateFromCommonName(pilc.IACommonName);
if (!string.IsNullOrEmpty(expiryDate))
{
pilc.QACommonName = pilc.QACommonName.Replace(expiryDate.Substring(2).ToUpper(), newExpiryDateShort);
pilc.IACommonName = pilc.IACommonName.Replace(expiryDate, newExpiryDateLong);
}
tableOfPlic[ric] = pilc;
}
else
{
break;
}
}
wBook.Save();
}
catch (Exception ex)
{
Logger.Log(ex.Message);
Logger.Log(ex.StackTrace);
throw ex;
}
finally
{
excelApp.Dispose();
}
return(tableOfPlic);
}
protected override void Start()
{
try
{
Dictionary <string, string> twseList = GetTwseList();
Dictionary <string, string> gtsmList = GetGtsmList();
_workbook = ExcelUtil.CreateOrOpenExcelFile(_app, _configObj.WorkingFolder + _configObj.SectorChainList);
LogMessage("Get Twse sector");
Dictionary <string, string> sectorsTwse = GetTwseSector();
Dictionary <string, string> resultsTwse = GetResultsTwse(twseList, sectorsTwse);
Dictionary <string, string> oldTwse = GetDbList(sectorsTwse);
LogMessage("Look for deletions in Twse sector");
Dictionary <string, string> duplicatesDBTwse = FindDuplicates(oldTwse);
OutPutDictionadyTest(duplicatesDBTwse, Path.Combine(_configObj.WorkingFolder, "IDNDuplicates(Twse sector).csv"));
AddResult("IDNDuplicates", Path.Combine(_configObj.WorkingFolder, "IDNDuplicates(Twse sector).csv"), "CSV bulk file");
Dictionary <string, string> duplicatesWebsiteTwse = FindDuplicates(resultsTwse);
OutPutDictionadyTest(duplicatesWebsiteTwse, Path.Combine(_configObj.WorkingFolder, "WebsiteDuplicates(Twse sector).csv"));
AddResult("WebsiteDuplicates", Path.Combine(_configObj.WorkingFolder, "WebsiteDuplicates(Twse sector).csv"), "CSV bulk file");
Dictionary <string, string> WebsiteRemovedIDNTwse = CompareWithDb(resultsTwse, oldTwse);
OutPutDictionadyTest(WebsiteRemovedIDNTwse, Path.Combine(_configObj.WorkingFolder, "WebsiteRemovedIDN(Twse sector).csv"));
AddResult("WebsiteRemovedIDN", Path.Combine(_configObj.WorkingFolder, "WebsiteRemovedIDN(Twse sector).csv"), "CSV bulk file");
Dictionary <string, string> IDNRemovedWebsiteTwse = CompareWithDb(oldTwse, resultsTwse);
OutPutDictionadyTest(IDNRemovedWebsiteTwse, Path.Combine(_configObj.WorkingFolder, "IDNRemovedWebsite(Twse sector).csv"));
AddResult("IDNRemovedWebsite", Path.Combine(_configObj.WorkingFolder, "IDNRemovedWebsite(Twse sector).csv"), "CSV bulk file");
//LogMessage("Look for deletions in Twse sector");
//OutPutDictionadyTest(resultsTwse, Path.Combine(_configObj.WorkingFolder, "resultsList(Twse sector).csv"));
//OutPutDictionadyTest(oldTwse, Path.Combine(_configObj.WorkingFolder, "dbList(Twse sector).csv"));
//Dictionary<string, string> updateTwse = CompareWithDb(resultsTwse, oldTwse);
//OutPutDictionadyTest(updateTwse, Path.Combine(_configObj.WorkingFolder, "resultsListRemovedbList(Twse sector).csv"));
//Dictionary<string, string> updateTwse1 = CompareWithDb(oldTwse, resultsTwse);
//OutPutDictionadyTest(updateTwse1, Path.Combine(_configObj.WorkingFolder, "dbListRemoveresultsList(Twse sector).csv"));
//WriteDeleteInTxt(updateTwse, true);
//LogMessage("Look for additions in Twse sector");
//WriteAddition(FindAddition(resultsTwse, oldTwse), String.Format("twse_additions_{0}.txt", DateTime.Now.ToString("ddMMMHHmm")));
LogMessage("Get Gtsm sector");
Dictionary <string, string> sectorsGtsm = GetGtsmSector();
Dictionary <string, string> resultsGtsm = GetResultsGtsm(gtsmList, sectorsGtsm);
Dictionary <string, string> oldGtsm = GetDbList(sectorsGtsm);
LogMessage("Look for deletions in Gtsm sector");
Dictionary <string, string> duplicatesDBGtsm = FindDuplicates(oldGtsm);
OutPutDictionadyTest(duplicatesDBGtsm, Path.Combine(_configObj.WorkingFolder, "IDNDuplicates(Gtsm sector).csv"));
AddResult("IDNDuplicates", Path.Combine(_configObj.WorkingFolder, "IDNDuplicates(Gtsm sector).csv"), "CSV bulk file");
Dictionary <string, string> duplicatesWebsiteGtsm = FindDuplicates(resultsGtsm);
OutPutDictionadyTest(duplicatesWebsiteGtsm, Path.Combine(_configObj.WorkingFolder, "WebsiteDuplicates(Gtsm sector).csv"));
AddResult("WebsiteDuplicates", Path.Combine(_configObj.WorkingFolder, "WebsiteDuplicates(Gtsm sector).csv"), "CSV bulk file");
Dictionary <string, string> WebsiteRemovedIDNGtsm = CompareWithDb(resultsGtsm, oldGtsm);
OutPutDictionadyTest(WebsiteRemovedIDNGtsm, Path.Combine(_configObj.WorkingFolder, "WebsiteRemovedIDN(Gtsm sector).csv"));
AddResult("WebsiteRemovedIDN", Path.Combine(_configObj.WorkingFolder, "WebsiteRemovedIDN(Gtsm sector).csv"), "CSV bulk file");
Dictionary <string, string> IDNRemovedWebsiteGtsm = CompareWithDb(oldGtsm, resultsGtsm);
OutPutDictionadyTest(IDNRemovedWebsiteGtsm, Path.Combine(_configObj.WorkingFolder, "IDNRemovedWebsite(Gtsm sector).csv"));
AddResult("IDNRemovedWebsite", Path.Combine(_configObj.WorkingFolder, "IDNRemovedWebsite(Gtsm sector).csv"), "CSV bulk file");
//LogMessage("Look for deletions in Gtsm sector");
//OutPutDictionadyTest(resultsTwse, Path.Combine(_configObj.WorkingFolder, "resultsList(Gtsm sector).csv"));
//OutPutDictionadyTest(oldTwse, Path.Combine(_configObj.WorkingFolder, "dbList(Gtsm sector).csv"));
//Dictionary<string, string> updateGtsm = CompareWithDb(resultsGtsm, oldGtsm);
//OutPutDictionadyTest(updateGtsm, Path.Combine(_configObj.WorkingFolder, "resultsListRemovedbList(Gtsm sector).csv"));
//Dictionary<string, string> updateGtsm1 = CompareWithDb(oldGtsm, resultsGtsm);
//OutPutDictionadyTest(updateGtsm1, Path.Combine(_configObj.WorkingFolder, "dbListRemoveresultsList(Gtsm sector).csv"));
//WriteDeleteInTxt(updateGtsm);
//LogMessage("Look for additions in Gtsm sector");
//WriteAddition(FindAddition(resultsGtsm, oldGtsm), String.Format("gtsm_additions_{0}.txt", DateTime.Now.ToString("ddMMMHHmm")));
}
catch (Exception ex)
{
throw new Exception("Error while running task: " + ex.Message, ex);
}
finally
{
_workbook.Close();
_app.Dispose();
}
}
/// <summary>
/// updateFmToLocal
/// </summary>
public void UpdateFMToLocal()
{
DateTime dateValue;
fMFilePath = configObj.FMFilePath;
if (File.Exists(fMFilePath))
{
ExcelApp app = new ExcelApp(false, false);
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, fMFilePath);
Worksheet ws = workbook.Worksheets[1] as Worksheet;
Range rangeISIN = ws.Range["G1"];
ExcelUtil.InsertBlankCols(rangeISIN, 1);
ws.Cells[4, 7] = "ISIN_New";
Range rangeTicker = ws.Range["I1"];
ExcelUtil.InsertBlankCols(rangeTicker, 1);
ws.Cells[4, 9] = "Ticker_New";
Range rangeQACommonName = ws.Range["L1"];
ExcelUtil.InsertBlankCols(rangeQACommonName, 1);
ws.Cells[4, 12] = "QACommonName_New";
Range rangeMatDate = ws.Range["N1"];
ExcelUtil.InsertBlankCols(rangeMatDate, 1);
ws.Cells[4, 14] = "MatDate_New";
Range rangeStrikePrice = ws.Range["P1"];
ExcelUtil.InsertBlankCols(rangeStrikePrice, 1);
ws.Cells[4, 16] = "StrikePrice_New";
foreach (KoreaFMLine fm in listKFM)
{
for (int i = 4; ws.Range["C" + i].Value2 != null; i++)
{
if (fm.RIC.Trim() == ws.Range["C" + i].Value2.ToString())
{
ws.Cells[i, 7] = fm.ISIN.Trim();
ws.Cells[i, 9] = fm.Ticker.Trim();
ws.Cells[i, 12] = fm.QACommonName.Trim();
dateValue = DateTime.ParseExact(fm.MatDate.Trim(), "yyyyMMdd", new CultureInfo("en-US"), DateTimeStyles.None);
fm.MatDate = dateValue.ToString("dd-MMM-yy");
ws.Cells[i, 14] = fm.MatDate;
ws.Cells[i, 16] = fm.StrikePrice.Trim();
if (ws.Range["F" + i].Value2.ToString() != fm.ISIN.Trim())
{
ExcelUtil.GetRange(i, 7, ws).Interior.Color = System.Drawing.Color.FromArgb(0, 200, 0).ToArgb();
}
if (ws.Range["H" + i].Value2.ToString() != fm.Ticker.Trim())
{
ExcelUtil.GetRange(i, 9, ws).Interior.Color = System.Drawing.Color.FromArgb(0, 200, 0).ToArgb();
}
if (ws.Range["K" + i].Value2.ToString() != fm.QACommonName.Trim())
{
ExcelUtil.GetRange(i, 12, ws).Interior.Color = System.Drawing.Color.FromArgb(0, 200, 0).ToArgb();
}
if (ws.Range["M" + i].Value2.ToString() != fm.MatDate.Trim())
{
ExcelUtil.GetRange(i, 14, ws).Interior.Color = System.Drawing.Color.FromArgb(0, 200, 0).ToArgb();
}
if (ws.Range["O" + i].Value2.ToString() != fm.StrikePrice.Trim())
{
ExcelUtil.GetRange(i, 16, ws).Interior.Color = System.Drawing.Color.FromArgb(0, 200, 0).ToArgb();
}
}
}
}
app.ExcelAppInstance.AlertBeforeOverwriting = false;
workbook.Save();
workbook.Close();
app.Dispose();
}
else
{
Logger.LogErrorAndRaiseException("FM file is not exist");
}
}
/// <summary>
/// Generate FM file for Drop.
/// </summary>
private void GenerateFMFiles()
{
foreach (CompanyWarrantDropTemplate item in waDrop)
{
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
string msg = "Excel could not be started. Check that your office installation and project reference are correct!";
Logger.Log(msg, Logger.LogType.Error);
return;
}
string fileName = "KR FM (Company Warrant Drop) Request_" + item.RIC + " (wef " + item.EffectiveDate + ").xls";
string filePath = Path.Combine(configObj.FM, fileName);
try
{
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, filePath);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (wSheet == null)
{
string msg = "Worksheet could not be started. Check that your office installation and project reference are correct!";
Logger.Log(msg, Logger.LogType.Error);
return;
}
wSheet.Name = "DROP";
((Range)wSheet.Columns["A", System.Type.Missing]).ColumnWidth = 20;
((Range)wSheet.Columns["B", System.Type.Missing]).ColumnWidth = 2;
((Range)wSheet.Columns["C", System.Type.Missing]).ColumnWidth = 30;
((Range)wSheet.Columns["A:C", System.Type.Missing]).Font.Name = "Arial";
((Range)wSheet.Rows[1, Type.Missing]).Font.Bold = System.Drawing.FontStyle.Bold;
((Range)wSheet.Rows[1, Type.Missing]).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
wSheet.Cells[1, 1] = "FM Request";
wSheet.Cells[1, 2] = " ";
wSheet.Cells[1, 3] = "Deletion";
((Range)wSheet.Cells[3, 1]).Font.Bold = System.Drawing.FontStyle.Bold;
((Range)wSheet.Cells[3, 1]).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
wSheet.Cells[3, 1] = "Effective Date";
wSheet.Cells[3, 2] = ":";
((Range)wSheet.Cells[3, 3]).NumberFormat = "@";
wSheet.Cells[3, 3] = item.EffectiveDate;
((Range)wSheet.Cells[4, 1]).Font.Bold = System.Drawing.FontStyle.Bold;
((Range)wSheet.Cells[4, 1]).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
wSheet.Cells[4, 1] = "RIC";
wSheet.Cells[4, 2] = ":";
wSheet.Cells[4, 3] = item.RIC;
((Range)wSheet.Cells[5, 1]).Font.Bold = System.Drawing.FontStyle.Bold;
((Range)wSheet.Cells[5, 1]).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
wSheet.Cells[5, 1] = "ISIN";
wSheet.Cells[5, 2] = ":";
wSheet.Cells[5, 3] = item.ISIN;
wSheet.Cells[6, 1] = "QA Short Name";
wSheet.Cells[6, 2] = ":";
((Range)wSheet.Cells[6, 3]).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Blue);
((Range)wSheet.Cells[6, 3]).Font.Underline = true;
wSheet.Cells[6, 3] = item.QAShortName;
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
MailToSend mail = new MailToSend();
mail.ToReceiverList.AddRange(configObj.MailTo);
mail.CCReceiverList.AddRange(configObj.MailCC);
mail.MailSubject = Path.GetFileNameWithoutExtension(fileName);
mail.AttachFileList.Add(filePath);
mail.MailBody = "Company Warrant Drop:\t\t" + item.RIC + "\r\n\r\n"
+ "Effective Date:\t\t" + item.EffectiveDate + "\r\n\r\n\r\n\r\n";
string signature = string.Join("\r\n", configObj.MailSignature.ToArray());
mail.MailBody += signature;
AddResult(fileName, filePath, "FM File");
Logger.Log("Generate FM file successfully. Filepath is " + filePath);
}
catch (Exception ex)
{
String msg = "Error found in generating FM file for RIC:" + item.RIC + " \r\n" + ex.Message;
Logger.Log(msg, Logger.LogType.Error);
}
finally
{
excelApp.Dispose();
}
}
}
private void GenerateAdjustmentFile_xls()
{
if (spcrList.Count > 0)
{
ExcelApp excelApp = new ExcelApp(false, false);
if (excelApp.ExcelAppInstance == null)
{
}
try
{
String filename = String.Format("KR FM (ELW Adjustment) Strike Price and Conversion Ratio (wef {0}).xls", spcrList[0].EffectiveDate);
String ipath = "C:\\Korea_Auto\\ELW_FM\\ELW_Adjustment\\" + filename;
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(excelApp, ipath);
Worksheet wSheet = ExcelUtil.GetWorksheet("Sheet1", wBook);
if (wSheet == null)
{
}
if (wSheet.get_Range("C1", Type.Missing).Value2 == null)
{
((Range)wSheet.Columns["A", System.Type.Missing]).ColumnWidth = 15;
((Range)wSheet.Columns["B", System.Type.Missing]).ColumnWidth = 15;
((Range)wSheet.Columns["C", System.Type.Missing]).ColumnWidth = 15;
((Range)wSheet.Columns["D", System.Type.Missing]).ColumnWidth = 15;
((Range)wSheet.Columns["E", System.Type.Missing]).ColumnWidth = 20;
((Range)wSheet.Columns["F", System.Type.Missing]).ColumnWidth = 20;
((Range)wSheet.Columns["G", System.Type.Missing]).ColumnWidth = 35;
((Range)wSheet.Columns["A:G", System.Type.Missing]).Font.Name = "Arial";
((Range)wSheet.Rows[1, Type.Missing]).Font.Bold = System.Drawing.FontStyle.Bold;
((Range)wSheet.Rows[1, Type.Missing]).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
((Range)wSheet.Rows[1, Type.Missing]).WrapText = true;
wSheet.Cells[1, 1] = "Update Date";
wSheet.Cells[1, 2] = "Effective Date";
wSheet.Cells[1, 3] = "RIC";
wSheet.Cells[1, 4] = "ISIN";
wSheet.Cells[1, 5] = "Strike Price(After Adjustment)";
wSheet.Cells[1, 6] = "Conversion Ratio(After Adjustment)";
wSheet.Cells[1, 7] = "QA Common Name";
}
int startLine = 2;
foreach (var item in spcrList)
{
wSheet.Cells[startLine, 1] = DateTime.Today.ToString("dd-MMM-yy", new CultureInfo("en-US"));
wSheet.Cells[startLine, 2] = item.EffectiveDate;
wSheet.Cells[startLine, 3] = item.RIC;
wSheet.Cells[startLine, 4] = item.ISIN;
wSheet.Cells[startLine, 5] = item.StrikePrice;
wSheet.Cells[startLine, 6] = item.ConversionRatio;
wSheet.Cells[startLine, 7] = item.QACommonName;
startLine++;
}
excelApp.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
}
catch (Exception ex)
{
String msg = "Error found in GenerateAdjustmentFile_xls() : \r\n" + ex.ToString();
Logger.Log(msg, Logger.LogType.Error);
return;
}
finally
{
excelApp.Dispose();
}
}
}
