Link Search Menu Expand Document
ByteScout Docs

Return to previous pageExplore Spreadsheet SDK

Calculations and Spreadsheet as Database - VB.NET

Spreadsheet SDK sample in VB.NET demonstrating ‘Calculations and Spreadsheet as Database’

View on GitHub Download Source Code (.zip)
Form1.Designer.vb
��<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _

Partial Class Form1

    Inherits System.Windows.Forms.Form



    'Form overrides dispose to clean up the component list.

    <System.Diagnostics.DebuggerNonUserCode()> _

    Protected Overrides Sub Dispose(ByVal disposing As Boolean)

        If disposing AndAlso components IsNot Nothing Then

            components.Dispose()

        End If

        MyBase.Dispose(disposing)

    End Sub



    'Required by the Windows Form Designer

    Private components As System.ComponentModel.IContainer



    'NOTE: The following procedure is required by the Windows Form Designer

    'It can be modified using the Windows Form Designer.  

    'Do not modify it using the code editor.

    <System.Diagnostics.DebuggerStepThrough()> _

    Private Sub InitializeComponent()

        Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(Form1))

        Me.GroupBox1 = New System.Windows.Forms.GroupBox

        Me.Button4 = New System.Windows.Forms.Button

        Me.TextBox4 = New System.Windows.Forms.TextBox

        Me.Label3 = New System.Windows.Forms.Label

        Me.TextBox3 = New System.Windows.Forms.TextBox

        Me.Label4 = New System.Windows.Forms.Label

        Me.TextBox2 = New System.Windows.Forms.TextBox

        Me.Label2 = New System.Windows.Forms.Label

        Me.TextBox1 = New System.Windows.Forms.TextBox

        Me.Label1 = New System.Windows.Forms.Label

        Me.Button1 = New System.Windows.Forms.Button

        Me.Button2 = New System.Windows.Forms.Button

        Me.Button3 = New System.Windows.Forms.Button

        Me.Label5 = New System.Windows.Forms.Label

        Me.GroupBox1.SuspendLayout()

        Me.SuspendLayout()

        '

        'GroupBox1

        '

        Me.GroupBox1.Controls.Add(Me.Button4)

        Me.GroupBox1.Controls.Add(Me.TextBox4)

        Me.GroupBox1.Controls.Add(Me.Label3)

        Me.GroupBox1.Controls.Add(Me.TextBox3)

        Me.GroupBox1.Controls.Add(Me.Label4)

        Me.GroupBox1.Controls.Add(Me.TextBox2)

        Me.GroupBox1.Controls.Add(Me.Label2)

        Me.GroupBox1.Controls.Add(Me.TextBox1)

        Me.GroupBox1.Controls.Add(Me.Label1)

        Me.GroupBox1.Location = New System.Drawing.Point(12, 6)

        Me.GroupBox1.Name = "GroupBox1"

        Me.GroupBox1.Size = New System.Drawing.Size(166, 212)

        Me.GroupBox1.TabIndex = 0

        Me.GroupBox1.TabStop = False

        '

        'Button4

        '

        Me.Button4.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(204, Byte))

        Me.Button4.Location = New System.Drawing.Point(9, 175)

        Me.Button4.Name = "Button4"

        Me.Button4.Size = New System.Drawing.Size(135, 23)

        Me.Button4.TabIndex = 14

        Me.Button4.Text = "Recalculate"

        Me.Button4.UseVisualStyleBackColor = True

        '

        'TextBox4

        '

        Me.TextBox4.Location = New System.Drawing.Point(9, 149)

        Me.TextBox4.Name = "TextBox4"

        Me.TextBox4.Size = New System.Drawing.Size(135, 20)

        Me.TextBox4.TabIndex = 13

        '

        'Label3

        '

        Me.Label3.AutoSize = True

        Me.Label3.Location = New System.Drawing.Point(6, 94)

        Me.Label3.Name = "Label3"

        Me.Label3.Size = New System.Drawing.Size(39, 13)

        Me.Label3.TabIndex = 12

        Me.Label3.Text = "Label3"

        '

        'TextBox3

        '

        Me.TextBox3.Location = New System.Drawing.Point(9, 110)

        Me.TextBox3.Name = "TextBox3"

        Me.TextBox3.Size = New System.Drawing.Size(135, 20)

        Me.TextBox3.TabIndex = 11

        '

        'Label4

        '

        Me.Label4.AutoSize = True

        Me.Label4.Location = New System.Drawing.Point(6, 133)

        Me.Label4.Name = "Label4"

        Me.Label4.Size = New System.Drawing.Size(39, 13)

        Me.Label4.TabIndex = 10

        Me.Label4.Text = "Label4"

        '

        'TextBox2

        '

        Me.TextBox2.Location = New System.Drawing.Point(9, 71)

        Me.TextBox2.Name = "TextBox2"

        Me.TextBox2.Size = New System.Drawing.Size(135, 20)

        Me.TextBox2.TabIndex = 9

        '

        'Label2

        '

        Me.Label2.AutoSize = True

        Me.Label2.Location = New System.Drawing.Point(6, 55)

        Me.Label2.Name = "Label2"

        Me.Label2.Size = New System.Drawing.Size(39, 13)

        Me.Label2.TabIndex = 8

        Me.Label2.Text = "Label2"

        '

        'TextBox1

        '

        Me.TextBox1.Location = New System.Drawing.Point(9, 32)

        Me.TextBox1.Name = "TextBox1"

        Me.TextBox1.Size = New System.Drawing.Size(135, 20)

        Me.TextBox1.TabIndex = 7

        '

        'Label1

        '

        Me.Label1.AutoSize = True

        Me.Label1.Location = New System.Drawing.Point(6, 16)

        Me.Label1.Name = "Label1"

        Me.Label1.Size = New System.Drawing.Size(39, 13)

        Me.Label1.TabIndex = 6

        Me.Label1.Text = "Label1"

        '

        'Button1

        '

        Me.Button1.Location = New System.Drawing.Point(184, 12)

        Me.Button1.Name = "Button1"

        Me.Button1.Size = New System.Drawing.Size(100, 23)

        Me.Button1.TabIndex = 1

        Me.Button1.Text = "Load from XLS"

        Me.Button1.UseVisualStyleBackColor = True

        '

        'Button2

        '

        Me.Button2.Location = New System.Drawing.Point(184, 41)

        Me.Button2.Name = "Button2"

        Me.Button2.Size = New System.Drawing.Size(100, 23)

        Me.Button2.TabIndex = 2

        Me.Button2.Text = "Save to XLS"

        Me.Button2.UseVisualStyleBackColor = True

        '

        'Button3

        '

        Me.Button3.Location = New System.Drawing.Point(184, 70)

        Me.Button3.Name = "Button3"

        Me.Button3.Size = New System.Drawing.Size(100, 23)

        Me.Button3.TabIndex = 3

        Me.Button3.Text = "View in Excel"

        Me.Button3.UseVisualStyleBackColor = True

        '

        'Label5

        '

        Me.Label5.Location = New System.Drawing.Point(9, 236)

        Me.Label5.Name = "Label5"

        Me.Label5.Size = New System.Drawing.Size(275, 113)

        Me.Label5.TabIndex = 4

        Me.Label5.Text = resources.GetString("Label5.Text")

        '

        'Form1

        '

        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)

        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font

        Me.ClientSize = New System.Drawing.Size(294, 372)

        Me.Controls.Add(Me.Label5)

        Me.Controls.Add(Me.Button3)

        Me.Controls.Add(Me.Button2)

        Me.Controls.Add(Me.Button1)

        Me.Controls.Add(Me.GroupBox1)

        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedToolWindow

        Me.Name = "Form1"

        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen

        Me.Text = "Simple Database"

        Me.GroupBox1.ResumeLayout(False)

        Me.GroupBox1.PerformLayout()

        Me.ResumeLayout(False)



    End Sub

    Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox

    Friend WithEvents TextBox3 As System.Windows.Forms.TextBox

    Friend WithEvents Label4 As System.Windows.Forms.Label

    Friend WithEvents TextBox2 As System.Windows.Forms.TextBox

    Friend WithEvents Label2 As System.Windows.Forms.Label

    Friend WithEvents TextBox1 As System.Windows.Forms.TextBox

    Friend WithEvents Label1 As System.Windows.Forms.Label

    Friend WithEvents TextBox4 As System.Windows.Forms.TextBox

    Friend WithEvents Label3 As System.Windows.Forms.Label

    Friend WithEvents Button4 As System.Windows.Forms.Button

    Friend WithEvents Button1 As System.Windows.Forms.Button

    Friend WithEvents Button2 As System.Windows.Forms.Button

    Friend WithEvents Button3 As System.Windows.Forms.Button

    Friend WithEvents Label5 As System.Windows.Forms.Label



