Keskustelu: Koodit: Alkon hinnasto

neosofta [04.06.2021 11:13:07]

VisualBasic Windows Web ASP.NET (.NET Framework) projekti: AlkoGetPricesNET
Toteutettu Visual Studio 2019 Community ympäristössä (.NET Framework 4.5)
Empty, Web Forms, Not Configured for HTTPS
Lisää projektiin uusi Web Form (Default.aspx)
Poista Global.asax -tiedosto sekä App_Data ja Models -kansiot
Design: Web Formille paneeli (Panel1) ja sen sisään gridview (GridView1).

Default.aspx.vb:

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

Defaul.aspx:

<%@ 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>

mystyle.css:

.pnl1 {height: 512px; width: 1120px; scroll-behavior: smooth;}

.gdvhdr {background-color: black; color: white; top: 0px; left: 0px; width: 100%; position: relative;}

visitors.xml (serverillä: read/write):

<?xml version="1.0" standalone="yes"?>
<counter>
  <count>
    <hits>0</hits>
  </count>
</counter>

Web.config: Visual Studio generoi automaattisesti (älä tee muutoksia).

Web.Config ulkoisella palvelimella:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <system.web>
    <customErrors mode="Off" />
    <compilation debug="true" />
  </system.web>
   <system.webServer>
       <httpErrors errorMode="Detailed" />
   </system.webServer>
</configuration>

EPPlus projektin voi ladata github:sta

HUOM esim. SmarterASP.NET 60 days Trial ASP.NET hosting -palvelussa sivuston juureen (= site root) pitää luoda bin -hakemisto ja .dll -tiedostot pitää ladata sinne.
(ei koske esim. myASP.NET 60 days Trial ASP.NET hosting -palvelua)

neosofta [04.06.2021 11:13:36]

VB.NET Windows Forms Desktop App (.NET Framevork 4.5) projekti: AlkoGetPrices
(mukaan liitetty simppeli haku systeemi)

'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

EPPlus projektin voi ladata myös CodePlex arkistosta vielä tämän kuun loppuun asti.

jalski [04.06.2021 12:00:22]

Mille pimeälle keskiajalle Alkon sivusto on jämähtänyt kun hinnasto on tarjolla vaan Excel tiedostona?

neosofta [04.06.2021 12:41:26]

AlkoGetPrices desktop executable & sorsat Tarjolla vain Putkassa...Putkasta Alkoon 😊 Alkosta Putkaan 😒

Metabolix [04.06.2021 20:01:08]

Nyt on nähty Alkon katalogista jo niin monta versiota, että saa luvan riittää. Myös tämä viestien sisällön puljaaminen edestakaisin (näkisittepä historian) on ihan sekopäistä, koeta joskus saada viestit valmiiksi.

Seuraavaksi voisit koodata juhannuksen jälkeiset laskurit (AUDIT, SADD, CIWA-Ar).

neosofta [05.06.2021 14:47:40]

Metabolix kirjoitti:

Nyt on nähty Alkon katalogista jo niin monta versiota, että saa luvan riittää

Joo nyt saa riittää Alkon hinnaston virittelyt. Kaivelin täältä Putkasta vanhan VB.NET AviFile Wrapperin, joka jäi aikoinaan jotenkin unholaan. Rakentelin huvikseni sen pohjalta pienen testiviritelmän.
Projektit sorsineen löytyy täältä.

neosofta [12.06.2021 02:02:54]

Pistetään nyt vielä sekaan pyörivät tiikerit

Vastaus

Aihe on jo aika vanha, joten et voi enää vastata siihen.

Kirjautuminen

Haku

Tehtävät

Kilpailu