Link Search Menu Expand Document

Calculate Formula In Cell - VB.NET

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

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


Download Source Code (.zip)

Return to the previous page Explore Spreadsheet SDK


Copyright © 2016 - 2021 ByteScout