I am developing a Blazor server application to replace Excel sheets that perform various calculations. Instead of a large grid containing all the cells, I am using a few Razor components with HTML tables, and the cells contain text inputs. The formulas for each cell are stored in a database using a format that is parseable through the FLEE library (expression evaluator).
During application startup, I retrieve all formulas from the database into memory, which is stored as a List of objects. Then, each cell input is value-bound to a property in my form class whose getter and setter interact with the List of formulas through a function.
Using Blazor's own lifecycle, every time the content is refreshed, the cell's input @bind attribute calls the property getter, which calls a function to retrieve the value associated with that cell's name. The function performs several string operations and finally evaluates the formula using FLEE to obtain the final value, which is displayed in the input.
However, I have encountered two problems. Firstly, as the formulas become more complex and the forms grow in size, the application's performance slows down significantly. Secondly, while the final value is being evaluated, I can interact with my Blazor page, but it only performs the desired action after many "refresh cycles" - for example, I click a button, but only after several seconds of calculations, the button action is called.
Now I will provide code snippets showing the application workflow
How formulas are store in DB:
| PropertyName | Value |
| ------------ | --------------------- |
| BMI | = @Weight / @Height^2 |
| Height | |
| Weight | |
My razor page, containing the cells:
<EditForm Model=@BodyMeasurements>
<div class="container form">
<table class="table table-hover">
<tbody>
<tr>
<td>
<input type="text" class="form-control @bind=Weight>
</td>
<td>
<input type="text" class="form-control @bind=Height>
</td>
<td>
<input type="text" class="form-control @bind=BMI readonly>
</td>
</tr>
</tbody>
</table class="table table-hover">
</div>
</EditForm>
My form model
using System.Globalization;
namespace FitApp.Models.BodyMeasurements
{
public class BodyMeasurements
{
private readonly Calculator calc;
public BodyMeasurements(Calc calc)
{
this.calc = calc;
}
public string? Weight
{
get
{
return calc.RetrieveValue("UserWeight");
}
set
{
calc.SetValue("UserWeight", value);
}
}
public string? Height
{
get
{
return calc.RetrieveValue("UserHeight");
}
set
{
calc.SetValue("UserHeight", value);
}
}
public string? BMI
{
get
{
return calc.RetrieveValue("BMI");
}
set
{
return;
}
}
}
}
My Calculator class
using FitApp.Models;
namespace FitApp.Models.BodyMeasurements
{
public class Calculator
{
public readonly List<UserAttributes> userAttributes;
public DadosProposta(IEnumerable<UserAttributes> userAttributes)
{
// Recebo os dados
this.userAttributes = userAttributes.ToList();
}
public void SetValue(string propertyName, object newValue)
{
foreach (var result in userAttributes.Where(r => r.PropertyName == propertyName))
{
result.Value = Convert.ToString(newValue);
break;
}
}
public string RetrieveValue(string propertyName)
{
try
{
// Search for the attribute in the list
UserAttributes result = userAttributes.FirstOrDefault(r => r.PropertyName == propertyName);
// If not found, or null Value, returns null
if (result is null || string.IsNullOrEmpty(result.Value))
{
return null;
}
// If value is not a formula, returns Value
if (IsNotFormula(result.Value))
{
return result.Value.Trim();
}
// Takes the formula as is
string originalFormula = result.Value.Trim();
// Simplifies the formula's IFs, if necessary, to reduce its size and execution time
string simplifiedFormula = SimplifyIF(originalFormula).Item1;
// Replaces the remaining references and VLOOKUPs with the values
string replacedFormula = simplifiedFormula;
while (replacedFormula is not null && (replacedFormula.Contains("VLOOKUPs") || replacedFormula.Contains('@')))
{
// Replaces references with numeric values
replacedFormula = TreatVLOOKUP(replacedFormula);
replacedFormula = ReplaceReferences(replacedFormula).GetAwaiter().GetResult();
}
string finalValue = "";
// In case the translated formula is not yet in the table, or it is different in the library
if (!string.IsNullOrEmpty(replacedFormula) && (result.TranslatedValue is null || result.TranslatedValue != replacedFormula))
{
// Get the field index in the library
var index = userAttributes.IndexOf(result);
// Stores the processed formula in the TranslatedValue column of the library
userAttributes[index].TranslatedValue = replacedFormula;
// Solves the formula
finalValue = SolveFormula(replacedFormula).GetAwaiter().GetResult();
// Updates the value in the library -- if it returns null, I leave it blank, so that null values are not calculated repeatedly
userAttributes[index].EvaluatedValue = finalValue ?? string.Empty;
}
else
{
// If already evaluated
finalValue = result.EvaluatedValue;
}
return finalValue;
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
return string.Empty;
}
}
private async Task<string> ReplaceReferences(string formula)
{
// If there is still a reference
if (formula is not null && formula.Contains('@'))
{
// Iinitialize the list
List<string> references = new();
// Scans the text, looking for references
foreach (var item in formula.Trim().Split('@'))
{
string name = string.Empty;
// Remove non-alphanumeric characters from substring
for (int i = 0; i < item.Length; i++)
{
if (char.IsLetterOrDigit(item[i]))
{
name += string.Concat(item[i]);
}
else
{
break;
}
}
// I add it only once, to avoid repeated queries
if (!references.Contains(name) && !string.IsNullOrEmpty(name))
{
references.Add(name);
}
}
// Sort the list, in descending order, by the number of characters
references.Sort((a, b) => b.Length.CompareTo(a.Length));
// If any found
if (references.Count > 0)
{
// Get the attribute
UserAttributes result = userAttributes.FirstOrDefault(d => d.PropertyName == references[0]) ?? null;
// If not found
if (result == null)
{
return null;
}
else
{
// Treat the value if it is empty
string value = string.IsNullOrEmpty(result.Valor) ? "0" : result.Valor;
// If it is a simple formula, without reference or VLOOKUP, proceeds to evaluation
if (!value.Contains('@') && !value.Contains("VLOOKUP", StringComparison.CurrentCultureIgnoreCase) && value.Contains('='))
{
value = await SolveFormula(value);
}
// Try and simplify IF
if (FormulaHasIF(value))
{
value = SimplifyIF(value).Item1;
}
// Removes the '=' symbol from the beginning, if necessary
if (value is not null && value.Trim().StartsWith('='))
{
value = value.Trim().Remove(0, 1);
}
if (!(string.IsNullOrEmpty(result.EvaluatedValue)))
{
formula = formula.Replace($"@{references[0]}", $"({result.EvaluatedValue,})");
}
else
{
// Replace with value in text -- wrap new value with ( ) to correctly handle IFs within IFs
formula = formula.Replace($"@{references[0]}", $"({value})");
}
}
}
}
return formula;
}
private async Task<string> SolveFormula(string formula)
{
if (string.IsNullOrEmpty(formula))
{
return null;
}
else
{
while (formula is not null && (formula.Contains("VLOOKUPs") || formula.Contains('@')))
{
// Replaces references with numeric values
formula = TreatVLOOKUP(formula);
formula = ReplaceReferences(formula).GetAwaiter().GetResult();
}
// Simplifies the formula's IFs, if necessary, to reduce its size and execution time
formula = SimplifyIF(originalFormula).Item1;
if (formula is null)
{
return null;
}
// Remove '=' from the start
if (formula.IndexOf('=') > -1 && formula.IndexOf('=') < 3)
formula = formula.Remove(formula.IndexOf('='), 1).Trim();
// Solves
var result = FLEE.Eval(formula);
// Returns
return result;
}
}
}
}
FLEE implementation
using Flee.PublicTypes;
using System.Globalization;
namespace FitApp.Models
{
public static class Equacoes
{
static ExpressionContext context;
static Equacoes()
{
// Flee context
context = new ExpressionContext();
context.Options.ParseCulture = new CultureInfo("pt-BR");
context.ParserOptions.FunctionArgumentSeparator = ';';
context.ParserOptions.RecreateParser();
context.Options.RealLiteralDataType = RealLiteralDataType.Double;
// Allow the expression to use all static public methods of System.Math
context.Imports.AddType(typeof(Math));
}
public static string Eval(string expression)
{
try
{
Flee.PublicTypes.IDynamicExpression eDynamic = context.CompileDynamic(expression);
// Calcula
var result = eDynamic.Evaluate();
return Convert.ToString(result) == "NaN" ? null : Convert.ToString(result);
}
catch (ExpressionCompileException)
{
return null;
}
catch (Exception)
{
return null;
}
}
}
}
In this example (sorry if it will no compile, had to ommit some parts due to company policies), in order to display BMI, the user must fill Weight and Height. Then, @bind attribute in the BMI input will call the function RetrieveValue through the getter. The RetrieveValue function will get the field formula in the list, replace @Height and @Weight for its corresponding values, and then send the "translated" formula to the evalution library.
This example is pretty simple, and would work with no problems, but there are much more complex formulas in my system, which generates string with thousands of characters (imagine an expression with 25k characters, due to excel design of linking cells). Solving these big expressions is not the slowest part, but actually working with the string, replacing references with the correct values, which can take from 200ms to 4000ms
I tried string operations to reduce complexity of formula sent to the evaluation lib, but then realized it's not the bottleneck, its actually when I'm working with the strings.
Also, tried to using different Render methods on Blazor lifecycle (like OnAfterRender), so user could at least see the form while the values are dinamically being loaded, but did not work
Aucun commentaire:
Enregistrer un commentaire