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

Public Class EnhancedCameraShape
    Inherits BaseSampleControl

    Private Sub InitCameraShape(ByVal sheetView As SheetView)
        Dim worksheet As IWorksheet = sheetView.AsWorksheet()
        Dim workbook As IWorkbook = worksheet.Workbook
        worksheet.Name = "CameraShape"

        worksheet.SetValue(1, 0, "Select :")
        Dim comboBoxCell As ComboBoxCellType = New ComboBoxCellType()
        Dim strings As String()
        ReDim strings(3)
        strings(0) = "Late Arrivals By Reported Cause"
        strings(1) = "My Assets"
        strings(2) = "Revenue By State"
        comboBoxCell.Items = strings
        comboBoxCell.EditorValue = EditorValue.Index
        comboBoxCell.StopEditingAfterDropDownItemSelected = True
        worksheet.Cells("B2").CellType = comboBoxCell
        worksheet.Cells("B2").Value = 0
        workbook.Names.Add("SelectedRange", "=IF(CameraShape!$B$2=0,Pareto!$A$1:$E$8,IF(CameraShape!$B$2=1,Pie!$A$1:$D$8,Area!$A$1:$E$7))")
        worksheet.Columns("A:B").AutoFit()
        Dim cameraShape As IShape = worksheet.Shapes.AddPicture("", False, True, 0, 60)
        cameraShape.Formula = "SelectedRange"
    End Sub

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

        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,0.5,0.8,0,2,false,""gray"",""orange"",$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 = System.Drawing.Color.Gray
        sheetView.Cells("A1").ForeColor = System.Drawing.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 InitPieSparkline(ByVal sheetView As SheetView)
        Dim worksheet As IWorksheet = sheetView.AsWorksheet()
        worksheet.Name = "Pie"

        worksheet.Cells("A1:D1").Merge()
        Dim range As IRange = worksheet.Cells("A1")
        range.Value = "My Assets"
        range.Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 17
            })
        range.VerticalAlignment = VerticalAlignment.Center

        For i As Integer = 65 To 69 - 1
            sheetView.Cells($"{Convert.ToChar(i)}2").BackColor = ColorTranslator.FromHtml("#E3E3E3")
        Next

        worksheet.SetValue(1, 0, New Object(,) {
            {"Asset Type", "Amount", "Diagram", "Note"},
            {"House", 120000, Nothing, Nothing},
            {"401k", 78000, Nothing, Nothing},
            {"Savings", 25000, Nothing, Nothing},
            {"Bonds", 15000, Nothing, Nothing},
            {"Stocks", 9000, Nothing, Nothing},
            {"Car", 7500, Nothing, Nothing}})

        worksheet.Cells("C3:C8").Merge()
        worksheet.Cells("C3").Formula2 = "PIESPARKLINE(B3:B8,""#82bc00"",""#96c63f"",""#aacf62"",""#bcd983"",""#cee3a3"",""#dfecc3"")"
        sheetView.Cells("D3").BackColor = ColorTranslator.FromHtml("#82bc00")
        worksheet.Cells("D3").Formula = "B3/SUM(B3:B8)"
        sheetView.Cells("D4").BackColor = ColorTranslator.FromHtml("#96c63f")
        worksheet.Cells("D4").Formula = "B4/SUM(B3:B8)"
        sheetView.Cells("D5").BackColor = ColorTranslator.FromHtml("#aacf62")
        worksheet.Cells("D5").Formula = "B5/SUM(B3:B8)"
        sheetView.Cells("D6").BackColor = ColorTranslator.FromHtml("#bcd983")
        worksheet.Cells("D6").Formula = "B6/SUM(B3:B8)"
        sheetView.Cells("D7").BackColor = ColorTranslator.FromHtml("#cee3a3")
        worksheet.Cells("D7").Formula = "B7/SUM(B3:B8)"
        sheetView.Cells("D8").BackColor = ColorTranslator.FromHtml("#dfecc3")
        worksheet.Cells("D8").Formula = "B8/SUM(B3:B8)"

        worksheet.Cells("D3:D8").CellType = New PercentCellType() With {
                .DecimalPlaces = 2
            }

        worksheet.Cells("B3:B8").CellType = New CurrencyCellType() With {
                .ShowCurrencySymbol = True,
                .DecimalPlaces = 0,
                .ShowSeparator = True,
                .CurrencySymbol = "$"
            }

        worksheet.Rows("1").RowHeight = 30
        worksheet.Rows("2:8").RowHeight = 25
        worksheet.Columns("A").ColumnWidth = 100
        worksheet.Columns("B").ColumnWidth = 100
        worksheet.Columns("C").ColumnWidth = 200

    End Sub

    Private Sub InitAreaSparkline(ByVal sheetView As SheetView)
        Dim worksheet As IWorksheet = sheetView.AsWorksheet()
        worksheet.Name = "Area"
        worksheet.Cells("A1:E1").Merge()
        worksheet.Rows("1").RowHeight = 32
        worksheet.Cells("A1").Text = "Revenue by State"
        worksheet.Cells("A1").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
        worksheet.Cells("A1").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 17
            })

        Dim table As ITable = worksheet.Tables.Add("A2:E6", GrapeCity.Spreadsheet.YesNoGuess.Guess, FarPoint.Win.Spread.TableStyle.TableStyleLight1)
        table.ShowAutoFilter = False

        worksheet.SetValue(1, 0, New Object(,) {
            {"State", 2018, 2019, 2020, "Diagram"},
            {"Idaho", 10000, -9000, 15000, Nothing},
            {"Montana", 1000, 9000, Nothing, Nothing},
            {"Oregon", 10000, 7000, 0, Nothing},
            {"Washington", 1000, 10000, 5000, Nothing},
            {"Utah", -5000, 5000, 12000, Nothing}})
        worksheet.Cells("B3:D7").CellType = New CurrencyCellType() With {
                .DecimalPlaces = 0
            }

        worksheet.Cells("E3").Formula = "AREASPARKLINE(B3:D3,,,0,10000,""#82bc00"",""#f7a711"")"
        worksheet.Cells("E4").Formula = "AREASPARKLINE(B4:D4,,,0,10000,""#82bc00"",""#f7a711"")"
        worksheet.Cells("E5").Formula = "AREASPARKLINE(B5:D5,,,0,10000,""#82bc00"",""#f7a711"")"
        worksheet.Cells("E6").Formula = "AREASPARKLINE(B6:D6,,,0,10000,""#82bc00"",""#f7a711"")"
        worksheet.Cells("E7").Formula = "AREASPARKLINE(B7:D7,,,0,10000,""#82bc00"",""#f7a711"")"

        worksheet.Rows("1").RowHeight = 50
        worksheet.Rows("2").RowHeight = 25
        worksheet.Rows("3:7").RowHeight = 35

        worksheet.Columns("A").ColumnWidth = 100
        worksheet.Columns("B:D").ColumnWidth = 80
        worksheet.Columns("E").ColumnWidth = 200

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        FpSpread1.Features.EnhancedShapeEngine = True
        FpSpread1.Sheets.Count = 4

        InitAreaSparkline(FpSpread1.Sheets(3))
        InitPieSparkline(FpSpread1.Sheets(2))
        InitParetoSparkline(FpSpread1.Sheets(1))
        InitCameraShape(FpSpread1.Sheets(0))
    End Sub

    Private Sub NameManagerToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles NameManagerToolStripMenuItem.Click
        FpSpread1.ShowListCustomNameForm(Me.ParentForm)
    End Sub

