C# & Excel: Value2 has no definition

Question

I needed a way to read an Excel file through c#. I found an example https://coderwall.com/p/app3ya/read-excel-file-in-c

I created a standalone program to test it and it worked as I had expected. I added the code as a subprogram into an existing program and encountered errors.

The first error, "Cannot implicitly convert type 'object' to Microsoft.Office.Interop.Excel._Worksheet. An explicit conversion exists."

The second error, "'Object' does not contain a definition for 'Value2'".

I fixed the first error with adding (Excel._Worksheet) to xlWorkbook.Sheets[1];

I can't figure out the second one on Value2 and need your help: "Error 98 'object' does not contain a definition for 'Value2' and no extension method 'Value2' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)"

Here's my code:

using System;
using System.Collections.Generic;
using System.Collections;ArrayList, ListBox, ComboBox, etc.
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using XView.Properties;
using System.Reflection;
using ZedGraph;
using System.IO;
using System.Management;
using System.Threading;
using System.Diagnostics;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;



private void readExcelFile(ArrayList al)
    {
        string rowItems = string.Empty;

        //Create COM Objects. Create a COM object for everything that is referenced
        Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"W:\acad\x-pak\XPak setting_tool_charts.xlsx");
        Excel._Worksheet xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
        Excel.Range xlRange = xlWorksheet.UsedRange;

        int rowCount = xlRange.Rows.Count;
        int colCount = xlRange.Columns.Count;

        //iterate over the rows and columns and print to the console as it appears in the file - Excel is not zero based!!
        //---start at i = 4 since in Rick's Excel file, that's where the data begins!
        for (int i = 4; i <= rowCount; i++)
        {
            rowItems = string.Empty;

            for (int j = 1; j <= colCount; j++)
            {
                //new line
                if (j == 1)
                    Console.Write("\r\n");

                //write the value to the console
                if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
                {
                    //Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t");

                    if (j == 1) //new line
                        rowItems = xlRange.Cells[i, j].Value2.ToString();
                    else
                        rowItems += "^" + xlRange.Cells[i, j].Value2.ToString();
                }
            }
            al.Add(rowItems);
        }

        //cleanup
        GC.Collect();
        GC.WaitForPendingFinalizers();

        //rule of thumb for releasing com objects:
        //  never use two dots, all COM objects must be referenced and released individually
        //  ex: [somthing].[something].[something] is bad

        //release com objects to fully kill excel process from running in the background
        Marshal.ReleaseComObject(xlRange);
        Marshal.ReleaseComObject(xlWorksheet);

        //close and release
        xlWorkbook.Close();
        Marshal.ReleaseComObject(xlWorkbook);

        //quit and release
        xlApp.Quit();
        Marshal.ReleaseComObject(xlApp);
    }

Solution 1

The cause of this problem was the Target Framework! It was using .Net 3.5 because the main program was created in 2008. This subprogram was added today and it assumes that "Embed Interop Types = true" (Property of Reference: Microsoft.Office.Interop.Excel).

I went to the Project Properties and set the Target Framework to ".Net Framework 4 Client Profile". That generated 43 errors (can't remember the error name but indicated that maybe I needed to add a reference to Microsoft.CSharp.dll). I added the Microsoft.CSharp reference and all the errors disappeared!

Solution 2

I think it is relevant to Microsoft.Office.Interop.Excel version. I have never previously used code block following ways to solve the same mistake. I think it will work for him.

   object _xVal;

    _xVal= ((Excel.Range)xlWorksheet.Cells[i, j]).Value2;

Comments

[NoChance]

+1, Good one Bob, sorry I did not see your invitation earlier.

[Bob T]

Thanks Emmad, when you said " Please check this and make sure you add all the references", it made me check the differences between the standalone and the subprogram.

[Robert]

Can you add an explanation?