Imports Mediatheek.Data
Imports Mediatheek.DataAcces
Imports Microsoft.Office.Interop


Public Class frmStatestiek
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents PictureBox1 As System.Windows.Forms.PictureBox
    Friend WithEvents picGrafiek As System.Windows.Forms.PictureBox
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(frmStatestiek))
        Me.PictureBox1 = New System.Windows.Forms.PictureBox
        Me.picGrafiek = New System.Windows.Forms.PictureBox
        Me.SuspendLayout()
        '
        'PictureBox1
        '
        Me.PictureBox1.Image = CType(resources.GetObject("PictureBox1.Image"), System.Drawing.Image)
        Me.PictureBox1.Location = New System.Drawing.Point(0, 0)
        Me.PictureBox1.Name = "PictureBox1"
        Me.PictureBox1.Size = New System.Drawing.Size(792, 70)
        Me.PictureBox1.TabIndex = 0
        Me.PictureBox1.TabStop = False
        '
        'picGrafiek
        '
        Me.picGrafiek.Location = New System.Drawing.Point(0, 80)
        Me.picGrafiek.Name = "picGrafiek"
        Me.picGrafiek.Size = New System.Drawing.Size(792, 448)
        Me.picGrafiek.TabIndex = 1
        Me.picGrafiek.TabStop = False
        '
        'frmStatestiek
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(792, 535)
        Me.Controls.Add(Me.picGrafiek)
        Me.Controls.Add(Me.PictureBox1)
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle
        Me.Icon = CType(resources.GetObject("$this.Icon"), System.Drawing.Icon)
        Me.MaximizeBox = False
        Me.Name = "frmStatestiek"
        Me.Text = "Statestiek"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private moExcellApp As Excel.Application


    Private Sub frmStatestiek_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'maken van de app, goedstellen van taalsettings
        System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
        moExcellApp = New Excel.Application
        moExcellApp.Visible = False

        'nieuwe werkboek en werkblad openen
        Dim oWerkBoek As Excel.Workbook
        oWerkBoek = moExcellApp.Workbooks.Add()

        Dim oWBlad As Excel.Worksheet

        '1 werkblad maken, rest deleten
        Dim iLaatste As Integer = oWerkBoek.Worksheets.Count
        Do While iLaatste > 1
            oWBlad = oWerkBoek.Worksheets.Item(iLaatste)
            oWBlad.Delete()
            iLaatste = oWerkBoek.Worksheets.Count
        Loop

        'werkblad naam geven
        oWBlad = oWerkBoek.Worksheets.Item(1)
        oWBlad.Name = "Ontleners per gemeente"

        'range installen
        'eerste kolom: alle gemeentes
        Dim oRange As Excel.Range
        Dim iCatRij As Integer = 1
        For Each oGemeente As clsOntlener In clsOntlenerDA.GetOntlenersPerGemeente()
            oRange = oWBlad.Cells
            oRange = oRange.Item(iCatRij, 1)
            oRange.Value = oGemeente.Gemeente
            oRange = oRange.Offset(0, 1)
            iCatRij += 1
        Next
        'tweede kolom aantal ontleners per gemeente
        Dim iAantalRij As Integer = 1
        For Each oAantal As clsOntlener In clsOntlenerDA.GetOntlenersPerGemeente()
            oRange = oWBlad.Cells
            oRange = oRange.Item(iAantalRij, 2)
            oRange.Value = oAantal.Id
            iAantalRij += 1
        Next

        'excell opslaan
        moExcellApp.DisplayAlerts = False
        oWerkBoek.SaveAs(Application.StartupPath & "\files\OntlenersPerGemeents.xls")
        moExcellApp.DisplayAlerts = True

        'grafiek opstellen
        Dim iLaatsteRij As Integer = 1
        Dim Range As Excel.Range
        Range = oWBlad.Range("A1")
        Do Until Range.Value Is Nothing
            Range = Range.Offset(1, 0)
            iLaatsteRij += 1
        Loop
        iLaatsteRij -= 1

        'vba code
        oWerkBoek.Charts.Add()
        oWerkBoek.ActiveChart.ChartType = Excel.XlChartType.xlColumnClustered
        oWerkBoek.ActiveChart.SetSourceData(Source:=oWBlad.Range("A1:B" & iLaatsteRij), PlotBy:=Excel.XlRowCol.xlColumns)
        oWerkBoek.ActiveChart.Location(Where:=Excel.XlChartLocation.xlLocationAsObject, Name:="Ontleners per Gemeente")
        With oWerkBoek.ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "Aantal ontleners per gemeente"
            .Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasTitle = True
            .Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Gemeente"
            .Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasTitle = True
            .Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "aantal"
        End With
        oWerkBoek.ActiveChart.HasLegend = False
        oWerkBoek.ActiveChart.HasDataTable = False

        'afsluiten
        Dim sPath As String = Application.StartupPath & "\images\OntlenersPerGemeente.gif"
        If Dir$(sPath) = "" Then
            oWerkBoek.ActiveChart.Export(Application.StartupPath & "\images\OntlenersPerGemeente.gif")
        End If

        oWerkBoek.Close(False)
        'gif aanmaken die we tonen in programma
        If Not picGrafiek.Image Is Nothing Then
            picGrafiek.Image.Dispose()
            picGrafiek.Image = Nothing
        End If
        picGrafiek.Image = Image.FromFile(Application.StartupPath & "\images\OntlenersPerGemeente.gif")

        moExcellApp.Workbooks.Close()

        moExcellApp.Quit()

        Process.GetProcessesByName("EXCEL")(0).Kill()
    End Sub
 
   

End Class