End Class


'
'EnhancedCameraShape.Designer.vb
'


Imports System.Windows.Forms

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()>
Partial Class EnhancedCameraShape

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()>
    Protected Shadows Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    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.
    <System.Diagnostics.DebuggerStepThrough()>
    Private Sub InitializeComponent()
        Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(EnhancedCameraShape))
        Me.SplitContainer1 = New System.Windows.Forms.SplitContainer()
        Me.SplitContainer2 = New System.Windows.Forms.SplitContainer()
        Me.NameBox1 = New FarPoint.Win.Spread.NameBox()
        Me.Panel1 = New System.Windows.Forms.Panel()
        Me.FormulaTextBox1 = New FarPoint.Win.Spread.FormulaTextBox()
        Me.FpSpread1 = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None, CType(resources.GetObject("SplitContainer2.Panel1.Controls"), Object))
        Me.FpSpread1_Sheet1 = Me.FpSpread1.GetSheet(0)
        Me.MenuStrip1 = New System.Windows.Forms.MenuStrip()
        Me.NameManagerToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem()
        CType(Me.SplitContainer1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SplitContainer1.Panel1.SuspendLayout()
        Me.SplitContainer1.Panel2.SuspendLayout()
        Me.SplitContainer1.SuspendLayout()
        CType(Me.SplitContainer2, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SplitContainer2.Panel1.SuspendLayout()
        Me.SplitContainer2.Panel2.SuspendLayout()
        Me.SplitContainer2.SuspendLayout()
        Me.Panel1.SuspendLayout()
        CType(Me.FpSpread1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.MenuStrip1.SuspendLayout()
        Me.SuspendLayout()
        '
        'SplitContainer1
        '
        Me.SplitContainer1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.SplitContainer1.Location = New System.Drawing.Point(0, 24)
        Me.SplitContainer1.Name = "SplitContainer1"
        Me.SplitContainer1.Orientation = System.Windows.Forms.Orientation.Horizontal
        '
        'SplitContainer1.Panel1
        '
        Me.SplitContainer1.Panel1.Controls.Add(Me.SplitContainer2)
        Me.SplitContainer1.Panel1MinSize = 21
        '
        'SplitContainer1.Panel2
        '
        Me.SplitContainer1.Panel2.Controls.Add(Me.FpSpread1)
        Me.SplitContainer1.Size = New System.Drawing.Size(800, 426)
        Me.SplitContainer1.SplitterDistance = 21
        Me.SplitContainer1.TabIndex = 0
        '
        'SplitContainer2
        '
        Me.SplitContainer2.Dock = System.Windows.Forms.DockStyle.Fill
        Me.SplitContainer2.Location = New System.Drawing.Point(0, 0)
        Me.SplitContainer2.Name = "SplitContainer2"
        '
        'SplitContainer2.Panel1
        '
        Me.SplitContainer2.Panel1.Controls.Add(Me.NameBox1)
        '
        'SplitContainer2.Panel2
        '
        Me.SplitContainer2.Panel2.Controls.Add(Me.Panel1)
        Me.SplitContainer2.Size = New System.Drawing.Size(800, 21)
        Me.SplitContainer2.SplitterDistance = 266
        Me.SplitContainer2.TabIndex = 0
        '
        'NameBox1
        '
        Me.NameBox1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.NameBox1.Location = New System.Drawing.Point(0, 0)
        Me.NameBox1.MaxLength = 255
        Me.NameBox1.Name = "NameBox1"
        Me.NameBox1.Size = New System.Drawing.Size(266, 21)
        Me.NameBox1.TabIndex = 0
        'Attach NameBox1 to FpSpread1
        Me.NameBox1.Attach(Me.FpSpread1)
        '
        'Panel1
        '
        Me.Panel1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.Panel1.Controls.Add(Me.FormulaTextBox1)
        Me.Panel1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.Panel1.Location = New System.Drawing.Point(0, 0)
        Me.Panel1.Name = "Panel1"
        Me.Panel1.Size = New System.Drawing.Size(530, 21)
        Me.Panel1.TabIndex = 0
        '
        'FormulaTextBox1
        '
        Me.FormulaTextBox1.BorderStyle = System.Windows.Forms.BorderStyle.None
        Me.FormulaTextBox1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.FormulaTextBox1.Location = New System.Drawing.Point(0, 0)
        Me.FormulaTextBox1.Name = "FormulaTextBox1"
        Me.FormulaTextBox1.Size = New System.Drawing.Size(528, 19)
        Me.FormulaTextBox1.TabIndex = 0
        'Attach FormulaTextBox1 to FpSpread1
        Me.FormulaTextBox1.Attach(Me.FpSpread1)
        '
        '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(800, 401)
        Me.FpSpread1.TabIndex = 0
        '
        'MenuStrip1
        '
        Me.MenuStrip1.Items.AddRange(New System.Windows.Forms.ToolStripItem() {Me.NameManagerToolStripMenuItem})
        Me.MenuStrip1.Location = New System.Drawing.Point(0, 0)
        Me.MenuStrip1.Name = "MenuStrip1"
        Me.MenuStrip1.Size = New System.Drawing.Size(800, 24)
        Me.MenuStrip1.TabIndex = 1
        Me.MenuStrip1.Text = "MenuStrip1"
        '
        'NameManagerToolStripMenuItem
        '
        Me.NameManagerToolStripMenuItem.Name = "NameManagerToolStripMenuItem"
        Me.NameManagerToolStripMenuItem.Size = New System.Drawing.Size(101, 20)
        Me.NameManagerToolStripMenuItem.Text = "Name Manager"
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(800, 450)
        Me.pnlMain.Controls.Add(Me.SplitContainer1)
        Me.Controls.Add(Me.MenuStrip1)
        Me.Name = "Enhanced Camera Shape"
        Me.Text = "Enhanced Camera Shape"
        Me.SplitContainer1.Panel1.ResumeLayout(False)
        Me.SplitContainer1.Panel2.ResumeLayout(False)
        CType(Me.SplitContainer1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.SplitContainer1.ResumeLayout(False)
        Me.SplitContainer2.Panel1.ResumeLayout(False)
        Me.SplitContainer2.Panel2.ResumeLayout(False)
        CType(Me.SplitContainer2, System.ComponentModel.ISupportInitialize).EndInit()
        Me.SplitContainer2.ResumeLayout(False)
        Me.Panel1.ResumeLayout(False)
        CType(Me.FpSpread1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.MenuStrip1.ResumeLayout(False)
        Me.MenuStrip1.PerformLayout()
        Me.ResumeLayout(False)
        Me.PerformLayout()

    End Sub

    Friend WithEvents SplitContainer1 As SplitContainer
    Friend WithEvents SplitContainer2 As SplitContainer
    Friend WithEvents NameBox1 As FarPoint.Win.Spread.NameBox
    Friend WithEvents Panel1 As Panel
    Friend WithEvents FormulaTextBox1 As FarPoint.Win.Spread.FormulaTextBox
    Friend WithEvents FpSpread1 As FarPoint.Win.Spread.FpSpread
    Friend WithEvents FpSpread1_Sheet1 As FarPoint.Win.Spread.SheetView
    Friend WithEvents MenuStrip1 As MenuStrip
    Friend WithEvents NameManagerToolStripMenuItem As ToolStripMenuItem
End Class
