Excel Jetcell .NET component - C# / VB.NET Examples & Samples Code

Imports System.Drawing
Imports Docs.Excel

Module Module1

Sub Main()
        ' TODO: If using Excel Jetcell .NET Full Version, put YOUR-LICENSE-CODE below.
        ' Otherwise, if you are using Excel Jetcell .NET Trial, comment out the following line
        ' (Free version doesn't have LicenseCode method). 
        ' Docs.Excel.ExcelWorkbook.SetLicenseCode("YOUR-LICENSE-CODE");

Dim WBook = New ExcelWorkbook()
        WBook.Worksheets.Add("SupportedFunction")

WBook.Worksheets(0).Cells(0, 0).Value = "List of supported function in Docs.Excel:"
        WBook.Worksheets(0).Columns(0).Width = 285
        WBook.Worksheets(0).Columns(1).Width = 15
        WBook.Worksheets(0).Columns(2).Width = 285

WBook.Worksheets(0).Cells(2, 0).Value = "Function of Date & Time:"
        WBook.Worksheets(0).Cells(2, 0).Style.Font.Color = Color.Blue
        WBook.Worksheets(0).Cells(3, 0).Value = "DATE"
        WBook.Worksheets(0).Cells(4, 0).Value = "DATEVALUE"
        WBook.Worksheets(0).Cells(5, 0).Value = "DAY"
        WBook.Worksheets(0).Cells(6, 0).Value = "HOUR"
        WBook.Worksheets(0).Cells(7, 0).Value = "MINUTE"
        WBook.Worksheets(0).Cells(8, 0).Value = "MONTH"
        WBook.Worksheets(0).Cells(9, 0).Value = "NOW"
        WBook.Worksheets(0).Cells(10, 0).Value = "SECOND"
        WBook.Worksheets(0).Cells(11, 0).Value = "TIME"
        WBook.Worksheets(0).Cells(12, 0).Value = "TIMEVALUE"
        WBook.Worksheets(0).Cells(13, 0).Value = "TODAY"
        WBook.Worksheets(0).Cells(14, 0).Value = "WEEKDAY"
        WBook.Worksheets(0).Cells(15, 0).Value = "YEAR"

WBook.Worksheets(0).Cells(17, 0).Value = "Financial function:"
        WBook.Worksheets(0).Cells(17, 0).Style.Font.Color = Color.Blue
        WBook.Worksheets(0).Cells(18, 0).Value = "SLN"
        WBook.Worksheets(0).Cells(19, 0).Value = "SYD"

WBook.Worksheets(0).Cells(21, 0).Value = "Logical function:"
        WBook.Worksheets(0).Cells(21, 0).Style.Font.Color = Color.Blue
        WBook.Worksheets(0).Cells(22, 0).Value = "AND"
        WBook.Worksheets(0).Cells(23, 0).Value = "FALSE"
        WBook.Worksheets(0).Cells(24, 0).Value = "IF"
        WBook.Worksheets(0).Cells(25, 0).Value = "NOT"
        WBook.Worksheets(0).Cells(26, 0).Value = "OR"
        WBook.Worksheets(0).Cells(27, 0).Value = "TRUE"

