Imports System.Drawing
Imports FarPoint.Win.Spread
Imports FarPoint.Win
Imports FarPoint.Win.Spread.Model
Imports FarPoint.Win.Chart
Imports Spread.Common.Features.Spread.Common.Features.Sample

Namespace Features
    Partial Public Class IntroPrint
        Inherits BaseSampleControl
#Region "ctor"
        Public Sub New()
            InitializeComponent()
            Name = "Introduction"

            InitSpreadSheet(fpSpread.ActiveSheet)

            AddHandler btnPrint.Click, AddressOf BtnPrint_Click
        End Sub
#End Region

#Region "Private methods"
        Private Sub InitSpreadSheet(sheet As SheetView)
            fpSpread.SuspendLayout()

            fpSpread.AllowCellOverflow = True
            sheet.SetMultipleRowHeights(0, sheet.RowCount, 24)

            sheet.AddSpanCell(1, 1, 1, 3)
            sheet.Cells(1, 1).Text = "Store"
            sheet.AddSpanCell(1, 4, 1, 7)
            sheet.Cells(1, 4).Text = "Goods"
            sheet.AddSpanCell(2, 1, 1, 2)
            sheet.Cells(2, 1).Text = "Area"
            sheet.AddSpanCell(2, 3, 2, 1)
            sheet.Cells(2, 3).Text = "ID"
            sheet.AddSpanCell(2, 4, 1, 2)
            sheet.Cells(2, 4).Text = "Fruits"
            sheet.AddSpanCell(2, 6, 1, 2)
            sheet.Cells(2, 6).Text = "Vegetables"
            sheet.AddSpanCell(2, 8, 1, 2)
            sheet.Cells(2, 8).Text = "Foods"
            sheet.AddSpanCell(2, 10, 2, 1)
            sheet.Cells(2, 10).Text = "Total"

            sheet.Cells(3, 1).Text = "State"
            sheet.Cells(3, 2).Text = "City"
            sheet.Cells(3, 4).Text = "Grape"
            sheet.Cells(3, 5).Text = "Apple"
            sheet.Cells(3, 6).Text = "Potato"
            sheet.Cells(3, 7).Text = "Tomato"
            sheet.Cells(3, 8).Text = "Sandwich"
            sheet.Cells(3, 9).Text = "Hamburger"

            sheet.AddSpanCell(4, 1, 7, 1)
            sheet.AddSpanCell(4, 2, 3, 1)
            sheet.AddSpanCell(7, 2, 3, 1)
            sheet.AddSpanCell(10, 2, 1, 2)
            sheet.Cells(10, 2).Text = "Sub Total:"
            sheet.AddSpanCell(11, 1, 7, 1)
            sheet.AddSpanCell(11, 2, 3, 1)
            sheet.AddSpanCell(14, 2, 3, 1)
            sheet.AddSpanCell(17, 2, 1, 2)
            sheet.Cells(17, 2).Text = "Sub Total:"
            sheet.AddSpanCell(18, 1, 1, 3)
            sheet.Cells(18, 1).Text = "Total:"

            sheet.Cells(4, 1).Text = "NC"
            sheet.Cells(4, 2).Text = "Raleigh"
            sheet.Cells(7, 2).Text = "Charlotte"
            For row As Integer = 4 To 9
                sheet.Cells(row, 3).Text = $"{(row - 3):d3}"
            Next
            sheet.Cells(11, 1).Text = "PA"
            sheet.Cells(11, 2).Text = "Philadelphia"
            sheet.Cells(14, 2).Text = "Pittsburgh"
            For row As Integer = 11 To 16
                sheet.Cells(row, 3).Text = $"{(row - 4):d3}"
            Next

            For col As Integer = 4 To 9
                Dim colChar As Char = ChrW(Asc("A") + col)
                sheet.Cells(10, col).Formula = "SUM(" & colChar & "5: " & colChar & "10)"
                sheet.Cells(17, col).Formula = "SUM(" & colChar & "12:" & colChar & "17)"
            Next

            For i As Integer = 0 To 13
                sheet.Cells(4 + i, 10).Formula = "SUM(E" & 5 + i & ":J" & 5 + i & ")"
            Next

            For col As Integer = 4 To 10
                Dim colChar As Char = ChrW(Asc("A") + col)
                sheet.Cells(18, col).Formula = "" & colChar & "11 + " & colChar & "18"
            Next

            sheet.Cells(1, 1, 3, 10).BackColor = Color.FromArgb(217, 217, 255)
            sheet.Cells(4, 1, 18, 3).BackColor = Color.FromArgb(217, 255, 217)
            sheet.Cells(1, 1, 3, 10).HorizontalAlignment = CellHorizontalAlignment.Center

            Dim complexBorderFactory As New ComplexBorderFactory()
            Dim thinLineBorder = complexBorderFactory.CreateComplexBorder(New ComplexBorderSide(Color.Black, 1))
            Dim dottedLineBorder = complexBorderFactory.CreateComplexBorder(New ComplexBorderSide(ComplexBorderSideStyle.Dotted, Color.Black))

            Dim compoundBorder As New CompoundBorder(New LineBorder(Color.Black, 0), dottedLineBorder)
            Dim complexBorder As New ComplexBorder(New ComplexBorderSide(ComplexBorderSideStyle.Dotted, Color.Black), New ComplexBorderSide(Color.Black, 1), New ComplexBorderSide(ComplexBorderSideStyle.Dotted, Color.Black), New ComplexBorderSide(ComplexBorderSideStyle.Dotted, Color.Black))

            sheet.Cells(1, 1, 18, 10).Border = thinLineBorder
            sheet.Cells(4, 4, 6, 9).Border = compoundBorder
            sheet.Cells(7, 4, 9, 9).Border = compoundBorder
            sheet.Cells(11, 4, 13, 9).Border = compoundBorder
            sheet.Cells(14, 4, 16, 9).Border = compoundBorder

            sheet.Cells(7, 4, 7, 9).Border = complexBorder
            sheet.Cells(14, 4, 14, 9).Border = complexBorder

            FillSampleData(sheet, New CellRange(4, 4, 6, 6))
            FillSampleData(sheet, New CellRange(11, 4, 6, 6))

            sheet.Columns(0, 1).Width = 40
            sheet.Columns(3, 4).Width = 40
            sheet.Columns(11).Width = 40

            AddPieChart(sheet)

            fpSpread.ResumeLayout(True)
        End Sub

        Private Sub AddPieChart(sheet As SheetView)
            sheet.Cells(20, 1).Formula = "SUM(K5:K7)"
            sheet.Cells(20, 2).Formula = "SUM(K8:K10)"
            sheet.Cells(20, 3).Formula = "SUM(K12:K14)"
            sheet.Cells(20, 4).Formula = "SUM(K15:K17)"
            sheet.Rows(20).Visible = False

            Dim location As Point = GetCellLocation(sheet, 2, 12)

            Dim chart = sheet.AddChart(New CellRange(20, 1, 1, 4), GetType(PieSeries), 300, 200, location.X, location.Y,
                ChartViewType.View2D, False)
            chart.DataSetting.ShowHiddenData = True
        End Sub

        Private Function GetCellLocation(sheet As SheetView, row As Integer, col As Integer) As Point
            Dim x As Single = 0, y As Single = 0
            For j As Integer = 0 To col - 1
                x += sheet.Columns(j).Width
            Next

            For i As Integer = 0 To row - 1
                y += sheet.Rows(i).Height
            Next

            Return New Point(CInt(Math.Truncate(x)), CInt(Math.Truncate(y)))
        End Function

        Private Sub FillSampleData(sheet As SheetView, range As CellRange)
            Dim random As New Random()
            For i As Integer = 0 To range.RowCount - 1
                For j As Integer = 0 To range.ColumnCount - 1
                    sheet.Cells(i + range.Row, j + range.Column).Value = random.[Next](50, 300)
                Next
            Next
        End Sub
