protected override void OnModelCreating(ExcelModelBuilder modelBuilder)
{
var sheet1 = modelBuilder.Sheet <Class1>();
sheet1.Column(x => x.StringColumn)
.Optional();
sheet1.Column(x => x.IntColumn)
.Optional();
sheet1.Column(x => x.FloatColumn)
.Optional();
sheet1.Column(x => x.DoubleColumn)
.Optional();
sheet1.Column(x => x.DateTimeColumn)
.Optional();
sheet1.Column(x => x.TimeSpanColumn)
.Optional();
sheet1.Column(x => x.BooleanColumn)
.Optional();
sheet1.Column(x => x.GuidColumn)
.Optional();
sheet1.Column(x => x.UriColumn)
.Optional();
sheet1.Column(x => x.DecimalColumn)
.Optional();
sheet1.Column(x => x.NullableIntColumn)
.Optional();
var sheet2 = modelBuilder.Sheet <Class2>();
sheet2.Column(x => x.StringColumn);
}
public void CantAddDuplicateSheetClass()
{
var builder = new ExcelModelBuilder();
builder.Sheet <Class1>("Sheet1");
Assert.ThrowsException <InvalidOperationException>(() => builder.Sheet <Class1>("Sheet2"));
}
public void CantAddNullSheetName()
{
var builder = new ExcelModelBuilder();
Assert.ThrowsException <ArgumentNullException>(() => { builder.Sheet <Class1>(null); });
var sheet = builder.Sheet <Class1>();
Assert.ThrowsException <ArgumentNullException>(() => { sheet.AlternateName(null); });
}
public void AccessSheetTwice()
{
var builder = new ExcelModelBuilder();
var sheet1 = builder.Sheet <Class1>();
var sheet2 = builder.Sheet <Class1>();
Assert.AreEqual(sheet1, sheet2);
var sheet3 = builder.Sheet <Class2>("Sheet2");
var sheet4 = builder.Sheet <Class2>("Sheet2");
Assert.AreEqual(sheet3, sheet4);
}
public void DefaultColumns_PartialRow()
{
sheet.Cells[1, 4].Value = 4;
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.StringColumn).Optional();
sheetBuilder.Column(x => x.DoubleColumn).Optional();
sheetBuilder.Column(x => x.NullableIntColumn).Optional();
sheetBuilder.Column(x => x.FloatColumn);
var context = new SampleContext(builder.Build());
var result = context.TestOnReadRow(sheet.Cells[1, 1, 1, 4], context.Model.Sheets[0], new[] {
context.Model.Sheets[0].Columns[0],
context.Model.Sheets[0].Columns[1],
context.Model.Sheets[0].Columns[2],
context.Model.Sheets[0].Columns[3],
});
Assert.IsInstanceOfType(result, typeof(Class1));
var ret = (Class1)result;
Assert.AreEqual(null, ret.StringColumn);
Assert.AreEqual(0, ret.DoubleColumn);
Assert.AreEqual(null, ret.NullableIntColumn);
Assert.AreEqual(4, ret.FloatColumn);
}
public void InvalidParametersThrows()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>()
.SkipEmptyRows();
sheetBuilder.Column(x => x.StringColumn);
var context = new SampleContext(builder.Build());
var resultSuccess = context.TestOnReadRow(sheet.Cells[1, 1], context.Model.Sheets[0], new[] { context.Model.Sheets[0].Columns[0] });
Assert.ThrowsException <ArgumentOutOfRangeException>(() => {
var result = context.TestOnReadRow(sheet.Cells[1, 1], context.Model.Sheets[0], new[] { context.Model.Sheets[0].Columns[0], null });
});
Assert.ThrowsException <ArgumentOutOfRangeException>(() => {
var result = context.TestOnReadRow(sheet.Cells[1, 1, 1, 2], context.Model.Sheets[0], new[] { context.Model.Sheets[0].Columns[0] });
});
Assert.ThrowsException <ArgumentOutOfRangeException>(() => {
var result = context.TestOnReadRow(sheet.Cells[1, 1, 2, 1], context.Model.Sheets[0], new[] { context.Model.Sheets[0].Columns[0] });
});
Assert.ThrowsException <ArgumentNullException>(() => {
var result = context.TestOnReadRow(null, context.Model.Sheets[0], new[] { context.Model.Sheets[0].Columns[0] });
});
Assert.ThrowsException <ArgumentNullException>(() => {
var result = context.TestOnReadRow(sheet.Cells[1, 1], null, new[] { context.Model.Sheets[0].Columns[0] });
});
Assert.ThrowsException <ArgumentNullException>(() => {
var result = context.TestOnReadRow(sheet.Cells[1, 1], context.Model.Sheets[0], null);
});
}
public void SkipEmptyRows()
{
sheet.SetValue(1, 1, "col1");
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.StringColumn, "col1");
var model = builder.Build();
var sheetModel = model.Sheets[0];
var mock = new Mock <SampleContext>(model);
var testret1 = new Class1();
var testret2 = new Class1();
mock.Protected().Setup <object>("OnReadRow", ItExpr.Is <ExcelRange>(x => x.Address == "A2"), sheetModel, ItExpr.Is <IColumnModel[]>(x => x[0] == sheetModel.Columns[0])).Returns(testret1).Verifiable();
mock.Protected().Setup <object>("OnReadRow", ItExpr.Is <ExcelRange>(x => x.Address == "A3"), sheetModel, ItExpr.Is <IColumnModel[]>(x => x[0] == sheetModel.Columns[0])).Returns(null).Verifiable();
mock.Protected().Setup <object>("OnReadRow", ItExpr.Is <ExcelRange>(x => x.Address == "A4"), sheetModel, ItExpr.Is <IColumnModel[]>(x => x[0] == sheetModel.Columns[0])).Returns(testret2).Verifiable();
mock.Protected().Setup <ExcelRange>("DefaultReadRangeLocator", sheet).Returns(sheet.Cells[1, 1, 4, 1]).Verifiable();
mock.CallBase = true;
var context = mock.Object;
var result = context.TestOnReadSheet(sheet, context.Model.Sheets[0]);
Assert.IsInstanceOfType(result, typeof(List <Class1>));
var ret = (List <Class1>)result;
Assert.AreEqual(2, ret.Count);
Assert.AreEqual(testret1, ret[0]);
Assert.AreEqual(testret2, ret[1]);
mock.Verify();
}
public void Multiple()
{
sheet.SetValue(1, 1, "col1");
sheet.SetValue(1, 2, "col2");
sheet.SetValue(2, 1, "test");
sheet.SetValue(2, 2, 2);
sheet.SetValue(3, 1, "test2");
sheet.SetValue(3, 2, 3);
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.IntColumn, "col2");
sheetBuilder.Column(x => x.StringColumn, "col1");
var model = builder.Build();
var sheetModel = model.Sheets[0];
var mock = new Mock <SampleContext>(model);
mock.Protected().Setup <object>("OnReadRow", ItExpr.Is <ExcelRange>(x => x.Address == "A2:B2"), sheetModel, ItExpr.Is <IColumnModel[]>(x => x[0] == sheetModel.Columns[1] && x[1] == sheetModel.Columns[0])).Returns(new Class1()).Verifiable();
mock.Protected().Setup <object>("OnReadRow", ItExpr.Is <ExcelRange>(x => x.Address == "A3:B3"), sheetModel, ItExpr.Is <IColumnModel[]>(x => x[0] == sheetModel.Columns[1] && x[1] == sheetModel.Columns[0])).Returns(new Class1()).Verifiable();
mock.CallBase = true;
var context = mock.Object;
var result = context.TestOnReadSheet(sheet, context.Model.Sheets[0]);
Assert.IsInstanceOfType(result, typeof(List <Class1>));
var ret = (List <Class1>)result;
Assert.AreEqual(2, ret.Count);
mock.Verify();
}
public void AddMultipleColumns()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.StringColumn);
sheetBuilder.Column(x => x.IntColumn);
var model = builder.Build().Sheets[0];
Assert.AreEqual(2, model.Columns.Count);
var columnModel = model.Columns[0];
Assert.AreEqual("StringColumn", columnModel.Name);
Assert.AreEqual(0, columnModel.AlternateNames.Count);
Assert.AreEqual(columnModel, model.Columns["StringColumn"]);
Assert.AreEqual(columnModel, model.Columns["STRINGCOLUMN"]);
var columnModel2 = model.Columns[1];
Assert.AreEqual("IntColumn", columnModel2.Name);
Assert.AreEqual(0, columnModel2.AlternateNames.Count);
Assert.AreEqual(columnModel2, model.Columns["IntColumn"]);
Assert.AreEqual(columnModel2, model.Columns["INTCOLUMN"]);
}
protected override void OnModelCreating(ExcelModelBuilder modelBuilder)
{
var sheetBuilder = modelBuilder.Sheet <Class1>();
sheetBuilder.Column(x => x.StringColumn)
.AlternateName("String");
sheetBuilder.Column(x => x.IntColumn);
}
public void CantAddDuplicateColumnTwice()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
var columnBuilder = sheetBuilder.Column(x => x.StringColumn, "Column1");
Assert.ThrowsException <InvalidOperationException>(() => {
sheetBuilder.Column(x => x.StringColumn, "Column2");
});
}
public void ColumnReturnsSameReference()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class3>();
var col1 = sheetBuilder.Column(x => x.Valid1);
var col2 = sheetBuilder.Column(x => x.Valid2, "Test");
Assert.AreEqual(col1, sheetBuilder.Column(x => x.Valid1));
Assert.AreEqual(col2, sheetBuilder.Column(x => x.Valid2, "Test"));
}
public void CantAddNullColumnName()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
Assert.ThrowsException <ArgumentNullException>(() => sheetBuilder.Column(x => x.StringColumn, null));
var columnBuilder = sheetBuilder.Column(x => x.StringColumn);
Assert.ThrowsException <ArgumentNullException>(() => columnBuilder.AlternateName(null));
}
public void CantAddDuplicateSheetName()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>("Sheet1");
Assert.ThrowsException <ArgumentException>(() => {
sheetBuilder.AlternateName("Sheet1");
});
Assert.ThrowsException <ArgumentException>(() => {
sheetBuilder.AlternateName("SHEET1");
});
}
public void CtorRedundantChecks()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class3>();
Expression <Func <Class3, int> > expressionValid1 = c => c.Valid1;
var memberValid1 = (MemberExpression)expressionValid1.Body;
var memberInvalid2 = Expression.MakeMemberAccess(Expression.Parameter(typeof(Class3)), typeof(Class3).GetMember("Invalid2")[0]);
Assert.ThrowsException <ArgumentNullException>(() => new ColumnModelBuilder <Class3, int>(sheetBuilder, memberValid1, null));
Assert.ThrowsException <ArgumentOutOfRangeException>(() => new ColumnModelBuilder <Class3, int>(sheetBuilder, memberInvalid2, "test"));
Assert.ThrowsException <ArgumentNullException>(() => new ColumnModelBuilder <Class3, int>(null, memberValid1, "test"));
}
public void OptionalSheet()
{
var sheet = workbook.Worksheets.Add("Sheet1");
var builder = new ExcelModelBuilder();
builder.Sheet <Class1>().Optional();
builder.Sheet <Class2>("Sheet1");
var model = builder.Build();
var mock = new Mock <SampleContext>(model);
mock.Protected().Setup <object>("OnReadSheet", sheet, model.Sheets[1]).Returns(new List <Class2>()).Verifiable();
mock.CallBase = true;
var context = mock.Object;
var result = context.TestOnReadFile(workbook);
Assert.IsNotNull(result);
Assert.AreEqual(2, result.Count);
Assert.IsInstanceOfType(result[0], typeof(List <Class1>));
Assert.IsInstanceOfType(result[1], typeof(List <Class2>));
mock.Verify();
}
public void SkipEmptyRow()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>()
.SkipEmptyRows();
sheetBuilder.Column(x => x.StringColumn);
var context = new SampleContext(builder.Build());
var result = context.TestOnReadRow(sheet.Cells[1, 1], context.Model.Sheets[0], new[] { context.Model.Sheets[0].Columns[0] });
Assert.IsNull(result);
}
public void SpecifyWriteSerializer()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.StringColumn)
.WriteSerializer((range, value) => range.Value = "SpecifyWriteSerializer")
.Optional();
var context = new SampleContext(builder.Build());
context.TestOnWriteRow(sheet.Cells[1, 1], context.Model.Sheets[0], testRow1);
Assert.AreEqual("SpecifyWriteSerializer", sheet.Cells[1, 1].Value);
}
public void ColumnMemberTypes()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class3>();
sheetBuilder.Column(x => x.Valid1);
sheetBuilder.Column(x => x.Valid2);
sheetBuilder.Column(x => x.Valid3);
sheetBuilder.Column(x => x.Valid4);
Assert.ThrowsException <ArgumentOutOfRangeException>(() => sheetBuilder.Column(x => x.Invalid1));
Assert.ThrowsException <ArgumentOutOfRangeException>(() => sheetBuilder.Column(x => x.Invalid3()));
Assert.ThrowsException <ArgumentOutOfRangeException>(() => sheetBuilder.Column(x => x.Invalid3(), "Invalid3"));
}
public void CantAddDuplicateColumnName()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
var columnBuilder = sheetBuilder.Column(x => x.StringColumn, "Column1");
Assert.ThrowsException <ArgumentException>(() => {
columnBuilder.AlternateName("Column1");
});
Assert.ThrowsException <ArgumentException>(() => {
columnBuilder.AlternateName("COLUMN1");
});
}
public void EmptyRowThrows()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.DoubleColumn);
sheetBuilder.Column(x => x.StringColumn);
var context = new SampleContext(builder.Build());
Assert.ThrowsException <RowEmptyException>(() => {
var result = context.TestOnReadRow(sheet.Cells[1, 1, 1, 2], context.Model.Sheets[0], new[] { context.Model.Sheets[0].Columns[1], context.Model.Sheets[0].Columns[0] });
});
}
public void InvalidDataThrows()
{
sheet.SetValue(1, 1, "test1");
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.DoubleColumn);
var context = new SampleContext(builder.Build());
Assert.ThrowsException <ParseDataException>(() => {
var result = context.TestOnReadRow(sheet.Cells[1, 1], context.Model.Sheets[0], new[] { context.Model.Sheets[0].Columns[0] });
});
}
public void DefaultSheetProperties()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
var model = builder.Build();
Assert.AreEqual(0, model.Sheets[0].Columns.Count);
Assert.AreEqual(false, model.Sheets[0].Optional);
Assert.AreEqual(false, model.Sheets[0].SkipEmptyRows);
Assert.AreEqual(null, model.Sheets[0].ReadRangeLocator);
Assert.AreEqual(null, model.Sheets[0].WriteRangeLocator);
Assert.AreEqual(null, model.Sheets[0].WritePolisher);
}
public void FailEmptySheet()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.StringColumn, "col1");
var model = builder.Build();
var sheetModel = model.Sheets[0];
var context = new SampleContext(model);
Assert.ThrowsException <SheetEmptyException>(() => {
var result = context.TestOnReadSheet(sheet, context.Model.Sheets[0]);
});
}
public void NullableColumnTest()
{
sheet.SetValue(1, 1, "test1");
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.StringColumn);
sheetBuilder.Column(x => x.NullableIntColumn);
var context = new SampleContext(builder.Build());
Assert.ThrowsException <ColumnDataMissingException>(() => {
var result = context.TestOnReadRow(sheet.Cells[1, 1, 1, 2], context.Model.Sheets[0], new[] { context.Model.Sheets[0].Columns[0], context.Model.Sheets[0].Columns[1] });
});
}
public void DefaultColumnProperties()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
var columnBuilder = sheetBuilder.Column(x => x.StringColumn);
var model = builder.Build();
var columnModel = model.Sheets[0].Columns[0];
Assert.AreEqual(false, columnModel.Optional);
Assert.AreEqual(null, columnModel.ReadSerializer);
Assert.AreEqual(null, columnModel.WriteSerializer);
Assert.AreEqual(null, columnModel.WritePolisher);
Assert.AreEqual(null, columnModel.HeaderFormatter);
Assert.AreEqual(null, columnModel.ColumnFormatter);
}
public void SingleProperty()
{
sheet.SetValue(1, 1, "test");
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class2>();
sheetBuilder.Column(x => x.StringColumn);
var context = new SampleContext(builder.Build());
var result = context.TestOnReadRow(sheet.Cells[1, 1], context.Model.Sheets[0], new[] { context.Model.Sheets[0].Columns[0] });
Assert.IsInstanceOfType(result, typeof(Class2));
var ret = (Class2)result;
Assert.AreEqual("test", ret.StringColumn);
}
public void AddMultipleSheets()
{
var builder = new ExcelModelBuilder();
builder.Sheet <Class1>();
builder.Sheet <Class2>();
var model = builder.Build();
Assert.AreEqual(2, model.Sheets.Count);
var sheetModel = model.Sheets[0];
Assert.AreEqual("Class1", sheetModel.Name);
Assert.AreEqual(0, sheetModel.AlternateNames.Count);
Assert.AreEqual(sheetModel, model.Sheets["Class1"]);
Assert.AreEqual(sheetModel, model.Sheets["CLASS1"]);
Assert.AreEqual(sheetModel, model.Sheets[typeof(Class1)]);
sheetModel = model.Sheets[1];
Assert.AreEqual("Class2", sheetModel.Name);
Assert.AreEqual(0, sheetModel.AlternateNames.Count);
Assert.AreEqual(sheetModel, model.Sheets["Class2"]);
Assert.AreEqual(sheetModel, model.Sheets["CLASS2"]);
Assert.AreEqual(sheetModel, model.Sheets[typeof(Class2)]);
Assert.ThrowsException <KeyNotFoundException>(() => model.Sheets["Sheet1"]);
}
public void DoesntCheckMapping()
{
sheet.SetValue(1, 1, "test");
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.StringColumn);
sheetBuilder.Column(x => x.DoubleColumn);
var context = new SampleContext(builder.Build());
var result = context.TestOnReadRow(sheet.Cells[1, 1, 1, 2], context.Model.Sheets[0], new[] { context.Model.Sheets[0].Columns[0], null });
Assert.IsInstanceOfType(result, typeof(Class1));
var ret = (Class1)result;
Assert.AreEqual("test", ret.StringColumn);
}
public void AddSheetAltName()
{
var builder = new ExcelModelBuilder();
builder.Sheet <Class1>("Sheet1");
var model = builder.Build();
Assert.AreEqual(1, model.Sheets.Count);
var sheet = model.Sheets[0];
Assert.AreEqual("Sheet1", sheet.Name);
Assert.AreEqual(0, sheet.AlternateNames.Count);
Assert.AreEqual(sheet, model.Sheets["Sheet1"]);
Assert.AreEqual(sheet, model.Sheets[typeof(Class1)]);
Assert.ThrowsException <KeyNotFoundException>(() => model.Sheets["Class1"]);
}