importing excel file to report viewer in vb.net

[Jeline_1]

hi everyone

im really new to report viewer in vb.net and i wanto import the data from an excel file to the reportviewer in vb.net can anyone help me out?

here is waht i tried so far:

Private Sub ViewReport_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        ReportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local



      Me.ReportViewer1.RefreshReport()
        End Sub

        Private Sub ReportViewer1_Load(sender As System.Object, e As System.EventArgs) Handles ReportViewer1.Load
            ReportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local



            conn = New OleDbConnection
            conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";Extended Properties=Excel 12.0;")
            'dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)
            conn.Open()
            adpt = New OleDbDataAdapter("select * from [Sheet1$]", conn)
            rs = New DataTable
            adpt.Fill(rs)
            Dim ds As DataSet = New DataSet("DataSetOne")
            ds.Tables.Add(rs)





            Me.ReportViewer1.SetDisplayMode(Microsoft.Reporting.WinForms.DisplayMode.PrintLayout)
            ReportViewer1.LocalReport.DataSources.Clear()
            ReportViewer1.LocalReport.DataSources.Add(New ReportDataSource(strExcelPath))
            ReportViewer1.LocalReport.Refresh()
        End Sub

the variable strExcelPath is a string variable that holds the location of the exel file i wan to import

any help will be greatly appriciated

thanks in advance

[J.C. SolvoTerra]

Here's two examples of what you are looking for. They certainly have clues

MSDN
Click Here

DIC
Click Here

I hope they help.

[Santanu Das]

These are not appropriate examples. They are for exporting Report Data to Exel file Format. He/She wants the oposite ones.

[J.C. SolvoTerra]

Yeah, I thought Jeline 1 might be able to figure it out from those articles...

Ok, I'll do it...

    'Imports System.Data.OleDb
    'Imports Microsoft.Reporting.WinForms

    Private Const EXCEL97 = "Provider=Microsoft.Jet.OLEDB.4.0; _
    Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"

    Private Const EXCEL07 = "Provider=Microsoft.ACE.OLEDB.12.0; _
    Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"

    Private Sub Import_To_Grid(ByVal FilePath As String, _
    ByVal Extension As String, ByVal isHDR As String)

        Dim conStr As String = ""
        Select Case Extension
            Case ".xls"
                'Excel 97-03
                conStr = EXCEL97
                Exit Select
            Case ".xlsx"
                'Excel 07
                conStr = EXCEL07
                Exit Select
        End Select
        conStr = String.Format(conStr, FilePath, isHDR)

        Dim connExcel As New OleDbConnection(conStr)
        Dim cmdExcel As New OleDbCommand()
        Dim oda As New OleDbDataAdapter()
        Dim dt As New DataTable()

        cmdExcel.Connection = connExcel

        'Get the name of First Sheet
        connExcel.Open()
        Dim dtExcelSchema As DataTable

        dtExcelSchema = _
            connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

        Dim SheetName As String = dtExcelSchema.Rows(0) _
            ("TABLE_NAME").ToString()
        connExcel.Close()

        'Read Data from First Sheet
        connExcel.Open()
        cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
        oda.SelectCommand = cmdExcel
        oda.Fill(dt)
        connExcel.Close()


        Dim RV As ReportViewer = New ReportViewer
        RV.LocalReport.DataSources.Add(New ReportDataSource)
        RV.LocalReport.DataSources.Item(0).Value = dt

    End Sub

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

        Import_To_Grid("C:\Users\Jay\Documents\Book1.xlsx", _
            ".xlsx", "{1}")

    End Sub

Sorry

The ReportViewer is now populated with the Excel Data.

If there's anything you can add to help Santanu Das, that would be awesome.

[Santanu Das]

I am truely sorry J. C., from my heart.
I did not have any intention to hurt you.
Worngly, I posted here for another question for another Forum.