StrategyLight, Business Logic under Business Control in Excel File

using System;
using System.Collections.Generic;
using System.Text;
using Aspose.Cells;
using System.Data;
using System.IO;

namespace StrategyLight
{
    public class CalculateEngine
    {
        private const string _inputRegionName="InputData";
        private const string _outputRegionName="OutputData";
        private const char _newRow = '\n';
        private const char _newCol = ';';

        private string _calculationCore;
        public string CalculationCore
        {
            get { return _calculationCore; }
            private set {_calculationCore=value;}
        }

        private Workbook _workbook;
        private Range _inputRng;
        private Range _outputRng;

        public CalculateEngine(string calculationCore)
        {
            //  Create full path
            if (!Path.IsPathRooted(calculationCore))
                CalculationCore = Path.Combine(Config.Instance.CalculationCorePath, calculationCore);
            else
                CalculationCore = calculationCore;

            if (!File.Exists(CalculationCore))
                Error("Invalid calculation core '"+CalculationCore+"'");
            else
            {
                _workbook = new Workbook();
                _workbook.Open(CalculationCore);

                //  Get input region
                _inputRng = _workbook.Worksheets.GetRangeByName(_inputRegionName);
                if (_inputRng == null)
                    Error("Invalid or not existing region with name '" + _inputRegionName + "'");

                //  Get output region
                _outputRng = _workbook.Worksheets.GetRangeByName(_outputRegionName);
                if (_outputRng == null)
                    Error("Invalid or not existing region with name '" + _outputRegionName + "'");
            }

            Config.Instance.Log.Info("Create calculation engine '" + CalculationCore+"'");
        }

        public string Execute(string inputData)
        {
            return GetText(Execute(CreateTable(inputData.Trim(_newRow))));
        }

        public DataTable Execute(DataTable inputTbl)
        {
            DataTable outputTbl=null;

            if (Config.Instance.Log.IsInfoEnabled)
            {
                Config.Instance.Log.Info("Execute");
                Config.Instance.Log.Info(" Input '" + GetText(inputTbl) + "'");
            }

            try
            {
                if (inputTbl != null)
                {
                    int row, col;

                    //  Set range (region intersection)
                    row = _inputRng.RowCount > inputTbl.Rows.Count ? inputTbl.Rows.Count : _inputRng.RowCount;
                    col = _inputRng.ColumnCount > inputTbl.Columns.Count ? inputTbl.Columns.Count : _inputRng.ColumnCount;

                    //  Write data from table to the Excel
                    for (int i = 0; i < row; i++)
                        for (int j = 0; j < col; j++)
                            _inputRng.Worksheet.Cells[_inputRng.FirstRow + i, _inputRng.FirstColumn + j].PutValue(inputTbl.Rows[i][j]);
                }

                //  Start Excel calculation
                _workbook.CalculateFormula();

                //  Return data
                outputTbl = _outputRng.ExportDataTable();
                outputTbl.TableName = _outputRegionName;
            }
            catch (Exception ex)
            {
                Error(ex.Message);
            }

            if (Config.Instance.Log.IsInfoEnabled)
                Config.Instance.Log.Info(" Output '" + GetText(outputTbl) + "'");

            return outputTbl;
        }

        //  Create text from table
        private string GetText(DataTable tbl)
        {
            if (tbl == null)
                return null;
            else
            {
                StringBuilder bld = new StringBuilder();

                foreach (DataRow row in tbl.Rows)
                {
                    for (int i = 0; i < tbl.Columns.Count; i++)
                    {
                        bld.Append(row[i]);
                        bld.Append(_newCol);
                    }
                    bld.Append(_newRow);
                }
                return bld.ToString();
            }
        }

        //  Create table from text
        private DataTable CreateTable(string data)
        {
            if (data == null)
                return null;
            else
            {
                DataTable tbl = new DataTable();
                string[] rowList = data.Split(_newRow);

                tbl.BeginLoadData();
                for (int i = 0; i < rowList.Length; i++)
                {
                    string[] colList = rowList[i].Trim(_newCol).Split(_newCol);

                    if (tbl.Columns.Count < colList.Length)
                        for (int j = tbl.Columns.Count; j < colList.Length; j++)
                            tbl.Columns.Add(tbl.Columns.Count.ToString(), typeof(int));

                    tbl.LoadDataRow(colList, true);
                }
                tbl.EndLoadData();
                return tbl;
            }
        }

        //  Report eror
        private void Error(string msg)
        {
            Config.Instance.Log.Error(msg);
            throw (new CalculateException(msg));
        }
    }
}