﻿Imports System.Data
Imports GrapeCity.CalcEngine
Imports GrapeCity.Spreadsheet
Imports Spread.Common.Features.Spread.Common.Features.Sample

Public Class Introduction
    Inherits BaseSampleControl

    Dim gradeTable As DataTable

    Sub New()

        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        FpSpread1.AllowCellOverflow = True
        ' enable Cell Data Types feature
        FpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = GrapeCity.Spreadsheet.CalcFeatures.All
        ' enable enhanced excel keyboard shortcuts for Ctrl+Shift+F5 Show Card action
        FpSpread1.Features.ExcelCompatibleKeyboardShortcuts = True
        FpSpread1.SetProtect(False, "")
        InitCustomers()
        InitExternalVariable()
    End Sub

    Private Sub FormatAsTable(ByVal sheet As IWorksheet, ByVal row As Integer, ByVal column As Integer, ByVal row2 As Integer, ByVal column2 As Integer)
        Dim table As ITable = sheet.Tables.Add(row, column, row2, column2, YesNoGuess.Yes)
        Dim action As FarPoint.Win.Spread.UndoRedo.ConvertTableToRangeUndoAction = New FarPoint.Win.Spread.UndoRedo.ConvertTableToRangeUndoAction(FpSpread1, FpSpread1.Sheets(sheet.Name), table.Name)
        action.PerformUndoAction(FpSpread1.GetRootWorkbook())
    End Sub

    Private Sub InitCustomers()
        Dim sheet As IWorksheet = FpSpread1.AsWorkbook().ActiveSheet
        Dim styles As IStyles = sheet.Workbook.Styles
        ' create styles for cells
        Dim titleStyle As IStyle = styles.Add("title")
        titleStyle.IncludeFont = True
        titleStyle.Font.Size = 14
        titleStyle.Font.Bold = True
        Dim exampleTitleStyle As IStyle = styles.Add("exampleTitleStyle")
        exampleTitleStyle.Font.Bold = True
        exampleTitleStyle.IncludeFont = True
        sheet.Name = "Customers Vehicles"
        sheet.SetValue(5, 1, "Customer")
        sheet.SetValue(5, 2, "Email")
        sheet.SetValue(5, 3, "Vehicles")
        ' format as table
        FormatAsTable(sheet, 5, 1, 8, 4)
        ' merge cells for Vehicles column header
        sheet.Cells(5, 3, 5, 4).Merge()
        Dim customer1 As RichValue(Of Customer) = New RichValue(Of Customer)(GCCustomers.customers(0)) With {.ShowDetailsIcon = True}
        Dim customer2 As RichValue(Of Customer) = New RichValue(Of Customer)(GCCustomers.customers(1)) With {.ShowDetailsIcon = True}
        Dim customer3 As RichValue(Of Customer) = New RichValue(Of Customer)(GCCustomers.customers(2)) With {.ShowDetailsIcon = True}
        sheet.SetValue(Of RichValue(Of Customer))(6, 1, New RichValue(Of Customer)(,) {{customer1}, {customer2}, {customer3}})
        sheet.Cells(6, 2, 8, 2).Formula = "$B7.Email"
        sheet.Cells(6, 3, 8, 3).Formula2 = "TRANSPOSE($B7.Vehicles)"

        sheet.SetValue(10, 1, "Vehicle")
        ' note that Vehicle implements IRichValue, so it doesn't need to use IRichValue<T>
        sheet.SetValue(Of Vehicle)(11, 1, New Vehicle(,) {{GCCustomers.customers(0).Vehicles(0)}, {GCCustomers.customers(0).Vehicles(1)}, {GCCustomers.customers(1).Vehicles(0)}, {GCCustomers.customers(1).Vehicles(1)}, {GCCustomers.customers(2).Vehicles(0)}})
        sheet.Tables.Add(10, 1, 15, 1, YesNoGuess.Yes)
        sheet.Columns("B:E").AutoFit()
        sheet.SetValue(1, 1, "Examples of Custom Cell Data Types")
        sheet.Cells(1, 1).Style = titleStyle
        sheet.SetValue(3, 1, "Table of Customer Vehicles")
        sheet.Cells(3, 1).Style = exampleTitleStyle
        sheet.SetValue(10, 1, "Table of Vehicles")
        sheet.Cells(10, 1).Style = exampleTitleStyle
        sheet.SetValue(17, 1, "Use Ctrl+Shift+F5 to invoke the ShowCard action for the cell data type, or click the glyph in the cell.")
        sheet.Cells(17, 1).ApplyStyle(BuiltInStyle.Note)
        sheet.SetValue(18, 1, "You can add more fields for Vehicle in the table using the drop-down list or using the data card.")
        sheet.Cells(18, 1).ApplyStyle(BuiltInStyle.Note)
    End Sub

    Private Sub InitExternalVariable()
        Dim sheet As IWorksheet = FpSpread1.AsWorkbook().Worksheets.Add("ExternalVariable")
        Dim Styles As IStyles = sheet.Workbook.Styles ' workbook styles
        Dim exampleTitleStyle As IStyle = Styles("exampleTitle")
        gradeTable = GetDataTable()
        Dim tableVar As DataTableVariable = New DataTableVariable(gradeTable, False, "Week 1")
        FpSpread1.AsWorkbook().Names.AddExternalVariable("gradeTable", tableVar)
        FormatAsTable(sheet, 4, 1, 19, 9)
        For c As Integer = 0 To gradeTable.Columns.Count - 1
            sheet.Cells(4, c + 1).Text = gradeTable.Columns(c).ColumnName
        Next c
        sheet.Cells("B6").Formula2 = "gradeTable"
        sheet.Cells("C6").Formula2 = "B6.[Week 2]"
        sheet.Cells("D6").Formula2 = "B6.[Week 3]"
        sheet.Cells("E6").Formula2 = "B6.[Week 4]"
        sheet.Cells("F6").Formula2 = "B6.[Week 5]"
        sheet.Cells("G6").Formula2 = "B6.[Week 6]"
        sheet.Cells("H6").Formula2 = "B6.[Week 7]"
        sheet.Cells("I6").Formula2 = "B6.[Subject]"
        sheet.Cells("J6").Formula2 = "B6.[owner-subject]"

        Dim dynamicTableVar As DataTableVariable = New DataTableVariable(gradeTable, True)
        FpSpread1.AsWorkbook().Names.AddExternalVariable("gradeDynamicTable", dynamicTableVar)
        For c As Integer = 0 To gradeTable.Columns.Count - 1
            sheet.Cells(23, c + 1).Text = gradeTable.Columns(c).ColumnName
        Next c
        FormatAsTable(sheet, 23, 1, 38, 9)
        sheet.Cells("B25").Formula2 = "gradeDynamicTable"
        sheet.Columns("B:J").AutoFit()
        sheet.Cells("B3").Text = "ExternalVariable ""gradeTable"" returns the DataTable as IRichValue -- dynamic array example below."
        sheet.Cells("B3").Style = exampleTitleStyle
        sheet.Cells("B22").Text = "ExternalVariable ""gradeDynamicTable"" returns the DataTable as a dynamic array."
        sheet.Cells("B22").Style = exampleTitleStyle
    End Sub

    Private Function GetDataTable() As DataTable
        Dim ds As DataSet = New System.Data.DataSet()
        Dim fpnames As DataTable
        Dim fpclass As DataTable
        Dim fpgrades As DataTable

        fpnames = ds.Tables.Add("Students")
        fpnames.Columns.AddRange(New DataColumn() {New DataColumn("LastName", Type.GetType("System.String")), New DataColumn("FirstName",
            Type.GetType("System.String")), New DataColumn("id", Type.GetType("System.Int32"))})
        fpnames.Rows.Add(New Object() {"Jane", "Mary", 0})
        fpnames.Rows.Add(New Object() {"Rodglet", "Jonnny", 1})
        fpnames.Rows.Add(New Object() {"Steris", "Pat", 2})

        fpclass = ds.Tables.Add("Class")
        fpclass.Columns.AddRange(New DataColumn() {New DataColumn("Subject", Type.GetType("System.String")), New DataColumn("owner",
            Type.GetType("System.Int32")), New DataColumn("owner-subject", Type.GetType("System.String"))})

        'First Student
        fpclass.Rows.Add(New Object() {"Math", 0, "0-Math"})
        fpclass.Rows.Add(New Object() {"English", 0, "0-English"})
        fpclass.Rows.Add(New Object() {"History", 0, "0-History"})
        fpclass.Rows.Add(New Object() {"Music", 0, "0-Music"})
        fpclass.Rows.Add(New Object() {"Science", 0, "0-Science"})

        'Second Student
        fpclass.Rows.Add(New Object() {"Math", 1, "1-Math"})
        fpclass.Rows.Add(New Object() {"English", 1, "1-English"})
        fpclass.Rows.Add(New Object() {"History", 1, "1-History"})
        fpclass.Rows.Add(New Object() {"Music", 1, "1-Music"})
        fpclass.Rows.Add(New Object() {"Science", 1, "1-Science"})

        'Third Student
        fpclass.Rows.Add(New Object() {"Math", 2, "2-Math"})
        fpclass.Rows.Add(New Object() {"English", 2, "2-English"})
        fpclass.Rows.Add(New Object() {"History", 2, "2-History"})
        fpclass.Rows.Add(New Object() {"Music", 2, "2-Music"})
        fpclass.Rows.Add(New Object() {"Science", 2, "2-Science"})

        fpgrades = ds.Tables.Add("Grades")
        fpgrades.Columns.AddRange(New DataColumn() {
                New DataColumn("Week 1", Type.GetType("System.String")),
                New DataColumn("Week 2", Type.GetType("System.String")),
                New DataColumn("Week 3", Type.GetType("System.String")),
                New DataColumn("Week 4", Type.GetType("System.String")),
                New DataColumn("Week 5", Type.GetType("System.String")),
                New DataColumn("Week 6", Type.GetType("System.String")),
                New DataColumn("Week 7", Type.GetType("System.String")),
                New DataColumn("Subject", Type.GetType("System.String")),
                New DataColumn("owner-subject", Type.GetType("System.String"))})

        'First Student
        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "Math", "0-Math"})
        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "English", "0-English"})
        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "History", "0-History"})
        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "Music", "0-Music"})
        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "Science", "0-Science"})

        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "Math", "1-Math"})
        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "English", "1-English"})
        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "History", "1-History"})
        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "Music", "1-Music"})
        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "Science", "1-Science"})

        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "Math", "2-Math"})
        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "English", "2-English"})
        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "History", "2-History"})
        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "Music", "2-Music"})
        fpgrades.Rows.Add(New Object() {"76", "80", "85", "89", "90", "93", "99", "Science", "2-Science"})

        Return fpgrades
    End Function
End Class
