dotNetHell.it - Forum - Codice Sorgente

ho una classe1


con questo codice

Private Sub Butexcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Butexcel.Click
        Dim cm As New OSMXP.Common

        cm.exportxls(Me.LstRic.SelectedItem.Value, Me.Lstcampi.SelectedItem.Value, Me.Calendar1.SelectedDate.ToShortDateString, _
                    Me.txtcerca.Text, Me.txtend.Text)
    End Sub


'che richiama una funzione in questa classe:

Public Class Common

    Public response As System.Web.HttpResponse

    Public Sub exportxls(ByVal list1 As String, ByVal Lstcampi As String, ByVal calendar As String, _
    ByVal txtcerca As String, ByVal txtend As String)

        'per primo listbox
        Dim tv As String = list1
        'per listbox range date
        Dim newtv As String

        'query per OrdineSud, OrdineSpA e Nrpl-ShopOrder
        Dim queryOrdsuddett As String = "select " & _
                                        "spa_order," & _
                                        "sud_order," & _
                                        "[NRPL-ShopOrder]," & _
                                        "Shipping_date, " & _
                                        "Acceptance_Date, " & _
                                        "Allocation_Date " & _
                                        "from OSM_Ord " & _
                                        "where " & _
                                        "Sud_Order= @elemento " & _
                                        "order by Shipping_Date"

        Dim queryOrdspadett As String = "select " & _
                                        "spa_order," & _
                                        "sud_order," & _
                                        "[NRPL-ShopOrder]," & _
                                        "Shipping_date, " & _
                                        "Acceptance_Date, " & _
                                        "Allocation_Date " & _
                                        " from OSM_Ord " & _
                                        "where " & _
                                        "SpA_Order= @elemento " & _
                                        "order by Shipping_Date"

        Dim queryNRpldett As String = "select " & _
                                      "spa_order," & _
                                      "sud_order," & _
                                      "[NRPL-ShopOrder]," & _
                                      "Shipping_date, " & _
                                      "Acceptance_Date, " & _
                                      "Allocation_Date " & _
                                      " from OSM_Ord " & _
                                      "where " & _
                                      "[NRPL-ShopOrder]= @elemento " & _
                                      "order by Shipping_Date"

        'esportazione per SpA Order
        If tv = "SpA_Order" Then

            Try


                response.Write("<HTML>")
                response.AddHeader("Content-Disposition", "attachment; filename=" & "Lista Ordini ")
                response.ContentType = "application/vnd.ms-excel"

                Dim s As String
                s = "<BODY>" & _
                    "<TABLE WIDTH='50%' BORDER='1' CELLSPACING='1' CELLPADDING='1'>" & _
                    "<TR>" & _
                    "  <TD><P align='left'><STRONG>SpA_Order</STRONG></P></TD>" & _
                    "  <TD><P align='left'><STRONG>Sud_Order</STRONG></P></TD>" & _
                    "  <TD><P align='left'><STRONG>NRPL_ShopOrder</STRONG></P></TD>" & _
                    "  <TD><P align='left'><STRONG>Shipping_Date</STRONG></P></TD>" & _
                    "  <TD><P align='left'><STRONG>Acceptance_Date</STRONG></P></TD>" & _
                    "  <TD><P align='left'><STRONG>Allocation_Date</STRONG></P></TD>" & _
                    "</TR>"
                response.Write(s)

                Dim ds As New DataSet


                Me.connord.Open()

                Dim com As New SqlCommand(queryOrdspadett, Me.connord)
                com.Parameters.Add("@elemento", Lstcampi)

                Dim da As New SqlDataAdapter(com)
                da.Fill(ds)



                da.Dispose()
                com.Dispose()

                Me.connord.Dispose()
                Me.connord.Close()

                Dim dr As DataRow
                For Each dr In ds.Tables(0).Rows
                    s = "<TR>" & _
                        "<TD><P align='left'>" & dr(0) & "</P></TD>" & _
                        "<TD><P align='left'>" & dr(1) & "</P></TD>" & _
                        "<TD><P align='left'>" & dr(2) & "</P></TD>" & _
                        "<TD><P align='left'>" & dr(3) & "</P></TD>" & _
                        "<TD><P align='left'>" & dr(4) & "</P></TD>" & _
                        "<TD><P align='left'>" & dr(5) & "</P></TD>" & _
                        "</TR>"
                    response.Write(s)
                Next
                ds.Dispose()
                s = "</TABLE></BODY></HTML>"
                response.Write(s)

            Catch ex As Exception

                response.Write(ex.ToString)
            End Try

            'esportazione per Sud Order
        ElseIf tv = "Sud_Order" Then


            response.Write("<HTML>")
            response.AddHeader("Content-Disposition", "attachment; filename=" & "Lista Ordini ")
            response.ContentType = "application/vnd.ms-excel"

            Dim s As String
            s = "<BODY>" & _
                "<TABLE WIDTH='50%' BORDER='1' CELLSPACING='1' CELLPADDING='1'>" & _
                "<TR>" & _
                "  <TD><P align='left'><STRONG>SpA_Order</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Sud_Order</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>NRPL_ShopOrder</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Shipping_Date</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Acceptance_Date</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Allocation_Date</STRONG></P></TD>" & _
                "</TR>"
            response.Write(s)

            Dim ds As New DataSet


            Me.connord.Open()

            Dim com As New SqlCommand(queryOrdsuddett, Me.connord)
            com.Parameters.Add("@elemento", Lstcampi)
            Dim da As New SqlDataAdapter(com)
            da.Fill(ds)

            da.Dispose()
            com.Dispose()

            Me.connord.Dispose()
            Me.connord.Close()

            Dim dr As DataRow
            For Each dr In ds.Tables(0).Rows
                s = "<TR>" & _
                    "<TD><P align='left'>" & dr(0) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(1) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(2) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(3) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(4) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(5) & "</P></TD>" & _
                    "</TR>"
                response.Write(s)
            Next
            ds.Dispose()
            s = "</TABLE></BODY></HTML>"
            response.Write(s)

            'esportazione per RPL-ShopOrder
        ElseIf tv = "NRPL-ShopOrder" Then


            response.Write("<HTML>")
            response.AddHeader("Content-Disposition", "attachment; filename=" & "Lista Ordini ")
            response.ContentType = "application/vnd.ms-excel"

            Dim s As String
            s = "<BODY>" & _
                "<TABLE WIDTH='50%' BORDER='1' CELLSPACING='1' CELLPADDING='1'>" & _
                "<TR>" & _
                "  <TD><P align='left'><STRONG>SpA_Order</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Sud_Order</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>NRPL_ShopOrder</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Shipping_Date</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Acceptance_Date</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Allocation_Date</STRONG></P></TD>" & _
                "</TR>"
            response.Write(s)

            Dim ds As New DataSet


            Me.connord.Open()

            Dim com As New SqlCommand(queryNRpldett, Me.connord)
            com.Parameters.Add("@elemento", Lstcampi)
            Dim da As New SqlDataAdapter(com)
            da.Fill(ds)

            da.Dispose()
            com.Dispose()

            Me.connord.Dispose()
            Me.connord.Close()

            Dim dr As DataRow
            For Each dr In ds.Tables(0).Rows
                s = "<TR>" & _
                    "<TD><P align='left'>" & dr(0) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(1) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(2) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(3) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(4) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(5) & "</P></TD>" & _
                    "</TR>"
                response.Write(s)
            Next
            ds.Dispose()
            s = "</TABLE></BODY></HTML>"
            response.Write(s)


            'esportazione per Shipping_Date, Acceptance_Date, Allocation_Date
        ElseIf tv = "Shipping_Date" OrElse _
               tv = "Acceptance_Date" OrElse _
               tv = "Allocation_Date" Then

            response.Write("<HTML>")
            response.AddHeader("Content-Disposition", "attachment; filename=" & "Lista Ordini " & _
                               calendar.Replace("/", "-"))
            response.ContentType = "application/vnd.ms-excel"

            Dim s As String
            s = "<BODY>" & _
                "<TABLE WIDTH='50%' BORDER='1' CELLSPACING='1' CELLPADDING='1'>" & _
                "<TR>" & _
                "  <TD><P align='left'><STRONG>SpA_Order</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Sud_Order</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>NRPL_ShopOrder</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Shipping_Date</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Acceptance_Date</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Allocation_Date</STRONG></P></TD>" & _
                "</TR>"
            response.Write(s)

            Dim ds As New DataSet

            txtcerca = calendar
            Dim start As String
            start = Year(txtcerca) & " / " & Month(txtcerca) & " / " & Day(txtcerca)
            Dim ends As String
            ends = Year(txtcerca) & "/" & Month(txtcerca) & "/" & Day(txtcerca) & " " & "23:59:59.999"

            Dim queryData As String = "select " & _
                                      "spa_order," & _
                                      "sud_order," & _
                                      "[NRPL-ShopOrder]," & _
                                      "Shipping_date, " & _
                                      "Acceptance_Date, " & _
                                      "Allocation_Date " & _
                                      " from OSM_Ord " & _
                                      "where " & _
                                      "(" & tv & " between '" & start & "'  and '" & ends & "') " & _
                                      "and (id <> 1) " & _
                                      "order by " & tv & ""

            Me.connord.Open()

            Dim com As New SqlCommand(queryData, Me.connord)

            Dim da As New SqlDataAdapter(com)
            da.Fill(ds)

            da.Dispose()
            com.Dispose()

            Me.connord.Dispose()
            Me.connord.Close()

            Dim dr As DataRow
            For Each dr In ds.Tables(0).Rows
                s = "<TR>" & _
                    "<TD><P align='left'>" & dr(0) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(1) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(2) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(3) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(4) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(5) & "</P></TD>" & _
                    "</TR>"
                response.Write(s)
            Next
            ds.Dispose()
            s = "</TABLE></BODY></HTML>"
            response.Write(s)

            'esportazione per Range_Date
        ElseIf Me.ListRange.SelectedItem.Value = "Shipping_Date" OrElse _
               Me.ListRange.SelectedItem.Value = "Acceptance_Date" OrElse _
               Me.ListRange.SelectedItem.Value = "Allocation_Date" Then
            newtv = Me.ListRange.SelectedItem.Value

            response.Write("<HTML>")
            response.AddHeader("Content-Disposition", "attachment; filename=" & "Orders list " & _
                              "from " & txtcerca.Replace("/", "-") & " to " & _
                              txtend.Replace("/", "-"))
            response.ContentType = "application/vnd.ms-excel"

            Dim s As String
            s = "<BODY>" & _
                "<TABLE WIDTH='50%' BORDER='1' CELLSPACING='1' CELLPADDING='1'>" & _
                "<TR>" & _
                "  <TD><P align='left'><STRONG>SpA_Order</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Sud_Order</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>NRPL_ShopOrder</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Shipping_Date</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Acceptance_Date</STRONG></P></TD>" & _
                "  <TD><P align='left'><STRONG>Allocation_Date</STRONG></P></TD>" & _
                "</TR>"
            response.Write(s)

            Dim ds As New DataSet


            Dim sb As New StringBuilder
            'COSTRUZIONE QUERIES PER: dateShip, dateAcc, dataAll, dateRange

            Dim start As String
            start = Year(txtcerca) & "/" & Month(txtcerca) & "/" & Day(txtcerca) & " " & "00:00:00.000"
            Dim ends As String
            ends = Year(txtend) & "/" & Month(txtend) & "/" & Day(txtend) & " " & "23:59:59.999"

            'query per data_range: Shipping_date,Acceptance_Date,Allocation_Date
            Dim sbShip As New StringBuilder
            sbShip.Append("SELECT ")
            sbShip.Append("spa_order,")
            sbShip.Append("sud_order,")
            sbShip.Append("[NRPL-ShopOrder],")
            sbShip.Append("Shipping_date,")
            sbShip.Append("Acceptance_Date,")
            sbShip.Append("Allocation_Date ")
            sbShip.Append("FROM ")
            sbShip.Append("OSM_Ord ")
            sbShip.Append("WHERE ")
            sbShip.Append("(" & newtv & " ")
            sbShip.Append("BETWEEN ")
            sbShip.Append("'" & start & "' ")
            sbShip.Append("AND ")
            sbShip.Append("'" & ends & "') ")
            sbShip.Append("AND ")
            sbShip.Append("(id <> 1) ")
            sbShip.Append("ORDER BY " & newtv & "")

            Dim queryData As String = sbShip.ToString

            Me.connord.Open()

            Dim com As New SqlCommand(queryData, Me.connord)
            'cmd.Parameters.Add("@elemento", Lstcampi)

            Dim da As New SqlDataAdapter(com)
            da.Fill(ds)

            da.Dispose()
            com.Dispose()

            Me.connord.Dispose()
            Me.connord.Close()

            Dim dr As DataRow
            For Each dr In ds.Tables(0).Rows
                s = "<TR>" & _
                    "<TD><P align='left'>" & dr(0) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(1) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(2) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(3) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(4) & "</P></TD>" & _
                    "<TD><P align='left'>" & dr(5) & "</P></TD>" & _
                    "</TR>"
                response.Write(s)
            Next
            ds.Dispose()
            s = "</TABLE></BODY></HTML>"
            response.Write(s)
        End If


    End Sub


End Class