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!");
}
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;
}
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);
}
}
}
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);
}
}
private void SetCellValue(int row, int col, string value)
{
ExcelReference cell = new ExcelReference(row - 1, col - 1);
cell.SetValue(value);
}
// 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 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());
}
}
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);
//});
}
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 override bool SetValue(object value)
{
return(excelReference.SetValue(value));
}
