FormulaSamplesVB

Imports GemBox.Spreadsheet

Module FormulaSamplesVB

    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

        CreateFormulaFile(excelFile)

        Dim fileName As String = "FormulasVB.xls"
        excelFile.SaveXls(fileName)
        ' Uncomment if you want to export in XLSX.
        'Dim fileName As String = "FormulasVB.xlsx"
        'excelFile.SaveXlsx(fileName)

        TryToDisplayGeneratedFile(fileName)
    End Sub

    Sub CreateFormulaFile(ByVal excelFile As ExcelFile)
        Dim rowIndex As Integer = 0

        Dim sheet1 As ExcelWorksheet = ExcelFile.Worksheets.Add("sheet1")
        Dim sheet2 As ExcelWorksheet = ExcelFile.Worksheets.Add("sheet2")

        sheet1.Columns(0).Width = 35 * 256
        sheet1.Columns(1).Width = 15 * 256
        sheet1.Columns(2).Width = 15 * 256

        sheet1.Cells(rowIndex, 0).Value = "Examples of typical formulas usage:"
        rowIndex = rowIndex + 2

        sheet1.Cells(rowIndex, 0).Value = "Some data:"
        sheet1.Cells(rowIndex, 1).Value = 3
        sheet1.Cells(rowIndex, 2).Value = 4.1
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 1).Value = 5.2
        sheet1.Cells(rowIndex, 2).Value = 6
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 1).Value = 7
        sheet1.Cells(rowIndex, 2).Value = 8.3
        rowIndex = rowIndex + 1

        ' Named ranges.
        Dim namedRange As String = "Range1"
        sheet1.NamedRanges.Add(namedRange, sheet1.Cells.GetSubrange("B3", "C4"))

        ' Floats without first digit.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "Float number without first digit:"
        sheet1.Cells(rowIndex, 1).Formula = "=.5/23+.1-2"

        ' Function using named range.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "Named range:"
        sheet1.Cells(rowIndex, 1).Formula = "=SUM(" + namedRange + ")"

        ' 3D sheet references.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "3d sheet reference:"
        sheet1.Cells(rowIndex, 1).Formula = "=sheet2!$C$2"

        ' 3D area sheet references.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "3d area sheet reference:"
        sheet1.Cells(rowIndex, 1).Formula = "=AVERAGE(sheet2!$A$2:C$2)"

        ' Function's miss argument.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "Function's miss arguments:"
        sheet1.Cells(rowIndex, 1).Formula = "=Count(1,  ,  ,,,2, 23,,,,,, 34,,,54,,,,  ,)"

        ' Functions are case-insensitive.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "Functions are case-insensitive:"
        sheet1.Cells(rowIndex, 1).Formula = "=cOs( 1 )"

        ' Functions.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "Supported functions:"

        Dim nextFunction As String
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "Results"
        sheet1.Cells(rowIndex, 1).Value = "Formulas"
        rowIndex = rowIndex + 1

        nextFunction = "=NOW()+123"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=SECOND(12)/23"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=MINUTE(24)-1343/35"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=(HOUR(56)-23/35)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=WEEKDAY(5)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=YEAR(23)-WEEKDAY(5)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=MONTH(3)-2342/235345"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=((DAY(1)))"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=TIME(1,2,3)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=DATE(1,2,3)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=RAND()"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=TEXT(" + Chr(34) + "text" + Chr(34) + ", " + Chr(34) + "$d" + Chr(34) + ")"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=VAR(1,2)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=MOD(1,2)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=NOT(FALSE)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=OR(FALSE)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=AND(TRUE)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=FALSE()"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=TRUE()"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=VALUE(3)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=LEN(" + Chr(34) + "hello" + Chr(34) + ")"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=MID(" + Chr(34) + "hello" + Chr(34) + ",1,1)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=ROUND(1,2)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=SIGN(-2)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=INT(3)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=ABS(-3)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=LN(2)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=EXP(4)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=SQRT(2)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=PI()"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=COS(4)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=SIN(3)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=MAX(1,2)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=MIN(1,2)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=AVERAGE(1,2)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=SUM(1,3)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=IF(1,2,3)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=COUNT(1,2,3)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        nextFunction = "=SUBTOTAL(1,sheet2!A2:C2)"
        sheet1.Cells(rowIndex, 0).Formula = nextFunction
        sheet1.Cells(rowIndex, 1).Value = nextFunction
        rowIndex = rowIndex + 1

        ' Paranthless checks.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "Paranthless:"
        sheet1.Cells(rowIndex, 1).Formula = "=((12+2343+34545))"

        ' Unary operators.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "Unary operators:"
        sheet1.Cells(rowIndex, 1).Formula = "=B5%"
        sheet1.Cells(rowIndex, 2).Formula = "=+++B5"

        ' Operand tokens, bool.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "Bool values:"
        sheet1.Cells(rowIndex, 1).Formula = "=TRUE"
        sheet1.Cells(rowIndex, 2).Formula = "=FALSE"

        ' Operand tokens, int.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "Integer values:"
        sheet1.Cells(rowIndex, 1).Formula = "=1"
        sheet1.Cells(rowIndex, 2).Formula = "=20"

        ' Operand tokens, num.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "Float values:"
        sheet1.Cells(rowIndex, 1).Formula = "=.4"
        sheet1.Cells(rowIndex, 2).Formula = "=2235.5132"

        ' Operand tokens, str.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "String values:"
        sheet1.Cells(rowIndex, 1).Formula = "=" + Chr(34) + "hello world!" + Chr(34)

        ' Operand tokens, error.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "Error values:"
        sheet1.Cells(rowIndex, 1).Formula = "=#NULL!"
        sheet1.Cells(rowIndex, 2).Formula = "=#DIV/0!"

        ' Binary operators.
        rowIndex = rowIndex + 1
        sheet1.Cells(rowIndex, 0).Value = "Binary operators:"
        sheet1.Cells(rowIndex, 1).Formula = "=(1)-(2)+(3/2+34)/2+12232-32-4"

        ' Another sheet.
        rowIndex = 0
        sheet2.Cells(rowIndex, 0).Value = "Some data on another sheet:"
        rowIndex = rowIndex + 1
        sheet2.Cells(rowIndex, 0).Value = 33
        sheet2.Cells(rowIndex, 1).Value = 44.1
        sheet2.Cells(rowIndex, 2).Value = 55.2
        rowIndex = rowIndex + 1
        sheet2.Cells(rowIndex, 0).Value = 66
        sheet2.Cells(rowIndex, 1).Value = 77
        sheet2.Cells(rowIndex, 2).Value = 88.3
    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