public void StartOptionRicGeneratorJob() { List <StockOption> stockOptionList = new List <StockOption>(); stockOptionList = GetStockOptionList(); using (ExcelApp app = new ExcelApp(false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, Path.Combine(configObj.RIC_GENERATE_FILE_DIR, NewFileName())); var worksheet = workbook.Worksheets[1] as Worksheet; using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right)) { foreach (StockOption stockOption in stockOptionList) { for (int i = 0; i < stockOption.PutRicList.Count; i++) { writer.WriteLine(stockOption.CallRicList[i]); writer.WriteLine("D" + stockOption.CallRicList[i]); writer.WriteLine("/" + stockOption.CallRicList[i]); writer.PlaceNext(writer.Row + 1, 1); writer.WriteLine(stockOption.PutRicList[i]); writer.WriteLine("D" + stockOption.PutRicList[i]); writer.WriteLine("/" + stockOption.PutRicList[i]); writer.PlaceNext(writer.Row + 1, 1); } } } workbook.Save(); } }
public void GenerateISINFile(List <RicISINInfo> ricInfoList) { using (ExcelApp app = new ExcelApp(false, false)) { string targetFilePath = NewTargetFilePath(Path.Combine(Path.GetDirectoryName(configObj.SourceFilePath), configObj.TargetIsinFileName)); if (File.Exists(targetFilePath)) { File.Delete(targetFilePath); } var workbook = ExcelUtil.CreateOrOpenExcelFile(app, targetFilePath); var worksheet = (Worksheet)workbook.Worksheets[1]; using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right)) { writer.WriteLine("Ric"); writer.WriteLine("Official Code"); writer.WriteLine("ISIN"); writer.WriteLine("Name"); writer.WriteLine("Type"); writer.PlaceNext(writer.Row + 1, 1); foreach (RicISINInfo ricISINInfo in ricInfoList) { writer.WriteLine(ricISINInfo.ric); ExcelUtil.GetRange(writer.Row, writer.Col, worksheet).NumberFormat = "@"; writer.WriteLine(ricISINInfo.OfficialCode); writer.WriteLine(ricISINInfo.ISIN); writer.WriteLine(ricISINInfo.Name); writer.WriteLine(ricISINInfo.type); writer.PlaceNext(writer.Row + 1, 1); } } workbook.Save(); TaskResultList.Add(new TaskResultEntry("Newly Generated ISIN File", "The file contains all the newly generated ISIN.", targetFilePath)); } }
/// <summary> /// Write a sector for Security Option transaction /// </summary> /// <param name="worksheet"></param> /// <param name="startRow"></param> /// <param name="oseTransSector"></param> public static void WriteTransCompanyInfosForSecurityOption(Worksheet worksheet, int startRow, int curCol, SecurityOptionSector securityOptionTransSector) { int currentRow = startRow; if (securityOptionTransSector.SellCompanyInfoList.Count == 0) { worksheet.Cells[currentRow, curCol + 1] = "UNQ"; worksheet.Cells[currentRow, curCol] = "出来ず"; } if (securityOptionTransSector.BuyCompanyInfoList.Count == 0) { worksheet.Cells[currentRow, curCol + 4] = "UNQ"; worksheet.Cells[currentRow, curCol + 3] = "出来ず"; } using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, currentRow, curCol, ExcelLineWriter.Direction.Right)) { for (int i = 0; i < securityOptionTransSector.SellCompanyInfoList.Count; i++) { OSETransSingleCompanyInfo singleCompanyInfo = securityOptionTransSector.SellCompanyInfoList[i]; writeCompanyInfoForSecurityOption(worksheet, writer, currentRow, singleCompanyInfo); writer.PlaceNext(startRow + i + 1, curCol); } writer.PlaceNext(startRow, curCol + 3); for (int i = 0; i < securityOptionTransSector.BuyCompanyInfoList.Count; i++) { OSETransSingleCompanyInfo singleCompanyInfo = securityOptionTransSector.BuyCompanyInfoList[i]; writeCompanyInfoForSecurityOption(worksheet, writer, startRow, singleCompanyInfo); writer.PlaceNext(startRow + i + 1, curCol + 3); } } }
//Update page Id from HKBKC to HKBK for English version broker3h file. public void UpdatePageId(Worksheet worksheet) { int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 4, 5, ExcelLineWriter.Direction.Down)) { while (writer.Row <= lastUsedRow) { Range r = ExcelUtil.GetRange(writer.Row, 5, worksheet); while (r.Text != null && r.Text.ToString().Contains("HKBKC")) { writer.WriteLine(r.Text.ToString().Replace("HKBKC", "HKBK")); writer.PlaceNext(writer.Row + 15, 5); writer.WriteLine(ExcelUtil.GetRange(writer.Row, 5, worksheet).Text.ToString().Replace("HKBKC", "HKBK")); } break; } for (int i = 0; i < 3; i++) { writer.PlaceNext(2, 9 + i * 3); while (ExcelUtil.GetRange(writer.Row, 7, worksheet).Text != null && !ExcelUtil.GetRange(writer.Row, 7, worksheet).Text.ToString().Contains("REFER")) { Range referRange = ExcelUtil.GetRange(writer.Row, writer.Col, worksheet); if (referRange.Text != null) { writer.WriteLine(referRange.Text.ToString().Replace("HKBKC", "HKBK")); } } } } }
public void InsertNameChange(int rowPos, ExcelLineWriter writer, List <BrokerChangeInfo> nameChangeList, Worksheet worksheet) { if (nameChangeList == null || nameChangeList.Count == 0) { return; } else { ExcelUtil.InsertBlankRows(ExcelUtil.GetRange(rowPos, 1, worksheet), 2 * nameChangeList.Count); int lastUsedCol = worksheet.UsedRange.Column + worksheet.UsedRange.Columns.Count - 1; foreach (BrokerChangeInfo nameChange in nameChangeList) { writer.PlaceNext(rowPos++, 2); writer.WriteLine(nameChange.Ric); writer.WriteLine(nameChange.Type); writer.WriteLine(nameChange.Date); writer.PlaceNext(rowPos++, 1); writer.WriteLine(nameChange.Name); writer.PlaceNextAndWriteLine(rowPos, 4, nameChange.Date); if (IsIPos(nameChange.Ric)) { ExcelUtil.GetRange(rowPos - 2, lastUsedCol, rowPos - 1, lastUsedCol, worksheet).Interior.Color = 10092543.0; } else { ExcelUtil.GetRange(rowPos - 2, lastUsedCol, rowPos - 1, lastUsedCol, worksheet).Interior.Color = 16777164.0; } } } }
/// <summary> /// /// </summary> /// <param name="results"></param> private void WriteResultsInCsv(IEnumerable <string> results) { using (ExcelApp app = new ExcelApp(false, false)) { Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.WorkingFolder + "result.csv"); try { Worksheet worksheet = workbook.Worksheets[1] as Worksheet; ExcelLineWriter lineWriter = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right); lineWriter.PlaceNext(1, 1); lineWriter.WriteLine("RIC"); lineWriter.WriteLine("RETIRE DATE"); int line = 2; foreach (string entry in results) { lineWriter.PlaceNext(line, 1); lineWriter.WriteLine(entry); lineWriter.WriteLine(DateTime.Now.ToString("dd-MMM-yyyy")); line++; } } catch (Exception ex) { LogMessage("Error while creating CSV", Logger.LogType.Error); throw new Exception("Error when creating result CSV: " + ex.Message); } finally { AddResult("TW IO Drop results", configObj.WorkingFolder + "result.csv", "file"); workbook.SaveAs(configObj.WorkingFolder + "result.csv", XlFileFormat.xlCSV); workbook.Close(); } } }
private void ReadFile(List <WrtQuaNotHK> list, string path) { using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, path); Worksheet worksheet = workbook.Worksheets[1] as Worksheet; int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 5, 7, ExcelLineWriter.Direction.Right)) { string code = string.Empty; string listingDate = string.Empty; string issuePrice = string.Empty; string furtherIssueSize = string.Empty; for (int i = 1; i <= lastUsedRow; i++) { listingDate = reader.ReadLineCellText(); if (listingDate != null && listingDate.Trim() != "") { if (NextWorkDay().ToString("M/d/yyyy").Equals(listingDate.Trim())) { reader.PlaceNext(reader.Row, 2); code = reader.ReadLineCellText(); reader.PlaceNext(reader.Row, 8); issuePrice = reader.ReadLineCellText(); reader.PlaceNext(reader.Row, 9); furtherIssueSize = reader.ReadLineCellText().Replace(",", ""); if (code == null || issuePrice == null || furtherIssueSize == null) { string msg = String.Format("value (row,clo)=({0},{1}) is null!", reader.Row, reader.Col); Logger.Log(msg, Logger.LogType.Error); MessageBox.Show(msg); continue; } WrtQuaNotHK wrt = new WrtQuaNotHK(); wrt.LogicalKey = list.Count + 1; wrt.SecondaryID = code.Trim(); wrt.ISIN = GetISIN(wrt.SecondaryID); wrt.SecondaryIDType = "HONG KONG CODE"; wrt.EHIssueQuantity = "N"; wrt.IssueQuantity = GetIssueQuantity(wrt.SecondaryID, furtherIssueSize); wrt.Action = "I"; wrt.Note1Type = "O"; wrt.TranchePrice = issuePrice.Replace("HKD", "").Trim(); wrt.Note1 = string.Format("Further issue with issue price HKD {0} on {1}.", issuePrice.Replace("HKD", "").Trim(), NextWorkDay().ToString("dd-MMM-yyyy")); wrt.TrancheListingDate = DateTime.Parse(listingDate).ToString("dd-MMM-yyyy"); list.Add(wrt); } } reader.PlaceNext(reader.Row + 1, 7); } } } }
private List <FMELWEntity> ReadFile(string filePath, List <FMELWEntity> listFMELW) { List <FMELWEntity> list = new List <FMELWEntity>(); if (!File.Exists(filePath)) { string msg = string.Format("the file [{0}] is not exist.", filePath); Logger.Log(msg, Logger.LogType.Error); return(null); } try { using (ExcelApp eApp = new ExcelApp(false, false)) { Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(eApp, 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!"; Logger.Log(msg, Logger.LogType.Error); return(null); } //wSheet.Name = "FM"; int lastUsedRow = wSheet.UsedRange.Row + wSheet.UsedRange.Rows.Count - 1;//193=1+193-1 using (ExcelLineWriter reader = new ExcelLineWriter(wSheet, 2, 2, ExcelLineWriter.Direction.Right)) { while (reader.Row <= lastUsedRow) { FMELWEntity fm = new FMELWEntity(); fm.ISIN = reader.ReadLineCellText();//web.StandardCongenial reader.PlaceNext(reader.Row, 4); fm.IssuingAuthority = reader.ReadLineValue2(); reader.PlaceNext(reader.Row + 1, 2); if (!string.IsNullOrEmpty(fm.ISIN.Trim()) && !string.IsNullOrEmpty(fm.IssuingAuthority.Trim())) { list.Add(fm); } } } } return(list); } catch (Exception ex) { string msg = string.Format("read xls file error :{0}", ex.ToString()); Logger.Log(msg); return(null); } }
private void FillInListTemplate(List <IssueAssetAddTemplate> listIAATemplate) { using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, hkQAAddFileName); Worksheet worksheet = workbook.Worksheets[1] as Worksheet; int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 2, 1, ExcelLineWriter.Direction.Right)) { string ric = string.Empty; string warrantIssueQuantity = string.Empty; //18 string tranche = string.Empty; //17 string trancheListingAate = string.Empty; //16 for (int i = 1; i <= lastUsedRow; i++) { ric = reader.ReadLineCellText(); if (ric == null || ric.Trim() == "") { continue; } foreach (var item in listIAATemplate) { if (!(item.HongKongCode.Trim() + ".HK").Equals(ric.Trim())) { continue; } reader.PlaceNext(reader.Row, 16); trancheListingAate = DateTime.Parse(reader.ReadLineCellText()).ToString("dd-MMM-yyyy"); reader.PlaceNext(reader.Row, 17); tranche = reader.ReadLineCellText(); reader.PlaceNext(reader.Row, 18); warrantIssueQuantity = reader.ReadLineValue2(); if (trancheListingAate == null || tranche == null || warrantIssueQuantity == null) { string msg = String.Format("value (row,clo)=({0},{1}) is null!", reader.Row, reader.Col); Logger.Log(msg, Logger.LogType.Error); MessageBox.Show(msg); continue; } item.TrancheListingDate = trancheListingAate; item.TranchePrice = tranche; item.WarrantIssueQuantity = warrantIssueQuantity; } reader.PlaceNext(reader.Row + 1, 1); } } } }
private void GetHongKongCodeToList(List <IssueAssetAddTemplate> listIAATemplate) { using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, hkIAAddFileName); Worksheet worksheet = workbook.Worksheets[1] as Worksheet; int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 2, 1, ExcelLineWriter.Direction.Right)) { string ric = string.Empty; string type = string.Empty; string category = string.Empty; string rcsAssrtClass = string.Empty; string warrantIssue = string.Empty; for (int i = 1; i <= lastUsedRow; i++) { ric = reader.ReadLineCellText(); reader.PlaceNext(reader.Row, 2); type = reader.ReadLineCellText(); reader.PlaceNext(reader.Row, 3); category = reader.ReadLineCellText(); reader.PlaceNext(reader.Row, 4); rcsAssrtClass = reader.ReadLineCellText(); reader.PlaceNext(reader.Row, 5); warrantIssue = reader.ReadLineCellText(); if (ric == null || type == null || category == null || rcsAssrtClass == null || warrantIssue == null) { string msg = String.Format("value (row,clo)=({0},{1}) is null!", reader.Row, reader.Col); Logger.Log(msg, Logger.LogType.Error); MessageBox.Show(msg); continue; } //if (string.IsNullOrWhiteSpace(ric)) // continue; IssueAssetAddTemplate iaat = new IssueAssetAddTemplate(); iaat.HongKongCode = ric; iaat.Type = type; iaat.Category = category; iaat.RcsAssetClass = rcsAssrtClass; iaat.WarrantIssuer = warrantIssue; listIAATemplate.Add(iaat); reader.PlaceNext(reader.Row + 1, 1); } } } }
public static void writeCompanyInfoForSecurityOption(Worksheet worksheet, ExcelLineWriter writer, int startRow, OSETransSingleCompanyInfo singleCompanyInfo) { if (singleCompanyInfo != null && singleCompanyInfo.CompanyInfo != null) { if (!string.IsNullOrEmpty(singleCompanyInfo.CompanyInfo.JapaneseName)) { writer.WriteLine(singleCompanyInfo.CompanyInfo.JapaneseName); ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignLeft; } else { writer.WriteLine(singleCompanyInfo.CompanyInfo.OriginalName); ExcelUtil.GetRange(writer.Row, writer.Col - 1, writer.Row, writer.Col, worksheet).Font.Color = ColorTranslator.ToOle(Color.Red); ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignLeft; } if (!string.IsNullOrEmpty(singleCompanyInfo.CompanyInfo.EnglishName)) { writer.WriteLine(singleCompanyInfo.CompanyInfo.EnglishName); ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignLeft; } else { writer.WriteLine(singleCompanyInfo.CompanyInfo.OriginalName); ExcelUtil.GetRange(writer.Row, writer.Col - 1, writer.Row, writer.Col, worksheet).Font.Color = ColorTranslator.ToOle(Color.Red); ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignLeft; } writer.WriteLine(singleCompanyInfo.Volume_OP_INT.Replace(",", "")); ExcelUtil.GetRange(writer.Row, writer.Col - 1, worksheet).HorizontalAlignment = XlHAlign.xlHAlignRight; writer.PlaceNext(writer.Row + 1, writer.Col); } }
//Update BN underlying column: (Removing the first number redundant "0", 00005.HK --> 0005.HK, 00038.HK --> 0038.HK, and changes "HSC" to "HSCE" //There's a bug in the macro, this method is a work around public void UpdateBNColumn(Worksheet worksheet) { int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 2, 66, ExcelLineWriter.Direction.Down)) { while (reader.Row <= lastUsedRow) { var cellValue = ExcelUtil.GetRange(reader.Row, reader.Col, worksheet).Text; if (cellValue != null) { string text = cellValue.ToString().Trim().ToUpper(); if (text.Contains(".HSC")) { reader.WriteInPlace(text.Replace(".HSC", ".HSCE")); } //00005.HK else if (text.Contains(".HK")) { string underlyingCode = text.Substring(0, text.IndexOf('.')); if (underlyingCode.Length == 5 && underlyingCode.StartsWith("0")) { reader.WriteInPlace(text.Remove(0, 1)); } } } reader.PlaceNext(reader.Row + 1, reader.Col); } } }
public void UpdateSheet5(Worksheet worksheet5, List <BordLotSizeChangeInfo> bordLotSizeChangeList) { using (ExcelLineWriter writer = new ExcelLineWriter(worksheet5, 2, 2, ExcelLineWriter.Direction.Down)) { while (true) { Range nameCell = ExcelUtil.GetRange(writer.Row, 1, worksheet5); if (nameCell == null || nameCell.Text.ToString().Trim() == "0") { break; } else { if (bordLotSizeChangeList != null && bordLotSizeChangeList.Count != 0) { if (ExcelUtil.GetRange(writer.Row, 3, worksheet5).Text.ToString().Trim() == "#N/A") { writer.PlaceNextAndWriteLine(writer.Row, 3, "CHANGE"); writer.PlaceNext(writer.Row - 1, writer.Col); } } Range r = ExcelUtil.GetRange(writer.Row, 2, worksheet5); string line = r.Value2.ToString().Trim(new char[] { '>', '<' }); writer.PlaceNextAndWriteLine(writer.Row, 2, line); } } } }
private void WriteResultsInCsv() { Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.ResultFileName); try { Worksheet worksheet = workbook.Worksheets[1] as Worksheet; ExcelLineWriter lineWriter = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down); int col = 1; bool skip = false; foreach (List <string> updateEntry in toUpdate) { List <string> tmp = updateEntry; tmp.Sort(); skip = false; lineWriter.PlaceNext(1, col); string test = tmp[0]; if (ricExl.ContainsKey("0#" + tmp[0])) { lineWriter.WriteLine(ricExl["0#" + tmp[0]]); } foreach (string rowEntry in tmp) { if (rowEntry.StartsWith("1") || rowEntry.StartsWith("5")) { skip = true; } string suffix = ""; if (rowEntry.StartsWith("6")) { suffix = ".SS"; } else if ((rowEntry.StartsWith("0") && !rowEntry.StartsWith("0#")) || rowEntry.StartsWith("2") || rowEntry.StartsWith("3")) { suffix = ".SZ"; } if (!rowEntry.StartsWith("1")) { lineWriter.WriteLine(rowEntry + suffix); } } if (skip == false) { col++; } } } catch (Exception ex) { throw new Exception("Error when creating result CSV: " + ex.Message); } finally { workbook.SaveAs(configObj.ResultFileName, XlFileFormat.xlCSV); workbook.Close(); } }
private void UpdateAddDropFile(string templateFilePath, List <AddDropChangeInfo> addDropEventList) { 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 (AddDropChangeInfo changeInfo in addDropEventList) { writer.WriteLine(changeInfo.EventAction); writer.WriteLine(changeInfo.RicSeqId); writer.WriteLine(changeInfo.ChangeType); writer.WriteLine(changeInfo.Date.ToString("ddMMMyy")); writer.WriteLine(changeInfo.DescriptionWas); writer.WriteLine(changeInfo.DescriptionNow); writer.WriteLine(changeInfo.RicWas); writer.WriteLine(changeInfo.RicNow); writer.WriteLine(changeInfo.ISINWas); writer.WriteLine(changeInfo.ISINNow); writer.WriteLine(changeInfo.SecondID); writer.WriteLine(changeInfo.SecondWas); writer.WriteLine(changeInfo.SecondNow); writer.WriteLine(changeInfo.ThomsonWas); writer.WriteLine(changeInfo.ThomsonNow); writer.WriteLine(""); writer.WriteLine(changeInfo.Exchange); writer.WriteLine(changeInfo.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); AddResult("Result File", targetFilePath, ""); //workbook.Save(); workbook.Close(false, templateFilePath, false); } }
private void updateTemplateV2File() { using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.RIC_CONVS_TEMPLATE_V2_FILE_PATH); Worksheet worksheet = ExcelUtil.GetWorksheet(configObj.WORKSHEET_NAME_TEMPLATE, workbook); if (worksheet == null) { LogMessage(string.Format("There's no worksheet {0} in the file {1}", configObj.WORKSHEET_NAME_TEMPLATE, workbook.Name)); } using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 3, 1, ExcelLineWriter.Direction.Right)) { foreach (string key in addRicDic.Keys) { writer.WriteLine("Revise"); //EventAction writer.WriteLine(key.Remove(key.IndexOf('.'))); //Ric SeqId writer.WriteLine("Add"); //Change Type writer.WriteLine(DateTime.Parse(addRicDic[key].EffectiveDate).ToString("ddMMMyy")); // Date writer.WriteLine(""); //Description Was writer.WriteLine(addRicDic[key].DescriptionNow); //Description Now writer.WriteLine(""); //Ric Was writer.WriteLine(addRicDic[key].RicNow); //RICNow writer.WriteLine(""); //ISINWas writer.WriteLine(addRicDic[key].ISIN); //ISINNow writer.WriteLine("Official Code"); //2ndId writer.WriteLine(""); //2ndWas writer.WriteLine(addRicDic[key].SecondMarketNow); //2ndNow writer.WriteLine(""); //ThomsonWas writer.WriteLine(""); //ThomsonNow writer.WriteLine(""); writer.WriteLine(addRicDic[key].Exchanges); //Exchange writer.WriteLine(addRicDic[key].AssetClass); //Asset writer.PlaceNext(writer.Row + 1, 1); } } //Run Macro worksheet.Activate(); app.ExcelAppInstance.GetType().InvokeMember("Run", BindingFlags.Default | BindingFlags.InvokeMethod, null, app.ExcelAppInstance, new object[] { "FormatData" }); string targetFileName = Path.Combine(Path.GetDirectoryName(configObj.RIC_CONVS_TEMPLATE_V2_FILE_PATH), configObj.DATE); targetFileName += Path.GetFileName(configObj.RIC_CONVS_TEMPLATE_V2_FILE_PATH); workbook.SaveCopyAs(targetFileName); workbook.Close(false, workbook.FullName, false); AddResult("Target file ", targetFileName, "The file has been updated with ISIN"); } }
public void getNameMap() { using (ExcelApp app = new ExcelApp(false, false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, NAME_MAP_FILE_PATH); var worksheet = (Worksheet)workbook.Worksheets[1]; int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right)) { while (reader.Row <= lastUsedRow) { string englishOrigName = reader.ReadLineCellText().ToUpper(); if (!englishNameMap.ContainsKey(englishOrigName)) { englishNameMap.Add(englishOrigName, new HKBrokerPageNameMap()); englishNameMap[englishOrigName].longName = reader.ReadLineCellText().ToUpper(); englishNameMap[englishOrigName].shortName = reader.ReadLineCellText().ToUpper(); } else { reader.PlaceNext(reader.Row, reader.Col + 2); } reader.MoveNext(); string chineseOrigName = reader.ReadLineCellText().ToUpper(); if (!chineseNameMap.ContainsKey(chineseOrigName)) { chineseNameMap.Add(chineseOrigName, new HKBrokerPageNameMap()); chineseNameMap[chineseOrigName].longName = reader.ReadLineCellText().ToUpper(); chineseNameMap[chineseOrigName].shortName = reader.ReadLineCellText().ToUpper(); } reader.PlaceNext(reader.Row + 1, 1); } } workbook.Close(false, workbook.FullName, false); } }
public void InsertRicChange(int rowPos, ExcelLineWriter writer, List <StockNameRicChange> ricChangeList, Worksheet worksheet) { if (ricChangeList == null || ricChangeList.Count == 0) { return; } else { ExcelUtil.InsertBlankRows(ExcelUtil.GetRange(rowPos, 1, worksheet), 2 * ricChangeList.Count); int lastUsedCol = worksheet.UsedRange.Column + worksheet.UsedRange.Columns.Count - 1; foreach (StockNameRicChange ricChange in ricChangeList) { writer.PlaceNext(rowPos++, 1); writer.WriteLine(ricChange.Name); writer.WriteLine(ricChange.OldValue); writer.WriteLine(ricChange.Type); writer.WriteLine(ricChange.Date); writer.PlaceNext(rowPos++, 2); writer.WriteLine(ricChange.NewValue); if (IsUpgrade(ricChange)) { writer.WriteLine("UPGRADE"); } writer.PlaceNextAndWriteLine(rowPos, 4, ricChange.Date); if (IsIPos(ricChange.Ric)) { ExcelUtil.GetRange(rowPos - 2, lastUsedCol, rowPos - 1, lastUsedCol, worksheet).Interior.Color = 10092543.0; } else { ExcelUtil.GetRange(rowPos - 2, lastUsedCol, rowPos - 1, lastUsedCol, worksheet).Interior.Color = 16777164.0; } } } }
private void generateTXTFile(Workbook warGuideBWorkbook) { Worksheet insertV3Sheet = ExcelUtil.GetWorksheet(configObj.WorksheetWarrantGuideBInsertV3, warGuideBWorkbook); if (insertV3Sheet == null) { LogMessage(string.Format("There's no worksheet {0} in workbook {1}", configObj.WorksheetWarrantGuideBInsertV3, warGuideBWorkbook.FullName)); } int lastUsedRow = insertV3Sheet.UsedRange.Row + insertV3Sheet.UsedRange.Rows.Count - 1; var sb = new StringBuilder(); sb.AppendLine("HKSE;29-JUN-29;7:1;TPS;"); sb.AppendLine("ROW80_5;ROW80_6;ROW80_7;ROW80_8;ROW80_9;ROW80_10;ROW80_11;ROW80_12;ROW80_13;ROW80_14;ROW80_15;ROW80_16;ROW80_17;ROW80_18;ROW80_19;ROW80_20;ROW80_21;ROW80_22;ROW80_23;ROW80_24;ROW80_25;"); sb.Append("HK/WTS06;"); using (var reader = new ExcelLineWriter(insertV3Sheet, 86, 1, ExcelLineWriter.Direction.Right)) { string line = string.Empty; int pageNum = -1; while (reader.Row <= lastUsedRow) { line += reader.ReadLineCellText().PadRight(9); line += reader.ReadLineCellText().PadRight(16); line += reader.ReadLineCellText().PadLeft(9); line += reader.ReadLineCellText().PadLeft(8); line += reader.ReadLineCellText().PadRight(10); line += reader.ReadLineCellText().PadLeft(9); line += reader.ReadLineCellText().PadLeft(8); line += ";"; sb.Append(line); line = string.Empty; if (reader.Row % 20 == 5) { pageNum = reader.Row / 20 + 1; sb.AppendLine(string.Format(" PREV <HK/WTS{0}> / NEXT <HK/WTS{1}>;", (pageNum - 1).ToString("D2"), (pageNum + 1).ToString("D2"))); line = string.Format("HK/WTS{0};", (pageNum + 1).ToString("D2")); } reader.PlaceNext(reader.Row + 1, 1); } } using (var sw = new StreamWriter(Path.Combine(configObj.WarrantGuideBTxtFileDir, "war_guideB.txt"), false, Encoding.UTF8)) { sw.WriteLine(sb.ToString()); sw.AutoFlush = true; sw.Close(); AddResult("war_guideB.txt", Path.Combine(configObj.WarrantGuideBTxtFileDir, "war_guideB.txt"), "file"); } }
public void GenerateSCBFile(SCBChangeInfo changeInfo) { using (ExcelApp app = new ExcelApp(false)) { var workbook = ExcelUtil.CreateOrOpenExcelFile(app, configObj.GENERATED_SCB_FILE_PATH); var worksheet = workbook.Worksheets[1] as Worksheet; int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1; int lastUsedCol = worksheet.UsedRange.Column + worksheet.UsedRange.Columns.Count - 1; ExcelUtil.GetRange(1, 1, lastUsedRow, lastUsedCol, worksheet).ClearContents(); using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right)) { writer.WriteLine("EventType"); writer.WriteLine("Function"); writer.WriteLine("FILE_CODE"); writer.WriteLine("ISIN"); writer.WriteLine("SEDOL"); writer.WriteLine("OLD TICKER"); writer.WriteLine("NEW TICKER"); writer.WriteLine("Old RIC"); writer.WriteLine("New RIC"); writer.WriteLine("MIC"); writer.WriteLine("EXCHANGE_CODE"); writer.WriteLine("EXCHANGE_NAME"); writer.WriteLine("OLD COMPANY_NAME"); writer.WriteLine("NEW COMPANY_NAME"); writer.WriteLine("ANNOUNCEMENT DATE"); writer.WriteLine("EFFECTIVE DATE"); writer.PlaceNext(2, 1); writer.WriteLine(changeInfo.EventType); writer.WriteLine(changeInfo.Function); writer.WriteLine(changeInfo.FileCode); writer.WriteLine(changeInfo.Isin); writer.WriteLine(changeInfo.Sedol); writer.WriteLine(changeInfo.OldTicker); writer.WriteLine(changeInfo.NewTicker); writer.WriteLine(changeInfo.OldRic); writer.WriteLine(changeInfo.NewRic); writer.WriteLine(changeInfo.Mic); writer.WriteLine(changeInfo.ExchangeCode); writer.WriteLine(changeInfo.ExchangeName); writer.WriteLine(changeInfo.OldCompName); writer.WriteLine(changeInfo.NewCompName); writer.WriteLine(changeInfo.AnnouncementDate); writer.WriteLine(changeInfo.EffectiveDate); } workbook.SaveCopyAs(GetSCBGenerateFilePath(configObj.GENERATED_SCB_FILE_PATH)); workbook.Close(false, workbook.FullName, false); } }
private void WriterWorksheet(Worksheet worksheet, List <ChinaIndex> indexList, int startPos, int endPos) { using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down)) { for (int i = startPos; i < endPos; i++) { writer.WriteLine(indexList[i].Chain); writer.WriteLine(indexList[i].ChineseName); foreach (string ric in indexList[i].RicList) { writer.WriteLine(ric); } writer.PlaceNext(1, writer.Col + 1); } } }
public void WriterWorksheet(Worksheet worksheet, Dictionary <string, List <string> > indicesRicDic, int startPos, int endPos) { using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down)) { for (int i = startPos; i < endPos; i++) { string key = indicesRicDic.Keys.ToList()[i]; writer.WriteLine("0#" + key); writer.WriteLine(key); foreach (string ric in indicesRicDic[key]) { writer.WriteLine(ric); } writer.PlaceNext(1, writer.Col + 1); } } }
public void InsertNewDeleteChangeInfo(int rowPos, ExcelLineWriter writer, List <BrokerChangeInfo> brokerChangeList, Worksheet worksheet) { ExcelUtil.InsertBlankRows(ExcelUtil.GetRange(rowPos, 1, worksheet), brokerChangeList.Count); if (brokerChangeList.Count == 0) { return; } int lastUsedCol = worksheet.UsedRange.Column + worksheet.UsedRange.Columns.Count - 1; foreach (BrokerChangeInfo broker in brokerChangeList) { writer.PlaceNext(rowPos++, 1); writer.WriteLine(broker.Name); writer.WriteLine(broker.Ric); writer.WriteLine(broker.Type); writer.WriteLine(broker.Date); if (IsIPos(broker.Ric)) { //Mark as yellow ExcelUtil.GetRange(rowPos - 1, 1, rowPos - 1, lastUsedCol, worksheet).Interior.Color = 10092543.0; } else { ExcelUtil.GetRange(rowPos - 1, 1, rowPos - 1, lastUsedCol, worksheet).Interior.Color = 16777215.0; } } ExcelUtil.GetRange(rowPos - brokerChangeList.Count, 4, rowPos - 1, 4, worksheet).NumberFormat = "MM/dd/yyyy"; if (brokerChangeList[0].Type == "delete") { if (ParseDateTime(brokerChangeList[0].Date) == GetNextBusinessDay(DateTime.Now, configObj.HOLIDAY_COUNT)) { //Mark as red ExcelUtil.GetRange(rowPos - brokerChangeList.Count, 1, rowPos - 1, lastUsedCol, worksheet).Font.Color = 255.0; } else { ExcelUtil.GetRange(rowPos - brokerChangeList.Count, 1, rowPos - 1, lastUsedCol, worksheet).Font.Color = 0.0; } } else { ExcelUtil.GetRange(rowPos - brokerChangeList.Count, 1, rowPos - 1, lastUsedCol, worksheet).Font.Color = 0.0; } }
private void WriteTransactionInfo(IEnumerable <Transaction> transactionList, Worksheet worksheet, int currentRow, int currentCol) { for (int i = currentRow; i < currentRow + 15; i++) { worksheet.Cells[i, 1] = (i - currentRow + 1).ToString(); } using (ExcelLineWriter writer = new ExcelLineWriter(worksheet, currentRow, currentCol + 1, ExcelLineWriter.Direction.Right)) { foreach (Transaction transaction in transactionList) { writer.WriteLine(transaction.CompanyInfo.EnglishName); writer.WriteLine(transaction.CompanyInfo.JapaneseName); writer.WriteLine(transaction.SellSum); writer.WriteLine(transaction.BuySum); writer.WriteLine(transaction.TotalSum); writer.PlaceNext(writer.Row + 1, currentCol + 1); } } }
public void InsertBordSizeChangeInfo(int rowPos, ExcelLineWriter writer, List <BrokerChangeInfo> brokerChangeList, List <BordLotSizeChangeInfo> bordLotSizeChangeInfoList, Worksheet worksheet) { ExcelUtil.InsertBlankRows(ExcelUtil.GetRange(rowPos, 1, worksheet), brokerChangeList.Count); if (brokerChangeList.Count == 0) { return; } int lastUsedCol = worksheet.UsedRange.Column + worksheet.UsedRange.Columns.Count - 1; foreach (BrokerChangeInfo broker in brokerChangeList) { string type = string.Empty; writer.PlaceNext(rowPos++, 1); writer.WriteLine(broker.Name); writer.WriteLine(broker.Ric); foreach (BordLotSizeChangeInfo bordLotSizeChangeInfo in bordLotSizeChangeInfoList) { if (bordLotSizeChangeInfo.Name == broker.Name) { type = string.Format("Bord Lot Size Change to {0}", bordLotSizeChangeInfo.BoardLotSize); break; } else { continue; } } writer.WriteLine(type); writer.WriteLine(broker.Date); if (IsIPos(broker.Ric)) { ExcelUtil.GetRange(rowPos - 1, 1, rowPos - 1, lastUsedCol, worksheet).Interior.Color = 10092543.0; } else { ExcelUtil.GetRange(rowPos - 1, 1, rowPos - 1, lastUsedCol, worksheet).Interior.Color = 16777215.0; } } }
/// <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 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); AddResult("Result File", targetFilePath, ""); workbook.Close(false, templateFilePath, false); } }
/// <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); } }
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); } }
public TradingInfo GetTradingInfoFromMain01_10File(int pageNum, int lineNumEachPage) { TradingInfo tradingInfo = new TradingInfo(); tradingInfo.StockList = new List <StockInfo>(); using (ExcelApp excelApp = new ExcelApp(false, false)) { Workbook orOpenExcelFile = ExcelUtil.CreateOrOpenExcelFile(excelApp, configObj.HKMAIN01_10_Workbook); Worksheet worksheet = ExcelUtil.GetWorksheet(this.configObj.HKMAIN01_10_Worksheet, orOpenExcelFile); if (worksheet == null) { LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", (object)configObj.HKMAIN01_10_Worksheet, (object)orOpenExcelFile.Name)); } using ( ExcelLineWriter excelLineWriter = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Right)) { tradingInfo.DateStr = ExcelUtil.GetRange(1, 1, worksheet).Text.ToString(); excelLineWriter.PlaceNext(4, 1); while (excelLineWriter.Row < pageNum * lineNumEachPage + 4) { if (ExcelUtil.GetRange(excelLineWriter.Row, 1, worksheet).Text != null) { string str = ExcelUtil.GetRange(excelLineWriter.Row, 1, worksheet).Text.ToString().Trim(); if (str.StartsWith("<")) { tradingInfo.StockList.Add(new StockInfo() { Ric = excelLineWriter.ReadLineCellText(), StockName = excelLineWriter.ReadLineCellText(), Shares = excelLineWriter.ReadLineCellText(), Turnover = excelLineWriter.ReadLineCellText() }); excelLineWriter.PlaceNext(excelLineWriter.Row + 1, 1); } else if (str != string.Empty) { tradingInfo.DesignatedSecuritiesRecordingSum = ExcelUtil.GetRange(excelLineWriter.Row, 1, worksheet).Text.ToString().Trim(); tradingInfo.DesignatedSharesShortSoldSum = ExcelUtil.GetRange(excelLineWriter.Row + 1, 1, worksheet).Text.ToString().Trim(); tradingInfo.DesignatedShortSellTurnoverShares = ExcelUtil.GetRange(excelLineWriter.Row + 2, 1, worksheet).Text.ToString().Trim(); tradingInfo.DesignatedShortSellTurnoverValue = ExcelUtil.GetRange(excelLineWriter.Row + 3, 1, worksheet).Text.ToString().Trim(); if (ExcelUtil.GetRange(excelLineWriter.Row + 4, 1, worksheet).Text != null) { tradingInfo.HKDTurnoverValue = ExcelUtil.GetRange(excelLineWriter.Row + 4, 1, worksheet).Text.ToString().Trim(); } excelLineWriter.PlaceNext(excelLineWriter.Row + 5, 1); } else { excelLineWriter.PlaceNext(excelLineWriter.Row + 1, 1); } } else { excelLineWriter.PlaceNext(excelLineWriter.Row + 1, 1); } } orOpenExcelFile.Close((object)false, (object)configObj.HKMAIN01_10_Workbook, (object)false); } } return(tradingInfo); }