public void CanUseBackwardOwnCellValues()
{
var expressions = new List <MappingExpression>
{
new MappingExpression()
{
Name = "F01", Cell = "J13", Expression = "=3"
},
new MappingExpression()
{
Name = "F02", Cell = "J14", Expression = "=4"
},
new MappingExpression()
{
Name = "F03", Cell = "J15", Expression = "=J13+J14"
}
};
var processor = new MappingExpressionEvaluator(CultureInfo.GetCultureInfo("el-GR"));
var results = processor.Evaluate(expressions, null);
var result = results.ElementAt(2);
Assert.Null(result.Error);
Assert.Equal("7", result.Text);
Assert.Equal(7.0, result.Value);
}
public EvaluationOutput Evaluate(EvaluationRequest request)
{
IEnumerable <TemplateField> templateFields = null;
if (!string.IsNullOrEmpty(request.TemplateName))
{
var template = repository.GetLatestTemplate(request.TemplateName);
if (template != null)
{
templateFields = OpenXmlWordProcessing.FindTemplateFields(template.Buffer);
}
}
if (templateFields == null)
{
templateFields = request.Expressions.Select(e => new TemplateField()
{
Name = e.Name,
Parent = e.Parent,
IsCollection = e.IsCollection,
Content = e.Content
});
}
var processor = new MappingExpressionEvaluator();
var input = new EvaluationInput()
{
Fields = templateFields,
Expressions = request.Expressions,
Sources = request.Sources
};
var response = processor.Evaluate(input);
return(response);
}
public void CanSumRangeOwnCellValues()
{
var info = new MappingInfo()
{
Expressions = new List <MappingExpression>()
{
new MappingExpression()
{
Name = "x", Cell = "F3", Expression = "=1"
},
new MappingExpression()
{
Name = "y", Cell = "F4", Expression = "=2"
},
new MappingExpression()
{
Name = "z", Cell = "F5", Expression = "=SUM(F3:F4)"
},
}
};
var processor = new MappingExpressionEvaluator();
var results = processor.Evaluate(info.Expressions, info.Sources);
var z = results.First(o => o.Name == "z");
Assert.Null(z.Error);
Assert.Equal(3.0, z.Value);
}
public void CanDoStandaloneEvaluation()
{
var input = new EvaluationInput()
{
Fields = new List <TemplateField>()
{
new TemplateField()
{
Name = "a"
},
new TemplateField()
{
Name = "b"
},
new TemplateField()
{
Name = "c"
},
},
Expressions = new List <MappingExpression>()
{
new MappingExpression()
{
Name = "a", Expression = "=MAPVALUE(\"INP\",\"a\")"
},
new MappingExpression()
{
Name = "b", Expression = "=MAPVALUE(\"INP\",\"b\")"
},
new MappingExpression()
{
Name = "c", Expression = "=a+b"
},
},
Sources = new List <EvaluationSource>()
{
new EvaluationSource()
{
Name = "INP", Payload = JObject.Parse("{a:3, b:4}")
}
}
};
var processor = new MappingExpressionEvaluator();
var output = processor.Evaluate(input);
var results = output.Results.ToList();
Assert.Equal(3, results.Count);
Assert.Null(results[0].Error);
Assert.Equal("3", results[0].Text);
Assert.Null(results[1].Error);
Assert.Equal("4", results[1].Text);
Assert.Null(results[2].Error);
Assert.Equal("7", results[2].Text);
}
public void CanUseForwardOwnCellValues()
{
var bytes = new MemoryStream(Resources.use_forward_own_cell_values_xlsm);
var info = OpenXmlSpreadsheet.GetMappingInfo(bytes, null);
var processor = new MappingExpressionEvaluator();
var results = processor.Evaluate(info.Expressions, info.Sources);
Assert.True(results.All(r => r.Error == null));
Assert.Equal("22", results.First(o => o.Name == "F01").Text);
Assert.Equal("2", results.First(o => o.Name == "F02").Text);
Assert.Equal("6", results.First(o => o.Name == "F03").Text);
Assert.Equal("16", results.First(o => o.Name == "F04").Text);
Assert.Equal("8", results.First(o => o.Name == "F05").Text);
}
public void CanFormatNumbersWithStandardDateFormats()
{
var expressions = new List <MappingExpression>()
{
new MappingExpression()
{
Name = "A1", Cell = "A1", Expression = "=PI()*10^4"
}
};
for (var i = 14; i <= 22; i++)
{
expressions.Add(new MappingExpression()
{
Name = $"B{i}", Cell = $"B{i}", Expression = "=A1", NumFormatId = i
});
}
for (var i = 45; i <= 48; i++)
{
expressions.Add(new MappingExpression()
{
Name = $"B{i}", Cell = $"B{i}", Expression = "=A1", NumFormatId = i
});
}
var input = new EvaluationInput()
{
Expressions = expressions
};
var processor = new MappingExpressionEvaluator(CultureInfo.GetCultureInfo("el-GR"));
var results = processor.Evaluate(input).Results;
Assert.Equal("3/1/1986", results.First(o => o.Name == "B14").Text);
Assert.Equal("3-Ιαν-86", results.First(o => o.Name == "B15").Text);
Assert.Equal("3-Ιαν", results.First(o => o.Name == "B16").Text);
Assert.Equal("Ιαν-86", results.First(o => o.Name == "B17").Text);
Assert.Equal("10:14 μμ", results.First(o => o.Name == "B18").Text);
Assert.Equal("10:14:13 μμ", results.First(o => o.Name == "B19").Text);
Assert.Equal("22:14", results.First(o => o.Name == "B20").Text);
Assert.Equal("22:14:13", results.First(o => o.Name == "B21").Text);
Assert.Equal("3/1/1986 22:14", results.First(o => o.Name == "B22").Text);
Assert.Equal("14:13", results.First(o => o.Name == "B45").Text);
//Assert.Equal("753982:14:13", results.First(o => o.Name == "B46").Text);
//Assert.Equal("14:12,7", results.First(o => o.Name == "B47").Text);
Assert.Equal("31,4E+3", results.First(o => o.Name == "B48").Text);
}
public void CanEvaluateForExcelExample01()
{
var json = JObject.Parse(Resources.evaluate_for_excel_example01_json);
var request = json.ToObject <EvaluationRequest>();
var templateBytes = new MemoryStream(Resources.evaluate_for_excel_example01_docx);
var templateFields = OpenXmlWordProcessing.FindTemplateFields(templateBytes);
var processor = new MappingExpressionEvaluator(CultureInfo.GetCultureInfo("el-GR"));
var input = new EvaluationInput()
{
Fields = templateFields,
Expressions = request.Expressions,
Sources = request.Sources
};
var response = processor.Evaluate(input);
var fields = new Dictionary <string, string>();
response.Results.ToList().ForEach(o => fields.Add(o.Name, o.Text));
Assert.Equal(23, response.Total);
Assert.Equal(0, response.Errors);
Assert.Equal(DateTime.Today.ToString("d/M/yyyy"), fields["F01"]);
Assert.Equal("ΠΡΟΘΕΣΜΙΑΚΗ ΜΕ BONUS 3 ΜΗΝΩΝ - ΑΠΟ ΕΥΡΩ 10.000", fields["F02"]);
Assert.Equal("923456789012345", fields["F03"]);
Assert.Equal("3", fields["F04"]);
Assert.Equal("MONTH", fields["F05"]);
Assert.Equal("έκαστης", fields["F06"]);
Assert.Equal("10000", fields["F07"]);
Assert.Equal("3", fields["F08"]);
Assert.Equal("1", fields["F09"]);
Assert.Equal("['{}','{}']", fields["F10"]);
Assert.Equal("['1','3']", fields["F11"]);
Assert.Equal("['0,2','0,25']", fields["F12"]);
Assert.Equal("['500','1000']", fields["F13"]);
Assert.Equal("0,17", fields["F14"]);
Assert.Equal("1", fields["F15"]);
Assert.Equal("Πρώτος προαιρετικός όρος", fields["F16"]);
Assert.Equal("1", fields["F17"]);
Assert.Equal("", fields["F18"]);
Assert.Equal("5000", fields["F19"]);
Assert.Equal("10000", fields["F20"]);
Assert.Equal("Προθεσμιακή με Bonus 3 Μηνών - Από Ευρώ 10.000", fields["F21"]);
Assert.Equal("923456789012345", fields["F22"]);
Assert.Equal("123", fields["F23"]);
}
private IEnumerable <EvaluationResult> CreateDocumentInternal(IEnumerable <TemplateField> templateFields,
IEnumerable <MappingExpression> templateFieldExpressions,
IEnumerable <EvaluationSource> sources)
{
var expressions = new List <MappingExpression>();
foreach (var templateField in templateFields)
{
var expression = templateFieldExpressions.FirstOrDefault(m => m.Name == templateField.Name);
if (expression != null)
{
expressions.Add(expression);
}
}
var processor = new MappingExpressionEvaluator();
var results = processor.Evaluate(expressions, sources);
return(results);
}
public void CanFormatResultValues()
{
var input = new EvaluationInput()
{
Expressions = new List <MappingExpression>()
{
new MappingExpression()
{
Name = "quantity", Cell = "F3", Expression = "=10/3"
},
new MappingExpression()
{
Name = "price", Cell = "F4", Expression = "=1.3"
},
new MappingExpression()
{
Name = "subtotal", Cell = "F5", Expression = "=F3 * F4", NumFormatId = 2
},
new MappingExpression()
{
Name = "tax", Cell = "F6", Expression = "=F5 * 10%", NumFormatId = 2
},
new MappingExpression()
{
Name = "total", Cell = "F7", Expression = "=F5 + F6", NumFormatId = 2
},
}
};
var processor = new MappingExpressionEvaluator(CultureInfo.GetCultureInfo("el-GR"));
var results = processor.Evaluate(input).Results;
var subtotal = results.First(o => o.Name == "subtotal");
Assert.Equal("4,33", subtotal.Text);
var tax = results.First(o => o.Name == "tax");
Assert.Equal("0,43", tax.Text);
var total = results.First(o => o.Name == "total");
Assert.Equal("4,77", total.Text);
}
public void CanFormatDatesAndTimes()
{
var expressions = new List <MappingExpression>()
{
new MappingExpression()
{
Name = "A1", Expression = "=1", NumFormatId = 14
},
new MappingExpression()
{
Name = "A2", Expression = "=1", NumFormatId = 19
},
new MappingExpression()
{
Name = "B1", Expression = "=2", NumFormatId = 14
},
new MappingExpression()
{
Name = "B2", Expression = "=2", NumFormatId = 19
},
new MappingExpression()
{
Name = "A3", Expression = "=PI()", NumFormatId = 14
},
new MappingExpression()
{
Name = "A4", Expression = "=PI()", NumFormatId = 19
},
new MappingExpression()
{
Name = "A5", Expression = "=10000.00001", NumFormatId = 14
},
new MappingExpression()
{
Name = "A6", Expression = "=10000.00001", NumFormatId = 19
},
new MappingExpression()
{
Name = "B5", Expression = "=10000.000001", NumFormatId = 14
},
new MappingExpression()
{
Name = "B6", Expression = "=10000.000001", NumFormatId = 19
},
new MappingExpression()
{
Name = "A7", Expression = "=10000.99999", NumFormatId = 14
},
new MappingExpression()
{
Name = "A8", Expression = "=10000.99999", NumFormatId = 19
},
new MappingExpression()
{
Name = "A9", Expression = "=10000.999999", NumFormatId = 14
},
new MappingExpression()
{
Name = "A10", Expression = "=10000.999999", NumFormatId = 19
},
new MappingExpression()
{
Name = "A11", Expression = "=0.5787", NumFormatId = 14
},
new MappingExpression()
{
Name = "A12", Expression = "=0.5787", NumFormatId = 19
},
new MappingExpression()
{
Name = "C5", Expression = "=A5", NumFormatId = 0
},
new MappingExpression()
{
Name = "C6", Expression = "=A6", NumFormatId = 0
},
new MappingExpression()
{
Name = "C7", Expression = "=B5", NumFormatId = 0
},
new MappingExpression()
{
Name = "C8", Expression = "=B6", NumFormatId = 0
},
new MappingExpression()
{
Name = "C9", Expression = "=A7", NumFormatId = 0
},
new MappingExpression()
{
Name = "C10", Expression = "=A8", NumFormatId = 0
},
};
var input = new EvaluationInput()
{
Expressions = expressions
};
var processor = new MappingExpressionEvaluator(CultureInfo.GetCultureInfo("el-GR"));
var results = processor.Evaluate(input).Results;
Assert.Equal("1/1/1900", results.First(o => o.Name == "A1").Text);
Assert.Equal("12:00:00 πμ", results.First(o => o.Name == "A2").Text);
Assert.Equal("2/1/1900", results.First(o => o.Name == "B1").Text);
Assert.Equal("12:00:00 πμ", results.First(o => o.Name == "B2").Text);
Assert.Equal("3/1/1900", results.First(o => o.Name == "A3").Text);
Assert.Equal("3:23:54 πμ", results.First(o => o.Name == "A4").Text);
Assert.Equal("18/5/1927", results.First(o => o.Name == "A5").Text);
Assert.Equal("12:00:01 πμ", results.First(o => o.Name == "A6").Text);
Assert.Equal("18/5/1927", results.First(o => o.Name == "B5").Text);
Assert.Equal("12:00:00 πμ", results.First(o => o.Name == "B6").Text);
Assert.Equal("18/5/1927", results.First(o => o.Name == "A7").Text);
Assert.Equal("11:59:59 μμ", results.First(o => o.Name == "A8").Text);
Assert.Equal("19/5/1927", results.First(o => o.Name == "A9").Text);
Assert.Equal("12:00:00 πμ", results.First(o => o.Name == "A10").Text);
Assert.Equal("1/1/1900", results.First(o => o.Name == "A11").Text);
Assert.Equal("1:53:20 μμ", results.First(o => o.Name == "A12").Text);
Assert.Equal("10000,00001", results.First(o => o.Name == "C5").Text);
Assert.Equal("10000,00001", results.First(o => o.Name == "C6").Text);
Assert.Equal("10000", results.First(o => o.Name == "C7").Text);
Assert.Equal("10000", results.First(o => o.Name == "C8").Text);
Assert.Equal("10000,99999", results.First(o => o.Name == "C9").Text);
Assert.Equal("10000,99999", results.First(o => o.Name == "C10").Text);
}
public void CanFormatNumbersWithStandardNumberFormats()
{
var expressions = new List <MappingExpression>()
{
new MappingExpression()
{
Name = "A1", Cell = "A1", Expression = "=PI()*10^4"
}
};
for (var i = 1; i <= 12; i++)
{
expressions.Add(new MappingExpression()
{
Name = $"B{i}", Cell = $"B{i}", Expression = "=A1", NumFormatId = i
});
}
for (var i = 37; i <= 44; i++)
{
expressions.Add(new MappingExpression()
{
Name = $"B{i}", Cell = $"B{i}", Expression = "=A1", NumFormatId = i
});
}
expressions.Add(new MappingExpression()
{
Name = $"B49", Cell = $"B49", Expression = "=A1", NumFormatId = 49
});
var input = new EvaluationInput()
{
Expressions = expressions
};
var processor = new MappingExpressionEvaluator(CultureInfo.GetCultureInfo("el-GR"));
var results = processor.Evaluate(input).Results;
Assert.Equal("31416", results.First(o => o.Name == "B1").Text);
Assert.Equal("31415,93", results.First(o => o.Name == "B2").Text);
Assert.Equal("31.416", results.First(o => o.Name == "B3").Text);
Assert.Equal("31.415,93", results.First(o => o.Name == "B4").Text);
Assert.Equal("31.416 €", results.First(o => o.Name == "B5").Text);
Assert.Equal("31.416 €", results.First(o => o.Name == "B6").Text);
Assert.Equal("31.415,93 €", results.First(o => o.Name == "B7").Text);
Assert.Equal("31.415,93 €", results.First(o => o.Name == "B8").Text);
Assert.Equal("3.141.593%", results.First(o => o.Name == "B9").Text);
Assert.Equal("3.141.592,65%", results.First(o => o.Name == "B10").Text);
Assert.Equal("3,14E+004", results.First(o => o.Name == "B11").Text);
Assert.Equal("31416 ", results.First(o => o.Name == "B12").Text);
//Assert.Equal("31415 63/68", results.First(o => o.Name == "B13").Text);
Assert.Equal("31.416 ", results.First(o => o.Name == "B37").Text);
Assert.Equal("31.416 ", results.First(o => o.Name == "B38").Text);
Assert.Equal("31.415,93 ", results.First(o => o.Name == "B39").Text);
Assert.Equal("31.415,93 ", results.First(o => o.Name == "B40").Text);
Assert.Equal(" 31.416 ", results.First(o => o.Name == "B41").Text);
Assert.Equal(" 31.416 € ", results.First(o => o.Name == "B42").Text);
Assert.Equal(" 31.415,93 ", results.First(o => o.Name == "B43").Text);
Assert.Equal(" 31.415,93 € ", results.First(o => o.Name == "B44").Text);
Assert.Equal("31415,92654", results.First(o => o.Name == "B49").Text);
}
public void CanEvaluateMapAndGetUDF()
{
var json = JObject.Parse(Resources.can_evaluate_map_and_get_udf_json);
var sources = new List <EvaluationSource>
{
new EvaluationSource {
Name = "N3", Payload = (JObject)json["sources"][0]["payload"]
},
new EvaluationSource {
Name = "N4", Payload = (JObject)json["sources"][1]["payload"]
},
new EvaluationSource {
Name = "N5", Payload = (JObject)json["sources"][2]["payload"]
},
};
var expressions = new List <MappingExpression>
{
new MappingExpression()
{
Name = "F02", Cell = "F4", Expression = "=source(\"N4\",source(\"N3\",\"RequestData.PS1016\"))"
},
new MappingExpression()
{
Name = "F03", Cell = "F5", Expression = "=MAPVALUE(N4,MAPVALUE(N3,\"RequestData.PS1016\"))"
},
new MappingExpression()
{
Name = "F04", Cell = "F6", Expression = "=MAPVALUE(N3,\"RequestData.PS1016\")"
},
new MappingExpression()
{
Name = "F05", Cell = "F7", Expression = "=MAPVALUE(N3,\"RequestData.PS1016\",N4)"
},
new MappingExpression()
{
Name = "F06", Cell = "F8", Expression = "=MAPVALUE(N3,\"LogHeader.Version\")"
},
new MappingExpression()
{
Name = "F10", Cell = "F12", Expression = "=MAPVALUE(N3,\"RequestData.InterestTable\")"
},
new MappingExpression()
{
Name = "F11", Cell = "F13", Expression = "=MAPITEM(F12,\"Period\")"
},
new MappingExpression()
{
Name = "F12", Cell = "F14", Expression = "=GETITEM(F12,\"Period\")*100"
},
new MappingExpression()
{
Name = "F14", Cell = "F16", Expression = "=MAPVALUE(N5,GETITEM(F12,\"Period\",,2))"
},
new MappingExpression()
{
Name = "F15", Cell = "F17", Expression = "=GETITEM(F12,\"Period\",N5)"
},
new MappingExpression()
{
Name = "F17", Cell = "F19", Expression = "=GETITEM(F12,\"Period\",N5,2)"
},
new MappingExpression()
{
Name = "F18", Cell = "F20", Expression = "=GETITEM(F12,\"Period\")"
},
new MappingExpression()
{
Name = "F19", Cell = "F21", Expression = "=GETITEM(F12,\"Period\",,2)"
},
new MappingExpression()
{
Name = "F13", Cell = "F15", Expression = "=SUM(GETLIST(F12,\"Period\"))"
},
//new MappingExpression() { Name = "F16", Cell = "F18", Expression = "{=GETLIST(F12,\"Period\")+7}" },
};
var processor = new MappingExpressionEvaluator(CultureInfo.GetCultureInfo("el-GR"));
var results = processor.Evaluate(expressions, sources);
//Assert.Equal("MM", expressions.First(o => o.Name == "F01").Result.Text);
AssertExpression(results, "F02", "MM");
AssertExpression(results, "F03", "MM");
AssertExpression(results, "F04", "MONTH");
AssertExpression(results, "F05", "MM");
AssertExpression(results, "F06", "3.10");
AssertExpression(results, "F10", "['{}','{}']");
AssertExpression(results, "F11", "['1','3']");
AssertExpression(results, "F12", "100");
AssertExpression(results, "F14", "Three");
AssertExpression(results, "F15", "One");
AssertExpression(results, "F17", "Three");
AssertExpression(results, "F18", "1");
AssertExpression(results, "F19", "3");
AssertExpression(results, "F13", "4");
}
