public ExportSchoolClass(Configuration config, XlsReader xlsReader)
{
if (xlsReader.TryGetValue(config.GetXlsColumnName("Klasse"), out Id))
{
Code = Id;
}
}
public ExportStudent(Configuration config, XlsReader xlsReader)
{
xlsReader.TryGetValue(config.GetXlsColumnName("Vorname"), out FirstName);
xlsReader.TryGetValue(config.GetXlsColumnName("Mittelname"), out MiddleName);
xlsReader.TryGetValue(config.GetXlsColumnName("Nachname"), out LastName);
xlsReader.TryGetValue(config.GetXlsColumnName("Geburtstag"), out BirthDate);
xlsReader.TryGetValue(config.GetXlsColumnName("Rufname"), out NickName);
xlsReader.TryGetValue(config.GetXlsColumnName("Geschlecht"), out Gender);
xlsReader.TryGetValue(config.GetXlsColumnName("Anrede"), out Salutation);
if (xlsReader.TryGetValue(config.GetXlsColumnName("Name"), out string name))
{
var csvLineParser = new CsvLineParser(' ');
var parts = csvLineParser.Read(name);
if (parts.Length == 2)
{
FirstName = parts[0];
LastName = parts[1];
}
else if (parts.Length == 3)
{
FirstName = parts[0];
MiddleName = parts[1];
LastName = parts[2];
}
}
if (!xlsReader.TryGetValue(config.GetXlsColumnName("Id"), out Id))
{
Id = GenerateId();
}
}
public ExportSubject(Configuration config, XlsReader xlsReader, string headerName)
{
if (xlsReader.TryGetValue(config.GetXlsColumnName(headerName), out Id))
{
Code = Id;
}
}
private async Task Execute(IXLWorkbook xlsDocument, string ecfTableName, Func <XlsReader, EcfTableWriter, string[], Task <int> > action)
{
if (ShouldExportTable(ecfTableName, out var ecfFile))
{
// Report status
Console.WriteLine($"[Extracting] [{ecfTableName}] Start...");
// Create Excel Reader
var xlsReader = new XlsReader(xlsDocument, _config?.EcfExport?.XlsSheetName, _config?.EcfExport?.XlsFirstRowNumber, _config?.EcfExport?.XlsLastRowNumber);
// Generate ECF file name
var ecfFileName = Path.ChangeExtension(Path.Combine(_config.EcfExport.TargetFolderName, ecfTableName), "csv");
// Create ECF file for export
using var ecfWriterStream = new FileStream(ecfFileName, FileMode.Create, FileAccess.ReadWrite, FileShare.None);
// Create ECF Writer
using var ecfWriter = new CsvWriter(ecfWriterStream, Encoding.UTF8);
// Call table specific action
var ecfRecordCounter = await action(xlsReader, new EcfTableWriter(ecfWriter), ecfFile?.Headers);
// Inc counters
_recordCounter += ecfRecordCounter;
_tableCounter++;
// Report status
Console.WriteLine($"[Extracting] [{ecfTableName}] {ecfRecordCounter} record(s) extracted");
}
}
private async Task <int> ExportStudentSchoolClassAttendances(XlsReader xlsReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.StudentId,
EcfHeaders.SchoolClassId);
}
while (xlsReader.ReadLine())
{
var student = new ExportStudent(_config, xlsReader);
var schoolClass = new ExportSchoolClass(_config, xlsReader);
if (!string.IsNullOrEmpty(schoolClass.Id))
{
ecfTableWriter.SetValue(EcfHeaders.StudentId, student.Id);
ecfTableWriter.SetValue(EcfHeaders.SchoolClassId, schoolClass.Id);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportSchoolClasses(XlsReader xlsReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfCache = new HashSet <string>();
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code);
}
while (xlsReader.ReadLine())
{
var schoolClass = new ExportSchoolClass(_config, xlsReader);
if (!string.IsNullOrEmpty(schoolClass.Id) && !ecfCache.Contains(schoolClass.Id))
{
ecfTableWriter.SetValue(EcfHeaders.Id, schoolClass.Id);
ecfTableWriter.SetValue(EcfHeaders.Code, schoolClass.Code);
await ecfTableWriter.WriteAsync();
ecfCache.Add(schoolClass.Id);
ecfRecordCounter++;
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportStudents(XlsReader xlsReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfCache = new HashSet <string>();
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.LastName,
EcfHeaders.FirstName,
EcfHeaders.MiddleName,
EcfHeaders.NickName,
EcfHeaders.Salutation,
EcfHeaders.Gender,
EcfHeaders.Birthdate);
}
while (xlsReader.ReadLine())
{
var student = new ExportStudent(_config, xlsReader);
if (!ecfCache.Contains(student.Id))
{
ecfTableWriter.SetValue(EcfHeaders.Id, student.Id);
ecfTableWriter.SetValue(EcfHeaders.LastName, student.LastName);
ecfTableWriter.SetValue(EcfHeaders.FirstName, student.FirstName);
ecfTableWriter.SetValue(EcfHeaders.MiddleName, student.MiddleName);
ecfTableWriter.SetValue(EcfHeaders.NickName, student.NickName);
ecfTableWriter.SetValue(EcfHeaders.Salutation, student.Salutation);
ecfTableWriter.SetValue(EcfHeaders.Gender, student.Gender);
ecfTableWriter.SetValue(EcfHeaders.Birthdate, student.BirthDate);
await ecfTableWriter.WriteAsync();
ecfCache.Add(student.Id);
ecfRecordCounter++;
}
}
return(ecfRecordCounter);
}
private async Task <int> ExportSubjects(XlsReader xlsReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfCache = new HashSet <string>();
var ecfRecordCounter = 0;
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code);
}
while (xlsReader.ReadLine())
{
for (int i = 1; i < 20; i++)
{
var subject = new ExportSubject(_config, xlsReader, $"Fach{i}");
if (!string.IsNullOrEmpty(subject.Id) && !ecfCache.Contains(subject.Id))
{
ecfTableWriter.SetValue(EcfHeaders.Id, subject.Id);
ecfTableWriter.SetValue(EcfHeaders.Code, subject.Code);
await ecfTableWriter.WriteAsync();
ecfCache.Add(subject.Id);
ecfRecordCounter++;
}
}
}
return(ecfRecordCounter);
}