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); } } } }
public void UpdateAddDropSheet(Worksheet worksheetAddDrop, List <BordLotSizeChangeInfo> bordLotSizeChangeList) { using (ExcelLineWriter writer = new ExcelLineWriter(worksheetAddDrop, 2, 1, ExcelLineWriter.Direction.Down)) { while (true) { Range changeTypeRange = ExcelUtil.GetRange(writer.Row, 1, worksheetAddDrop); if (changeTypeRange == null || changeTypeRange.Text.ToString().Trim() == "#N/A") { break; } else { if (changeTypeRange != null && changeTypeRange.Text.ToString().Trim() == "FALSE") { writer.WriteLine("CHANGE"); writer.PlaceNextAndWriteLine(writer.Row, 3, ExcelUtil.GetRange(writer.Row, 4, worksheetAddDrop).Text.ToString()); } else if (changeTypeRange != null && changeTypeRange.Text.ToString().Trim() == "Change") { writer.PlaceNextAndWriteLine(writer.Row, 3, ExcelUtil.GetRange(writer.Row, 4, worksheetAddDrop).Text.ToString()); } else { writer.MoveNext(); } } } } }
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; } } } }
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 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> /// 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); } } }
//Generate insert sheet public void GenerateInsertSheet(Workbook workbook, int brokerRowsSum, List <BrokerInfo> brokerList) { var worksheetInsert = ExcelUtil.GetWorksheet(configObj.INSERT_WORKSHEET_NAME, workbook); if (worksheetInsert == null) { LogMessage(string.Format("Cannot get worksheet {0} from workbook {1}", configObj.INSERT_WORKSHEET_NAME, workbook.Name)); } Range copyRange = GenerateMasterSheet(workbook, brokerList, out brokerRowsSum); using (ExcelLineWriter writerInsert = new ExcelLineWriter(worksheetInsert, 4, 1, ExcelLineWriter.Direction.Right)) { Range r = ExcelUtil.GetRange(4, 1, brokerRowsSum, 4, worksheetInsert); copyRange.Copy(r); int lastUsedRowInsert = worksheetInsert.UsedRange.Row + worksheetInsert.UsedRange.Rows.Count - 1; if (lastUsedRowInsert > (brokerRowsSum)) { ExcelUtil.GetRange(brokerRowsSum + 1, 1, lastUsedRowInsert, 4, worksheetInsert).Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); } ExcelUtil.GetRange(4, 5, lastUsedRowInsert, 5, worksheetInsert).ClearContents(); int pageSum = ((brokerRowsSum - 4)) / 17 + 1; List <PageInfo> pageList = new List <PageInfo>(); for (int i = 0; i < pageSum; i++) { PageInfo pageInfo = new PageInfo(); string id = "HKBKC" + (i + 2).ToString("D2"); int upRow = 17 * i + 4; int downRow = upRow + 16; writerInsert.PlaceNextAndWriteLine(upRow, 5, id); writerInsert.PlaceNextAndWriteLine(downRow, 5, id); pageInfo.pageNumberChineseStr = "<" + id + ">"; pageInfo.pageNumberStr = pageInfo.pageNumberChineseStr.Replace("HKBKC", "HKBK"); pageInfo.minNumberStr = GetMinMaxValue(ExcelUtil.GetRange(upRow, 1, worksheetInsert).Text.ToString() + "," + ExcelUtil.GetRange(upRow, 2, worksheetInsert).Text.ToString())[0]; if (downRow < (brokerRowsSum)) { pageInfo.maxNumberStr = GetMinMaxValue(ExcelUtil.GetRange(downRow, 1, worksheetInsert).Text.ToString() + "," + ExcelUtil.GetRange(downRow, 2, worksheetInsert).Text.ToString())[1]; } else { pageInfo.maxNumberStr = GetMinMaxValue(ExcelUtil.GetRange(brokerRowsSum, 1, worksheetInsert).Text.ToString() + ExcelUtil.GetRange(brokerRowsSum, 2, worksheetInsert).Text.ToString())[1]; } pageList.Add(pageInfo); } writerInsert.PlaceNext(2, 7); int referStartRow = 0; int referLastRow = 0; while ((ExcelUtil.GetRange(writerInsert.Row + 1, 7, worksheetInsert).Text.ToString() != "") && (ExcelUtil.GetRange(writerInsert.Row + 2, 7, worksheetInsert).Text.ToString() != "")) { if (ExcelUtil.GetRange(writerInsert.Row, 7, worksheetInsert).Text.ToString() == "REFER <HKBK01>") { referStartRow = writerInsert.Row; } writerInsert.PlaceNext(writerInsert.Row + 1, 7); } referLastRow = writerInsert.Row + 2; Range referRange = ExcelUtil.GetRange(referStartRow, 7, referLastRow, 15, worksheetInsert); Range bakRange = ExcelUtil.GetRange(referLastRow + 1, 7, referLastRow + 1 + (referLastRow - referStartRow), 15, worksheetInsert); referRange.Copy(bakRange); ExcelUtil.GetRange(2, 7, referStartRow - 1, 15, worksheetInsert).Clear(); writerInsert.PlaceNext(1, 7); for (int j = 0; j < pageList.Count; j++) { if (j % 3 == 0) { writerInsert.PlaceNext(writerInsert.Row + 1, 7); } else { } writerInsert.WriteLine(pageList[j].minNumberStr); writerInsert.WriteLine(pageList[j].maxNumberStr); writerInsert.WriteLine(pageList[j].pageNumberChineseStr); } ExcelUtil.GetRange(2, 7, writerInsert.Row, 15, worksheetInsert).Font.FontStyle = ExcelUtil.GetRange(4, 1, worksheetInsert).Font.FontStyle; ExcelUtil.GetRange(2, 7, writerInsert.Row, 15, worksheetInsert).Font.Size = ExcelUtil.GetRange(4, 1, worksheetInsert).Font.Size; ExcelUtil.GetRange(2, 7, writerInsert.Row, 9, worksheetInsert).Interior.Color = 16777164.0; ExcelUtil.GetRange(2, 10, writerInsert.Row, 12, worksheetInsert).Interior.Color = 13409484.0; ExcelUtil.GetRange(2, 13, writerInsert.Row, 15, worksheetInsert).Interior.Color = 10092543.0; Range referR = ExcelUtil.GetRange(writerInsert.Row + 1, 7, writerInsert.Row + (referLastRow - referStartRow) + 1, 15, worksheetInsert); bakRange.Copy(referR); if ((referLastRow - referStartRow) > (writerInsert.Row - referStartRow)) { ExcelUtil.GetRange(writerInsert.Row + (referLastRow - referStartRow) + 3, 7, referLastRow + 1 + (referLastRow - referStartRow), 15, worksheetInsert).Clear(); } } }