#End Region

#Region "Event handlers"
        Private Sub BtnPrint_Click(sender As Object, e As EventArgs)
            Dim pi = fpSpread.ActiveSheet.PrintInfo
            pi.ShowPrintDialog = True
            pi.ShowColor = True
            pi.EnhancePreview = InlineAssignHelper(pi.Preview, chkPrintPreview.Checked)

            fpSpread.PrintSheet(fpSpread.ActiveSheet)
        End Sub
        Private Shared Function InlineAssignHelper(Of T)(ByRef target As T, value As T) As T
            target = value
            Return value
        End Function
#End Region
    End Class
End Namespace

'
'IntroPrint.Designer.vb
'

Namespace Features
	Partial Class IntroPrint
		''' <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()
			Me.fpSpread = New FarPoint.Win.Spread.FpSpread()
			Me.fpSpread_Introduction = New FarPoint.Win.Spread.SheetView()
			Me.btnPrint = New System.Windows.Forms.Button()
			Me.chkPrintPreview = New System.Windows.Forms.CheckBox()
			Me.pnlMain.SuspendLayout()
			Me.pnlControls.SuspendLayout()
			DirectCast(Me.fpSpread, System.ComponentModel.ISupportInitialize).BeginInit()
			DirectCast(Me.fpSpread_Introduction, System.ComponentModel.ISupportInitialize).BeginInit()
			Me.SuspendLayout()
			' 
			' pnlMain
			' 
			Me.pnlMain.Controls.Add(Me.fpSpread)
			' 
			' pnlControls
			' 
			Me.pnlControls.Controls.Add(Me.btnPrint)
			Me.pnlControls.Controls.Add(Me.chkPrintPreview)
			' 
			' fpSpread
			' 
			Me.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill
			Me.fpSpread.Location = New System.Drawing.Point(0, 0)
			Me.fpSpread.Name = "fpSpread"
			Me.fpSpread.Sheets.AddRange(New FarPoint.Win.Spread.SheetView() {Me.fpSpread_Introduction})
			Me.fpSpread.Size = New System.Drawing.Size(477, 487)
			Me.fpSpread.TabIndex = 0
			' 
			' fpSpread_Introduction
			' 
			Me.fpSpread_Introduction.Reset()
			Me.fpSpread_Introduction.SheetName = "Introduction"
			' Formulas and custom names must be loaded with R1C1 reference style
			Me.fpSpread_Introduction.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
			Me.fpSpread_Introduction.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
			' 
			' btnPrint
			' 
			Me.btnPrint.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
			Me.btnPrint.Location = New System.Drawing.Point(16, 22)
			Me.btnPrint.Name = "btnPrint"
			Me.btnPrint.Size = New System.Drawing.Size(128, 23)
			Me.btnPrint.TabIndex = 0
			Me.btnPrint.Text = "Print Sheet"
			Me.btnPrint.UseVisualStyleBackColor = True
			' 
			' chkPrintPreview
			' 
			Me.chkPrintPreview.AutoSize = True
			Me.chkPrintPreview.Checked = True
			Me.chkPrintPreview.Location = New System.Drawing.Point(6, 52)
			Me.chkPrintPreview.Name = "chkPrintPreview"
			Me.chkPrintPreview.Size = New System.Drawing.Size(53, 17)
			Me.chkPrintPreview.TabIndex = 1
			Me.chkPrintPreview.Text = "Print Preview"
			Me.chkPrintPreview.UseVisualStyleBackColor = True
			' 
			' IntroPrint
			' 
			Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
			Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
			Me.Name = "IntroPrint"
			Me.pnlMain.ResumeLayout(False)
			Me.pnlControls.ResumeLayout(False)
			DirectCast(Me.fpSpread, System.ComponentModel.ISupportInitialize).EndInit()
			DirectCast(Me.fpSpread_Introduction, System.ComponentModel.ISupportInitialize).EndInit()
			Me.ResumeLayout(False)

		End Sub

		#End Region

		Private fpSpread As FarPoint.Win.Spread.FpSpread
		Private fpSpread_Introduction As FarPoint.Win.Spread.SheetView
		Private btnPrint As System.Windows.Forms.Button
		Private chkPrintPreview As System.Windows.Forms.CheckBox
	End Class
End Namespace
