Keskustelu: Koodit: Alkon hinnasto

Imports System
Imports System.IO
Imports System.Data
Imports System.Drawing
Imports OfficeOpenXml 'referenssi EPPlus.dll
Imports System.Net
Imports System.Globalization
Imports System.Threading

Public Class _Default
    Inherits System.Web.UI.Page

    Public Shared slen As Long

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not Page.IsPostBack Then

            Dim ds As DataSet = New DataSet
            ds.ReadXml(Server.MapPath("visitors.xml"))
            Dim hits As Integer = Int32.Parse(ds.Tables(0).Rows(0)("hits").ToString())
            hits += 1
            ds.Tables(0).Rows(0)("hits") = hits.ToString()
            ds.WriteXml(Server.MapPath("visitors.xml"))
            ds.Tables.Clear()
            ds = Nothing

            Dim fi As CultureInfo = New CultureInfo("fi-FI")
            Thread.CurrentThread.CurrentCulture = fi
            Dim URL As String = "https://www.alko.fi/INTERSHOP/static/WFS/Alko-OnlineShop-Site/-/Alko-OnlineShop/fi_FI/Alkon%20Hinnasto%20Tekstitiedostona/alkon-hinnasto-tekstitiedostona.xlsx"
            Dim package As New ExcelPackage()
            Dim mystream As Stream = GetTheShit(URL)

            Dim bytes() As Byte
            Using reader As BinaryReader = New BinaryReader(mystream)
                bytes = reader.ReadBytes(slen)
            End Using

            Dim memstream As New MemoryStream
            memstream.Write(bytes, 0, bytes.Length - 1)

            package.Load(memstream)
            Dim sheet As ExcelWorksheet = package.Workbook.Worksheets(1)
            Dim dt As New DataTable
            Dim headers As String = String.Empty

            For i As Integer = 4 To 4

                For j As Integer = 1 To 10
                    Dim col As New DataColumn
                    Select Case j
                        Case 1 To 6
                            col.ColumnName = "Col" & j.ToString
                            col.DataType = Type.GetType("System.String")
                            col.Caption = sheet.Cells(i, j).Text
                            headers += col.Caption & ","
                            dt.Columns.Add(col)
                        Case 9, 10
                            col.ColumnName = "Col" & (j - 2).ToString
                            col.DataType = Type.GetType("System.String")
                            col.Caption = sheet.Cells(i, j).Text
                            headers += col.Caption & ","
                            dt.Columns.Add(col)
                    End Select

                    col.Dispose()
                Next j

            Next i


            headers = headers.Substring(0, headers.Length - 1)
            Dim harray() As String = headers.Split(",")
            headers = String.Empty

            For i As Integer = 5 To sheet.Dimension.End.Row

                Dim dr As DataRow = dt.NewRow

                For j As Integer = 1 To 10
                    Select Case j
                        Case 1 To 4
                            dr("Col" & j.ToString) = Replace(sheet.Cells(i, j).Text, " ", " ")  'välilyönnin tilalle Alt + 255
                        Case 5 To 6
                            dr("Col" & j.ToString) = CType(Val(sheet.Cells(i, j).Text), Decimal).ToString("C")
                        Case 9, 10
                            dr("Col" & (j - 2).ToString) = Replace(sheet.Cells(i, j).Text, " ", " ") 'välilyönnin tilalle Alt + 255
                    End Select
                Next j

                dt.Rows.Add(dr)

            Next i

            For i As Integer = 0 To dt.Columns.Count - 1
                dt.Columns(i).ColumnName = harray(i)
            Next
            Erase harray
            GridView1.AlternatingRowStyle.BackColor = Color.AliceBlue
            GridView1.DataSource = dt
            GridView1.DataBind()
            sheet.Dispose()
            package.Dispose()

        End If

    End Sub

    Public Function GetTheShit(strURL As String) As Stream

        ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12
        Dim Req As HttpWebRequest = CType(WebRequest.Create(strURL), HttpWebRequest)
        Req.ContentType = "application/octet-stream"
        Req.Method = "GET"
        Req.KeepAlive = False
        Dim Resp As HttpWebResponse = CType(Req.GetResponse(), HttpWebResponse)

        If (Resp.StatusCode = HttpStatusCode.OK) Then
            slen = Resp.ContentLength
            Return CType(Resp.GetResponseStream, System.IO.Stream)
            Resp.Close() : Resp.Dispose() : Req = Nothing
            Exit Function
        End If

        Resp.Dispose()
        Return Nothing

    End Function

End Class

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="AlkoGetPricesNET._Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <meta http-equiv="x-ua-compatible" content="ie=edge, chrome=1" />
    <meta charset="utf-8" />
    <title></title>
    <link rel="stylesheet" href="mystyle.css"/>
    <link rel="shortcut icon" href="favicon.ico"/>
</head>
<body>
    <form id="form1" runat="server">
        <asp:Panel ID="Panel1" runat="server" CssClass="pnl1" ScrollBars="Both">
            <asp:GridView ID="GridView1" runat="server" Font-Size="Smaller" Height="16px" Width="1100px" CssClass="dgv1">
                <HeaderStyle CssClass="gdvhdr" />
        </asp:GridView>
        </asp:Panel>
    </form>
</body>
</html>

'Form1 ohjausobjektit:
'1 DataGridView (DataGridView1)
'1 ComboBox (ComboBox1)
'1 GroupBox (GroupBox1, Text: Anna hakusana)
'GroupBoxin sisälle: TextBox (TextBox1) ja Button (Button1, Text: Etsi)
'1 Button (Button2, Text: Palaa listaan)

