public static void ClearA2B5() { ExcelReference xlRef = new ExcelReference(1, 4, 0, 1); int rows = xlRef.RowLast - xlRef.RowFirst + 1; int cols = xlRef.ColumnLast - xlRef.ColumnFirst + 1; object[,] values = new object[rows, cols]; // nulls xlRef.SetValue(values); MessageBox.Show("Done clearing!"); }
static void DoResize(ExcelReference target) { try { // Get the current state for reset later XlCall.Excel(XlCall.xlcEcho, false); // Get the formula in the first cell of the target string formula = (string)XlCall.Excel(XlCall.xlfGetCell, 41, target); ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId); bool isFormulaArray = (bool)XlCall.Excel(XlCall.xlfGetCell, 49, target); if (isFormulaArray) { object oldSelectionOnActiveSheet = XlCall.Excel(XlCall.xlfSelection); object oldActiveCell = XlCall.Excel(XlCall.xlfActiveCell); // Remember old selection and select the first cell of the target string firstCellSheet = (string)XlCall.Excel(XlCall.xlSheetNm, firstCell); XlCall.Excel(XlCall.xlcWorkbookSelect, new object[] { firstCellSheet }); object oldSelectionOnArraySheet = XlCall.Excel(XlCall.xlfSelection); XlCall.Excel(XlCall.xlcFormulaGoto, firstCell); // Extend the selection to the whole array and clear XlCall.Excel(XlCall.xlcSelectSpecial, 6); ExcelReference oldArray = (ExcelReference)XlCall.Excel(XlCall.xlfSelection); oldArray.SetValue(ExcelEmpty.Value); XlCall.Excel(XlCall.xlcSelect, oldSelectionOnArraySheet); XlCall.Excel(XlCall.xlcFormulaGoto, oldSelectionOnActiveSheet); } // Get the formula and convert to R1C1 mode bool isR1C1Mode = (bool)XlCall.Excel(XlCall.xlfGetWorkspace, 4); string formulaR1C1 = formula; if (!isR1C1Mode) { // Set the formula into the whole target formulaR1C1 = (string)XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell); } // Must be R1C1-style references object ignoredResult; XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlcFormulaArray, out ignoredResult, formulaR1C1, target); if (retval != XlCall.XlReturn.XlReturnSuccess) { // TODO: Consider what to do now!? // Might have failed due to array in the way. firstCell.SetValue("'" + formula); } } finally { XlCall.Excel(XlCall.xlcEcho, true); } }
public Range Range(ExcelReference reference) { string internalSheetName = (string)XlCall.Excel(XlCall.xlSheetNm, reference); Match match = Regex.Match(internalSheetName, @"\[(.*)\](.*)"); string workbookName = match.Groups[1].Value; string sheetName = match.Groups[2].Value; Range TopLeft = Workbooks[workbookName].Sheets[sheetName].Cells(reference.RowFirst + 1, reference.ColumnFirst + 1); Range BottowRight = Workbooks[workbookName].Sheets[sheetName].Cells(reference.RowLast + 1, reference.ColumnLast + 1); Range rng = Range(TopLeft, BottowRight); return rng; }
internal static object ResizeObservable(object[,] array, ExcelReference caller) { object callerAfter = XlCall.Excel(XlCall.xlfCaller); if (callerAfter == null) { // This is the good RTD array call return Resize(array, caller); } // Some spurious RTD call - just return. return array; }
public static void GenerateGuid() { ExcelReference selectedCells = (ExcelReference)XlCall.Excel(XlCall.xlfSelection); for (int row = selectedCells.RowFirst; row <= selectedCells.RowLast; ++row) { for (int column = selectedCells.ColumnFirst; column <= selectedCells.ColumnLast; ++column) { string guid = Guid.NewGuid().ToString().ToUpper(); ExcelReference activeCell = new ExcelReference(row, column); activeCell.SetValue(guid); } } }
public static void DnaCS() { var sw = Stopwatch.StartNew(); foreach (var r_c in range) { var row = r_c.Item1 - 1; var col = r_c.Item2 - 1; var cell = new ExcelReference(row, col); cell.SetValue(1); } sw.Stop(); dynamic excel = ExcelDnaUtil.Application; excel.StatusBar = sw.Elapsed.TotalSeconds; }
// This function will run in the UDF context. // Needs extra protection to allow multithreaded use. public static object Resize(object[,] array) { if (array == null) return (object[,]) null; var caller = Excel(xlfCaller) as ExcelReference; if (caller == null) return array; int rows = array.GetLength(0); int columns = array.GetLength(1); if (rows == 0 || columns == 0) return array; if ((caller.RowLast - caller.RowFirst + 1 == rows) && (caller.ColumnLast - caller.ColumnFirst + 1 == columns)) { // Size is already OK - just return result return array; } var rowLast = caller.RowFirst + rows - 1; var columnLast = caller.ColumnFirst + columns - 1; // Check for the sheet limits if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1 || columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1) { // Can't resize - goes beyond the end of the sheet - just return #VALUE // (Can't give message here, or change cells) return ExcelError.ExcelErrorValue; } // TODO: Add some kind of guard for ever-changing result? ExcelAsyncUtil.QueueAsMacro(() => { // Create a reference of the right size var target = new ExcelReference(caller.RowFirst, rowLast, caller.ColumnFirst, columnLast, caller.SheetId); DoResize(target); // Will trigger a recalc by writing formula }); // Return what we have - to prevent flashing #N/A return array; }
public static object Resize(object[,] array, ExcelReference caller) { if (caller == null) { Debug.Print("Resize - Abandoning - No Caller"); return array; } int rows = array.GetLength(0); int columns = array.GetLength(1); if ((caller.RowLast - caller.RowFirst + 1 != rows) || (caller.ColumnLast - caller.ColumnFirst + 1 != columns)) { // Size problem: enqueue job, call async update and return #N/A EnqueueResize(caller, rows, columns); ExcelAsyncUtil.QueueAsMacro(DoResizing); } // Size is already OK - just return result return array; }
public void OnButtonPressed(IRibbonControl control) { //MessageBox.Show("Hello from control " + control.Id); string str=FirstAddIn.MyGetHostname(); var UIHandler = new Action<object>((o) => { ctrl.ShowDialog(); }); //if (waitSet) //avoid double submission //{ // ThreadPool.QueueUserWorkItem(new WaitCallback(UIHandler)); // return; //} //else //{ // waitSet = true; //} //var wait = new ManualResetEvent(false); var handler = new EventHandler((o, e) => { cr = (DataResponse)o; result = cr.data; //waitSet = false; //wait.Set(); ExcelAsyncUtil.QueueAsMacro(() => { //ExcelReference cell = (ExcelReference)XlCall.Excel(XlCall.xlfActiveCell); ExcelReference cell = refCell; int testRowSize = cr.row; int testColSize = cr.col; var activeCell = new ExcelReference(cell.RowFirst, testRowSize + cell.RowFirst - 1, cell.ColumnLast, cell.ColumnLast + testColSize - 1); activeCell.SetValue(result); XlCall.Excel(XlCall.xlcSelect, activeCell); }); }); if (this.ctrl.getRunState() == RunState.IDLE) { ExcelAsyncUtil.QueueAsMacro(() => { refCell = (ExcelReference)XlCall.Excel(XlCall.xlfActiveCell); }); ctrl.registerCallback(handler); } ThreadPool.QueueUserWorkItem(new WaitCallback(UIHandler)); ////For simplicity, we implement the wait here //wait.WaitOne(); //if (result == null) //{ // return; //} //ExcelAsyncUtil.QueueAsMacro(() => //{ // //ExcelReference cell = (ExcelReference)XlCall.Excel(XlCall.xlfActiveCell); // ExcelReference cell = refCell; // int testRowSize = cr.row; // int testColSize = cr.col; // var activeCell = new ExcelReference(cell.RowFirst,testRowSize+cell.RowFirst-1, cell.ColumnLast ,cell.ColumnLast + testColSize-1); // activeCell.SetValue(result); // XlCall.Excel(XlCall.xlcSelect, activeCell); //}); }
static void EnqueueResize(ExcelReference caller, int rows, int columns) { ExcelReference target = new ExcelReference(caller.RowFirst, caller.RowFirst + rows - 1, caller.ColumnFirst, caller.ColumnFirst + columns - 1, caller.SheetId); ResizeJobs.Enqueue(target); }
private void SetCellValue(int row, int col, string value) { ExcelReference cell = new ExcelReference(row - 1, col - 1); cell.SetValue(value); }
public ExcelSelectionHelper(ExcelReference refToSelect) { // Remember old selection state on the active sheet _oldSelectionOnActiveSheet = XlCall.Excel(XlCall.xlfSelection); _oldActiveCellOnActiveSheet = XlCall.Excel(XlCall.xlfActiveCell); // Switch to the sheet we want to select var refSheet = (string) XlCall.Excel(XlCall.xlSheetNm, refToSelect); XlCall.Excel(XlCall.xlcWorkbookSelect, refSheet); // record selection and active cell on the sheet we want to select _oldSelectionOnRefSheet = XlCall.Excel(XlCall.xlfSelection); _oldActiveCellOnRefSheet = XlCall.Excel(XlCall.xlfActiveCell); // make the selection XlCall.Excel(XlCall.xlcFormulaGoto, refToSelect); }
public static void RegistrationInfo() { try { Excel.Application Application = ExcelDnaUtil.Application as Excel.Application; List<string> addinPaths = new List<string>(); if (ExcelDnaUtil.ExcelVersion >= 14.0) { foreach (dynamic addIn in Application.AddIns2) { if (addIn.IsOpen) { addinPaths.Add(addIn.FullName); } } } else { HashSet<string> allPaths = new HashSet<string>(); dynamic funcInfos = Application.RegisteredFunctions; if ((funcInfos != null)) { for (int i = funcInfos.GetLowerBound(0); i <= funcInfos.GetUpperBound(0); i++) { allPaths.Add(funcInfos[i, 1]); } } addinPaths.AddRange(allPaths); } dynamic wb = Application.Workbooks.Add(); Excel.Worksheet shIndex = wb.Sheets(1); shIndex.Name = "Add-Ins"; shIndex.Cells[1, 1] = "Add-In Path"; shIndex.Cells[1, 2] = "Registration Info?"; int row = 2; foreach (string path in addinPaths) { shIndex.Cells[row, 1] = path; // Try to read RegistrationInfo dynamic result = ExcelIntegration.GetRegistrationInfo(path, 0); if (result.Equals(ExcelError.ExcelErrorNA)) { shIndex.Cells[row, 2] = false; } else { shIndex.Cells[row, 2] = true; // Dump the result to a new sheet Excel.Worksheet shInfo = wb.Sheets.Add(After: wb.Sheets(wb.Sheets.Count)); shInfo.Name = System.IO.Path.GetFileName(path); // C API via ExcelReference would work well here dynamic refInfo = new ExcelReference(0, result.GetUpperBound(0), 0, 254, shInfo.Name); refInfo.SetValue(result); } row = row + 1; } shIndex.Activate(); } catch (Exception ex) { Debug.Print(ex.ToString()); } }
static void DoResize(ExcelReference target) { // Get the current state for reset later using (new ExcelEchoOffHelper()) using (new ExcelCalculationManualHelper()) { ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId); // Get the formula in the first cell of the target string formula = (string)Excel(xlfGetCell, 41, firstCell); bool isFormulaArray = (bool)Excel(xlfGetCell, 49, firstCell); if (isFormulaArray) { // Select the sheet and firstCell - needed because we want to use SelectSpecial. using (new ExcelSelectionHelper(firstCell)) { // Extend the selection to the whole array and clear Excel(xlcSelectSpecial, 6); ExcelReference oldArray = (ExcelReference)Excel(xlfSelection); oldArray.SetValue(ExcelEmpty.Value); } } // Get the formula and convert to R1C1 mode bool isR1C1Mode = (bool)Excel(xlfGetWorkspace, 4); string formulaR1C1 = formula; if (!isR1C1Mode) { object formulaR1C1Obj; XlReturn formulaR1C1Return = TryExcel(xlfFormulaConvert, out formulaR1C1Obj, formula, true, false, ExcelMissing.Value, firstCell); if (formulaR1C1Return != XlReturn.XlReturnSuccess || formulaR1C1Obj is ExcelError) { string firstCellAddress = (string)Excel(xlfReftext, firstCell, true); Excel(xlcAlert, "Cannot resize array formula at " + firstCellAddress + " - formula might be too long when converted to R1C1 format."); firstCell.SetValue("'" + formula); return; } formulaR1C1 = (string)formulaR1C1Obj; } // Must be R1C1-style references object ignoredResult; //Debug.Print("Resizing START: " + target.RowLast); XlReturn formulaArrayReturn = TryExcel(xlcFormulaArray, out ignoredResult, formulaR1C1, target); //Debug.Print("Resizing FINISH"); // TODO: Find some dummy macro to clear the undo stack if (formulaArrayReturn != XlReturn.XlReturnSuccess) { string firstCellAddress = (string)Excel(xlfReftext, firstCell, true); Excel(xlcAlert, "Cannot resize array formula at " + firstCellAddress + " - result might overlap another array."); // Might have failed due to array in the way. firstCell.SetValue("'" + formula); } } }
public ExcelSelectionHelper(ExcelReference refToSelect) { // Remember old selection state on the active sheet mOldSelectionOnActiveSheet = Excel(xlfSelection); mOldActiveCellOnActiveSheet = Excel(xlfActiveCell); // Switch to the sheet we want to select string refSheet = (string)Excel(xlSheetNm, refToSelect); Excel(xlcWorkbookSelect, new object[] { refSheet }); // record selection and active cell on the sheet we want to select mOldSelectionOnRefSheet = Excel(xlfSelection); mOldActiveCellOnRefSheet = Excel(xlfActiveCell); // make the selection Excel(xlcFormulaGoto, refToSelect); }
// This function will run in the UDF context. // Needs extra protection to allow multithreaded use. public static object Resize(object[,] array) { var caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference; if (caller == null) return array; var rows = array.GetLength(0); var columns = array.GetLength(1); if (rows == 0 || columns == 0) return array; if ((caller.RowLast - caller.RowFirst + 1 == rows) && (caller.ColumnLast - caller.ColumnFirst + 1 == columns)) { // Size is already OK - just return result return array; } var rowLast = caller.RowFirst + rows - 1; var columnLast = caller.ColumnFirst + columns - 1; // Check for the sheet limits if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1 || columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1) { // Can't resize - goes beyond the end of the sheet - just return #VALUE // (Can't give message here, or change cells) return ExcelError.ExcelErrorValue; } // TODO: Add some kind of guard for ever-changing result? if (columns > 1) { ExcelAsyncUtil.QueueAsMacro(() => { var target = new ExcelReference(caller.RowFirst, caller.RowFirst, caller.ColumnFirst + 1, columnLast); var firstRow = new object[columns - 1]; for (var i = 1; i < columns; i++) { firstRow[i - 1] = array[0, i]; } target.SetValue(firstRow); }); } if (rows > 1) { ExcelAsyncUtil.QueueAsMacro(() => { var target = new ExcelReference(caller.RowFirst + 1, rowLast, caller.ColumnFirst, columnLast); var data = new object[rows - 1, columns]; for (var i = 1; i < rows; i++) { for (var j = 0; j < columns; j++) { data[i - 1, j] = array[i, j]; } } target.SetValue(data); }); } // Return what we have - to prevent flashing #N/A return array; }
public object GetCell( int row, int col ) { // TODO: optimise to not create an ExcelReference on every visit. ExcelReference xlref = new ExcelReference( row, row, col, col, "s2cfg" ); return xlref.GetValue( ); }
/// <summary>Creates a dropdown list at a specific Excel cell. /// </summary> /// <param name="excelRange">The Excel range given as a single row or single column containing values of some properties.</param> /// <param name="rowIndex">The null-based row index of the <paramref name="excelRange"/> where to add a dropdown list.</param> /// <param name="columnIndex">The null-based column index of the <paramref name="excelRange"/> where to add a dropdown list.</param> /// <param name="dropDownListAsString">The semicolon separated <see cref="System.String"/> representation of the dropdown list to add.</param> /// <remarks>This method adds a specific (Excel range) data validation with respect to a specific Excel position of <paramref name="excelRange"/> /// and the dropdown list will contains the elements of the <paramref name="dropDownListAsString"/>.</remarks> public static void CreateDropdownList(this ExcelDna.Integration.ExcelReference excelRange, int rowIndex, int columnIndex, string dropDownListAsString) { if ((sm_UseDataAdvice != DropDownListCreationType.None) && (dropDownListAsString != null) && (dropDownListAsString.Length > 0)) { try { if (ExcelDna.Integration.ExcelDnaUtil.IsInFunctionWizard() == false) { dynamic sheet = ExcelAddIn.ExcelApplication.ActiveSheet; dynamic cell = sheet.Cells[excelRange.RowFirst + rowIndex + 1, excelRange.ColumnFirst + columnIndex + 1]; // in Excel rows/columns are one-based if (((sm_UseDataAdvice == DropDownListCreationType.EmptyExcelCells) && (ExcelDataConverter.IsEmptyCell(cell.Value) == true)) || ((sm_UseDataAdvice == DropDownListCreationType.ExcelCellsWithoutFormula) && (cell.HasFormula == false)) || (sm_UseDataAdvice == DropDownListCreationType.Always)) { cell.Validation.Delete(); cell.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertInformation, XlFormatConditionOperator.xlBetween, dropDownListAsString, Type.Missing); // todo: Excel has a restriction cell.Validation.ShowError = false; } } } catch (Exception e) { // Logger.Stream.Add_Info_ExcelCellDropdownListFails(exception: e); } } }
void ProcessRtdCalls() { if (_rtdCalls.Count == 0) { return; } // First, build a dictionary of what we saw, // and add any new active callers. Dictionary <ExcelReference, TopicIdList> callerTopicMap = new Dictionary <ExcelReference, TopicIdList>(); foreach (RtdCall call in _rtdCalls) { // find the corresponding RtdTopicInfo int topic; if (!_activeTopics.TryGetValue(call.TopicKey, out topic)) { Debug.Print("!!! Unknown Rtd Call: " + call.TopicKey); continue; } // This is a call to a topic we know // Check if we already have an entry for this caller in the callerTopicMap.... TopicIdList callerTopics; if (!callerTopicMap.TryGetValue(call.Caller, out callerTopics)) { // ... no - it's a new entry. // Add the caller, and the topic map callerTopics = new TopicIdList(); callerTopicMap[call.Caller] = callerTopics; } // Get the known callers ExcelReferenceSet callers = _activeTopicCallers[topic]; if (!callers.Contains(call.Caller)) { // Previously unknown caller for this topic - add to _activeTopicCallers callers.Add(call.Caller); // Add the Topic to the list of topic to watch for this caller TopicIdList topics; if (!_activeCallerTopics.TryGetValue(call.Caller, out topics)) { // Not seen this caller before for this topic - record for future use topics = new TopicIdList(); _activeCallerTopics[call.Caller] = topics; } // This is a caller we've dealt with before // This topic should not be in the list // TODO: What if it is called twice from a single formula...? Debug.Assert(!topics.Contains(topic)); topics.Add(topic); // NOTE: topics might include the orphans! } // One of the known callers // Anyway - record that we saw it in this calc callerTopics.Add(topic); } // Now figure out what to clean up // For each caller and its topics that we saw in this calc ... TopicIdList orphans = new TopicIdList(); foreach (KeyValuePair <ExcelReference, TopicIdList> callerTopics in callerTopicMap) { ExcelReference thisCalcCaller = callerTopics.Key; TopicIdList thisCalcTopics = callerTopics.Value; // ... Check the topics in the _activeCallerTopics list for this caller. TopicIdList activeTopics = _activeCallerTopics[thisCalcCaller]; TopicIdList activeTopicsToRemove = null; // Lazy initialize foreach (int activeTopic in activeTopics) { // If we've seen the topic in this calc, all is fine. if (thisCalcTopics.Contains(activeTopic)) { continue; } // ... Any topic not seen in this calc might be an orphan (so check if it has other callers). // ... ensure that the active topic also does not have the caller in its activeCallers list any more. ExcelReferenceSet activeCallers = _activeTopicCallers[activeTopic]; if (activeCallers.Remove(thisCalcCaller)) { // - now check if this topic is an orphan if (activeCallers.Count == 0) { orphans.Add(activeTopic); } } // The activeTopic was one of the topics for thisCalcCaller, but is no longer. // Should now be removed from the list of topics for this caller. if (activeTopicsToRemove == null) { activeTopicsToRemove = new TopicIdList(); } activeTopicsToRemove.Add(activeTopic); } if (activeTopicsToRemove != null) { foreach (int topicToRemove in activeTopicsToRemove) { activeTopics.Remove(topicToRemove); if (activeTopics.Count == 0) { // Unlikely...? (due to how the bug works - the caller should have a new topic) _activeCallerTopics.Remove(thisCalcCaller); } } } } // Clear our recording and disconnect the orphans _rtdCalls.Clear(); DisconnectOrphanedTopics(orphans); }
public RtdCall(ExcelReference caller, string[] topicInfo) { Caller = caller; TopicInfo = topicInfo; }
public ExcelReferenceWrapper(int rowFirst, int rowLast, int columnFirst, int columnLast) { excelReference = new ExcelReference(rowFirst, rowLast, columnFirst, columnLast); }
public ExcelReferenceWrapper(int row, int column) { excelReference = new ExcelReference(row, column); }
public ExcelReferenceWrapper(int rowFirst, int rowLast, int columnFirst, int columnLast, IntPtr sheetId) { excelReference = new ExcelReference(rowFirst, rowLast, columnFirst, columnLast, sheetId); }
public void OnButtonPressed(IRibbonControl control) { //MessageBox.Show("Hello from control " + control.Id); string str=FirstAddIn.MyGetHostname(); var UIHandler = new Action<object>((o) => { ctrl.ShowDialog(); }); ExcelAsyncUtil.QueueAsMacro(() => { refCell = (ExcelReference)XlCall.Excel(XlCall.xlfActiveCell); }); if (waitSet) //avoid double submission { ThreadPool.QueueUserWorkItem(new WaitCallback(UIHandler)); return; } else { waitSet = true; } var wait = new ManualResetEvent(false); var handler = new EventHandler((o, e) => { cr = (TestWinForm.UserControl1.CtrlmRequest)o; result = MakeArrayetest(cr.row, cr.col); waitSet = false; wait.Set(); }); ctrl.registerCallback(handler); ThreadPool.QueueUserWorkItem(new WaitCallback(UIHandler)); //For simplicity, we implement the wait here wait.WaitOne(); //ExcelReference cell = ExcelAsyncUtil.QueueAsMacro(() =>XlCall.Excel(XlCall.xlfActiveCell); //ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference; //MessageBox.Show("Active cell:" + cell.RowFirst+","+cell.ColumnFirst); //var activeCell = new ExcelReference(1, 1); ExcelAsyncUtil.QueueAsMacro(() => { //ExcelReference cell = (ExcelReference)XlCall.Excel(XlCall.xlfActiveCell); ExcelReference cell = refCell; int testRowSize = cr.row; int testColSize = cr.col; var activeCell = new ExcelReference(cell.RowFirst,testRowSize+cell.RowFirst-1, cell.ColumnLast ,cell.ColumnLast + testColSize-1); //object[,] o = new object[testRowSize, testColSize]; //for (int i = 0; i < testRowSize; i++) //{ // o[i, 0] = i; // o[i, 1] = "test" + i; // o[i, 2] = DateTime.Now; // o[i, 3] = "" + i + ",3"; // o[i, 4] = "" + i + ",4"; //} activeCell.SetValue(result); XlCall.Excel(XlCall.xlcSelect, activeCell); }); }
public ExcelReferenceWrapper(int rowFirst, int rowLast, int columnFirst, int columnLast, string sheetName) { excelReference = new ExcelReference(rowFirst, rowLast, columnFirst, columnLast, sheetName); }
void ProcessRtdCalls() { if (_rtdCalls.Count == 0 && _rtdCompletes.Count == 0) { return; } // First, build a dictionary of what we saw, // and add any new active callers. Dictionary <ExcelReference, TopicIdList> callerTopicMap = new Dictionary <ExcelReference, TopicIdList>(); foreach (RtdCall call in _rtdCalls) { // find the corresponding RtdTopicInfo int topic; if (!_activeTopics.TryGetValue(call.TopicKey, out topic)) { Debug.Print("!!! Unknown Rtd Call: " + call.TopicKey); continue; } // This is a call to a topic we know // Check if we already have an entry for this caller in the callerTopicMap.... TopicIdList callerTopics; if (!callerTopicMap.TryGetValue(call.Caller, out callerTopics)) { // ... no - it's a new entry. // Add the caller, and the topic map callerTopics = new TopicIdList(); callerTopicMap[call.Caller] = callerTopics; } // Get the known callers ExcelReferenceSet callers = _activeTopicCallers[topic]; if (!callers.Contains(call.Caller)) { // Previously unknown caller for this topic - add to _activeTopicCallers callers.Add(call.Caller); // Add the Topic to the list of topic to watch for this caller TopicIdList topics; if (!_activeCallerTopics.TryGetValue(call.Caller, out topics)) { // Not seen this caller before for this topic - record for future use topics = new TopicIdList(); _activeCallerTopics[call.Caller] = topics; } // This is a caller we've dealt with before // This topic should not be in the list // TODO: What if it is called twice from a single formula...? Debug.Assert(!topics.Contains(topic)); topics.Add(topic); // NOTE: topics might include the orphans! } // One of the known callers // Anyway - record that we saw it in this calc callerTopics.Add(topic); } // Process calls that are 'complete' - we can record that we didn't see (i.e. call xlfRtd for) the topic in this call foreach (var call in _rtdCompletes) { // These callers were called, but not with the topics we expected (since there was no real RTD call) // find the corresponding RtdTopicInfo int topic; if (!_activeTopics.TryGetValue(call.TopicKey, out topic)) { Debug.Fail("!!! Unknown Rtd Call: " + call.TopicKey); continue; } // This is a call to a topic we know // Check if we already have an entry for this caller in the callerTopicMap.... TopicIdList callerTopics; if (!callerTopicMap.TryGetValue(call.Caller, out callerTopics)) { // This caller has no topics (in this calculation) // Note that it was called (but we'll add no topics...) // Otherwise it's fine - we've listed this as a caller to examine, but we won't put this topic in callerTopics = new TopicIdList(); callerTopicMap[call.Caller] = callerTopics; } if (callerTopics.Contains(topic)) { Debug.Fail("!!! Inconsistent Rtd Call (RtdCalls contains the RtdComplete call): " + call.TopicKey); } } // Now figure out what to clean up // For each caller and its topics that we saw in this calc ... TopicIdList orphans = new TopicIdList(); foreach (KeyValuePair <ExcelReference, TopicIdList> callerTopics in callerTopicMap) { ExcelReference thisCalcCaller = callerTopics.Key; TopicIdList thisCalcTopics = callerTopics.Value; // ... Check the topics in the _activeCallerTopics list for this caller. TopicIdList activeTopics = _activeCallerTopics[thisCalcCaller]; TopicIdList activeTopicsToRemove = null; // Lazy initialize foreach (int activeTopic in activeTopics) { // If we've seen the topic in this calc, all is fine. if (thisCalcTopics.Contains(activeTopic)) { continue; } // ... Any topic not seen in this calc might be an orphan (so check if it has other callers). // ... ensure that the active topic also does not have the caller in its activeCallers list any more. ExcelReferenceSet activeCallers = _activeTopicCallers[activeTopic]; if (activeCallers.Remove(thisCalcCaller)) { // - now check if this topic is an orphan if (activeCallers.Count == 0) { orphans.Add(activeTopic); } } // The activeTopic was one of the topics for thisCalcCaller, but is no longer. // Should now be removed from the list of topics for this caller. if (activeTopicsToRemove == null) { activeTopicsToRemove = new TopicIdList(); } activeTopicsToRemove.Add(activeTopic); } if (activeTopicsToRemove != null) { foreach (int topicToRemove in activeTopicsToRemove) { activeTopics.Remove(topicToRemove); if (activeTopics.Count == 0) { // This happens if we have completed the topic, and Excel might not disconnect // but the topic is no longer active. // I think Excel will try to Disconnect later, e.g. if we delete the formula or something. _activeCallerTopics.Remove(thisCalcCaller); } } } } // Clear our recording and disconnect the orphans _rtdCalls.Clear(); DisconnectOrphanedTopics(orphans); }
bool Equals(ExcelReference other) { if (ReferenceEquals(null, other)) return false; if (ReferenceEquals(this, other)) return true; // Implement equality check based on contents. // CONSIDER: Implement in class derived from List. if (rectangles.Count != other.rectangles.Count) return false; for (int i = 0; i < rectangles.Count; i++) { if (!Equals(rectangles[i], other.rectangles[i])) return false; } return other.sheetId.Equals(sheetId); }