Filling DataGrid Columns w/ Excel Data in VB

问题:

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

回答1:

This ended up being way easier to import the data. Im posting this in case anyone else comes across this thread.

    If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
        xLApp = New Excel.Application
        xLBook = xLApp.Workbooks.Open(OpenFileDialog1.FileName)
        xLSheet = xLBook.Worksheets("Sheet1")
        For x = 1 To xLSheet.UsedRange.Rows.Count - 1
            DataGridView1.Rows.Add()
            DataGridView1.Item(0, x - 1).Value = xLSheet.Cells(1 + x, 1).value
            DataGridView1.Item(1, x - 1).Value = xLSheet.Cells(1 + x, xLSheet.UsedRange.Columns.Count).value
        Next
    End If

Dont even bother with:

    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)

回答2:

Think of it roughly as follows:

• Excel Work book = Database

• Excel Work sheet = Table

• Each Excel column = Table column

• Each Excel row = Table row

• Excel cell = a particular column value in a particular row

If your Excel has column headers, those are your field names. Now change your SQL query to select the columns you want and bind as usual.