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