Dictionary <string, Species> ImportMonsterSpecies(ExcelImporter excel, Dictionary <string, Element> elements, Dictionary <string, Ability> abilities)
{
if (!excel.TryGetTable("Monsters", out ExcelImporter.Table table))
{
throw new System.IO.InvalidDataException($"No Monsters table was found!");
}
var assets = GetAllAssetsOfType <Species>();
int originalCount = assets.Count;
int nonBlankCount = 0;
for (int row = 1; row <= table.RowCount; row++)
{
string name = table.GetValue <string>(row, "Name");
if (string.IsNullOrWhiteSpace(name))
{
continue;
}
nonBlankCount++;
var monster = GetOrCreateAsset(name, assets, "Monsters/Species");
string rarityName = table.GetValue <string>(row, "Rarity");
if (!System.Enum.TryParse(rarityName, out monster.rarity))
{
Debug.LogError($"Unknown rarity {rarityName} used by monster species {name}.");
}
string elementName = table.GetValue <string>(row, "Element");
if (!elements.TryGetValue(elementName, out monster.element))
{
Debug.LogError($"Element {elementName} used by monster {name} was not found.");
}
monster.baseStats.health = table.GetValue <int>(row, "Health");
monster.baseStats.stamina = table.GetValue <int>(row, "Stamina");
monster.baseStats.attack = table.GetValue <int>(row, "Attack");
monster.baseStats.defense = table.GetValue <int>(row, "Defense");
monster.baseStats.luck = table.GetValue <int>(row, "Luck");
monster.abilities[0] = SetAbility(row, 1, table, abilities);
monster.abilities[1] = SetAbility(row, 2, table, abilities);
monster.OnValidate();
}
MarkChangesForSaving(speciesCollection);
speciesCollection.species.Clear();
foreach (var monster in assets.Values)
{
speciesCollection.species.Add(monster);
}
speciesCollection.OnEnable();
Debug.Log($"Successfully imported {nonBlankCount} monsters " +
$"({assets.Count - originalCount} new, {assets.Count - nonBlankCount} unused).");
return(assets);
}
/// <summary>
/// 生成模板
/// </summary>
/// <returns></returns>
public async Task <ExportFileInfo> GenerateTemplateFileAsync <T>(string filePath) where T : class, new()
{
FileUtil.DeleteFile(filePath);
var result = await ExcelImporter.GenerateTemplate <T>(filePath);
return(result);
}
public void ImportData_FileDoesntExist_Throws()
{
var type = TypeExcelFactory.EmptyClass;
var ex = Assert.Catch <FileNotFoundException>(() => ExcelImporter.ImportData("notexistedfile.xlsx", type, 0));
StringAssert.Contains("Could not find file", ex.Message);
}
public void ImportData_FileNameConsistsOfExtensionOnly_Throws()
{
var type = TypeExcelFactory.EmptyClass;
var ex = Assert.Catch <FileNotFoundException>(() => ExcelImporter.ImportData(".xlsx", type, 0));
StringAssert.Contains("Could not find file", ex.Message);
}
public void ImportData_FileNameIsWhitespaces_Throws()
{
var type = TypeExcelFactory.EmptyClass;
var ex = Assert.Catch <ArgumentException>(() => ExcelImporter.ImportData(" ", type, 0));
StringAssert.Contains("The path is not of a legal form.", ex.Message);
}
public void ImportData_FileNameIsEmptyString_Throws()
{
var type = TypeExcelFactory.EmptyClass;
var ex = Assert.Catch <ArgumentException>(() => ExcelImporter.ImportData(String.Empty, type, 0));
StringAssert.Contains("Empty path name is not legal.", ex.Message);
}
public void ImportData_TypeIsNull_Throws()
{
var stream = new MemoryStream(new byte[1]);
var ex = Assert.Catch <ArgumentNullException>(() => ExcelImporter.ImportData(stream, null, 0));
StringAssert.Contains("Type cannot be null.", ex.Message);
}
public override void ExecuteCommand(CancellationToken token)
{
var file = DialogManager.ShowSelectFileDialog();
if (file == null)
{
return;
}
var ok = new Action <string>((tableName) =>
{
ExcelImporter importer = new ExcelImporter();
DDLManager ddlManager = new DDLManager();
var meta = importer.GetMetaData(file.FullName);
ddlManager.CreateTable(tableName, meta);
importer.ImportToTable(file.FullName, tableName, meta);
ShellManager.AppendToEndOfSelection("SELECT * FROM " + tableName);
ShellManager.AppendToEndOfSelection("--DROP TABLE " + tableName);
});
DialogManager.GetDialogInputFromUser("Choose target table name (This table will be created)", "ExcelImport", ok);
}
private ActionResult Import(int start = 0)
{
Stopwatch sw = Stopwatch.StartNew();
try
{
// Seed VISIT records
OracleDbHelper.SeedVisitTable(db);
// Use ExcelImporter to import CP_ORDER table.
var excelImporter = new ExcelImporter(db);
excelImporter.Import(start);
// Use OracleDbHelper to import all other tables.
OracleDbHelper.PopulateDB <CP_VITAL_SIGN>(db);
OracleDbHelper.PopulateDB <CP_EXAM>(db);
OracleDbHelper.PopulateDB <CP_LAB_TEST>(db);
OracleDbHelper.PopulateDB <CP_MEDICAL_DOC>(db); // alone cost: 3.23801252166667 minutes.
//return ("Total cost: " + sw.Elapsed.TotalMinutes + "minutes. ");
return(RedirectToAction("Index"));
}
catch (Exception e)
{
return(RedirectToAction("Error", new{ message = e.Message }));
}
}
public async Task <byte[]> GenerateTemplate()
{
IImporter Importer = new ExcelImporter();
var content = await Importer.GenerateTemplateBytes <ArticleImportDto>();
return(content);
}
private static ImportExcelDataCommand CreateImportExcelDataCommand()
{
var excelDataImporter = new ExcelImporter();
var importExcelDataCommand = new ImportExcelDataCommand(excelDataImporter);
return(importExcelDataCommand);
}
public async Task MultipleSheetGenerateTemplate_Test()
{
var importer = new ExcelImporter();
var result = await importer.GenerateTemplateBytes <ImportClassStudentDto>();
var filePath = GetTestFilePath($"{nameof(MultipleSheetGenerateTemplate_Test)}.xlsx");
DeleteFile(filePath);
result.ShouldNotBeNull();
result.Length.ShouldBeGreaterThan(0);
result.ToExcelExportFileInfo(filePath);
File.Exists(filePath).ShouldBeTrue();
using (var pck = new ExcelPackage(new FileInfo(filePath)))
{
//检查转换结果
pck.Workbook.Worksheets.Count.ShouldBe(2);
#if NET461
pck.Workbook.Worksheets[1].Name.ShouldBe("1班导入数据");
pck.Workbook.Worksheets[2].Name.ShouldBe("2班导入数据");
#else
pck.Workbook.Worksheets[0].Name.ShouldBe("1班导入数据");
pck.Workbook.Worksheets[1].Name.ShouldBe("2班导入数据");
#endif
}
}
private void ImportResourceListAction_Execute(object sender, SimpleActionExecuteEventArgs e)
{
ExcelImporter ei = new ExcelImporter();
ei.ImportResourceList(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString,
@"C:\repositories\BadgES\Files\Badge_GDC Manila Resource List template v3.xlsx");
}
private void FormStatistics_Load(object sender, EventArgs e)
{
this.Location = this.Owner.Location;
this.Left += this.Owner.ClientSize.Width / 2 - this.Width / 2;
this.Top += this.Owner.ClientSize.Height / 2 - this.Height / 2;
var today = DateTime.Now;
datePickerStart.Value = new DateTime(today.Year, today.Month, 1);
datePickerEnd.Value = new DateTime(today.Year, today.Month, DateTime.DaysInMonth(today.Year, today.Month));
var startDate = datePickerStart.Value;
var endDate = datePickerEnd.Value;
var currentAccount = Account.Instance;
currentAccount.Clear();
var importer = new ExcelImporter();
var currentList = ImportFromFile.ImportData(importer);
foreach (var transaction in currentList)
{
currentAccount.AddTransaction(transaction);
}
labelTotalMoney.Text = string.Format("Your current money: {0}", currentAccount.Balance);
ChangeTextbox(datePickerStart.Value, datePickerEnd.Value, TransactionType.RegularIncome, StatisticsTextBox);
ChangeTextbox(datePickerStart.Value, datePickerEnd.Value, TransactionType.IrregularIncome, StatisticsTextBox);
ChangeTextbox(datePickerStart.Value, datePickerEnd.Value, TransactionType.RegularExpense, StatisticsTextBox);
ChangeTextbox(datePickerStart.Value, datePickerEnd.Value, TransactionType.IrregularExpense, StatisticsTextBox);
CheckIfEmpty(datePickerStart.Value, datePickerEnd.Value, "Transaction", StatisticsTextBox);
}
public static void Main(string[] args)
{
Console.WriteLine("Iniciando programa...");
string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); // obtiene la ruta del escritorio del usuario en sesion
Console.Write("Indique el nombre de archivo (Formato .xlsx) ubicado en su escritorio: ");
string fileName = Console.ReadLine();
if (fileName.Remove(0, fileName.Length - 4) == ".xlsx") // si es formato ".xlsx" entra
{
using (var stream = File.OpenRead(path + "/" + fileName))
using (var importer = new ExcelImporter(stream))
{
ExcelSheet sheet = importer.ReadSheet();
Event[] events = sheet.ReadRows <Event>().ToArray();
Console.WriteLine(events[0].Name); // Pub Quiz
Console.WriteLine(events[1].Name); // Live Music
Console.WriteLine(events[2].Name); // Live Football
}
}
else
{
Console.WriteLine("Solo archivos de formato .xlsx");
}
Console.ReadKey();
}
public void SetUp()
{
_fileStreamWrapperMock = new Mock <IFileStreamWrapper>();
_excelCreatorMock = new Mock <IExcelCreator>();
_excelFileMock = new Mock <IExcelFile>();
_excelImporter = new ExcelImporter <TestExcelObject>(_excelCreatorMock.Object, _fileStreamWrapperMock.Object);
}
public static IEnumerable <TEntity> Load <TEntity, TConfiguration>(byte[] content) where TConfiguration : ExcelClassMap <TEntity>, new()
{
IEnumerable <TEntity> entities;
try
{
using (var ms = new MemoryStream(content))
using (var importer = new ExcelImporter(ms))
{
var index = GetHeadingIndex(content);
if (index < 0)
{
throw new InvalidDataException("The file does not contain data-sheet data.");
}
importer.Configuration.RegisterClassMap <TConfiguration>();
var sheet = importer.ReadSheet();
sheet.HeadingIndex = index;
entities = sheet.ReadRows <TEntity>().ToList();
}
}
catch (Exception ex)
{
var message = $"Cannot load collection of {typeof(TEntity).Name} type from the give file stream.";
throw new ImportFormatException(message, ex);
}
return(entities);
}
private async void ImportExcelDataIntoDatabase()
{
try
{
var message = "The program will read into memory all rows from the spreadsheet, " +
"and then process saving them to the database.\n" +
"Ready to begin the import process?";
var dr = XtraMessageBox.Show(message, "Begin Import?", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dr == DialogResult.No)
{
return;
}
var importer = new ExcelImporter(gridViewExcelSource, MappingInfo, Persistent);
EventMediator.GetInstance().OnImportStarted();
EventMediator.GetInstance().ImportProgressUpdated += OnProgressUpdated;
marquee.Properties.Stopped = false;
await importer.Import();
message = "Data has been imported successfully!";
XtraMessageBox.Show(message, "Success!", MessageBoxButtons.OK, MessageBoxIcon.Information);
marquee.Properties.Stopped = true;
EventMediator.GetInstance().OnImportComplete();
}
catch (Exception ex)
{
ex.Report();
}
}
public void Imports_data_set_instances_and_annotations()
{
ExcelImporter importer = new ExcelImporter(new ExcelFactory().GetTestDataFolder());
var dataSet = importer.Import("BurningKnight");
var classes = dataSet.GetInstancesOfType(SnippetType.Class);
classes.Count.ShouldBe(79);
classes.Count(c => c.Annotations.Count == 3).ShouldBe(79);
var functions = dataSet.GetInstancesOfType(SnippetType.Function);
functions.Count.ShouldBe(313);
functions.Count(c => c.Annotations.Count == 1).ShouldBe(7);
functions.Count(c => c.Annotations.Count == 2).ShouldBe(10);
functions.Count(c => c.Annotations.Count == 3).ShouldBe(296);
var annotators = new List <Annotator>()
{
new Annotator(1, 6, 1),
new Annotator(2, 2, 2),
new Annotator(3, 2, 3)
};
JoinInstancesAndAnnotators(classes, annotators);
var allClassAnnotations = classes.SelectMany(c => c.Annotations);
allClassAnnotations.First(a => a.Annotator.Id == 1).Annotator.ShouldBe(annotators.Find(a => a.Id == 1));
allClassAnnotations.First(a => a.Annotator.Id == 2).Annotator.ShouldBe(annotators.Find(a => a.Id == 2));
allClassAnnotations.First(a => a.Annotator.Id == 3).Annotator.ShouldBe(annotators.Find(a => a.Id == 3));
}
public void BuildProductivities_IntegrationTest()
{
var service = GetFakeRaportService();
var sheetTable = ExcelImporter.GetSheetTable("test.xlsx".AppendAssemblyPath("Contexts\\IntegrationTests"));
TypeRepository.TryGetPropertyMap(sheetTable, typeof(TestImportModel), out var propertyMap);
var visitor = new TestImportModelVisitor(_operations);
var actions = ExcelImporter.GetDataFromTable(sheetTable, propertyMap, new ImportModelConverter <TestImportModel, EmployeeActionBase>(visitor));
var shortBreaks = new ShortBreakSchedule {
Duration = TimeSpan.FromMinutes(10),
FirstBreakTime = new TimeSpan(9, 55, 0),
Periodicity = TimeSpan.FromHours(2)
};
var shift = new Shift {
Lunch = TimeSpan.FromMinutes(30),
};
var productivities = service.Build(actions, shortBreaks, shift);
var employeeProductivity = new EmployeeProductivity(new Employee(), productivities);
var totalTime = employeeProductivity.GetTotalWorkHours();
Assert.That(totalTime, Is.GreaterThan(5.7));
var pause = employeeProductivity.DowntimePeriods.Sum(d => d.Duration.TotalSeconds);
Assert.That(pause, Is.GreaterThan(16000));
}
public void ImportData_TypeWithoutPublicParameterlessCtor_Throws()
{
var type = TypeExcelFactory.ClassWithParameterizedCtor;
var stream = StreamFactory.GetExcelMemoryStream();
var ex = Assert.Catch <TypeAccessException> (() => ExcelImporter.ImportData(stream, type, 0));
StringAssert.Contains("has no public parameterless constructor!", ex.Message);
}
public void ImportData_FileNameIsNull_Throws()
{
var type = TypeExcelFactory.EmptyClass;
string path = null;
var ex = Assert.Catch <ArgumentException> (() => ExcelImporter.ImportData(path, type, 0));
StringAssert.Contains("Path cannot be null.", ex.Message);
}
public void ImportData_SheetIndexIsNegative_Throws()
{
var type = TypeExcelFactory.EmptyClass;
var stream = new MemoryStream(new byte[1]);
var ex = Assert.Catch <ArgumentException>(() => ExcelImporter.ImportData(stream, type, -1));
StringAssert.Contains("Index of sheet cannot be less than zero.", ex.Message);
}
public ActionResult Index()
{
string excelPath = ConfigurationManager.AppSettings["exceppath"];
IList <SaleOrder> saleorderlist = ExcelImporter.ReadToList <SaleOrder>(excelPath, 2, 1);
return(View());
}
public void Ctor_Stream()
{
using (var stream = Helpers.GetResource("Primitives.xlsx"))
using (var importer = new ExcelImporter(stream))
{
Assert.Equal("Primitives", importer.ReadSheet().Name);
}
}
public void ImportData_StreamDoesNotContainExcelData_Throws()
{
var type = TypeExcelFactory.EmptyClass;
var stream = new MemoryStream(new byte[1]);
var ex = Assert.Catch <FileFormatException>(() => ExcelImporter.ImportData(stream, type, 0));
StringAssert.Contains("has invalid data format or has no sheetTable with 0 index.", ex.Message);
}
public void ExcelImport(ImportCheckResponse checkResponse)
{
using (IObjectRepository auditedTdb = DBContext.CreateAuditable(CheckPoint.Instance.UserName, CheckPoint.Instance.HostAddress))
{
ExcelImporter importer = new ExcelImporter(auditedTdb);
importer.Import(checkResponse);
}
}
private static async Task <IEnumerable <AdministrativeDistrict> > GetAdministrativeDistrictsAsync(string rootPath)
{
var file = Path.Combine(rootPath, "Baidu/administrative_districts_and_towns_202003.xlsx");
var importer = new ExcelImporter();
var result = await importer.Import <AdministrativeDistrict>(file);
return(result.Data);
}
Dictionary <string, Ability> ImportAbilities(ExcelImporter excel, Dictionary <string, Element> elements)
{
if (!excel.TryGetTable("Abilities", out ExcelImporter.Table table))
{
throw new System.IO.InvalidDataException($"No Abilities table was found!");
}
var specials = GetAllAssetsOfType <SpecialEffect>();
var assets = GetAllAssetsOfType <Ability>();
int originalCount = assets.Count;
int nonBlankCount = 0;
for (int row = 1; row <= table.RowCount; row++)
{
string name = table.GetValue <string>(row, "Name");
if (string.IsNullOrWhiteSpace(name))
{
continue;
}
nonBlankCount++;
var ability = GetOrCreateAsset(name, assets, "Abilities");
string elementName = table.GetValue <string>(row, "Element");
if (!elements.TryGetValue(elementName, out ability.element))
{
Debug.LogError($"Element {elementName} used by ability {name} was not found.");
}
ability.staminaCost = table.GetValue <int>(row, "Stamina Cost");
ability.minDamage = table.GetValue <int>(row, "Min Damage");
ability.maxDamage = table.GetValue <int>(row, "Max Damage");
string specialName = table.GetValue <string>(row, "Special Effect");
if (string.IsNullOrWhiteSpace(specialName) || !specials.TryGetValue(specialName, out SpecialEffect special))
{
if (!string.IsNullOrWhiteSpace(specialName))
{
Debug.LogError($"Special effect {specialName} used by ability {name} was not found.");
}
ability.special = null;
ability.specialChance = 0;
}
else
{
ability.special = special;
ability.specialChance = table.GetValue <float>(row, "Effect Chance");
}
}
Debug.Log($"Successfully imported {nonBlankCount} abilities " +
$"({assets.Count - originalCount} new, {assets.Count - nonBlankCount} unused).");
return(assets);
}
private void InitializeDependencis()
{
_addressBuilder = new AddressBuilder();
_personBuilder = new PersonBuilder();
_financialStateBuilder = new FinancialStateBuilder();
_agreementBuilder = new AgreementBuilder();
_agreementDao = new AgreementDao();
excelImporter = new ExcelImporter();
}
public void Test()
{
var models = GetModels();
var exporter = new ExcelExporter();
var bytes = exporter.ObjectToExcelBytes(models);
var path = GetFilePath("text.xls");
Console.WriteLine(path);
File.WriteAllBytes(path, bytes);
Assert.IsTrue(File.Exists(path));
var importer = new ExcelImporter();
var result = importer.ExcelToObject<ReportModel>(path);
Assert.AreEqual(models.Count, result.Count());
}
private static void ImportProducesFromExcel()
{
var db = new ChemicalsDbContext();
IZipExtractor zipExtractor = new ZipExtractor();
ExcelImporter<Produce> k = new ExcelImporter<Produce>(zipExtractor);
ICollection<Produce> produces = k.ImportModelsDataFromZipFile("../../../Files/Produces.zip", "./tests1");
foreach (var item in produces)
{
db.Produces.Add(item);
}
db.SaveChanges();
}
public void ImportInterviewResult()
{
if (!string.IsNullOrWhiteSpace(FileName))
{
using (var context = new VnrHrmDataContext())
{
IUnitOfWork unitOfWork = new UnitOfWork(context);
ExcelImporter importer = new ExcelImporter(context);
importer.FileName = FileName;
var objectType = typeof(Rec_ImportInterviewResultEntity);
List<ImportItemInfo> importTemplateItems = new List<ImportItemInfo>();
importTemplateItems.Add(CreateImportItemInfo(0, false, true, 1, "CandidateCode", string.Empty, FieldMappings["CandidateCode"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(1, true, true, 1, "CandidateName", string.Empty, FieldMappings["CandidateName"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(2, false, true, 1, "JobVacancyCode", string.Empty, FieldMappings["JobVacancyCode"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(3, true, true, 1, "CandidateEmail", string.Empty, FieldMappings["CandidateEmail"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(4, true, true, 1, "LanguageCode", string.Empty, FieldMappings["LanguageCode"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(5, true, true, 1, "Group1Score1", string.Empty, FieldMappings["Group1Score1"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(6, true, true, 1, "Group1Score2", string.Empty, FieldMappings["Group1Score2"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(7, true, true, 1, "Group1Score3", string.Empty, FieldMappings["Group1Score3"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(8, false, true, 1, "GroupCondition1", string.Empty, FieldMappings["GroupCondition1"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(9, false, true, 1, "GroupResult1", string.Empty, FieldMappings["GroupResult1"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(10, true, true, 1, "Group2Score1", string.Empty, FieldMappings["Group2Score1"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(11, true, true, 1, "Group2Score2", string.Empty, FieldMappings["Group2Score2"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(12, true, true, 1, "Group2Score3", string.Empty, FieldMappings["Group2Score3"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(13, false, true, 1, "GroupCondition2", string.Empty, FieldMappings["GroupCondition2"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(14, false, true, 1, "GroupResult2", string.Empty, FieldMappings["GroupResult2"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(15, true, true, 1, "Group3Score1", string.Empty, FieldMappings["Group3Score1"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(16, true, true, 1, "Group3Score2", string.Empty, FieldMappings["Group3Score2"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(17, true, true, 1, "Group3Score3", string.Empty, FieldMappings["Group3Score3"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(18, false, true, 1, "GroupCondition3", string.Empty, FieldMappings["GroupCondition3"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(19, false, true, 1, "GroupResult3", string.Empty, FieldMappings["GroupResult3"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(20, true, true, 1, "Group4Score1", string.Empty, FieldMappings["Group4Score1"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(21, true, true, 1, "Group4Score2", string.Empty, FieldMappings["Group4Score2"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(22, true, true, 1, "Group4Score3", string.Empty, FieldMappings["Group4Score3"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(23, false, true, 1, "GroupCondition4", string.Empty, FieldMappings["GroupCondition4"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(24, false, true, 1, "GroupResult4", string.Empty, FieldMappings["GroupResult4"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(25, false, true, 1, "GroupCondition5", string.Empty, FieldMappings["GroupCondition5"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(26, true, true, 1, "Group5Score1", string.Empty, FieldMappings["Group5Score1"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(27, true, true, 1, "Group5Score2", string.Empty, FieldMappings["Group5Score2"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(28, true, true, 1, "Group5Score3", string.Empty, FieldMappings["Group5Score3"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(29, false, true, 1, "GroupResult5", string.Empty, FieldMappings["GroupResult5"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(30, true, true, 1, "Group6Score1", string.Empty, FieldMappings["Group6Score1"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(31, true, true, 1, "Group6Score2", string.Empty, FieldMappings["Group6Score2"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(32, true, true, 1, "Group6Score3", string.Empty, FieldMappings["Group6Score3"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(33, false, true, 1, "GroupCondition6", string.Empty, FieldMappings["GroupCondition6"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(34, false, true, 1, "GroupResult6", string.Empty, FieldMappings["GroupResult6"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(35, true, true, 1, "Group7Score1", string.Empty, FieldMappings["Group7Score1"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(36, true, true, 1, "Group7Score2", string.Empty, FieldMappings["Group7Score2"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(37, true, true, 1, "Group7Score3", string.Empty, FieldMappings["Group7Score3"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(38, false, true, 1, "GroupCondition7", string.Empty, FieldMappings["GroupCondition7"], string.Empty));
importTemplateItems.Add(CreateImportItemInfo(39, false, true, 1, "GroupResult7", string.Empty, FieldMappings["GroupResult7"], string.Empty));
var dtImportObject = importer.ReadExcelData(objectType, 0, 4, 0, importTemplateItems);
listInvisibleField = importTemplateItems.Where(d => d.IsOutOfRange).Select(d => d.ExcelField1.TrimAll()).ToList();
var listJobVacancyCode = dtImportObject.Rows.OfType<DataRow>().Where(d =>
!d.IsNull(FieldMappings["JobVacancyCode"])).Select(d => d[FieldMappings["JobVacancyCode"]].GetString()).Distinct().ToList();
var listLanguageCode = dtImportObject.Rows.OfType<DataRow>().Where(d =>
!d.IsNull(FieldMappings["LanguageCode"])).Select(d => d[FieldMappings["LanguageCode"]].GetString()).Distinct().ToList();
var listCandidateCode = dtImportObject.Rows.OfType<DataRow>().Where(d =>
!d.IsNull(FieldMappings["CandidateCode"])).Select(d => d[FieldMappings["CandidateCode"]].GetString()).Distinct().ToList();
var listGroupConditionCode = dtImportObject.Rows.OfType<DataRow>().Where(d =>
!d.IsNull(FieldMappings["GroupCondition1"])).Select(d => d[FieldMappings["GroupCondition1"]].GetString()).Distinct().ToList();
listGroupConditionCode.AddRange(dtImportObject.Rows.OfType<DataRow>().Where(d =>
!d.IsNull(FieldMappings["GroupCondition2"])).Select(d => d[FieldMappings["GroupCondition2"]].GetString()).Distinct().ToList());
listGroupConditionCode.AddRange(dtImportObject.Rows.OfType<DataRow>().Where(d =>
!d.IsNull(FieldMappings["GroupCondition3"])).Select(d => d[FieldMappings["GroupCondition3"]].GetString()).Distinct().ToList());
listGroupConditionCode.AddRange(dtImportObject.Rows.OfType<DataRow>().Where(d =>
!d.IsNull(FieldMappings["GroupCondition4"])).Select(d => d[FieldMappings["GroupCondition4"]].GetString()).Distinct().ToList());
listGroupConditionCode.AddRange(dtImportObject.Rows.OfType<DataRow>().Where(d =>
!d.IsNull(FieldMappings["GroupCondition5"])).Select(d => d[FieldMappings["GroupCondition5"]].GetString()).Distinct().ToList());
listGroupConditionCode.AddRange(dtImportObject.Rows.OfType<DataRow>().Where(d =>
!d.IsNull(FieldMappings["GroupCondition6"])).Select(d => d[FieldMappings["GroupCondition6"]].GetString()).Distinct().ToList());
listGroupConditionCode.AddRange(dtImportObject.Rows.OfType<DataRow>().Where(d =>
!d.IsNull(FieldMappings["GroupCondition7"])).Select(d => d[FieldMappings["GroupCondition7"]].GetString()).Distinct().ToList());
var listJobVacancy = new List<Rec_JobVacancy>().Select(d => new
{
d.ID,
d.Code
}).ToList();
var listLanguage = new List<Cat_NameEntity>().Select(d => new
{
d.ID,
d.Code
}).ToList();
var listCandidate = new List<Rec_Candidate>().Select(d => new
{
d.ID,
d.CodeCandidate,
d.CandidateName
}).ToList();
var listGroupCondition = new List<Rec_GroupCondition>().Select(d => new
{
d.ID,
d.Code,
d.GroupName
}).ToList();
var listRecruitmentHistory = new List<Rec_RecruitmentHistory>().Select(d => new
{
d.ID,
d.CandidateID,
d.DateApply
}).ToList();
var listInterview = new List<Rec_Interview>().Select(d => new
{
d.ID,
d.CandidateID,
d.GroupConditionID,
d.RecruitmentHistoryID
}).ToList();
foreach (var item in listLanguageCode.Chunk(1000))
{
listLanguage.AddRange(unitOfWork.CreateQueryable<Cat_NameEntity>(d =>
item.Contains(d.Code)).Select(d => new
{
d.ID,
d.Code
}).ToList());
}
foreach (var item in listJobVacancyCode.Chunk(1000))
{
listJobVacancy.AddRange(unitOfWork.CreateQueryable<Rec_JobVacancy>(d =>
item.Contains(d.Code)).Select(d => new
{
d.ID,
d.Code
}).ToList());
}
foreach (var item in listCandidateCode.Chunk(1000))
{
listCandidate.AddRange(unitOfWork.CreateQueryable<Rec_Candidate>(d =>
item.Contains(d.CodeCandidate)).Select(d => new
{
d.ID,
d.CodeCandidate,
d.CandidateName
}).ToList());
}
foreach (var item in listGroupConditionCode.Chunk(1000))
{
listGroupCondition.AddRange(unitOfWork.CreateQueryable<Rec_GroupCondition>(d =>
item.Contains(d.Code)).Select(d => new
{
d.ID,
d.Code,
d.GroupName
}).ToList());
}
var listCandidateID = listCandidate.Select(d => d.ID).Distinct().ToList();
foreach (var item in listCandidateID.Chunk(1000))
{
listRecruitmentHistory.AddRange(unitOfWork.CreateQueryable<Rec_RecruitmentHistory>(d =>
item.Contains(d.CandidateID)).Select(d => new
{
d.ID,
d.CandidateID,
d.DateApply
}).ToList());
}
foreach (var item in listCandidateID.Chunk(1000))
{
listInterview.AddRange(unitOfWork.CreateQueryable<Rec_Interview>(d =>
d.CandidateID.HasValue && item.Contains(d.CandidateID.Value)).Select(d => new
{
d.ID,
d.CandidateID,
d.GroupConditionID,
d.RecruitmentHistoryID
}).ToList());
}
dtImportObject.Columns.Add("CandidateID", typeof(Guid));
dtImportObject.Columns.Add("JobVacancyID", typeof(Guid));
dtImportObject.Columns.Add("LanguageID", typeof(Guid));
dtImportObject.Columns.Add("InterviewID", typeof(Guid));
dtImportObject.Columns.Add("RecruitmentHistoryID", typeof(Guid));
dtImportObject.Columns.Add("InterviewID1", typeof(Guid));
dtImportObject.Columns.Add("InterviewID2", typeof(Guid));
dtImportObject.Columns.Add("InterviewID3", typeof(Guid));
dtImportObject.Columns.Add("InterviewID4", typeof(Guid));
dtImportObject.Columns.Add("InterviewID5", typeof(Guid));
dtImportObject.Columns.Add("InterviewID6", typeof(Guid));
dtImportObject.Columns.Add("InterviewID7", typeof(Guid));
dtImportObject.Columns.Add("GroupConditionID1", typeof(Guid));
dtImportObject.Columns.Add("GroupConditionID2", typeof(Guid));
dtImportObject.Columns.Add("GroupConditionID3", typeof(Guid));
dtImportObject.Columns.Add("GroupConditionID4", typeof(Guid));
dtImportObject.Columns.Add("GroupConditionID5", typeof(Guid));
dtImportObject.Columns.Add("GroupConditionID6", typeof(Guid));
dtImportObject.Columns.Add("GroupConditionID7", typeof(Guid));
var dtInvalidObject = new DataTable("InvalidObject");
dtInvalidObject.Columns.Add("DataField", typeof(string));
dtInvalidObject.Columns.Add("InvalidValue", typeof(string));
dtInvalidObject.Columns.Add("ExcelField", typeof(string));
dtInvalidObject.Columns.Add("ExcelValue", typeof(object));
dtInvalidObject.Columns.Add("ValueType", typeof(string));
dtInvalidObject.Columns.Add("Desciption", typeof(string));
List<DataRow> listInvalidRow = new List<DataRow>();
foreach (DataRow excelRow in dtImportObject.Rows)
{
var excelRowIndex = excelRow.Field<int>(DefaultConstants.ExcelRowIndex);
var nullData = HRM.Business.Main.Domain.InvalidDataType.NullData.ToString().TranslateString();
var duplicateInDb = HRM.Business.Main.Domain.InvalidDataType.DuplicateInDb.ToString().TranslateString();
var duplicateInFile = HRM.Business.Main.Domain.InvalidDataType.DuplicateInFile.ToString().TranslateString();
var referenceNotFound = HRM.Business.Main.Domain.InvalidDataType.ReferenceNotFound.ToString().TranslateString();
var listGroupConditionID = new List<Guid>();
Guid recruitmentHistoryID = Guid.Empty;
Guid candidateID = Guid.Empty;
bool isInvalid = false;
foreach (var templateItem in importTemplateItems)
{
string fieldName = templateItem.ChildFieldLevel1.TrimAll();
var excelField = templateItem.ExcelField1.TrimAll();
string excelAddress = excelField + (excelRowIndex + 1);
if (excelRow.IsNull(excelField))
{
if (!templateItem.AllowNull.GetBoolean())
{
bool isNotNullGroup = false;
if (fieldName == "GroupCondition1" || fieldName == "GroupResult1")
{
if (!excelRow.IsNull(FieldMappings["GroupCondition1"])
|| !excelRow.IsNull(FieldMappings["Group1Score1"])
|| !excelRow.IsNull(FieldMappings["Group1Score2"])
|| !excelRow.IsNull(FieldMappings["Group1Score3"])
|| !excelRow.IsNull(FieldMappings["GroupResult1"]))
{
isNotNullGroup = true;
}
}
else if (fieldName == "GroupCondition2" || fieldName == "GroupResult2")
{
if (!excelRow.IsNull(FieldMappings["GroupCondition2"])
|| !excelRow.IsNull(FieldMappings["Group2Score1"])
|| !excelRow.IsNull(FieldMappings["Group2Score2"])
|| !excelRow.IsNull(FieldMappings["Group2Score3"])
|| !excelRow.IsNull(FieldMappings["GroupResult2"]))
{
isNotNullGroup = true;
}
}
else if (fieldName == "GroupCondition3" || fieldName == "GroupResult3")
{
if (!excelRow.IsNull(FieldMappings["GroupCondition3"])
|| !excelRow.IsNull(FieldMappings["Group3Score1"])
|| !excelRow.IsNull(FieldMappings["Group3Score2"])
|| !excelRow.IsNull(FieldMappings["Group3Score3"])
|| !excelRow.IsNull(FieldMappings["GroupResult3"]))
{
isNotNullGroup = true;
}
}
else if (fieldName == "GroupCondition4" || fieldName == "GroupResult4")
{
if (!excelRow.IsNull(FieldMappings["GroupCondition4"])
|| !excelRow.IsNull(FieldMappings["Group4Score1"])
|| !excelRow.IsNull(FieldMappings["Group4Score2"])
|| !excelRow.IsNull(FieldMappings["Group4Score3"])
|| !excelRow.IsNull(FieldMappings["GroupResult4"]))
{
isNotNullGroup = true;
}
}
else if (fieldName == "GroupCondition5" || fieldName == "GroupResult5")
{
if (!excelRow.IsNull(FieldMappings["GroupCondition5"])
|| !excelRow.IsNull(FieldMappings["Group5Score1"])
|| !excelRow.IsNull(FieldMappings["Group5Score2"])
|| !excelRow.IsNull(FieldMappings["Group5Score3"])
|| !excelRow.IsNull(FieldMappings["GroupResult5"]))
{
isNotNullGroup = true;
}
}
else if (fieldName == "GroupCondition6" || fieldName == "GroupResult6")
{
if (!excelRow.IsNull(FieldMappings["GroupCondition6"])
|| !excelRow.IsNull(FieldMappings["Group6Score1"])
|| !excelRow.IsNull(FieldMappings["Group6Score2"])
|| !excelRow.IsNull(FieldMappings["Group6Score3"])
|| !excelRow.IsNull(FieldMappings["GroupResult6"]))
{
isNotNullGroup = true;
}
}
else if (fieldName == "GroupCondition7" || fieldName == "GroupResult7")
{
if (!excelRow.IsNull(FieldMappings["GroupCondition7"])
|| !excelRow.IsNull(FieldMappings["Group7Score1"])
|| !excelRow.IsNull(FieldMappings["Group7Score2"])
|| !excelRow.IsNull(FieldMappings["Group7Score3"])
|| !excelRow.IsNull(FieldMappings["GroupResult7"]))
{
isNotNullGroup = true;
}
}
else
{
isNotNullGroup = true;
}
if (isNotNullGroup)
{
var invalidRow = dtInvalidObject.NewRow();
dtInvalidObject.Rows.Add(invalidRow);
invalidRow.SetField("DataField", fieldName);
invalidRow.SetField("InvalidValue", string.Empty);
invalidRow.SetField("ExcelField", excelAddress);
invalidRow.SetField("ExcelValue", string.Empty);
invalidRow.SetField("Desciption", nullData);
isInvalid = true;
}
}
}
else
{
var excelValue = excelRow[excelField];
if (fieldName == "JobVacancyCode")
{
var jobVacancy = listJobVacancy.Where(d => d.Code == excelValue.GetString()).FirstOrDefault();
if (jobVacancy == null)
{
var invalidRow = dtInvalidObject.NewRow();
dtInvalidObject.Rows.Add(invalidRow);
invalidRow.SetField("DataField", fieldName);
invalidRow.SetField("InvalidValue", excelValue.GetString());
invalidRow.SetField("ExcelField", excelAddress);
invalidRow.SetField("ExcelValue", excelValue);
invalidRow.SetField("Desciption", referenceNotFound);
isInvalid = true;
}
else
{
excelRow.SetField("JobVacancyID", jobVacancy.ID);
}
}
else if (fieldName == "LanguageCode")
{
var language = listLanguage.Where(d => d.Code == excelValue.GetString()).FirstOrDefault();
if (language == null)
{
if (!string.IsNullOrWhiteSpace(excelValue.GetString()))
{
var invalidRow = dtInvalidObject.NewRow();
dtInvalidObject.Rows.Add(invalidRow);
invalidRow.SetField("DataField", fieldName);
invalidRow.SetField("InvalidValue", excelValue.GetString());
invalidRow.SetField("ExcelField", excelAddress);
invalidRow.SetField("ExcelValue", excelValue);
invalidRow.SetField("Desciption", referenceNotFound);
isInvalid = true;
}
}
else
{
excelRow.SetField("LanguageID", language.ID);
}
}
else if (fieldName == "CandidateCode")
{
candidateID = listCandidate.Where(d => d.CodeCandidate
== excelValue.GetString()).Select(d => d.ID).FirstOrDefault();
if (candidateID == Guid.Empty)
{
var invalidRow = dtInvalidObject.NewRow();
dtInvalidObject.Rows.Add(invalidRow);
invalidRow.SetField("DataField", fieldName);
invalidRow.SetField("InvalidValue", excelValue.GetString());
invalidRow.SetField("ExcelField", excelAddress);
invalidRow.SetField("ExcelValue", excelValue);
invalidRow.SetField("Desciption", referenceNotFound);
isInvalid = true;
}
else
{
excelRow.SetField("CandidateID", candidateID);
recruitmentHistoryID = listRecruitmentHistory.Where(d => d.CandidateID == candidateID)
.OrderByDescending(d => d.DateApply).Select(d => d.ID).FirstOrDefault();
if (recruitmentHistoryID != Guid.Empty)
{
excelRow.SetField("RecruitmentHistoryID", recruitmentHistoryID);
}
}
}
else if (fieldName == "GroupCondition1" || fieldName == "GroupCondition2"
|| fieldName == "GroupCondition3" || fieldName == "GroupCondition4"
|| fieldName == "GroupCondition5" || fieldName == "GroupCondition6"
|| fieldName == "GroupCondition7")
{
var groupConditionID = listGroupCondition.Where(d => d.Code
== excelValue.GetString()).Select(d => d.ID).FirstOrDefault();
if (groupConditionID == Guid.Empty)
{
var invalidRow = dtInvalidObject.NewRow();
dtInvalidObject.Rows.Add(invalidRow);
invalidRow.SetField("DataField", fieldName);
invalidRow.SetField("InvalidValue", excelValue.GetString());
invalidRow.SetField("ExcelField", excelAddress);
invalidRow.SetField("ExcelValue", excelValue);
invalidRow.SetField("Desciption", referenceNotFound);
isInvalid = true;
}
else
{
string groupIDField = fieldName.Substring(14);
groupIDField = "GroupConditionID" + groupIDField;
excelRow.SetField(groupIDField, groupConditionID);
if (listGroupConditionID.Contains(groupConditionID))
{
var invalidRow = dtInvalidObject.NewRow();
dtInvalidObject.Rows.Add(invalidRow);
invalidRow.SetField("DataField", fieldName);
invalidRow.SetField("InvalidValue", excelValue.GetString());
invalidRow.SetField("ExcelField", excelAddress);
invalidRow.SetField("ExcelValue", excelValue);
invalidRow.SetField("Desciption", duplicateInFile);
isInvalid = true;
}
else
{
listGroupConditionID.Add(groupConditionID);
var isDupplicateOnTable = dtImportObject.Rows.OfType<DataRow>().Any(d => d != excelRow && d["CandidateID"].GetString() == candidateID.ToString()
&& (d["GroupConditionID1"].GetString() == groupConditionID.ToString() || d["GroupConditionID2"].GetString() == groupConditionID.ToString()
|| d["GroupConditionID3"].GetString() == groupConditionID.ToString() || d["GroupConditionID4"].GetString() == groupConditionID.ToString()
|| d["GroupConditionID5"].GetString() == groupConditionID.ToString() || d["GroupConditionID6"].GetString() == groupConditionID.ToString()
|| d["GroupConditionID7"].GetString() == groupConditionID.ToString()));
if (isDupplicateOnTable)
{
var invalidRow = dtInvalidObject.NewRow();
dtInvalidObject.Rows.Add(invalidRow);
invalidRow.SetField("DataField", fieldName);
invalidRow.SetField("InvalidValue", excelValue.GetString());
invalidRow.SetField("ExcelField", excelAddress);
invalidRow.SetField("ExcelValue", excelValue);
invalidRow.SetField("Desciption", duplicateInFile);
isInvalid = true;
}
else
{
var listInterviewByCandidate = listInterview.Where(d => d.CandidateID == candidateID && d.RecruitmentHistoryID == recruitmentHistoryID).ToList();
var interViewDupplicate = listInterviewByCandidate.Where(d => d.GroupConditionID == groupConditionID).FirstOrDefault();
if (interViewDupplicate != null)
{
string interviewIDField = fieldName.Substring(14);
interviewIDField = "InterviewID" + interviewIDField;
excelRow.SetField(interviewIDField, interViewDupplicate.ID);
}
}
}
}
}
}
}
if (isInvalid)
{
listInvalidRow.Add(excelRow);
}
}
if (listInvalidRow.Count() > 0)
{
foreach (DataRow item in listInvalidRow)
{
dtImportObject.Rows.Remove(item);
}
}
if (ImportObjects.ContainsKey(UserID))
{
ImportObjects[UserID] = dtImportObject;
}
else
{
ImportObjects.Add(UserID, dtImportObject);
}
if (InvalidObjects.ContainsKey(UserID))
{
InvalidObjects[UserID] = dtInvalidObject;
}
else
{
InvalidObjects.Add(UserID, dtInvalidObject);
}
}
}
}
private static void ImportSalesFromExcel()
{
var db = new ChemicalsDbContext();
IZipExtractor zipExtractor = new ZipExtractor();
ExcelImporter<Sale> k = new ExcelImporter<Sale>(zipExtractor);
ICollection<Sale> sales = k.ImportModelsDataFromZipFile("../../../Files/Sales.zip", "./tests2");
foreach (var item in sales)
{
db.Sales.Add(item);
}
db.SaveChanges();
Console.WriteLine("The data was successfully imported to SQL Server.");
}
public string[] Unpivot()
{
string[] result = null;
if (!string.IsNullOrWhiteSpace(FileName))
{
using (var context = new VnrHrmDataContext())
{
IUnitOfWork unitOfWork = new UnitOfWork(context);
if (PivotTemplateID != Guid.Empty)
{
var listPivotItem = unitOfWork.CreateQueryable<Cat_PivotItem>(Guid.Empty,
d => d.PivotID == PivotTemplateID).Select(d => new
{
d.StartColumnIndex,
d.StartRowIndex,
d.SkipRowNumbers,
d.PivotColumnEnd,
d.PivotColumnName,
d.PivotColumnStart,
d.PivotHeaderRowIndex,
d.PivotSheetIndex,
d.TargetSheetIndex,
d.TargetSheetName
}).ToList();
string fileExt = Path.GetExtension(FileName);
fileExt = string.IsNullOrWhiteSpace(fileExt) ? "xls" : fileExt;
fileExt = fileExt.StartsWith(".") ? fileExt : "." + fileExt;
string downloadPath = Common.GetPath(Common.DownloadURL);
downloadPath = downloadPath.Replace("/", "\\");
string fileSuffix = DateTime.Now.ToString("yyyyMMddHHmmss");
string outputFile = Path.GetFileNameWithoutExtension(FileName);
outputFile = downloadPath + "\\" + outputFile + fileSuffix + fileExt;
outputFile = Regex.Replace(outputFile, @"[^0-9a-zA-Z_\\:.]+", "");
var listPivotTemplate = listPivotItem.Select(d => new PivotTemplate
{
PivotFileName = FileName,
TargetFileName = outputFile,
StartColumnIndex = d.StartColumnIndex.GetInteger(),
StartRowIndex = d.StartRowIndex.GetInteger(),
SkipRowNumbers = d.SkipRowNumbers.GetInteger(),
PivotColumnEnd = d.PivotColumnEnd.GetInteger(),
PivotColumnName = d.PivotColumnName,
PivotColumnStart = d.PivotColumnStart.GetInteger(),
PivotHeaderRowIndex = d.PivotHeaderRowIndex.GetInteger(),
PivotSheetIndex = d.PivotSheetIndex.GetInteger(),
TargetSheetIndex = d.TargetSheetIndex.GetInteger(),
TargetSheetName = d.TargetSheetName
}).ToArray();
ExcelImporter importer = new ExcelImporter();
result = importer.Unpivot(listPivotTemplate);
}
}
}
return result;
}
