Filling DataGrid Columns w/ Excel Data
Alright, I finally got this code to work after hours of toiling:
vb Code:
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:
vb Code:
DataGridView1.DataSource = myDataset.Tables(0).DefaultView
with:
vb Code:
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:
vb Code:
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.
Re: Filling DataGrid Columns w/ Excel Data
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.
Code:
DataGridView1.Columns(2).DisplayIndex = 1
Re: Filling DataGrid Columns w/ Excel Data
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
1 Attachment(s)
Re: Filling DataGrid Columns w/ Excel Data
Quote:
Originally Posted by
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
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.
Code:
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
Re: Filling DataGrid Columns w/ Excel Data
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:
vb Code:
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?
1 Attachment(s)
Re: Filling DataGrid Columns w/ Excel Data
Quote:
Originally Posted by
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:
vb Code:
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.
Code:
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.
Code:
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
Re: Filling DataGrid Columns w/ Excel Data
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:
vb Code:
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:
vb Code:
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet201)
Re: Filling DataGrid Columns w/ Excel Data
Quote:
Originally Posted by
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:
vb Code:
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:
vb Code:
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.
