Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version
Go to top

A Calculation Engine for .NET

, 1 Sep 2013
A calculation engine that is small, fast, and extensible.
using System;
using System.Drawing;
using System.Collections.Generic;
using System.Windows.Forms;

namespace CalcEngineDemo
{
    public class DataGridCalc : DataGridView
    {
        // fields
        DataGridCalcEngine _ce;
        DataGridViewCellStyle _styleNumber, _styleDate;
        const string FORMAT_NUMBER = "#,##0.#########";
        const string FORMAT_DATE = "d";

        // ctor
        public DataGridCalc()
        {
            // create calc engine
            _ce = new DataGridCalcEngine(this);

            // row headers must be wider to show the row number
            this.RowHeadersWidth = 50;

            // center-align row and column headers
            this.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            this.RowHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

            // define the format to use for showing calculated numbers
            _styleNumber = new DataGridViewCellStyle(this.DefaultCellStyle);
            _styleNumber.Format = FORMAT_NUMBER;
            _styleNumber.Alignment = DataGridViewContentAlignment.MiddleRight;
            _styleDate = new DataGridViewCellStyle(this.DefaultCellStyle);
            _styleDate.Format = FORMAT_DATE;

            // make grid lines more subtle
            this.GridColor = Color.FromArgb(0xf0, 0xf0, 0xf0);
        }

        // ** object model

        // expose arbitrary objects to calc engine
        public object DataContext { get; set; }

        // gets the value in a cell
        public object Evaluate(int rowIndex, int colIndex)
        {
            // get the value
            var val = this.Rows[rowIndex].Cells[colIndex].Value;
            var text = val as string;
            if (!string.IsNullOrEmpty(text) && text[0] == '=')
            {
                val = Evaluate(text);
            }
            return val;
        }

        // evaluates an expression
        public object Evaluate(string expression)
        {
            return _ce.Evaluate(expression);
        }

        // gets the address of a cell using Excel notation (e.g. A1)
        public string GetAddress(int row, int col)
        {
            return string.Format("{0}{1}",
                GetAlphaColumnHeader(col),
                row + 1);
        }

        // gets the address of a cell range using Excel notation (e.g. A1:B10)
        public string GetAddress(CellRange rng)
        {
            return rng.IsSingleCell
                ? GetAddress(rng.r1, rng.c1)
                : string.Format("{0}:{1}", GetAddress(rng.r1, rng.c1), GetAddress(rng.r2, rng.c2));
        }

        // gets an alpha numeric header for a column (e.g. A, B, C, AA, AB, AC, etc)
        public static string GetAlphaColumnHeader(int i)
        {
            return GetAlphaColumnHeader(i, string.Empty);
        }
        static string GetAlphaColumnHeader(int i, string s)
        {
            var rem = i % 26;
            s = (char)((int)'A' + rem) + s;
            i = i / 26 - 1;
            return i < 0 ? s : GetAlphaColumnHeader(i, s);
        }

        // ** overrides

        // show row numbers in row headers
        protected override void OnRowPrePaint(DataGridViewRowPrePaintEventArgs e)
        {
            var cell = this.Rows[e.RowIndex].HeaderCell;
            var label = (e.RowIndex + 1).ToString();
            if (!object.Equals(cell.Value, label))
            {
                cell.Value = label;
            }
            base.OnRowPrePaint(e);
        }

        // evaluate expressions when showing cells
        protected override void OnCellFormatting(DataGridViewCellFormattingEventArgs e)
        {
            // get the cell
            var cell = this.Rows[e.RowIndex].Cells[e.ColumnIndex];

            // if not in edit mode, calculate value
            if (cell != null && !cell.IsInEditMode)
            {
                var val = e.Value as string;
                if (!string.IsNullOrEmpty(val))
                {
                    if (val[0] == '=')
                    {
                        try
                        {
                            e.Value = Evaluate(val);
                        }
                        catch (Exception x)
                        {
                            e.Value = "** ERR: " + x.Message;
                        }
                    }
                    else if (val[0] == '\'')
                    {
                        e.Value = val.Substring(1);
                    }
                }
            }

            // apply default numeric formatting
            if (object.Equals(this.DefaultCellStyle, e.CellStyle))
            {
                if (e.Value is double)
                {
                    e.CellStyle = _styleNumber;
                }
                else if (e.Value is DateTime)
                {
                    e.CellStyle = _styleDate;
                }
            }

            // fire event as usual
            base.OnCellFormatting(e);
        }

        // invalidate cells with formulas after editing
        protected override void OnCellEndEdit(DataGridViewCellEventArgs e)
        {
            // try converting strings into doubles after editing
            double dbl;
            var cell = this.Rows[e.RowIndex].Cells[e.ColumnIndex];
            var str = cell.Value as string;
            if (!string.IsNullOrEmpty(str) && double.TryParse(str, out dbl))
            {
                cell.Value = dbl;
            }

            // invalidate grid to update formulas
            this.Invalidate();

            // fire event as usual
            base.OnCellEndEdit(e);
        }

        // update the grid styles to use the current font
        protected override void OnFontChanged(EventArgs e)
        {
            // update default number style
            this.DefaultCellStyle.Font = this.Font;
            _styleNumber.Font = this.DefaultCellStyle.Font;

            // fire event as usual
            base.OnFontChanged(e);
        }
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Bernardo Castilho
Chief Technology Officer ComponentOne
United States United States
No Biography provided

| Advertise | Privacy | Mobile
Web03 | 2.8.140926.1 | Last Updated 1 Sep 2013
Article Copyright 2011 by Bernardo Castilho
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid