-2

How to export data to excel according to below code

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

5

Contributors

4

Replies

19 Hours

Discussion Span

1 Year Ago

Last Updated

6

Views

adhani85

Newbie Poster

1 post since Nov 2011

Reputation Points: 8

Solved Threads: 0

Skill Endorsements: 0

1 Year Ago

0

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..

jbutardo

Junior Poster in Training

74 posts since Jan 2012

Reputation Points: 8

Solved Threads: 2

Skill Endorsements: 0

1 Year Ago

0

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.

Reverend Jim

Illigitimae non carborundum

Moderator

3,743 posts since Aug 2010

Reputation Points: 585

Solved Threads: 470

Skill Endorsements: 33

1 Year Ago

0

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

ChrisPadgham

Posting Pro in Training

451 posts since Sep 2009

Reputation Points: 127

Solved Threads: 88

Skill Endorsements: 5

1 Year Ago

0

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

thines01

Postaholic

Team Colleague

2,433 posts since Oct 2009

Reputation Points: 447

Solved Threads: 408

Skill Endorsements: 7

How to export data to excel according to below code

This article has been dead for over three months: Start a new discussion instead