hello, i am happy to get this download

Sep 6, 2009 at 3:29 PM
Edited Sep 6, 2009 at 3:30 PM

hi, i am newbie,

this is my pgm.

        OpenFileDialog1.InitialDirectory = "C:\"
        OpenFileDialog1.Filter = "Excel 97-2003 (*.xls)|*.xls|Excel 2007 (*.xlsx)"
        OpenFileDialog1.FileName = ""
        OpenFileDialog1.FilterIndex = 1
        Dim dlgOpenFile As New OpenFileDialog
        Dim strFileName As String

        If OpenFileDialog1.ShowDialog = DialogResult.OK Then
            strFileName = OpenFileDialog1.FileName()
        End If

        Dim stream As FileStream = File.Open(strFileName, FileMode.Open, FileAccess.Read)

        Dim excelReader As IExcelDataReader
        'Load spreadsheet file from specified format.
        Select Case OpenFileDialog1.FilterIndex
            Case 1
                '1. Reading from a binary Excel file ('97-2003 format; *.xls)
                excelReader = ExcelReaderFactory.CreateBinaryReader(stream)
                Exit Select
            Case 2
                '2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream)
                Exit Select
        End Select

        '3. DataSet - The result of each spreadsheet will be created in the result.Tables
        Dim result As DataSet = excelReader.AsDataSet()
        '...
        '4. DataSet - Create column names from first row
        excelReader.IsFirstRowAsColumnNames = True

        '5. Data Reader methods
        While excelReader.Read()
            ' excelReader.GetInt32(0)
            MsgBox(result.Tables.Item(0).ToString)
            MsgBox(excelReader.Item(10) & "  -  " & excelReader.Item(11) & "  -  " & excelReader.Item(12))


        End While

        '6. Free resources (IExcelDataReader is IDisposable)
        excelReader.Close()

 

i don't know how to read the worksheets, although i got the result from this "excelReader.Item(10)"

1.  can  i read cells(A2) directly?

2. is there any readnext rows functions just like adodb .movenext??

3. does it support sql statement?

 

thank you

Sep 8, 2009 at 2:34 PM

 

If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then

Dim stream As FileStream = File.Open(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read)

Dim excelreader As IExcelDataReader

If Microsoft.VisualBasic.Right(OpenFileDialog1.FileName, 5) = ".xlsx" Then

excelreader = Factory.CreateReader(stream, ExcelFileType.OpenXml)

Else

excelreader = Factory.CreateReader(stream, ExcelFileType.Binary)

End If

'Dim ds As DataSet = excelreader.AsDataSet

'Reads only two worksheets

'Do While excelreader.NextResult

' Do While excelreader.Read

' Dim res As String = String.Empty

' Dim objs() As Object

' Dim objcount As Int32 = excelreader.GetValues(objs)

' For i As Int32 = 0 To objcount - 1

' res &= String.Format("{0}, ", objs(i))

' Next

' Debug.Print(res)

' Loop

'Loop

Dim ds As DataSet = excelreader.AsDataSet

For Each tbl As System.Data.DataTable In ds.Tables

For Each row As DataRow In tbl.Rows

Dim res As String = String.Empty

For i As Int32 = 0 To row.ItemArray.Length - 1

res &= String.Format("{0}, ", row.ItemArray(i))

Next

Debug.Print(res)

Next

Next

End If

Sep 12, 2009 at 2:21 AM

thank you gdj,

your code provided read all data from spreadsheet, good.

Is it a method to read select rows of data and selected column of data like "SQL statement"?

I am now using the oledb connection from this excelreader. :(