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"); }