Imports FarPoint.Win
Imports FarPoint.Win.Spread
Imports System
Imports System.Drawing
Imports System.Windows.Forms
Imports Spread.Common.Features.Spread.Common.Features.Sample

Namespace Features
    Partial Public Class ParetoSparkline
        Inherits BaseSampleControl

        Public Sub New()
            Me.InitializeComponent()
            Name = "Pareto"
            InitControls()
            Me.InitHorizontalSparkline(Me._fpSpread.Sheets(0))
            Me.IntiVerticalSparkline(Me._fpSpread.Sheets(1))
        End Sub

#Region "Private Methods"
        Private Sub IntiVerticalSparkline(ByVal sheetView As SheetView)
            Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = sheetView.AsWorksheet()
            worksheet.Name = "Vertical"

            worksheet.SetValue(1, 0, New Object(,) {
            {"Reason", "Traffic", "Child care", "Public transportation", "Weather", "Overslept", "Emergency"},
            {"Cases", 20, 15, 13, 5, 4, 1},
            {"Color", "#CCCCCC", "#82BC00", "#F7A711", "#00C2D6", "#FFE7BA", "#000000"},
            {"Bar Size", 0.1, 0.3, 0.5, 0.7, 0.8, 1},
            {"Diagram", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing}})

            worksheet.Cells("A9:I9").Merge()
            worksheet.Cells("A9").Value = "*Result: By the sparkline above can draw a conclusion that the reasons for 80% of the employees be late are ""traffic"", ""child care"" and ""public transportation""."

            For index As Integer = 1 To 7 - 1
                worksheet.Cells($"{ChrW((index + 65))}6").Formula2 = $"PARETOSPARKLINE($B$3:$G$3,COLUMN()-1,$B$4:$G$4," &
                            $"{Me._txtTarget1.Text}, {Me._txtTarget2.Text}, " &
                            $"{Me._cmbHighlightItem.SelectedIndex}, " &
                            $"{Me._cmbLabelType.SelectedIndex}, true, " &
                            $"""{Me._cmbTarget1Color.SelectedItem}"", " &
                            $"""{Me._cmbTarget2Color.SelectedItem}"",$B$4:$G$4,$B$5:$G$5)"
            Next

            For i As Integer = 0 To 7 - 1
                worksheet.Columns($"{ChrW((i + 65))}").ColumnWidth = 120
            Next

            worksheet.Columns("D").ColumnWidth = 150
            worksheet.Rows("6").RowHeight = 160
            worksheet.Rows("1").RowHeight = 35
            worksheet.Cells("A1:G1").Merge()
            worksheet.Cells("A1").Value = "Late Arrivals by Reported Cause"
            worksheet.Cells("A1").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("A1").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 10,
                .Bold = True
            })

            sheetView.Cells("A1").BackColor = Color.Gray
            sheetView.Cells("A1").ForeColor = Color.White
            worksheet.Cells("A2:G5").HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Left
            worksheet.Cells("A2:A6").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 10,
                .Bold = True
            })

            worksheet.Cells("A2:A6").Borders.ApplyBorder(New GrapeCity.Spreadsheet.Border() With {
                .Right = New GrapeCity.Spreadsheet.BorderLine(GrapeCity.Spreadsheet.BorderLineStyle.Thin, GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Black))
            })
        End Sub

        Private Sub InitHorizontalSparkline(ByVal sheetView As SheetView)
            Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = sheetView.AsWorksheet()
            worksheet.Name = "Horizontal"

            worksheet.SetValue(1, 0, New Object(,) {
            {"Reason", "Cases", "Color", "Bar Size", "Diagram"},
            {"Traffic", 20, "#CCCCCC", 0.1, Nothing},
            {"Child care", 15, "#82BC00", 0.3, Nothing},
            {"Public transportation", 13, "#F7A711", 0.5, Nothing},
            {"Weather", 6, "#00C2D6", 0.7, Nothing},
            {"Overslept", 4, "#FFE7BA", 0.8, Nothing},
            {"Emergency", 1, "#000000", 1, Nothing}})

            worksheet.Cells("A11:I11").Merge()
            worksheet.Cells("A11").Value = "*Result: By the sparkline above can draw a conclusion that the reasons for 80% of the employees be late are ""traffic"", ""child care"" and ""public transportation""."

            For index As Integer = 2 To 8 - 1
                worksheet.Cells($"E{index + 1}").Formula2 = $"PARETOSPARKLINE($B$3:$B$8,ROW()-2,$C$3:$C$8," &
                                $"{Me._txtTarget1.Text}," & $"{Me._txtTarget2.Text}, " &
                                $"{Me._cmbHighlightItem.SelectedIndex}, " &
                                $"{Me._cmbLabelType.SelectedIndex}, false, " &
                                $"""{Me._cmbTarget1Color.SelectedItem}"", " &
                                $"""{Me._cmbTarget2Color.SelectedItem}"",$C$3:$C$8,$D$3:$D$8)"
            Next

            For i As Integer = 1 To 4 - 1
                worksheet.Columns($"{ChrW((i + 65))}").ColumnWidth = 80
            Next

            worksheet.Columns("E").ColumnWidth = 340
            worksheet.Columns("A").ColumnWidth = 140
            worksheet.Rows("1").RowHeight = 35
            worksheet.Cells("A1:E1").Merge()
            worksheet.Cells("A1").Value = "Late Arrivals by Reported Cause"
            worksheet.Cells("A1").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("A1").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 10,
                .Bold = True
            })

            sheetView.Cells("A1").BackColor = Color.Gray
            sheetView.Cells("A1").ForeColor = Color.White
            worksheet.Cells("A2:D8").HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Left
            worksheet.Cells("A2:E2").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 10,
                .Bold = True
            })

            worksheet.Cells("A2:E2").Borders.ApplyBorder(New GrapeCity.Spreadsheet.Border() With {
                .Bottom = New GrapeCity.Spreadsheet.BorderLine(GrapeCity.Spreadsheet.BorderLineStyle.Thin, GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Black))
            })
        End Sub

        Private Sub ApplyChanges()
            Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = _fpSpread.ActiveSheet.AsWorksheet()

            If _fpSpread.ActiveSheetIndex = 0 Then
                Dim formula As String = $"PARETOSPARKLINE($B$3:$B$8,ROW()-2,$C$3:$C$8," &
                            $"{Me._txtTarget1.Text}," & $"{Me._txtTarget2.Text}, " &
                            $"{Me._cmbHighlightItem.SelectedIndex}, " &
                            $"{Me._cmbLabelType.SelectedIndex}, false, " &
                            $"""{Me._cmbTarget1Color.SelectedItem}"", " &
                            $"""{Me._cmbTarget2Color.SelectedItem}"",$C$3:$C$8,$D$3:$D$8)"

                For i As Integer = 2 To 7
                    worksheet.Cells($"E{i + 1}").Formula2 = formula
                Next
            Else
                Dim formulaVertical As String = $"PARETOSPARKLINE($B$3:$G$3,COLUMN()-1,$B$4:$G$4," &
                            $"{Me._txtTarget1.Text}, {Me._txtTarget2.Text}, " &
                            $"{Me._cmbHighlightItem.SelectedIndex}, " &
                            $"{Me._cmbLabelType.SelectedIndex}, true, " &
                            $"""{Me._cmbTarget1Color.SelectedItem}"", " &
                            $"""{Me._cmbTarget2Color.SelectedItem}"",$B$4:$G$4,$B$5:$G$5)"

                For index As Integer = 1 To 7 - 1
                    worksheet.Cells($"{ChrW((index + 65))}6").Formula2 = formulaVertical
                Next
            End If
        End Sub


        Private Sub InitControls()
            Me._cmbLabelType.DataSource = New Object() {"None", "Cumulated Percentage", "Single Percentage"}
            Me._cmbHighlightItem.DataSource = New String() {"None", "1st", "2nd", "3rd", "4th", "5th", "6th"}

            Me._txtTarget1.Text = "0.5"
            Me._txtTarget2.Text = "0.8"

            Me._cmbTarget1Color.DataSource = New String() {"Gray", "Green", "Black", "Orange"}
            Me._cmbTarget2Color.DataSource = New String() {"Gray", "Green", "Black", "Orange"}

            Me._cmbLabelType.DropDownStyle = ComboBoxStyle.DropDownList
            Me._cmbHighlightItem.DropDownStyle = ComboBoxStyle.DropDownList
            Me._cmbTarget1Color.DropDownStyle = ComboBoxStyle.DropDownList
            Me._cmbTarget2Color.DropDownStyle = ComboBoxStyle.DropDownList

            AddHandler Me._txtTarget1.LostFocus, AddressOf Me.OnTextBoxLostFocus
            AddHandler Me._txtTarget2.LostFocus, AddressOf Me.OnTextBoxLostFocus
            AddHandler Me._cmbLabelType.SelectedIndexChanged, AddressOf Me.OnComboBoxSelectionChanged
            AddHandler Me._cmbHighlightItem.SelectedIndexChanged, AddressOf Me.OnComboBoxSelectionChanged
            AddHandler Me._cmbTarget1Color.SelectedIndexChanged, AddressOf Me.OnComboBoxSelectionChanged
            AddHandler Me._cmbTarget2Color.SelectedIndexChanged, AddressOf Me.OnComboBoxSelectionChanged

            Me._cmbLabelType.SelectedIndex = 2
            Me._cmbHighlightItem.SelectedIndex = 0
            Me._cmbTarget1Color.SelectedIndex = 0
            Me._cmbTarget2Color.SelectedIndex = 3
        End Sub

        Private Sub OnComboBoxSelectionChanged(ByVal sender As Object, ByVal e As EventArgs)
            ApplyChanges()
        End Sub

        Private Sub OnTextBoxLostFocus(ByVal sender As Object, ByVal e As EventArgs)
            Dim __ As Double = Nothing

            If Double.TryParse(TryCast(sender, TextBox).Text, __) Then
                ApplyChanges()
            End If
        End Sub
