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

Namespace Features
    Partial Public Class MonthSparkline
        Inherits BaseSampleControl
        Private _rand As Random

        Public Sub New()
            InitializeComponent()
            Name = "Month"
            _rand = New Random()
            InitNormalSheet(_fpSpread.Sheets(0))
            InitColorRangeSheet(_fpSpread.Sheets(1))
        End Sub

#Region "Private Methods"
        Private Sub InitColorRangeSheet(ByVal sheetView As SheetView)
            Dim worksheet As IWorksheet = sheetView.AsWorksheet()
            worksheet.Name = "ColorRange"

            worksheet.Cells("A1").Text = "January"
            worksheet.Cells("B1").Text = "Value"
            worksheet.Cells("C1").Text = "Color"
            worksheet.Cells("E1").Text = "February"
            worksheet.Cells("F1").Text = "Value"
            worksheet.Cells("G1").Text = "Color"
            worksheet.Cells("I1").Text = "Diagram"

            worksheet.Columns("A").ColumnWidth = 80
            worksheet.Columns("B").ColumnWidth = 70
            worksheet.Columns("C").ColumnWidth = 70
            worksheet.Columns("D").ColumnWidth = 30
            worksheet.Columns("E").ColumnWidth = 80
            worksheet.Columns("F").ColumnWidth = 70
            worksheet.Columns("G").ColumnWidth = 70
            worksheet.Columns("H").ColumnWidth = 30
            worksheet.Columns("I").ColumnWidth = 90

            sheetView.Cells("A1:I1").BackColor = ColorTranslator.FromHtml("#999999")
            sheetView.Cells("A1:I1").ForeColor = ColorTranslator.FromHtml("#FFFFFF")

            Dim dateCellType As DateTimeCellType = New DateTimeCellType() With {
                .DateTimeFormat = DateTimeFormat.UserDefined,
                .UserDefinedFormat = "yyyy-MM-dd"
            }

            worksheet.Cells("A2:A32").CellType = dateCellType
            worksheet.Cells("E2:E29").CellType = dateCellType
            worksheet.Cells("I3:I7").Merge()
            worksheet.Cells("I10:I14").Merge()

            Dim dateTime As DateTime = New DateTime(2020, 1, 1)

            For row As Integer = 1 To 31
                worksheet.Cells($"A{row + 1}").Value = dateTime.AddDays(row - 1)
                worksheet.Cells($"B{row + 1}").Value = Math.Round(_rand.NextDouble() * 100)
                worksheet.Cells($"C{row + 1}").Value = GetRandomColor()
            Next

            dateTime = New DateTime(2020, 2, 1)

            For row As Integer = 1 To 28
                worksheet.Cells($"E{row + 1}").Value = dateTime.AddDays(row - 1)
                worksheet.Cells($"F{row + 1}").Value = Math.Round(_rand.NextDouble() * 100)
                worksheet.Cells($"G{row + 1}").Value = GetRandomColor()
            Next

            worksheet.Cells("I3").Formula2 = "MONTHSPARKLINE(2020,1,A2:B31,C2:C32)"
            worksheet.Cells("I8").Formula2 = "TEXT(DATE(2020,1, 1),""mmmm"")"
            worksheet.Cells("I10").Formula2 = "MONTHSPARKLINE(2020,2,E2:F29,G2:G29)"
            worksheet.Cells("I15").Formula2 = "TEXT(DATE(2020,2, 1),""mmmm"")"
        End Sub

        Private Sub InitNormalSheet(ByVal sheetView As SheetView)
            Dim worksheet As IWorksheet = sheetView.AsWorksheet()
            worksheet.Name = "No ColorRange"

            worksheet.Cells("A1").Text = "January"
            worksheet.Cells("B1").Text = "Value"
            worksheet.Cells("D1").Text = "February"
            worksheet.Cells("E1").Text = "Value"
            worksheet.Cells("G1").Text = "Diagram"

            worksheet.Columns("A").ColumnWidth = 80
            worksheet.Columns("B").ColumnWidth = 70
            worksheet.Columns("C").ColumnWidth = 30
            worksheet.Columns("D").ColumnWidth = 80
            worksheet.Columns("E").ColumnWidth = 70
            worksheet.Columns("F").ColumnWidth = 30
            worksheet.Columns("G").ColumnWidth = 90

            sheetView.Cells("A1:G1").BackColor = ColorTranslator.FromHtml("#999999")
            sheetView.Cells("A1:G1").ForeColor = ColorTranslator.FromHtml("#FFFFFF")

            Dim dateCellType As DateTimeCellType = New DateTimeCellType() With {
                .DateTimeFormat = DateTimeFormat.UserDefined,
                .UserDefinedFormat = "yyyy-MM-dd"
            }

            worksheet.Cells("A2:A32").CellType = dateCellType
            worksheet.Cells("D2:D29").CellType = dateCellType
            worksheet.Cells("G3:G7").Merge()
            worksheet.Cells("G10:G14").Merge()

            Dim dateTime As DateTime = New DateTime(2020, 1, 1)

            For row As Integer = 1 To 31
                worksheet.Cells($"A{row + 1}").Value = dateTime.AddDays(row - 1)
                worksheet.Cells($"B{row + 1}").Value = Math.Round(_rand.NextDouble() * 100)
            Next

            dateTime = New DateTime(2020, 2, 1)

            For row As Integer = 1 To 28
                worksheet.Cells($"D{row + 1}").Value = dateTime.AddDays(row - 1)
                worksheet.Cells($"E{row + 1}").Value = Math.Round(_rand.NextDouble() * 100)
            Next

            worksheet.Cells("G3").Formula2 = "MONTHSPARKLINE(2020, 1, A2:B32, ""#6d6d6d"", ""#c4c4c4"", ""#979797"", ""#f3f3f3"")"
            worksheet.Cells("G8").Formula2 = "TEXT(DATE(2020,1, 1),""mmmm"")"
            worksheet.Cells("G10").Formula2 = "MONTHSPARKLINE(2020,2, D2:E29, ""#6d6d6d"", ""#c4c4c4"", ""#979797"", ""#f3f3f3"")"
            worksheet.Cells("G15").Formula2 = "TEXT(DATE(2020,2, 1),""mmmm"")"
        End Sub

        Private Function GetRandomColor() As String
            Dim colorList As String() = New String() {"#82bc00", "#9fc94c", "#00C2D6", "#d2e4a7", "#e9f2d2", "#ffedd3", "#ffdba8", "#ffc97d", "#feb850", "#f7a711", "#00c2d6"}
            Return colorList(_rand.[Next](0, colorList.Length))
        End Function
#End Region
    End Class
End Namespace


'
'MonthSparkline.Designer.vb
'


Namespace Features
    <Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()>
    Partial Class MonthSparkline

        'UserControl overrides dispose to clean up the component list.
        <System.Diagnostics.DebuggerNonUserCode()>
        Protected Overloads 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(MonthSparkline))
            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()
            CType(Me._fpSpread, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.SuspendLayout()
            '
            'pnlMain
            '
            Me.pnlMain.Controls.Add(Me._fpSpread)
            '
            '_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
            '
            'MonthSparkline
            '
            Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.Name = "MonthSparkline"
            Me.pnlMain.ResumeLayout(False)
            CType(Me._fpSpread, System.ComponentModel.ISupportInitialize).EndInit()
            Me.ResumeLayout(False)

        End Sub

        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
