Unable to write response in Excel using ExcelDNA in C#

Question

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).

Please can someone help.

Answer 1

It is basically a bad idea to modify the worksheet values from within a cell-function. When a cell-function is executed, Excel is typically busy. Thus an attempt to modify a certain cell value may fail.

Your code is correct. The only thing you are missing is the IsMacroType=true property of the ExcelFunction attribute. The attribute changes the way the function interacts with the worksheet. For more details, see here. The example of the code is below.

[ExcelFunction(IsMacroType = true)]
public static int TestWrite1()
{
    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;

    currentSheet.Cells[1, 1].Value = "Hello, world";

    return 0;
}

Alternatively, you could manually queue your code for execution using the function ExcelAsyncUtil.QueueAsMacro. It will run your delegate safely when Excel is ready. See here for more details on that function. The example is below.

[ExcelFunction]
public static int TestWrite2()
{
    ExcelAsyncUtil.QueueAsMacro(new ExcelAction(() => {
        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;

        currentSheet.Cells[1, 1].Value = "Hello, world";
    }));

    return 0;
}

Finally, if your aim is to return some value from the cell-funtion to the active cell, you could just return the value normally.

[ExcelFunction]
public static string TestWrite3()
{
    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;

    /* Insert to the current cell where the cell-function is being executed. */
    return "Hello, world"; 
}

The complete code is below:

using ExcelDna.Integration;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;

namespace ClassLibraryExcelDna
{
public class UDF
{
    [ExcelFunction(IsMacroType = true)]
    public static int TestWrite1()
    {
        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;

        currentSheet.Cells[1, 1].Value = "Hello, world";

        return 0;
    }

    [ExcelFunction]
    public static int TestWrite2()
    {
        ExcelAsyncUtil.QueueAsMacro(new ExcelAction(() => {
            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;

            currentSheet.Cells[1, 1].Value = "Hello, world";
        }));

        return 0;
    }

    [ExcelFunction]
    public static string TestWrite3()
    {
        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;

        /* Insert to the current cell where the cell-function is being executed. */
        return "Hello, world"; 
    }
}
}