Excel worksheet in Windows form application withou

Question

I am converting several VBA projects to Windows form applications. The only problem I have is that some of the functionality of Excel is essential to the application, such as R1C1 formulas. I do not want to instantiate the Excel application or access saved worksheets. All of the data is retrieved by querying Oracle databases. 2-Dimensional arrays are not an option because the columns contain differing datatypes, and DataGridViews are too slow to work with.

I thought simply dimming a Microsoft.Office.Interop.Excel.Worksheet object would be enough, but the program kept failing and upon inspecting the object's elements in debug mode, I found that every value says this:

{"Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.WorksheetClass' to interface type 'Microsoft.Office.Interop.Excel._Worksheet'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D8-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))."}

I would really appreciate the help if someone is able to tell me how to get a worksheet object without opening Excel, or at least offer a reasonable alternative.

Answer 1

You probably need to instantinate your object correctly. Worksheet requires Excel COM object, so you normally instantinate its first and then access a sheet. Here is some sample code:

Dim xl As Microsoft.Office.Interop.Excel.Application
xl = New Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
wb = xl.Workbooks.Add()
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
ws = wb.ActiveSheet

Now you can work with your ws. Notice, that I do not instantinate it using Dim .. as New.

By doing this you will get an invisible instance of Excel running in your background. You must close your application explicitly after you are done to prevent it from staying in memory:

/// after your are finished
xl.Quit()
Marshal.ReleaseComObject(xl)

This is especially important if you using it in a kind of loop.

Answer 2

maybe you've already solved this issue, but here is my solution, i used what you posted and what i've found from another article, so i can tell you this works fine, here is my example:

    Dim xlApp As New Microsoft.Office.Interop.Excel.Application

    Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
    System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
    xlApp.Workbooks.Add()
    xlApp.ActiveSheet.Delete()
    xlApp.ActiveSheet.Delete()

    With xlApp
        With .ActiveSheet.QueryTables.Add("ODBC;DRIVER=SQL Server;SERVER=" & DAO.GetNombreServidor & ";UID=" & DAO.GetLoginUsuario & ";PWD=" & DAO.GetPassUsuario & ";WSID=RICARDO2;DATABASE=" & DAO.GetNombreBaseDeDatos & "", xlApp.Range("A1"))
            .CommandText = "SELECT * FROM ESTADOS"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            '.RefreshStyle = xlOverwriteCells
            'xlInsertDeleteCells
            .SavePassword = False
            .SaveData = False
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = False
            .Refresh(False)
        End With
    End With

    System.Threading.Thread.CurrentThread.CurrentCulture = oldCI

    xlApp.Visible = True

I hope it helps!