I've got the following code in my Excel DNA plugin
In my AutoOpen method I put the following code:
ExcelIntegration.RegisterUnhandledExceptionHandler(ex => ex.ToString());
I've got the following function that gets called from my excel sheet.
[ExcelFunction(Category = "Foo", Description = "Sets value of cell")]
public static Foo(String idx)
{
Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
Excel.Workbook wb = app.Workbooks[1];
Excel.Worksheet ws = GetSheet("Main");
// This gives us the row
Excel.Name idxRange = wb.Names.Item("COL_Main_Index");
var row = (int)app.WorksheetFunction.Match(idx, idxRange.RefersToRange, 0);
// Get the Column
Excel.Name buyOrderRange = wb.Names.Item("COL_Main_BuyOrder");
var col = (int)buyOrderRange.RefersToRange.Cells.Column;
// create the range and update it
Excel.Range r = (Excel.Range)ws.Cells[row, col];
r.Value ="Foo";
}
The issue is that I can't actually set any cell values. When I call the method it causes an error on the last line.
My error handler gives me the followign error:
{System.Runtime.InteropServices.COMException (0x800A03EC)
I've also tried to set the cell value like so:
r = (Excel.Range)ws.Cells[12, 22];
const int nCells = 1;
Object[] args1 = new Object[1];
args1[0] = nCells;
r.GetType().InvokeMember("Value2", BindingFlags.SetProperty, null, r, args1);
With the same result.
Can anyone point to what I might be doing wrong here?
Excel-DNA is an independent project to integrate . NET into Excel. With Excel-DNA you can make native (. xll) add-ins for Excel using C#, Visual Basic.NET or F#, providing high-performance user-defined functions (UDFs), custom ribbon interfaces and more. Your entire add-in can be packed into a single .
Actually, you can write in any cell if you do this in an async job as a macro.
Simple Example:
using ExcelDna.Integration;
using Excel = Microsoft.Office.Interop.Excel;
[ExcelFunction(Category = "Foo", Description = "Sets value of cell")]
public static Foo(String idx)
{
Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
Excel.Range range = app.ActiveCell;
object[2,2] dummyData = new object[2, 2] {
{ "foo", "bar" },
{ 2500, 7500 }
};
var reference = new ExcelReference(
range.Row, range.Row + 2 - 1, // from-to-Row
range.Column - 1, range.Column + 2 - 1); // from-to-Column
// Cells are written via this async task
ExcelAsyncUtil.QueueAsMacro(() => { reference.SetValue(dummyData); });
// Value displayed in the current cell.
// It still is a UDF and can be executed multiple times via F2, Return.
return "=Foo()";
}
Writing into a single Cell:
int row = 5;
int column = 6;
var reference = new ExcelReference(row - 1, column - 1);
ExcelAsyncUtil.QueueAsMacro(() => { reference.SetValue("Foobar"); });
// edit: just fyi, you can also use:
private void WriteArray(object[,] data)
{
Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
Excel.Worksheet worksheet= (Excel.Worksheet)app.ActiveWorkbook.ActiveSheet;
Excel.Range startCell = app.ActiveCell;
Excel.Range endCell = (Excel.Range)worksheet.Cells[startCell.Row + data.GetLength(0) - 1, startCell.Column + data.GetLength(1) - 1];
var writeRange = worksheet.Range[startCell, endCell];
writeRange.Value2 = data;
}
And then:
object[,] data = ...;
ExcelAsyncUtil.QueueAsMacro(() =>
{
WriteArray();
});
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With