CODEWITHSUNDEEP
c#excelvsto
Valip
Valip

Reputation: 4610

C# add content to Excel Worksheet

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";

Upvotes: 1

Views: 1660

Answers (2)

Jeremy Thompson
Jeremy Thompson

Reputation: 65544

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;

Upvotes: 3

Renatas M.
Renatas M.

Reputation: 11820

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.

Upvotes: 0

Related Questions