How to export data to excel according to below code

Question

adhani85 -2 Newbie Poster

13 Years Ago

Imports System.Data.SqlClient
Imports System.Data.OleDb.OleDbConnection
Imports System.Data.OleDb.OleDbCommand
Imports System.Data
Partial Class Main 
Inherits System.Web.UI.Page

    Public strSQL As String
    Public strConn As String = ConfigurationSettings.AppSettings("strConn")
    Dim objConn As SqlConnection
    Dim objAdap As SqlDataAdapter
    Dim objCmd As SqlCommand
    Dim objReader As SqlDataReader

    Private Sub cbfGenerateTable()


        Try
            objConn = New SqlConnection(strConn)

            strSQL = "SELECT a.strLocation, a.strPhoto, a.strProblem, a.dtInitialize, b.strdesc as strArea, " & _
            "c.strdesc as strCompany, d.strdesc as strDept, e.strDesc as strProblemKind, " & _
            "a.strPIC, a.dtExpected, a.dtEnded, a.strStatus, a.strVIN, a.intFrequency, a.strGravity " & _
            "FROM pm_Main a, pr_area b, pr_company c, pr_dept d, pr_ProblemKind e " & _
            "WHERE a.intAreaId=b.Id AND a.intCompanyId=c.Id AND a.intDeptId=d.Id AND a.intProblemId=e.Id " & _
            "AND a.strModel='" & ddlModel.SelectedValue & "'"

            objAdap = New SqlDataAdapter(strSQL, objConn)

            Dim dtItem As New DataTable
            Dim dgGenerate As New DataGrid
            Dim dr As DataRow
            Dim i As Integer

            objConn.Open()
            objAdap.Fill(dtItem)
         
            Dim strGenerate As String

            strGenerate = "<table id=""dgItem"" border=""1"" rules=""all"" cellspacing=""0"" cellpadding=""3"">"
            strGenerate = strGenerate + "<tr bgcolor=""#0099CC"">"
            strGenerate = strGenerate + "<td align=""center"">No.</td>"
            strGenerate = strGenerate + "<td>Location</td>"
            strGenerate = strGenerate + "<td>Photo</td>"
            strGenerate = strGenerate + "<td>Problem</td>"
            strGenerate = strGenerate + "<td>Initialize Date</td>"
            strGenerate = strGenerate + "<td>Area</td>"
            strGenerate = strGenerate + "<td>Kind of Problem</td>"
            strGenerate = strGenerate + "<td>Company</td>"
            strGenerate = strGenerate + "<td>Department</td>"
            strGenerate = strGenerate + "<td>PIC Name</td>"
            strGenerate = strGenerate + "<td>Expected Date</td>"
            strGenerate = strGenerate + "<td>Ended Date</td>"
            strGenerate = strGenerate + "<td>Status</td>"
            strGenerate = strGenerate + "<td>VIN</td>"
            strGenerate = strGenerate + "<td>Frequency</td>"
            strGenerate = strGenerate + "<td>Gravity</td>"
            strGenerate = strGenerate + "</tr>"

            If dtItem.Rows.Count > 0 Then
                For Each dr In dtItem.Rows


                    strGenerate = strGenerate + "<tr>"
                    strGenerate = strGenerate + "<td align=""center""> " & i + 1 & "  </td>"
                    strGenerate = strGenerate + "<td>" & dr("strLocation") & "</td>"
                    strGenerate = strGenerate + "<td><img src=""images\upload\" & dr("strPhoto") & """ height=""184"" width=""376""></td>"
                    strGenerate = strGenerate + "<td>" & dr("strProblem") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("dtInitialize") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strArea") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strCompany") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strDept") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strProblemKind") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strPIC") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("dtExpected") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("dtEnded") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strStatus") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strVIN") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("intFrequency") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strGravity") & "</td>"
                    strGenerate = strGenerate + "</tr>"

                Next
                strGenerate = strGenerate + "</table>"



            End If

            ltrGenerate.Text = strGenerate

        Catch ex As Exception

        End Try

    End Sub

    Protected Sub ddlModel_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlModel.SelectedIndexChanged

        cbfGenerateTable()

    End Sub

    Protected Sub btnGenerate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerate.Click

            End Sub
End Class

microsoft-office vb.net

Edited 13 Years Ago by adhani85 because: n/a

5 Contributors
4 Replies
171 Views
1 Day Discussion Span
Latest Post 13 Years Ago Latest Post by thines01

Reverend Jim commented: No details, no comments, no effort. -2

All 4 Replies

ChrisPadgham 113 Posting Whiz

13 Years Ago

This appears to be generating HTML, where does Excel come into it.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

jbutardo -2 Junior Poster in Training · Answer 1 · 2012-02-01T15:24:51+00:00

