Return to previous page Explore Spreadsheet SDK

Calculate Formula In Cell - VB.NET

Spreadsheet SDK sample in VB.NET demonstrating ‘Calculate Formula In Cell’

View on GitHub Download Source Code (.zip)

Module1.vb

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.IO
Imports System.Diagnostics

Imports Bytescout.Spreadsheet

Module Module1
    Sub Main()
			' Create new spreadsheet
			Dim spreadsheet As New Spreadsheet()

			' Add new worksheet
			Dim sheet As Worksheet = spreadsheet.Workbook.Worksheets.Add()

			' Get decimal separator. Decimal separator depends from locale.
			Dim dsep As String = spreadsheet.Workbook.Locale.NumberFormat.CurrencyDecimalSeparator

			' Get list separator. List separator depends from locale.
			Dim lsep As String = spreadsheet.Workbook.Locale.TextInfo.ListSeparator

			' Set starting row
			Dim rowNumber As Integer = 1

			' Example on SIN() function in formula
			sheet.Cell(rowNumber, 0).Value = "SIN(30)"
			sheet.Cell(rowNumber, 1).Value = "=SIN(30)"

			rowNumber += 2

			' Example on COS() function in formula
			sheet.Cell(rowNumber, 0).Value = "COS(30)"
			sheet.Cell(rowNumber, 1).Value = "=COS(30)"

			rowNumber += 2

			' Example on TAN() function in formula
			sheet.Cell(rowNumber, 0).Value = "TAN(30)"
			sheet.Cell(rowNumber, 1).Value = "=TAN(30)"

			rowNumber += 2

			' Example on ATAN() function in formula
			sheet.Cell(rowNumber, 0).Value = "ATAN(0" + dsep + "5)"
			sheet.Cell(rowNumber, 1).Value = "=ATAN(0" + dsep + "5)"

			rowNumber += 2

			' Example on ATAN2() function in formula
			sheet.Cell(rowNumber, 0).Value = "ATAN2(0" + dsep + "3" + lsep + "0.1)"
			sheet.Cell(rowNumber, 1).Value = "=ATAN2(0" + dsep + "3" + lsep + "0.1)"

			rowNumber += 2

			' Example on ASIN() function in formula
			sheet.Cell(rowNumber, 0).Value = "ASIN(0" + dsep + "5)"
			sheet.Cell(rowNumber, 1).Value = "=ASIN(0" + dsep + "5)"

			rowNumber += 2

			' Example on ACOS() function in formula
			sheet.Cell(rowNumber, 0).Value = "ACOS(0" + dsep + "5)"
			sheet.Cell(rowNumber, 1).Value = "=ACOS(0" + dsep + "5)"

			rowNumber += 2

			' Example on EXP() function in formula
			sheet.Cell(rowNumber, 0).Value = "EXP(5)"
			sheet.Cell(rowNumber, 1).Value = "=EXP(5)"

			rowNumber += 2

			' Example on SQRT() function in formula
			sheet.Cell(rowNumber, 0).Value = "SQRT(5)"
			sheet.Cell(rowNumber, 1).Value = "=SQRT(5)"

			rowNumber += 2

			' Example on LN() function in formula
			sheet.Cell(rowNumber, 0).Value = "LN(5)"
			sheet.Cell(rowNumber, 1).Value = "=LN(5)"

			rowNumber += 2

			' Example on LOG10() function in formula
			sheet.Cell(rowNumber, 0).Value = "LOG10(5)"
			sheet.Cell(rowNumber, 1).Value = "=LOG10(5)"

			rowNumber += 2

			' Example on SUM() function in formula
			sheet.Cell(rowNumber, 0).Value = "SUM(5" + lsep + "3)"
			sheet.Cell(rowNumber, 1).Value = "=SUM(5" + lsep + "3)"

			rowNumber += 2

			' Example on PRODUCT() function in formula
			sheet.Cell(rowNumber, 0).Value = "PRODUCT(5" + lsep + "3)"
			sheet.Cell(rowNumber, 1).Value = "=PRODUCT(5" + lsep + "3)"

			rowNumber += 2

			' Example on VAR() function in formula
			sheet.Cell(rowNumber, 0).Value = "VAR(5" + lsep + "3)"
			sheet.Cell(rowNumber, 1).Value = "=VAR(5" + lsep + "3)"

			rowNumber += 2

			' Example on VARP() function in formula
			sheet.Cell(rowNumber, 0).Value = "VARP(5" + lsep + "3)"
			sheet.Cell(rowNumber, 1).Value = "=VARP(5" + lsep + "3)"

			rowNumber += 2

			' Example on STDEV() function in formula
			sheet.Cell(rowNumber, 0).Value = "STDEV(5" + lsep + "3)"
			sheet.Cell(rowNumber, 1).Value = "=STDEV(5" + lsep + "3)"

			rowNumber += 2

			' Example on STDEVP() function in formula
			sheet.Cell(rowNumber, 0).Value = "STDEVP(5" + lsep + "3)"
			sheet.Cell(rowNumber, 1).Value = "=STDEVP(5" + lsep + "3)"

			rowNumber += 2

			' Example on AVERAGE() function in formula
			sheet.Cell(rowNumber, 0).Value = "AVERAGE(50" + lsep + "30)"
			sheet.Cell(rowNumber, 1).Value = "=AVERAGE(50" + lsep + "30)"

			rowNumber += 2

			' Example on MIN() function in formula
			sheet.Cell(rowNumber, 0).Value = "MIN(50" + lsep + "30)"
			sheet.Cell(rowNumber, 1).Value = "=MIN(50" + lsep + "30)"

			rowNumber += 2

			' Example on MAX() function in formula
			sheet.Cell(rowNumber, 0).Value = "MAX(50" + lsep + "30)"
			sheet.Cell(rowNumber, 1).Value = "=MAX(50" + lsep + "30)"

			rowNumber += 2

			' Example on PI() function in formula
			sheet.Cell(rowNumber, 0).Value = "PI()"
			sheet.Cell(rowNumber, 1).Value = "=PI()"

			rowNumber += 2

			' Example on ABS() function in formula
			sheet.Cell(rowNumber, 0).Value = "ABS(-5)"
			sheet.Cell(rowNumber, 1).Value = "=ABS(-5)"

			rowNumber += 2

			' Example on RAND() function in formula
			sheet.Cell(rowNumber, 0).Value = "RAND()"
			sheet.Cell(rowNumber, 1).Value = "=RAND()"

			rowNumber += 2

			' Example on MOD() function in formula
			sheet.Cell(rowNumber, 0).Value = "MOD(10" + dsep + "8" + lsep + "2)"
			sheet.Cell(rowNumber, 1).Value = "=MOD(10" + dsep + "8" + lsep + "2)"

			rowNumber += 2

			' Example on INT() function in formula
			sheet.Cell(rowNumber, 0).Value = "INT(10" + dsep + "8)"
			sheet.Cell(rowNumber, 1).Value = "=INT(10" + dsep + "8)"

			rowNumber += 2

			' Example on SIGN() function in formula
			sheet.Cell(rowNumber, 0).Value = "SIGN(10" + dsep + "8)"
			sheet.Cell(rowNumber, 1).Value = "=SIGN(10" + dsep + "8)"

			rowNumber += 2

			' Example on ROUND() function in formula
			sheet.Cell(rowNumber, 0).Value = "ROUND(10" + dsep + "862456" + lsep + "4)"
			sheet.Cell(rowNumber, 1).Value = "=ROUND(10" + dsep + "862456" + lsep + "4)"

			rowNumber += 2

			' Example on RADIANS() function in formula
			sheet.Cell(rowNumber, 0).Value = "RADIANS(180)"
			sheet.Cell(rowNumber, 1).Value = "=RADIANS(180)"

			rowNumber += 2

			' Example on DEGREES() function in formula
			sheet.Cell(rowNumber, 0).Value = "DEGREES(3" + dsep + "14)"
			sheet.Cell(rowNumber, 1).Value = "=DEGREES(3" + dsep + "14)"

			rowNumber += 2

			' Example on LEN() function in formula
			sheet.Cell(rowNumber, 0).Value = "LEN(""Bytescout"")"
			sheet.Cell(rowNumber, 1).Value = "=LEN(""Bytescout"")"

			rowNumber += 2

			' Example on MID() function in formula
			sheet.Cell(rowNumber, 0).Value = "MID(""Bytescout""" + lsep + "5" + lsep + "5)"
			sheet.Cell(rowNumber, 1).Value = "=MID(""Bytescout""" + lsep + "5" + lsep + "5)"

			rowNumber += 2

			' Example on NOW() function in formula
			sheet.Cell(rowNumber, 0).Value = "NOW()"
			sheet.Cell(rowNumber, 1).Value = "=NOW()"

			rowNumber += 2

			' Example on DATE() function in formula
			sheet.Cell(rowNumber, 0).Value = "DATE(2009" + lsep + "1" + lsep + "2)"
			sheet.Cell(rowNumber, 1).Value = "=DATE(2009" + lsep + "1" + lsep + "2)"

			rowNumber += 2

			' Example on TIME() function in formula
			sheet.Cell(rowNumber, 0).Value = "TIME(1" + lsep + "1" + lsep + "2)"
			sheet.Cell(rowNumber, 1).Value = "=TIME(1" + lsep + "1" + lsep + "2)"

			rowNumber += 2

			' Example on SECOND() function in formula
			sheet.Cell(rowNumber, 0).Value = "SECOND(""18:45:02"")"
			sheet.Cell(rowNumber, 1).Value = "=SECOND(""18:45:02"")"

			rowNumber += 2

			' Example on MINUTE() function in formula
			sheet.Cell(rowNumber, 0).Value = "MINUTE(""18:45:02"")"
			sheet.Cell(rowNumber, 1).Value = "=MINUTE(""18:45:02"")"

			rowNumber += 2

			' Example on HOUR() function in formula
			sheet.Cell(rowNumber, 0).Value = "HOUR(""18:45:02"")"
			sheet.Cell(rowNumber, 1).Value = "=HOUR(""18:45:02"")"

			rowNumber += 2

			' Example on YEAR() function in formula
			sheet.Cell(rowNumber, 0).Value = "YEAR(NOW())"
			sheet.Cell(rowNumber, 1).Value = "=YEAR(NOW())"

			rowNumber += 2

			' Example on MONTH() function in formula
			sheet.Cell(rowNumber, 0).Value = "MONTH(NOW())"
			sheet.Cell(rowNumber, 1).Value = "=MONTH(NOW())"

			rowNumber += 2

			' Example on DAY() function in formula
			sheet.Cell(rowNumber, 0).Value = "DAY(NOW())"
			sheet.Cell(rowNumber, 1).Value = "=DAY(NOW())"

			rowNumber += 2

			' Example on WEEKDAY() function in formula
			sheet.Cell(rowNumber, 0).Value = "WEEKDAY(NOW())"
			sheet.Cell(rowNumber, 1).Value = "=WEEKDAY(NOW())"

			rowNumber += 2

			' Example on FALSE in formula
			sheet.Cell(rowNumber, 0).Value = "FALSE"
			sheet.Cell(rowNumber, 1).Value = "=FALSE"

			rowNumber += 2

			' Example on TRUE in formula
			sheet.Cell(rowNumber, 0).Value = "TRUE"
			sheet.Cell(rowNumber, 1).Value = "=TRUE"

			rowNumber += 2

			' Example on AND() function in formula
			sheet.Cell(rowNumber, 0).Value = "AND"
			sheet.Cell(rowNumber, 1).Value = "=AND(10>1" + lsep + "10<100)"

			rowNumber += 2

			' Example on OR() function in formula
			sheet.Cell(rowNumber, 0).Value = "OR(10>1" + lsep + "10<100)"
			sheet.Cell(rowNumber, 1).Value = "=OR(10>1" + lsep + "10<100)"

			rowNumber += 2

			' Example on NOT() function in formula
			sheet.Cell(rowNumber, 0).Value = "NOT(1+1=2)"
			sheet.Cell(rowNumber, 1).Value = "=NOT(1+1=2)"

			rowNumber += 2

			' Example on ISNA() function in formula
			sheet.Cell(rowNumber, 0).Value = "ISNA(NA())"
			sheet.Cell(rowNumber, 1).Value = "=ISNA(NA())"

			rowNumber += 2

			' Example on NA() function in formula
			sheet.Cell(rowNumber, 0).Value = "NA()"
			sheet.Cell(rowNumber, 1).Value = "=NA()"

			rowNumber += 2

			' Example on ISERROR() function in formula
			sheet.Cell(rowNumber, 0).Value = "ISERROR(1/0)"
			sheet.Cell(rowNumber, 1).Value = "=ISERROR(1/0)"

			rowNumber += 2

			' Example on ROW() function in formula
			sheet.Cell(rowNumber, 0).Value = "ROW()"
			sheet.Cell(rowNumber, 1).Value = "=ROW()"

			rowNumber += 2

			' Example on COLUMN() function in formula
			sheet.Cell(rowNumber, 0).Value = "COLUMN()"
			sheet.Cell(rowNumber, 1).Value = "=COLUMN()"

			rowNumber += 2

			' Example on COUNT() function in formula
			sheet.Cell(rowNumber, 0).Value = "COUNT(B1:B10)"
			sheet.Cell(rowNumber, 1).Value = "=COUNT(B1:B10)"

			rowNumber += 2

			' Example on COUNTA() function in formula
			sheet.Cell(rowNumber, 0).Value = "COUNTA(B1:B10)"
			sheet.Cell(rowNumber, 1).Value = "=COUNTA(B1:B10)"

			rowNumber += 2

			' Example on COUNTA() function in formula
			sheet.Cell(rowNumber, 0).Value = "IF(1=1" + lsep + "TRUE" + lsep + "FALSE)"
			sheet.Cell(rowNumber, 1).Value = "=IF(1=1" + lsep + "TRUE" + lsep + "FALSE)"

			rowNumber += 2

			' Example on COUNTA() function in formula
			sheet.Cell(rowNumber, 0).Value = "NPV(10%" + lsep + "10000" + lsep + "1000" + lsep + "2000" + lsep + "300)"
			sheet.Cell(rowNumber, 1).Value = "=NPV(10%" + lsep + "10000" + lsep + "1000" + lsep + "2000" + lsep + "300)"

			Try
				System.IO.File.Delete("output.xls")
			Catch ex As Exception
			End Try

			spreadsheet.SaveAs("output.xls")

			spreadsheet.Close()

			System.Diagnostics.Process.Start("output.xls")
    End Sub
