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); }