[DotNetDevelopment] Re: I need to insert data read from an excel file straight into a Dataset

[Mostafa El-Barky]

Dear Slim
        You can use this snippet to fill dataset from excel sheet

Dim MyConnection As New
System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data
source='(excel file path)'; Extended Properties=Excel 8.0;")

        Try
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            ' Select the data from Sheet1 of the workbook.
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select *
from [sheet1$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "Content")
            'DtSet = New System.Data.DataSet
            MyCommand.Fill(ds)
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            DataGridView1.DataSource = ds.Tables(0)
            MyConnection.Close()
        Catch ex As Exception
            MyConnection.Close()
            MsgBox("Error in getting data from excel: " & ex.Message)
        End Try

thanks

Mostafa El-Barky

-----Original Message-----
From: dotnetdevelopment@googlegroups.com
[mailto:dotnetdevelopm...@googlegroups.com] On Behalf Of Slim
Sent: 15/Sep/2009 11:37 AM
To: DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML Web
Services,.NET Remoting
Subject: [DotNetDevelopment] I need to insert data read from an excel file
straight into a Dataset


hie everyone,

Can anyone help me out, i need to insert data i am reading from an
excel file straight into a dataset, i wish to loop through it so that
i can save data that i need from it.

my code to extract data from the excel file is:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

        Dim xl As Syncfusion.XlsIO.ExcelEngine = New
Syncfusion.XlsIO.ExcelEngine()
        Dim strFileName As String = "C:\Users\Tamuka\Documents\Visual
Studio 2005\Projects\WebSecurity\SynchFusionTest\Test.xls"

        Using xl

            Dim xlApp As Syncfusion.XlsIO.IApplication
            Dim wkbk As Syncfusion.XlsIO.IWorkbook
            Dim sheet As Syncfusion.XlsIO.IWorksheet = Nothing
            Try
                'instantiate excel application object
                xlApp = xl.Excel
                'create a new workbook with 2 worksheets
                wkbk = xl.Excel.Workbooks.Open(strFileName)
                'get a reference to both worksheets
                sheet = wkbk.Worksheets(0)
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

            For r As Integer = 0 To sheet.UsedRange.Rows.Length - 1

                Dim RangeRow As Syncfusion.XlsIO.IRange =
sheet.UsedRange.Rows(r)

                For i As Integer = 0 To RangeRow.Cells.Length - 1

                    Dim Range As Syncfusion.XlsIO.IRange =
RangeRow.Cells(i)

                    If Range.HasNumber Then
                        Debug.Write("Number: " & Range.Number & vbTab)
                    ElseIf Range.HasDateTime Then
                        'it reads date and time the same way.
                        'In our file, time has a date part which is i
think 31/dec/1950
                        'anyway, i'll just assume that any date less
than 2000 is a representation of time
                        'i dont think we'll import records from before
then at all
                        Debug.Write("DateTime: " & IIf(Range.DateTime
< CDate("1/Jan/1950"), Range.DateTime.ToString("HH:mm:ss"),
Range.DateTime.ToString("dd/MMM/yyyy")) & vbTab)
                    ElseIf Range.HasString Then
                        Debug.Write("String: " & Range.Text & vbTab)
                    Else
                        'Debug.Write("Indeterminate: " & Range.Value &
vbTab)
                    End If

                Next

                Debug.WriteLine("")

            Next

        End Using

    End Sub

Now instead of Debug.Write() i need to be inserting the data into a
dataset.

Please help out.