Reading Data From Excel SpreadSheet - Cannot perform runtime binding on a null reference

Question

I have the code below to read data from an excel file, the problem I am running into is that when I try to read the range data, I get an exception "Cannot perform runtime binding on a null reference" on this line

if (Int32.TryParse(xlRange.Cells[1, 4].Value2.ToString(), out value))

What I want to know is how I do access the information in the range properly please. Thanks in advance

void ReadFromExcelToGrid2(String fileNameString)
        {
            try
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileNameString);
                //get list of worksheets associated with the current workbook
                Microsoft.Office.Interop.Excel.Sheets worksheets = xlWorkbook.Worksheets;
                //list the work books in a dropdownlist 
                IDictionary<int, String> sheets = new Dictionary<int, String>();
                foreach (Microsoft.Office.Interop.Excel.Worksheet displayWorksheet in xlWorkbook.Worksheets)
                {
                    sheets.Add(displayWorksheet.Index, displayWorksheet.Name);
                }
                cmbWorksheets.DataSource = new BindingSource(sheets, null);;
                cmbWorksheets.DisplayMember = "Value";
                cmbWorksheets.ValueMember = "Key";

                Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[4]; // assume it is the first sheet
                Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange; // get the entire used range
                int value = 0;
                if (Int32.TryParse(xlRange.Cells[1, 4].Value2.ToString(), out value)) // get the F cell from the first row
                {
                    int numberOfColumnsToRead = value * 4;
                    for (int col = 7; col < (numberOfColumnsToRead + 7); col++)
                    {
                        Console.WriteLine(xlRange.Cells[1, col].Value2.ToString()); // do whatever with value
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            }

That was my mistake. I changed the numbers to test earlier but forgot to change it back. Yes, the problem persists. — Kobojunkie
– Kobojunkie, Commented Jul 21, 2012 at 16:33

Zoltan Toth · Accepted Answer · 2012-09-04 22:44:50Z

7

if (xlRange.Cells[row, 1].Text == "")
{
    xlRange.Cells[row, 1].value = "Blank";
}

The above code will resolve this problem. However Blank wont be there in the cell. But it will allow you to proceed further. Basically C# / Excel does not like null.

edited Sep 4, 2012 at 22:44

Zoltan Toth

47.8k12 gold badges132 silver badges138 bronze badges

answered Sep 4, 2012 at 17:05

Sivakumar

712 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:22:50Z

1

What if you use "Convert.ToString()" rather than ".ToString()"? If "xlRange.Cells[1, 4].Value2" is null then it won't have a .ToString() function and, if i'm thinking correctly, should throw an exception. The difference between Convert.ToString() and .ToString() is that Convert.ToString() will return a null value, whereas .ToString() will blowup.

Can you set a break point before that code executes to ensure that the xlRange actually contains information in it?

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Jul 21, 2012 at 17:38

StoriKnow

5,8676 gold badges40 silver badges47 bronze badges

Collectives™ on Stack Overflow

Reading Data From Excel SpreadSheet - Cannot perform runtime binding on a null reference

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related