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"))
        SummariesSample(excelFile)
 
        Dim fileName As String = "AdvancedFeatures.xlsx"
        excelFile.SaveXlsx(fileName)
 
        'Uncomment if you want to export in ODS.
        'Dim fileName As String = "AdvancedFeatures.ods"
        'excelFile.SaveOds(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 SummariesSample(ByVal excelFile As ExcelFile)
 
        Dim builtInDocProp = excelFile.DocumentProperties.BuiltIn
        builtInDocProp(BuiltInDocumentProperties.Author) = Environment.UserName
        builtInDocProp(BuiltInDocumentProperties.Title) = "Advanced Featrues Sample"
        builtInDocProp(BuiltInDocumentProperties.Comments) = "This is Advanced Featrues Sample"
 
        excelFile.DocumentProperties.Custom("X") = "Some custom property"
    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