Return to previous page Explore Spreadsheet SDK

Calculations and Spreadsheet as Database - C#

Spreadsheet SDK sample in C# demonstrating ‘Calculations and Spreadsheet as Database’

View on GitHub Download Source Code (.zip)

Form1.Designer.cs

��namespace SimpleDatabase

{

    partial class Form1

    {

        /// <summary>

        /// Required designer variable.

        /// </summary>

        private System.ComponentModel.IContainer components = null;

        /// <summary>

        /// Clean up any resources being used.

        /// </summary>

        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>

        protected override void Dispose(bool disposing)

        {

            if (disposing && (components != null))

            {

                components.Dispose();

            }

            base.Dispose(disposing);

        }

        #region Windows Form Designer generated code

        /// <summary>

        /// Required method for Designer support - do not modify

        /// the contents of this method with the code editor.

        /// </summary>

        private void InitializeComponent()

        {

            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(Form1));

            this.Label3 = new System.Windows.Forms.Label();

            this.TextBox3 = new System.Windows.Forms.TextBox();

            this.Label4 = new System.Windows.Forms.Label();

            this.Button2 = new System.Windows.Forms.Button();

            this.Button4 = new System.Windows.Forms.Button();

            this.Button1 = new System.Windows.Forms.Button();

            this.Button3 = new System.Windows.Forms.Button();

            this.TextBox4 = new System.Windows.Forms.TextBox();

            this.Label5 = new System.Windows.Forms.Label();

            this.TextBox2 = new System.Windows.Forms.TextBox();

            this.Label2 = new System.Windows.Forms.Label();

            this.GroupBox1 = new System.Windows.Forms.GroupBox();

            this.TextBox1 = new System.Windows.Forms.TextBox();

            this.Label1 = new System.Windows.Forms.Label();

            this.GroupBox1.SuspendLayout();

            this.SuspendLayout();

            // 

            // Label3

            // 

            this.Label3.AutoSize = true;

            this.Label3.Location = new System.Drawing.Point(6, 94);

            this.Label3.Name = "Label3";

            this.Label3.Size = new System.Drawing.Size(39, 13);

            this.Label3.TabIndex = 12;

            this.Label3.Text = "Label3";

            // 

            // TextBox3

            // 

            this.TextBox3.Location = new System.Drawing.Point(9, 110);

            this.TextBox3.Name = "TextBox3";

            this.TextBox3.Size = new System.Drawing.Size(135, 20);

            this.TextBox3.TabIndex = 11;

            // 

            // Label4

            // 

            this.Label4.AutoSize = true;

            this.Label4.Location = new System.Drawing.Point(6, 133);

            this.Label4.Name = "Label4";

            this.Label4.Size = new System.Drawing.Size(39, 13);

            this.Label4.TabIndex = 10;

            this.Label4.Text = "Label4";

            // 

            // Button2

            // 

            this.Button2.Location = new System.Drawing.Point(184, 45);

            this.Button2.Name = "Button2";

            this.Button2.Size = new System.Drawing.Size(100, 23);

            this.Button2.TabIndex = 7;

            this.Button2.Text = "Save to XLS";

            this.Button2.UseVisualStyleBackColor = true;

            this.Button2.Click += new System.EventHandler(this.Button2_Click);

            // 

            // Button4

            // 

            this.Button4.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(204)));

            this.Button4.Location = new System.Drawing.Point(9, 175);

            this.Button4.Name = "Button4";

            this.Button4.Size = new System.Drawing.Size(135, 23);

            this.Button4.TabIndex = 14;

            this.Button4.Text = "Recalculate";

            this.Button4.UseVisualStyleBackColor = true;

            this.Button4.Click += new System.EventHandler(this.Button4_Click);

            // 

            // Button1

            // 

            this.Button1.Location = new System.Drawing.Point(184, 16);

            this.Button1.Name = "Button1";

            this.Button1.Size = new System.Drawing.Size(100, 23);

            this.Button1.TabIndex = 6;

            this.Button1.Text = "Load from XLS";

            this.Button1.UseVisualStyleBackColor = true;

            this.Button1.Click += new System.EventHandler(this.Button1_Click);

            // 

            // Button3

            // 

            this.Button3.Location = new System.Drawing.Point(184, 74);

            this.Button3.Name = "Button3";

            this.Button3.Size = new System.Drawing.Size(100, 23);

            this.Button3.TabIndex = 8;

            this.Button3.Text = "View in Excel";

            this.Button3.UseVisualStyleBackColor = true;

            this.Button3.Click += new System.EventHandler(this.Button3_Click);

            // 

            // TextBox4

            // 

            this.TextBox4.Location = new System.Drawing.Point(9, 149);

            this.TextBox4.Name = "TextBox4";

            this.TextBox4.Size = new System.Drawing.Size(135, 20);

            this.TextBox4.TabIndex = 13;

            // 

            // Label5

            // 

            this.Label5.Location = new System.Drawing.Point(9, 240);

            this.Label5.Name = "Label5";

            this.Label5.Size = new System.Drawing.Size(275, 113);

            this.Label5.TabIndex = 9;

            this.Label5.Text = resources.GetString("Label5.Text");

            // 

            // TextBox2

            // 

            this.TextBox2.Location = new System.Drawing.Point(9, 71);

            this.TextBox2.Name = "TextBox2";

            this.TextBox2.Size = new System.Drawing.Size(135, 20);

            this.TextBox2.TabIndex = 9;

            // 

            // Label2

            // 

            this.Label2.AutoSize = true;

            this.Label2.Location = new System.Drawing.Point(6, 55);

            this.Label2.Name = "Label2";

            this.Label2.Size = new System.Drawing.Size(39, 13);

            this.Label2.TabIndex = 8;

            this.Label2.Text = "Label2";

            // 

            // GroupBox1

            // 

            this.GroupBox1.Controls.Add(this.Button4);

            this.GroupBox1.Controls.Add(this.TextBox4);

            this.GroupBox1.Controls.Add(this.Label3);

            this.GroupBox1.Controls.Add(this.TextBox3);

            this.GroupBox1.Controls.Add(this.Label4);

            this.GroupBox1.Controls.Add(this.TextBox2);

            this.GroupBox1.Controls.Add(this.Label2);

            this.GroupBox1.Controls.Add(this.TextBox1);

            this.GroupBox1.Controls.Add(this.Label1);

            this.GroupBox1.Location = new System.Drawing.Point(12, 10);

            this.GroupBox1.Name = "GroupBox1";

            this.GroupBox1.Size = new System.Drawing.Size(166, 212);

            this.GroupBox1.TabIndex = 5;

            this.GroupBox1.TabStop = false;

            // 

            // TextBox1

            // 

            this.TextBox1.Location = new System.Drawing.Point(9, 32);

            this.TextBox1.Name = "TextBox1";

            this.TextBox1.Size = new System.Drawing.Size(135, 20);

            this.TextBox1.TabIndex = 7;

            // 

            // Label1

            // 

            this.Label1.AutoSize = true;

            this.Label1.Location = new System.Drawing.Point(6, 16);

            this.Label1.Name = "Label1";

            this.Label1.Size = new System.Drawing.Size(39, 13);

            this.Label1.TabIndex = 6;

            this.Label1.Text = "Label1";

            // 

            // Form1

            // 

            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);

            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;

            this.ClientSize = new System.Drawing.Size(292, 362);

            this.Controls.Add(this.Button2);

            this.Controls.Add(this.Button1);

            this.Controls.Add(this.Button3);

            this.Controls.Add(this.Label5);

            this.Controls.Add(this.GroupBox1);

            this.Name = "Form1";

            this.Text = "Form1";

            this.FormClosing += new System.Windows.Forms.FormClosingEventHandler(this.Form1_FormClosing);

            this.Load += new System.EventHandler(this.Form1_Load);

            this.GroupBox1.ResumeLayout(false);

            this.GroupBox1.PerformLayout();

            this.ResumeLayout(false);

        }

        #endregion

        internal System.Windows.Forms.Label Label3;

        internal System.Windows.Forms.TextBox TextBox3;

        internal System.Windows.Forms.Label Label4;

        internal System.Windows.Forms.Button Button2;

        internal System.Windows.Forms.Button Button4;

        internal System.Windows.Forms.Button Button1;

        internal System.Windows.Forms.Button Button3;

        internal System.Windows.Forms.TextBox TextBox4;

        internal System.Windows.Forms.Label Label5;

        internal System.Windows.Forms.TextBox TextBox2;

        internal System.Windows.Forms.Label Label2;

        internal System.Windows.Forms.GroupBox GroupBox1;

        internal System.Windows.Forms.TextBox TextBox1;

        internal System.Windows.Forms.Label Label1;

    }

}

Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using Bytescout.Spreadsheet;

