1,105,633 Community Members

How to export data to excel according to below code

Member Avatar
adhani85
Newbie Poster
1 post since Nov 2011
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
-2
 
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
Member Avatar
jbutardo
Junior Poster in Training
74 posts since Jan 2012
Reputation Points: -2 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
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..

Member Avatar
Reverend Jim
Noli mentula
5,470 posts since Aug 2010
Reputation Points: 770 [?]
Q&As Helped to Solve: 654 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
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.

Member Avatar
ChrisPadgham
Posting Pro in Training
450 posts since Sep 2009
Reputation Points: 113 [?]
Q&As Helped to Solve: 90 [?]
Skill Endorsements: 8 [?]
 
0
 

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

Member Avatar
thines01
Postaholic
2,420 posts since Oct 2009
Reputation Points: 389 [?]
Q&As Helped to Solve: 413 [?]
Skill Endorsements: 10 [?]
Team Colleague
Featured
 
0
 

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

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: