public void GivenSheet_WhenMappingDecimalValueBiggerThanMaxValue_ItSetsValidationError()
{
var columnName = "Decimal";
var sheetData = new SheetBuilder()
.AddHeaders(columnName)
.AddRow(r => r
.AddCell(c => c.WithColumnIndex(0).WithRowIndex(0).WithValue(5.012m).Build())
.Build(0))
.Build();
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader(columnName)
.WithMaximum(5.011m)
.IsRequired()
.MapTo(t => t.DecimalProperty)))
.Map <TestModel>(sheetData);
result.ValidationErrors.Should().HaveCount(1);
result.ValidationErrors.Single().PropertyName.Should().Be("DecimalProperty");
result.ValidationErrors.Single().CellValue.Should().Be(string.Empty);
result.ValidationErrors.Single().ColumnName.Should().Be(columnName);
}
public async Task <List <JobOffer> > GetJobOffers()
{
var provider = new SheetToObjects.Adapters.GoogleSheets.ProtectedGoogleSheetAdapter();
var filePath = _secretService.GetSecretFilePath();
var sheetData = await provider.GetAsync(filePath,
"Job offers",
"1MlqUNhlcBEjx5V8IEa1MzJhQv9Bj-1eUECat1L8fgqM",
"A:G")
.ConfigureAwait(false);
var sheetMapper = new SheetMapper().AddConfigFor <JobOffer>(cfg => cfg
.MapColumn(column => column.WithHeader("Title").MapTo(m => m.Title))
.MapColumn(column => column.WithHeader("Introduction").MapTo(m => m.Introduction))
.MapColumn(column => column.WithHeader("Text").MapTo(m => m.Text))
.MapColumn(column => column.WithHeader("Tags").MapTo(m => m.Tags))
.MapColumn(column => column.WithHeader("Color").MapTo(m => m.Color))
.MapColumn(column => column.WithHeader("Date").MapTo(m => m.Date))
.MapColumn(column => column.WithHeader("Published")
.WithDefaultValue <bool>(true)
.MapTo(m => m.Published)));
var data = sheetMapper.Map <JobOffer>(sheetData);
if (data.IsSuccess)
{
return(data.ParsedModels
.ToList()
.Select(x => x.ParsedModel)
.ToList());
}
return(null);
}
public void GivenSheetMapper_WhenCreatingTwoConfigs_TheyBothExist()
{
var sheetDataModelOne = new SheetBuilder()
.AddHeaders("ModelOnePropertyOne")
.AddRow(r => r
.AddCell(c => c.WithColumnIndex(0).WithRowIndex(0).WithValue("SomeValue").Build())
.Build(1))
.Build();
var sheetDataModelTwo = new SheetBuilder()
.AddHeaders("ModelTwoPropertyOne")
.AddRow(r => r
.AddCell(c => c.WithColumnIndex(0).WithRowIndex(0).WithValue("1").Build())
.Build(1))
.Build();
var sheetMapper = new SheetMapper()
.AddConfigFor <ModelOne>(cfg => cfg
.HasHeaders()
.MapColumn(column => column.WithHeader("ModelOnePropertyOne").IsRequired().MapTo(t => t.ModelOnePropertyOne)))
.AddConfigFor <ModelTwo>(cfg => cfg
.HasHeaders()
.MapColumn(column => column.IsRequired().MapTo(t => t.ModelTwoPropertyOne)));
var resultModelOne = sheetMapper.Map <ModelOne>(sheetDataModelOne);
var resultModelTwo = sheetMapper.Map <ModelTwo>(sheetDataModelTwo);
resultModelOne.IsSuccess.Should().BeTrue();
resultModelOne.ParsedModels.Single().Value.ModelOnePropertyOne.Should().Be("SomeValue");
resultModelTwo.IsSuccess.Should().BeTrue();
resultModelTwo.ParsedModels.Single().Value.ModelTwoPropertyOne.Should().Be(1);
}
public void GivenSheet_WhenCustomEnumParserIsProvided_ValueIsConvertedToEnum()
{
var sheetData = new SheetBuilder()
.AddHeaders("EnumProperty")
.AddRow(r => r.AddCell(c => c.WithColumnIndex(0).WithRowIndex(1).WithValue("Second").Build()).Build(1))
.Build();
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader("EnumProperty")
.IsRequired()
.ParseValueUsing(x =>
{
switch (x)
{
case "First": return(EnumModel.First);
case "Second": return(EnumModel.Second);
case "Third": return(EnumModel.Third);
default: return(EnumModel.Default);
}
})
.MapTo(t => t.EnumProperty)))
.Map <TestModel>(sheetData);
result.IsSuccess.Should().BeTrue();
result.ParsedModels.Should().HaveCount(1);
result.ParsedModels.First().Value.EnumProperty.Should().Be(EnumModel.Second);
}
/// <summary>Adds services required for using SheetToObjects by scanning the given assemblies for SheetToObjectConfig files</summary>
/// <param name="services">The <see cref="T:Microsoft.Extensions.DependencyInjection.IServiceCollection" /> to add the services to.</param>
/// <param name="assemblies"></param>
/// <returns>The <see cref="T:Microsoft.Extensions.DependencyInjection.IServiceCollection" /> so that additional calls can be chained.</returns>
public static IServiceCollection AddSheetToObjects(this IServiceCollection services, Assembly[] assemblies)
{
if (services == null)
{
throw new ArgumentNullException(nameof(services));
}
if (assemblies == null || !assemblies.Any())
{
throw new ArgumentNullException(nameof(assemblies));
}
var sheetToObjectConfigTypes = GetTypesFromAssemblies <SheetToObjectConfig>(assemblies);
var sheetToObjectConfigs = CreateInstances <SheetToObjectConfig>(sheetToObjectConfigTypes).ToList();
var sheetMapper = new SheetMapper();
foreach (var config in sheetToObjectConfigs)
{
sheetMapper.AddSheetToObjectConfig(config);
}
services.TryAdd(ServiceDescriptor.Singleton <IMapSheetToObjects>(sheetMapper));
return(services);
}
public ReadSCFile(IMapper mapper, IAppRepository <string> appRepository)
{
exSheetMapper = new SheetMapper()
.AddSheetToObjectConfig(new SlickConfig());
excelSheetAdapter = new SheetProvider();
slickChartFirmNames = new List <SlickChartFirmNames>();
this.mapper = mapper;
this.appRepository = appRepository;
}
public void GivenSheet_WhenMappingModelEnumProperty_ItSetsPropertyOnModel()
{
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column.WithHeader("Enumeration").IsRequired().MapTo(t => t.EnumProperty)))
.Map <TestModel>(_sheetData);
result.ParsedModels.Should().HaveCount(1);
result.ParsedModels.Single().Value.EnumProperty.Should().Be(_enumValue);
}
public void GivenSheetMap_WhenColumnsAreProperlyConfigured_ModelCanBeMappedToSheet()
{
var sheetData = new SheetBuilder()
.AddHeaders("Weight")
.AddRow(r => r
.AddCell(c => c.WithColumnIndex(0).WithRowIndex(1).WithValue(80.5.ToString(CultureInfo.InvariantCulture)).Build()).Build(0))
.Build();
var result = new SheetMapper()
.AddSheetToObjectConfig(new TestConfig())
.Map <Test>(sheetData);
result.IsSuccess.Should().BeTrue();
}
public void GivenSheet_WhenMappingModelDateTimeProperty_ItSetsPropertyOnModel()
{
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader("DateTime")
.UsingFormat("yyyy-MM-dd")
.IsRequired()
.MapTo(t => t.DateTimeProperty)))
.Map <TestModel>(_sheetData);
result.ParsedModels.Should().HaveCount(1);
result.ParsedModels.Single().Value.DateTimeProperty.Should().Be(_dateTimeValue);
}
public void GivenSheet_WhenMappingRequiredModelPropertyToInvalidType_ItShouldSetDefaultValue()
{
var columnName = "String";
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader(columnName)
.IsRequired()
.MapTo(t => t.DoubleProperty)))
.Map <TestModel>(_sheetData);
result.IsFailure.Should().BeTrue();
result.ParsedModels.Should().HaveCount(0);
result.ValidationErrors.Single().ColumnName.Should().Be(columnName);
}
public void GivenSheet_WhenMappingModelStringPropertyWithRegexValidation_ShouldThrowValidationError()
{
var columnName = "String";
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader(columnName)
.Matches("some_invalid_pattern")
.MapTo(t => t.StringProperty)))
.Map <TestModel>(_sheetData);
result.IsFailure.Should().BeTrue();
result.ParsedModels.Should().HaveCount(0);
result.ValidationErrors.Single().ColumnName.Should().Be(columnName);
}
public void GivenColumnWithUniqueValidation_WhenOneValueExistsTwice_ItIsNotValid()
{
var sheetData = new SheetBuilder()
.AddHeaders("FirstColumn")
.AddRow(r => r.AddCell(c => c.WithColumnIndex(0).WithRowIndex(1).WithValue("Same Value").Build()).Build(1))
.AddRow(r => r.AddCell(c => c.WithColumnIndex(0).WithRowIndex(2).WithValue("Same Value").Build()).Build(2))
.Build();
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader("FirstColumn")
.ShouldHaveUniqueValues()
.MapTo(t => t.StringProperty)))
.Map <TestModel>(sheetData);
result.IsSuccess.Should().BeFalse();
result.ParsedModels.Should().HaveCount(2);
}
public void GivenValidatingCustomRule_WhenIntIsThreeAndShouldBeBetweenOneAndFive_ItIsValid()
{
var sheetData = new SheetBuilder()
.AddHeaders("Column1")
.AddRow(r => r.AddCell(c => c.WithColumnIndex(0).WithRowIndex(1).WithValue(3).Build()).Build(1))
.Build();
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader("Column1")
.IsRequired()
.WithCustomRule <int>(x => x >= 3 && x <= 5, "Value should be between 3 and 5")
.MapTo(t => t.IntProperty)))
.Map <TestModel>(sheetData);
result.IsSuccess.Should().BeTrue();
result.ValidationErrors.Should().BeEmpty();
}
public void GivenSheet_WhenMappingRequiredPropertyThatIsSet_ItSetsNoValidationError()
{
var sheetData = new SheetBuilder()
.AddHeaders("Column1")
.AddRow(r => r
.AddCell(c => c.WithColumnIndex(0).WithRowIndex(0).WithValue("SomeValue").Build())
.Build(0))
.Build();
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader("Column1")
.IsRequired()
.MapTo(t => t.StringProperty)))
.Map <TestModel>(sheetData);
result.ValidationErrors.Should().BeEmpty();
}
public void GivenSheet_WhenCustomEnumParserFails_ResultContainsFailure()
{
var sheetData = new SheetBuilder()
.AddHeaders("StringProperty")
.AddRow(r => r.AddCell(c => c.WithColumnIndex(0).WithRowIndex(1).WithValue("MyValue").Build()).Build(1))
.Build();
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader("StringProperty")
.ParseValueUsing(x => throw new Exception("An exception occured"))
.MapTo(t => t.StringProperty)))
.Map <TestModel>(sheetData);
result.IsSuccess.Should().BeFalse();
result.ValidationErrors.Should().HaveCount(1);
result.ValidationErrors.First().ErrorMessage.Should().Be("Could not parse value");
}
public void GivenValidatingWithIsRequiredInHeaderRow_WhenColumnIsInHeader_ItIsValid()
{
var sheetData = new SheetBuilder()
.AddHeaders("Column1")
.AddRow(r => r.AddCell(c => c.WithColumnIndex(0).WithRowIndex(1).WithValue(4).Build()).Build(1))
.AddRow(r => r.AddCell(c => c.WithColumnIndex(0).WithRowIndex(2).WithValue(12).Build()).Build(2))
.Build();
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader("Column1")
.IsRequired()
.WithRequiredInHeaderRow()
.MapTo(t => t.IntProperty)))
.Map <TestModel>(sheetData);
result.IsSuccess.Should().BeTrue();
result.ValidationErrors.Should().HaveCount(0);
}
public void GivenParsingShouldBeStoppedOnFirstEmptyRow_WhenSecondRowIsEmptyAndThirdIsNotEmpty_OnlyRowOneIsParsed()
{
var sheetData = new SheetBuilder()
.AddHeaders("FirstColumn")
.AddRow(r => r.AddCell(c => c.WithColumnIndex(0).WithRowIndex(1).WithValue("Some Value").Build()).Build(1))
.AddRow(r => r.AddCell(c => c.WithColumnIndex(0).WithRowIndex(2).Build()).Build(2))
.AddRow(r => r.AddCell(c => c.WithColumnIndex(0).WithRowIndex(3).WithValue("Foo").Build()).Build(3))
.Build();
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader("FirstColumn")
.MapTo(t => t.StringProperty))
.StopParsingAtFirstEmptyRow())
.Map <TestModel>(sheetData);
result.IsSuccess.Should().BeTrue();
result.ParsedModels.Should().HaveCount(1);
}
public void GivenSheet_WhenCustomBooleanParserIsProvided_ValueIsConvertedToBoolean()
{
var sheetData = new SheetBuilder()
.AddHeaders("Bool")
.AddRow(r => r.AddCell(c => c.WithColumnIndex(0).WithRowIndex(1).WithValue("1").Build()).Build(1))
.Build();
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader("Bool")
.IsRequired()
.ParseValueUsing(x => x.Equals("1") ? true : false)
.MapTo(t => t.BoolProperty)))
.Map <TestModel>(sheetData);
result.IsSuccess.Should().BeTrue();
result.ParsedModels.Should().HaveCount(1);
result.ParsedModels.First().Value.BoolProperty.Should().BeTrue();
}
public void GivenSheet_WhenMappingStringPropertyThatDoesNotSatisfyRegex_ItSetsValidationError()
{
var columnName = "String";
var sheetData = new SheetBuilder()
.AddHeaders(columnName)
.AddRow(r => r
.AddCell(c => c
.WithColumnIndex(0)
.WithRowIndex(0)
.WithValue("import1@mail")
.Build())
.Build(0))
.Build();
var result = new SheetMapper()
.Map <TestModel>(sheetData);
result.ValidationErrors.Should().HaveCount(1);
result.ValidationErrors.Single().PropertyName.Should().Be("StringRegexProperty");
result.ValidationErrors.Single().CellValue.Should().Be(string.Empty);
}
public void GivenSheet_WhenMappingEmptyStringWhichIsNotRequired_ItShouldSetDefaultValue()
{
var sheetData = new SheetBuilder()
.AddHeaders("Label")
.AddRow(r => r
.AddCell(c => c.WithColumnIndex(0).WithRowIndex(1).WithValue("").Build())
.Build(1))
.Build();
var defaultLabel = "Common";
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader("Label")
.WithDefaultValue(defaultLabel)
.MapTo(t => t.StringProperty)))
.Map <TestModel>(sheetData);
result.IsSuccess.Should().BeTrue();
result.ParsedModels.Single().Value.StringProperty.Should().Be(defaultLabel);
}
public void GivenSheet_WhenMappingDoubleValueBiggerThanMinValue_ItSetsNoValidationError()
{
var columnName = "Double";
var sheetData = new SheetBuilder()
.AddHeaders(columnName)
.AddRow(r => r
.AddCell(c => c.WithColumnIndex(0).WithRowIndex(0).WithValue(5.0D).Build())
.Build(0))
.Build();
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader(columnName)
.WithMinimum(4D)
.IsRequired()
.MapTo(t => t.DoubleProperty)))
.Map <TestModel>(sheetData);
result.ValidationErrors.Should().BeEmpty();
}
public void GivenSheet_WhenMappingRequiredPropertyThatIsEmpty_ItSetsValidationError()
{
var columnName = "String";
var sheetData = new SheetBuilder()
.AddHeaders(columnName)
.AddRow(r => r
.AddCell(c => c.WithColumnIndex(4).WithRowIndex(1).WithValue(string.Empty).Build())
.Build(0))
.Build();
var result = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column.WithHeader(columnName).IsRequired().MapTo(t => t.StringProperty)))
.Map <TestModel>(sheetData);
result.ValidationErrors.Should().HaveCount(1);
result.ValidationErrors.Single().PropertyName.Should().Be("StringProperty");
result.ValidationErrors.Single().CellValue.Should().Be(string.Empty);
result.ValidationErrors.Single().ColumnIndex.Should().Be(0);
result.ValidationErrors.Single().RowIndex.Should().Be(0);
result.ValidationErrors.Single().ColumnName.Should().Be(columnName);
}
public void GivenASheet_WhenMappingRequiredModelPropertyToInvalidType_ShouldSetDefaultValue()
{
var columnName = "String";
var sheetData = new SheetBuilder()
.AddHeaders("Double")
.AddRow(r => r
.AddCell(c => c.WithColumnIndex(0).WithRowIndex(1).WithValue(5.3D.ToString()).Build())
.Build(0))
.Build();
var testModelList = new SheetMapper()
.AddConfigFor <TestModel>(cfg => cfg
.HasHeaders()
.MapColumn(column => column
.WithHeader(columnName)
.IsRequired()
.MapTo(t => t.DoubleProperty)))
.Map <TestModel>(sheetData);
testModelList.IsFailure.Should().BeTrue();
testModelList.ParsedModels.Should().HaveCount(0);
testModelList.ValidationErrors.Single().ColumnName.Should().Be(columnName);
}
private async void okBtn_Click(object sender, EventArgs e)
{
okBtn.Enabled = false;
SheetId = txtBoxSheetID.Text;
SheetMapper sheetMapper = new SheetMapper().AddConfigFor <TransactionHistory>(cfg
=> cfg.MapColumn(column => column.WithHeader("Crop").IsRequired().MapTo(t => t.Crop))
.MapColumn(
column => column.WithHeader("Farm Representative").IsRequired().MapTo(t => t.FarmRep))
.MapColumn(column => column.WithHeader("Amount Sold").IsRequired().MapTo(t => t.AmountSold))
.MapColumn(column => column.WithHeader("Income").IsRequired().MapTo(t => t.Income)))
.AddConfigFor <MonthlyHarvest>(cfg => cfg
.MapColumn(column => column.WithHeader("Crop").IsRequired().MapTo(m => m.Crop))
.MapColumn(column =>
column.WithHeader("Amount Harvested").IsRequired().MapTo(m => m.AmountHarvest))
.MapColumn(column => column.WithHeader("Farm Representative").IsRequired().MapTo(o => o.FarmRep)))
.AddConfigFor <FarmInventory>(cfg => cfg
.MapColumn(c => c.WithHeader("Crop").IsRequired().MapTo(o => o.Crop))
.MapColumn(c => c.WithHeader("Amount in Storage").IsRequired().MapTo(o => o.Amount))
.MapColumn(c => c.WithHeader("Time in Storage").IsRequired().MapTo(o => o.TimeInStorage))
.MapColumn(c => c.WithHeader("Farm Representative").IsRequired().MapTo(o => o.FarmRep)));
GoogleSheetAdapter adapter = new GoogleSheetAdapter();
try
{
Task <Sheet> transactionSheet = adapter.GetAsync(SheetId, "'Transaction History'!A1:ZZ",
"AIzaSyCkssJLOPN-8WdM3HX_8N3kdq62_9hn_wA");
Task <Sheet> harvestSheet = adapter.GetAsync(SheetId,
"'Monthly Harvest'!A1:ZZ", "AIzaSyCkssJLOPN-8WdM3HX_8N3kdq62_9hn_wA");
Task <Sheet> inventorySheet = adapter.GetAsync(SheetId,
"'Farm Inventory'!A1:ZZ", "AIzaSyCkssJLOPN-8WdM3HX_8N3kdq62_9hn_wA");
await Task.WhenAll(transactionSheet, harvestSheet, inventorySheet);
List <TransactionHistory> tranInfo = sheetMapper.Map <TransactionHistory>(transactionSheet.Result)
.ParsedModels
.Select(o => o.Value).OrderBy(o => o.Crop).ToList();
List <DateTime> transDates = transactionSheet.Result.Rows
.Where(o => DateTime.TryParse((string)o.Cells[0].Value, out _) && o.Cells.Count != 1)
.Select(o => DateTime.Parse((string)o.Cells[0].Value)).ToList();
List <MonthlyHarvest> monthlyHarvestsInfo = sheetMapper.Map <MonthlyHarvest>(harvestSheet.Result)
.ParsedModels
.Select(o => o.Value).ToList();
List <DateTime> monthDates = harvestSheet.Result.Rows
.Where(o => DateTime.TryParse((string)o.Cells[0].Value, out _) && o.Cells.Count != 1)
.Select(o => DateTime.Parse((string)o.Cells[0].Value)).ToList();
MainForm.MonthlyHarvests = Enumerable.Range(0, monthDates.Count)
.ToDictionary(i => monthDates[i], i => monthlyHarvestsInfo[i]);
MainForm.Transactions = Enumerable.Range(0, transDates.Count)
.ToDictionary(i => transDates[i], i => tranInfo[i]);
MainForm.FarmInventories = sheetMapper.Map <FarmInventory>(inventorySheet.Result)
.ParsedModels
.Select(o => o.Value).ToList();
}
catch
{
CustomMessageBox msBox = new CustomMessageBox("Error",
"Please enter a valid sheetID!", CustomMessageBoxStatus.Cross);
msBox.ShowDialog();
okBtn.Enabled = true;
return;
}
CustomMessageBox msSuccessBox = new CustomMessageBox("Success!",
"Connected to sheet", CustomMessageBoxStatus.Check);
msSuccessBox.ShowDialog();
okBtn.Enabled = true;
Close();
}
public SheetMapperWrapper(SheetMapper sheetMapper, Dictionary <Type, string> dictionary)
{
_sheetMapper = sheetMapper;
_dictionary = dictionary;
}
