Writing values to Excel cells using C#

Question

I have a simple C# method written in Visual Studio Code that should create a new Excel workbook, write a value to a cell within a worksheet and then save the file.

using System;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;

namespace Test
{
    class ExcelTest
    {
        public void TestMethod()
        {
            String outputPath = "C:\\Test.xlsx";

            Excel.Application excel = new Excel.Application();
            Excel.Workbook workbook = excel.Workbooks.Add(Type.Missing);
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.ActiveSheet;

            ((Excel.Range)sheet.Cells[1,1]).Value = "Hello";

            workbook.SaveAs(outputPath);
            workbook.Close();
            excel.Quit();
        }
    }
}

When calling this method, code execution stops on the ((Excel.Range)sheet.Cells[1,1]).Value = "Hello"; line with the following exception:

Exception has occurred: CLR/System.NullReferenceException An unhandled exception of type 'System.NullReferenceException' occurred: 'Object reference not set to an instance of an object.'

The method seems to be interacting with Excel as replacing the line with 'sheet.Name = "Test";' results in the file being saved and the workbook containing one sheet with the name 'Test'.

For reference I am using Visual Studio Code, and have Excel 2016 installed on my machine, and the .csproj file has the following references:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.0</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Office.Interop.Excel" Version="*"/>
  </ItemGroup>
</Project>

Thanks

What is null? Is it sheet? Is it sheet.Cells? Is it sheet.Cells[1,1]? — MineR
That's odd - your code worked perfectly for me, so the code itself is not a problem. The reference in my project file is "<Reference Include="Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c, processorArchitecture=MSIL">" — PaulF
I believe it is 'sheet.Cells[1,1]' which is null, as I can reference 'sheet.Name' and change that, and also get 'sheet.Cells.Count' which returns a value of 0. — Tom
Have you tried making the Application visible (excel.Visible = true;) to see what the worksheet looks like & whether you can manually type into cell A1. My other thought is possibly a permissions issue - you may not have authority to create files in the root directory - try creating it in a folder you know you have write permission (e.g. Environment.SpecialFolder.MyDocuments) — PaulF

Tom Tom · Accepted Answer · 2018-05-03T14:22:48

For reference I fixed this by rebuilding the application as a .NET Framework application rather than a .NET Core application. The COM interop functionality worked after doing this.

Writing values to Excel cells using C#

2 Answers