C# add content to Excel Worksheet

Question

How can I add a value to an Excel Cell of the active Workbook using C#? I'm new to VSTO C# and can't find a solution that works for me...

This is my code (from this question Write to cell in excel using c#) :

using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelSDRAddIn
{
    public partial class UserControlSDR : UserControl
    {
        public UserControlSDR()
        {
            InitializeComponent();
        }

        private void btnTemplate_Click(object sender, EventArgs e)
        {
            Excel.Worksheet ws = (Excel.Worksheet)(sender as Workbook).ActiveSheet;
            ws.Cells[1, 1] = "Value";
        }
    }
}

After running it I get the following exception on this line Excel.Worksheet ws = (Excel.Worksheet)(sender as Workbook).ActiveSheet;:

An exception of type 'System.NullReferenceException' occurred in ExcelSDRAddIn.dll but was not handled in user code

Additional information: Object reference not set to an instance of an object.

Also tried with this:

    dynamic excelType = Type.GetTypeFromProgID("Excel.Application");
    excelType.Visible = true;
    excelType.Workbooks.Add();
    dynamic workSheet = excelType.ActiveSheet;

    workSheet.Cells[1, 1] = "Names";
    workSheet.Cells[1, 2] = "Age";

And returns:

An exception of type 'Microsoft.CSharp.RuntimeBinder.RuntimeBinderException' occurred in System.Core.dll but was not handled in user code

Additional information: 'System.Reflection.TypeInfo' does not contain a definition for 'Visible'

Another example that fails:

Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;
...
var Excel = new Excel.Application();
oXL = new Microsoft.Office.Interop.Excel.Application();
oWB = oXL.ActiveWorkbook;
oSheet = oWB.ActiveSheet;
oSheet.Cells[1, 1] = "Value";

What's the runtime type of sender coming in as? From what you have posted, it seems that the sender is either not of type workbook, so its null when you try to cast it as Workbook, or sender is coming in as null. — Jinish, Mar 07 '17 at 12:23
Additionally you might want to check an article i wrote to interact with Office without using Interop: https://jinishbhardwaj.wordpress.com/2016/06/21/com-interop-without-referencing-com-assemblies-using-dynamic-c/ — Jinish, Mar 07 '17 at 12:24
I checked the article and added the code, but still doesn't work...see the updated Question — Valip, Mar 07 '17 at 12:34
I would advice you to NOT use Interop, but rather use one of the good NuGet packages for Excel, like ClosedXml: https://www.nuget.org/packages/ClosedXML/ — AndersJH, Mar 07 '17 at 12:43
Please do not delete this question afterwards. I saw the previous question and now I see it's not there and link is broken. — Ori Nachum, Mar 07 '17 at 12:48
@AndersJH I followed your advice and installed ClosedXML, but I can't get it to insert a simple value to the worksheet — Valip, Mar 07 '17 at 13:12

Jeremy Thompson · Accepted Answer · 2017-03-07T13:22:23.347

3

To access the workbook in your scenario you use:

  Globals.ThisAddIn.Application.Workbooks...

MSDN Ref: Programming VSTO Add-Ins

The following code example shows how to use the Application field to create a new workbook in an VSTO Add-in for Microsoft Office Excel. This example is intended to be run from the ThisAddIn class.

  Excel.Workbook newWorkbook = this.Application.Workbooks.Add(System.Type.Missing);

To do the same thing from outside the ThisAddIn class, use the Globals object to access the ThisAddIn class. For more information about the Globals object, see Global Access to Objects in Office Projects.

  Excel.Workbook newWorkbook = Globals.ThisAddIn.Application.Workbooks.Add(System.Type.Missing);

Edit: You need to use this code to get the active worksheet

  Excel.Worksheet ws = (Excel.Worksheet)Globals.ThisAddin.Application.ActiveSheet;

edited Mar 07 '17 at 13:22

answered Mar 07 '17 at 12:45

Jeremy Thompson

61,933
36
195
321

How can I achieve that without creating a new Workbook? I need to use the active Workbook – Valip Mar 07 '17 at 13:17
1

See my edit **Excel.Worksheet ws = (Excel.Worksheet)Globals.ThisAddin.Application.ActiveSheet;** – Jeremy Thompson Mar 07 '17 at 13:22
ws.Get_Range.. you can Google this stuff – Jeremy Thompson Mar 07 '17 at 13:31
Yes, now I get the rest. Thank you! – Valip Mar 07 '17 at 13:33
thx, one last thing, Vsto is SLOW! To get good speed see http://stackoverflow.com/questions/356371/excel-interop-efficiency-and-performance and look up VSTOContrib – Jeremy Thompson Mar 07 '17 at 13:36

Renatas M. · Answer 2 · 2017-03-07T12:34:32.420

0

From your method name btnTemplate_Click I bet you are using button to call this function. So the sender in this case is Button and not the Workbook. When you try to unbox sender as Workbook you will get null and you getting NullReferenceException while trying to access ActiveSheet property.

Please read the question in your linked reference one more time. It states that:

I created a visual studio excel workbook project which contains ThisWorkbook class

I am not sure how that class looks like but I think this is the place where method private void ThisWorkbook_Startup(object sender, System.EventArgs e) should be hooked to some kind `Startup event.

edited Mar 07 '17 at 12:34

answered Mar 07 '17 at 12:25

Renatas M.

11,694
1
43
62

You're right. I'm trying to add some content to the active sheet when I press a button from the User Control...it would help me a lot if you post a solution with an working example – Valip Mar 07 '17 at 12:38
Sorry, no. This is not the place to ask to build you full solution. Here you can ask for help to overcome your problem, but no one will post all solution. – Renatas M. Mar 07 '17 at 12:49

C# add content to Excel Worksheet

2 Answers2