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