#End Region
    End Class
End Namespace


'
'ParetoSparkline.Designer.vb
'


Namespace Features
    Partial Class ParetoSparkline
        ''' <summary> 
        ''' Required designer variable.
        ''' </summary>
        Private components As ComponentModel.IContainer = Nothing

        ''' <summary> 
        ''' Clean up any resources being used.
        ''' </summary>
        ''' <paramname="disposing">true if managed resources should be disposed; otherwise, false.</param>
        Protected Overloads Sub Dispose(ByVal disposing As Boolean)
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If

            MyBase.Dispose(disposing)
        End Sub

#Region "Component Designer generated code"

        ''' <summary> 
        ''' Required method for Designer support - do not modify 
        ''' the contents of this method with the code editor.
        ''' </summary>
        Private Sub InitializeComponent()
            Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(ParetoSparkline))
            Me.groupBox1 = New System.Windows.Forms.GroupBox()
            Me._txtTarget2 = New System.Windows.Forms.TextBox()
            Me._txtTarget1 = New System.Windows.Forms.TextBox()
            Me._cmbTarget2Color = New System.Windows.Forms.ComboBox()
            Me._cmbTarget1Color = New System.Windows.Forms.ComboBox()
            Me._cmbHighlightItem = New System.Windows.Forms.ComboBox()
            Me._cmbLabelType = New System.Windows.Forms.ComboBox()
            Me.label6 = New System.Windows.Forms.Label()
            Me.label5 = New System.Windows.Forms.Label()
            Me.label4 = New System.Windows.Forms.Label()
            Me.label3 = New System.Windows.Forms.Label()
            Me.label2 = New System.Windows.Forms.Label()
            Me.label1 = New System.Windows.Forms.Label()
            Me._fpSpread = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None, CType(resources.GetObject("pnlMain.Controls"), Object))
            Me._fpSpread_Sheet1 = Me._fpSpread.GetSheet(0)
            Me._fpSpread_Sheet2 = Me._fpSpread.GetSheet(1)
            Me.pnlMain.SuspendLayout()
            Me.pnlControls.SuspendLayout()
            Me.groupBox1.SuspendLayout()
            CType(Me._fpSpread, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.SuspendLayout()
            '
            'pnlMain
            '
            Me.pnlMain.Controls.Add(Me._fpSpread)
            '
            'pnlControls
            '
            Me.pnlControls.Controls.Add(Me.groupBox1)
            '
            'groupBox1
            '
            Me.groupBox1.Controls.Add(Me._txtTarget2)
            Me.groupBox1.Controls.Add(Me._txtTarget1)
            Me.groupBox1.Controls.Add(Me._cmbTarget2Color)
            Me.groupBox1.Controls.Add(Me._cmbTarget1Color)
            Me.groupBox1.Controls.Add(Me._cmbHighlightItem)
            Me.groupBox1.Controls.Add(Me._cmbLabelType)
            Me.groupBox1.Controls.Add(Me.label6)
            Me.groupBox1.Controls.Add(Me.label5)
            Me.groupBox1.Controls.Add(Me.label4)
            Me.groupBox1.Controls.Add(Me.label3)
            Me.groupBox1.Controls.Add(Me.label2)
            Me.groupBox1.Controls.Add(Me.label1)
            Me.groupBox1.Dock = System.Windows.Forms.DockStyle.Top
            Me.groupBox1.Location = New System.Drawing.Point(0, 0)
            Me.groupBox1.Name = "groupBox1"
            Me.groupBox1.Size = New System.Drawing.Size(157, 271)
            Me.groupBox1.TabIndex = 0
            Me.groupBox1.TabStop = False
            Me.groupBox1.Text = "Sparkline Settings:"
            '
            '_txtTarget2
            '
            Me._txtTarget2.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) _
            Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
            Me._txtTarget2.Location = New System.Drawing.Point(92, 150)
            Me._txtTarget2.Name = "_txtTarget2"
            Me._txtTarget2.Size = New System.Drawing.Size(62, 20)
            Me._txtTarget2.TabIndex = 12
            '
            '_txtTarget1
            '
            Me._txtTarget1.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) _
            Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
            Me._txtTarget1.Location = New System.Drawing.Point(92, 107)
            Me._txtTarget1.Name = "_txtTarget1"
            Me._txtTarget1.Size = New System.Drawing.Size(62, 20)
            Me._txtTarget1.TabIndex = 11
            '
            '_cmbTarget2Color
            '
            Me._cmbTarget2Color.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) _
            Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
            Me._cmbTarget2Color.FormattingEnabled = True
            Me._cmbTarget2Color.Location = New System.Drawing.Point(92, 232)
            Me._cmbTarget2Color.Name = "_cmbTarget2Color"
            Me._cmbTarget2Color.Size = New System.Drawing.Size(62, 21)
            Me._cmbTarget2Color.TabIndex = 10
            '
            '_cmbTarget1Color
            '
            Me._cmbTarget1Color.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) _
            Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
            Me._cmbTarget1Color.FormattingEnabled = True
            Me._cmbTarget1Color.Location = New System.Drawing.Point(92, 190)
            Me._cmbTarget1Color.Name = "_cmbTarget1Color"
            Me._cmbTarget1Color.Size = New System.Drawing.Size(62, 21)
            Me._cmbTarget1Color.TabIndex = 9
            '
            '_cmbHighlightItem
            '
            Me._cmbHighlightItem.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) _
            Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
            Me._cmbHighlightItem.FormattingEnabled = True
            Me._cmbHighlightItem.Location = New System.Drawing.Point(92, 65)
            Me._cmbHighlightItem.Name = "_cmbHighlightItem"
            Me._cmbHighlightItem.Size = New System.Drawing.Size(62, 21)
            Me._cmbHighlightItem.TabIndex = 7
            '
            '_cmbLabelType
            '
            Me._cmbLabelType.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) _
            Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
            Me._cmbLabelType.FormattingEnabled = True
            Me._cmbLabelType.Location = New System.Drawing.Point(92, 25)
            Me._cmbLabelType.Name = "_cmbLabelType"
            Me._cmbLabelType.Size = New System.Drawing.Size(62, 21)
            Me._cmbLabelType.TabIndex = 6
            '
            'label6
            '
            Me.label6.AutoSize = True
            Me.label6.Location = New System.Drawing.Point(5, 235)
            Me.label6.Name = "label6"
            Me.label6.Size = New System.Drawing.Size(74, 13)
            Me.label6.TabIndex = 5
            Me.label6.Text = "Target2 Color:"
            '
            'label5
            '
            Me.label5.AutoSize = True
            Me.label5.Location = New System.Drawing.Point(5, 193)
            Me.label5.Name = "label5"
            Me.label5.Size = New System.Drawing.Size(74, 13)
            Me.label5.TabIndex = 4
            Me.label5.Text = "Target1 Color:"
            '
            'label4
            '
            Me.label4.AutoSize = True
            Me.label4.Location = New System.Drawing.Point(6, 153)
            Me.label4.Name = "label4"
            Me.label4.Size = New System.Drawing.Size(47, 13)
            Me.label4.TabIndex = 3
            Me.label4.Text = "Target2:"
            '
            'label3
            '
            Me.label3.AutoSize = True
            Me.label3.Location = New System.Drawing.Point(6, 110)
            Me.label3.Name = "label3"
            Me.label3.Size = New System.Drawing.Size(47, 13)
            Me.label3.TabIndex = 2
            Me.label3.Text = "Target1:"
            '
            'label2
            '
            Me.label2.AutoSize = True
            Me.label2.Location = New System.Drawing.Point(6, 68)
            Me.label2.Name = "label2"
            Me.label2.Size = New System.Drawing.Size(74, 13)
            Me.label2.TabIndex = 1
            Me.label2.Text = "Highlight Item:"
            '
            'label1
            '
            Me.label1.AutoSize = True
            Me.label1.Location = New System.Drawing.Point(6, 28)
            Me.label1.Name = "label1"
            Me.label1.Size = New System.Drawing.Size(63, 13)
            Me.label1.TabIndex = 0
            Me.label1.Text = "Label Type:"
            '
            '_fpSpread
            '
            Me._fpSpread.Dock = System.Windows.Forms.DockStyle.Fill
            Me._fpSpread.Font = New System.Drawing.Font("Calibri", 11.0!)
            Me._fpSpread.Location = New System.Drawing.Point(0, 0)
            Me._fpSpread.Name = "_fpSpread"
            Me._fpSpread.Size = New System.Drawing.Size(477, 487)
            Me._fpSpread.TabIndex = 0
            '
            'ParetoSparkline
            '
            Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.Name = "ParetoSparkline"
            Me.pnlMain.ResumeLayout(False)
            Me.pnlControls.ResumeLayout(False)
            Me.groupBox1.ResumeLayout(False)
            Me.groupBox1.PerformLayout()
            CType(Me._fpSpread, System.ComponentModel.ISupportInitialize).EndInit()
            Me.ResumeLayout(False)

        End Sub

#End Region
        Private groupBox1 As System.Windows.Forms.GroupBox
        Private label6 As System.Windows.Forms.Label
        Private label5 As System.Windows.Forms.Label
        Private label4 As System.Windows.Forms.Label
        Private label3 As System.Windows.Forms.Label
        Private label2 As System.Windows.Forms.Label
        Private label1 As System.Windows.Forms.Label
        Private _cmbTarget2Color As System.Windows.Forms.ComboBox
        Private _cmbTarget1Color As System.Windows.Forms.ComboBox
        Private _cmbHighlightItem As System.Windows.Forms.ComboBox
        Private _cmbLabelType As System.Windows.Forms.ComboBox
        Private _txtTarget2 As System.Windows.Forms.TextBox
        Private _txtTarget1 As System.Windows.Forms.TextBox
        Friend WithEvents _fpSpread As FarPoint.Win.Spread.FpSpread
        Friend WithEvents _fpSpread_Sheet1 As FarPoint.Win.Spread.SheetView
        Friend WithEvents _fpSpread_Sheet2 As FarPoint.Win.Spread.SheetView
    End Class
End Namespace
