CODEWITHSUNDEEP
c#excelvisual-studio-2017
Fayaz
Fayaz

Reputation: 89

Read excel null/blank values

I am trying to read excel null/blank values. I have looked into hundreds of solutions and either I am implementing it wrong or it just does not seem to work and results in Microsoft.CSharp.RuntimeBinder.RuntimeBinderException:'Cannot perform runtime binding on a null reference'

This is one of the last codes I tried.(Since I was trying to put NA in all the null cells)

for (int i = 2; i <= rowCount; i++)
{
    string natext = xlRange.Value2[rowCount, colCount];
    if (natext == null)
    {
        natext = "NA";
    }

Any ideas that can help me with some examples?

If the click the details it shows: Microsoft.CSharp.RuntimeBinder.RuntimeBinderException
HResult=0x80131500 Message=Cannot perform runtime binding on a null reference Source=
StackTrace:

Upvotes: 1

Views: 4386

Answers (1)

Scott Hannen
Scott Hannen

Reputation: 29222

First, the Excel object model is really weird. Value2 returns an object, and that object can be of all sorts of different types. If xlRange is a cell, then it returns the value of that cell, which could be a string or a double or something else. If xlRange is multiple cells then that object is an array of values. And then each of those values is an object. For each value you don't know if it's a string or a double or something else.

That's not fun to deal with. It's actually really, really bad. C# is a strongly-typed language, which means that you know what type everything is and you don't have to guess. Excel Interop takes that away from you and says, "Here's an object. It could be anything or lots of things that could each be anything. Figure it out. Good luck."

Instead of getting the Value2 property of the range and then looping through the array, it's much easier to deal with the cells in the range instead.

Given that excelRange is a Range of cells:

for (var row = 1; row <= excelRange.Rows.Count; row++)
{
    for (var column = 1; row <= excelRange.Columns.Count; row++)
    {
        var cellText = excelRange[row, column].Text.ToString();
    }
}

This does two things. First, you're looking at one cell at a time. Second, you're using the Text property. The Text property should always be a string so you could just do this and it would almost certainly work:

string cellText = excelRange.Cells[row, column].Text;

It's just that the object model returns dynamic, so even though it is a string, the possibility is left open that maybe it won't be.

My strong recommendation - and I think most developers would agree - is to abandon Excel Interop and run from it, and use a library like EPPlus instead. There are tons of examples.

Excel Interop works by actually starting an instance of Excel and giving you access to the clunky VBA object model. It's evil. Chances are that if you open your task manager right now you'll see several extra instances of Excel open that you didn't expect to see. Fixing that is a whole separate frustrating problem.

For some years Excel files have just been collections of XML documents, and EPPlus helps you to work with them as documents, but providing all sorts of helper methods so that you can interact with sheets, ranges, cells, and so forth. Try it. Trust me, you'll never look back.

Here's an example after adding the EPPlus Nuget package:

var pathToYourExcelWorkbook = @"c:\somepath\document.xlsx";
using (var workbookPackage = new ExcelPackage(new FileInfo(pathToYourExcelWorkbook)))
{
    var workbook = workbookPackage.Workbook;
    var sheet = workbook.Worksheets[1]; // 1-based, or use the name.
    for (var row = 1; row <= 10; row++)
    {
        for (var column = 1; column <= 10; column++)
        {
            var cellText = sheet.Cells[row, column].Text;
        }
    }
}

It's awesome. No starting or closing an application - you're just reading from a file. No weird COM objects. And the objects are all strongly-typed. The Text property returns a string.

Upvotes: 4

Related Questions