public void ProcessOneSheetFile_HasCorrectData() { var source = TestSampleFiles.Sample_OneSheet; using (var ms = new MemoryStream(source)) using (var reader = ExcelReaderFactory.CreateReader(ms)) { var config = FluentConfig.ConfigureFor <OneSheetExcel>().WithTables(table => { table.Bind("My Sheet 1", m => m.FirstSheetRows).WithColumns(column => { column.Bind("Text Column", c => c.TextColumn); column.Bind("Some Int", c => c.IntColumn); column.Bind("Decimals", c => c.DecimalColumn); }); }); var result = reader.AsClass <OneSheetExcel>(config); var targetText = new List <string> { "Data 1", "Data 2", "Other Data" }; result.FirstSheetRows.Select(s => s.TextColumn).ShouldBe(targetText); var targetInts = new List <int> { 1, 2, 3 }; result.FirstSheetRows.Select(s => s.IntColumn).ShouldBe(targetInts); var targetDecimals = new List <decimal> { 1.5m, 3m, 4.5m }; result.FirstSheetRows.Select(s => s.DecimalColumn).ShouldBe(targetDecimals); } }
public void ProcessOneSheetNullableFile_HasCorrectNullableSequence() { var source = TestSampleFiles.Sample_OneSheet_Nullable_WithErrors; using (var ms = new MemoryStream(source)) using (var reader = ExcelReaderFactory.CreateReader(ms)) { var config = FluentConfig.ConfigureFor <OneSheetExcel>().WithTables(table => { table.Bind("My Sheet 1", m => m.FirstSheetRows).WithColumns(column => { column.Bind("Text Column", c => c.TextColumn); column.Bind("Some Int", c => c.IntColumn); column.Bind("Decimals", c => c.DecimalColumn); column.Bind("Nullable ints", c => c.NullableIntColumn); column.Bind("Nullable ints with err", c => c.NullableIntWithErrColumn); }); }); var result = reader.AsClass <OneSheetExcel>(out List <string> errors, config); var targetResult = new List <int?> { 1, null, 3, 4, 5, 6, 7 }; result.FirstSheetRows.Select(s => s.NullableIntWithErrColumn).ShouldBe(targetResult); errors.Count.ShouldBe(1); } }
public void ProcessOneSheetFile_HasCorrectEnums() { var source = TestSampleFiles.Sample_OneSheet; using (var ms = new MemoryStream(source)) using (var reader = ExcelReaderFactory.CreateReader(ms)) { var config = FluentConfig.ConfigureFor <OneSheetExcel>().WithTables(table => { table.Bind("My Sheet 1", m => m.FirstSheetRows).WithColumns(column => { column.Bind("Enums", c => c.EnumColumn); column.Bind("Str Enums", c => c.StringEnumColumn); }); }); var result = reader.AsClass <OneSheetExcel>(config); var targetEnums = new List <MyEnum> { MyEnum.Value1, MyEnum.Value1, MyEnum.OtherEnum }; result.FirstSheetRows.Select(s => s.EnumColumn).ShouldBe(targetEnums); var targetStringEnums = new List <MyEnum> { MyEnum.Value1, MyEnum.OtherEnum, MyEnum.Value1 }; result.FirstSheetRows.Select(s => s.StringEnumColumn).ShouldBe(targetStringEnums); } }
public void ProcessMultipleTablesPerSheet_CanReadAllRows() { var source = TestSampleFiles.Sample_ComplexMultipleSheets; using (var ms = new MemoryStream(source)) using (var reader = ExcelReaderFactory.CreateReader(ms)) { var config = FluentConfig.ConfigureFor <MultipleTablesExcel>().WithTables(table => { table.Bind("MultipleTables", m => m.FirstTableRows).StartingFromCell("B4").WithColumns(column => { column.Bind("Name", c => c.Name, isMandatory: true); column.Bind("Value", c => c.Value); }); table.Bind("MultipleTables", m => m.SecondTableRows).StartingFromCell("F6").WithColumns(column => { column.Bind("Title", c => c.Title, isMandatory: true); column.Bind("Amount", c => c.Amount); column.Bind("Count", c => c.Count); }); }); var result = reader.AsClass <MultipleTablesExcel>(config); result.FirstTableRows.Count.ShouldBe(3); result.SecondTableRows.Count.ShouldBe(4); } }
public void ProcessOneSheetNullableFile_HasCorrectNullableSequence() { var source = TestSampleFiles.Sample_OneSheet_Nullable; using (var ms = new MemoryStream(source)) using (var reader = ExcelReaderFactory.CreateReader(ms)) { var config = FluentConfig.ConfigureFor <OneSheetExcel>().WithTables(table => { table.Bind("My Sheet 1", m => m.FirstSheetRows).WithColumns(column => { column.Bind("Text Column", c => c.TextColumn); column.Bind("Some Int", c => c.IntColumn); column.Bind("Decimals", c => c.DecimalColumn); column.Bind("Nullable ints", c => c.NullableIntColumn); column.Bind("Nullable Enums", c => c.NullableEnumColumn); }); }); var result = reader.AsClass <OneSheetExcel>(config); var targetResult = new List <int?> { 1, 2, null, 4, 5, null, 7 }; result.FirstSheetRows.Select(s => s.NullableIntColumn).ShouldBe(targetResult); var targetEnumResult = new List <MyEnum?> { MyEnum.Val1, MyEnum.Val2, MyEnum.Val2, null, MyEnum.Val1, MyEnum.Val1, MyEnum.Val1 }; result.FirstSheetRows.Select(s => s.NullableEnumColumn).ShouldBe(targetEnumResult); } }
public void SheetWithSubRows_CanReadAllDataWithSubTables() { var source = TestSampleFiles.Sample_ComplexMultipleSheets; using (var ms = new MemoryStream(source)) using (var reader = ExcelReaderFactory.CreateReader(ms)) { var config = FluentConfig.ConfigureFor <ComplexExcel>().WithTables(table => { var columnCount = 0; object[] columns = null; table.Bind("SubData", m => m.SheetRows).WithColumns(column => { column.Bind("Id", c => c.Id); column.Bind("Name", c => c.Name); }).OnHeaderRead((header) => { columnCount = header.Length; columns = header; }).OnRowRead((myRow, data) => { var staticColumnCount = 2; myRow.YearlyData = new List <SubYearlyData>(); for (int i = staticColumnCount; i < columnCount; i++) { myRow.YearlyData.Add(new SubYearlyData { Year = Convert.ToInt32(columns[i]), Value = Convert.ToInt32(data[i]), }); } }); }); var result = reader.AsClass <ComplexExcel>(config); result.SheetRows.Select(s => s.Id) .ShouldBe(new List <int> { 1, 2, 3 }); result.SheetRows.Select(s => s.Name) .ShouldBe(new List <string> { "Aaaa", "Bbbb", "Cccc" }); result.SheetRows.First().YearlyData.Select(s => s.Year) .ShouldBe(new List <int> { 2017, 2018, 2019 }); result.SheetRows.First().YearlyData.Select(s => s.Value) .ShouldBe(new List <int> { 11, 22, 33 }); } }
public void ProcessMultipleTablesPerSheet_HasCorrectData() { var source = TestSampleFiles.Sample_ComplexMultipleSheets; using (var ms = new MemoryStream(source)) using (var reader = ExcelReaderFactory.CreateReader(ms)) { var config = FluentConfig.ConfigureFor <MultipleTablesExcel>().WithTables(table => { table.Bind("MultipleTables", m => m.FirstTableRows).StartingFromCell("B4").WithColumns(column => { column.Bind("Name", c => c.Name, isMandatory: true); column.Bind("Value", c => c.Value); }); table.Bind("MultipleTables", m => m.SecondTableRows).StartingFromCell("F6").WithColumns(column => { column.Bind("Title", c => c.Title, isMandatory: true); column.Bind("Amount", c => c.Amount); column.Bind("Count", c => c.Count); }); }); var result = reader.AsClass <MultipleTablesExcel>(config); var targetFirstTableNames = new List <string> { "Eee", "Fff", "Ggg" }; result.FirstTableRows.Select(s => s.Name).ShouldBe(targetFirstTableNames); var targetFirstTableValues = new List <int> { 13, 26, 75 }; result.FirstTableRows.Select(s => s.Value).ShouldBe(targetFirstTableValues); var targetSecondTableTitles = new List <string> { "Aaa", "Bbb", "Ccc", "Ddd" }; result.SecondTableRows.Select(s => s.Title).ShouldBe(targetSecondTableTitles); var targetSecondTableAmounts = new List <decimal> { 10.1m, 22.22m, 0.1m, 23m }; result.SecondTableRows.Select(s => s.Amount).ShouldBe(targetSecondTableAmounts); var targetSecondTableCounts = new List <int> { 4, 5, 6, 7 }; result.SecondTableRows.Select(s => s.Count).ShouldBe(targetSecondTableCounts); } }
public void ProcessOneSheetFileWithNestedClasses_HasCorrectData() { var source = TestSampleFiles.Sample_OneSheet_Nullable; using (var ms = new MemoryStream(source)) using (var reader = ExcelReaderFactory.CreateReader(ms)) { var config = FluentConfig.ConfigureFor <OneSheetExcel>().WithTables(table => { table.Bind("My Sheet 1", m => m.FirstSheetRows).WithColumns(column => { column.Bind("Text Column", c => c.TextColumn); column.Bind("Some Int", c => c.IntColumn); column.Bind("Decimals", c => c.Nested.DecimalColumn); column.Bind("Nullable ints", c => c.Nested.NullableIntColumn); column.Bind("Nullable Enums", c => c.Nested.NullableEnumColumn); }); }); var result = reader.AsClass <OneSheetExcel>(config); var targetText = new List <string> { "Data 1", "Data 2", "Other Data", "Lorem", "Ipsum", "Dolor", "Hodor" }; result.FirstSheetRows.Select(s => s.TextColumn).ShouldBe(targetText); var targetInts = new List <int> { 1, 2, 3, 4, 5, 6, 7 }; result.FirstSheetRows.Select(s => s.IntColumn).ShouldBe(targetInts); var targetDecimals = new List <decimal> { 1.5m, 3m, 4.5m, 6m, 7.5m, 9m, 10.5m }; result.FirstSheetRows.Select(s => s.Nested.DecimalColumn).ShouldBe(targetDecimals); var targetResult = new List <int?> { 1, 2, null, 4, 5, null, 7 }; result.FirstSheetRows.Select(s => s.Nested.NullableIntColumn).ShouldBe(targetResult); var targetEnumResult = new List <MyEnum?> { MyEnum.Val1, MyEnum.Val2, MyEnum.Val2, null, MyEnum.Val1, MyEnum.Val1, MyEnum.Val1 }; result.FirstSheetRows.Select(s => s.Nested.NullableEnumColumn).ShouldBe(targetEnumResult); } }
static void TestingFluent() { var source = Resource1.Sample_OneSheet; using (var ms = new MemoryStream(source)) using (var reader = ExcelReaderFactory.CreateReader(ms)) { var config = FluentConfig.ConfigureFor <OneSheetExcel>().WithTables(table => { table.Bind("My Sheet 1", m => m.FirstSheetRows).WithColumns(column => { column.Bind("Text Column", c => c.TextColumn); column.Bind("Some Int", c => c.IntColumn); column.Bind("Decimals", c => c.DecimalColumn); }); }); var result = reader.AsClass <OneSheetExcel>(config); Console.WriteLine("Rows: " + result.FirstSheetRows.Count.ToString()); } }
public void ProcessOneSheetNullableFile_Has8Rows() { var source = TestSampleFiles.Sample_OneSheet_Nullable_WithErrors; using (var ms = new MemoryStream(source)) using (var reader = ExcelReaderFactory.CreateReader(ms)) { var config = FluentConfig.ConfigureFor <OneSheetExcel>().WithTables(table => { table.Bind("My Sheet 1", m => m.FirstSheetRows).WithColumns(column => { column.Bind("Text Column", c => c.TextColumn); column.Bind("Some Int", c => c.IntColumn); column.Bind("Decimals", c => c.DecimalColumn); column.Bind("Nullable ints", c => c.NullableIntColumn); column.Bind("Nullable ints with err", c => c.NullableIntWithErrColumn); }); }); var result = reader.AsClass <OneSheetExcel>(config); result.FirstSheetRows.Count.ShouldBe(7); } }
public void ProcessOneSheetFile_CanReadFormattedDates() { var source = TestSampleFiles.Sample_OneSheet_Dates; using (var ms = new MemoryStream(source)) using (var reader = ExcelReaderFactory.CreateReader(ms)) { var config = FluentConfig.ConfigureFor <OneSheetExcel>().WithTables(table => { table.Bind("My Sheet 1", m => m.FirstSheetRows).WithColumns(column => { column.Bind("Formatted Date", c => c.FormattedDate); }); }); var result = reader.AsClass <OneSheetExcel>(config); var targetFormattedDates = new List <DateTime> { new DateTime(2018, 01, 13), new DateTime(2018, 02, 13), new DateTime(2018, 03, 13) }; result.FirstSheetRows.Select(s => s.FormattedDate).ShouldBe(targetFormattedDates); } }
public void ProcessOneSheetFile_CanReadFromDateHeaderColumn() { var source = TestSampleFiles.Sample_OneSheet_Dates; using (var ms = new MemoryStream(source)) using (var reader = ExcelReaderFactory.CreateReader(ms)) { var config = FluentConfig.ConfigureFor <OneSheetExcel>().WithTables(table => { table.Bind("My Sheet 1", m => m.FirstSheetRows).WithColumns(column => { column.Bind(new DateTime(2018, 08, 13).ToString(), c => c.StringValuesWithDateHeader); }); }); var result = reader.AsClass <OneSheetExcel>(config); var targetDates = new List <string> { "Date header", "Some value", "Othe value" }; result.FirstSheetRows.Select(s => s.StringValuesWithDateHeader).ShouldBe(targetDates); } }
public void ProcessOneSheetFile_CanReadAllRows() { var source = TestSampleFiles.Sample_OneSheet_Dates; using (var ms = new MemoryStream(source)) using (var reader = ExcelReaderFactory.CreateReader(ms)) { var config = FluentConfig.ConfigureFor <OneSheetExcel>().WithTables(table => { table.Bind("My Sheet 1", m => m.FirstSheetRows).WithColumns(column => { column.Bind("Date Column", c => c.NormalDate); column.Bind("Nullable Date", c => c.NullableDate); column.Bind("Formatted Date", c => c.FormattedDate); column.Bind("Formatted Nullable Date", c => c.FormattedDate); column.Bind("13.08.2018", c => c.StringValuesWithDateHeader); }); }); var result = reader.AsClass <OneSheetExcel>(config); result.FirstSheetRows.Count.ShouldBe(3); } }