Unable to write response in Excel cells / range using ExcelDNA in C#
Aniket Karhadkar
I'm unable to write my REST API response in Excel using ExcelDNA in C#. It is working fine with reading values from specific column.
xlCell[i, 1] = "TEST"; // fails with System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800A03EC'
currentSheet.Cells[i, 2].Value = "TEST"; // same issue
Excel reading code --
Excel.Range xlCell;
Excel.Application xlapp = (Excel.Application)ExcelDnaUtil.Application;
xlapp.Visible = true;
Worksheet currentSheet;
xlCell = xlapp.ActiveCell;
Excel.Workbook wbook = xlapp.ActiveWorkbook;
currentSheet = wbook.ActiveSheet;
Please note that same code piece is working fine with VSTO addin project (through ribbon button event click) but not in .net class library (for UDF).
But I need UDF support explicitly that's the reason , went ahead with .net class library project with (.xll)
Appreciate if please can someone help.
Thanks,
Aniket Karhadkar
Govert van Drimmelen
Hi Aniket,
Excel does not allow you to write to a cell using during a calculation (i.e. from a UDF being evaluated from a cell).
The idea is that your UDF returns a value, which Excel will then display and use as the cell value.
So you’d have:
public static object GetMyData(string info)
{
// Calculate or get the result
var result = info + " – Result”;
return result;
}
And not the macro version where your write to a cell:
public static void PutMyDataIntoActive(string info)
{
// Calculate or get the result
// Write to a cell using the COM object model
}
There is some support in Excel-DNA for making async functions, if your back-end calls are slow.
But for a start, I suggest just doing a synchronous call, and caching the result into a local Dictionary to speed things up when recalculating.
Once that works you can refine more.
-Govert
--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/7273cea9-fe0f-4bec-930d-517b9c943aebn%40googlegroups.com.
Aniket Karhadkar
() =>
{
xlCell.Value2 = cellValue;
});
Govert van Drimmelen
Hi Aniket,
Writing to a cell from a UDF is a bad idea – one problem is that the active cell might not be the caller of the UDF, another is that the dependency tree the Excel manages can’t track things correctly.
If you have a version of Excel which supports Dynamic Arrays – Excel 2022 or an Office 365 subscription – then you can return a large array of data from your UDF and it will expand and work correctly on the sheet.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/ab632fc6-c855-4c8a-905d-c444f45c97fen%40googlegroups.com.
Aniket Karhadkar
Govert van Drimmelen
Hi Aniket,
> Is there any way you can suggest to have UDF support which will eventually write large REST API data to Excel sheet ?
If you have a version of Excel which supports Dynamic Arrays – Excel 2022 or an Office 365 subscription – then you can return a large array of data from your UDF and it will expand and work correctly on the sheet.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/1a24acc1-4c40-42e0-bc62-8e70a34733f5n%40googlegroups.com.
Kedar Kulkarni
Please read Govert's answer first and implement that if all users are on office 365.
For office with no DynamicArray support - you can try the following.
1. Please make sure you write a custom function that solely depends on the parameters of the function. Understand how excel UDFs are written and what ExcelAsyncUtil.Run does.
2. Get the cell where the excel formula is entered using below in the function call and in main thread. Do not access excel application / range from background thread.
var caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
3. Convert the caller to excel range using the method ReferenceToRange explained at https://docs.excel-dna.net/range-parameters/ in QueueAsMacro / main thread context.
4. Create an object array from the data you receive from your service and write all data at once just below the formula cell. (do not overwrite the formula cell) .
5. below code is copied from https://stackoverflow.com/questions/536636/write-array-to-excel-range and is modified a bit to use excelreference. I have not tested the code myself.
[ExcelFunction("GetMyData")]
public static object GetMyData(string info)
{
var caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
// should be queued in a background thread
return ExcelAsyncUtil.Run("GetMyData", info, () => GetDataAndRenderTask(info, caller));
}
private static object GetDataAndRenderTask(string info, ExcelReference caller)
{
var dt = GetDataFromRestApi(info);
ExcelAsyncUtil.QueueAsMacro(()
=>
{
var arr = new object[dt.Rows.Count, dt.Columns.Count];
for (var r = 0; r < dt.Rows.Count; r++)
{
var dr = dt.Rows[r];
for (var c = 0; c < dt.Columns.Count; c++)
{
arr[r, c] = dr[c];
}
}
var c1 = ReferenceToRange(caller).Offset[1, 0];
var ws = c1.Worksheet;
Excel.Range c2 = ws.Cells[c1.Row + dt.Rows.Count - 1, dt.Columns.Count];
var range = ws.Range[c1, c2];
range.Value = arr;
});
return DateTime.Now.ToString("dd-MMM-yyyy hh:mm:ss"); // current time (can return cache creation time) as a return value so that you know when this function last calculated / hit the service.
}
private static Excel.Range ReferenceToRange(ExcelReference caller)
{
// https://docs.excel-dna.net/range-parameters/
var app = (Application)ExcelDnaUtil.Application;
var refText = (string)XlCall.Excel(XlCall.xlfReftext, caller, true);
var range = app.Range[refText];
return range;
}
private static DataTable GetDataFromRestApi(string info)
{
// get data from rest API / from cache if already called once and cache hasnt expired. Return date time of cache creation from here as an out parameter.
throw new NotImplementedException();
}
6. I have used a similar technique a lot of times and it works - but it comes with some careful planning - It is possible that excel could calculate your formula again and again if there are volatile formulas in the precedents so you might need caching done for the web service calls. Also, it is possible that user gives hundreds of functions and your server may suddenly receive too many rest api service calls so you must make sure that your excel code would queue the requests in such a way that it does not overwhelm your backend server and server also has a mechanism to expect REST api calls from multiple users and it is able to load balance.
thanks
Kedar