End Module

Resources.Designer.vb

��'------------------------------------------------------------------------------

' <auto-generated>

'     This code was generated by a tool.

'     Runtime Version:2.0.50727.42

'

'     Changes to this file may cause incorrect behavior and will be lost if

'     the code is regenerated.

' </auto-generated>

'------------------------------------------------------------------------------

Option Strict On

Option Explicit On

Namespace My.Resources

    'This class was auto-generated by the StronglyTypedResourceBuilder

    'class via a tool like ResGen or Visual Studio.

    'To add or remove a member, edit your .ResX file then rerun ResGen

    'with the /str option, or rebuild your VS project.

    '<summary>

    '  A strongly-typed resource class, for looking up localized strings, etc.

    '</summary>

    <Global.System.CodeDom.Compiler.GeneratedCodeAttribute("System.Resources.Tools.StronglyTypedResourceBuilder", "2.0.0.0"),  _

     Global.System.Diagnostics.DebuggerNonUserCodeAttribute(),  _

     Global.System.Runtime.CompilerServices.CompilerGeneratedAttribute(),  _

     Global.Microsoft.VisualBasic.HideModuleNameAttribute()>  _

    Friend Module Resources

        Private resourceMan As Global.System.Resources.ResourceManager

        Private resourceCulture As Global.System.Globalization.CultureInfo

        '<summary>

        '  Returns the cached ResourceManager instance used by this class.

        '</summary>

        <Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)>  _

        Friend ReadOnly Property ResourceManager() As Global.System.Resources.ResourceManager

            Get

                If Object.ReferenceEquals(resourceMan, Nothing) Then

                    Dim temp As Global.System.Resources.ResourceManager = New Global.System.Resources.ResourceManager("VB.NET.Resources", GetType(Resources).Assembly)

                    resourceMan = temp

                End If

                Return resourceMan

            End Get

        End Property

        '<summary>

        '  Overrides the current thread's CurrentUICulture property for all

        '  resource lookups using this strongly typed resource class.

        '</summary>

        <Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)>  _

        Friend Property Culture() As Global.System.Globalization.CultureInfo

            Get

                Return resourceCulture

            End Get

            Set(ByVal value As Global.System.Globalization.CultureInfo)

                resourceCulture = value

            End Set

        End Property

    End Module

End Namespace

Settings.Designer.vb

��'------------------------------------------------------------------------------

' <auto-generated>

'     This code was generated by a tool.

'     Runtime Version:2.0.50727.42

'

'     Changes to this file may cause incorrect behavior and will be lost if

'     the code is regenerated.

' </auto-generated>

'------------------------------------------------------------------------------

Option Strict On

Option Explicit On

Namespace My

    <Global.System.Runtime.CompilerServices.CompilerGeneratedAttribute(),  _

     Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "8.0.0.0"),  _

     Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)>  _

    Partial Friend NotInheritable Class MySettings

        Inherits Global.System.Configuration.ApplicationSettingsBase

        Private Shared defaultInstance As MySettings = CType(Global.System.Configuration.ApplicationSettingsBase.Synchronized(New MySettings), MySettings)

#Region "My.Settings Auto-Save Functionality"

#If _MyType = "WindowsForms" Then

        Private Shared addedHandler As Boolean

        Private Shared addedHandlerLockObject As New Object

        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)> _

        Private Shared Sub AutoSaveSettings(ByVal sender As Global.System.Object, ByVal e As Global.System.EventArgs)

            If My.Application.SaveMySettingsOnExit Then

                My.Settings.Save()

            End If

        End Sub

#End If

#End Region

        Public Shared ReadOnly Property [Default]() As MySettings

            Get

#If _MyType = "WindowsForms" Then

                   If Not addedHandler Then

                        SyncLock addedHandlerLockObject

                            If Not addedHandler Then

                                AddHandler My.Application.Shutdown, AddressOf AutoSaveSettings

                                addedHandler = True

                            End If

                        End SyncLock

                    End If

#End If

                Return defaultInstance

            End Get

        End Property

    End Class

End Namespace

Namespace My

    <Global.Microsoft.VisualBasic.HideModuleNameAttribute(),  _

     Global.System.Diagnostics.DebuggerNonUserCodeAttribute(),  _

     Global.System.Runtime.CompilerServices.CompilerGeneratedAttribute()>  _

    Friend Module MySettingsProperty

        <Global.System.ComponentModel.Design.HelpKeywordAttribute("My.Settings")>  _

        Friend ReadOnly Property Settings() As Global.VB.NET.My.MySettings

            Get

                Return Global.VB.NET.My.MySettings.Default

            End Get

        End Property

    End Module

End Namespace

Calculate Formula In Cell - VB.NET

Module1.vb

Resources.Designer.vb

Settings.Designer.vb

Download Source Code (.zip)

Return to the previous page Explore Spreadsheet SDK