WBook.Worksheets(0).Cells(29, 0).Value = "Text function"
        WBook.Worksheets(0).Cells(29, 0).Style.Font.Color = Color.Blue
        WBook.Worksheets(0).Cells(30, 0).Value = "CHAR"
        WBook.Worksheets(0).Cells(31, 0).Value = "CODE"
        WBook.Worksheets(0).Cells(32, 0).Value = "CONCATENATE"
        WBook.Worksheets(0).Cells(33, 0).Value = "DOLLAR"
        WBook.Worksheets(0).Cells(34, 0).Value = "EXACT"
        WBook.Worksheets(0).Cells(35, 0).Value = "FIXED"
        WBook.Worksheets(0).Cells(36, 0).Value = "LEFT"
        WBook.Worksheets(0).Cells(37, 0).Value = "LEN"
        WBook.Worksheets(0).Cells(38, 0).Value = "LOWER"
        WBook.Worksheets(0).Cells(39, 0).Value = "MID"
        WBook.Worksheets(0).Cells(40, 0).Value = "PROPER"
        WBook.Worksheets(0).Cells(41, 0).Value = "REPLACE"
        WBook.Worksheets(0).Cells(42, 0).Value = "REPT"
        WBook.Worksheets(0).Cells(43, 0).Value = "RIGHT"
        WBook.Worksheets(0).Cells(44, 0).Value = "SUBSTITUTE"
        WBook.Worksheets(0).Cells(45, 0).Value = "TEXT"
        WBook.Worksheets(0).Cells(46, 0).Value = "TRIM"
        WBook.Worksheets(0).Cells(47, 0).Value = "UPPER"
        WBook.Worksheets(0).Cells(48, 0).Value = "VALUE"

WBook.Worksheets(0).Cells(2, 2).Value = "Math & Trig function:"
        WBook.Worksheets(0).Cells(2, 2).Style.Font.Color = Color.Blue
        WBook.Worksheets(0).Cells(3, 2).Value = "ABS"
        WBook.Worksheets(0).Cells(4, 2).Value = "ACOS"
        WBook.Worksheets(0).Cells(5, 2).Value = "ASIN"
        WBook.Worksheets(0).Cells(6, 2).Value = "ATAN"
        WBook.Worksheets(0).Cells(7, 2).Value = "ATAN2"
        WBook.Worksheets(0).Cells(8, 2).Value = "CEILING"
        WBook.Worksheets(0).Cells(9, 2).Value = "COMBIN"
        WBook.Worksheets(0).Cells(10, 2).Value = "COS"
        WBook.Worksheets(0).Cells(11, 2).Value = "COSH"
        WBook.Worksheets(0).Cells(12, 2).Value = "DEGREES"
        WBook.Worksheets(0).Cells(13, 2).Value = "EVEN"
        WBook.Worksheets(0).Cells(14, 2).Value = "EXP"
        WBook.Worksheets(0).Cells(15, 2).Value = "FACT"
        WBook.Worksheets(0).Cells(16, 2).Value = "FLOOR"
        WBook.Worksheets(0).Cells(17, 2).Value = "INT"
        WBook.Worksheets(0).Cells(18, 2).Value = "LN"
        WBook.Worksheets(0).Cells(19, 2).Value = "LOG"
        WBook.Worksheets(0).Cells(20, 2).Value = "LOG10"
        WBook.Worksheets(0).Cells(21, 2).Value = "MOD"
        WBook.Worksheets(0).Cells(22, 2).Value = "ODD"
        WBook.Worksheets(0).Cells(23, 2).Value = "PI"
        WBook.Worksheets(0).Cells(24, 2).Value = "POWER"
        WBook.Worksheets(0).Cells(25, 2).Value = "PRODUCT"
        WBook.Worksheets(0).Cells(26, 2).Value = "RADIANS"
        WBook.Worksheets(0).Cells(27, 2).Value = "RAND"
        WBook.Worksheets(0).Cells(28, 2).Value = "ROUND"
        WBook.Worksheets(0).Cells(29, 2).Value = "ROUNDDOWN"
        WBook.Worksheets(0).Cells(30, 2).Value = "ROUNDUP"
        WBook.Worksheets(0).Cells(31, 2).Value = "SIGN"
        WBook.Worksheets(0).Cells(32, 2).Value = "SIN"
        WBook.Worksheets(0).Cells(33, 2).Value = "SINH"
        WBook.Worksheets(0).Cells(34, 2).Value = "SQRT"
        WBook.Worksheets(0).Cells(35, 2).Value = "SUM"
        WBook.Worksheets(0).Cells(36, 2).Value = "SUMSQ"
        WBook.Worksheets(0).Cells(37, 2).Value = "TAN"
        WBook.Worksheets(0).Cells(38, 2).Value = "TANH"
        WBook.Worksheets(0).Cells(39, 2).Value = "TRUNC"