namespace SimpleDatabase
{
    public partial class Form1 : Form
    {
        Spreadsheet _spreadsheet;

        private void LoadXLS()
        {
            Worksheet worksheet;
            _spreadsheet = new Spreadsheet();
            _spreadsheet.LoadFromFile("Database.xls");

            worksheet = _spreadsheet.Workbook.Worksheets[0];
            Label1.Text = (string)worksheet.Cell("A1").Value;
            TextBox1.Text = worksheet.Cell("A2").Value.ToString();

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

            Label3.Text = (string)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.ToString();
        }

        private void SaveXLS()
        {
            Worksheet 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 (Exception ex)
            {
            }

            _spreadsheet.SaveAs("Database.xls");
        }

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            SaveXLS();

            _spreadsheet.Close();
        }

        private void ReCalculate()
        {
            Worksheet 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.ToString();
        }

        private void Button4_Click(object sender, EventArgs e)
        {
            ReCalculate();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            LoadXLS();
        }

        private void Button3_Click(object sender, EventArgs e)
        {
            // open in default spreadsheets viewer/editor
            SaveXLS();
            try
            {
                Process.Start("Database.xls");
            }
            catch
            {
            }
        }

        private void Button1_Click(object sender, EventArgs e)
        {
            LoadXLS();
            MessageBox.Show("Loaded from Database.xls");
        }

