AdvancedFeaturesVB

Imports System
Imports System.Drawing

Imports GemBox.Spreadsheet

Module AdvancedFeaturesVB

    Sub Main(ByVal args As String())
        ' TODO: If using GemBox.Spreadsheet Professional, put your serial key below.
        ' Otherwise, if you are using GemBox.Spreadsheet Free, comment out the 
        ' following line (Free version doesn't have SetLicense method). 
        'SpreadsheetInfo.SetLicense("YOUR-SERIAL-KEY-HERE")

        Dim excelFile As New ExcelFile
        Dim worksheets As ExcelWorksheetCollection = excelFile.Worksheets

        ImagesSample(worksheets.Add("Images"))
        CommentsSample(worksheets.Add("Comments"))
        PrintViewOptionsSample(worksheets.Add("PrintViewOptions"))
        HyperlinkSample(worksheets.Add("Hyperlinks"))

        Dim fileName As String = "AdvancedFeatures.xlsx"

        ' Images, Comments and Hyperlinks are only supported in XLSX.
        excelFile.SaveXlsx(fileName)

        TryToDisplayGeneratedFile(fileName)
    End Sub

    Sub ImagesSample(ByVal ws As ExcelWorksheet)
        ws.Cells.Item(0, 0).Value = "Image examples:"
        ' Small BMP added by using rectangle.
        ws.Pictures.Add("..\..\SmallImage.bmp", New Rectangle(50, 50, 48, 48))

        ' Large JPG added by using rectangle.
        ws.Pictures.Add("..\..\FragonardReader.jpg", New Rectangle(50, 150, 478, 600))

        ' PNG added by using anchors.
        ws.Pictures.Add("..\..\Dices.png", PositioningMode.FreeFloating, New AnchorCell(ws.Columns(8), ws.Rows(16), True), New AnchorCell(ws.Columns(9), ws.Rows(20), False))

        ' GIF added by using anchors. Notice that animation is lost in MS Excel.
        ws.Pictures.Add("..\..\Zahnrad.gif", PositioningMode.Move, New AnchorCell(ws.Columns(8), ws.Rows(22), 100000, 100000), New AnchorCell(ws.Columns(9), ws.Rows(24), 50000, 50000))

        ' WMF added by using anchors.
        ws.Pictures.Add("..\..\Graphics1.wmf", PositioningMode.MoveAndSize, New AnchorCell(ws.Columns(10), ws.Rows(10), True), New AnchorCell(ws.Columns(15), ws.Rows(15), False))
    End Sub

    Sub CommentsSample(ByVal ws As ExcelWorksheet)
        ws.Cells.Item(0, 0).Value = "Comment examples:"

        ws.Cells.Item(2, 1).Comment.Text = "Empty cell."

        ws.Cells.Item(4, 1).Value = 5
        ws.Cells.Item(4, 1).Comment.Text = "Cell with a number."

        ws.Cells.Item("B7").Value = "Cell B7"
        ws.Cells.Item("B7").Comment.Text = "Some text. Comment is:" & ChrW(10) & "a) multiline," & ChrW(10) & "b) large, and " & ChrW(10) & "c) visible."
        ws.Cells.Item("B7").Comment.IsVisible = True
    End Sub

    Sub HyperlinkSample(ByVal ws As ExcelWorksheet)
        ws.Cells.Item("B16").Value = "GemboxSoftware"
        ws.Cells.Item("B16").Style.Font.UnderlineStyle = UnderlineStyle.Single
        ws.Cells.Item("B16").Style.Font.Color = Color.Blue
        ws.Cells.Item("B16").Hyperlink.Location = "http://www.gemboxsoftware.com"
        ws.Cells.Item("B16").Hyperlink.IsExternal = True

        ws.Cells.Item("B20").Value = "Jump"
        ws.Cells.Item("B20").Hyperlink.ToolTip = "This is tool tip! This hyperlink jumps to A1!"
        ws.Cells.Item("B20").Hyperlink.Location = (ws.Name & "!A1")

        ws.Cells.Item("A1").Value = "Jump Here"
    End Sub

    Sub PrintViewOptionsSample(ByVal ws As ExcelWorksheet)
        ws.Cells.Item("M1").Value = "This worksheet shows how to set various print related and view related options."
        ws.Cells.Item("M2").Value = "To see results of print options, go to Print and Page Setup dialogs in MS Excel."
        ws.Cells.Item("M3").Value = "Notice that print and view options are worksheet based, not workbook based."

        ' Print options:
        ws.PrintOptions.Portrait = False
        ws.PrintOptions.PaperSize = 8
        ws.PrintOptions.NumberOfCopies = 5

        ' View options:
        ws.ViewOptions.FirstVisibleColumn = 3
        ws.ViewOptions.ShowColumnsFromRightToLeft = True
        ws.ViewOptions.Zoom = 123
    End Sub

    Sub TryToDisplayGeneratedFile(ByVal fileName As String)
        Try
            Process.Start(fileName)
        Catch exception1 As Exception
            Console.WriteLine((fileName & " created in application folder."))
        End Try
    End Sub

End Module