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

Namespace Features
    Public Class StackedSparkline
        Inherits BaseSampleControl

        Public Sub New()
            InitializeComponent()
            Name = "Stacked"

            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.Cells("A1:G1").Merge()
            worksheet.Cells("A1").Value = "Quarterly Sales by Regions 2020"
            worksheet.Cells("A1").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 20
            })
            worksheet.Cells("A1").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center

            For i As Integer = 65 To 72 - 1
                Dim columnChar As Char = Convert.ToChar(i)
                sheetView.Cells($"{columnChar}2").BackColor = ColorTranslator.FromHtml("#E3E3E3")
                worksheet.Columns($"{columnChar}").ColumnWidth = 120
                worksheet.Columns($"{columnChar}").NumberFormat = "$#,##0"
            Next

            worksheet.SetValue(1, 0, New Object(,) {
            {"Quarter", "Color Range", "East", "West", "North", "South", "Diagram"},
            {"Q1", "#82BC00", 42000, 45000, 26000, 38000, Nothing},
            {"Q2", "#AED069", 52000, 35000, 45000, 20000, Nothing},
            {"Q3", "#D4E3AF", 25000, 34000, 55000, 19000, Nothing},
            {"Q4", "#F4F8EB", 50000, 25000, 60000, 20000, Nothing}})

            worksheet.Cells("G3").Formula2 = "STACKEDSPARKLINE(C3:F3,$B$3:$B$6,$C$2:$F$2,170000)"
            worksheet.Cells("G4").Formula2 = "STACKEDSPARKLINE(C4:F4,$B$3:$B$6,$C$2:$F$2,170000)"
            worksheet.Cells("G5").Formula2 = "STACKEDSPARKLINE(C5:F5,$B$3:$B$6,$C$2:$F$2,170000)"
            worksheet.Cells("G6").Formula2 = "STACKEDSPARKLINE(C6:F6,$B$3:$B$6,$C$2:$F$2,170000)"
            worksheet.Cells("G7").Formula2 = "STACKEDSPARKLINE(C7:F7,$B$3:$B$6,$C$2:$F$2,170000)"
            worksheet.Rows("1").RowHeight = 50
            worksheet.Rows("2").RowHeight = 25

            For i As Integer = 3 To 6
                worksheet.Rows($"{i}").RowHeight = 45
            Next

            worksheet.Columns("G").ColumnWidth = 300
            worksheet.Columns("B").ColumnWidth = 0
        End Sub

        Private Sub InitVerticalSparkline(ByVal sheetView As SheetView, ByVal name As String)
            Dim worksheet As IWorksheet = sheetView.AsWorksheet()
            worksheet.Name = name
            worksheet.Cells("A1:E1").Merge()
            worksheet.Cells("A1").Value = "Quarterly Sales by Regions 2020"
            worksheet.Cells("A1").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 20
            })
            worksheet.Cells("A1").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center

            For i As Integer = 65 To 70 - 1
                Dim columnChar As Char = Convert.ToChar(i)
                sheetView.Cells($"{columnChar}2").BackColor = ColorTranslator.FromHtml("#E3E3E3")
                worksheet.Columns($"{columnChar}").ColumnWidth = 120
                worksheet.Columns($"{columnChar}").NumberFormat = "$#,##0"
            Next

            worksheet.SetValue(1, 0, New Object(,) {
            {"Quarter", "Q1", "Q2", "Q3", "Q4"},
            {"Color Range", "#82BC00", "#AED069", "#D4E3AF", "#F4F8EB"},
            {"East", 42000, 52000, 25000, 50000},
            {"West", 45000, 35000, 34000, 25000},
            {"North", 26000, 45000, 55000, 60000},
            {"South", 38000, 20000, 19000, 20000}})

            worksheet.Cells("B8").Formula2 = "STACKEDSPARKLINE(B$4:B$7,$B$3:$E$3,$A$4:$A$7,170000,,,,,,,TRUE)"
            worksheet.Cells("C8").Formula2 = "STACKEDSPARKLINE(C$4:C$7,$B$3:$E$3,$A$4:$A$7,170000,,,,,,,TRUE)"
            worksheet.Cells("D8").Formula2 = "STACKEDSPARKLINE(D$4:D$7,$B$3:$E$3,$A$4:$A$7,170000,,,,,,,TRUE)"
            worksheet.Cells("E8").Formula2 = "STACKEDSPARKLINE(E$4:E$7,$B$3:$E$3,$A$4:$A$7,170000,,,,,,,TRUE)"
            worksheet.Cells("F8").Formula2 = "STACKEDSPARKLINE(F$4:F$7,$B$3:$E$3,$A$4:$A$7,170000,,,,,,,TRUE)"

            For i As Integer = 1 To 7
                worksheet.Rows($"{i}").RowHeight = 35
            Next

            worksheet.Rows("3").RowHeight = 0
            worksheet.Rows("8").RowHeight = 250
        End Sub
#End Region

    End Class
End Namespace


'
'StackedSparkline.Designer.vb
'


Namespace Features
    Partial Class StackedSparkline
        ''' <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(StackedSparkline))
            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 = 3
            '
            'StackedSparkline
            '
            Me.AutoScaleDimensions = New System.Drawing.SizeF(8.0!, 16.0!)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.Name = "StackedSparkline"
            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
