Ejemplo n.º 1
1
    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!");
    }
Ejemplo n.º 2
1
        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);
            }
        }
Ejemplo n.º 3
1
 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;
 }
Ejemplo n.º 4
1
 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;
 }
Ejemplo n.º 5
1
 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);
         }
     }
 }
Ejemplo n.º 6
1
        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;
        }
Ejemplo n.º 7
1
        // 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;
        }
Ejemplo n.º 8
1
        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;
        }
Ejemplo n.º 9
1
        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);

            //});
        }
Ejemplo n.º 10
1
 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);
 }
Ejemplo n.º 12
1
            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);
            }
Ejemplo n.º 13
1
    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());
        }
    }
Ejemplo n.º 14
1
        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);
                }
            }
        }
Ejemplo n.º 15
1
        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);
        }
Ejemplo n.º 16
1
            // 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;
            }
Ejemplo n.º 17
1
 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( );
 }
Ejemplo n.º 18
0
        /// <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);
 }
Ejemplo n.º 24
0
        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);
        }
Ejemplo n.º 27
0
 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);
 }