Imports System.Drawing
Imports FarPoint.Win
Imports FarPoint.Win.Spread
Imports GrapeCity.Spreadsheet
Imports Spread.Common.Features.Spread.Common.Features.Sample

Namespace Features
    Public Class BarSparkline
        Inherits BaseSampleControl

        Public Sub New()
            InitializeComponent()
            Name = "HBar and VBar"

            InitSpread()
        End Sub

#Region "Private methods"
        Private Sub InitSpread()
            fpSpread1.Sheets.Count = 2
            fpSpread1.TabStripInsertTab = False
            InitHorizontalSparkline(fpSpread1.Sheets(0), "Horizontal")
            InitVerticalSparkline(fpSpread1.Sheets(1), "Vertical")
        End Sub

        Private Sub InitHorizontalSparkline(ByVal sheetView As SheetView, ByVal name As String)
            Dim worksheet As IWorksheet = sheetView.AsWorksheet()
            worksheet.Name = name
            worksheet.SetValue(1, 0, New Object(,) {
                {"Channel", "Satisfaction %", "Diagram"},
                {"Email", 0.92, Nothing},
                {"Social Networks", 0.79, Nothing},
                {"Forums/Message Boards", 0.65, Nothing},
                {"Phone", 0.58, Nothing},
                {"Text Message", 0.55, Nothing},
                {"Online Chat", 0.39, Nothing},
                {"Self-Serve Knowledge Base", 0.28, Nothing},
                {"On-Page Support Widget", 0.19, Nothing}})

            For i As Integer = 3 To 10
                worksheet.Rows($"{i}").RowHeight = 30
                worksheet.Cells($"C{i}").Formula = GetFormula($"B{i}")
            Next

            worksheet.Cells("A1:C1").Merge()
            worksheet.Cells("A1").Value = "Customer Service Satisfaction, by Channel"
            worksheet.Cells("A1").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
        .Name = "Arial",
        .Size = 17
    })
            sheetView.Cells("A1").BackColor = ColorTranslator.FromHtml("#E8E8E8")
            worksheet.Cells("A1").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("A2:C2").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
        .Name = "Arial",
        .Size = 13,
        .Bold = True
    })
            worksheet.Cells("A2:C2").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("A2:C2").Borders.ApplyBorder(New GrapeCity.Spreadsheet.Border() With {
        .Bottom = New GrapeCity.Spreadsheet.BorderLine(GrapeCity.Spreadsheet.BorderLineStyle.Thin, GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Black))
    })
            worksheet.Cells("B3:B10").NumberFormat = "0%"
            worksheet.Cells("B3:B10").HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Left
            worksheet.Rows("1:2").RowHeight = 35
            worksheet.Columns("A").ColumnWidth = 250
            worksheet.Columns("B").ColumnWidth = 120
            worksheet.Columns("C").ColumnWidth = 250
        End Sub

        Private Sub InitVerticalSparkline(ByVal sheetView As SheetView, ByVal name As String)
            Dim worksheet As IWorksheet = sheetView.AsWorksheet()
            worksheet.Name = name
            worksheet.SetValue(1, 0, New Object(,) {
    {"Name", "Employee 1", "Employee 2", "Employee 3", "Employee 4", "Employee 5", "Employee 6", "Employee 7", "Employee 8"},
    {"Progress", 1.1, 0.79, 0.59, 0.55, 0.37, 0.19, 0.35, 0.6}})

            For i As Integer = 0 To 8 - 1
                Dim columnChar As Char = ChrW((66 + i))
                worksheet.Columns($"{columnChar}").ColumnWidth = 80
                worksheet.Cells($"{columnChar}4").Formula = GetVBarFormula(columnChar & "3")
            Next

            worksheet.Cells("A1:I1").Merge()
            worksheet.Cells("A1").Value = "Sprint 3 Progress"
            worksheet.Cells("A1").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 17
            })
            sheetView.Cells("A1").BackColor = ColorTranslator.FromHtml("#E8E8E8")
            worksheet.Cells("A1").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("A2:A3").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 17,
                .Bold = True
            })
            worksheet.Cells("A2:A3").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("A2:A3").Borders.ApplyBorder(New GrapeCity.Spreadsheet.Border() With {
                .Right = New GrapeCity.Spreadsheet.BorderLine(GrapeCity.Spreadsheet.BorderLineStyle.Thin, GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Black))
            })
            worksheet.Cells("B3:I3").NumberFormat = "0%"
            worksheet.Cells("B3:I3").HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Left
            worksheet.Rows("1:3").RowHeight = 40
            worksheet.Rows("4").RowHeight = 220
            worksheet.Columns("A").ColumnWidth = 120
        End Sub

        Private Function GetFormula(ByVal range As String) As String
            Return "IF(" & range & ">=0.8,HBARSPARKLINE(" & range & ",""#092834"",true," & range & "), " &
                "IF(" & range & ">=0.6,HBARSPARKLINE(" & range & ",""#347B98"",true," & range & "), " &
                "IF(" & range & ">=0.4,HBARSPARKLINE(" & range & ",""#66B032"",true," & range & "), " &
                "IF(" & range & ">=0.2,HBARSPARKLINE(" & range & ",""#B2D732"",true," & range & "), " &
                "IF(" & range & ">=0,HBARSPARKLINE(" & range & ",""#F0F7D4"",true," & range & "), HBARSPARKLINE(" & range & ",""red"") " & ")))))"
        End Function

        Private Function GetVBarFormula(ByVal range As String) As String
            Return "IF(" & range & ">=0.8,VBARSPARKLINE(" & range & ",""#092834"",true,0.4), " &
                "IF(" & range & ">=0.6,VBARSPARKLINE(" & range & ",""#347B98"",true,0.4), " &
                "IF(" & range & ">=0.4,VBARSPARKLINE(" & range & ",""#66B032"",true,0.4), " &
                "IF(" & range & ">=0.2,VBARSPARKLINE(" & range & ",""#B2D732"",true,0.4), " &
                "IF(" & range & ">=0,VBARSPARKLINE(" & range & ",""#F0F7D4"",true,0.4), VBARSPARKLINE(" & range & ",""red"") " & ")))))"
        End Function
#End Region

    End Class
End Namespace


'
'BarSparkline.Designer.vb
'


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

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

        '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.
        <System.Diagnostics.DebuggerStepThrough()>
        Private Sub InitializeComponent()
            Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(BarSparkline))
            Me.fpSpread1 = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None, CType(resources.GetObject("pnlMain.Controls"), Object))
            Me.fpSpread1_Sheet1 = Me.fpSpread1.GetSheet(0)
            Me.pnlMain.SuspendLayout()
            CType(Me.fpSpread1, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.SuspendLayout()
            '
            'pnlMain
            '
            Me.pnlMain.Controls.Add(Me.fpSpread1)
            '
            'pnlControls
            '
            Me.pnlControls.Size = New System.Drawing.Size(210, 599)
            '
            'fpSpread1
            '
            Me.fpSpread1.AccessibleDescription = "Book1, Sheet1, Row 0, Column 0"
            Me.fpSpread1.Dock = System.Windows.Forms.DockStyle.Fill
            Me.fpSpread1.Font = New System.Drawing.Font("Calibri", 11.0!)
            Me.fpSpread1.Location = New System.Drawing.Point(0, 0)
            Me.fpSpread1.Name = "fpSpread1"
            Me.fpSpread1.Size = New System.Drawing.Size(636, 599)
            Me.fpSpread1.TabIndex = 4
            '
            'BarSparkline
            '
            Me.AutoScaleDimensions = New System.Drawing.SizeF(8.0!, 16.0!)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.Name = "BarSparkline"
            Me.pnlMain.ResumeLayout(False)
            CType(Me.fpSpread1, System.ComponentModel.ISupportInitialize).EndInit()
            Me.ResumeLayout(False)

        End Sub
        Private WithEvents fpSpread1 As FarPoint.Win.Spread.FpSpread
        Friend WithEvents fpSpread1_Sheet1 As FarPoint.Win.Spread.SheetView
    End Class
End Namespace
