lyfz_repos/铂金版源码/Biff8/Formulas/Calculator.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using Biff8Excel.Interfaces;
using Biff8Excel.Excel;

namespace Biff8Excel.Formulas
{
    public class Calculator
    {
        //Calculate
        double[] arrStack = new double[100];
        ushort stackPointer;
        //ExcelCellLink m_cells;
        ExcelCellDictionary m_cells;
        IExternSheet m_externSheet;
        ushort m_thisSheetNumber;

        public Calculator()
        {
            //stackPointer = 0;
        }

        // If there are no sheets in the function string, use
        // this value to make sure we only reference cells from the formulas sheet
        internal ushort ThisSheetNumber
        {
            set { m_thisSheetNumber = value; }
        }

        internal IExternSheet ExternSheet
        {
            set { m_externSheet = value; }
        }

        internal ExcelCellDictionary CellCollection
        //internal ExcelCellLink CellCollection
        {
            set { m_cells = value; }
        }

        /// <summary>
        /// Procedure : Function EvaluteSupportedFunction
        /// </summary>
        /// <param name="funcString"></param>
        /// <returns></returns>
        private double EvaluteSupportedFunction(string funcString)
        {
            switch ( Globals.GetFunctionName(funcString))
            {
                case "SUM":
                    return EvaluateSum(funcString);

                case "AVERAGE":
                    return EvaluateAverage(funcString);

                case "COUNT":
                    return EvaluateCount(funcString);

                default:
                    return 0;
            }
        }

        private ushort GetSheetNumberFromName(string sheetName)
        {
            try
            {
                for (ushort i = 0; i < Globals.g_arrSheetNames.Length; i++)
                {
                    if (Globals.g_arrSheetNames[i] == sheetName)
                        return i;
                }
                throw new Biff8ExcelException("not find sheetName");
            }
            catch
            {
                throw;
            }
        }

        private ushort[] GetSheetRange(string funcString)
        {
            int pos;
            string reference;
            ushort first;
            ushort last;
            ushort[] result;
            string sheets;
            string sheet1;
            string sheet2;

            result = new ushort[2];

            reference = funcString;

            pos = reference.IndexOf("(");

            if (pos > -1)
            {
                //得到括号内的内容
                reference = reference.Substring(pos + 1, reference.Length - pos - 2);
            }

            // check for sheet references
            pos = reference.IndexOf("!");
            if (pos > -1)
            {
                sheets = reference.Substring(0, pos);

                //how many sheets are their
                pos = sheets.IndexOf(":");
                if (pos < 0)
                {
                    last = first = GetSheetNumberFromName(sheets);
                }
                else
                {
                    sheet1 = sheets.Substring(0, pos);
                    if (sheets.Substring(pos + 1) == "$")
                        sheet2 = sheets.Substring(pos + 2);
                    else
                        sheet2 = sheets.Substring(pos + 1);

                    first = GetSheetNumberFromName(sheet1);
                    last = GetSheetNumberFromName(sheet2);
                }
                result[0] = first;
                result[1] = last;
            }
            else
            {
                result[0] = m_thisSheetNumber;
                result[1] = m_thisSheetNumber;
            }

            // order the sheets 小的数在前 
            if (result[0] > result[1])
                result[0] = (ushort)(result[1] + (result[1] = result[0]) * 0);

            return result;
        }

        //private bool IsAlphaNumeric(byte b)
        //{
        //    if ((b >= 48 && b <= 57) || (b >= 65 && b <= 90) || (b >= 97 && b <= 123))
        //        return true;
        //    return false;
        //}

        private string GetAreaRef(string funcString)
        {
            //string area = "";
            StringBuilder area = new StringBuilder();
            int pos;

            //remove any Sheet references
            pos = funcString.IndexOf("!");
            if (pos > -1)
            {
                funcString = funcString.Substring(pos + 1);
                if (funcString.Substring(funcString.Length - 1) == ")")
                {
                    funcString = funcString.Substring(0, funcString.Length - 1);
                    return funcString;
                }
            }

            // Get the Cell references
            pos = funcString.IndexOf(":");
            if (pos < 0) return "";

            // get the left cell ref
            for (int i = (pos - 1); i >= 0; i--)
            {
                if (char.IsLetterOrDigit(funcString, i) && (funcString.Substring(i, 1) != "!"))
                //if (IsAlphaNumeric((byte)(funcString.Substring(i, 1).ToCharArray()[0])) && (funcString.Substring(i, 1) != "!"))
                    area.Append(funcString.Substring(i, 1));
                else
                    break;
            }

            string st = Globals.StrReverse(area.ToString()) + ":";
            area = new StringBuilder();
            area.Append(st);
            //area = Globals.StrReverse(area) + ":";

            // get the right cell ref
            for (int i = (pos + 1); i < funcString.Length; i++)
            {
                if (char.IsLetterOrDigit(funcString, i))
                    //if (IsAlphaNumeric((byte)(funcString.Substring(i, 1).ToCharArray()[0])))
                    area.Append( funcString.Substring(i, 1));
                else
                    break;
            }

            return area.ToString() ;
        }

        private string[] GetCellReferences(string area)
        {
            string[] result;
            string first;
            string last;
            int count = 0;
            ushort rowfrom;
            ushort rowto;
            ushort colfrom;
            ushort colto;

            // get the first an last cell in the range
            count = area.IndexOf(":");
            first = area.Substring(0, count);
            last = area.Substring(count + 1);

            rowfrom = (ushort)Globals.ConvertRowStringToNum(first);
            colfrom = (ushort)Globals.ConvertColStringToNum(first);

            rowto = (ushort)Globals.ConvertRowStringToNum(last);
            colto = (ushort)Globals.ConvertColStringToNum(last);

            int num;
            num = (rowto - rowfrom + 1) * (colto - colfrom + 1);
            result = new string[num];

            count = 0;

            for (ushort x = rowfrom; x <= rowto; x++)
            {
                for (ushort y = colfrom; y <= colto; y++)
                {
                    result[count] = Globals.CellReferenceFromRowColumn(x, y);
                    count++;
                }
            }

            return result;
        }

        private double EvaluateSum(string funcString)
        {
            ushort count = 0 ;
            return EvaluateSum(funcString,ref count);
        }
         
        private double EvaluateSum(string funcString ,ref ushort cellEvaluated)
        {
            string area;
            ushort[] sheets;
            string[] cells;
            double num;
            double result = 0;
            ICell cell = null;
            string key;

            try
            {
                if (funcString.IndexOf("(") < 0) return default(double);

                // returns the Sheet Range     (eg =Sum(Sheet1:Sheet3!A1:B1) returns   1, 2 & 3)
                sheets = GetSheetRange(funcString);

                // get the cells contained between the brackets    ( format = Sum(A1:A3) returns A1:A3  )
                area = GetAreaRef(funcString);

                // get an array of all the cells in the enclosed area;
                cells = GetCellReferences(area);

                for (ushort y = sheets[0]; y <= sheets[1]; y++)
                {
                    for (ushort x = 0; x < cells.Length; x++)
                    {
                        key = y.ToString("000") + "!" + cells[x];
                        cell = m_cells[key];

                        if (cell != null)
                        {
                            if (cell.ResolvedValue == null)
                                result += 0;
                            // check for a valid value in the cell                            
                            else if (double.TryParse(cell.ResolvedValue.ToString(),out num) == true)
                                result += num;

                            cellEvaluated++;
                        }
                    } //end for x
                }   // end for y

                return result;
            }
            catch 
            {
                return default(double);
            }
        }

        private double EvaluateAverage(string funcString)
        {
            ushort count = 0;
            double sum;
            try
            {
                // count returns the number of cell evaluated
                sum = EvaluateSum(funcString,ref count);
                return sum / count;
            }
            catch
            {
                return default(double);
            }
        }

        private ushort EvaluateCount(string funcString)
        {
            try
            {
                ushort count = 0;
                // count returns the number of cell evaluated
                EvaluateSum(funcString,ref count);
                return count;
            }
            catch 
            {
                return default(ushort);
            }
        }

        internal double Calculate(List<string> tokens)
        {
            int y;
            double num;
            ICell cell;
            string token;
            double lOp, rOp;
            string key;
            int pos;
            ushort iSheet;
            string sheets = "";
            string s;

            for (int i = 0; i < tokens.Count; i++)
            {
                token = tokens[i].ToString();

                switch (token)
                {
                    case "+":
                        rOp = arrStack[stackPointer];
                        stackPointer--;
                        lOp = arrStack[stackPointer];
                        arrStack[stackPointer] = lOp + rOp;
                        break;

                    case "-":
                        rOp = arrStack[stackPointer];
                        stackPointer--;
                        lOp = arrStack[stackPointer];
                        arrStack[stackPointer] = lOp - rOp;
                        break;

                    case "*":
                        rOp = arrStack[stackPointer];
                        stackPointer--;
                        lOp = arrStack[stackPointer];
                        arrStack[stackPointer] = lOp * rOp;
                        break;

                    case "/":
                        rOp = arrStack[stackPointer];
                        stackPointer--;
                        lOp = arrStack[stackPointer];
                        arrStack[stackPointer] = lOp / rOp;
                        break;

                    case "^":
                        rOp = arrStack[stackPointer];
                        stackPointer--;
                        lOp = arrStack[stackPointer];
                        arrStack[stackPointer] = Math.Pow(lOp, rOp);
                        break;

                    case " ":
                    case "":
                        break;

                    default:
                        double tmp;
                        if (double.TryParse(token, out tmp))
                        {
                            stackPointer++;
                            arrStack[stackPointer] = tmp;
                        }
                        else if (Globals.g_sSupported.IndexOf(Globals.GetFunctionName(token)) > -1)     //Supported Function
                        {
                            num = EvaluteSupportedFunction(token);
                            if (default(double) != num)
                            {
                                stackPointer++;
                                arrStack[stackPointer] = num;
                            }
                            else
                            {
                                return default(double);
                            }
                        }
                        else
                        {
                            // if there is a sheet reference then get it's number
                            // otherwise use m_thisSheetNumber
                            pos = token.IndexOf("!");
                            if (pos > -1)
                            {
                                // split out the sheet(s)
                                y = pos - 1;

                                do
                                {
                                    s = token.Substring(y, 1);
                                    if ((s == "(") || (s == "$") || (s == ":")) break;
                                    sheets += s;
                                    y--;
                                } while (y > 0);
                                iSheet = GetSheetNumberFromName(Globals.StrReverse(sheets));
                                s = token.Substring(pos + 1);
                            }
                            else
                            {
                                iSheet = m_thisSheetNumber;
                                s = token;
                            }

                            key = iSheet.ToString("000") + "!" + s;
                            cell = m_cells[key];

                            if (cell == null)
                            {
                                stackPointer++;
                                arrStack[stackPointer] = 0.00;
                            }
                            else
                            {
                                if (double.TryParse(  cell.ResolvedValue.ToString(),out num) == true)
                                {
                                    stackPointer++;
                                    arrStack[stackPointer] = num;
                                }
                                else
                                {
                                    return default(double);
                                }
                            }
                        }
                        break;
                }
            }
            return arrStack[1];
        }
    }
}