Imports System.IO
Imports MSXML2 'Com referessi MicrosoftXML v6.0
Imports OfficeOpenXml 'referessi EPPlus.dll
Imports System.Data

Public Class Form1

    Public Shared dt As DataTable = Nothing
    Public Shared tempdt As DataTable = Nothing
    Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown

        GroupBox1.Visible = False
        Button2.Visible = False
        ComboBox1.Visible = False
        DataGridView1.ScrollBars = ScrollBars.Both

        Dim URL As String = "https://www.alko.fi/INTERSHOP/static/WFS/Alko-OnlineShop-Site/-/Alko-OnlineShop/fi_FI/Alkon%20Hinnasto%20Tekstitiedostona/alkon-hinnasto-tekstitiedostona.xlsx"
        Dim oHTTP As ServerXMLHTTP60 = New ServerXMLHTTP60
        oHTTP.open("GET", URL, False)
        oHTTP.setRequestHeader("Content-Type", "application/octet-stream")
        oHTTP.send()
        Dim bytes() As Byte = CType(oHTTP.responseBody, Byte())
        Dim oStream As New MemoryStream
        oStream.Write(bytes, bytes.GetLowerBound(0), bytes.GetUpperBound(0))
        Dim package As New ExcelPackage()
        package.Load(oStream)
        oStream.Close()
        oStream = Nothing
        oHTTP = Nothing
        dt = New DataTable

        Dim sheet As ExcelWorksheet = package.Workbook.Worksheets(1)
        ComboBox1.Items.Add("- ETSI -")

        For i As Integer = 4 To 4

            For j As Integer = 1 To 10
                Dim col As New DataColumn
                Select Case j
                    Case 1 To 6
                        col.ColumnName = "Col" & j.ToString
                        col.DataType = Type.GetType("System.String")
                        col.Caption = sheet.Cells(i, j).Text
                        If j = 2 Or j = 3 Then
                            ComboBox1.Items.Add(col.Caption)
                        End If

                        dt.Columns.Add(col)
                    Case 9, 10
                        col.ColumnName = "Col" & (j - 2).ToString
                        col.DataType = Type.GetType("System.String")
                        col.Caption = sheet.Cells(i, j).Text
                        If j = 9 Then
                            ComboBox1.Items.Add(col.Caption)
                        End If
                        dt.Columns.Add(col)
                End Select
                col = Nothing
            Next j

        Next i

        ComboBox1.SelectedIndex = 0

        For i As Integer = 5 To sheet.Dimension.End.Row
            Dim dr As DataRow = dt.NewRow
            For j As Integer = 1 To 10
                Select Case j
                    Case 1 To 4
                        dr("Col" & j.ToString) = sheet.Cells(i, j).Text
                    Case 5 To 6
                        dr("Col" & j.ToString) = CType(Val(sheet.Cells(i, j).Text), Decimal).ToString("C")
                    Case 9, 10
                        dr("Col" & (j - 2).ToString) = sheet.Cells(i, j).Text
                End Select
            Next j
            dt.Rows.Add(dr)
            dr = Nothing
        Next i

        sheet.Dispose() : sheet = Nothing
        package.Dispose() : package = Nothing
        DataGridView1.RowHeadersVisible = False
        DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.AliceBlue
        DataGridView1.DataSource = dt
        DgvInit(DataGridView1, dt)
        ComboBox1.Visible = True

    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged

        If ComboBox1.SelectedIndex > 0 Then
            GroupBox1.Visible = True
        Else
            TextBox1.Clear()
            GroupBox1.Visible = False
        End If

    End Sub

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

        If TextBox1.Text <> "" And ComboBox1.SelectedIndex > 0 Then

            If tempdt IsNot Nothing Then
                tempdt.Clear()
            Else
                tempdt = New DataTable
            End If

            For i As Integer = 0 To dt.Columns.Count - 1
                Dim col As New DataColumn
                col.ColumnName = "Col" & i.ToString
                col.DataType = Type.GetType("System.String")
                col.Caption = dt.Columns(i).Caption
                tempdt.Columns.Add(col)
            Next

            For i As Integer = 0 To dt.Rows.Count - 1
                For j As Integer = 0 To dt.Columns.Count - 1
                    If dt.Columns(j).Caption = ComboBox1.SelectedItem Then
                        If dt.Rows(i)(j) = TextBox1.Text Then
                            Dim temprow As DataRow = tempdt.NewRow
                            For k As Integer = 0 To dt.Columns.Count - 1
                                temprow("Col" & k.ToString) = dt.Rows(i)(k)
                            Next k
                            tempdt.Rows.Add(temprow)
                        End If
                    End If
                Next j
            Next i
        End If

        DataGridView1.DataSource = tempdt
        DgvInit(DataGridView1, tempdt)
        ComboBox1.SelectedIndex = 0
        ComboBox1.Visible = False
        Button2.Visible = True

    End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        DataGridView1.DataSource = dt
        DgvInit(DataGridView1, dt)
        tempdt.Clear()
        tempdt = Nothing
        ComboBox1.Visible = True
        GroupBox1.Visible = True
        Button2.Visible = False

    End Sub

    Public Sub DgvInit(ByVal ctlr As DataGridView, ByVal dtbl As DataTable)
        For i As Integer = 0 To ctlr.Columns.Count - 1
            ctlr.Columns(i).HeaderText = dtbl.Columns(i).Caption
            ctlr.Columns(i).SortMode = DataGridViewColumnSortMode.NotSortable
            ctlr.Columns(i).ReadOnly = True
        Next
    End Sub

End Class