public void CellWithStringArray()
{
var row = Utils.GetRow(0, new object[] { "1", "[email protected];[email protected]", "John" });
var options = new SheetMappingOptions
{
Map = new string[] { "id", "emails", "people" }, SheetName = "order"
};
var strategy = JsonNamingStrategy.SnakeCase;
var request = new ExcelRowParseOnTypeRequest
{
NamingStrategy = strategy, Options = options, RootType = typeof(Order), Row = row
};
var excludeCells = new int[] { 0 };
var svc = new ExcelRowTypeParser(null);
var result = svc.ParseCells(request, excludeCells);
Assert.AreEqual(0, result.InvalidCells.Length);
Assert.IsNotNull(result.RowMapped);
var obj = JsonConvert.DeserializeObject <Order>(result.RowMapped, JsonUtil.GetSettings(strategy));
Assert.AreEqual("*****@*****.**", obj.Emails[0]);
Assert.AreEqual("*****@*****.**", obj.Emails[1]);
Assert.AreEqual("John", obj.People[0]);
}
public void OnLeafBasic()
{
var row = Utils.GetRow(2, new object[] { "1", "item1", "US", 1d });
var options = new SheetMappingOptions
{
Map = new string[] { "OrderId", "name", "country_code", "quantity" }, SheetName = "items"
};
var strategy = JsonNamingStrategy.SnakeCase;
var request = new ExcelRowParseOnTypeRequest
{
NamingStrategy = strategy, Options = options, RootType = typeof(Order), Row = row
};
var excludeCells = new int[] { 0 };
var svc = new ExcelRowTypeParser(null);
var result = svc.ParseCells(request, excludeCells);
Assert.AreEqual(0, result.InvalidCells.Length);
Assert.IsNotNull(result.RowMapped);
var obj = JsonConvert.DeserializeObject <Item>(result.RowMapped, JsonUtil.GetSettings(strategy));
Assert.AreEqual("item1", obj.Name);
Assert.AreEqual("US", obj.CountryCode);
Assert.AreEqual(1, obj.Quantity);
}
public void OnRootBasic()
{
var row = Utils.GetRow(0, new object[] { "1", "101", "the user number", "kg", 8, "2020-05-25T05:44:12.251Z" });
var options = new SheetMappingOptions
{
Map = new string[] { "id", "number", "user_number", "weight.unit", "weight.value", "order_date" }, SheetName = "order"
};
var strategy = JsonNamingStrategy.SnakeCase;
var request = new ExcelRowParseOnTypeRequest
{
NamingStrategy = strategy, Options = options, RootType = typeof(Order), Row = row
};
var excludeCells = new int[] { 0 };
var svc = new ExcelRowTypeParser(null);
var result = svc.ParseCells(request, excludeCells);
Assert.AreEqual(0, result.InvalidCells.Length);
Assert.IsNotNull(result.RowMapped);
var obj = JsonConvert.DeserializeObject <Order>(result.RowMapped, JsonUtil.GetSettings(strategy));
Assert.AreEqual("101", obj.Number);
Assert.AreEqual("the user number", obj.UserNumber);
Assert.IsNotNull(obj.Weight);
Assert.AreEqual("kg", obj.Weight.Unit);
Assert.AreEqual(8m, obj.Weight.Value);
}
public async Task RelatedBasic()
{
var sheet = Utils.GetSheet(2, "items",
new object[] { "number", "name", "country_code", "quantity" });
var request = new SheetOnTypeParseRequest
{
MappingOptions = SheetMappingOptions.Default(sheet.Index),
NamingStrategy = JsonNamingStrategy.SnakeCase,
RootType = typeof(Order),
Sheet = sheet
};
var svc = new ExcelSheetOnTypeValidator();
var result = await svc.Validate(request);
Assert.AreEqual(false, result.HasErrors);
Assert.AreEqual(true, result.IgnoreFirstRow);
Assert.AreEqual(0, result.InvalidHeaders.Length);
Assert.AreEqual(false, result.InvalidName);
var map = sheet.Header.Select(c => c.Value.ToString()).ToArray();
for (int i = 0; i < map.Length; i++)
{
Assert.AreEqual(map[i], result.Map[i]);
}
}
public virtual IdParseResult ParseId(IExcelRow row, SheetMappingOptions options)
{
var result = new IdParseResult
{
InvalidForeignIdValue = false,
InvalidIdValue = false
};
var id = 0;
var excludeColumns = new List <int>();
if (options.IdIndex.HasValue)
{
excludeColumns.Add(options.IdIndex.Value);
var val = row.Cells[options.IdIndex.Value].Value?.ToString();
if (int.TryParse(val, out id))
{
result.UserDefinedId = id;
}
else
{
result.InvalidIdValue = true;
}
}
if (options.IndexAsId)
{
result.UserDefinedId = row.Index;
result.InvalidIdValue = false;
}
if (options.ParentIdIndex.HasValue)
{
excludeColumns.Add(options.ParentIdIndex.Value);
var val = row.Cells[options.ParentIdIndex.Value].Value?.ToString();
if (int.TryParse(val, out id))
{
result.ForeignUserDefinedId = id;
}
else
{
result.InvalidForeignIdValue = true;
}
}
result.ExcludeColumns = excludeColumns.ToArray();
return(result);
}
public void InvalidBasic()
{
var options = new SheetMappingOptions {
IdIndex = 0
};
var svc = new ExcelRowTypeParser(null);
var row = Utils.GetRow(1, new object[] { "invalid value", 5d, DateTime.Now });
var result = svc.ParseId(row, options);
Assert.AreEqual(true, result.InvalidIdValue);
Assert.AreEqual(false, result.InvalidForeignIdValue);
Assert.IsNull(result.ForeignUserDefinedId);
Assert.IsTrue(result.ExcludeColumns.Length == 1);
Assert.IsTrue(result.ExcludeColumns[0] == 0);
}
public void OnRootInvalidBasic()
{
var row = Utils.GetRow(0, new object[] { "1", "101", "the user number", "kg", "invalid" });
var options = new SheetMappingOptions
{
Map = new string[] { "id", "number", "user_number", "weight.unit", "weight.value" }, SheetName = "order"
};
var strategy = JsonNamingStrategy.SnakeCase;
var request = new ExcelRowParseOnTypeRequest
{
NamingStrategy = strategy, Options = options, RootType = typeof(Order), Row = row
};
var excludeCells = new int[] { 0 };
var svc = new ExcelRowTypeParser(null);
var result = svc.ParseCells(request, excludeCells);
Assert.AreEqual(1, result.InvalidCells.Length);
Assert.IsNull(result.RowMapped);
Assert.AreEqual(4, result.InvalidCells[0]);
}
public async Task <ISheetContainer> Load(string fileUrl, ICollection <SheetMappingOptions> sheetsOptions)
{
url = fileUrl;
tempLocalFilePath = await CopyFileLocal(fileUrl);
await using var tempStream = File.Open(tempLocalFilePath, FileMode.Open, FileAccess.Read);
using var tmpReader = ExcelReaderFactory.CreateReader(tempStream);
if (tmpReader.ResultsCount == 0)
{
return(null);
}
var sheets = new ISheet[tmpReader.ResultsCount];
var result = new SheetContainer(fileUrl, sheets);
for (var i = 0; i < sheets.Length; i++)
{
var ignoreHeader = true;
var options = sheetsOptions?.FirstOrDefault(o => o.SheetIndex == i) ?? SheetMappingOptions.Default(i);
if (options.Map != null)
{
ignoreHeader = false;
}
tmpReader.Read();
var sheet = new Sheet(tmpReader, result, i, ignoreHeader);
result.Sheets[i] = sheet;
tmpReader.NextResult();
}
Container = result;
CurrentSheet = Container.Sheets[0];
return(result);
}
private static (bool, string[]) GetMap(ISheet sheet, SheetMappingOptions options) => (options.Map == null, options.Map ?? sheet.Header.Select(x => x.Value?.ToString()).ToArray());
private static SheetMappingOptions GetOptions(ISheet sheet, IProcessOptions options) => options.SheetsOptions?.FirstOrDefault(o => o.SheetIndex == sheet.Index) ?? SheetMappingOptions.Default(sheet.Index);
