public void TestImport()
{
var sheet1 = new ImportSheet <ImportStudent>
{
UniqueValidationPrompt = "零花钱不可重复",
HeaderRowIndex = 0,
SheetIndex = 0,
ValidateHandler = (list) =>
{
foreach (ImportStudent model in list)
{
if (model.IsValidated && model.Name == "name0")
{
model.SetError(nameof(model.Name), "名字不可为0");
}
if (model.IsValidated && model.Money < 0.5)
{
model.SetError(nameof(model.Money), "零花钱不可小于0.5");
}
}
}
};
var sheet2 = new ImportSheet <ImportGrade>
{
HeaderRowIndex = 0,
SheetIndex = 1
};
var sheet3 = new ImportSheet <ImportSchool>
{
HeaderRowIndex = 1,
SheetIndex = 2,
ValidateHandler = (list) =>
{
foreach (var model in list)
{
if (model.Price > 0.5)
{
model.SetError(nameof(model.Price), "学费不可大于0.5");
}
}
}
};
var import = new DefaultExcelImporter();
using var inputStrem = new FileStream("D://export.xlsx", FileMode.OpenOrCreate, FileAccess.Read);
using var outStrem = new FileStream("D://error.xlsx", FileMode.Create, FileAccess.Write);
var bookmodel = new ImportBook();
bookmodel.SetSheetModels(sheet1, sheet2, sheet3);
var ret = import.ImportExcel(inputStrem, ExtEnum.XLSX, bookmodel, outStrem);
var success = ret.ImportSuccess;
var summaryErrorMsg = ret.GetSummaryErrorMessage();
var notDisplayMsg = ret.GetNotDisplayErrorMessage();
Output.WriteLine($"success:{success}");
Output.WriteLine("summaryErrorMsg------------");
Output.WriteLine(summaryErrorMsg);
Output.WriteLine("notDisplayMsg------------");
Output.WriteLine(notDisplayMsg);
}
public void TestImportSheet()
{
ImportConfig config = new ImportConfig();
ImportSheet sheet = config.GetSheet("stocktemplate");
Assert.IsNotNull(sheet);
}
private async Task ImportSheet(ImportSheet sheet)
{
var sheetHash = await _dataLoader.LoadSheetHash(sheet.SpreadsheetId, sheet.SheetName);
if (!HasSheetChanged(sheet, sheetHash))
{
_logger.LogInformation($"No changes found in sheet {sheet.SheetName}.");
return;
}
var repository = _sheetNameHelper.GetSheetRepositoryForSheetName(sheet.SheetName);
var sheetHashes = await _dataLoader.LoadHashes(sheet.SpreadsheetId, sheet.SheetName, sheet.Id);
var dbHashes = repository.ReadDbHashes(sheet);
// Compare hash list of sheet and DB to find rows that need to be deleted, inserted, updated
var hashListComparisonResult = _hashListComparator.CompareHashLists(sheetHashes, dbHashes);
var sheetIdHashes = sheetHashes.Select(rh => rh.IdHash).ToList();
// Delete
if (hashListComparisonResult.RowsToDelete.Any())
{
var deletedCount = repository.Delete(hashListComparisonResult.RowsToDelete);
_logger.LogInformation($"Deleted {deletedCount} entries for sheet {sheet.SheetName}.");
}
// Insert
if (hashListComparisonResult.RowsToInsert.Any())
{
var insertRowIndexes = GetRowIndexesForIdHashes(hashListComparisonResult.RowsToInsert.Select(rh => rh.IdHash), sheetIdHashes);
var dataToInsert = await _dataLoader.LoadRows(sheet.SpreadsheetId, sheet.SheetName, insertRowIndexes);
var dataToInsertForHashes = hashListComparisonResult.RowsToInsert
.Zip(dataToInsert, (hash, values) => new { hash, values })
.ToDictionary(x => x.hash, x => x.values);
var insertedCount = repository.Insert(dataToInsertForHashes);
_logger.LogInformation($"Inserted {insertedCount} entries for sheet {sheet.SheetName}.");
}
// Update
if (hashListComparisonResult.RowsToUpdate.Any())
{
var updateRowIndexes = GetRowIndexesForIdHashes(hashListComparisonResult.RowsToUpdate.Select(rh => rh.IdHash), sheetIdHashes);
var dataToUpdate = await _dataLoader.LoadRows(sheet.SpreadsheetId, sheet.SheetName, updateRowIndexes);
var dataToUpdateForHashes = hashListComparisonResult.RowsToUpdate
.Zip(dataToUpdate, (hash, values) => new { hash, values })
.ToDictionary(x => x.hash, x => x.values);
var updatedCount = repository.Update(dataToUpdateForHashes);
_logger.LogInformation($"Updated {updatedCount} entries for sheet {sheet.SheetName}.");
}
UpdateSheetHash(sheet.SpreadsheetId, sheet.SheetName, sheetHash);
}
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
Event.ConfigureForDatabase(builder);
Region.ConfigureForDatabase(builder);
LocationGroup.ConfigureForDatabase(builder);
Location.ConfigureForDatabase(builder);
BagCategory.ConfigureForDatabase(builder);
Item.ConfigureForDatabase(builder);
PlacedItem.ConfigureForDatabase(builder);
Currency.ConfigureForDatabase(builder);
CurrencyAmount.ConfigureForDatabase(builder);
ElementalType.ConfigureForDatabase(builder);
ElementalTypeRelation.ConfigureForDatabase(builder);
Ability.ConfigureForDatabase(builder);
PvpTier.ConfigureForDatabase(builder);
PokemonAvailability.ConfigureForDatabase(builder);
PokemonVarietyUrl.ConfigureForDatabase(builder);
Entities.PokemonSpecies.ConfigureForDatabase(builder);
PokemonVariety.ConfigureForDatabase(builder);
PokemonForm.ConfigureForDatabase(builder);
Evolution.ConfigureForDatabase(builder);
MoveDamageClass.ConfigureForDatabase(builder);
Move.ConfigureForDatabase(builder);
MoveTutor.ConfigureForDatabase(builder);
MoveTutorMove.ConfigureForDatabase(builder);
MoveTutorMovePrice.ConfigureForDatabase(builder);
MoveLearnMethod.ConfigureForDatabase(builder);
MoveLearnMethodLocation.ConfigureForDatabase(builder);
MoveLearnMethodLocationPrice.ConfigureForDatabase(builder);
LearnableMove.ConfigureForDatabase(builder);
LearnableMoveLearnMethod.ConfigureForDatabase(builder);
TimeOfDay.ConfigureForDatabase(builder);
Season.ConfigureForDatabase(builder);
SeasonTimeOfDay.ConfigureForDatabase(builder);
SpawnType.ConfigureForDatabase(builder);
Spawn.ConfigureForDatabase(builder);
SpawnOpportunity.ConfigureForDatabase(builder);
Nature.ConfigureForDatabase(builder);
HuntingConfiguration.ConfigureForDatabase(builder);
Build.ConfigureForDatabase(builder);
ItemOption.ConfigureForDatabase(builder);
MoveOption.ConfigureForDatabase(builder);
NatureOption.ConfigureForDatabase(builder);
ItemStatBoost.ConfigureForDatabase(builder);
Entities.ItemStatBoostPokemon.ConfigureForDatabase(builder);
ImportSheet.ConfigureForDatabase(builder);
}
public List <RowHash> ReadDbHashes(ImportSheet sheet)
{
var rowHashes = DbSet
.Where(x => x.ImportSheetId == sheet.Id)
.Select(x => new RowHash {
ContentHash = x.Hash, IdHash = x.IdHash, ImportSheetId = sheet.Id
})
.ToList();
return(rowHashes);
}
public ImportSheet GetSheet(string id)
{
ImportSheet targetSheet = new ImportSheet();
var target = _importSheet.Find(p => p.Id.Equals(id));
if (target != null)
{
targetSheet = target;
}
return(targetSheet);
}
private ImportSheet GetSheet(string spreadsheetId, string sheetName)
{
var sheet = _dbContext.ImportSheets
.SingleOrDefault(s =>
s.SpreadsheetId.Equals(spreadsheetId) &&
s.SheetName.Equals(sheetName));
if (sheet is null)
{
sheet = new ImportSheet
{
SpreadsheetId = spreadsheetId,
SheetName = sheetName,
SheetHash = "No successful import yet."
};
_dbContext.ImportSheets.Add(sheet);
_dbContext.SaveChanges();
}
return(sheet);
}
private bool HasSheetChanged(ImportSheet sheet, string sheetHash)
{
return(!string.Equals(sheet.SheetHash, sheetHash, StringComparison.Ordinal));
}
