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(); } }
//Generate broker3h file public void GenerateBroker3hFile(List <BrokerInfo> brokerList) { string broker3hFilePath = MiscUtil.BackupFileWithNewName(configObj.BROKER3H_FILE_PATH); if (File.Exists(configObj.CHINESE_VERSION_FILE_PATH)) { string ChineseVersionFileBakFilePath = MiscUtil.BackupFileWithNewName(configObj.CHINESE_VERSION_FILE_PATH); } using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, broker3hFilePath); if (workbook.ReadOnly == true) { workbook.ReadOnlyRecommended = false; } int brokerRowsSum = 0; Logger.Log("**************************************************************************"); //Copy the content in Master sheet in Insert sheet Logger.Log("Begin to Generate the Insert Sheet"); GenerateInsertSheet(workbook, brokerRowsSum, brokerList); Logger.Log("**************************************************************************"); //Generate Chinese version File workbook.SaveCopyAs(configObj.CHINESE_VERSION_FILE_PATH); TaskResultList.Add(new TaskResultEntry("Broker3h_Chinese.xls", "Chinese Version Broker3h file", configObj.CHINESE_VERSION_FILE_PATH)); UpdateCColAsEnglish(brokerList, ExcelUtil.GetWorksheet(configObj.MASTER_WORKSHEET_NAME, workbook)); UpdateDColAsEnglish(brokerList, ExcelUtil.GetWorksheet(configObj.MASTER_WORKSHEET_NAME, workbook)); UpdateCColAsEnglish(brokerList, ExcelUtil.GetWorksheet(configObj.INSERT_WORKSHEET_NAME, workbook)); UpdateDColAsEnglish(brokerList, ExcelUtil.GetWorksheet(configObj.INSERT_WORKSHEET_NAME, workbook)); UpdatePageId(ExcelUtil.GetWorksheet(configObj.INSERT_WORKSHEET_NAME, workbook)); workbook.SaveCopyAs(configObj.BROKER3H_FILE_PATH); TaskResultList.Add(new TaskResultEntry("Broker3h.xls", "English Version Broker3h file", configObj.BROKER3H_FILE_PATH)); workbook.Close(false, workbook.FullName, false); } }
private void GenerateFile(List <List <string> > listList, string strSaveFilePath) { if (listList != null) { string content = string.Empty; foreach (var str in listList) { content += string.Format("{0}\t", str[0]); content += string.Format("{0}\t", str[1]); content += string.Format("{0}\t", str[2]); content += "\r\n"; } try { File.WriteAllText(strSaveFilePath, content); TaskResultList.Add(new TaskResultEntry("IDN file", "IDN", strSaveFilePath)); } catch (Exception ex) { Logger.Log(string.Format("Error happens when generating file. Ex: {0} .", ex.Message)); } } }
private void GenerateFile(List <string> list, string path) { StringBuilder sb = new StringBuilder(); sb.Append("MissingRIC\r\n"); foreach (var str in list) { sb.Append(str); sb.Append("\r\n"); } try { File.WriteAllText(path, sb.ToString()); TaskResultList.Add(new TaskResultEntry("TWipoQC", "MissingRIC", path)); } catch (Exception ex) { string msg = string.Format("Error happens when generating file. Ex: {0} .", ex.Message); Logger.Log(msg, Logger.LogType.Error); } }
private void GenerateGedaFile(List <string> rics) { try { string fileName = string.Format("KR_DROP_{0}.txt", DateTime.Today.ToString("yyyyMMdd")); string filePath = Path.Combine(configObj.BulkFile, fileName); FileUtil.WriteOutputFile(filePath, rics, "RIC", WriteMode.Overwrite); TaskResultList.Add(new TaskResultEntry("Output Folder", "Output Folder", configObj.BulkFile)); TaskResultList.Add(new TaskResultEntry(fileName, fileName, filePath, FileProcessType.GEDA_BULK_CHAIN_RIC_DELETION)); Logger.Log("Generate drop GEDA file. OK!"); } catch (Exception ex) { string msg = "Error found in generating GEDA drop file. Error:" + ex.Message; Logger.Log(msg, Logger.LogType.Error); throw ex; } }
public void GenerateIndiceRicFile(Dictionary <string, List <string> > indicesRicDic) { using (ExcelApp app = new ExcelApp(false, false)) { int startPos = 0; var workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.TargetIndicesAdjustmentFileName); int sheetNum = (indicesRicDic.Keys.Count + configObj.ChainNumPerSheet - 1) / configObj.ChainNumPerSheet; if (sheetNum > workbook.Worksheets.Count) { workbook.Worksheets.Add(Missing.Value, Missing.Value, sheetNum - workbook.Worksheets.Count, Missing.Value); } for (int i = 0; i < sheetNum; i++) { var worksheet = workbook.Worksheets[i + 1] as Worksheet; startPos = i * configObj.ChainNumPerSheet; int endPos = indicesRicDic.Keys.Count < (startPos + configObj.ChainNumPerSheet) ? indicesRicDic.Keys.Count : (startPos + configObj.ChainNumPerSheet); WriterWorksheet(worksheet, indicesRicDic, startPos, endPos); } TaskResultList.Add(new TaskResultEntry("New Indices File", "", workbook.FullName)); workbook.Close(true, workbook.FullName, true); } }
//Get the official code of which ISIN needs updated public List <RicISINInfo> GetAllNewGenerateISINList() { List <RicISINInfo> ricInfoList = new List <RicISINInfo>(); Range nameRange; Range isinRange; Range ricRange; int lastUsedRow; File.Copy(configObj.SourceFilePath, NewTargetFilePath(configObj.SourceFilePath)); using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.SourceFilePath); //Get Equity ric from "Equity" worksheet var equityWorksheet = ExcelUtil.GetWorksheet(configObj.EquityWorksheet, workbook); if (equityWorksheet == null) { Logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.EquityWorksheet, workbook.Name)); } lastUsedRow = equityWorksheet.UsedRange.Row + equityWorksheet.UsedRange.Rows.Count - 1; for (int i = 1; i <= lastUsedRow; i++) { nameRange = ExcelUtil.GetRange(i, 20, equityWorksheet); isinRange = ExcelUtil.GetRange(i, 19, equityWorksheet); ricRange = ExcelUtil.GetRange(i, 3, equityWorksheet); if ((nameRange.Value2 != null && nameRange.Value2.ToString() != string.Empty) && (isinRange.Value2 == null || isinRange.Value2.ToString() == string.Empty)) { RicISINInfo ricInfo = new RicISINInfo { type = "Equity", OfficialCode = ExcelUtil.GetRange(i, 6, equityWorksheet).Value2.ToString(), Name = nameRange.Value2.ToString() }; ricInfo.ISIN = GetISINFromCode(ricInfo.OfficialCode); ricInfo.ric = ricRange.Value2.ToString(); if (ricInfo.ISIN != string.Empty) { isinRange.Value2 = ricInfo.ISIN; ricInfoList.Add(ricInfo); } } } //Get bond ric from "Bond" worksheet List <RicISINInfo> allBondISINList = GetAllISINForGoverBond(); var bondWorksheet = ExcelUtil.GetWorksheet(configObj.BondWorksheet, workbook); if (bondWorksheet == null) { Logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.BondWorksheet, workbook.Name)); } lastUsedRow = bondWorksheet.UsedRange.Row + bondWorksheet.UsedRange.Rows.Count - 1; for (int j = 1; j <= lastUsedRow; j++) { nameRange = ExcelUtil.GetRange(j, 22, bondWorksheet); isinRange = ExcelUtil.GetRange(j, 21, bondWorksheet); ricRange = ExcelUtil.GetRange(j, 3, bondWorksheet); if ((nameRange.Value2 != null && nameRange.Value2.ToString() != string.Empty) && ((isinRange.Value2 == null) || (isinRange.Value2.ToString() == string.Empty))) { RicISINInfo ricInfo = new RicISINInfo { type = "Bond", Name = nameRange.Value2.ToString(), OfficialCode = ExcelUtil.GetRange(j, 5, bondWorksheet).Value2.ToString(), ric = ricRange.Value2.ToString() }; ricInfo.ISIN = !ricInfo.Name.Contains("國債") ? GetISINFromCode(ricInfo.OfficialCode) : GetISINFromName(ricInfo.Name, allBondISINList); if (ricInfo.ISIN != string.Empty) { ricInfoList.Add(ricInfo); isinRange.Value2 = ricInfo.ISIN; } } } TaskResultList.Add(new TaskResultEntry("Updated Source File", "All the newly generated ISIN had been updated into the file.", workbook.FullName)); workbook.Close(true, workbook.FullName, true); } return(ricInfoList); }
private void GenerateMAINSSExcelAndXmlFile(string xlsFilePath, TradingInfo tradingInfo) { xlsFilePath = MiscUtil.BackUpFileWithDateFolder(xlsFilePath, false); //MiscUtil.BackupFileWithNewName(xlsFilePath); List <string> linesToWrite = new List <string>(); foreach (StockInfo stockInfo in tradingInfo.StockList) { linesToWrite.Add(stockInfo.ToSingleLine()); } linesToWrite.Add(""); if (!string.IsNullOrEmpty(tradingInfo.DesignatedSecuritiesRecordingSum)) { linesToWrite.Add(tradingInfo.DesignatedSecuritiesRecordingSum.PadLeft(recordAlignrightLength)); } if (!string.IsNullOrEmpty(tradingInfo.DesignatedSharesShortSoldSum)) { linesToWrite.Add(tradingInfo.DesignatedSharesShortSoldSum.PadLeft(recordAlignrightLength)); } if (!string.IsNullOrEmpty(tradingInfo.DesignatedShortSellTurnoverShares)) { linesToWrite.Add(tradingInfo.DesignatedShortSellTurnoverShares.PadLeft(recordAlignrightLength)); } if (!string.IsNullOrEmpty(tradingInfo.DesignatedShortSellTurnoverValue)) { linesToWrite.Add(tradingInfo.DesignatedShortSellTurnoverValue.PadLeft(recordAlignrightLength)); } if (!string.IsNullOrEmpty(tradingInfo.HKDTurnoverValue)) { linesToWrite.Add(tradingInfo.HKDTurnoverValue.PadLeft(recordAlignrightLength)); } using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, xlsFilePath); var worksheet = ExcelUtil.GetWorksheet(configObj.HKMAINSS_CONFIG_Worksheet, workbook); if (worksheet == null) { LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.HKGEMSS_Worksheet, workbook.Name)); } //Generate MAINSSExcelFile using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down)) { // Allocate spaces int startOffset = (configObj.HKMAINSS_CONFIG_StartPage - 1) * configObj.HKMAINSS_CONFIG_LineNum; int startRow = startOffset + 1; int curLineNum = 0; int insertBlankPageNum = (linesToWrite.Count - 1) / configObj.HKMAINSS_CONFIG_LineEach + 1; ExcelUtil.InsertBlankRows(ExcelUtil.GetRange(startRow, 1, worksheet), insertBlankPageNum * 24); writer.PlaceNext(startRow, 1); // Form 1st line and write string line = string.Empty; List <string> headerLineList = new List <string>(); headerLineList.Add(tradingInfo.DateStr); line = "RIC STOCK NAME SHARES TURNOVER ($)"; headerLineList.Add(line); line = "========= ==================== =============== ==============="; headerLineList.Add(line); for (int i = 0; i < insertBlankPageNum; i++) { int curStartRow = startOffset + i * configObj.HKMAINSS_CONFIG_LineNum + 1; int nextStartRow = curStartRow + 24; writer.WriteLine(headerLineList[0]); writer.WriteLine(headerLineList[1]); writer.WriteLine(headerLineList[2]); //keep a line without text. writer.MoveNext(); while (writer.Row < nextStartRow && curLineNum < linesToWrite.Count) { writer.WriteLine(linesToWrite[curLineNum]); curLineNum++; } writer.PlaceNext(nextStartRow, 1); } // Fill designated securities summary with page no. and background color. for (int page = configObj.HKMAINSS_CONFIG_StartPage; page <= configObj.HKMAINSS_CONFIG_TotalPage; page++) { string id = "HK/MAINSS" + page.ToString("D2"); int upRow = configObj.HKMAINSS_CONFIG_LineNum * (page - 1) + 1; int downRow = upRow + (configObj.HKMAINSS_CONFIG_LineNum - 1); writer.PlaceNextAndWriteLine(upRow, 2, id); writer.PlaceNextAndWriteLine(downRow, 2, id); Range pageRange = ExcelUtil.GetRange(upRow, 2, downRow, 2, worksheet); if (page % 2 == 0) { pageRange.Interior.Color = 16777164.0; } else { pageRange.Interior.Color = 10092543.0; } } Range rangToRemove = ExcelUtil.GetRange(writer.Row, 1, writer.Row + 24 * insertBlankPageNum, 2, worksheet); rangToRemove.Clear(); } //Generate MAINSSXmlFile ProductionXmlFileTemplate xmlFileTem = GetHKMAINSSXmlFileContent(worksheet); ConfigUtil.WriteXml(configObj.HKMAINSS_CONFIG_XmlPath, xmlFileTem); TaskResultList.Add(new TaskResultEntry("XML file for HKMAINSS", "", configObj.HKMAINSS_CONFIG_XmlPath)); if (File.Exists(configObj.HKMAINSS_CONFIG_Workbook)) { File.Delete(configObj.HKMAINSS_CONFIG_Workbook); } workbook.SaveCopyAs(configObj.HKMAINSS_CONFIG_Workbook); TaskResultList.Add(new TaskResultEntry("HKMAINSS", "", configObj.HKMAINSS_CONFIG_Workbook)); workbook.Close(false, xlsFilePath, true); } }
/// <summary> /// Generate the HKMAIN01_10Excel file and the HKMAIN01_10Xml file. /// </summary> /// <param name="main01_10TradingInfo">the Main01_10 trading News Info.</param> public void GenerateMain01_10ExcelAndXMLFile(TradingInfo main01_10TradingInfo) { string main01_10FilePath = MiscUtil.BackUpFileWithDateFolder(configObj.HKMAIN01_10_Workbook, false); //MiscUtil.BackupFileWithNewName(configObj.HKMAIN01_10_Workbook); using (ExcelApp app = new ExcelApp(true, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, main01_10FilePath); var worksheet = ExcelUtil.GetWorksheet(configObj.HKMAIN01_10_Worksheet, workbook); if (worksheet == null) { LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.HKMAIN01_10_Worksheet, workbook.Name)); } using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right)) { // Update the date string line = string.Empty; writer.WriteLine(main01_10TradingInfo.DateStr); //Copy from line 4 writer.PlaceNext(4, 1); foreach (StockInfo stock in main01_10TradingInfo.StockList) { writer.WriteLine(stock.Ric); writer.WriteLine(stock.StockName); writer.WriteLine(stock.Shares); writer.WriteLine(stock.Turnover); writer.PlaceNext(writer.Row + 1, 1); } int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; ExcelUtil.GetRange(writer.Row, writer.Col, lastUsedRow, writer.Col + 4, worksheet).Clear(); writer.PlaceNext(writer.Row + 2, 1); writer.WriteLine(main01_10TradingInfo.DesignatedSecuritiesRecordingSum); writer.PlaceNext(writer.Row + 1, 1); writer.WriteLine(main01_10TradingInfo.DesignatedSharesShortSoldSum); writer.PlaceNext(writer.Row + 1, 1); writer.WriteLine(main01_10TradingInfo.DesignatedShortSellTurnoverShares); writer.PlaceNext(writer.Row + 1, 1); writer.WriteLine(main01_10TradingInfo.DesignatedShortSellTurnoverValue); writer.PlaceNext(writer.Row + 1, 1); writer.WriteLine(main01_10TradingInfo.HKDTurnoverValue); ExcelUtil.GetRange(1, 1, writer.Row - 1, 4, worksheet).Borders.LineStyle = 0; } ProductionXmlFileTemplate xmlFileTem = GetHKMAINXmlFileContent(worksheet); ConfigUtil.WriteXml(configObj.HKMAIN01_10_XmlPath, xmlFileTem); TaskResultList.Add(new TaskResultEntry("XML file for HK-MAIN01-10", "", configObj.HKMAIN01_10_XmlPath)); //Save the HK-MAIN01-10 file as a copy if (File.Exists(configObj.HKMAIN01_10_Workbook)) { File.Delete(configObj.HKMAIN01_10_Workbook); } app.ExcelAppInstance.AlertBeforeOverwriting = false; workbook.SaveCopyAs(configObj.HKMAIN01_10_Workbook); TaskResultList.Add(new TaskResultEntry("HKMAINSS01-10", "", configObj.HKMAIN01_10_Workbook)); workbook.Close(false, main01_10FilePath, true); } }
/// <summary> /// Update HK-GEMSS.xls file and create HK-GEMSS.xml /// </summary> /// <param name="gemTradingInfo">the GEM trading News Info.</param> public void UpdateGemssFileAndGenerateXMLFile(TradingInfo gemTradingInfo) { string gemssFilePath = MiscUtil.BackUpFileWithDateFolder(configObj.HKGEMSS_Workbook, false); using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, gemssFilePath); var worksheet = ExcelUtil.GetWorksheet(configObj.HKGEMSS_Worksheet, workbook); if (worksheet == null) { LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.HKGEMSS_Worksheet, workbook.Name)); } using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down)) { // Allocate spaces ExcelUtil.InsertBlankRows(ExcelUtil.GetRange("A1", worksheet), 24); writer.PlaceNext(1, 1); // Form 1st line and write string line = string.Empty; writer.WriteLine(gemTradingInfo.DateStr); // Copy 2nd and 3rd line from line 26 and line 27 writer.WriteLine(worksheet.Cells[writer.Row + 24, writer.Col]); writer.WriteLine(worksheet.Cells[writer.Row + 24, writer.Col]); if (gemTradingInfo.StockList == null) { writer.WriteLine("NIL"); while (writer.Row < 19) { writer.WriteLine(" "); } } else { // Fill stock info foreach (StockInfo stockInfo in gemTradingInfo.StockList) { StringBuilder sb = new StringBuilder(); sb.Append(stockInfo.Ric.PadRight(12)); sb.Append(stockInfo.StockName.PadRight(27)); sb.Append(stockInfo.Shares.PadLeft(15)); sb.Append(stockInfo.Turnover.PadLeft(20)); line = sb.ToString(); writer.WriteLine(line); } while (writer.Row < 19) { writer.WriteLine(" "); } //Fill non-designated securities summary if non-designated securities exist writer.PlaceNext(19, 1); if (!string.IsNullOrEmpty(gemTradingInfo.NonDesignatedSecuritiesRecordingSum) && gemTradingInfo.NonDesignatedSecuritiesRecordingSum != "0") { writer.WriteLine(gemTradingInfo.NonDesignatedSecuritiesRecordingSum.PadLeft(recordAlignrightLength)); writer.WriteLine(gemTradingInfo.NonDesignatedSharesShortSoldSum.PadLeft(recordAlignrightLength)); writer.WriteLine(gemTradingInfo.NonDesignatedShortSellTransactionSum.PadLeft(recordAlignrightLength)); } // Fill designated securities summary writer.WriteLine(gemTradingInfo.DesignatedSecuritiesRecordingSum.PadLeft(recordAlignrightLength)); writer.WriteLine(gemTradingInfo.DesignatedSharesShortSoldSum.PadLeft(recordAlignrightLength)); writer.WriteLine(gemTradingInfo.DesignatedShortSellTurnoverShares.PadLeft(recordAlignrightLength)); writer.WriteLine(gemTradingInfo.DesignatedShortSellTurnoverValue.PadLeft(recordAlignrightLength)); if (!string.IsNullOrEmpty(gemTradingInfo.HKDTurnoverValue)) { writer.WriteLine(gemTradingInfo.HKDTurnoverValue.PadLeft(recordAlignrightLength)); } } for (int page = 1; page <= 25; page++) { string id = "HK/GEMSS" + page.ToString("D2"); int upRow = 24 * (page - 1) + 1; int downRow = upRow + 23; writer.PlaceNextAndWriteLine(upRow, 3, id); writer.PlaceNextAndWriteLine(downRow, 3, id); } //Fill color for C columns Range range = ExcelUtil.GetRange(1, 3, 24, 3, worksheet); range.Interior.Color = ExcelUtil.GetRange(49, 3, worksheet).Interior.Color; ProductionXmlFileTemplate xmlFileTem = GetGemssXmlFileContent(worksheet); ConfigUtil.WriteXml(configObj.HKGEMSS_XmlPath, xmlFileTem); TaskResultList.Add(new TaskResultEntry("XML file for HK-GEMSS", "", configObj.HKGEMSS_XmlPath)); if (File.Exists(configObj.HKGEMSS_Workbook)) { File.Delete(configObj.HKGEMSS_Workbook); } //Save files as a copy workbook.SaveCopyAs(configObj.HKGEMSS_Workbook); TaskResultList.Add(new TaskResultEntry("HK-GEMSS", "", configObj.HKGEMSS_Workbook)); //Close current opend excel file workbook.Close(false, gemssFilePath, false); } } }
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 GenerateFiles(List <ETFinsertTaskTemplate> res) { DateTime lastTradingDay = MiscUtil.GetLastTradingDay(DateTime.Now, _holidayList, 1); StringBuilder sb = new StringBuilder(); string fileContents = ""; sb.Append("TQSK.DAT;21-MAR-2012 15:00:00;TPS;\r\n"); sb.Append("GEN_VAL6;GV6_TEXT;\r\n"); foreach (var term in res) { if (term.Code.Equals("006201")) { sb.AppendFormat("{0}.TWO;{1};144700;\r\n", term.Code, term.EstimatedValue); } else { sb.AppendFormat("{0}.TW;{1};144700;\r\n", term.Code, term.EstimatedValue); } } sb.Append("\r\n\r\n\r\n\r\n\r\n"); fileContents += sb.ToString(); sb.Remove(0, sb.Length); //file one sb.Append("TAM.DAT;21-MAR-2012 15:00:00;TPS;\r\n"); sb.Append("GEN_VAL7;\r\n"); foreach (var term in from term in res let @where = string.Format("where Code='{0}' and GrabTime='{1}' and EstimatedValue='{2}'", term.Code, lastTradingDay.ToString("yyyy/MM/dd", new CultureInfo("en-US")), term.EstimatedValue) let table = ManagerBase.Select(TableName, new[] { "*" }, @where) where table.Rows.Count == 0 select term) { if (term.Code.Equals("006201")) { sb.AppendFormat("{0}.TWO;{1};\r\n", term.Code, term.ReferenceValue); } else { sb.AppendFormat("{0}.TW;{1};\r\n", term.Code, term.ReferenceValue); } } sb.Append("\r\n\r\n\r\n\r\n\r\n"); fileContents += sb.ToString(); string file2 = sb.ToString(); sb.Remove(0, sb.Length); //file two sb.Append("TAM.DAT;21-MAR-2012 15:00:00;TPS;\r\n"); sb.Append("SPARE_DATE5;\r\n"); foreach (var term in res) { if (term.Code.Equals("006201")) { sb.AppendFormat("{0}.TWO;{1};\r\n", term.Code, DateTime.Now.ToString("dd/MM/yyyy")); } else { sb.AppendFormat("{0}.TW;{1};\r\n", term.Code, DateTime.Now.ToString("dd/MM/yyyy")); } } sb.Append("\r\n\r\n\r\n\r\n\r\n"); fileContents += sb.ToString(); sb.Remove(0, sb.Length); //file three sb.Append("TAM.DAT;21-MAR-2012 15:00:00;TPS;\r\n"); sb.Append("SPARE_DATE5;\r\n"); sb.AppendFormat("006201.TWO;{0};\r\n", lastTradingDay.ToString("dd/MM/yyyy")); //sb.AppendFormat("006202.TWO;{0};\r\n", lastTradingDay.ToString("dd/MM/yyyy")); sb.Append("\r\n\r\n\r\n\r\n\r\n"); fileContents += sb.ToString(); sb.Remove(0, sb.Length); //file four string path = string.Format("{0}\\TQSK RICs.txt", _configObj.ResultFileDir); File.WriteAllText(path, fileContents, Encoding.UTF8); AddResult(Path.GetFileNameWithoutExtension(path), path, "file"); TaskResultList.Add(new TaskResultEntry(Path.GetFileNameWithoutExtension(path), path, path, CreateMailOne(file2))); }
private void GeneratePageUpdateFile(string templateFilePath, System.Data.DataTable dt) { using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, templateFilePath); var worksheet = ExcelUtil.GetWorksheet("INPUT SHEET", workbook); if (worksheet == null) { Logger.LogErrorAndRaiseException(string.Format("There's no worksheet: {0}", worksheet.Name)); } ((Range)worksheet.Columns["D"]).NumberFormat = "@"; using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 3, 1, ExcelLineWriter.Direction.Right)) { foreach (DataRow dr in dt.Rows) { writer.WriteLine("Create"); writer.WriteLine(""); writer.WriteLine(Convert.ToString(dr["ChangeType"])); string effectiveDate = Convert.ToDateTime(dr["EffectiveDate"]).ToString("ddMMMyy"); writer.WriteLine(effectiveDate); writer.WriteLine(Convert.ToString(dr["DescriptionWas"])); writer.WriteLine(Convert.ToString(dr["DescriptionNow"])); string ricWas = (Convert.ToString(dr["RICWas"])).Replace("D^", "").Replace("^", ""); writer.WriteLine(ricWas); writer.WriteLine(Convert.ToString(dr["RICNow"])); writer.WriteLine(Convert.ToString(dr["ISINWas"])); writer.WriteLine(Convert.ToString(dr["ISINNow"])); writer.WriteLine("Official Code"); writer.WriteLine(Convert.ToString(dr["SecondWas"])); writer.WriteLine(Convert.ToString(dr["SecondNow"])); writer.WriteLine(""); writer.WriteLine(""); writer.WriteLine(""); //Exchange string exchange = Convert.ToString(dr["RICNow"]); if (string.IsNullOrEmpty(exchange)) { exchange = Convert.ToString(dr["RICWas"]); } if (exchange.Contains(".KS")) { exchange = "KSC"; } else if (exchange.Contains(".KQ")) { exchange = "KOE"; } else if (exchange.Contains(".KN")) { exchange = "KNX"; } writer.WriteLine(exchange); string asset = Convert.ToString(dr["Asset"]); if (asset.Equals("KDR")) { asset = "DRC"; } writer.WriteLine(asset); writer.PlaceNext(writer.Row + 1, 1); } } //Run Macros app.ExcelAppInstance.GetType().InvokeMember("Run", BindingFlags.Default | BindingFlags.InvokeMethod, null, app.ExcelAppInstance, new object[] { "FormatData" }); string targetFilePath = Path.GetDirectoryName(templateFilePath); targetFilePath = Path.Combine(targetFilePath, DateTime.Today.ToString("yyyy-MM-dd")); if (!Directory.Exists(targetFilePath)) { Directory.CreateDirectory(targetFilePath); } targetFilePath += "\\Result_Add_Drop_Upload_File.xls"; workbook.SaveCopyAs(targetFilePath); TaskResultList.Add(new TaskResultEntry("Result File", "", targetFilePath)); workbook.Close(false, templateFilePath, false); } }
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(); TaskResultList.Add(new TaskResultEntry("ELWExtractData", "XLS", strFilePath)); } catch (Exception ex) { string msg = "Error found in NDA T&C file :" + ex.ToString(); Logger.Log(msg, Logger.LogType.Error); } finally { excelApp.Dispose(); } }
/// <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", Type.Missing]).ColumnWidth = 20; ((Range)wSheet.Columns["B", Type.Missing]).ColumnWidth = 2; ((Range)wSheet.Columns["C", Type.Missing]).ColumnWidth = 30; ((Range)wSheet.Columns["A:C", Type.Missing]).Font.Name = "Arial"; ((Range)wSheet.Rows[1, Type.Missing]).Font.Bold = FontStyle.Bold; ((Range)wSheet.Rows[1, Type.Missing]).Font.Color = 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 = FontStyle.Bold; ((Range)wSheet.Cells[3, 1]).Font.Color = 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 = FontStyle.Bold; ((Range)wSheet.Cells[4, 1]).Font.Color = 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 = FontStyle.Bold; ((Range)wSheet.Cells[5, 1]).Font.Color = 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 = 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; TaskResultList.Add(new TaskResultEntry(fileName, "FM File", filePath, mail)); 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 GenerateNDAFile() { if (announcementList.Count > 0) { if (!Directory.Exists(configObj.NDA)) { Directory.CreateDirectory(configObj.NDA); } string fileName = "KR" + DateTime.Now.ToString("yyyyMMdd") + "QAADD.csv"; string ndaFile = Path.Combine(configObj.NDA, fileName); StringBuilder kqBuilder = new StringBuilder(); NDAFileTemplate KOSDAQ = new NDAFileTemplate(); kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Ric); kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Tag); kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Base_Asset); kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Ticker_Symbol); kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Asset_Short_Name); kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Asset_Common_Name); kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Type); kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Category); kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Currency); kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Exchange); kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Equity_First_Trading_Day); kqBuilder.AppendFormat(",{0}", KOSDAQ.Nda_Round_Lot_Size); kqBuilder.Remove(0, 1); kqBuilder.AppendLine(); foreach (var item in announcementList) { if (item.RIC.Contains("KQ")) { for (int i = 0; i < 6; i++) { switch (i) { case 0: kqBuilder.AppendFormat("{0}", item.RIC); kqBuilder.AppendFormat(",{0}", "673"); break; case 1: kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "F.KQ"); kqBuilder.AppendFormat(",{0}", "64399"); break; case 2: kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "S.KQ"); kqBuilder.AppendFormat(",{0}", "60673"); break; case 3: kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "stat.KQ"); kqBuilder.AppendFormat(",{0}", "61287"); break; case 4: kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "ta.KQ"); kqBuilder.AppendFormat(",{0}", "64380"); break; case 5: kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "bl.KQ"); kqBuilder.AppendFormat(",{0}", "67094"); break; } AppendOtherColumnToNDAFile(kqBuilder, item, "KOE"); } } if (item.RIC.Contains("KS")) { for (int i = 0; i < 6; i++) { switch (i) { case 0: kqBuilder.AppendFormat("{0}", item.RIC); kqBuilder.AppendFormat(",{0}", "184"); break; case 1: kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "F.KS"); kqBuilder.AppendFormat(",{0}", "64398"); break; case 2: kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "S.KS"); kqBuilder.AppendFormat(",{0}", "60184"); break; case 3: kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "stat.KS"); kqBuilder.AppendFormat(",{0}", "61286"); break; case 4: kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "ta.KS"); kqBuilder.AppendFormat(",{0}", "64379"); break; case 5: kqBuilder.AppendFormat("{0}", item.RIC.Split('.')[0] + "bl.KS"); kqBuilder.AppendFormat(",{0}", "67093"); break; } AppendOtherColumnToNDAFile(kqBuilder, item, "KSC"); } } } File.WriteAllText(ndaFile, kqBuilder.ToString(), Encoding.UTF8); TaskResultList.Add(new TaskResultEntry(fileName, "NDA File", ndaFile, FileProcessType.NDA)); } }
private void GenerateGEDAFile() { if (announcementList.Count > 0) { if (!Directory.Exists(configObj.GEDA)) { Directory.CreateDirectory(configObj.GEDA); } string fileName = "KR_PEO_Bulk_Creation_KOSDAQ.txt"; string gedaFile = Path.Combine(configObj.GEDA, fileName); StringBuilder kqBuilder = new StringBuilder(); GEDAFileTemplate KOSDAQ = new GEDAFileTemplate(); kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Symbol); kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Display_Name); kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Ric); kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Official_Code); kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Ex_Symbol); kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Background_Page); kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Display_Nmll); kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Bcast_Ref); kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Instmod_ISIN); kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Instmod_Mnemonic); kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Instmod_Tdn_Issuer_Name); kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Instmod_Tdn_Symbol); kqBuilder.AppendFormat("{0}\t", KOSDAQ.Geda_Exl_Name); kqBuilder.Append(KOSDAQ.Geda_BCU); kqBuilder.AppendLine(); foreach (var item in announcementList) { kqBuilder.AppendFormat("{0}\t", item.RIC); kqBuilder.AppendFormat("{0}\t", item.IDNDisplayName); kqBuilder.AppendFormat("{0}\t", item.RIC); kqBuilder.AppendFormat("{0}\t", item.Ticker); if (string.IsNullOrEmpty(item.ISIN)) { kqBuilder.AppendFormat("==NOISIN==\t"); } else { kqBuilder.AppendFormat("{0}\t", item.ISIN); } kqBuilder.AppendFormat("{0}\t", "****"); kqBuilder.AppendFormat("{0}\t", item.KoreaName); kqBuilder.AppendFormat("{0}\t", item.RIC); kqBuilder.AppendFormat("{0}\t", item.ISIN); kqBuilder.AppendFormat("{0}\t", "A" + item.Ticker); kqBuilder.AppendFormat("{0}\t", item.LegalName); kqBuilder.AppendFormat("{0}\t", item.Ticker); if (item.Type == null) { kqBuilder.Append("==NOTYPE=="); } else if (item.Type.Equals("KDR")) { if (item.RIC.Contains(".KQ")) { kqBuilder.Append("KOSDAQ_EQB_KDR"); } else if (item.RIC.Contains(".KS")) { kqBuilder.Append("KSE_EQB_KDR"); } } else { if (item.RIC.Contains(".KQ")) { kqBuilder.Append("KOSDAQ_EQB_2"); } else if (item.RIC.Contains(".KS")) { kqBuilder.Append("KSE_EQB_3"); } // BCU if (item.Type.Equals("ORD")) { if (item.RIC.Contains(".KQ")) { kqBuilder.Append("\tKOSDAQ_EQ_IPO"); } else if (item.RIC.Contains(".KS")) { kqBuilder.Append("\tKSE_EQ_IPO"); } } } kqBuilder.AppendLine(); } File.WriteAllText(gedaFile, kqBuilder.ToString(), Encoding.UTF8); TaskResultList.Add(new TaskResultEntry(fileName, "GEDA File", gedaFile, FileProcessType.GEDA_BULK_RIC_CREATION)); } }
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; TaskResultList.Add(new TaskResultEntry(filename, "FM File", fmFile, mail)); } catch (Exception ex) { Logger.Log("Error in GenerateFM(): " + ex, Logger.LogType.Error); } finally { excelApp.Dispose(); } }
/// <summary> /// 发送邮件功能。 /// </summary> public void StartSendMail() { string inscrubed = ""; inscrubed = inscrubed + "\r\n\r\n\r\n"; //inscrubed = inscrubed + configObj.ALERT_MAIL_INSCRIBED_PERSON + "\r\n"; //inscrubed = inscrubed + configObj.ALERT_MAIL_INSCRIBED_PSITION + "\r\n"; //inscrubed = inscrubed + "\r\nThomson Reuters\r\n\r\nPhone: " + configObj.ALERT_MAIL_INSCRIBED_PHONE + "\r\n"; //inscrubed = inscrubed + configObj.ALERT_MAIL_INSCRIBED_EMAIL_ADRESS + "\r\nthomsonreuters.com\r\n"; for (int i = 0; i < configObj.ALERT_MAIL_SIGNATURE_INFORMATION_LIST.Count; i++) { inscrubed = inscrubed + configObj.ALERT_MAIL_SIGNATURE_INFORMATION_LIST[i] + "\r\n"; } MailToSend mail = new MailToSend(); string sendFileDate = DateTime.Today.ToString("yyyy-MM-dd", new CultureInfo("en-US")); string today = DateTime.Today.ToString("dd-MMM-yyyy", new CultureInfo("en-US")); string mailSubject = "Daily files from East Asia " + today + " (South Korea)"; if (!string.IsNullOrEmpty(configObj.SEND_FILE_DATE)) { sendFileDate = configObj.SEND_FILE_DATE; } //string emaFileDir = configObj.EMA_FILE_BASE_DIR + "\\" + sendFileDate; // string emaFileDir = ConfigureOperator.getEmaFileSaveDir()+"\\" + sendFileDate; string emaFileDir = Path.Combine(ConfigureOperator.GetEmaFileSaveDir(), sendFileDate); List <string> fileList = GetAllTheEMAFile(emaFileDir); if (fileList == null) { return; } mail.ToReceiverList.AddRange(configObj.ALERT_MAIL_TO_LIST); mail.MailSubject = mailSubject; mail.CCReceiverList.AddRange(configObj.ALERT_MAIL_CC_LIST); string strADD = "\nFile for ADD.\n"; string strXLSX = "\nThe excel file is for your checking on the mature date (compare with the date in the csv file dd/mm/yy).\n"; string strISIN = "\nFiles for Change such as ISIN, Issue amount, issue price and strike price change and so on.\n"; int countADDFile = 0; int countXLSXFile = 0; int countISINFile = 0; foreach (string file in fileList) { string fileName = Path.GetFileName(file); string fileExtension = Path.GetExtension(file); mail.AttachFileList.Add(file); if (fileExtension == ".xls" || fileExtension == ".xlsx") { countXLSXFile++; strXLSX = strXLSX + " " + fileName + "\n"; } else { if (fileName.Split('.').Length > 1 && fileName.Split('_')[1] == "ADD") { countADDFile++; strADD = strADD + " " + fileName + "\n"; } else { countISINFile++; strISIN = strISIN + " " + fileName + "\n"; } } } if (countADDFile == 0) { strADD = strADD + "No such files today ! \n"; } if (countISINFile == 0) { strISIN = strISIN + "No such files today ! \n"; } if (countXLSXFile == 0) { strXLSX = strXLSX + "No such files today ! \n"; } mail.MailBody = "Hi, colleagues,\n Please find the files for South Korea today." + "\n" + strADD + strXLSX + strISIN + "\nPlease let me know if you have any concerns.\n\n" + inscrubed; //string err = "Send EMA mail error !"; //using (OutlookApp app = new OutlookApp()) //{ // OutlookUtil.CreateAndSendMail(app, mail, out err); //} // AddResult(sendFileDate,emaFileDir,"EMA File Folder Path"); TaskResultList.Add(new TaskResultEntry("EMAFile", "EMA File Today", emaFileDir, mail)); }
/// <summary> /// 发送邮件功能。 /// </summary> public void StartSendMail() { string inscrubed = ""; inscrubed = inscrubed + "\r\n\r\n\r\n"; inscrubed = configObj.AlertMailSignatureInformationList.Aggregate(inscrubed, (current, t) => current + t + "\r\n"); MailToSend mail = new MailToSend(); string sendFileDate = DateTime.Today.ToString("yyyy-MM-dd", new CultureInfo("en-US")); string today = DateTime.Today.ToString("dd-MMM-yyyy", new CultureInfo("en-US")); string mailSubject = "Daily files from East Asia " + today + " (South Korea)"; if (!string.IsNullOrEmpty(configObj.SendFileDate)) { sendFileDate = configObj.SendFileDate; } string emaFileDir = Path.Combine(ConfigureOperator.GetEmaFileSaveDir(), sendFileDate); List <string> fileList = GetAllTheEMAFile(emaFileDir); if (fileList == null) { return; } mail.ToReceiverList.AddRange(configObj.AlertMailToList); mail.MailSubject = mailSubject; mail.CCReceiverList.AddRange(configObj.AlertMailCCList); string strADD = "\nFile for ADD.\n"; string strXLSX = "\nThe excel file is for your checking on the mature date (compare with the date in the csv file dd/mm/yy).\n"; string strISIN = "\nFiles for Change such as ISIN, Issue amount, issue price and strike price change and so on.\n"; int countADDFile = 0; int countXLSXFile = 0; int countISINFile = 0; foreach (string file in fileList) { string fileName = Path.GetFileName(file); string fileExtension = Path.GetExtension(file); mail.AttachFileList.Add(file); if (fileExtension == ".xls" || fileExtension == ".xlsx") { countXLSXFile++; strXLSX = strXLSX + " " + fileName + "\n"; } else { if (fileName.Split('.').Length > 1 && fileName.Split('_')[1] == "ADD") { countADDFile++; strADD = strADD + " " + fileName + "\n"; } else { countISINFile++; strISIN = strISIN + " " + fileName + "\n"; } } } if (countADDFile == 0) { strADD = strADD + "No such files today ! \n"; } if (countISINFile == 0) { strISIN = strISIN + "No such files today ! \n"; } if (countXLSXFile == 0) { strXLSX = strXLSX + "No such files today ! \n"; } mail.MailBody = "PEO:\t" + "Hi, colleagues,\n Please find the files for South Korea today." + "\n" + strADD + strXLSX + strISIN + "\nPlease let me know if you have any concerns.\n\n" + inscrubed; TaskResultList.Add(new TaskResultEntry("EMAFile", "EMA File Today", emaFileDir, mail)); }