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 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 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 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 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"]);
}
public void DefaultProperties()
{
var builder = new ExcelModelBuilder();
var model = builder.Build();
Assert.AreEqual(0, model.Sheets.Count);
Assert.AreEqual(false, model.IgnoreSheetNames);
}
public void SetProperties()
{
var builder = new ExcelModelBuilder();
builder.IgnoreSheetNames();
var model = builder.Build();
Assert.AreEqual(true, model.IgnoreSheetNames);
}
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 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 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 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 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 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 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 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 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 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"]);
}
public void FailRequiredColumns()
{
sheet.SetValue(1, 1, "col1");
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.StringColumn, "col1");
sheetBuilder.Column(x => x.IntColumn, "col2");
var model = builder.Build();
var sheetModel = model.Sheets[0];
var context = new SampleContext(model);
Assert.ThrowsException <ColumnMissingException>(() => {
var result = context.TestOnReadSheet(sheet, context.Model.Sheets[0]);
});
}
public void EmptySheet()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.StringColumn, "col1")
.Optional();
var model = builder.Build();
var sheetModel = model.Sheets[0];
var context = new SampleContext(model);
var result = context.TestOnReadSheet(sheet, context.Model.Sheets[0]);
Assert.IsInstanceOfType(result, typeof(List <Class1>));
var ret = (List <Class1>)result;
Assert.AreEqual(0, ret.Count);
}
public void MissingSheet()
{
var sheet = workbook.Worksheets.Add("Sheet1");
var builder = new ExcelModelBuilder();
builder.Sheet <Class1>();
var model = builder.Build();
var mock = new Mock <SampleContext>(model);
mock.Protected().Setup <object>("OnReadSheet", sheet, model.Sheets[0]).Returns(new List <Class1>()).Verifiable();
mock.CallBase = true;
var context = mock.Object;
Assert.ThrowsException <SheetMissingException>(() => {
var result = context.TestOnReadFile(workbook);
});
}
public void AddColumnAltName()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.StringColumn, "Column1");
var model = builder.Build().Sheets[0];
Assert.AreEqual(1, model.Columns.Count);
var columnModel = model.Columns[0];
Assert.AreEqual("Column1", columnModel.Name);
Assert.AreEqual(0, columnModel.AlternateNames.Count);
Assert.AreEqual(columnModel, model.Columns["Column1"]);
Assert.AreEqual(columnModel, model.Columns["COLUMN1"]);
Assert.ThrowsException <KeyNotFoundException>(() => model.Columns["StringColumn"]);
}
public void NullResultFromOnReadSheet_Unnamed()
{
var sheet = workbook.Worksheets.Add("Sheet1");
var builder = new ExcelModelBuilder();
builder.IgnoreSheetNames();
builder.Sheet <Class1>();
var model = builder.Build();
var mock = new Mock <SampleContext>(model);
mock.Protected().Setup <object>("OnReadSheet", sheet, model.Sheets[0]).Returns(null).Verifiable();
mock.CallBase = true;
var context = mock.Object;
Assert.ThrowsException <InvalidOperationException>(() => {
var result = context.TestOnReadFile(workbook);
});
}
public void DefaultWriteSerializer()
{
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.StringColumn)
.Optional();
var mock = new Mock <SampleContext>(builder.Build());
mock.CallBase = true;
mock.Protected().Setup("DefaultWriteSerializer", ItExpr.IsAny <ExcelRange>(), testRow1.StringColumn)
.Callback <ExcelRange, object>((range, value) => range.Value = "SpecifyWriteSerializer2")
.Verifiable();
var context = mock.Object;
context.TestOnWriteRow(sheet.Cells[1, 1], context.Model.Sheets[0], testRow1);
Assert.AreEqual("SpecifyWriteSerializer2", sheet.Cells[1, 1].Value);
mock.Verify();
}
public void Single()
{
var sheet = workbook.Worksheets.Add("Sheet1");
var builder = new ExcelModelBuilder();
builder.Sheet <Class1>("Sheet1");
var model = builder.Build();
var mock = new Mock <SampleContext>(model);
mock.Protected().Setup <object>("OnReadSheet", sheet, model.Sheets[0]).Returns(new List <Class1>()).Verifiable();
mock.CallBase = true;
var context = mock.Object;
var result = context.TestOnReadFile(workbook);
Assert.IsNotNull(result);
Assert.AreEqual(1, result.Count);
Assert.IsInstanceOfType(result[0], typeof(List <Class1>));
mock.Verify();
}
public void InvalidParametersThrows()
{
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 context = new SampleContext(model);
var resultSuccess = context.TestOnReadSheet(sheet, context.Model.Sheets[0]);
Assert.ThrowsException <ArgumentNullException>(() => {
var result = context.TestOnReadSheet(null, context.Model.Sheets[0]);
});
Assert.ThrowsException <ArgumentNullException>(() => {
var result = context.TestOnReadSheet(sheet, null);
});
}
public void VerifyDefaultSerializer()
{
sheet.SetValue(1, 1, "test");
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.StringColumn);
var mock = new Mock <SampleContext>(builder.Build());
mock.CallBase = true;
mock.Protected().Setup <object>("DefaultReadSerializer", ItExpr.Is <ExcelRange>(x => x.Address == "A1"), typeof(string)).Returns("test2").Verifiable();
var context = mock.Object;
var result = context.TestOnReadRow(sheet.Cells[1, 1], context.Model.Sheets[0], new[] { context.Model.Sheets[0].Columns[0] });
Assert.IsInstanceOfType(result, typeof(Class1));
var ret = (Class1)result;
Assert.AreEqual("test2", ret.StringColumn);
mock.Verify();
}
protected ExcelContext()
{
var excelModel = new ExcelModelBuilder();
OnModelCreating(excelModel);
_model = excelModel.Build();
_sheets = new List <IList>(Model.Sheets.Count);
_sheetNameLookup = new Dictionary <string, int>(Model.Sheets.Count);
_typeLookup = new Dictionary <Type, int>(Model.Sheets.Count);
for (int i = 0; i < Model.Sheets.Count; i++)
{
var sheet = Model.Sheets[i];
_sheets.Add(CreateListForSheet(sheet.Type));
_sheetNameLookup.Add(sheet.Name, i);
foreach (var sheetName in sheet.AlternateNames)
{
_sheetNameLookup.Add(sheetName, i);
}
_typeLookup.Add(sheet.Type, i);
}
_initialized = true;
}
public void SheetProperties()
{
Func <ExcelRange, string> readSerializer = range => "testdata";
Action <ExcelRange, string> writeSerializer = (range, value) => range.Value = "testwrite";
Action <ExcelRange> headerFormatter = _ => { };
Action <ExcelRange> columnFormatter = _ => { };
Action <ExcelRange> writePolisher = _ => { };
var builder = new ExcelModelBuilder();
var sheetBuilder = builder.Sheet <Class1>();
sheetBuilder.Column(x => x.StringColumn)
.Optional()
.ReadSerializer(null)
.ReadSerializer(readSerializer)
.WriteSerializer(null)
.WriteSerializer(writeSerializer)
.HeaderFormatter(null)
.HeaderFormatter(headerFormatter)
.ColumnFormatter(null)
.ColumnFormatter(columnFormatter)
.WritePolisher(null)
.WritePolisher(writePolisher);
var model = builder.Build();
var sheetModel = model.Sheets[0];
var columnModel = sheetModel.Columns[0];
Assert.AreEqual(true, columnModel.Optional);
Assert.AreEqual("testdata", columnModel.ReadSerializer(null));
var newFile = new ExcelPackage();
var newSheet = newFile.Workbook.Worksheets.Add("Sheet1");
var cell = newSheet.Cells[1, 1];
columnModel.WriteSerializer(cell, "test");
Assert.AreEqual("testwrite", cell.Value);
Assert.AreEqual(headerFormatter, columnModel.HeaderFormatter);
Assert.AreEqual(columnFormatter, columnModel.ColumnFormatter);
Assert.AreEqual(writePolisher, columnModel.WritePolisher);
}
public void SheetProperties()
{
Func <ExcelWorksheet, ExcelRange> readRangeLocator = worksheet => worksheet.Cells;
Func <ExcelWorksheet, ExcelRange> writeRangeLocator = worksheet => worksheet.Cells[1, 1];
Action <ExcelWorksheet, ExcelRange> writePolisher = (worksheet, range) => { };
var builder = new ExcelModelBuilder();
builder.Sheet <Class1>()
.Optional()
.SkipEmptyRows()
.ReadRangeLocator(readRangeLocator)
.WriteRangeLocator(writeRangeLocator)
.WritePolisher(writePolisher);
var model = builder.Build();
Assert.AreEqual(0, model.Sheets[0].Columns.Count);
Assert.AreEqual(true, model.Sheets[0].Optional);
Assert.AreEqual(true, model.Sheets[0].SkipEmptyRows);
Assert.AreEqual(readRangeLocator, model.Sheets[0].ReadRangeLocator);
Assert.AreEqual(writeRangeLocator, model.Sheets[0].WriteRangeLocator);
Assert.AreEqual(writePolisher, model.Sheets[0].WritePolisher);
}
