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

Namespace Features
    Partial Public Class ImageSparkline
        Inherits BaseSampleControl

        Private _data As Object(,)
        Private ReadOnly _imagesDir As String = Path.Combine(Application.StartupPath, "Images")

        Public Sub New()
            InitializeComponent()
            Name = "Image"
            InitData()
            InitSpread(_fpSpread)
        End Sub

#Region "Private Methods"
        Private Sub InitSpread(ByVal fpSpread As FpSpread)
            Dim sheet1 As SheetView = fpSpread.Sheets(0)
            InitSpreadSheet(sheet1, New Action(Of IWorksheet)(AddressOf ApplyImageFormula))
            Dim sheet2 As SheetView = fpSpread.Sheets(1)
            InitSpreadSheet(sheet2, New Action(Of IWorksheet)(AddressOf ApplyImageFormatter))
        End Sub

        Private Sub ApplyImageFormula(ByVal worksheet As IWorksheet)
            For i As Integer = 2 To 17 - 1
                worksheet.Cells($"A{i + 1}").Formula2 = $"GC.IMAGE(F{i + 1})"
            Next

            worksheet.Columns("F").ColumnWidth = 0
        End Sub

        Private Sub ApplyImageFormatter(ByVal sheet As IWorksheet)
            For i As Integer = 2 To 17 - 1
                Dim cell As IRange = sheet.Cells($"F{i + 1}")
                cell.HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center
                cell.VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
                cell.CellType = New ImageCellType(RenderStyle.Normal)
                cell.Value = New Bitmap(cell.Text)
            Next
        End Sub

        Private Sub InitSpreadSheet(ByVal sheetview As SheetView, ByVal imageSetAction As Action(Of IWorksheet))
            Dim worksheet As IWorksheet = sheetview.AsWorksheet()
            worksheet.Cells("A1:F1").Merge()
            worksheet.Cells("A1").Value = "Employees List"
            worksheet.Cells("A1").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 15
            })

            sheetview.Cells("A1").BackColor = ColorTranslator.FromHtml("#E8E8E8")
            worksheet.Cells("A1").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("A2:F2").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 13,
                .Bold = True
            })

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

            worksheet.SetValue(1, 1, _data)

            imageSetAction?.Invoke(worksheet)
            worksheet.Cells("E3:E17").CellType = New DateTimeCellType() With {
                .DateTimeFormat = DateTimeFormat.UserDefined,
                .UserDefinedFormat = "dd-MM-yyyy"
            }

            worksheet.Cells("A3:F17").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center

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

            For i As Integer = 2 To 17 - 1
                worksheet.Rows($"{i + 1}").RowHeight = 55
            Next

            worksheet.Rows("1:2").RowHeight = 30
            worksheet.Columns("A").ColumnWidth = 60
            worksheet.Cells("H3:L3").Merge()
            worksheet.Cells("H3").Text = "You can set image according to URL or base64"
            worksheet.Cells("H3:L5").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("H3:L5").HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center
            worksheet.Cells("H4:L5").Merge()
            worksheet.Cells("H4").Formula2 = $"GC.IMAGE(""{_imagesDir}\logo-en.ico"", 4, 50, 150, 1, 1, 1, 1, 1, 1)"
        End Sub

        Private Sub InitData()
            _data = New Object(,) {
            {"Employee Name", "Department", "Email", "Start Date", "PhotoUrl"},
            {"Thomas Miller", "Legal", "t.miller@company.com", DateTime.Parse("2020-06-11T00:00:00.000Z"), $"{_imagesDir}\\1.png"},
            {"Billy May", "R&D", "b.may@company.com", DateTime.Parse("2020-01-11T00:00:00.000Z"), $"{_imagesDir}\\2.png"},
            {"John Watson", "Retail", "j.watson@company.com", DateTime.Parse("2020-01-30T00:00:00.000Z"), $"{_imagesDir}\\3.png"},
            {"Harry Tale", "HR", "h.tale@company.com", DateTime.Parse("2021-01-11T00:00:00.000Z"), $"{_imagesDir}\\4.png"},
            {"Bernard Martin", "R&D", "b.martin@company.com", DateTime.Parse("2020-01-13T00:00:00.000Z"), $"{_imagesDir}\\5.png"},
            {"Robin Caruso", "Sales", "r.caruso@company.com", DateTime.Parse("2021-02-11T00:00:00.000Z"), $"{_imagesDir}\\6.png"},
            {"David Willson", "HR", "d.willson@company.com", DateTime.Parse("2020-01-22T00:00:00.000Z"), $"{_imagesDir}\\7.png"},
            {"Stephan Willinghan", "HR", "s.willinghan@company.com", DateTime.Parse("2021-01-11T00:00:00.000Z"), $"{_imagesDir}\\8.png"},
            {"Anita Deville", "R&D", "a.deville@company.com", DateTime.Parse("2020-01-11T00:00:00.000Z"), $"{_imagesDir}\\9.png"},
            {"Lana O'Connell", "Legal", "l.oconnell@company.com", DateTime.Parse("2020-01-11T00:00:00.000Z"), $"{_imagesDir}\\10.png"},
            {"Adam Backer", "Finance", "a.backer@company.com", DateTime.Parse("2021-03-11T00:00:00.000Z"), $"{_imagesDir}\\11.png"},
            {"Ted Stanton", "Sales", "t.stanton@company.com", DateTime.Parse("2020-01-11T00:00:00.000Z"), $"{_imagesDir}\\12.png"},
            {"Bryan Roberts", "HR", "b.roberts@company.com", DateTime.Parse("2020-11-16T00:00:00.000Z"), $"{_imagesDir}\\13.png"},
            {"Richard McLear", "Legal", "r.mclear@company.com", DateTime.Parse("2021-12-18T00:00:00.000Z"), $"{_imagesDir}\\14.png"},
            {"Jesse White", "Finance", "j.white@company.com", DateTime.Parse("2020-08-11T00:00:00.000Z"), $"{_imagesDir}\\15.png"}}
        End Sub
#End Region
    End Class
End Namespace


'
'ImageSparkline.Designer.vb
'


Namespace Features
    Partial Class ImageSparkline

        '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(ImageSparkline))
            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
            Me._fpSpread.ActiveSheetIndex = 0
            '
            'ImageSparkline
            '
            Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.Name = "ImageSparkline"
            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
