Click here to Skip to main content
Click here to Skip to main content

Tagged as

StrategyLight, Business Logic under Business Control in Excel File

, 19 Nov 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
Definition of business logic (calculations, scorings, decisions, …) under business control in Excel file (without Excel installation and automation technique).

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

  • 19th November, 2008: Initial post

License

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

Share

About the Author

steuer.j
Team Leader
Czech Republic Czech Republic
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 19 Nov 2008
Article Copyright 2008 by steuer.j
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid