diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index d4073e5..5960577 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -62,6 +62,7 @@ + diff --git a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs new file mode 100644 index 0000000..b5853b8 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs @@ -0,0 +1,74 @@ +using System; +using System.Diagnostics; +using System.Text.RegularExpressions; + +namespace ClosedXML.Excel.CalcEngine +{ + internal class CalcEngineHelpers + { + internal static bool ValueSatisfiesCriteria(object value, object criteria, CalcEngine ce) + { + // safety... + if (value == null) + { + return false; + } + + // if criteria is a number, straight comparison + if (criteria is double) + { + if (value is Double) + return (double)value == (double)criteria; + Double dValue; + return Double.TryParse(value.ToString(), out dValue) && dValue == (double)criteria; + } + + // convert criteria to string + var cs = criteria as string; + if (!string.IsNullOrEmpty(cs)) + { + // if criteria is an expression (e.g. ">20"), use calc engine + if (cs[0] == '=' || cs[0] == '<' || cs[0] == '>') + { + // build expression + var expression = string.Format("{0}{1}", value, cs); + + // add quotes if necessary + var pattern = @"(\w+)(\W+)(\w+)"; + var m = Regex.Match(expression, pattern); + if (m.Groups.Count == 4) + { + double d; + if (!double.TryParse(m.Groups[1].Value, out d) || + !double.TryParse(m.Groups[3].Value, out d)) + { + expression = string.Format("\"{0}\"{1}\"{2}\"", + m.Groups[1].Value, + m.Groups[2].Value, + m.Groups[3].Value); + } + } + + // evaluate + return (bool)ce.Evaluate(expression); + } + + // if criteria is a regular expression, use regex + if (cs.IndexOf('*') > -1) + { + var pattern = cs.Replace(@"\", @"\\"); + pattern = pattern.Replace(".", @"\"); + pattern = pattern.Replace("*", ".*"); + return Regex.IsMatch(value.ToString(), pattern, RegexOptions.IgnoreCase); + } + + // straight string comparison + return string.Equals(value.ToString(), cs, StringComparison.OrdinalIgnoreCase); + } + + // should never get here? + Debug.Assert(false, "failed to evaluate criteria in SumIf"); + return false; + } + } +} diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index 4c33e64..48a2bdd 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -1,10 +1,7 @@ using System; -using System.Diagnostics; using System.Collections; using System.Collections.Generic; -using System.Text; using System.Linq; -using System.Text.RegularExpressions; using ClosedXML.Excel.CalcEngine.Functions; namespace ClosedXML.Excel.CalcEngine @@ -221,7 +218,7 @@ var tally = new Tally(); for (var i = 0; i < Math.Min(rangeValues.Count, sumRangeValues.Count); i++) { - if (ValueSatisfiesCriteria(rangeValues[i], criteria, ce)) + if (CalcEngineHelpers.ValueSatisfiesCriteria(rangeValues[i], criteria, ce)) { tally.AddValue(sumRangeValues[i]); } @@ -231,71 +228,6 @@ return tally.Sum(); } - private static bool ValueSatisfiesCriteria(object value, object criteria, CalcEngine ce) - { - // safety... - if (value == null) - { - return false; - } - - // if criteria is a number, straight comparison - if (criteria is double) - { - if (value is Double) - return (double) value == (double) criteria; - Double dValue; - return Double.TryParse(value.ToString(), out dValue) && dValue == (double) criteria; - } - - // convert criteria to string - var cs = criteria as string; - if (!string.IsNullOrEmpty(cs)) - { - // if criteria is an expression (e.g. ">20"), use calc engine - if (cs[0] == '=' || cs[0] == '<' || cs[0] == '>') - { - // build expression - var expression = string.Format("{0}{1}", value, cs); - - // add quotes if necessary - var pattern = @"(\w+)(\W+)(\w+)"; - var m = Regex.Match(expression, pattern); - if (m.Groups.Count == 4) - { - double d; - if (!double.TryParse(m.Groups[1].Value, out d) || - !double.TryParse(m.Groups[3].Value, out d)) - { - expression = string.Format("\"{0}\"{1}\"{2}\"", - m.Groups[1].Value, - m.Groups[2].Value, - m.Groups[3].Value); - } - } - - // evaluate - return (bool) ce.Evaluate(expression); - } - - // if criteria is a regular expression, use regex - if (cs.IndexOf('*') > -1) - { - var pattern = cs.Replace(@"\", @"\\"); - pattern = pattern.Replace(".", @"\"); - pattern = pattern.Replace("*", ".*"); - return Regex.IsMatch(value.ToString(), pattern, RegexOptions.IgnoreCase); - } - - // straight string comparison - return string.Equals(value.ToString(), cs, StringComparison.OrdinalIgnoreCase); - } - - // should never get here? - Debug.Assert(false, "failed to evaluate criteria in SumIf"); - return false; - } - private static object Tan(List p) { return Math.Tan(p[0]); @@ -609,9 +541,9 @@ case 1: return tally.Average(); case 2: - return tally.Count(); + return tally.Count(true); case 3: - return tally.CountA(); + return tally.Count(false); case 4: return tally.Max(); case 5: @@ -636,7 +568,7 @@ private static object SumSq(List p) { var t = new Tally(p); - return t.Numerics().Sum(v => Math.Pow(v, 2)); + return t.NumericValues().Sum(v => Math.Pow(v, 2)); } private static object MMult(List p) @@ -700,4 +632,4 @@ return m.Invert().mat; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs index 973bb90..34b14f1 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs @@ -10,7 +10,7 @@ public static void Register(CalcEngine ce) { //ce.RegisterFunction("AVEDEV", AveDev, 1, int.MaxValue); - ce.RegisterFunction("AVERAGE", 1, int.MaxValue, Average); + ce.RegisterFunction("AVERAGE", 1, int.MaxValue, Average); // Returns the average (arithmetic mean) of the arguments ce.RegisterFunction("AVERAGEA", 1, int.MaxValue, AverageA); //BETADIST Returns the beta cumulative distribution function //BETAINV Returns the inverse of the cumulative distribution function for a specified beta distribution @@ -22,7 +22,7 @@ //CORREL Returns the correlation coefficient between two data sets ce.RegisterFunction("COUNT", 1, int.MaxValue, Count); ce.RegisterFunction("COUNTA", 1, int.MaxValue, CountA); - ce.RegisterFunction("COUNTBLANK", 1, int.MaxValue, CountBlank); + ce.RegisterFunction("COUNTBLANK", 1, CountBlank); ce.RegisterFunction("COUNTIF", 2, CountIf); //COVAR Returns covariance, the average of the products of paired deviations //CRITBINOM Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value @@ -77,6 +77,8 @@ ce.RegisterFunction("STDEVA", 1, int.MaxValue, StDevA); ce.RegisterFunction("STDEVP", 1, int.MaxValue, StDevP); ce.RegisterFunction("STDEVPA", 1, int.MaxValue, StDevPA); + ce.RegisterFunction("STDEV.S", 1, int.MaxValue, StDev); + ce.RegisterFunction("STDEV.P", 1, int.MaxValue, StDevP); //STEYX Returns the standard error of the predicted y-value for each x in the regression //TDIST Returns the Student's t-distribution //TINV Returns the inverse of the Student's t-distribution @@ -87,6 +89,8 @@ ce.RegisterFunction("VARA", 1, int.MaxValue, VarA); ce.RegisterFunction("VARP", 1, int.MaxValue, VarP); ce.RegisterFunction("VARPA", 1, int.MaxValue, VarPA); + ce.RegisterFunction("VAR.S", 1, int.MaxValue, Var); + ce.RegisterFunction("VAR.P", 1, int.MaxValue, VarP); //WEIBULL Returns the Weibull distribution //ZTEST Returns the one-tailed probability-value of a z-test } @@ -131,10 +135,10 @@ } return cnt; } - static bool IsBlank(object value) + internal static bool IsBlank(object value) { - return - value == null || + return + value == null || value is string && ((string)value).Length == 0; } static object CountIf(List p) @@ -144,13 +148,12 @@ var ienum = p[0] as IEnumerable; if (ienum != null) { - var crit = (string)p[1].Evaluate(); + var criteria = (string)p[1].Evaluate(); foreach (var value in ienum) { if (!IsBlank(value)) { - var exp = string.Format("{0}{1}", value, crit); - if ((bool)ce.Evaluate(exp)) + if (CalcEngineHelpers.ValueSatisfiesCriteria(value, criteria, ce)) cnt++; } } @@ -209,7 +212,7 @@ // utility for tallying statistics static Tally GetTally(List p, bool numbersOnly) { - return new Tally(p); + return new Tally(p, numbersOnly); } } } diff --git a/ClosedXML/Excel/CalcEngine/Functions/Tally.cs b/ClosedXML/Excel/CalcEngine/Functions/Tally.cs index f7d76fc..7f5766b 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Tally.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Tally.cs @@ -1,22 +1,38 @@ using System; -using System.Linq; -using System.Collections.Generic; -using System.Net; using System.Collections; +using System.Collections.Generic; +using System.Linq; namespace ClosedXML.Excel.CalcEngine { - internal class Tally: IEnumerable + internal class Tally : IEnumerable { private readonly List _list = new List(); + private readonly bool NumbersOnly; - public Tally(){} + public Tally() + : this(false) + { } + + public Tally(bool numbersOnly) + : this(null, numbersOnly) + { } + public Tally(IEnumerable p) + : this(p, false) + { } + + public Tally(IEnumerable p, bool numbersOnly) { - foreach (var e in p) + if (p != null) { - Add(e); + foreach (var e in p) + { + Add(e); + } } + + this.NumbersOnly = numbersOnly; } public void Add(Expression e) @@ -47,93 +63,74 @@ _list.Add(v); } - public double Count() { return _list.Count; } - public double CountA() + public double Count() { - Double cntA = 0; + return this.Count(this.NumbersOnly); + } + + public double Count(bool numbersOnly) + { + if (numbersOnly) + return NumericValues().Count(); + else + return _list.Where(o => !Statistical.IsBlank(o)).Count(); + } + + public IEnumerable NumericValues() + { + var retVal = new List(); foreach (var value in _list) { + Double tmp; var vEnumerable = value as IEnumerable; - if (vEnumerable == null) - cntA += AddCount(value); + if (vEnumerable == null && Double.TryParse(value.ToString(), out tmp)) + yield return tmp; else { foreach (var v in vEnumerable) { - cntA += AddCount(v); + if (Double.TryParse(v.ToString(), out tmp)) + yield return tmp; break; } } } - return cntA; - } - - private static double AddCount(object value) - { - var strVal = value as String; - if (value != null && (strVal == null || !XLHelper.IsNullOrWhiteSpace(strVal))) - return 1; - return 0; - } - - public List Numerics() - { - List retVal = new List(); - foreach (var value in _list) - { - var vEnumerable = value as IEnumerable; - if (vEnumerable == null) - AddNumericValue(value, retVal); - else - { - foreach (var v in vEnumerable) - { - AddNumericValue(v, retVal); - break; - } - } - } - return retVal; - } - - private static void AddNumericValue(object value, List retVal) - { - Double tmp; - if (Double.TryParse(value.ToString(), out tmp)) - { - retVal.Add(tmp); - } } public double Product() { - var nums = Numerics(); - if (nums.Count == 0) return 0; + var nums = NumericValues(); + if (!nums.Any()) return 0; Double retVal = 1; nums.ForEach(n => retVal *= n); return retVal; } - public double Sum() { return Numerics().Sum(); } + + public double Sum() { return NumericValues().Sum(); } + public double Average() { - return Numerics().Count == 0 ? 0 : Numerics().Average(); + if (NumericValues().Any()) + return NumericValues().Average(); + else + throw new ApplicationException("No values"); } public double Min() { - return Numerics().Count == 0 ? 0 : Numerics().Min(); + return NumericValues().Any() ? NumericValues().Min() : 0; } public double Max() { - return Numerics().Count == 0 ? 0 : Numerics().Max(); + return NumericValues().Any() ? NumericValues().Max() : 0; } public double Range() { - var nums = Numerics(); + var nums = NumericValues(); return nums.Max() - nums.Min(); } @@ -144,40 +141,46 @@ public double VarP() { - var nums = Numerics(); + var nums = NumericValues(); var avg = nums.Average(); var sum2 = nums.Sum(d => d * d); - return nums.Count <= 1 ? 0 : sum2 / nums.Count - avg * avg; + return nums.Count() <= 1 ? 0 : sum2 / nums.Count() - avg * avg; } + public double StdP() { - var nums = Numerics(); + var nums = NumericValues(); var avg = nums.Average(); var sum2 = nums.Sum(d => d * d); - return nums.Count <= 1 ? 0 : Math.Sqrt(sum2 / nums.Count - avg * avg); + return nums.Count() <= 1 ? 0 : Math.Sqrt(sum2 / nums.Count() - avg * avg); } + public double Var() { - var nums = Numerics(); + var nums = NumericValues(); var avg = nums.Average(); var sum2 = nums.Sum(d => d * d); - return nums.Count <= 1 ? 0 : (sum2 / nums.Count - avg * avg) * nums.Count / (nums.Count - 1); + return nums.Count() <= 1 ? 0 : (sum2 / nums.Count() - avg * avg) * nums.Count() / (nums.Count() - 1); } + public double Std() { - var values = Numerics(); + var values = NumericValues(); double ret = 0; - if (values.Count > 0) + if (values.Any()) { - //Compute the Average + //Compute the Average double avg = values.Average(); - //Perform the Sum of (value-avg)_2_2 + //Perform the Sum of (value-avg)_2_2 double sum = values.Sum(d => Math.Pow(d - avg, 2)); - //Put it all together + //Put it all together ret = Math.Sqrt((sum) / (values.Count() - 1)); } + else + { + throw new ApplicationException("No values"); + } return ret; - } public IEnumerator GetEnumerator() diff --git a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index 7cd14ba..a9f9ad8 100644 --- a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -97,6 +97,9 @@ Excel\CalcEngine\CalcEngine.cs + + Excel\CalcEngine\CalcEngineHelpers.cs + Excel\CalcEngine\Expression.cs diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index e89fd07..3f006c2 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -73,6 +73,7 @@ + diff --git a/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs b/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs index 98f8d87..48691a6 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs @@ -467,10 +467,10 @@ public void SubtotalCount() { object actual = XLWorkbook.EvaluateExpr("Subtotal(2,2,3)"); - Assert.AreEqual(2.0, actual); + Assert.AreEqual(2, actual); actual = XLWorkbook.EvaluateExpr(@"Subtotal(2,""A"",3)"); - Assert.AreEqual(2.0, actual); + Assert.AreEqual(1, actual); } [Test] @@ -609,14 +609,12 @@ XLWorkbook wb = new XLWorkbook(); wb.Worksheets.Add("TallyTests"); var cell = wb.Worksheet(1).Cell(1, 1).SetFormulaA1("=MAX(D1,D2)"); - Assert.AreEqual(0, cell.Value); + Assert.AreEqual(0, cell.Value); cell = wb.Worksheet(1).Cell(2, 1).SetFormulaA1("=MIN(D1,D2)"); Assert.AreEqual(0, cell.Value); cell = wb.Worksheet(1).Cell(3, 1).SetFormulaA1("=SUM(D1,D2)"); Assert.AreEqual(0, cell.Value); - cell = wb.Worksheet(1).Cell(3, 1).SetFormulaA1("=AVERAGE(D1,D2)"); - Assert.AreEqual(0, cell.Value); - + Assert.That(() => wb.Worksheet(1).Cell(3, 1).SetFormulaA1("=AVERAGE(D1,D2)").Value, Throws.Exception); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs new file mode 100644 index 0000000..9285cb4 --- /dev/null +++ b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs @@ -0,0 +1,270 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Linq; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + [TestFixture] + public class StatisticalTests + { + private double tolerance = 1e-6; + private XLWorkbook workbook; + + [Test] + public void Average() + { + double value; + value = workbook.Evaluate("AVERAGE(-27.5,93.93,64.51,-70.56)").CastTo(); + Assert.AreEqual(15.095, value, tolerance); + + var ws = workbook.Worksheets.First(); + value = ws.Evaluate("AVERAGE(G3:G45)").CastTo(); + Assert.AreEqual(49.3255814, value, tolerance); + + Assert.That(() => ws.Evaluate("AVERAGE(D3:D45)"), Throws.Exception); + } + + [Test] + public void Count() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=COUNT(D3:D45)").CastTo(); + Assert.AreEqual(0, value); + + value = ws.Evaluate(@"=COUNT(G3:G45)").CastTo(); + Assert.AreEqual(43, value); + + value = ws.Evaluate(@"=COUNT(G:G)").CastTo(); + Assert.AreEqual(43, value); + + value = workbook.Evaluate(@"=COUNT(Data!G:G)").CastTo(); + Assert.AreEqual(43, value); + } + + [Test] + public void CountA() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=COUNTA(D3:D45)").CastTo(); + Assert.AreEqual(43, value); + + value = ws.Evaluate(@"=COUNTA(G3:G45)").CastTo(); + Assert.AreEqual(43, value); + + value = ws.Evaluate(@"=COUNTA(G:G)").CastTo(); + Assert.AreEqual(44, value); + + value = workbook.Evaluate(@"=COUNTA(Data!G:G)").CastTo(); + Assert.AreEqual(44, value); + } + + [Test] + public void CountBlank() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=COUNTBLANK(B:B)").CastTo(); + Assert.AreEqual(1048532, value); + + value = ws.Evaluate(@"=COUNTBLANK(D43:D49)").CastTo(); + Assert.AreEqual(4, value); + + value = workbook.Evaluate(@"=COUNTBLANK(E3:E45)").CastTo(); + Assert.AreEqual(0, value); + } + + [Test] + public void CountIf() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=COUNTIF(D3:D45,""Central"")").CastTo(); + Assert.AreEqual(24, value); + + value = ws.Evaluate(@"=COUNTIF(D:D,""Central"")").CastTo(); + Assert.AreEqual(24, value); + + value = workbook.Evaluate(@"=COUNTIF(Data!D:D,""Central"")").CastTo(); + Assert.AreEqual(24, value); + } + + [OneTimeTearDown] + public void Dispose() + { + workbook.Dispose(); + } + + [OneTimeSetUp] + public void Init() + { + // Make sure tests run on a deterministic culture + System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); + workbook = SetupWorkbook(); + } + + [Test] + public void Max() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=MAX(D3:D45)").CastTo(); + Assert.AreEqual(0, value); + + value = ws.Evaluate(@"=MAX(G3:G45)").CastTo(); + Assert.AreEqual(96, value); + + value = ws.Evaluate(@"=MAX(G:G)").CastTo(); + Assert.AreEqual(96, value); + + value = workbook.Evaluate(@"=MAX(Data!G:G)").CastTo(); + Assert.AreEqual(96, value); + } + + [Test] + public void Min() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=MIN(D3:D45)").CastTo(); + Assert.AreEqual(0, value); + + value = ws.Evaluate(@"=MIN(G3:G45)").CastTo(); + Assert.AreEqual(2, value); + + value = ws.Evaluate(@"=MIN(G:G)").CastTo(); + Assert.AreEqual(2, value); + + value = workbook.Evaluate(@"=MIN(Data!G:G)").CastTo(); + Assert.AreEqual(2, value); + } + + [Test] + public void StDev() + { + var ws = workbook.Worksheets.First(); + double value; + Assert.That(() => ws.Evaluate(@"=STDEV(D3:D45)"), Throws.Exception); + + value = ws.Evaluate(@"=STDEV(H3:H45)").CastTo(); + Assert.AreEqual(47.34511769, value, tolerance); + + value = ws.Evaluate(@"=STDEV(H:H)").CastTo(); + Assert.AreEqual(47.34511769, value, tolerance); + + value = workbook.Evaluate(@"=STDEV(Data!H:H)").CastTo(); + Assert.AreEqual(47.34511769, value, tolerance); + } + + [Test] + public void StDevP() + { + var ws = workbook.Worksheets.First(); + double value; + Assert.That(() => ws.Evaluate(@"=STDEVP(D3:D45)"), Throws.Exception); + + value = ws.Evaluate(@"=STDEVP(H3:H45)").CastTo(); + Assert.AreEqual(46.79135458, value, tolerance); + + value = ws.Evaluate(@"=STDEVP(H:H)").CastTo(); + Assert.AreEqual(46.79135458, value, tolerance); + + value = workbook.Evaluate(@"=STDEVP(Data!H:H)").CastTo(); + Assert.AreEqual(46.79135458, value, tolerance); + } + + [Test] + public void Var() + { + var ws = workbook.Worksheets.First(); + double value; + Assert.That(() => ws.Evaluate(@"=VAR(D3:D45)"), Throws.Exception); + + value = ws.Evaluate(@"=VAR(H3:H45)").CastTo(); + Assert.AreEqual(2241.560169, value, tolerance); + + value = ws.Evaluate(@"=VAR(H:H)").CastTo(); + Assert.AreEqual(2241.560169, value, tolerance); + + value = workbook.Evaluate(@"=VAR(Data!H:H)").CastTo(); + Assert.AreEqual(2241.560169, value, tolerance); + } + + [Test] + public void VarP() + { + var ws = workbook.Worksheets.First(); + double value; + Assert.That(() => ws.Evaluate(@"=VARP(D3:D45)"), Throws.Exception); + + value = ws.Evaluate(@"=VARP(H3:H45)").CastTo(); + Assert.AreEqual(2189.430863, value, tolerance); + + value = ws.Evaluate(@"=VARP(H:H)").CastTo(); + Assert.AreEqual(2189.430863, value, tolerance); + + value = workbook.Evaluate(@"=VARP(Data!H:H)").CastTo(); + Assert.AreEqual(2189.430863, value, tolerance); + } + + private XLWorkbook SetupWorkbook() + { + var wb = new XLWorkbook(); + var ws = wb.AddWorksheet("Data"); + var data = new object[] + { + new {Id=1, OrderDate = DateTime.Parse("2015-01-06"), Region = "East", Rep = "Jones", Item = "Pencil", Units = 95, UnitCost = 1.99, Total = 189.05 }, + new {Id=2, OrderDate = DateTime.Parse("2015-01-23"), Region = "Central", Rep = "Kivell", Item = "Binder", Units = 50, UnitCost = 19.99, Total = 999.5}, + new {Id=3, OrderDate = DateTime.Parse("2015-02-09"), Region = "Central", Rep = "Jardine", Item = "Pencil", Units = 36, UnitCost = 4.99, Total = 179.64}, + new {Id=4, OrderDate = DateTime.Parse("2015-02-26"), Region = "Central", Rep = "Gill", Item = "Pen", Units = 27, UnitCost = 19.99, Total = 539.73}, + new {Id=5, OrderDate = DateTime.Parse("2015-03-15"), Region = "West", Rep = "Sorvino", Item = "Pencil", Units = 56, UnitCost = 2.99, Total = 167.44}, + new {Id=6, OrderDate = DateTime.Parse("2015-04-01"), Region = "East", Rep = "Jones", Item = "Binder", Units = 60, UnitCost = 4.99, Total = 299.4}, + new {Id=7, OrderDate = DateTime.Parse("2015-04-18"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 75, UnitCost = 1.99, Total = 149.25}, + new {Id=8, OrderDate = DateTime.Parse("2015-05-05"), Region = "Central", Rep = "Jardine", Item = "Pencil", Units = 90, UnitCost = 4.99, Total = 449.1}, + new {Id=9, OrderDate = DateTime.Parse("2015-05-22"), Region = "West", Rep = "Thompson", Item = "Pencil", Units = 32, UnitCost = 1.99, Total = 63.68}, + new {Id=10, OrderDate = DateTime.Parse("2015-06-08"), Region = "East", Rep = "Jones", Item = "Binder", Units = 60, UnitCost = 8.99, Total = 539.4}, + new {Id=11, OrderDate = DateTime.Parse("2015-06-25"), Region = "Central", Rep = "Morgan", Item = "Pencil", Units = 90, UnitCost = 4.99, Total = 449.1}, + new {Id=12, OrderDate = DateTime.Parse("2015-07-12"), Region = "East", Rep = "Howard", Item = "Binder", Units = 29, UnitCost = 1.99, Total = 57.71}, + new {Id=13, OrderDate = DateTime.Parse("2015-07-29"), Region = "East", Rep = "Parent", Item = "Binder", Units = 81, UnitCost = 19.99, Total = 1619.19}, + new {Id=14, OrderDate = DateTime.Parse("2015-08-15"), Region = "East", Rep = "Jones", Item = "Pencil", Units = 35, UnitCost = 4.99, Total = 174.65}, + new {Id=15, OrderDate = DateTime.Parse("2015-09-01"), Region = "Central", Rep = "Smith", Item = "Desk", Units = 2, UnitCost = 125, Total = 250}, + new {Id=16, OrderDate = DateTime.Parse("2015-09-18"), Region = "East", Rep = "Jones", Item = "Pen Set", Units = 16, UnitCost = 15.99, Total = 255.84}, + new {Id=17, OrderDate = DateTime.Parse("2015-10-05"), Region = "Central", Rep = "Morgan", Item = "Binder", Units = 28, UnitCost = 8.99, Total = 251.72}, + new {Id=18, OrderDate = DateTime.Parse("2015-10-22"), Region = "East", Rep = "Jones", Item = "Pen", Units = 64, UnitCost = 8.99, Total = 575.36}, + new {Id=19, OrderDate = DateTime.Parse("2015-11-08"), Region = "East", Rep = "Parent", Item = "Pen", Units = 15, UnitCost = 19.99, Total = 299.85}, + new {Id=20, OrderDate = DateTime.Parse("2015-11-25"), Region = "Central", Rep = "Kivell", Item = "Pen Set", Units = 96, UnitCost = 4.99, Total = 479.04}, + new {Id=21, OrderDate = DateTime.Parse("2015-12-12"), Region = "Central", Rep = "Smith", Item = "Pencil", Units = 67, UnitCost = 1.29, Total = 86.43}, + new {Id=22, OrderDate = DateTime.Parse("2015-12-29"), Region = "East", Rep = "Parent", Item = "Pen Set", Units = 74, UnitCost = 15.99, Total = 1183.26}, + new {Id=23, OrderDate = DateTime.Parse("2016-01-15"), Region = "Central", Rep = "Gill", Item = "Binder", Units = 46, UnitCost = 8.99, Total = 413.54}, + new {Id=24, OrderDate = DateTime.Parse("2016-02-01"), Region = "Central", Rep = "Smith", Item = "Binder", Units = 87, UnitCost = 15, Total = 1305}, + new {Id=25, OrderDate = DateTime.Parse("2016-02-18"), Region = "East", Rep = "Jones", Item = "Binder", Units = 4, UnitCost = 4.99, Total = 19.96}, + new {Id=26, OrderDate = DateTime.Parse("2016-03-07"), Region = "West", Rep = "Sorvino", Item = "Binder", Units = 7, UnitCost = 19.99, Total = 139.93}, + new {Id=27, OrderDate = DateTime.Parse("2016-03-24"), Region = "Central", Rep = "Jardine", Item = "Pen Set", Units = 50, UnitCost = 4.99, Total = 249.5}, + new {Id=28, OrderDate = DateTime.Parse("2016-04-10"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 66, UnitCost = 1.99, Total = 131.34}, + new {Id=29, OrderDate = DateTime.Parse("2016-04-27"), Region = "East", Rep = "Howard", Item = "Pen", Units = 96, UnitCost = 4.99, Total = 479.04}, + new {Id=30, OrderDate = DateTime.Parse("2016-05-14"), Region = "Central", Rep = "Gill", Item = "Pencil", Units = 53, UnitCost = 1.29, Total = 68.37}, + new {Id=31, OrderDate = DateTime.Parse("2016-05-31"), Region = "Central", Rep = "Gill", Item = "Binder", Units = 80, UnitCost = 8.99, Total = 719.2}, + new {Id=32, OrderDate = DateTime.Parse("2016-06-17"), Region = "Central", Rep = "Kivell", Item = "Desk", Units = 5, UnitCost = 125, Total = 625}, + new {Id=33, OrderDate = DateTime.Parse("2016-07-04"), Region = "East", Rep = "Jones", Item = "Pen Set", Units = 62, UnitCost = 4.99, Total = 309.38}, + new {Id=34, OrderDate = DateTime.Parse("2016-07-21"), Region = "Central", Rep = "Morgan", Item = "Pen Set", Units = 55, UnitCost = 12.49, Total = 686.95}, + new {Id=35, OrderDate = DateTime.Parse("2016-08-07"), Region = "Central", Rep = "Kivell", Item = "Pen Set", Units = 42, UnitCost = 23.95, Total = 1005.9}, + new {Id=36, OrderDate = DateTime.Parse("2016-08-24"), Region = "West", Rep = "Sorvino", Item = "Desk", Units = 3, UnitCost = 275, Total = 825}, + new {Id=37, OrderDate = DateTime.Parse("2016-09-10"), Region = "Central", Rep = "Gill", Item = "Pencil", Units = 7, UnitCost = 1.29, Total = 9.03}, + new {Id=38, OrderDate = DateTime.Parse("2016-09-27"), Region = "West", Rep = "Sorvino", Item = "Pen", Units = 76, UnitCost = 1.99, Total = 151.24}, + new {Id=39, OrderDate = DateTime.Parse("2016-10-14"), Region = "West", Rep = "Thompson", Item = "Binder", Units = 57, UnitCost = 19.99, Total = 1139.43}, + new {Id=40, OrderDate = DateTime.Parse("2016-10-31"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 14, UnitCost = 1.29, Total = 18.06}, + new {Id=41, OrderDate = DateTime.Parse("2016-11-17"), Region = "Central", Rep = "Jardine", Item = "Binder", Units = 11, UnitCost = 4.99, Total = 54.89}, + new {Id=42, OrderDate = DateTime.Parse("2016-12-04"), Region = "Central", Rep = "Jardine", Item = "Binder", Units = 94, UnitCost = 19.99, Total = 1879.06}, + new {Id=43, OrderDate = DateTime.Parse("2016-12-21"), Region = "Central", Rep = "Andrews", Item = "Binder", Units = 28, UnitCost = 4.99, Total = 139.72} + }; + ws.FirstCell() + .CellBelow() + .CellRight() + .InsertTable(data, "Table1"); + + return wb; + } + } +}