Populating A DataGrid With Data From Excel SpreadSheet

[softweng]

I would like to populate a DataGrid with data from an Excel SpreadSheet. I'm not sure how to
do this. I would like the text in cells A1:K1 to be the Column Headings and the rest of the data
in the spreadsheet to fill the datagrid. I know how to do this with an Access database but have
never done it with excel. If anyone knows how to do this please let me know. Any code samples are
greatly apreciated. Thanks!!!

[Iouri]

If you know how to do it with access, I assume that you know how to write to the data grid. Here I give you a code how to read from Excel

Set a reference to the Excel Object Library in Project-References.

Dim oApp As Excel.Application
Dim oSheet As Excel.Worksheet
Dim iRow As Integer
Dim iCol As Integer
Set oApp = GetObject(, "Excel.Application")

Set oSheet = oApp.ActiveSheet
For iRow = 1 To 10
For iCol = 1 To 10
Debug.Print oSheet.Cells(iRow, iCol)'change it to enter data to grid
Next iCol
Next iRow

Set oSheet = Nothing
Set oApp = Nothing

This will loop through all cells 10 rows by 10 columns of the active worksheet.



Iouri Boutchkine
[email protected]

[softweng]

When I use a datagrid with Access I just set the datasource to a recordset or datacontrol.
I have never written data to each cell of the datagrid. Wouldn't that be slow if there is alot
of data to display? Is there any way to extract the data from an Excel spreadsheet like you do
an Access database?

[Iouri]

First of all if your objective is to see records in data grid, you can bind data grid to Excel the same way you bind it to Access.

If you use unbound grid and populate it from excell, you don't have to be afraid that it going to be slow, because excel cannot have too many records.

Third, if you still think that the huge amount of records will slow down the grid population, you can do paging - fill only one visible page of the grid at a time. When you click to go to the next or previous page you will fill only this page.

I would recommend to use msflexgrid. Create recordset from Excel and enter it to msflexgrid.

If you need further help with entering records to msflexgrid or paging, let me know.

Iouri Boutchkine
[email protected]

[softweng]

I would like to bind the datagrid or flex grid to Excel. I do not know how to do this. I am sure
there is a driver for it just like JET 4.0 for Access. How do you create a recordset with data
from an Excel sheet?

[Iouri]

dim cn as adodb.connection
dim rs as adodb.recordset
dim sql as string
dim connstr as string

connstr = "provider=microsoft.jet.oledb.4.0;data source=c:\myfile.xls;extended properties=Excel 8.0;"

set cn = new adodb.connection
cn.open connstr

sql = "select * from [Sheet1$]"
set rs = new adodb.recordset

set rs = cn.execute(sql)



Iouri Boutchkine
[email protected]

[faatimah]

Hi how do i populate a datagrid control or a list view control with records from an access database? the book i am using is not covering it, is there a site out there that can help??

[softweng]

You can use a datacontrol or code to populate the datagrid. If you use a dataconrol you can set
it up to look at a DSN or a certain database file. Once the datacontrol is set, then set the DataSource
propertry of the DataGrid equal to the name of the datacontrol.
I have pasted some code here for how to do it through code.

private Sub FillDataGrid()
Dim cndata as ADODB.Connection
Dim rsdata as ADODB.Recordset
Dim strSQL as string

on error GoTo ErrHandler

'//Define Connection And Recordset
set cndata = new ADODB.Connection
set rsdata = new ADODB.Recordset

'//Open Connection
cndata.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestDB.mdb;"

'//Define Query string
strSQL = "SELECT * FROM Table ORDER BY [Table].[date];"

'//Open Recordset
rsdata.Open strSQL, cndata, adOpenKeyset, adLockOptimistic

'//set DataGrid Datasource to The Recordset
set dgDataGrid.DataSource = rsdata

'//Close Connection And Recordset
rsdata.Close
cndata.Close

'//set Connection And Recordset to nothing to Prevent Memory Leaks
set rsdata = nothing
set cndata = nothing

Exit Sub

ErrHandler:
ProcessError ("frmMain.FillDataGrid")

End Sub

[softweng]

I ended up using the MSFlexGrid. This is how I did it and it seems to work great for me.
Thanks for your input!!!

private Sub FillGrid()
Dim ExclApp as Excel.Application
Dim WorkBook as Excel.WorkBook
Dim WorkSheet as Excel.WorkSheet
Dim TotalRows as Integer
Dim intCol as Integer
Dim intRow as Integer

on error GoTo ErrHandler

'//Make Sure A SpreadSheet is Selected
If len(strSpreadSheetName) <= 0 then Exit Sub

'//Creat Instance Of Excel
set ExclApp = CreateObject("Excel.Application")

'//Don't Show Excel Application
ExclApp.Visible = false

'//Turn Off Excel Dialog Alerts
ExclApp.DisplayAlerts = false

'//Open The WorkBook
set WorkBook = ExclApp.Workbooks.Open(strSpreadSheetName)

'//Create The WorkSheet
set WorkSheet = WorkBook.Sheets("InspResults")

'//get The Number Of Rows In The SpreadSheet
TotalRows = WorkSheet.UsedRange.Rows.Count

'//Save Data to WorkSheet
intRow = 0
for intRow = 1 to TotalRows - 1
'//Add new Row
dgExcel.AddItem ""

'//ID
dgExcel.Col = 0
dgExcel.Row = intRow
dgExcel.CellAlignment = flexAlignCenterCenter
dgExcel.CellFontBold = false
dgExcel.CellFontSize = 10
dgExcel.Text = Format$(intRow)

intCol = 0
for intCol = 1 to 8
dgExcel.Col = intCol
dgExcel.Row = intRow
dgExcel.CellAlignment = flexAlignLeftCenter
dgExcel.CellFontBold = false
dgExcel.CellFontSize = 10
dgExcel.Text = Format$(WorkSheet.Cells((intRow + 1), (intCol)).Text)
next intCol

'//Inspection Status
dgExcel.Col = 9
dgExcel.Row = intRow
dgExcel.CellAlignment = flexAlignCenterCenter
dgExcel.CellFontBold = false
dgExcel.CellFontSize = 10
dgExcel.Text = Format$(WorkSheet.Cells((intRow + 1), 9).Text)

'//date Record Was Recorded
dgExcel.Col = 10
dgExcel.Row = intRow
dgExcel.CellAlignment = flexAlignRightCenter
dgExcel.CellFontBold = false
dgExcel.CellFontSize = 10
dgExcel.Text = Format$(WorkSheet.Cells((intRow + 1), 10).Text)

'//time Record Was Recorded
dgExcel.Col = 11
dgExcel.Row = intRow
dgExcel.CellAlignment = flexAlignRightCenter
dgExcel.CellFontBold = false
dgExcel.CellFontSize = 10
dgExcel.Text = Format$(WorkSheet.Cells((intRow + 1), 11).Text)
next intRow

'//Close The Workbook
ExclApp.Workbooks.Close

'//set Objects to nothing to Prevent Memory leaks
set WorkSheet = nothing
set WorkBook = nothing
set ExclApp = nothing

Exit Sub

ErrHandler:
'//Close The Workbook
ExclApp.Workbooks.Close

'//set Objects to nothing to Prevent Memory leaks
set WorkSheet = nothing
set WorkBook = nothing
set ExclApp = nothing

ProcessError ("frmViewExcel.FillGrid")

End Sub

[Share2]

HI,
Why not download AAA EasyGrid Control from http://www.share2.com/easygrid/ ?
It meets your requirements.