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:
- 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
- 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)
{
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);
_inputRng = _workbook.Worksheets.GetRangeByName(_inputRegionName);
if (_inputRng == null)
Error("Invalid or not existing region with name '" + _inputRegionName + "'");
_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;
row = _inputRng.RowCount > inputTbl.Rows.Count ?
inputTbl.Rows.Count : _inputRng.RowCount;
col = _inputRng.ColumnCount > inputTbl.Columns.Count ?
inputTbl.Columns.Count : _inputRng.ColumnCount;
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]);
}
_workbook.CalculateFormula();
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
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.