StrategyLight, Business Logic under Business Control in Excel File

• Download calculation - 7.17 KB
• Download demo - 1.44 MB
• Download source - 1.48 MB

Introduction

I was thinking how to get business logic from a business person to the application directly and this article shows a possible solution to do so. The main thing is to avoid installation of Excel and automation technique because it takes up a lot of resources (memory, CPU, etc.). It also has an impact on the application's stability.

Background

The main idea is very easy:

1. Business will define calculation logic, etc. to Excel (e.g. Calculation.xslx)
• Excel will contain two named regions ‘InputData’ and ‘OutputData’ (for the input and output)
• Excel will contain business logic
2. Excel will be stored to the server
• Excel will use think service (StrategyLight)
• We will use external component (Aspose.Cells.dll from company Aspose) in this think service for working with Excel (without Excel installation and automation technique)

Using the Code

Part of the code on server side (think service, file CalculateEngine.cs in project StrategyLight) is given below:

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 + "'");
    }
}
public string Execute(string inputData)
{
    return GetText(Execute(CreateTable(inputData.Trim(_newRow))));
}
public DataTable Execute(DataTable inputTbl)
{
    DataTable outputTbl=null;
    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);
    }
    return outputTbl;
} 

Part of the code on the client side (testing console application, file Program.cs in project StrategyLightTest) is given below:

StrategyLight.StrategyLightService engine = 
	new StrategyLightTest.StrategyLight.StrategyLightService();
string inputData;
inputData = "0\n0\n1000\n0\n0";
Console.WriteLine(engine.Execute2(@"Calculation.xlsx", inputData));

StrategyLight.StrategyLightService engine = 
	new StrategyLightTest.StrategyLight.StrategyLightService();
DataTable inputDataTable = new DataTable("input");
DataTable outputDataTable;
DataRow rw;
inputDataTable.Columns.Add("0", typeof(Int32));
rw = inputDataTable.NewRow();
rw[0] = 0;
inputDataTable.Rows.Add(rw);
outputDataTable = engine.Execute1(@"c:\.Dev\Calculation.xlsx", inputDataTable);

Points of Interest

I presented an easy way on how to keep business logic directly on the business side without difficult implementation on program code side (and also to save workload for final implementation).

History

• 19^th November, 2008: Initial post