﻿Imports System.Drawing
Imports System.Windows.Forms
Imports FarPoint.Win.Spread
Imports FarPoint.Win
Imports FarPoint.Win.Spread.Model
Imports FarPoint.Win.Spread.CellType

Imports Spread.Common.Features.Spread.Common.Features.Sample

Partial Public Class ExpenseBudget
    Inherits BaseSampleControl
#Region "ctor"
    Public Sub New()
        InitializeComponent()

        AddHandler Load, AddressOf ExpenseBudget_Load
    End Sub
#End Region

#Region "Event handlers"
    Private Sub ExpenseBudget_Load(sender As Object, e As EventArgs)
        SetupSheet(fpSpread.ActiveSheet)
        SetSheetData(fpSpread.ActiveSheet)
        SetSheetRules(fpSpread.ActiveSheet)
    End Sub
#End Region

#Region "Private methods"
    Private Sub SetupSheet(sheet As SheetView)
        SetSheetProperties(sheet)

        sheet.AddSpanCell(0, 1, 1, 4)
        sheet.AddSpanCell(0, 5, 1, 2)
        sheet.AddSpanCell(1, 1, 1, 6)
        sheet.AddSpanCell(2, 1, 1, 6)
        sheet.AddSpanCell(3, 1, 1, 2)
        sheet.AddSpanCell(16, 1, 1, 6)
        sheet.AddSpanCell(17, 1, 1, 6)

        sheet.Cells(0, 1).Value = "Expense Budget"
        sheet.Cells(0, 1).Font = New Font("Calibri", 28, FontStyle.Regular)
        sheet.Cells(0, 5).Value = "CONTOSO, 2013"
        sheet.Cells(0, 5).Font = New Font("Calibri", 11, FontStyle.Regular)
        sheet.Cells(0, 5).ForeColor = Color.FromArgb(64, 64, 64)
        sheet.Cells(0, 5).HorizontalAlignment = CellHorizontalAlignment.Right
        sheet.Cells(0, 5).VerticalAlignment = CellVerticalAlignment.Bottom

        sheet.Cells(3, 1).Value = "OPERATING BUDGET"
        sheet.Cells(3, 1).Font = New Font("Calibri", 13, FontStyle.Bold)
        sheet.Cells(3, 1).ForeColor = Color.FromArgb(64, 64, 64)

        sheet.Cells(4, 1, 15, 1).HorizontalAlignment = CellHorizontalAlignment.Center
        sheet.Cells(4, 1, 4, 6).Font = New Font("Calibri", 10, FontStyle.Bold)
        sheet.Cells(4, 1, 4, 6).Border = New LineBorder(Color.FromArgb(217, 217, 217), 1, False, True, False, False)
        sheet.Cells(15, 1, 15, 6).Font = New Font("Calibri", 10, FontStyle.Bold)
        sheet.Cells(15, 1, 15, 6).Border = New LineBorder(Color.FromArgb(217, 217, 217), 1, False, False, False, True)
        sheet.Cells(3, 1, 3, 2).Border = New LineBorder(Color.FromArgb(217, 217, 217), 1, True, True, True, False)
        sheet.Cells(4, 1, 15, 1).Border = New LineBorder(Color.FromArgb(217, 217, 217), 1, True, False, False, False)
        sheet.Cells(4, 1, 15, 1).HorizontalAlignment = CellHorizontalAlignment.Center
        sheet.Cells(4, 6, 15, 6).Border = New LineBorder(Color.FromArgb(217, 217, 217), 1, False, False, True, False)
        sheet.Cells(5, 1, 14, 6).Border = New LineBorder(Color.FromArgb(217, 217, 217), 1, False, False, False, True)

        sheet.Cells(1, 1, 1, 6).Border = New ComplexBorder(Nothing, New ComplexBorderSide(Color.FromArgb(217, 217, 217), 1), Nothing, New ComplexBorderSide(Color.FromArgb(217, 217, 217), 3))
        sheet.Cells(17, 1, 17, 6).Border = New ComplexBorder(Nothing, New ComplexBorderSide(Color.FromArgb(217, 217, 217), 3), Nothing, New ComplexBorderSide(Color.FromArgb(217, 217, 217), 1))

        sheet.Cells(4, 1).Border = New LineBorder(Color.FromArgb(217, 217, 217), 1, True, True, False, False)
        sheet.Cells(4, 6).Border = New LineBorder(Color.FromArgb(217, 217, 217), 1, False, True, True, False)
        sheet.Cells(15, 1).Border = New LineBorder(Color.FromArgb(217, 217, 217), 1, True, False, False, True)
        sheet.Cells(15, 6).Border = New LineBorder(Color.FromArgb(217, 217, 217), 1, False, False, True, True)

        Dim currencyCell1 As New CurrencyCellType()
        currencyCell1.DecimalPlaces = 2

        Dim currencyCell2 As New CurrencyCellType()
        currencyCell2.DecimalPlaces = 2
        currencyCell2.NegativeRed = True

        Dim percentCell1 As New PercentCellType()
        percentCell1.DecimalPlaces = 0

        Dim percentCell2 As New PercentCellType()
        percentCell2.DecimalPlaces = 1
        percentCell2.NegativeRed = True

        sheet.Cells(5, 3, 14, 5).CellType = currencyCell1
        sheet.Cells(5, 6, 14, 6).CellType = percentCell1
        sheet.Cells(15, 5).CellType = currencyCell2
        sheet.Cells(15, 6).CellType = percentCell2
    End Sub

    Private Sub SetSheetProperties(sheet As SheetView)
        sheet.DefaultStyle.VerticalAlignment = CellVerticalAlignment.Center
        sheet.DefaultStyle.Font = New Font("Calibri", 10, FontStyle.Regular)
        sheet.DefaultStyle.ForeColor = Color.FromArgb(68, 84, 106)
        sheet.VerticalGridLine = New GridLine(GridLineType.None)
        sheet.HorizontalGridLine = New GridLine(GridLineType.None)

        sheet.SetRowHeight(0, 74)
        sheet.SetRowHeight(1, 8)
        sheet.SetRowHeight(2, 20)
        sheet.SetMultipleRowHeights(3, 14, 20)
        sheet.SetRowHeight(17, 8)

        Dim colWidths As Integer() = {18, 83, 111, 84, 85, 126,
            129}
        For col As Integer = 0 To 6
            sheet.SetColumnWidth(col, colWidths(col))
        Next
    End Sub

    Private Sub SetSheetData(sheet As SheetView)
        Dim data As Object(,) = {{"STATUS", "OPERATING", "BUDGET", "ACTUAL", "DIFFERENCE ($)", "DIFFERENCE (%)"}, {0, "Advertising", 600, 545, 0, 0}, {0, "Debts", 125, 150, 0, 0}, {0, "Benefits", 100, 100, 0, 0}, {0, "Supplies", 100, 90, 0, 0}, {0, "Postage", 150, 145, 0, 0},
            {0, "Rent or mortgage", 1000, 1000, 0, 0}, {0, "Sales expenses", 500, 630, 0, 0}, {0, "Taxes", 350, 375, 0, 0}, {0, "Utilities", 400, 370, 0, 0}, {0, "Other", 500, 435, 0, 0}, {0, "Total Expenses", 0, 0, 0, 0}}

        For row As Integer = 4 To 15
            For col As Integer = 1 To 6
                sheet.Cells(row, col).Value = data(row - 4, col - 1)
            Next
        Next
        sheet.FpSpread.AllowUserFormulas = True
        Dim table As TableView = sheet.AddTable("tblOperatingExpense", 4, 1, 11, 6, TableStyle.TableStyleLight1)

        sheet.Cells(15, 3).Formula = "Sum($D$6:$D$15)"
        sheet.Cells(15, 4).Formula = "Sum($E$6:$E$15)"
        sheet.Cells(15, 5).Formula = "Sum($F$6:$F$15)"
        For row As Integer = 5 To 14
            sheet.Cells(row, 1).Formula = "IFERROR([ACTUAL] / [BUDGET], """")"
            sheet.Cells(row, 5).Formula = "[BUDGET] - [ACTUAL]"
            sheet.Cells(row, 6).Formula = "IFERROR([DIFFERENCE ($)] / [BUDGET], """")"
        Next
        sheet.Cells(15, 1).Formula = "$G$16"
        sheet.Cells(15, 6).Formula = "IFERROR($F$16 / $D$16, """")"
    End Sub

    Private Sub SetSheetRules(sheet As SheetView)
        Dim iconSetRule1 As New IconSetConditionalFormattingRule(ConditionalFormattingIconSetStyle.ThreeCircledSymbols)
        iconSetRule1.ShowConditionalFormatOnly = True
        iconSetRule1.IconRuleSet.Add(New ConditionalFormattingIconValue(-1, ConditionalFormattingValueType.Number, False))
        iconSetRule1.IconRuleSet.Add(New ConditionalFormattingIconValue(1, ConditionalFormattingValueType.Number, True))

        Dim iconSetRule2 As New IconSetConditionalFormattingRule(ConditionalFormattingIconSetStyle.ThreeUnCircledSymbols)
        iconSetRule2.ShowConditionalFormatOnly = True
        iconSetRule2.Reverse()
        iconSetRule2.IconRuleSet.Add(New ConditionalFormattingIconValue(-1, ConditionalFormattingValueType.Number, False))
        iconSetRule2.IconRuleSet.Add(New ConditionalFormattingIconValue(1, ConditionalFormattingValueType.Number, True))

        Dim dataBarRule1 As New DatabarConditionalFormattingRule()
        dataBarRule1.Minimum = New ConditionalFormattingValue(ConditionalFormattingValueType.AutoMin)
        dataBarRule1.Maximum = New ConditionalFormattingValue(ConditionalFormattingValueType.AutoMax)
        dataBarRule1.FillColor = Color.Orange
        dataBarRule1.Gradient = True
        dataBarRule1.ShowBorder = True
        dataBarRule1.BorderColor = Color.Orange
        dataBarRule1.Direction = RightToLeft.Yes

        Dim dataBarRule2 As New DatabarConditionalFormattingRule()
        dataBarRule2.Minimum = New ConditionalFormattingValue(ConditionalFormattingValueType.AutoMin)
        dataBarRule2.Maximum = New ConditionalFormattingValue(ConditionalFormattingValueType.AutoMax)
        dataBarRule2.FillColor = Color.FromArgb(0, 138, 239)
        dataBarRule2.Gradient = True
        dataBarRule2.ShowBorder = True
        dataBarRule2.BorderColor = Color.FromArgb(0, 138, 239)

        Dim dataBarRule3 As New DatabarConditionalFormattingRule()
        dataBarRule3.Minimum = New ConditionalFormattingValue(ConditionalFormattingValueType.Min)
        dataBarRule3.Maximum = New ConditionalFormattingValue(ConditionalFormattingValueType.Max)
        dataBarRule3.FillColor = Color.FromArgb(91, 155, 213)
        dataBarRule3.Gradient = True
        dataBarRule3.ShowBorder = True
        dataBarRule3.BorderColor = Color.FromArgb(91, 155, 213)
        dataBarRule3.UseNegativeFillColor = True
        dataBarRule3.NegativeFillColor = Color.FromArgb(237, 125, 49)
        dataBarRule3.UseNegativeBorderColor = True
        dataBarRule3.NegativeBorderColor = Color.FromArgb(237, 125, 49)
        dataBarRule3.AxisPosition = DataBarAxisPosition.Middle

        sheet.SetConditionalFormatting(New CellRange() {New CellRange(5, 4, 10, 1)}, dataBarRule1)
        sheet.SetConditionalFormatting(New CellRange() {New CellRange(5, 3, 10, 1)}, dataBarRule2)
        sheet.SetConditionalFormatting(New CellRange() {New CellRange(5, 6, 10, 1)}, dataBarRule3)
        sheet.SetConditionalFormatting(New CellRange() {New CellRange(15, 1, 1, 1)}, iconSetRule1)
        sheet.SetConditionalFormatting(New CellRange() {New CellRange(5, 1, 10, 1)}, iconSetRule2)
    End Sub
#End Region
End Class

'
'
'

Partial Class ExpenseBudget
	''' <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 Shadows 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 flatScrollBarRenderer1 As New FarPoint.Win.Spread.FlatScrollBarRenderer()
		Dim flatScrollBarRenderer2 As New FarPoint.Win.Spread.FlatScrollBarRenderer()
		Me.fpSpread = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None)
		Me.fpSpread_Sheet1 = New FarPoint.Win.Spread.SheetView()
		Me.pnlMain.SuspendLayout()
		DirectCast(Me.fpSpread, System.ComponentModel.ISupportInitialize).BeginInit()
		DirectCast(Me.fpSpread_Sheet1, System.ComponentModel.ISupportInitialize).BeginInit()
		Me.SuspendLayout()
		' 
		' pnlMain
		' 
		Me.pnlMain.Controls.Add(Me.fpSpread)
		' 
		' fpSpread
		' 
		Me.fpSpread.AccessibleDescription = ""
		Me.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill
		Me.fpSpread.HorizontalScrollBar.Buttons = New FarPoint.Win.Spread.FpScrollBarButtonCollection("BackwardLineButton,ThumbTrack,ForwardLineButton")
		Me.fpSpread.HorizontalScrollBar.Name = ""
		flatScrollBarRenderer1.ArrowColor = System.Drawing.Color.FromArgb(CInt(CByte(121)), CInt(CByte(121)), CInt(CByte(121)))
		flatScrollBarRenderer1.BackColor = System.Drawing.Color.FromArgb(CInt(CByte(255)), CInt(CByte(255)), CInt(CByte(255)))
		flatScrollBarRenderer1.BorderActiveColor = System.Drawing.Color.FromArgb(CInt(CByte(171)), CInt(CByte(171)), CInt(CByte(171)))
		flatScrollBarRenderer1.TrackBarBackColor = System.Drawing.Color.FromArgb(CInt(CByte(219)), CInt(CByte(219)), CInt(CByte(219)))
		Me.fpSpread.HorizontalScrollBar.Renderer = flatScrollBarRenderer1
		Me.fpSpread.Location = New System.Drawing.Point(0, 0)
		Me.fpSpread.Name = "fpSpread"
		Me.fpSpread.Sheets.AddRange(New FarPoint.Win.Spread.SheetView() {Me.fpSpread_Sheet1})
		Me.fpSpread.Size = New System.Drawing.Size(477, 487)
		Me.fpSpread.TabIndex = 0
		Me.fpSpread.VerticalScrollBar.Buttons = New FarPoint.Win.Spread.FpScrollBarButtonCollection("BackwardLineButton,ThumbTrack,ForwardLineButton")
		Me.fpSpread.VerticalScrollBar.Name = ""
		flatScrollBarRenderer2.ArrowColor = System.Drawing.Color.FromArgb(CInt(CByte(121)), CInt(CByte(121)), CInt(CByte(121)))
		flatScrollBarRenderer2.BackColor = System.Drawing.Color.FromArgb(CInt(CByte(255)), CInt(CByte(255)), CInt(CByte(255)))
		flatScrollBarRenderer2.BorderActiveColor = System.Drawing.Color.FromArgb(CInt(CByte(171)), CInt(CByte(171)), CInt(CByte(171)))
		flatScrollBarRenderer2.TrackBarBackColor = System.Drawing.Color.FromArgb(CInt(CByte(219)), CInt(CByte(219)), CInt(CByte(219)))
		Me.fpSpread.VerticalScrollBar.Renderer = flatScrollBarRenderer2
		' 
		' fpSpread_Sheet1
		' 
		Me.fpSpread_Sheet1.Reset()
		Me.fpSpread_Sheet1.SheetName = "Sheet1"
		' Formulas and custom names must be loaded with R1C1 reference style
		Me.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
		Me.fpSpread_Sheet1.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.SheetCornerStyle.BackColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty
		Me.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
		' 
		' ExpenseBudget
		' 
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.Name = "ExpenseBudget"
		Me.pnlMain.ResumeLayout(False)
		DirectCast(Me.fpSpread, System.ComponentModel.ISupportInitialize).EndInit()
		DirectCast(Me.fpSpread_Sheet1, System.ComponentModel.ISupportInitialize).EndInit()
		Me.ResumeLayout(False)

	End Sub

	#End Region

	Private fpSpread As FarPoint.Win.Spread.FpSpread
	Private fpSpread_Sheet1 As FarPoint.Win.Spread.SheetView
End Class