Imports System.Data.SqlClient
Imports System.Data.OleDb.OleDbConnection
Imports System.Data.OleDb.OleDbCommand
Imports System.Data
Partial Class Main 
Inherits System.Web.UI.Page

    Public strSQL As String
    Public strConn As String = ConfigurationSettings.AppSettings("strConn")
    Dim objConn As SqlConnection
    Dim objAdap As SqlDataAdapter
    Dim objCmd As SqlCommand
    Dim objReader As SqlDataReader

    Private Sub cbfGenerateTable()


        Try
            objConn = New SqlConnection(strConn)

            strSQL = "SELECT a.strLocation, a.strPhoto, a.strProblem, a.dtInitialize, b.strdesc as strArea, " & _
            "c.strdesc as strCompany, d.strdesc as strDept, e.strDesc as strProblemKind, " & _
            "a.strPIC, a.dtExpected, a.dtEnded, a.strStatus, a.strVIN, a.intFrequency, a.strGravity " & _
            "FROM pm_Main a, pr_area b, pr_company c, pr_dept d, pr_ProblemKind e " & _
            "WHERE a.intAreaId=b.Id AND a.intCompanyId=c.Id AND a.intDeptId=d.Id AND a.intProblemId=e.Id " & _
            "AND a.strModel='" & ddlModel.SelectedValue & "'"

            objAdap = New SqlDataAdapter(strSQL, objConn)

            Dim dtItem As New DataTable
            Dim dgGenerate As New DataGrid
            Dim dr As DataRow
            Dim i As Integer

            objConn.Open()
            objAdap.Fill(dtItem)
         
            Dim strGenerate As String

            strGenerate = "<table id=""dgItem"" border=""1"" rules=""all"" cellspacing=""0"" cellpadding=""3"">"
            strGenerate = strGenerate + "<tr bgcolor=""#0099CC"">"
            strGenerate = strGenerate + "<td align=""center"">No.</td>"
            strGenerate = strGenerate + "<td>Location</td>"
            strGenerate = strGenerate + "<td>Photo</td>"
            strGenerate = strGenerate + "<td>Problem</td>"
            strGenerate = strGenerate + "<td>Initialize Date</td>"
            strGenerate = strGenerate + "<td>Area</td>"
            strGenerate = strGenerate + "<td>Kind of Problem</td>"
            strGenerate = strGenerate + "<td>Company</td>"
            strGenerate = strGenerate + "<td>Department</td>"
            strGenerate = strGenerate + "<td>PIC Name</td>"
            strGenerate = strGenerate + "<td>Expected Date</td>"
            strGenerate = strGenerate + "<td>Ended Date</td>"
            strGenerate = strGenerate + "<td>Status</td>"
            strGenerate = strGenerate + "<td>VIN</td>"
            strGenerate = strGenerate + "<td>Frequency</td>"
            strGenerate = strGenerate + "<td>Gravity</td>"
            strGenerate = strGenerate + "</tr>"

            If dtItem.Rows.Count > 0 Then
                For Each dr In dtItem.Rows


                    strGenerate = strGenerate + "<tr>"
                    strGenerate = strGenerate + "<td align=""center""> " & i + 1 & "  </td>"
                    strGenerate = strGenerate + "<td>" & dr("strLocation") & "</td>"
                    strGenerate = strGenerate + "<td><img src=""images\upload\" & dr("strPhoto") & """ height=""184"" width=""376""></td>"
                    strGenerate = strGenerate + "<td>" & dr("strProblem") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("dtInitialize") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strArea") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strCompany") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strDept") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strProblemKind") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strPIC") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("dtExpected") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("dtEnded") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strStatus") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strVIN") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("intFrequency") & "</td>"
                    strGenerate = strGenerate + "<td>" & dr("strGravity") & "</td>"
                    strGenerate = strGenerate + "</tr>"

                Next
                strGenerate = strGenerate + "</table>"



            End If

            ltrGenerate.Text = strGenerate

        Catch ex As Exception

        End Try

    End Sub

    Protected Sub ddlModel_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlModel.SelectedIndexChanged

        cbfGenerateTable()

    End Sub

    Protected Sub btnGenerate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerate.Click

            End Sub
End Class

Try adding order by in your sql statement, maybe it'll work..

score 0 · Answer 2 · 2012-02-02T01:45:35+00:00

As a rule it is considered prudent to provide more than just a block of uncommented code and no actual explanation or question. What is the code supposed to do? How does this differ from what the code actually does? Are you getting any errors? What does your input look like? What does your output look like? Anyone can cut and paste code. At least go to the effort of putting together a coherent request with a few details. And please read the posting guidelines.

thines01 401 Postaholic Team Colleague Featured Poster · Answer 3 · 2012-02-02T07:30:54+00:00

If treated as XML, Excel will load it.
It will probably generate a warning, however.

How to export data to excel according to below code

Recommended Answers Collapse Answers

All 4 Replies

Recommended Answers