Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Data
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports System.Windows.Forms
Imports FarPoint.Win.Spread
Imports FarPoint.Win.Spread.CellType
Imports Spread.Common.Features.Spread.Common.Features.Sample

Namespace Features
    Partial Public Class ArrayFormulasDemo
        Inherits BaseSampleControl
        Public Sub New()
            InitializeComponent()
            InitSpreadSheet()
        End Sub
        Private Sub InitSpreadSheet()
            Name = "Array Formulas"
            sheetView.Rows.[Default].Height = 25
            sheetView.Columns.[Default].Width = 90

            sheetView.SetClip(0, 0, 1, 6, "Customer" & vbTab & "Product" & vbTab & "Sale number ( items)" & vbTab & "Price ($)" & vbTab & "Total no tax ($)" & vbTab & "Total with tax ($)")
            sheetView.SetClip(1, 0, 8, 2, "Mike" & vbTab & "Apple" & vbLf & "Sally" & vbTab & "Lemon" & vbLf & "Nancy" & vbTab & "Lemon" & vbLf & "Jane" & vbTab & "Lemon" & vbLf & "Sarah" & vbTab & "Apple" & vbLf & "Lucy" & vbTab & "Apple" & vbLf & "Eric" & vbTab & "Lemon" & vbLf & "Tom" & vbTab & "Apple")
            sheetView.Cells("C2:C9").FormulaArray = "{2;10;100;19;34;15;20;14}"
            sheetView.Cells("D2:D9").Formula = "IFS(B2=""Apple"",10,B2=""Lemon"",20)"

            sheetView.Cells("D2").Note = "Excel 2016 doesn't support the IFS function unless you have the Office 365 subscrition and suffuciently updated Excel version installed, so if want to export to older versions of Excel, use nested IF functions instead: IF(B2=""Apple"",10,(IF(B2=""Lemon"",20,"""")))"
            sheetView.Cells("E2:E9").Formula = "C2*D2"
            sheetView.Cells("F2:F9").FormulaArray = "C2:C9*D2:D9*1.08"
            sheetView.Cells("E10").Text = "Totals"
            sheetView.Cells("F10").Formula = "SUM(F2:F9)"

            sheetView.Cells("D2:F9").CellType = New CurrencyCellType()
            sheetView.Cells("F10").CellType = New CurrencyCellType()
            sheetView.Cells("A1:F1").BackColor = Color.LightBlue
            sheetView.Cells("A1:F1").ForeColor = Color.Navy
            sheetView.Cells("A2:B9").BackColor = Color.LightGreen
            sheetView.Cells("F2:F10").BackColor = Color.LightGreen
            sheetView.Cells("E10").BackColor = Color.LightGreen
            sheetView.Cells("C2:E9").BackColor = Color.LightYellow

            sheetView.Columns(2).Width = 110
            spread.DefaultSkin = DefaultSpreadSkins.Office2016Colorful
            sheetView.DefaultStyle.NoteIndicatorSize = New Size(7, 7)
            spread.CellNoteIndicatorRenderer = New TriangleNoteIndicatorRenderer()
        End Sub
    End Class
End Namespace

'
'
'
ArrayFormulasDemo.Designer.vb
'
'
'

Namespace Features
	Partial Class ArrayFormulasDemo
		''' <summary> 
		''' Required designer variable.
		''' </summary>
		Private components As System.ComponentModel.IContainer = Nothing

        ''' <summary> 
        ''' Clean up any resources being used.
        ''' </summary>
        ''' <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        Protected Overloads Sub Dispose(disposing As Boolean)
            If disposing AndAlso (components IsNot Nothing) Then
                components.Dispose()
            End If
            MyBase.Dispose(disposing)
        End Sub

#Region "Component Designer generated code"

        ''' <summary> 
        ''' Required method for Designer support - do not modify 
        ''' the contents of this method with the code editor.
        ''' </summary>
        Private Sub InitializeComponent()
            Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(ArrayFormulasDemo))
            Me.panelFormulaBar = New System.Windows.Forms.Panel()
            Me.splitContainer1 = New System.Windows.Forms.SplitContainer()
            Me.nameBox1 = New FarPoint.Win.Spread.NameBox()
            Me.formulaTextBox1 = New FarPoint.Win.Spread.FormulaTextBox()
            Me.spread = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None, CType(resources.GetObject("pnlMain.Controls"), Object))
            Me.sheetView = Me.spread.GetSheet(0)
            Me.pnlMain.SuspendLayout()
            Me.panelFormulaBar.SuspendLayout()
            CType(Me.splitContainer1, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.splitContainer1.Panel1.SuspendLayout()
            Me.splitContainer1.Panel2.SuspendLayout()
            Me.splitContainer1.SuspendLayout()
            CType(Me.spread, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.SuspendLayout()
            '
            'pnlMain
            '
            Me.pnlMain.Controls.Add(Me.spread)
            Me.pnlMain.Controls.Add(Me.panelFormulaBar)
            '
            'panelFormulaBar
            '
            Me.panelFormulaBar.Controls.Add(Me.splitContainer1)
            Me.panelFormulaBar.Dock = System.Windows.Forms.DockStyle.Top
            Me.panelFormulaBar.Location = New System.Drawing.Point(0, 0)
            Me.panelFormulaBar.Name = "panelFormulaBar"
            Me.panelFormulaBar.Size = New System.Drawing.Size(477, 23)
            Me.panelFormulaBar.TabIndex = 6
            '
            'splitContainer1
            '
            Me.splitContainer1.Dock = System.Windows.Forms.DockStyle.Fill
            Me.splitContainer1.Location = New System.Drawing.Point(0, 0)
            Me.splitContainer1.Name = "splitContainer1"
            '
            'splitContainer1.Panel1
            '
            Me.splitContainer1.Panel1.Controls.Add(Me.nameBox1)
            Me.splitContainer1.Panel1MinSize = 121
            '
            'splitContainer1.Panel2
            '
            Me.splitContainer1.Panel2.Controls.Add(Me.formulaTextBox1)
            Me.splitContainer1.Size = New System.Drawing.Size(477, 23)
            Me.splitContainer1.SplitterDistance = 121
            Me.splitContainer1.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(121, 21)
            Me.nameBox1.TabIndex = 0
            Me.nameBox1.Attach(spread)
            '
            'formulaTextBox1
            '
            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(352, 23)
            Me.formulaTextBox1.TabIndex = 0
            Me.formulaTextBox1.Attach(spread)
            '
            'spread
            '
            Me.spread.AccessibleDescription = "Book1, Sheet1, Row 0, Column 0"
            Me.spread.Dock = System.Windows.Forms.DockStyle.Fill
            Me.spread.Font = New System.Drawing.Font("Calibri", 11.0!)
            Me.spread.Location = New System.Drawing.Point(0, 23)
            Me.spread.Name = "spread"
            Me.spread.Size = New System.Drawing.Size(477, 464)
            Me.spread.TabIndex = 7
            '
            'ArrayFormulasDemo
            '
            Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.Name = "ArrayFormulasDemo"
            Me.pnlMain.ResumeLayout(False)
            Me.panelFormulaBar.ResumeLayout(False)
            Me.splitContainer1.Panel1.ResumeLayout(False)
            Me.splitContainer1.Panel2.ResumeLayout(False)
            CType(Me.splitContainer1, System.ComponentModel.ISupportInitialize).EndInit()
            Me.splitContainer1.ResumeLayout(False)
            CType(Me.spread, System.ComponentModel.ISupportInitialize).EndInit()
            Me.ResumeLayout(False)

        End Sub

#End Region

        Private splitContainer1 As System.Windows.Forms.SplitContainer
        Protected Friend panelFormulaBar As System.Windows.Forms.Panel
        Protected Friend nameBox1 As FarPoint.Win.Spread.NameBox
        Protected Friend formulaTextBox1 As FarPoint.Win.Spread.FormulaTextBox
        Friend WithEvents spread As FarPoint.Win.Spread.FpSpread
        Friend WithEvents sheetView As FarPoint.Win.Spread.SheetView
    End Class
End Namespace