        private void Button2_Click(object sender, EventArgs e)
        {
            SaveXLS();
            MessageBox.Show("Saved into Database.xls");
        }
    }
}

Program.cs

using System;
using System.Collections.Generic;
using System.Windows.Forms;

namespace SimpleDatabase
{
    static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}

Resources.Designer.cs

��//------------------------------------------------------------------------------

// <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>

//------------------------------------------------------------------------------

namespace SimpleDatabase.Properties

{

    /// <summary>

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

    /// </summary>

    // 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.

    [global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Resources.Tools.StronglyTypedResourceBuilder", "2.0.0.0")]

    [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]

    [global::System.Runtime.CompilerServices.CompilerGeneratedAttribute()]

    internal class Resources

    {

        private static global::System.Resources.ResourceManager resourceMan;

        private static global::System.Globalization.CultureInfo resourceCulture;

        [global::System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("Microsoft.Performance", "CA1811:AvoidUncalledPrivateCode")]

        internal Resources()

        {

        }

        /// <summary>

        ///   Returns the cached ResourceManager instance used by this class.

        /// </summary>

        [global::System.ComponentModel.EditorBrowsableAttribute(global::System.ComponentModel.EditorBrowsableState.Advanced)]

        internal static global::System.Resources.ResourceManager ResourceManager

        {

            get

            {

                if ((resourceMan == null))

                {

                    global::System.Resources.ResourceManager temp = new global::System.Resources.ResourceManager("SimpleDatabase.Properties.Resources", typeof(Resources).Assembly);

                    resourceMan = temp;

                }

                return resourceMan;

            }

        }

        /// <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)]

        internal static global::System.Globalization.CultureInfo Culture

        {

            get

            {

                return resourceCulture;

            }

            set

            {

                resourceCulture = value;

            }

        }

    }

}

Settings.Designer.cs

��//------------------------------------------------------------------------------

// <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>

//------------------------------------------------------------------------------

namespace SimpleDatabase.Properties

{

    [global::System.Runtime.CompilerServices.CompilerGeneratedAttribute()]

    [global::System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "8.0.0.0")]

    internal sealed partial class Settings : global::System.Configuration.ApplicationSettingsBase

    {

        private static Settings defaultInstance = ((Settings)(global::System.Configuration.ApplicationSettingsBase.Synchronized(new Settings())));

        public static Settings Default

        {

            get

            {

                return defaultInstance;

            }

        }

    }

}

Calculations and Spreadsheet as Database - C#

Form1.Designer.cs

Form1.cs

Program.cs

Resources.Designer.cs

Settings.Designer.cs

Download Source Code (.zip)

Return to the previous page Explore Spreadsheet SDK