End Class
Form1.vb
Imports Bytescout.Spreadsheet
Imports System.Diagnostics

Public Class Form1
    Dim _spreadsheet As Spreadsheet
    Private Sub LoadXLS()
        Dim worksheet As Worksheet
        _spreadsheet = New Spreadsheet()
        _spreadsheet.LoadFromFile("Database.xls")

        worksheet = _spreadsheet.WorkBook.Worksheets(0)
        Label1.Text = worksheet.Cell("A1").Value
        TextBox1.Text = worksheet.Cell("A2").Value

        Label2.Text = worksheet.Cell("B1").Value
        TextBox2.Text = worksheet.Cell("B2").Value

        Label3.Text = worksheet.Cell("C1").Value
        TextBox3.Text = worksheet.Cell("C2").Formula

        Label4.Text = "Calculated value of C2"
        worksheet.Cell("C2").Calculate()
        TextBox4.Text = worksheet.Cell("C2").Value
    End Sub

    Private Sub SaveXLS()
        Dim worksheet As Worksheet

        worksheet = _spreadsheet.WorkBook.Worksheets(0)

        worksheet.Cell("A2").Value = System.Convert.ToInt32(TextBox1.Text)
        worksheet.Cell("B2").Value = System.Convert.ToInt32(TextBox2.Text)
        worksheet.Cell("C2").Formula = TextBox3.Text

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

        _spreadsheet.SaveAs("Database.xls")
    End Sub

    Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        SaveXLS()

        _spreadsheet.Close()
    End Sub


    Private Sub ReCalculate()
        Dim worksheet As Worksheet

        worksheet = _spreadsheet.Workbook.Worksheets(0)

        worksheet.Cell("A2").Value = System.Convert.ToInt32(TextBox1.Text)
        worksheet.Cell("B2").Value = System.Convert.ToInt32(TextBox2.Text)
        worksheet.Cell("C2").Formula = TextBox3.Text
        worksheet.Cell("C2").Calculate()
        TextBox4.Text = worksheet.Cell("C2").Value
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        ReCalculate()
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadXLS()
    End Sub


    Private Sub TextBox_Recalculate(ByVal sender As System.Object, ByVal e As System.EventArgs)
        ReCalculate()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        ' open in default spreadsheets viewer/editor
        SaveXLS()
        Try
            Process.Start("Database.xls")
        Catch
        End Try

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        LoadXLS()
        MessageBox.Show("Loaded from Database.xls")
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        SaveXLS()
        MessageBox.Show("Saved into Database.xls")
    End Sub
End Class
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("SimpleDatabase.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.SimpleDatabase.My.MySettings

            Get

                Return Global.SimpleDatabase.My.MySettings.Default

            End Get

        End Property

    End Module

End Namespace

Download Source Code (.zip)

Return to the previous page Explore Spreadsheet SDK