public void Setup()
{
_package = new ExcelPackage();
var excelDataProvider = new EpplusExcelDataProvider(_package);
_parser = new FormulaParser(excelDataProvider);
}
public void CompileMultiCellReferenceWithValues()
{
var parsingContext = ParsingContext.Create();
var file = new FileInfo("filename.xlsx");
using (var package = new ExcelPackage(file))
using (var sheet = package.Workbook.Worksheets.Add("NewSheet"))
using (var excelDataProvider = new EpplusExcelDataProvider(package))
{
sheet.Cells[1, 1].Value = "Value1";
sheet.Cells[2, 1].Value = "Value2";
sheet.Cells[3, 1].Value = "Value3";
sheet.Cells[4, 1].Value = "Value4";
sheet.Cells[5, 1].Value = "Value5";
var rangeAddressFactory = new RangeAddressFactory(excelDataProvider);
using (parsingContext.Scopes.NewScope(rangeAddressFactory.Create("NewSheet", 3, 3)))
{
var expression = new ExcelAddressExpression("A1:A5", excelDataProvider, parsingContext);
var result = expression.Compile();
var rangeInfo = result.Result as ExcelDataProvider.IRangeInfo;
Assert.IsNotNull(rangeInfo);
Assert.AreEqual("A1:A5", rangeInfo.Address.Address);
Assert.AreEqual(5, rangeInfo.Count());
for (int i = 1; i <= 5; i++)
{
var rangeItem = rangeInfo.ElementAt(i - 1);
Assert.AreEqual("Value" + i, rangeItem.Value);
}
}
}
}
public void Initialize()
{
_package = new ExcelPackage();
_provider = new EpplusExcelDataProvider(_package);
_parsingContext = ParsingContext.Create();
_parsingContext.Scopes.NewScope(RangeAddress.Empty);
_worksheet = _package.Workbook.Worksheets.Add("testsheet");
}
public void GetSinglePivotTableFromReferenceNoPivotTable()
{
using (var package = new ExcelPackage(new FileInfo("PivotTableColumnFields.xlsx")))
{
var provider = new EpplusExcelDataProvider(package);
Assert.IsNull(provider.GetPivotTable(new ExcelAddress("Sheet1!ZZ999")));
}
}
public void GetSinglePivotTableFromRangePartialCellReference()
{
using (var package = new ExcelPackage(new FileInfo("PivotTableColumnFields.xlsx")))
{
var provider = new EpplusExcelDataProvider(package);
var pt = provider.GetPivotTable(new ExcelAddress("NoSubtotals!H12:K15"));
Assert.AreEqual("NoSubtotalsPivotTable1", pt.Name);
}
}
public void GetSinglePivotTableFromRangeWithMultiplePivotTables()
{
using (var package = new ExcelPackage(new FileInfo("PivotTableColumnFields.xlsx")))
{
var provider = new EpplusExcelDataProvider(package);
// RowItems!B3:M7 contains three pivot tables. The first one (closest to A1) should be returned.
var pt = provider.GetPivotTable(new ExcelAddress("RowItems!B3:M7"));
Assert.AreEqual("RowItemsPivotTable1", pt.Name);
}
}
private static ExcelDatabase GetDatabase(ExcelPackage package)
{
var provider = new EpplusExcelDataProvider(package);
var sheet = package.Workbook.Worksheets.Add("test");
sheet.Cells["A1"].Value = "col1";
sheet.Cells["A2"].Value = 1;
sheet.Cells["B1"].Value = "col2";
sheet.Cells["B2"].Value = 2;
var database = new ExcelDatabase(provider, "A1:B2");
return(database);
}
public void FromCompileResultEnumerableIntegerUnknown()
{
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("Sheet1");
sheet.Cells["C1"].Value = 1;
sheet.Cells["C2"].Value = 2;
sheet.Cells["C3"].Value = 3;
var dataProvider = new EpplusExcelDataProvider(package);
var range = dataProvider.GetRange("Sheet1", 1, 3, 3, 3);
var compileResult = new CompileResult(range, DataType.Unknown);
var result = new ExpressionConverter().FromCompileResult(compileResult);
Assert.IsInstanceOfType(result, typeof(IntegerExpression));
Assert.AreEqual(1d, result.Compile().Result);
}
}
public void CriteriaShouldIgnoreEmptyFields2()
{
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("test");
sheet.Cells["A1"].Value = "Crit1";
sheet.Cells["A2"].Value = 1;
var provider = new EpplusExcelDataProvider(package);
var criteria = new ExcelDatabaseCriteria(provider, "A1:B2");
Assert.AreEqual(1, criteria.Items.Count);
Assert.AreEqual("crit1", criteria.Items.Keys.First().ToString());
Assert.AreEqual(1, criteria.Items.Values.Last());
}
}
public void ExtractCriterionFromCellRangeWithObjectReturnsCorrectValue()
{
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Sheet1");
var provider = new EpplusExcelDataProvider(package);
this.ParsingContext.Scopes.NewScope(RangeAddress.Empty);
worksheet.Cells["E12"].Value = 1;
worksheet.Cells["E13"].Value = 2;
worksheet.Cells["E14"].Value = 3;
worksheet.Cells["F12"].Value = 1;
worksheet.Cells["F13"].Value = ">2";
worksheet.Cells["F14"].Value = 3;
worksheet.Cells["H13"].Formula = "SUMIF(E12:E14, F12:F14)";
worksheet.Calculate();
Assert.AreEqual(3d, worksheet.Cells["H13"].Value);
}
}
public void ExtractCriterionFromCellRangeWithNonMatchingColReturnsZero()
{
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Sheet1");
var provider = new EpplusExcelDataProvider(package);
this.ParsingContext.Scopes.NewScope(RangeAddress.Empty);
worksheet.Cells["E7"].Value = 5;
worksheet.Cells["F7"].Value = 10;
worksheet.Cells["G7"].Value = 15;
IRangeInfo testRange = provider.GetRange(worksheet.Name, 7, 5, 7, 7);
IRangeInfo firstRange = provider.GetRange(worksheet.Name, 8, 8, 8, 8);
var address = firstRange.Address;
var arguments = FunctionsHelper.CreateArgs(firstRange, testRange);
var result = IfHelper.ExtractCriterionFromCellRange(arguments.ElementAt(1), worksheet, address._fromRow, address._fromCol);
Assert.AreEqual(0, result);
}
}
public void ExtractCriterionFromCellRangeWithSameRowCellReferenceReturnsCorrectValue()
{
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Sheet2");
var provider = new EpplusExcelDataProvider(package);
this.ParsingContext.Scopes.NewScope(RangeAddress.Empty);
worksheet.Cells["B1"].Value = 5;
worksheet.Cells["B2"].Value = 10;
worksheet.Cells["B3"].Value = 15;
IRangeInfo testRange = provider.GetRange(worksheet.Name, 1, 2, 3, 2);
IRangeInfo firstRange = provider.GetRange(worksheet.Name, 2, 2, 2, 2);
var address = firstRange.Address;
var arguments = FunctionsHelper.CreateArgs(firstRange, testRange);
var result = IfHelper.ExtractCriterionFromCellRange(arguments.ElementAt(1), worksheet, address._fromRow, address._fromCol);
Assert.AreEqual(10, result);
}
}
public void CompileSingleCellReference()
{
var parsingContext = ParsingContext.Create();
var file = new FileInfo("filename.xlsx");
using (var package = new ExcelPackage(file))
using (var sheet = package.Workbook.Worksheets.Add("NewSheet"))
using (var excelDataProvider = new EpplusExcelDataProvider(package))
{
var rangeAddressFactory = new RangeAddressFactory(excelDataProvider);
using (parsingContext.Scopes.NewScope(rangeAddressFactory.Create("NewSheet", 3, 3)))
{
var expression = new ExcelAddressExpression("A1", excelDataProvider, parsingContext);
var result = expression.Compile();
Assert.IsNull(result.Result);
}
}
}
public void CompileSingleCellReferenceResolveToRangeAbsolute()
{
var parsingContext = ParsingContext.Create();
var file = new FileInfo("filename.xlsx");
using (var package = new ExcelPackage(file))
using (var sheet = package.Workbook.Worksheets.Add("NewSheet"))
using (var excelDataProvider = new EpplusExcelDataProvider(package))
{
var rangeAddressFactory = new RangeAddressFactory(excelDataProvider);
using (parsingContext.Scopes.NewScope(rangeAddressFactory.Create("NewSheet", 3, 3)))
{
var expression = new ExcelAddressExpression("$A$1", excelDataProvider, parsingContext);
expression.ResolveAsRange = true;
var result = expression.Compile();
var rangeInfo = result.Result as ExcelDataProvider.IRangeInfo;
Assert.IsNotNull(rangeInfo);
Assert.AreEqual("$A$1", rangeInfo.Address.Address);
}
}
}
public void CompileMultiCellReferenceAbsolute()
{
var parsingContext = ParsingContext.Create();
var file = new FileInfo("filename.xlsx");
using (var package = new ExcelPackage(file))
using (var sheet = package.Workbook.Worksheets.Add("NewSheet"))
using (var excelDataProvider = new EpplusExcelDataProvider(package))
{
var rangeAddressFactory = new RangeAddressFactory(excelDataProvider);
using (parsingContext.Scopes.NewScope(rangeAddressFactory.Create("NewSheet", 3, 3)))
{
var expression = new ExcelAddressExpression("$A$1:$A$5", excelDataProvider, parsingContext);
var result = expression.Compile();
var rangeInfo = result.Result as ExcelDataProvider.IRangeInfo;
Assert.IsNotNull(rangeInfo);
Assert.AreEqual("$A$1:$A$5", rangeInfo.Address.Address);
// Enumerating the range still yields no results.
Assert.AreEqual(0, rangeInfo.Count());
}
}
}