Try to open my Excel file using C# and get an error

Question

I try to read my Exel file from code and received System.InvalidCastException:

Additional information: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Tools.Excel.Worksheet'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{297DC8D9-EABD-45A1-BDEF-68AB67E5C3C3}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

This error occurs in objsheet = appExcel.ActiveWorkbook.ActiveSheet; so I try to cast it into objsheet = (Worksheet)appExcel.ActiveWorkbook.ActiveSheet; but this error still exist

using Microsoft.Office.Interop.Excel;

private static Microsoft.Office.Interop.Excel._Application appExcel;
private static Microsoft.Office.Interop.Excel.Workbook newWorkbook = null;
private static Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
private static string file = @"D:\file.xlsx";

      //Method to initialize opening Excel
    static void excel_init(String path)
    {
        //appExcel = new Microsoft.Office.Interop.Excel.Application();

        if (System.IO.File.Exists(path))
        {
            // then go and load this into excel
            newWorkbook = appExcel.Workbooks.Open(file, true, true);

            int count = newWorkbook.Worksheets.Count;
            if (count > 0)
            {
                objsheet = (Microsoft.Office.Interop.Excel.Worksheet)newWorkbook.Worksheets[1];
            }
        }
        else
        {
            MessageBox.Show("Unable to open file!");
            System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
            appExcel = null;
            System.Windows.Forms.Application.Exit();
        }
    }

newWorkbook is null.

Answer 1

Try the below code and see if it works, if not let us know what problems you have:

using Excel = Microsoft.Office.Interop.Excel;

namespace Excel_Sample
{
    public partial class YourClass
    {
        Excel.Application appExcel;
        Excel.Workbook newWorkbook;
        Excel.Worksheet objsheet;
        string file = @"D:\file.xlsx";

        static void excel_init()
        {
            if (System.IO.File.Exists(file))
            {
                //Start Excel and get Application object.
                appExcel = new Excel.Application();
                //Get a workbook.;
                newWorkbook = (Excel.Workbook)(appExcel.Workbooks.Open(file));

                int count = newWorkbook.Worksheets.Count;
                if (count > 0)
                {
                    //Get Your Worksheet
                    objsheet = (Excel.Worksheet)newWorkbook.ActiveSheet;
                }
            }
            else
            {
                MessageBox.Show("Unable to open file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;
                System.Windows.Forms.Application.Exit();
            }
        }
    }
}

Answer 2

Seems like you are using

    Microsoft.Office.Tools.Excel.Worksheet 

instead of

    Microsoft.Office.Interop.Excel.Worksheet

which is why you are getting an invalid cast exception.

Answer 3

Workbook.ActiveSheet Property might not be the best choice for programmatically opened Excel files as it can actually return a non-Worksheet object

You might want to consider checking sheets count and using indexes:

int count = newWorkbook.Worksheets.Count;
if (count > 0)
{
    objsheet = (Worksheet) newWorkbook.Worksheets[1];
}

And try not to break the 2-dot rule - you'll need to release all you COM's to properly close your app and Excel.

Edited:

You could be mixing Microsoft.Office.Interop.Excel with Microsoft.Office.Tools.Excel namespaces.

Try to declare and assign as follows:

private static Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
...
objsheet = (Microsoft.Office.Interop.Excel.Worksheet) newWorkbook.Worksheets[1];