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