Filling DataGrid Columns w/ Excel Data

[Zmcpherson]

Alright, I finally got this code to work after hours of toiling:

Dim path As String = OpenFileDialog1.FileName
        Dim myDataset As New DataSet()
        Dim strConn = New OleDbConnection("Provider=Microsoft.ACE.Oledb.12.0;Data Source=" & path & ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""")
        Dim myData As New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
        myData.Fill(myDataset)
        DataGridView1.DataSource = myDataset.Tables(0).DefaultView

Now that I figured that out I was going to try and place the data in a specific location. On my application I have a datagridview set up with 4 columns. What I would like to do is put column A of the excel file under the 1st column of the datagridview and column C of the Excel File in the second column of the datagridview.

So replace:

DataGridView1.DataSource = myDataset.Tables(0).DefaultView

with:

DataGridView1.columns(0) = myDataset.Tables(0).columns(0)
    DataGridView1.columns(1) = myDataset.Tables(0).columns(2)

Obviously this doesnt work, and something tells me I might need a for loop to import the data, but I have never imported information from an Excel file before and to make it worse I have never worked with datagridviews before so I have no idea how to go about this.

I would like to do something like this if I could:

For x = 1 To xldoc.rows.length - 1
            DataGridView1.Item(0, x).Value = CType(xlDoc.Cells(0, x + 1), Excel.Range).Text
        Next

Any and all help is always appreciated.

[kareninstructor]

If the data returned has column header information in the first row rather than data (seeing this in your connection string HDR=YES) I would suggest adding columns for the DataGridView at design time and set the data property for each column dependent on the column names of your data returned from Excel.

So if column C header is say LastName assign LastName to the data property for column 1 which is your second column.

Or you can change the display index of a column as follows which is an alternative if the column headers are not always the same.

DataGridView1.Columns(2).DisplayIndex = 1

[Zmcpherson]

Unfortunately the data starts on row 8, the header is on row 7, and the data could be anywhere from 2 lines to 15 lines. Would it be easier just to stop using excel and switch back to CSV's? CSV's seemed to be much less of a headache

[kareninstructor]

Unfortunately the data starts on row 8, the header is on row 7, and the data could be anywhere from 2 lines to 15 lines. Would it be easier just to stop using excel and switch back to CSV's? CSV's seemed to be much less of a headache

You could use a named range or set the cells to work with as shown below. There are two versions of the CommandText. In short it depends on if your column names are consistant or not. Blank rows can be easily handled as shown in the code below.

Note place the attached Excel file into your Bin\Debug folder to try this code out.

Public Class frmMainForm
    Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dtSheet1 As New DataTable
        Dim Builder As New OleDbConnectionStringBuilder _
        With _
        { _
            .DataSource = "ksg1.xlsx", _
            .Provider = "Microsoft.ACE.Oledb.12.0" _
        }
        '
        Using cn As New System.Data.OleDb.OleDbConnection
            Builder.Add("Extended Properties", "Excel 12.0;HDR=YES;IMEX=1;")
            cn.ConnectionString = Builder.ConnectionString

            Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
                cn.Open()

                cmd.CommandText = _
                    <SQL>
                    SELECT 
                        Column1, 
                        Column2, 
                        Column3, 
                        Column4 
                    FROM [Sheet1$A7:D19]
                    WHERE 
                        Column1 IS NOT NULL AND 
                        Column2 IS NOT NULL AND 
                        Column3 IS NOT NULL AND 
                        Column4 IS NOT NULL
                    </SQL>.Value

                ' Alternate is to use a named range
                'cmd.CommandText = _
                '    <SQL>
                '    SELECT 
                '        Column1, 
                '        Column2, 
                '        Column3, 
                '        Column4 
                '    FROM MyRange
                '    WHERE 
                '        Column1 IS NOT NULL AND 
                '        Column2 IS NOT NULL AND 
                '        Column3 IS NOT NULL AND 
                '        Column4 IS NOT NULL
                '    </SQL>.Value

                dtSheet1.Load(cmd.ExecuteReader)

            End Using
        End Using

        DataGridView1.DataSource = dtSheet1

        DataGridView1.Columns(2).DisplayIndex = 1
    End Sub
End Class

[Zmcpherson]

All in all there are going to be plenty more taken then just Columns 1-4. This is just a beginning to what I am trying to accomplish, Im just trying to understand this now so I do not need to ask for help in the future.

what about something like this:

oXL = New Excel.Application
            oBook = oXL.Workbooks.Open(OpenFileDialog1.FileName)
            oSheet = oBook.Worksheets("Sheet1")
            DataGridView1.Rows.Add()
            DataGridView1.Item(0, 0).value = oSheet.Cells(1, 1).value
            oSheet = Nothing
            oBook.Close()
            oBook = Nothing
            oXL.Quit()
            oXL = Nothing

granted that only copies one cell, but with a for loop it could easily do the rest. Ultimately, is there any advantage or disadvantage doing it this way?

[kareninstructor]

All in all there are going to be plenty more taken then just Columns 1-4. This is just a beginning to what I am trying to accomplish, Im just trying to understand this now so I do not need to ask for help in the future.

what about something like this:

oXL = New Excel.Application
            oBook = oXL.Workbooks.Open(OpenFileDialog1.FileName)
            oSheet = oBook.Worksheets("Sheet1")
            DataGridView1.Rows.Add()
            DataGridView1.Item(0, 0).value = oSheet.Cells(1, 1).value
            oSheet = Nothing
            oBook.Close()
            oBook = Nothing
            oXL.Quit()
            oXL = Nothing

granted that only copies one cell, but with a for loop it could easily do the rest. Ultimately, is there any advantage or disadvantage doing it this way?

When using Office automation this works fine if you get everything right. To start off, keeping with your code example, if you run this code while Window Task Manager is open note that Excel is not listed under processes. Now run the code and EXCEL appears under processes and when the code stops it is still there. The Excel process will not go away until you close the app.

If any line of code referencing Excel has more than one dot 99 percent of the time a object is not disposed of (this is known as tunneling).

Taken from your code, I count two dots hence Excel will remain in memory until the app closes.

oSheet.Cells(1, 1).value

If there is an error while running said code and the app crashed you will have to manually close Excel under Task Manager. If the user does not notice this and keeps going again and again sooner or later memory is drained from the PC.

Any ways with that said the example below shows how to open an Excel 2007 file, search for a value, read a single cell and properly dispose of all objects used to work with Excel. See attached zip for Excel file to be placed into Bin\Debug folder.

The code within KSG_USE section will not compile as KSG_USE is not defined. I placed this section here because this is how most developers code to force objects to dispose before the app closes, going back to tunneling.

Hope this helps you to see what is involved going the automation route.

Option Strict On
Option Infer On
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Public Class Form4
    Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SearchExcelFile(IO.Path.Combine(Application.StartupPath, "MyTest.xlsx"), "BONAP")
    End Sub
    Private Sub SearchExcelFile(ByVal FileName As String, ByVal SearchFor As String)
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim rng As Excel.Range = Nothing
        Dim Result As Excel.Range = Nothing

        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)

        xlWorkSheet = CType(xlWorkBook.Sheets(1), Excel.Worksheet)

        rng = xlWorkSheet.Range("A1")
        Result = rng.Find(SearchFor, , _
                          Excel.XlFindLookIn.xlValues, _
                          Excel.XlLookAt.xlPart, _
                          Excel.XlSearchOrder.xlByRows, _
                          Excel.XlSearchDirection.xlNext, _
                          False)

        If Result IsNot Nothing Then
            Console.WriteLine("Found [{0}] on row [{1}]", SearchFor, Result.Row)
        Else
            Console.WriteLine("Did not find [{0}]", SearchFor)
        End If

        '
        ' READING a single cell value
        '
        Dim xlCells As Excel.Range = Nothing
        Dim testCell = xlWorkSheet.Range("A1")
        Console.WriteLine(testCell.Value)

        Console.WriteLine(testCell)

        xlWorkBook.Close()
        xlApp.UserControl = True

        xlApp.Quit()

        If Not testCell Is Nothing Then
            Marshal.FinalReleaseComObject(testCell)
            testCell = Nothing
        End If

        If Not Result Is Nothing Then
            Marshal.FinalReleaseComObject(Result)
            Result = Nothing
        End If

        If Not rng Is Nothing Then
            Marshal.FinalReleaseComObject(rng)
            rng = Nothing
        End If


        If Not xlWorkSheet Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        End If

        If Not xlWorkBook Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
        End If

        If Not xlWorkBooks Is Nothing Then
            Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
        End If

        If Not xlApp Is Nothing Then
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
        End If

#If KSG_USE Then
        GC.Collect()
        GC.WaitForPendingFinalizers()
        ' GC needs to be called twice in order to get the Finalizers called 
        ' - the first time in, it simply makes a list of what is to be 
        ' finalized, the second time in, it actually the finalizing. Only 
        ' then will the object do its automatic ReleaseComObject.
        GC.Collect()
        GC.WaitForPendingFinalizers()
#End If
        MsgBox("Done")
    End Sub

End Class

[Zmcpherson]

I actually know exactly what you are talking about.

On my last application I was automatizing the exportation of information to excel files and I would close the objects using this code block:

Public Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

and then I would call them using:

releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet201)

[kareninstructor]

I actually know exactly what you are talking about.

On my last application I was automatizing the exportation of information to excel files and I would close the objects using this code block:

Public Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

and then I would call them using:

releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet201)

This is forcing a garbage collection as soon as the automation function is off the stack (at which point the Runtime Callable Wrapper (RCW) objects are no longer rooted) to clean up RCWs and release COM objects.

This is why my choices for automation are a) Aspose Cells library (third party), Open XML SDK (Micorosoft for Office 2007/2010 format) and OleDb.

Apsose Cells works on any version of Excel without the need for Excel to be available or any runtimes only the Aspose Cells library, one DLL.

Open XML involves a good deal more work, I have a Code Bank posting on this which shows how much more code is needed over Office Automation or any other methods for that matter. But a good option if you want to work with the underlying xml structure of 2007/2010 formatted Office products such as Excel and Word.