public ChangeInfo GetAllBrokerChangeInfo(Worksheet worksheet, int lastRowContainInfo) { ChangeInfo ChangedBrokerInfo = new ChangeInfo(); ChangedBrokerInfo.AllChangedBrokerList = new List <BrokerChangeInfo>(); for (int rowNum = 2; rowNum <= lastRowContainInfo; rowNum++) { BrokerChangeInfo brokerChangeInfo = new BrokerChangeInfo(); brokerChangeInfo.Name = ExcelUtil.GetRange(rowNum, 1, worksheet).Value2.ToString(); brokerChangeInfo.Ric = ExcelUtil.GetRange(rowNum, 2, worksheet).Value2.ToString(); brokerChangeInfo.Type = ExcelUtil.GetRange(rowNum, 3, worksheet).Value2.ToString(); brokerChangeInfo.Date = ExcelUtil.GetRange(rowNum, 4, worksheet).Value2.ToString(); ChangedBrokerInfo.AllChangedBrokerList.Add(brokerChangeInfo); } ChangedBrokerInfo.DifDayChangeInfo = ChangedBrokerInfo.GetDifDayChangeInfo(); //ChangedBrokerInfo.AllChangedBrokerList.Sort(); return(ChangedBrokerInfo); }
public void UpdateHKSEAFile(ChangeInfo ChangedBrokerInfo, List <BordLotSizeChangeInfo> bordLotSizeChangeList) { using (ExcelApp appSEA = new ExcelApp(false, false)) { string SEAFilePath = BackupFiles(configObj.HKSEA_Z2_FILE_PATH); var workbookSEA = ExcelUtil.CreateOrOpenExcelFile(appSEA, SEAFilePath); var worksheetSEA = ExcelUtil.GetWorksheet("Sheet2", workbookSEA); int rowPos = 1; using (ExcelLineWriter writer = new ExcelLineWriter(worksheetSEA, 1, 1, ExcelLineWriter.Direction.Right)) { int curRowPos = worksheetSEA.UsedRange.Row + worksheetSEA.UsedRange.Rows.Count - 1; for (int i = ChangedBrokerInfo.DifDayChangeInfo.Count - 1; i > -1; i--) { SameDayChangeInfo sameDayChangedInfo = ChangedBrokerInfo.DifDayChangeInfo[i]; rowPos = curRowPos; while (rowPos > 0) { if (ExcelUtil.GetRange(rowPos, 4, worksheetSEA).Value2 == null || string.IsNullOrEmpty(ExcelUtil.GetRange(rowPos, 4, worksheetSEA).Value2.ToString())) { rowPos--; continue; } else if (DateTimeCompare(sameDayChangedInfo.Date, ExcelUtil.GetRange(rowPos, 4, worksheetSEA).Text.ToString())) { if (rowPos == curRowPos) { curRowPos = rowPos; InsertChangeInfo(rowPos + 1, writer, sameDayChangedInfo, worksheetSEA, bordLotSizeChangeList); break; } else if (ExcelUtil.GetRange(rowPos + 1, 4, worksheetSEA) == null || (string.IsNullOrEmpty(ExcelUtil.GetRange(rowPos + 1, 4, worksheetSEA).Text.ToString()))) { rowPos--; } else if (!DateTimeCompare(sameDayChangedInfo.Date, ExcelUtil.GetRange(rowPos + 1, 4, worksheetSEA).Text.ToString())) { curRowPos = rowPos; InsertChangeInfo(rowPos + 1, writer, sameDayChangedInfo, worksheetSEA, bordLotSizeChangeList); break; } else { rowPos--; continue; } } else { if (rowPos == 1) { curRowPos = rowPos; InsertChangeInfo(rowPos + 1, writer, sameDayChangedInfo, worksheetSEA, bordLotSizeChangeList); break; } else if (ExcelUtil.GetRange(rowPos - 1, 4, worksheetSEA) == null || (ExcelUtil.GetRange(rowPos - 1, 4, worksheetSEA).Text == null)) { rowPos--; } else if (DateTimeCompare(sameDayChangedInfo.Date, ExcelUtil.GetRange(rowPos - 1, 4, worksheetSEA).Text.ToString())) { curRowPos = rowPos; InsertChangeInfo(rowPos + 1, writer, sameDayChangedInfo, worksheetSEA, bordLotSizeChangeList); break; } else { rowPos--; continue; } } } } } //workbookSEA.SaveCopyAs(Path.Combine(Path.GetDirectoryName(workbookSEA.FullName), Path.GetFileName(configObj.HKSEA_Z2_FILE_PATH))); workbookSEA.SaveCopyAs(configObj.HKSEA_Z2_FILE_PATH); workbookSEA.Close(false, workbookSEA.FullName, false); //File.Delete(SEAFilePath); } }
public void UpdateFiles() { int lastUsedRowNum = -1; List <BordLotSizeChangeInfo> bordLotSizeChangeList = new List <BordLotSizeChangeInfo>(); //Range AKRange = GetAKRange(out lastUsedRowNum); //Range AKRange = null; using (ExcelApp app = new ExcelApp(false, false)) { string addDropFilePath = BackupFiles(configObj.ADD_DROP_FILE_PATH); var workbook = ExcelUtil.CreateOrOpenExcelFile(app, addDropFilePath); Worksheet worksheetRaw = ExcelUtil.GetWorksheet(configObj.ADD_DROP_RAW_WORKSHEET_NAME, workbook); using (ExcelApp appSource = new ExcelApp(false, false)) { var workbookSource = ExcelUtil.CreateOrOpenExcelFile(appSource, configObj.SOURCE_FILE_PATH); Range AKRange = GetAKRange(workbookSource, out lastUsedRowNum, out bordLotSizeChangeList); Range r = ExcelUtil.GetRange(1, 1, lastUsedRowNum, 11, worksheetRaw); AKRange.Copy(Missing.Value); r.PasteSpecial(XlPasteType.xlPasteFormats, XlPasteSpecialOperation.xlPasteSpecialOperationNone, Missing.Value, Missing.Value); ExcelUtil.GetRange(1, 1, worksheetRaw).Copy(Missing.Value); workbookSource.Close(false, workbookSource.FullName, false); } //Run Macros worksheetRaw.Activate(); app.ExcelAppInstance.GetType().InvokeMember("Run", BindingFlags.Default | BindingFlags.InvokeMethod, null, app.ExcelAppInstance, new object[] { "Macro1" }); var worksheet5 = ExcelUtil.GetWorksheet("Sheet5", workbook); if (worksheet5 == null) { logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet5 from workbook {0}", workbook.FullName)); } UpdateSheet5(worksheet5, bordLotSizeChangeList); var worksheetAddDrop = ExcelUtil.GetWorksheet("ADD DROP", workbook); if (worksheetAddDrop == null) { logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet ADD DROP from workbook {0}", workbook.FullName)); } UpdateAddDropSheet(worksheetAddDrop, bordLotSizeChangeList); int lastRowContainInfo = 2; while (ExcelUtil.GetRange(lastRowContainInfo, 1, worksheetAddDrop).Value2 != null) { string value = (ExcelUtil.GetRange(lastRowContainInfo, 1, worksheetAddDrop).Value2.ToString().Trim().ToLower()); if (value.Contains("add") || (value.Contains("delete") || (value.Contains("change")))) { lastRowContainInfo++; } else { break; } } ChangeInfo ChangedBrokerInfo = GetAllBrokerChangeInfo(worksheet5, lastRowContainInfo - 1); UpdateHKSEAFile(ChangedBrokerInfo, bordLotSizeChangeList); Range A2Range = ExcelUtil.GetRange(2, 1, lastRowContainInfo - 1, 16, worksheetAddDrop); UpdateTemplateV2File(A2Range, lastRowContainInfo); workbook.SaveCopyAs(Path.Combine(Path.GetDirectoryName(workbook.FullName), Path.GetFileName(configObj.ADD_DROP_FILE_PATH))); workbook.Close(false, workbook.FullName, false); File.Delete(addDropFilePath); } }