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