WBook.Worksheets(0).Cells(41, 2).Value = "Statistical function"
        WBook.Worksheets(0).Cells(41, 2).Style.Font.Color = Color.Blue
        WBook.Worksheets(0).Cells(42, 2).Value = "AVERAGE"
        WBook.Worksheets(0).Cells(43, 2).Value = "COUNT"
        WBook.Worksheets(0).Cells(44, 2).Value = "MAX"
        WBook.Worksheets(0).Cells(45, 2).Value = "MIN"

'Samples worksheet
        WBook.Worksheets.Add("Examples")
        WBook.Worksheets(1).Columns(0).Width = 225
        WBook.Worksheets(1).Columns(1).Width = 100
        WBook.Worksheets(1).Cells(0, 0).Value = "Examples of typical formulas usage:"
        WBook.Worksheets(1).Cells(2, 0).Value = "Table of values:"
        WBook.Worksheets(1).Cells(2, 1).Value = 1
        WBook.Worksheets(1).Cells(2, 2).Value = 3
        WBook.Worksheets(1).Cells(2, 3).Value = 4
        WBook.Worksheets(1).Cells(2, 4).Value = 5
        WBook.Worksheets(1).Cells(2, 5).Value = 6
        WBook.Worksheets(1).Cells(3, 1).Value = 10.11
        WBook.Worksheets(1).Cells(3, 2).Value = 12.134
        WBook.Worksheets(1).Cells(3, 3).Value = 0.856
        WBook.Worksheets(1).Cells(3, 4).Value = -15.35
        WBook.Worksheets(1).Cells(3, 5).Value = -33.3

WBook.Worksheets(1).Cells(6, 0).Value = "Formula"
        WBook.Worksheets(1).Cells(6, 1).Value = "Result"
        WBook.Worksheets(1).Cells(7, 0).Value = "7+3="
        WBook.Worksheets(1).Cells(7, 1).Value = "=7+3"
        WBook.Worksheets(1).Cells(8, 0).Value = "(2*2^2)%="
        WBook.Worksheets(1).Cells(8, 1).Value = "=(2*2^2)%"
        WBook.Worksheets(1).Cells(9, 0).Value = "SIN(5)="
        WBook.Worksheets(1).Cells(9, 1).Value = "=SIN(5)"
        WBook.Worksheets(1).Cells(10, 0).Value = "SUM(B3:D5)="
        WBook.Worksheets(1).Cells(10, 1).Value = "=SUM(B3:D5)"
        WBook.Worksheets(1).Cells(11, 0).Value = "SUM(SIN(3);TAN(4);LOG(5;3))="
        WBook.Worksheets(1).Cells(11, 1).Value = "=SUM(SIN(3);TAN(3);LOG(5;3))"
        WBook.Worksheets(1).Cells(12, 0).Value = "CEILING(12.35;0.1)="
        WBook.Worksheets(1).Cells(12, 1).Value = "=CEILING(12.35;0.1)"
        WBook.Worksheets(1).Cells(13, 0).Value = "PI()="
        WBook.Worksheets(1).Cells(13, 1).Value = "=PI()"
        WBook.Worksheets(1).Cells(14, 0).Value = "TODAY()="
        WBook.Worksheets(1).Cells(14, 1).Value = "=TODAY()"
        WBook.Worksheets(1).Cells(14, 1).Style.StringFormat = "MMM-DD-YYYY"
        WBook.Worksheets(1).Cells(16, 0).Value = "TRUE()="
        WBook.Worksheets(1).Cells(16, 1).Value = "=TRUE()"

'XLS
        WBook.WriteXLS("..\..\..\Formulas.xls")

'XLSX
        WBook.WriteXLSX("..\..\..\Formulas.xlsx")

'Open specified file in MS Excel.
        System.Diagnostics.Process.Start("..\..\..\Formulas.xlsx")

End Sub

End Module