Imports System Imports System.Drawing Imports System.Data Imports GemBox.Spreadsheet Module FeatureSamplesVB Sub Main() ' 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 ExcelFile = New ExcelFile Dim worksheets As ExcelWorksheetCollection = excelFile.Worksheets ' Each static method modifies provided worksheet with one category of features. ValuesSample(worksheets.Add("Values")) StylesSample(worksheets.Add("Styles")) ReferencingAndGroupsSample(worksheets.Add("ReferencingAndGroups")) worksheets.ActiveWorksheet = worksheets("Styles") Dim fileName As String = "FeatureSamples.xls" excelFile.SaveXls(fileName) ' Uncomment if you want to export in XLSX. 'Dim fileName As String = "FeatureSamples.xlsx" 'excelFile.SaveXlsx(fileName) ' Uncomment if you want to export in ODS. 'Dim fileName As String = "FeatureSamples.ods" 'excelFile.SaveOds(fileName) TryToDisplayGeneratedFile(fileName) End Sub Sub ValuesSample(ByVal ws As ExcelWorksheet) ws.Cells(0, 0).Value = "Cell value examples:" ' Column width of 25 and 40 characters. ws.Columns(0).Width = 25 * 256 ws.Columns(1).Width = 40 * 256 Dim row As Integer = 1 row = row + 1 ws.Cells(row, 0).Value = "Type" ws.Cells(row, 1).Value = "Value" row = row + 1 ws.Cells(row, 0).Value = "System.DBNull:" ws.Cells(row, 1).Value = System.DBNull.Value row = row + 1 ws.Cells(row, 0).Value = "System.Byte:" ws.Cells(row, 1).Value = Byte.MaxValue row = row + 1 ws.Cells(row, 0).Value = "System.Int16:" ws.Cells(row, 1).Value = Short.MinValue row = row + 1 ws.Cells(row, 0).Value = "System.Int64:" ws.Cells(row, 1).Value = Long.MinValue row = row + 1 ws.Cells(row, 0).Value = "System.Int32:" ws.Cells(row, 1).Value = CType(-5678, Integer) row = row + 1 ws.Cells(row, 0).Value = "System.Single:" ws.Cells(row, 1).Value = Single.MaxValue row = row + 1 ws.Cells(row, 0).Value = "System.Double:" ws.Cells(row, 1).Value = Double.MaxValue row = row + 1 ws.Cells(row, 0).Value = "System.Boolean:" ws.Cells(row, 1).Value = True row = row + 1 ws.Cells(row, 0).Value = "System.Char:" ws.Cells(row, 1).Value = "a"c row = row + 1 ws.Cells(row, 0).Value = "System.Text.StringBuilder:" ws.Cells(row, 1).Value = New System.Text.StringBuilder("StringBuilder text.") row = row + 1 ws.Cells(row, 0).Value = "System.Decimal:" ws.Cells(row, 1).Value = New Decimal(50000) row = row + 1 ws.Cells(row, 0).Value = "System.DateTime:" ws.Cells(row, 1).Value = DateTime.Now row = row + 1 ws.Cells(row, 0).Value = "System.String:" ws.Cells(row, 1).Value = "Microsoft Excel is a spreadsheet program written and distributed by Microsoft" _ + " for computers using the Microsoft Windows operating system and Apple Macintosh" _ + " computers. It is overwhelmingly the dominant spreadsheet application available" _ + " for these platforms and has been so since version 5 1993 and its bundling as" _ + " part of Microsoft Office. For more information see:" _ + " http://en.wikipedia.org/wiki/Microsoft_Excel" row = row + 2 ws.Cells(row, 0).Value = "DataTable insert example:" ws.InsertDataTable(FeatureSamplesVB.CreateData(), row, 2, True) End Sub Function CreateData() As DataTable Dim dt As DataTable = New DataTable dt.Columns.Add("ID", Type.GetType("System.Int32")) dt.Columns.Add("FirstName", Type.GetType("System.String")) dt.Columns.Add("LastName", Type.GetType("System.String")) dt.Rows.Add(New Object() {100, "John", "Doe"}) dt.Rows.Add(New Object() {101, "Fred", "Nurk"}) dt.Rows.Add(New Object() {103, "Hans", "Meier"}) dt.Rows.Add(New Object() {104, "Ivan", "Horvat"}) dt.Rows.Add(New Object() {105, "Jean", "Dupont"}) dt.Rows.Add(New Object() {106, "Mario", "Rossi"}) Return dt End Function Sub StylesSample(ByVal ws As ExcelWorksheet) ws.Cells(0, 0).Value = "Cell style examples:" Dim row As Integer = 0 ' Column width of 4, 30 and 35 characters. ws.Columns(0).Width = 4 * 256 ws.Columns(1).Width = 30 * 256 ws.Columns(2).Width = 35 * 256 row = row + 2 ws.Cells(row, 1).Value = ".Style.Borders.SetBorders(...)" ws.Cells(row, 2).Style.Borders.SetBorders(MultipleBorders.All, Color.FromArgb(252, 1, 1), LineStyle.Thin) row = row + 2 ws.Cells(row, 1).Value = ".Style.FillPattern.SetPattern(...)" ws.Cells(row, 2).Style.FillPattern.SetPattern(FillPatternStyle.ThinHorizontalCrosshatch, Color.Green, Color.Yellow) row = row + 2 ws.Cells(row, 1).Value = ".Style.Font.Color =" ws.Cells(row, 2).Value = "Color.Blue" ws.Cells(row, 2).Style.Font.Color = Color.Blue row = row + 2 ws.Cells(row, 1).Value = ".Style.Font.Italic =" ws.Cells(row, 2).Value = "true" ws.Cells(row, 2).Style.Font.Italic = True row = row + 2 ws.Cells(row, 1).Value = ".Style.Font.Name =" ws.Cells(row, 2).Value = "Comic Sans MS" ws.Cells(row, 2).Style.Font.Name = "Comic Sans MS" row = row + 2 ws.Cells(row, 1).Value = ".Style.Font.ScriptPosition =" ws.Cells(row, 2).Value = "ScriptPosition.Superscript" ws.Cells(row, 2).Style.Font.ScriptPosition = ScriptPosition.Superscript row = row + 2 ws.Cells(row, 1).Value = ".Style.Font.Size =" ws.Cells(row, 2).Value = "18 * 20" ws.Cells(row, 2).Style.Font.Size = 18 * 20 row = row + 2 ws.Cells(row, 1).Value = ".Style.Font.Strikeout =" ws.Cells(row, 2).Value = "true" ws.Cells(row, 2).Style.Font.Strikeout = True row = row + 2 ws.Cells(row, 1).Value = ".Style.Font.UnderlineStyle =" ws.Cells(row, 2).Value = "UnderlineStyle.Double" ws.Cells(row, 2).Style.Font.UnderlineStyle = UnderlineStyle.Double row = row + 2 ws.Cells(row, 1).Value = ".Style.Font.Weight =" ws.Cells(row, 2).Value = "ExcelFont.BoldWeight" ws.Cells(row, 2).Style.Font.Weight = ExcelFont.BoldWeight row = row + 2 ws.Cells(row, 1).Value = ".Style.HorizontalAlignment =" ws.Cells(row, 2).Value = "HorizontalAlignmentStyle.Center" ws.Cells(row, 2).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center row = row + 2 ws.Cells(row, 1).Value = ".Style.Indent" ws.Cells(row, 2).Value = "five" ws.Cells(row, 2).Style.HorizontalAlignment = HorizontalAlignmentStyle.Left ws.Cells(row, 2).Style.Indent = 5 row = row + 2 ws.Cells(row, 1).Value = ".Style.IsTextVertical = " ws.Cells(row, 2).Value = "true" ' Set row height to 50 points. ws.Rows(row).Height = 50 * 20 ws.Cells(row, 2).Style.IsTextVertical = True row = row + 2 ws.Cells(row, 1).Value = ".Style.NumberFormat" ws.Cells(row, 2).Value = 1234 ws.Cells(row, 2).Style.NumberFormat = "#.##0,00 [$Krakozhian Money Units]" row = row + 2 ws.Cells(row, 1).Value = ".Style.Rotation" ws.Cells(row, 2).Value = "35 degrees up" ws.Cells(row, 2).Style.Rotation = 35 row = row + 2 ws.Cells(row, 1).Value = ".Style.ShrinkToFit" ws.Cells(row, 2).Value = "This property is set to true so this text appears shrunk." ws.Cells(row, 2).Style.ShrinkToFit = True row = row + 2 ws.Cells(row, 1).Value = ".Style.VerticalAlignment =" ws.Cells(row, 2).Value = "VerticalAlignmentStyle.Top" ' Set row height to 30 points. ws.Rows(row).Height = 30 * 20 ws.Cells(row, 2).Style.VerticalAlignment = VerticalAlignmentStyle.Top row = row + 2 ws.Cells(row, 1).Value = ".Style.WrapText" ws.Cells(row, 2).Value = "This property is set to true so this text appears broken into multiple lines." ws.Cells(row, 2).Style.WrapText = True End Sub Sub ReferencingAndGroupsSample(ByVal ws As ExcelWorksheet) ws.Cells(0).Value = "Cell referencing and grouping examples:" ws.Cells("B2").Value = "Cell B2." ws.Cells(6, 0).Value = "Cell in row 7 and column A." ws.Rows(2).Cells(0).Value = "Cell in row 3 and column A." ws.Rows("4").Cells("B").Value = "Cell in row 4 and column B." ws.Columns(2).Cells(4).Value = "Cell in column C and row 5." ws.Columns("AA").Cells("6").Value = "Cell in AA column and row 6." Dim cr As CellRange = ws.Rows(7).Cells cr(0).Value = cr.IndexingMode cr(3).Value = "D8" cr("B").Value = "B8" cr = ws.Columns(7).Cells cr(0).Value = cr.IndexingMode cr(2).Value = "H3" cr("5").Value = "H5" cr = ws.Cells.GetSubrange("I2", "L8") cr.SetBorders(MultipleBorders.Outside, Color.Navy, LineStyle.Dashed) cr("J7").Value = cr.IndexingMode cr(0, 0).Value = "I2" cr("J3").Value = "J3" cr(4).Value = "I3" ' Cell range width is 4 (I J K L). ' Vertical grouping. ws.Cells(12, 0).Value = "GroupA Start" ws.Rows(12).OutlineLevel = 1 ws.Cells(13, 0).Value = "A" ws.Rows(13).OutlineLevel = 1 ws.Cells(14, 1).Value = "GroupB Start" ws.Rows(14).OutlineLevel = 2 ws.Cells(15, 1).Value = "B" ws.Rows(15).OutlineLevel = 2 ws.Cells(16, 1).Value = "GroupB End" ws.Rows(16).OutlineLevel = 2 ws.Cells(17, 0).Value = "GroupA End" ws.Rows(17).OutlineLevel = 1 ' Put outline row buttons above groups. ws.ViewOptions.OutlineRowButtonsBelow = False ' Horizontal grouping (collapsed). ws.Cells("E12").Value = "Gr.C Start" ws.Columns("E").OutlineLevel = 1 ws.Columns("E").Hidden = True ws.Cells("F12").Value = "C" ws.Columns("F").OutlineLevel = 1 ws.Columns("F").Hidden = True ws.Cells("G12").Value = "Gr.C End" ws.Columns("G").OutlineLevel = 1 ws.Columns("G").Hidden = True ws.Columns("H").Collapsed = True End Sub Sub TryToDisplayGeneratedFile(ByVal fileName As String) Try System.Diagnostics.Process.Start(fileName) Catch Console.WriteLine(fileName + " created in application folder.") End Try End Sub End Module