public void ResolveStructuredReferenceTotalsThatDoNotExistColumnRangeSpecified()
{
var reference = new StructuredReference($"{EpplusExcelDataProviderTest.TableName}[[#Totals],[Header2]:[Header3]");
var result = this.DataProviderWithDataAndHeaders.ResolveStructuredReference(reference, "Sheet1", 15, 7);
Assert.AreEqual("#REF!", result.Address.Address);
}
public void ResolveStructuredReferenceHeadersThatDoNotExist()
{
var reference = new StructuredReference($"{EpplusExcelDataProviderTest.TableName}[#Headers]");
var result = this.DataProviderWithDataAndTotals.ResolveStructuredReference(reference, "Sheet1", 15, 7);
Assert.AreEqual("#REF!", result.Address.Address);
}
public void ResolveStructuredReferenceAllColumnRangeSpecified()
{
var reference = new StructuredReference($"{EpplusExcelDataProviderTest.TableName}[[#All],[Header2]:[Header3]");
var result = this.DataProviderWithDataHeadersAndTotals.ResolveStructuredReference(reference, "Sheet1", 15, 7);
Assert.AreEqual(3, result.Address.Start.Row);
Assert.AreEqual(4, result.Address.Start.Column);
Assert.AreEqual(10, result.Address.End.Row);
Assert.AreEqual(5, result.Address.End.Column);
Assert.AreEqual(EpplusExcelDataProviderTest.Header2, result.GetOffset(0, 0));
Assert.AreEqual(EpplusExcelDataProviderTest.Header3, result.GetOffset(0, 1));
Assert.AreEqual("h2_r1", result.GetOffset(1, 0));
Assert.AreEqual("h2_r2", result.GetOffset(2, 0));
Assert.AreEqual("h2_r3", result.GetOffset(3, 0));
Assert.AreEqual("h2_r4", result.GetOffset(4, 0));
Assert.AreEqual("h2_r5", result.GetOffset(5, 0));
Assert.AreEqual("h2_r6", result.GetOffset(6, 0));
Assert.AreEqual("h2_t", result.GetOffset(7, 0));
Assert.AreEqual("h3_r1", result.GetOffset(1, 1));
Assert.AreEqual("h3_r2", result.GetOffset(2, 1));
Assert.AreEqual("h3_r3", result.GetOffset(3, 1));
Assert.AreEqual("h3_r4", result.GetOffset(4, 1));
Assert.AreEqual("h3_r5", result.GetOffset(5, 1));
Assert.AreEqual("h3_r6", result.GetOffset(6, 1));
Assert.AreEqual("h3_t", result.GetOffset(7, 1));
}
public void ResolveStructuredReferenceThisRowHeaderRow()
{
var reference = new StructuredReference($"{EpplusExcelDataProviderTest.TableName}[[#This Row],[{EpplusExcelDataProviderTest.Header1}]]");
var result = this.DataProviderWithDataAndHeaders.ResolveStructuredReference(reference, "Sheet1", 3, 7);
Assert.IsNull(result);
}
public void StructuredReferenceWithTableAndColumnAll()
{
var structuredReference = new StructuredReference("MyTable[[#all],[MyColumn]]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.AreEqual("MyColumn", structuredReference.StartColumn);
Assert.AreEqual(ItemSpecifiers.All, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceWithEverySpecifier()
{
var structuredReference = new StructuredReference("MyTable[[#All],[#Totals],[#This Row],[#Headers],[#Data],[MyColumn]]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.AreEqual("MyColumn", structuredReference.StartColumn);
Assert.AreEqual(ItemSpecifiers.All | ItemSpecifiers.Totals | ItemSpecifiers.ThisRow | ItemSpecifiers.Headers | ItemSpecifiers.Data, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceColumnNameWithHashtagEscapedNested()
{
var structuredReference = new StructuredReference("MyTable[['#NotAnItemSpecifier]]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.AreEqual("#NotAnItemSpecifier", structuredReference.StartColumn);
Assert.AreEqual(ItemSpecifiers.Data, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceWithTableAndColumnHeadersIgnoreCase()
{
var structuredReference = new StructuredReference("MyTable[[#HEADERS],[MyColumn]]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.AreEqual("MyColumn", structuredReference.StartColumn);
Assert.AreEqual(ItemSpecifiers.Headers, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceEscapedRightBracketInColumn()
{
var structuredReference = new StructuredReference("MyTable[My']olumn]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.AreEqual("My]olumn", structuredReference.StartColumn);
Assert.AreEqual(ItemSpecifiers.Data, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceEscapedFourQuotationInColumn()
{
var structuredReference = new StructuredReference("MyTable[My''''olumn]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.AreEqual("My''olumn", structuredReference.StartColumn);
Assert.AreEqual(ItemSpecifiers.Data, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceWithTableAndColumnNoBracketsFirst()
{
var structuredReference = new StructuredReference("MyTable[MyColumn,[#data]]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.AreEqual("MyColumn", structuredReference.StartColumn);
Assert.AreEqual(ItemSpecifiers.Data, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceWithMultipleSpecifiersWithWhitespace()
{
var structuredReference = new StructuredReference("MyTable[\t[#Headers] ,\t [#Data] ,\t [MyColumn]]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.AreEqual("MyColumn", structuredReference.StartColumn);
Assert.AreEqual(ItemSpecifiers.Headers | ItemSpecifiers.Data, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceSpecialCharactersInColumnWithWhitespace()
{
var structuredReference = new StructuredReference("MyTable[ \t [#data] \t , \t [!|\\;`'] ,;:.ss'[]]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.AreEqual("!|\\;`] ,;:.ss[", structuredReference.StartColumn);
Assert.AreEqual(ItemSpecifiers.Data, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceSpecialCharactersInTableName()
{
var structuredReference = new StructuredReference("My\\}+'Table[column]");
Assert.AreEqual("My\\}+'Table", structuredReference.TableName);
Assert.AreEqual("column", structuredReference.StartColumn);
Assert.AreEqual(ItemSpecifiers.Data, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceWithTotals()
{
var structuredReference = new StructuredReference("MyTable[#Totals]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.IsNull(structuredReference.StartColumn);
Assert.AreEqual(ItemSpecifiers.Totals, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceNoArguments()
{
var structuredReference = new StructuredReference("MyTable[]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.IsTrue(string.IsNullOrEmpty(structuredReference.StartColumn));
Assert.AreEqual(ItemSpecifiers.Data, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceNoItemSpecifierDefaultsToThisRow()
{
var structuredReference = new StructuredReference("MyTable[MyColumn]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.AreEqual("MyColumn", structuredReference.StartColumn);
Assert.AreEqual(ItemSpecifiers.Data, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceWithTableAndColumnThisRowIgnoreCase()
{
var structuredReference = new StructuredReference("MyTable[[#THIS ROW],[MyColumn]]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.AreEqual("MyColumn", structuredReference.StartColumn);
Assert.AreEqual(ItemSpecifiers.ThisRow, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceWithColumnRange()
{
var structuredReference = new StructuredReference("MyTable[[#Headers],[MyStartColumn]:[MyEndColumn]]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.AreEqual("MyStartColumn", structuredReference.StartColumn);
Assert.AreEqual("MyEndColumn", structuredReference.EndColumn);
Assert.AreEqual(ItemSpecifiers.Headers, structuredReference.ItemSpecifiers);
}
public void StructuredReferenceNestedAtArgument()
{
var structuredReference = new StructuredReference("MyTable[[#this row]]");
Assert.AreEqual("MyTable", structuredReference.TableName);
Assert.IsNull(structuredReference.StartColumn);
Assert.IsNull(structuredReference.EndColumn);
Assert.AreEqual(ItemSpecifiers.ThisRow, structuredReference.ItemSpecifiers);
}
public void ResolveStructuredReferenceThisRowRightOfTable()
{
var reference = new StructuredReference($"{EpplusExcelDataProviderTest.TableName}[[#This Row],[{EpplusExcelDataProviderTest.Header1}]]");
var result = this.DataProviderWithDataAndHeaders.ResolveStructuredReference(reference, "Sheet1", 7, 7);
Assert.AreEqual(7, result.Address.Start.Row);
Assert.AreEqual(3, result.Address.Start.Column);
Assert.AreEqual(7, result.Address.End.Row);
Assert.AreEqual(3, result.Address.End.Column);
Assert.AreEqual("h1_r4", result.GetOffset(0, 0));
}
public void ResolveStructuredReferenceTotalsColumnSpecified()
{
var reference = new StructuredReference($"{EpplusExcelDataProviderTest.TableName}[[#Totals],[Header2]]");
var result = this.DataProviderWithDataHeadersAndTotals.ResolveStructuredReference(reference, "Sheet1", 15, 7);
Assert.AreEqual(10, result.Address.Start.Row);
Assert.AreEqual(4, result.Address.Start.Column);
Assert.AreEqual(10, result.Address.End.Row);
Assert.AreEqual(4, result.Address.End.Column);
Assert.AreEqual("h2_t", result.GetOffset(0, 0));
}
/// <summary>
/// Compiles the expression into a value.
/// </summary>
/// <returns>The <see cref="CompileResult"/> with the expression value.</returns>
public override CompileResult Compile()
{
var structuredReference = new StructuredReference(this.ExpressionString);
var c = this._parsingContext.Scopes.Current;
var result = _excelDataProvider.ResolveStructuredReference(structuredReference, c.Address.Worksheet, c.Address.FromRow, c.Address.FromCol);
if (result == null)
{
return(CompileResult.Empty);
}
return(base.BuildResult(result));
}
public void ResolveStructuredReferenceHeadersColumnRangeSpecified()
{
var reference = new StructuredReference($"{EpplusExcelDataProviderTest.TableName}[[#Headers],[Header2]:[Header4]]");
var result = this.DataProviderWithDataAndHeaders.ResolveStructuredReference(reference, "Sheet1", 15, 7);
Assert.AreEqual(3, result.Address.Start.Row);
Assert.AreEqual(4, result.Address.Start.Column);
Assert.AreEqual(3, result.Address.End.Row);
Assert.AreEqual(6, result.Address.End.Column);
Assert.AreEqual(EpplusExcelDataProviderTest.Header2, result.GetOffset(0, 0));
Assert.AreEqual(EpplusExcelDataProviderTest.Header3, result.GetOffset(0, 1));
Assert.AreEqual(EpplusExcelDataProviderTest.Header4, result.GetOffset(0, 2));
}
public void ResolveStructuredReferenceTotals()
{
var reference = new StructuredReference($"{EpplusExcelDataProviderTest.TableName}[#Totals]");
var result = this.DataProviderWithDataAndTotals.ResolveStructuredReference(reference, "Sheet1", 15, 7);
Assert.AreEqual(10, result.Address.Start.Row);
Assert.AreEqual(3, result.Address.Start.Column);
Assert.AreEqual(10, result.Address.End.Row);
Assert.AreEqual(6, result.Address.End.Column);
Assert.AreEqual("h1_t", result.GetOffset(0, 0));
Assert.AreEqual("h2_t", result.GetOffset(0, 1));
Assert.AreEqual("h3_t", result.GetOffset(0, 2));
Assert.AreEqual("h4_t", result.GetOffset(0, 3));
}
public void ResolveStructuredReferenceDataWithTotalsThatDoNotExistColumnSpecified()
{
var reference = new StructuredReference($"{EpplusExcelDataProviderTest.TableName}[[#Data],[#Totals],[Header2]]");
var result = this.DataProviderWithDataAndHeaders.ResolveStructuredReference(reference, "Sheet1", 15, 7);
Assert.AreEqual(4, result.Address.Start.Row);
Assert.AreEqual(4, result.Address.Start.Column);
Assert.AreEqual(9, result.Address.End.Row);
Assert.AreEqual(4, result.Address.End.Column);
Assert.AreEqual("h2_r1", result.GetOffset(0, 0));
Assert.AreEqual("h2_r2", result.GetOffset(1, 0));
Assert.AreEqual("h2_r3", result.GetOffset(2, 0));
Assert.AreEqual("h2_r4", result.GetOffset(3, 0));
Assert.AreEqual("h2_r5", result.GetOffset(4, 0));
Assert.AreEqual("h2_r6", result.GetOffset(5, 0));
}
public void ResolveStructuredReferenceEmptyBracketsResolvesToData()
{
var reference = new StructuredReference($"{EpplusExcelDataProviderTest.TableName}[]");
var result = this.DataProviderWithDataAndTotals.ResolveStructuredReference(reference, "Sheet1", 15, 7);
Assert.AreEqual(4, result.Address.Start.Row);
Assert.AreEqual(3, result.Address.Start.Column);
Assert.AreEqual(9, result.Address.End.Row);
Assert.AreEqual(6, result.Address.End.Column);
Assert.AreEqual("h1_r1", result.GetOffset(0, 0));
Assert.AreEqual("h1_r2", result.GetOffset(1, 0));
Assert.AreEqual("h1_r3", result.GetOffset(2, 0));
Assert.AreEqual("h1_r4", result.GetOffset(3, 0));
Assert.AreEqual("h1_r5", result.GetOffset(4, 0));
Assert.AreEqual("h1_r6", result.GetOffset(5, 0));
}
public void ResolveStructuredReferenceDataIgnoresHeaders()
{
var reference = new StructuredReference($"{EpplusExcelDataProviderTest.TableName}[[#Data],[{EpplusExcelDataProviderTest.Header4}]]");
var result = this.DataProviderWithDataAndHeaders.ResolveStructuredReference(reference, "Sheet1", 15, 7);
Assert.AreEqual(4, result.Address.Start.Row);
Assert.AreEqual(6, result.Address.Start.Column);
Assert.AreEqual(9, result.Address.End.Row);
Assert.AreEqual(6, result.Address.End.Column);
Assert.AreEqual("h4_r1", result.GetOffset(0, 0));
Assert.AreEqual("h4_r2", result.GetOffset(1, 0));
Assert.AreEqual("h4_r3", result.GetOffset(2, 0));
Assert.AreEqual("h4_r4", result.GetOffset(3, 0));
Assert.AreEqual("h4_r5", result.GetOffset(4, 0));
Assert.AreEqual("h4_r6", result.GetOffset(5, 0));
}
public void HasValidItemSpecifiersValidTests()
{
var structuredReference = new StructuredReference("MyTable[#Data]");
Assert.IsTrue(structuredReference.HasValidItemSpecifiers());
structuredReference = new StructuredReference("MyTable[#Headers]");
Assert.IsTrue(structuredReference.HasValidItemSpecifiers());
structuredReference = new StructuredReference("MyTable[#Totals]");
Assert.IsTrue(structuredReference.HasValidItemSpecifiers());
structuredReference = new StructuredReference("MyTable[#This row]");
Assert.IsTrue(structuredReference.HasValidItemSpecifiers());
structuredReference = new StructuredReference("MyTable[#All]");
Assert.IsTrue(structuredReference.HasValidItemSpecifiers());
structuredReference = new StructuredReference("MyTable[[#Data],[#Headers]]");
Assert.IsTrue(structuredReference.HasValidItemSpecifiers());
structuredReference = new StructuredReference("MyTable[[#Data],[#Totals]]");
Assert.IsTrue(structuredReference.HasValidItemSpecifiers());
}
public void ResolveStructuredReferenceAllHeadersDoNotExist()
{
var reference = new StructuredReference($"{EpplusExcelDataProviderTest.TableName}[#All]");
var result = this.DataProviderWithDataAndTotals.ResolveStructuredReference(reference, "Sheet1", 15, 7);
Assert.AreEqual(4, result.Address.Start.Row);
Assert.AreEqual(3, result.Address.Start.Column);
Assert.AreEqual(10, result.Address.End.Row);
Assert.AreEqual(6, result.Address.End.Column);
Assert.AreEqual("h1_r1", result.GetOffset(0, 0));
Assert.AreEqual("h1_r2", result.GetOffset(1, 0));
Assert.AreEqual("h1_r3", result.GetOffset(2, 0));
Assert.AreEqual("h1_r4", result.GetOffset(3, 0));
Assert.AreEqual("h1_r5", result.GetOffset(4, 0));
Assert.AreEqual("h1_r6", result.GetOffset(5, 0));
Assert.AreEqual("h4_r6", result.GetOffset(5, 3));
Assert.AreEqual("h1_t", result.GetOffset(6, 0));
Assert.AreEqual("h4_t", result.GetOffset(6, 3));
}
