Excel to DataGrid
Excel to DataGrid
Excel to DataGrid
Excel to DataGrid
Excel to DataGrid
Excel to DataGrid Excel to DataGrid Excel to DataGrid Excel to DataGrid Excel to DataGrid Excel to DataGrid Excel to DataGrid Excel to DataGrid
Excel to DataGrid Excel to DataGrid
Excel to DataGrid
Go Back  Xtreme Visual Basic Talk > > > Excel to DataGrid


Reply
 
Thread Tools Display Modes
  #1  
Old 09-17-2008, 05:03 AM
sanric sanric is offline
Newcomer
 
Join Date: Sep 2008
Posts: 1
Default Excel to DataGrid


Hi everybody,

I'm a Mac programmer totally new to this .NET world so please be gentle with me :-)
I'm writing my very first application for a customer of mine using VB.NET and I'm stuck with this: how can I open an xls file and put it on a datagrid?
I've seen it's quite complex but the lack of examples by Microsoft doesn't allow me to do all the things by myself.
Sorry for bothering you and best regards.

--
Riccardo
Reply With Quote
  #2  
Old 09-17-2008, 10:02 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi sanric,

Welcome to the forum.

I don't know about the DataGrid, unfortunately, but for the Excel Automation aspect of it, you should probably read the Automating Office Programs with VB.NET tutorial to give you a start...

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 09-17-2008, 04:54 PM
tomweigel tomweigel is offline
Newcomer
 
Join Date: Mar 2007
Posts: 9
Default

No, you don't have to automate Excel at all.

Here is an function I made. Just call the function and pass the filepath & file name as its parameter and it will return your filled dataset.

Code:
Public Function ImportExcelIntoDataset(ByVal strFile As String, Optional ByVal strWorkSheet As String = "Sheet1", Optional ByVal blnHeaderRow As Boolean = True, Optional ByVal blnDataAsText As Boolean = True) As DataSet

        Dim daExcel As New OleDb.OleDbDataAdapter
        Dim dsRecords As New DataSet
        Dim objFile As New OleDb.OleDbConnection

        Dim strConnection As String = ""
        Dim strHeader As String = "Yes"
        Dim strDataAsText As String = "Yes"

        '01) strFile
        '02) strWorkSheet - The worksheet name needs to be specified before it can be queried, Sheet1 is the default
        '03) blnHeaderRow - indicates the first row in the file contains the column name. Extended Property = HDR=Yes;
        '04) blnDataTypeText - ensures it always read's intermixed data columns as text. Extended Property = IMEX=1;

        'Format Parameters Values

        If blnHeaderRow = True Then
            strHeader = "YES"
        Else
            strHeader = "NO"
        End If

        If blnDataAsText = True Then
            strDataAsText = "1"
        Else
            strDataAsText = "0"
        End If

        'Connection String
        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
            & "; Extended Properties=""Excel 8.0;HDR=" & strHeader & ";IMEX=" & strDataAsText & """;"

        objFile.ConnectionString = strConnection

        Try
            'Open Excel Connection
            objFile.Open()

            'Check connection
            If objFile.State <> ConnectionState.Open Then Throw New System.Exception("Excel  Connection Failed.")

            'Query info from the file worksheet and put into dataset
            daExcel.SelectCommand = New OleDb.OleDbCommand("Select * From [" & strWorkSheet & "$]", objFile)
            daExcel.Fill(dsRecords)

        Catch ex As Exception
            MsgBox(ex.Message)
            'Pass the error back to the calling sub to handle
            Throw New System.Exception("Excel  Connection Failed.")

        Finally

            If Not objFile Is Nothing Then
                objFile.Close()
                objFile = Nothing
            End If

            If Not daExcel Is Nothing Then daExcel = Nothing

        End Try

        Return dsRecords

    End Function
Reply With Quote
  #4  
Old 09-22-2008, 08:54 AM
martonx martonx is offline
Centurion
 
Join Date: Jan 2007
Location: Szeged, Hungary
Posts: 129
Default

You don't need to do any programming. You should use an ODBC connection and make a Dataset (a wizard will help you).
You should bind this Dataset to a datagrid (for example you drag'n'drop the dataset to an empty form).
The VS will make you a grid, with every necessary component automatically.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Excel to DataGrid
Excel to DataGrid
Excel to DataGrid Excel to DataGrid
Excel to DataGrid
Excel to DataGrid
Excel to DataGrid Excel to DataGrid Excel to DataGrid Excel to DataGrid Excel to DataGrid Excel to DataGrid Excel to DataGrid
Excel to DataGrid
Excel to DataGrid
 
Excel to DataGrid
Excel to DataGrid
 
-->