﻿Imports System.IO
Imports System.Text
Imports FarPoint.Win.Spread.CellType
Imports GrapeCity.Spreadsheet

Public Class LetFunctionDemo
    Sub New()
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        fpSpread1.AllowCellOverflow = True
        InitializeBasicUsage()
        InitializeCustomName()
        InitializeNested()
        InitializeSimplify()
        InitializeAggregate()
        InitializePerformance()
        splitContainer1.SplitterDistance = 21
    End Sub

    Private Sub InitializeBasicUsage()
        Dim ws As IWorksheet = fpSpread1.AsWorkbook().Worksheets(0)
        ws.Name = "Basic Usage"
        ' enable dynamic arrays
        ws.Workbook.WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.DynamicArray
        ' create styles for cells
        Dim titleStyle As IStyle = ws.Workbook.Styles.Add("title")
        titleStyle.IncludeFont = True
        titleStyle.Font.Size = 14
        titleStyle.Font.Bold = True
        Dim formulaLabelStyle As IStyle = ws.Workbook.Styles.Add("formulaLabel")
        formulaLabelStyle.Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(255, 242, 204)
        formulaLabelStyle.IncludePatterns = True
        Dim resultLabelStyle As IStyle = ws.Workbook.Styles.Add("resultLabel")
        resultLabelStyle.Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(226, 240, 217)
        resultLabelStyle.IncludePatterns = True
        Dim exampleTitleStyle As IStyle = ws.Workbook.Styles.Add("exampleTitle")
        exampleTitleStyle.Font.Bold = True
        exampleTitleStyle.IncludeFont = True
        Dim formulaExampleStyle As IStyle = ws.Workbook.Styles.Add("formulaExample")
        formulaExampleStyle.Font.Color = GrapeCity.Spreadsheet.Color.FromArgb(192, 0, 0)
        formulaExampleStyle.IncludePatterns = True
        ' initialize cells
        ws.Cells(0, 0, ws.RowCount - 1, ws.ColumnCount - 1).Font.Name = "Calibri"
        ws.Cells(0, 0, ws.RowCount - 1, ws.ColumnCount - 1).Font.Size = 11
        ws.Cells("B2").Text = "LET basic usage"
        ws.Cells("B2").Style = titleStyle
        ws.Cells("B4").Text = "Sample pair variable"
        ws.Cells("B4").Style = exampleTitleStyle
        ws.Cells("B5").Text = "Formula"
        ws.Cells("B5").Style = formulaLabelStyle
        ws.Cells("C5").Formula = "FORMULATEXT(C6)"
        ws.Cells("C5").Style = formulaExampleStyle
        ws.Cells("B6").Text = "Result"
        ws.Cells("B6").Style = resultLabelStyle
        ws.Cells("C6").Formula = "LET(x,2,x+3)"
        ws.Cells("B8").Text = "Multiple pair variables"
        ws.Cells("B8").Style = exampleTitleStyle
        ws.Cells("B9").Text = "Formula"
        ws.Cells("B9").Style = formulaLabelStyle
        ws.Cells("C9").Formula = "FORMULATEXT(C10)"
        ws.Cells("C9").Style = formulaExampleStyle
        ws.Cells("B10").Text = "Result"
        ws.Cells("B10").Style = resultLabelStyle
        ws.Cells("C10").Formula = "LET(x,1,y,2,z,3,x+y+z=x*y*z)"
        ws.Cells("B12").Text = "Invoked formula"
        ws.Cells("B12").Style = exampleTitleStyle
        ws.Cells("B13").Text = "Formula"
        ws.Cells("B13").Style = formulaLabelStyle
        ws.Cells("C13").Formula = "FORMULATEXT(C14)"
        ws.Cells("C13").Style = formulaExampleStyle
        ws.Cells("B14").Text = "Result"
        ws.Cells("B14").Style = resultLabelStyle
        ws.Cells("C14").Formula = "LET(x,1,y,2,SUM(x,y))"
        ws.Cells("B16").Text = "Dynamic Array"
        ws.Cells("B16").Style = exampleTitleStyle
        ws.Cells("B17").Text = "Formula"
        ws.Cells("B17").Style = formulaLabelStyle
        ws.Cells("C17").Formula = "FORMULATEXT(C19)"
        ws.Cells("C17").Style = formulaExampleStyle
        ws.Cells("B18").Text = "Range"
        ws.Cells("B18").Style = formulaLabelStyle
        ws.Cells("C18").Value = 1
        ws.Cells("D18").Value = 2
        ws.Cells("E18").Value = 3
        ws.Cells("B19").Text = "Result"
        ws.Cells("B19").Style = resultLabelStyle
        ' use Formula2 to set dynamic array formula (otherwise it will be automatically modified with '@' to force single-value return)
        ws.Cells("C19").Formula2 = "LET(rows,0,cols,0,OFFSET(C18:E18,rows,cols))"
        ws.Cells("C6").Activate()
    End Sub

    Private Sub InitializeCustomName()
        Dim ws As IWorksheet = fpSpread1.AsWorkbook().Worksheets.Add("Custom Name")
        Dim styles As IStyles = ws.Workbook.Styles ' reuse workbook styles
        ws.Cells(0, 0, ws.RowCount - 1, ws.ColumnCount - 1).Font.Name = "Calibri"
        ws.Cells(0, 0, ws.RowCount - 1, ws.ColumnCount - 1).Font.Size = 11
        ws.Cells("B2").Text = "LET local variable & custom name"
        ws.Cells("B2").Style = styles("title")
        ws.Cells("B3").Text = "This sheet includes a custom name ""user"" which is assigned the value ""Michael"""
        ' add worksheet-level custom name
        ws.Names.Add("user", """Michael""")
        'fpSpread1.Sheets("Custom Name").AddCustomName("user", "Michael", 0, 0, true, "Example custom name for LET function")
        ws.Cells("B4").Text = "Formula"
        ws.Cells("B4").Style = styles("formulaLabel")
        ws.Cells("C4").Formula = "FORMULATEXT(C5)"
        ws.Cells("C4").Style = styles("formulaExample")
        ws.Cells("B5").Text = "Result"
        ws.Cells("B5").Style = styles("resultLabel")
        ws.Cells("C5").Formula2 = "user"
        ws.Cells("B7").Text = "Always use let local variable first."
        ws.Cells("B7").Style = styles("exampleTltle")
        ws.Cells("B8").Text = "Formula"
        ws.Cells("B8").Style = styles("formulaLabel")
        ws.Cells("C8").Formula = "FORMULATEXT(C9)"
        ws.Cells("C8").Style = styles("formulaExample")
        ws.Cells("B9").Text = "Result"
        ws.Cells("B9").Style = styles("resultLabel")
        ws.Cells("C9").Formula2 = "LET(user,""Ivy"",""The actual user Is:  ""&user)"
        ws.Cells("B11").Text = "Use custom name if not available in local variable."
        ws.Cells("B11").Style = styles("exampleTitle")
        ws.Cells("B12").Text = "Formula"
        ws.Cells("B12").Style = styles("formulaLabel")
        ws.Cells("C12").Formula = "FORMULATEXT(C13)"
        ws.Cells("C12").Style = styles("formulaExample")
        ws.Cells("B13").Text = "Result"
        ws.Cells("B13").Style = styles("resultLabel")
        ws.Cells("C13").Formula2 = "LET(user,user,""The actual user Is:  ""&user)"
        ws.Cells("C5").Activate()
    End Sub
    Private Sub InitializeNested()
        Dim ws As IWorksheet = fpSpread1.AsWorkbook().Worksheets.Add("Nested")
        Dim styles As IStyles = ws.Workbook.Styles ' reuse workbook styles
        ws.Cells(0, 0, ws.RowCount - 1, ws.ColumnCount - 1).Font.Name = "Calibri"
        ws.Cells(0, 0, ws.RowCount - 1, ws.ColumnCount - 1).Font.Size = 11
        ws.Cells("B2").Text = "LET nested"
        ws.Cells("B2").Style = styles("title")
        ws.Cells("B4").Text = "Always use the current scope variable."
        ws.Cells("B4").Style = styles("exampleTltle")
        ws.Cells("B5").Text = "Formula"
        ws.Cells("B5").Style = styles("formulaLabel")
        ws.Cells("C5").Formula = "FORMULATEXT(C6)"
        ws.Cells("C5").Style = styles("formulaExample")
        ws.Cells("B6").Text = "Result"
        ws.Cells("B6").Style = styles("resultLabel")
        ws.Cells("C6").Formula = "LET(var,""First Scope"",LET(var,""Second Scope"",var))"
        ws.Cells("B8").Text = "Use the top scope variable if the variable is not found in the current scope."
        ws.Cells("B8").Style = styles("exampleTltle")
        ws.Cells("B9").Text = "Formula"
        ws.Cells("B9").Style = styles("formulaLabel")
        ws.Cells("C9").Formula = "FORMULATEXT(C10)"
        ws.Cells("C9").Style = styles("formulaExample")
        ws.Cells("B10").Text = "Result"
        ws.Cells("B10").Style = styles("resultLabel")
        ws.Cells("C10").Formula = "LET(var,""First Scope"",LET(var,var,var&""(from the second scope)""))"
        ws.Cells("C6").Activate()
    End Sub
    Private Sub InitializeSimplify()
        Dim ws As IWorksheet = fpSpread1.AsWorkbook().Worksheets.Add("Simplify")
        Dim styles As IStyles = ws.Workbook.Styles ' reuse workbook styles
        ws.Cells(0, 0, ws.RowCount - 1, ws.ColumnCount - 1).Font.Name = "Calibri"
        ws.Cells(0, 0, ws.RowCount - 1, ws.ColumnCount - 1).Font.Size = 11
        ws.Cells("B2").Text = "LET simplifies complex formulas"
        ws.Cells("B2").Style = styles("title")
        ws.Cells("B4").Text = "Filter the data to show one person"
        ws.Cells("B4").Style = styles("exampleTltle")
        ws.Cells("B5").Text = "Formula"
        ws.Cells("B5").Style = styles("formulaLabel")
        ws.Cells("C5").Formula = "FORMULATEXT(H8)"
        ws.Cells("C5").Style = styles("formulaExample")
        'ws.Cells("B6:E6").Value = New String(,) { { "Rep", "Region", "Product", "Profit"} }
        ws.SetValue(5, 1, New String(,) {{"Rep", "Region", "Product", "Profit"}})
        ws.Cells("B6:E6").Style = styles.Add("ColumnTitle", styles(BuiltInStyle.Percent20Accent1).Name)
        'ws.Cells("B7:E13").Value = New String(,) _
        ws.SetValue(6, 1, New String(,) _
                {
                    {"Amy", "East", "Apple", "1.33"},
                    {"Fred", "South", "Banana", "0.99"},
                    {"Amy", "West", "Mango", "1.85"},
                    {"Fred", "North", "", "0.82"},
                    {"Fred", "West", "Banana", "1.25"},
                    {"Amy", "East", "Apple", "0.72"},
                    {"Fred", "North", "Mango", "0.54"}
                })
        ws.Cells("G7").Text = "Rep"
        ws.Cells("G7").Style = styles("formulaLabel")
        ws.Cells("H7").Text = "Fred"
        ws.Cells("G8").Text = "Result"
        ws.Cells("G8").Style = styles("resultLabel")
        ws.Cells("H8").Formula2 = "LET(filterCriteria,H7,filteredRange,FILTER(B7:E13,B7:B13=filterCriteria),IF(ISBLANK(filteredRange),"" - "",filteredRange))"
        ws.Cells("B16").Text = "Generate all weekday dates between May 1, 2020 and May 15, 2020"
        ws.Cells("B16").Style = styles("exampleTltle")
        ws.Cells("B17").Text = "Formula"
        ws.Cells("B17").Style = styles("formulaLabel")
        ws.Cells("C17").Formula = "FORMULATEXT(H18)"
        ws.Cells("C17").Style = styles("formulaExample")
        ws.Cells("B18").Text = "Start"
        ws.Cells("C18").Text = "01-May-20"
        ws.Cells("B19").Text = "End"
        ws.Cells("B18:B19").Style = styles("ColumnTitle")
        ws.Cells("C19").Text = "15-May-20"
        ws.Cells("C18").NumberFormat = ws.Cells("C19").NumberFormat
        ws.Cells("G18").Text = "Result"
        ws.Cells("G18").Style = styles("resultLabel")
        ws.Cells("H18").Formula2 = "LET(dates,SEQUENCE(C19-C18+1,1,C18,1),FILTER(dates,WEEKDAY(dates,2)<6))"
        ws.Columns(2).ColumnWidth = 75
        ws.Cells("H18:H28").NumberFormat = ws.Cells("C19").NumberFormat
        ws.Columns("H:H").AutoFit()
        ws.Cells("H8").Activate()
    End Sub
    Private Sub InitializeAggregate()
        Dim ws As IWorksheet = fpSpread1.AsWorkbook().Worksheets.Add("Aggregate")
        Dim styles As IStyles = ws.Workbook.Styles ' reuse workbook styles
        ws.Cells(0, 0, ws.RowCount - 1, ws.ColumnCount - 1).Font.Name = "Calibri"
        ws.Cells(0, 0, ws.RowCount - 1, ws.ColumnCount - 1).Font.Size = 11
        ws.Cells("B2").Text = "Support Engineer Employment Length"
        ' create inherited style for Aggregate title with center alignment for merged cell
        ws.Cells("B2").Style = styles("title")
        'ws.Cells("B3:E7").Value = New String(,) 
        ws.SetValue(2, 1, New String(,) _
            {{"Support Engineer", "Start Date", "Time with Company", "Salary"},
                {"Bob", "5/25/2014", "5+ years", "2790"},
                {"Jim", "7/20/2019", "1-4 years", "2216"},
                {"Kevin", "3/1/2017", "1-4 years", "2498"},
                {"Sarah", "7/14/2020", "< 1 year", "1989"}})
        ws.SetValue(3, 4, New Integer(,) {{2790}, {2216}, {2498}, {1989}})
        ws.Cells("B3:E7").CreateTable(True, "Table2", "TableStyleMedium7")
        ws.Cells("B9").Text = "Salary Calculations"
        'create combo box cell type for selecting aggregation function
        Dim combo As ComboBoxCellType = New ComboBoxCellType()
        combo.EditorValue = EditorValue.ItemData
        combo.Items = New String() {"Min", "Max", "Average"}
        combo.ItemData = New String() {"5", "4", "1"}
        combo.DropDownWhenStartEditing = True
        combo.StopEditingAfterDropDownItemSelected = True
        fpSpread1.Sheets("Aggregate").Cells(8, 2).CellType = combo
        ws.Cells("C9").Value = 1
        ws.Cells("D9").Formula = "LET(data,Table2[Salary],aggregate,C9,SUBTOTAL(aggregate,data))"
        ws.Cells("B10").Text = "Formula"
        ws.Cells("B10").Style = styles("formulaLabel")
        ws.Cells("C10").Formula = "FORMULATEXT(D9)"
        ws.Cells("C10").Style = styles("formulaExample")
        ws.Columns(2).ColumnWidth = 70
        ws.Cells("D9").Activate()
    End Sub
    Private Sub InitializePerformance()
        Dim ws1 As IWorksheet = fpSpread1.AsWorkbook().Worksheets.Add("Performance")
        fpSpread1.Sheets("Performance").TitleInfo.Text = "Using LET Function - Click Recalculate to compare performance (see formula in cell O2)"
        fpSpread1.Sheets("Performance").TitleInfo.Visible = True
        fpSpread1.Sheets("Performance").RowCount = 501
        Dim s As MemoryStream = New MemoryStream()
        s.Write(Encoding.ASCII.GetBytes(My.Resources.ExcelTemplates.us_500), 0, My.Resources.ExcelTemplates.us_500.Length)
        s.Seek(0, SeekOrigin.Begin)
        fpSpread1.Sheets("Performance").LoadTextFile(s, FarPoint.Win.Spread.TextFileFlags.None, FarPoint.Win.Spread.Model.IncludeHeaders.None, vbCr, ",", """")
        ws1.Cells("A1:L501").CreateTable(True, "Table1")
        ws1.Columns(12).ColumnWidth = 4
        ws1.Cells("N2").Formula2 = "SORT(UNIQUE(Table1[state]))"
        Dim rows As Integer = CType(ws1.Workbook.WorkbookSet.CalculationEngine.Evaluate("ROWS(SORT(UNIQUE(Table1[state])))", ws1, New GrapeCity.Spreadsheet.CellReference(1, 13)).GetNumber(), Integer)
        ws1.Cells(1, 14, rows, 14).Formula2 = "LET(cities,ROWS(UNIQUE(FILTER(Table1[city],Table1[state]=$N2,""""))),If(cities<10,""Less than 10."",If(cities<20,""Between 10 And 19."",If(cities<30,""Between 20 And 29"",""30 Or more.""))))"
        fpSpread1.AddViewport(5, -1, 0)
        fpSpread1.SetViewportLeftColumn(5, 1, 13)
        ws1.Cells(1, 14).Activate()

        fpSpread2.AllowCellOverflow = True
        Dim ws2 As IWorksheet = fpSpread2.AsWorkbook().Worksheets(0)
        ws2.Workbook.WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.DynamicArray
        ws2.Name = "Performance"
        fpSpread2.Sheets("Performance").TitleInfo.Text = "Not Using Let Function see formula In cell O2"
        fpSpread2.Sheets("Performance").TitleInfo.Visible = True
        fpSpread2.Sheets("Performance").RowCount = 501
        s = New MemoryStream()
        s.Write(Encoding.ASCII.GetBytes(My.Resources.ExcelTemplates.us_500), 0, My.Resources.ExcelTemplates.us_500.Length)
        s.Seek(0, SeekOrigin.Begin)
        fpSpread2.Sheets("Performance").LoadTextFile(s, FarPoint.Win.Spread.TextFileFlags.None, FarPoint.Win.Spread.Model.IncludeHeaders.None, vbCr, ",", """")
        ws2.Cells("A1: L501").CreateTable(True, "Table1")
        ws2.Columns(12).ColumnWidth = 4
        ws2.Cells("N2").Formula2 = "SORT(UNIQUE(Table1[state]))"
        ws2.Cells(1, 14, rows, 14).Formula2 = "IF(ROWS(UNIQUE(FILTER(Table1[city],Table1[state]=$N2,"""")))<10,""Less than 10."",IF(ROWS(UNIQUE(FILTER(Table1[city],Table1[state]=$N2,"""")))<20,""Between 10 And 19."",IF(ROWS(UNIQUE(FILTER(Table1[city],Table1[state]=$N2,"""")))<30,""Between 20 And 29"",""30 Or more."")))"
        fpSpread2.AddViewport(-1, 0)
        fpSpread2.SetViewportLeftColumn(1, 13)
        ws1.Cells(1, 14).Activate()

        s.Dispose()
    End Sub

    Private Sub recalculateToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles recalculateToolStripMenuItem.Click
        Dim t1 As Integer, t2 As Integer, t3 As Integer

        fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalculationOnDemand = False
        fpSpread2.AsWorkbook().WorkbookSet.CalculationEngine.CalculationOnDemand = False
        t1 = Environment.TickCount
        fpSpread1.ActiveSheet.RecalculateAll()
        t2 = Environment.TickCount
        fpSpread2.ActiveSheet.RecalculateAll()
        t3 = Environment.TickCount
        fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalculationOnDemand = True
        fpSpread2.AsWorkbook().WorkbookSet.CalculationEngine.CalculationOnDemand = True

        fpSpread1.ActiveSheet.TitleInfo.Text = "Using LET Function: " + (t2 - t1).ToString() + " ms (see formula in cell O2)"
        fpSpread2.ActiveSheet.TitleInfo.Text = "Not using LET Function: " + (t3 - t2).ToString() + " ms (see formula in cell O2)"
    End Sub

    Private Sub fpSpread1_ActiveSheetChanged(sender As Object, e As EventArgs) Handles fpSpread1.ActiveSheetChanged
        If fpSpread1.ActiveSheetIndex = 5 Then
            SuspendLayout()
            splitContainer3.Panel2Collapsed = False
            menuStrip1.Visible = True
            ResumeLayout()
        Else
            SuspendLayout()
            splitContainer3.Panel2Collapsed = True
            menuStrip1.Visible = False
            ResumeLayout()
        End If
    End Sub

    Private Sub fpSpread1_Enter(sender As Object, e As EventArgs) Handles fpSpread1.Enter
        nameBox1.Detach()
        formulaTextBox1.Detach()
        nameBox1.Attach(fpSpread1)
        formulaTextBox1.Attach(fpSpread1)
    End Sub

    Private Sub fpSpread2_Enter(sender As Object, e As EventArgs) Handles fpSpread2.Enter
        nameBox1.Detach()
        formulaTextBox1.Detach()
        nameBox1.Attach(fpSpread2)
        formulaTextBox1.Attach(fpSpread2)
    End Sub
End Class
