/// <summary>
/// Synchronisiert die Excel-Datei mit der Datenbank.
/// </summary>
/// <param name="fileName">Der Dateiname.</param>
public void Synchronize(string fileName)
{
if (OnStatusChange != null)
{
OnStatusChange(this, new StatusChangedEventArgs() { Status = "synchronisiere " + fileName });
}
using (ExcelSheet sheet = new ExcelSheet(fileName))
{
// erst mal schauen, ob der Kurs laut DB existiert. Todo: nächstes Jahr kursId verwenden!
KursTableAdapter kursAdapter = new KursTableAdapter();
var kurse = kursAdapter.GetDataByBezeichnung(sheet.Kursbezeichnung);
if (kurse.Count != 1)
{
kurse = kursAdapter.GetDataByBezeichnung(sheet.Fachname + " " + sheet.Kursbezeichnung);
if (kurse.Count != 1)
{
if (OnStatusChange != null)
{
OnStatusChange(this, new StatusChangedEventArgs() { Status = "Fehler in Datei " + fileName + ": Kurs nicht oder mehrfach gefunden: " + sheet.Kursbezeichnung });
}
}
}
int kursId = kurse[0].Id;
using (NoteTableAdapter noteAdapter = new NoteTableAdapter())
using (BerechneteNoteTableAdapter berechneteNotenAdapter = new BerechneteNoteTableAdapter())
{
DeleteAlteNoten(kursId, noteAdapter, berechneteNotenAdapter);
foreach (var schueler in sheet.Schueler)
{
InsertNoten(kursId, noteAdapter, berechneteNotenAdapter, schueler);
}
if (OnStatusChange != null)
{
OnStatusChange(this, new StatusChangedEventArgs() { Status = "Noten sind eingetragen. Prüfe auf Änderungen an den Schülerdaten." });
}
var alleSchueler = CheckSchueler(sheet, kursId);
CheckLegastheniker(sheet, kursAdapter, kurse[0], alleSchueler);
}
}
if (OnStatusChange != null)
{
OnStatusChange(this, new StatusChangedEventArgs() { Status = "Datei " + fileName + " erfolgreich gelesen" });
}
}
public void ReadRow_AutoMappedDouble_Success()
{
using var importer = Helpers.GetImporter("Doubles.xlsx");
ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();
// Valid cell value.
DoubleClass row1 = sheet.ReadRow <DoubleClass>();
Assert.Equal(2.2345, row1.Value);
// Empty cell value.
Assert.Throws <ExcelMappingException>(() => sheet.ReadRow <DoubleClass>());
// Invalid cell value.
Assert.Throws <ExcelMappingException>(() => sheet.ReadRow <DoubleClass>());
}
public void ReadRows_AllReadingHasHeadingFalse_ReturnsEmpty()
{
using (var importer = Helpers.GetImporter("Strings.xlsx"))
{
importer.Configuration.RegisterClassMap <StringValueClassMapColumnIndex>();
ExcelSheet sheet = importer.ReadSheet();
sheet.HasHeading = false;
IEnumerable <StringValue> rows1 = sheet.ReadRows <StringValue>();
Assert.Equal(new string[] { "Value", "value", " value ", null, "value" }, rows1.Select(p => p.Value).ToArray());
Assert.Null(sheet.Heading);
Assert.False(sheet.HasHeading);
StringValue[] rows2 = sheet.ReadRows <StringValue>().ToArray();
Assert.Empty(rows2.Select(p => p.Value).ToArray());
}
}
public void ReadRow_AutoMappedNullableEnum_ReturnsExpected()
{
using (var importer = Helpers.GetImporter("Enums.xlsx"))
{
ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();
// Valid cell value.
NullableEnumValue row1 = sheet.ReadRow <NullableEnumValue>();
Assert.Equal(TestEnum.Member, row1.Value);
// Empty cell value.
NullableEnumValue row2 = sheet.ReadRow <NullableEnumValue>();
Assert.Null(row2.Value);
// Invalid cell value.
Assert.Throws <ExcelMappingException>(() => sheet.ReadRow <NullableEnumValue>());
}
}
public void ReadRow_AutoMappedInt32_Success()
{
using var importer = Helpers.GetImporter("Numbers.xlsx");
ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();
// Valid cell value.
Int32Value row1 = sheet.ReadRow <Int32Value>();
Assert.Equal(2, row1.Value);
// Empty cell value.
Assert.Throws <ExcelMappingException>(() => sheet.ReadRow <Int32Value>());
// Invalid cell value.
Assert.Throws <ExcelMappingException>(() => sheet.ReadRow <Int32Value>());
}
public void ReadRow_AutoMappedNullableDecimal_Success()
{
using (var importer = Helpers.GetImporter("Doubles.xlsx"))
{
ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();
// Valid cell value.
NullableDecimalValue row1 = sheet.ReadRow <NullableDecimalValue>();
Assert.Equal(2.2345m, row1.Value);
// Empty cell value.
NullableDecimalValue row2 = sheet.ReadRow <NullableDecimalValue>();
Assert.Null(row2.Value);
// Invalid cell value.
Assert.Throws <ExcelMappingException>(() => sheet.ReadRow <DecimalValue>());
}
}
public void ReadRow_AutoMappedDateTime_Success()
{
using var importer = Helpers.GetImporter("DateTimes.xlsx");
ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();
// Valid cell value.
DateTimeValue row1 = sheet.ReadRow <DateTimeValue>();
Assert.Equal(new DateTime(2017, 07, 19), row1.Value);
// Empty cell value.
Assert.Throws <ExcelMappingException>(() => sheet.ReadRow <DateTimeValue>());
// Invalid cell value.
Assert.Throws <ExcelMappingException>(() => sheet.ReadRow <DateTimeValue>());
}
public void ReadRow_CustomMappedString_Success()
{
using (var importer = Helpers.GetImporter("Strings.xlsx"))
{
importer.Configuration.RegisterClassMap <TrimStringValueMap>();
ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();
StringValue row1 = sheet.ReadRow <StringValue>();
Assert.Equal("value", row1.Value);
StringValue row2 = sheet.ReadRow <StringValue>();
Assert.Equal("value", row2.Value);
StringValue row3 = sheet.ReadRow <StringValue>();
Assert.Null(row3.Value);
}
}
public void ReadRow_HasHeader_ReturnsExpected()
{
using (var importer = Helpers.GetImporter("Primitives.xlsx"))
{
importer.Configuration.RegisterClassMap <ColumnMatchingClassMap>();
ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();
StringValue row1 = sheet.ReadRow <StringValue>();
Assert.Equal("1", row1.Value);
StringValue row2 = sheet.ReadRow <StringValue>();
Assert.Equal("b", row2.Value);
StringValue row3 = sheet.ReadRow <StringValue>();
Assert.Null(row3.Value);
}
}
/// <summary>
/// Froms the excel.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="excelfile">The excelfile.</param>
/// <param name="sheetName">Name of the sheet.</param>
/// <param name="cellExistin">The cell existin.</param>
/// <param name="startRow">The start row.</param>
/// <returns>FromExcel</returns>
public dynamic FromExcel <TEntity>(System.IO.FileInfo excelfile, string sheetName, Dictionary <string, IEnumerable <string> > cellExistin = null, int startRow = 2)
where TEntity : class
{
if (cellExistin == null)
{
cellExistin = new Dictionary <string, IEnumerable <string> >();
}
var excelSheet = new ExcelSheet <TEntity>();
excelSheet.Name = sheetName;
using (ExcelPackage package = new ExcelPackage(excelfile))
{
if (package.Workbook.Worksheets.Count == 0)
{
excelSheet.Status = false;
excelSheet.Message = $"{sheetName} not Found in Excel File.";
return(excelSheet);
}
ExcelWorksheet workSheet = package.Workbook.Worksheets.FirstOrDefault(x => x.Name.ToLower().Trim() == sheetName.ToLower().Trim());
if (workSheet != null)
{
startRow = workSheet.Row(1).RowEmpty(workSheet) ? startRow - 1 : startRow;
int rowindex = startRow == 0 ? 1 : startRow;
excelSheet.Records = cellExistin.Count > 0 ? workSheet.ToExcelSheet <TEntity>(cellExistin, rowindex) : workSheet.ToExcelSheet <TEntity>(rowindex);
excelSheet.Name = workSheet.Name;
excelSheet.Status = excelSheet.Records.Length > 0;
excelSheet.Message = excelSheet.Records.Length > 0 ? string.Empty : $"No Data Found in {sheetName}.";
return(excelSheet);
}
else
{
excelSheet.Status = false;
excelSheet.Message = $"{sheetName} not Found in Excel File.";
return(excelSheet);
}
}
}
public void ReadRow_AutoMappedUri_ReturnsExpected()
{
using var importer = Helpers.GetImporter("Uris.xlsx");
ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();
UriClass row1 = sheet.ReadRow <UriClass>();
Assert.Equal(new Uri("http://google.com"), row1.Uri);
// Defaults to null if empty.
UriClass row2 = sheet.ReadRow <UriClass>();
Assert.Null(row2.Uri);
// Defaults to throw if invalid.
Assert.Throws <ExcelMappingException>(() => sheet.ReadRow <UriClass>());
}
public void ReadRow_DefaultMappedFloat_Success()
{
using var importer = Helpers.GetImporter("Doubles.xlsx");
importer.Configuration.RegisterClassMap <DefaultFloatClassMap>();
ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();
// Valid cell value.
FloatClass row1 = sheet.ReadRow <FloatClass>();
Assert.Equal(2.2345f, row1.Value);
// Empty cell value.
Assert.Throws <ExcelMappingException>(() => sheet.ReadRow <FloatClass>());
// Invalid cell value.
Assert.Throws <ExcelMappingException>(() => sheet.ReadRow <FloatClass>());
}
private bool insertToDatabase(string filename)
{
ExcelSheet sheet = new ExcelSheet();
sheet.open(filename);
if (!sheet.isOpen())
{
return(false);
}
DataRowCollection CallsData = sheet.getDataSet().Tables[0].Rows;
try
{
DateTime.FromOADate((double)CallsData[0][5]);
return(FillToDataBaseOA(CallsData));
}
catch { return(FillToDataBase(CallsData)); }
}
public void ReadRow_AutoMappedString_Success()
{
using (var importer = Helpers.GetImporter("Strings.xlsx"))
{
ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();
// Valid value
StringValue row1 = sheet.ReadRow <StringValue>();
Assert.Equal("value", row1.Value);
// Valid value
StringValue row2 = sheet.ReadRow <StringValue>();
Assert.Equal(" value ", row2.Value);
// Empty value
StringValue row3 = sheet.ReadRow <StringValue>();
Assert.Null(row3.Value);
}
}
public void ReadRows_CustomColumnIndexClassMap_ReturnsExpected()
{
using var importer = Helpers.GetImporter("CustomColumnIndex.xlsx");
importer.Configuration.RegisterClassMap <CustomColumnIndexClassMap>();
ExcelSheet sheet = importer.ReadSheet();
sheet.HasHeading = false;
CustomColumnIndexDataClass row1 = sheet.ReadRow <CustomColumnIndexDataClass>();
Assert.Equal("Column3_A", row1.Value1);
Assert.Equal("Column2_A", row1.Value2);
Assert.Equal("Column1_A", row1.Value3);
CustomColumnIndexDataClass row2 = sheet.ReadRow <CustomColumnIndexDataClass>();
Assert.Equal("Column3_B", row2.Value1);
Assert.Equal("Column2_B", row2.Value2);
Assert.Equal("Column1_B", row2.Value3);
}
public void ReadRows_InvalidColumnIndexOptionalClassMap2_ThrowsExcelMappingException()
{
using var importer = Helpers.GetImporter("CustomColumnIndex.xlsx");
importer.Configuration.RegisterClassMap <InvalidColumnIndexOptionalClassMap2>();
ExcelSheet sheet = importer.ReadSheet();
sheet.HasHeading = false;
CustomColumnIndexDataClass row1 = sheet.ReadRow <CustomColumnIndexDataClass>();
Assert.Null(row1.Value1);
Assert.Null(row1.Value2);
Assert.Null(row1.Value3);
CustomColumnIndexDataClass row2 = sheet.ReadRow <CustomColumnIndexDataClass>();
Assert.Null(row2.Value1);
Assert.Null(row2.Value2);
Assert.Null(row2.Value3);
}
public override Tree <Cell> Parse(CellFactory factory, Settings settings)
{
using (var archive = ZipFile.OpenRead(fileName)) {
var sheet = new ExcelSheet(factory.MakeEmptyCell);
foreach (var entry in archive.Entries)
{
if (entry.FullName == "xl/sharedStrings.xml")
{
sheet.LoadStrings(XDocument.Load(entry.Open()));
}
}
foreach (var entry in archive.Entries)
{
if (entry.FullName.StartsWith("xl/worksheets/") && entry.FullName.EndsWith(".xml"))
{
return(sheet.Parse(XDocument.Load(entry.Open())));
}
}
}
return(factory.MakeEmptyCell(string.Empty));
}
public void ErrorHandling(ExcelSheet excel)
{
ExcelSheetValidator validationRules = new ExcelSheetValidator();
var result = validationRules.Validate(excel);
if (!result.IsValid)
{
foreach (var err in result.Errors)
{
Debug.WriteLine(err.PropertyName + ": " + err.ErrorMessage);
/* var invalidCell = worksheet.Cells[i, err.PropertyName.GetIndex()];
* invalidCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
* invalidCell.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#ffc7ce"));
* invalidCell.Style.Font.Color.SetColor(ColorTranslator.FromHtml("#be0006"));
*
* worksheet.Cells[i, cols + 1].Value += $"{err.ErrorMessage} ";
* worksheet.Cells[i, cols + 1].Style.Font.Color.SetColor(ColorTranslator.FromHtml("#be0006"));*/
}
}
}
public override Tree <Cell> Parse(CellProcessor processor)
{
using (var archive = ZipFile.OpenRead(fileName)) {
var sheet = new ExcelSheet(text => MakeTreeCell(processor, text));
foreach (var entry in archive.Entries)
{
if (entry.FullName == "xl/sharedStrings.xml")
{
sheet.LoadStrings(XDocument.Load(entry.Open()));
}
}
foreach (var entry in archive.Entries)
{
if (entry.FullName.StartsWith("xl/worksheets/") && entry.FullName.EndsWith(".xml"))
{
return(sheet.Parse(XDocument.Load(entry.Open())));
}
}
}
return(MakeTreeCell(processor, string.Empty));
}
private void AddSheet(ExcelWorkbook book, ExcelSheet sheet)
{
var sheetName = sheet.Name;
var workSheet = book.Worksheets.Add(sheetName);
var data = sheet.Data;
for (int i = 0; i < data.GetLength(0); i++)
{
var rowPosition = i + 1;
for (int j = 0; j < data.GetLength(1); j++)
{
var columnPosition = j + 1;
var value = data[i, j];
workSheet.Cells[rowPosition, columnPosition].Value = value;
}
}
}
public void ReadRow_UriWithCustomFallback_ReturnsExpected()
{
using var importer = Helpers.GetImporter("Uris.xlsx");
importer.Configuration.RegisterClassMap <CustomUriClassMap>();
ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();
UriClass row1 = sheet.ReadRow <UriClass>();
Assert.Equal(new Uri("http://google.com"), row1.Uri);
UriClass row2 = sheet.ReadRow <UriClass>();
Assert.Equal(new Uri("http://empty.com"), row2.Uri);
UriClass row3 = sheet.ReadRow <UriClass>();
Assert.Equal(new Uri("http://invalid.com"), row3.Uri);
}
private static void GetReportInfo(ExcelSheet wss, int id, DataSet feedbackTables)
{
Microsoft.Office.Interop.Excel.Worksheet ws = wss.Worksheet;
DataTable infoTable = new DataTable("Info");
infoTable.Columns.Add("Id", typeof(int));
infoTable.Columns.Add("Name");
infoTable.Columns.Add("Month");
infoTable.Columns.Add("Company Number");
infoTable.Columns.Add("Environment");
infoTable.Columns.Add("Directory");
infoTable.Columns.Add("Pay Frequency");
infoTable.Columns.Add("Site Code");
infoTable.Columns.Add("CUID");
infoTable.Columns.Add("Account Manager");
infoTable.Columns.Add("Payroll Administrator");
infoTable.Columns.Add("Rollover Administrator");
infoTable.PrimaryKey = new DataColumn[] { infoTable.Columns["Id"] };
DataRow row = infoTable.NewRow();
row["Id"] = id;
string name = wss.SetRange(1, 2, 1, 2).Value.ToString();
row["Name"] = name.Replace("Monthly Process ", "").Replace(" ", "");
row["Month"] = ((Range)ws.Cells[2, 3]).Value;
row["Company Number"] = ((Range)ws.Cells[4, 3]).Value;
row["Environment"] = ((Range)ws.Cells[5, 3]).Value;
row["Directory"] = ((Range)ws.Cells[6, 3]).Value;
row["Pay Frequency"] = ((Range)ws.Cells[7, 3]).Value;
row["Site Code"] = ((Range)ws.Cells[2, 9]).Value;
row["CUID"] = ((Range)ws.Cells[3, 9]).Value;
row["Account Manager"] = ((Range)ws.Cells[6, 9]).Value;
row["Payroll Administrator"] = ((Range)ws.Cells[7, 9]).Value;
row["Rollover Administrator"] = ((Range)ws.Cells[8, 9]).Value;
infoTable.Rows.Add(row);
feedbackTables.Tables.Add(infoTable);
}
public static VestaReportSection create(
ExcelSheet sheet,
int top_row_idx,
string marker_text,
ReportTypes report_type,
int year
)
{
VestaReportSection retSect = null;
ExcelRow r = sheet[top_row_idx + 2];
if (!r.IsEmpty() &&
(r[EMPTY_SECTION_MARKER_COLUMN]).Trim() != EMPTY_SECTION_MARKER)
{
switch (report_type)
{
case ReportTypes.Participant:
retSect = new ParticipantReportSection(sheet, top_row_idx,
year);
break;
case ReportTypes.Labels:
Tuple <LabelType, RequestType> tu = parse_marker_text(marker_text);
LabelType lt = tu.Item1;
RequestType rt = tu.Item2;
switch (lt)
{
case LabelType.Bag:
retSect = new BagLabelReportSection(sheet, top_row_idx, rt, year);
break;
case LabelType.Gift:
retSect = new GiftLabelReportSection(sheet, top_row_idx, rt, year);
break;
}
break;
}
}
return(retSect);
}
public void ReadRow_CustomMappedNullableGuid_Success()
{
using var importer = Helpers.GetImporter("Guids.xlsx");
importer.Configuration.RegisterClassMap <CustomNullableGuidClassMap>();
ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();
// Valid cell value.
NullableGuidClass row1 = sheet.ReadRow <NullableGuidClass>();
Assert.Equal(new Guid("a8a110d5fc4943c5bf46802db8f843f1"), row1.Value);
NullableGuidClass row2 = sheet.ReadRow <NullableGuidClass>();
Assert.Equal(new Guid("a8a110d5fc4943c5bf46802db8f843f2"), row2.Value);
NullableGuidClass row3 = sheet.ReadRow <NullableGuidClass>();
Assert.Equal(new Guid("a8a110d5fc4943c5bf46802db8f843f3"), row3.Value);
NullableGuidClass row4 = sheet.ReadRow <NullableGuidClass>();
Assert.Equal(new Guid("a8a110d5fc4943c5bf46802db8f843f4"), row4.Value);
NullableGuidClass row5 = sheet.ReadRow <NullableGuidClass>();
Assert.Equal(new Guid("a8a110d5fc4943c5bf46802db8f843f5"), row5.Value);
// Empty cell value.
NullableGuidClass row6 = sheet.ReadRow <NullableGuidClass>();
Assert.Equal(new Guid("a8a110d5fc4943c5bf46802db8f843f6"), row6.Value);
// Invalid cell value.
NullableGuidClass row7 = sheet.ReadRow <NullableGuidClass>();
Assert.Equal(new Guid("a8a110d5fc4943c5bf46802db8f843f7"), row7.Value);
}
private ExcelSheet <TEntity> FromExcelSheet <TEntity>(ExcelWorksheet workSheet, string sheetName, Dictionary <string, IEnumerable <string> > cellExistin, int startRow)
where TEntity : class
{
var excelSheet = new ExcelSheet <TEntity>();
excelSheet.Name = sheetName;
if (workSheet != null)
{
startRow = workSheet.Row(1).RowEmpty(workSheet) ? startRow - 1 : startRow;
int rowindex = startRow == 0 ? 1 : startRow;
excelSheet.Records = cellExistin.Count > 0 ? workSheet.ToExcelSheet <TEntity>(cellExistin, rowindex) : workSheet.ToExcelSheet <TEntity>(rowindex);
excelSheet.Status = excelSheet.Records.Length > 0;
excelSheet.Message = excelSheet.Records.Length > 0 ? string.Empty : $"No Data Found in {sheetName}.";
return(excelSheet);
}
else
{
excelSheet.Status = false;
excelSheet.Message = $"{sheetName} not Found in Excel File.";
return(excelSheet);
}
}
public Errors OpenExcel(string filePath)
{
visible = false;
if (!File.Exists(filePath))
{
if (currExcel == filePath)
{
currExcel = null;
opened = false;
if (app != null)
{
app.Visible = false;
}
}
return(Errors.no_file);
}
if (currExcel == filePath)
{
return(0);
}
if (app == null)
{
app = new ExcelApp();
}
currExcel = filePath;
fileName = Path.GetFileNameWithoutExtension(filePath);
//if (app.Worksheets.Count > 0)
// app.Workbooks.Close();
object miss = Missing.Value;
book = app.Workbooks.Open(filePath, miss, true, miss, miss);
sheet = book.Worksheets.Item[1];
opened = sheet != null;
if (opened)
{
app.Visible = visible;
}
return(0);
}
public void ReadRow_CustomColumnName_ReturnsExpected()
{
using (var importer = Helpers.GetImporter("SplitWithComma.xlsx"))
{
importer.Configuration.RegisterClassMap(new SplitWithSeparatorMap());
ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();
CustomSplitWithSeparatorClass row1 = sheet.ReadRow <CustomSplitWithSeparatorClass>();
Assert.Equal(new string[] { "1", "2", "3" }, row1.Value);
Assert.Equal(new string[] { "1", "2", "3" }, row1.ValueWithColumnName);
Assert.Equal(new string[] { "1", "2", "3" }, row1.ValueWithColumnIndex);
Assert.Equal(new string[] { "1", "2", "3" }, row1.ValueWithColumnNameAcrossMultiColumnNames);
Assert.Equal(new string[] { "1", "2", "3" }, row1.ValueWithColumnNameAcrossMultiColumnIndices);
Assert.Equal(new string[] { "1", "2", "3" }, row1.ValueWithColumnIndexAcrossMultiColumnNames);
Assert.Equal(new string[] { "1", "2", "3" }, row1.ValueWithColumnIndexAcrossMultiColumnIndices);
}
}
public void ReadRow_CustomMappedNullableDouble_Success()
{
using (var importer = Helpers.GetImporter("Doubles.xlsx"))
{
importer.Configuration.RegisterClassMap <NullableDoubleValueFallbackMap>();
ExcelSheet sheet = importer.ReadSheet();
sheet.ReadHeading();
// Valid cell value.
NullableDoubleValue row1 = sheet.ReadRow <NullableDoubleValue>();
Assert.Equal(2.2345, row1.Value);
// Empty cell value.
NullableDoubleValue row2 = sheet.ReadRow <NullableDoubleValue>();
Assert.Equal(-10, row2.Value);
// Invalid cell value.
NullableDoubleValue row3 = sheet.ReadRow <NullableDoubleValue>();
Assert.Equal(10, row3.Value);
}
}
public ExcelRowViewModel(uint key, ExcelSheet <T> sheet, GameData lumina)
{
this.sheet = sheet;
this.Key = key;
this.lumina = lumina;
try
{
T?row = this.sheet.GetRow(this.Key);
if (row == null)
{
throw new Exception($"No row found at {this.Key}");
}
this.Value = row;
}
catch (Exception ex)
{
throw new Exception($"Failed to read Lumina row: {this.Key} for type: {typeof(T).Name}", ex);
}
}
public ActionResult Process(string fileName)
{
string spreadSheetPath = Path.Combine(Helpers.GetSetting("FolderToProcess"), "Pending", fileName);
var result = new ImportResult();
if (File.Exists(spreadSheetPath))
{
FileInfo fi = new FileInfo(spreadSheetPath);
var spreadSheet = new ExcelSheet();
spreadSheet.Open(spreadSheetPath);
var importStatusUpdate = new ImportStatusUpdate();
importStatusUpdate.Status = "Importing ...";
importStatusUpdate.Percentage = 0;
try
{
string dbName = Helpers.GetSetting("DBName");
<< << << < HEAD
var importRows = GetRows(spreadSheet);
public static ExcelSheet ConvertTransactionListToExcelSheet(List <Transaction> dbData)
{
var data = new ExcelSheet();
foreach (var item in dbData)
{
data.AddNewRow(new ExcelTransaction()
{
AccountingDate = item.AccountingDate,
TransactionId = item.TransactionId,
Type = item.Type,
Account = item.Account,
AccountName = item.AccountName,
PartnerAccount = item.PartnerAccount,
PartnerName = item.PartnerName,
Sum = (double)item.Sum.Value,
Currency = item.Currency.Name,
Message = item.Message
});
}
return(data);
}
/// <summary>
/// Holt sich die aktuelle Schülerliste aus der Datenbank. Falls ein Schüler fehlt, wird dieser in Excel unten angehängt.
/// Todo: Abgemeldete Schüler rauslöschen (zumindest den Namen).
/// </summary>
/// <param name="sheet">Das ExcelSheet.</param>
/// <param name="kursId">Die Id des Kurses.</param>
/// <returns>Die Datentabelle mit allen Schüler-Kurs-Zuordnungen.</returns>
private static diNoDataSet.SchuelerKursDataTable CheckSchueler(ExcelSheet sheet, int kursId)
{
var alleSchueler = new SchuelerKursTableAdapter().GetDataByKursId(kursId);
foreach (var schueler in alleSchueler)
{
if (!sheet.Schueler.Any(x => x.Id == schueler.SchuelerId))
{
// Dieser Schüler fehlt offenbar bisher
var dbSchueler = new SchuelerTableAdapter().GetDataById(schueler.SchuelerId)[0];
// TODO: Testen, ob da wirklich null drinsteht oder eher DateTime.MinValue oder sowas
if (dbSchueler.Austrittsdatum == null)
{
sheet.AppendSchueler(new Schueler(dbSchueler.Id, dbSchueler.Vorname, dbSchueler.Name, dbSchueler.LRSSchwaeche || dbSchueler.LRSStoerung, ""));
}
}
}
return alleSchueler;
}
public void processData()
{
ExcelSheet sheet = new ExcelSheet();
using(StreamReader file = new StreamReader(mFilename))
{
string line = null;
string[] row;
// Get to the data
while (line == null || line.Split(',')[1] != "Track")
{
line = file.ReadLine();
}
// Parse the data
while((line = file.ReadLine()) != null)
{
row = line.Split(',');
// If this cell does not have data then there is no more data.
if (row[4] == "")
{
break;
}
/*
* Column Name | Column Number
* Track | 1
* Direction | 2
* Move | 3
* Circuit | 4
* Brake Location | 5
* Target Location | 6
* Worst Grade | 8
* Entry Speed | 9
* Overspeed | 10
* Acceleration | 12
* Reaction Time | 14
* Brake Rate | 16
* Runaway Accel | 19
* Propulsion Remov | 21
* Brake Build up | 23
* Overhang Dist | 25
*/
ExcelRow curRow = new ExcelRow(
row[1] != "" ? Convert.ToInt32(row[1]) : (sheet.getRow(sheet.getCurrentRow() - 1)).getTrack(), // Track
row[2] != "" ? row[2] : (sheet.getRow(sheet.getCurrentRow() - 1)).getDirection(), // Direction
row[3] != "" ? row[3] : (sheet.getRow(sheet.getCurrentRow() - 1)).getMove(), // Move
row[4], // Circuit
Convert.ToInt32(row[5]), // Brake Location
Convert.ToInt32(row[6]), // Target Location
Convert.ToDouble(row[8]), // Worst Grade
Convert.ToDouble(row[9]), // Entry Speed
Convert.ToDouble(row[10]), // Overspeed
Convert.ToDouble(row[12]), // Acceleration
Convert.ToDouble(row[14]), // Reaction Time
Convert.ToDouble(row[16]), // Brake Rate
Convert.ToDouble(row[19]), // Runaway Accel
Convert.ToDouble(row[21]), // Propulsion Removal
Convert.ToInt32(row[23]), // Brake Build Up
Convert.ToInt32(row[25])); // Overhand Distance
sheet.addRow(curRow);
}
}
}
/// <summary>
/// Prüft, ob die Legasthenievermerke der Datenbank mit der Excel-Datei übereinstimmen.
/// </summary>
/// <param name="sheet">Das Excel-Sheet.</param>
/// <param name="kursAdapter">Der Kurs-Adapter.</param>
/// <param name="kurs">Die Zeile des aktuellen Kurses in der Datenbank.</param>
/// <param name="alleSchueler">Liste aller Schüler aus der Datenbank.</param>
private static void CheckLegastheniker(ExcelSheet sheet, KursTableAdapter kursAdapter, diNoDataSet.KursRow kurs, diNoDataSet.SchuelerKursDataTable alleSchueler)
{
//TODO: Methode ungetestet
using (FachTableAdapter fachAdapter = new FachTableAdapter())
{
var deutsch = fachAdapter.GetDataByKuerzel("D")[0];
var englisch = fachAdapter.GetDataByKuerzel("E")[0];
if (kurs.FachId == deutsch.Id || kurs.FachId == englisch.Id)
{
foreach (var schueler in alleSchueler)
{
var excelSchueler = sheet.Schueler.FirstOrDefault(
x => x.Id == schueler.SchuelerId
);
//falls der Schüler noch in der Excel-Datei drinsteht. Könnte ja sein, dass er schon ausgetreten o. ä. ist
if (excelSchueler != null)
{
var dbSchueler = new SchuelerTableAdapter().GetDataById(schueler.SchuelerId)[0];
if (excelSchueler.IsLegastheniker != (dbSchueler.LRSSchwaeche || dbSchueler.LRSStoerung))
{
excelSchueler.IsLegastheniker = (dbSchueler.LRSSchwaeche || dbSchueler.LRSStoerung);
sheet.SetLegasthenieVermerk(excelSchueler);
}
}
}
}
}
}
public OfficeOpenXml.ExcelRange GetRange(ExcelSheet excelSheet)
{
return excelSheet.ExcelWorksheet.Cells[this.RowStartPosition, this.ColumnStartPosition,
this.RowEndPosition, this.ColumnEndPosition];
}
public SheetStyle(ExcelRange excelRange, ExcelSheet excelSheet)
{
this.ExcelRange = excelRange;
this.ExcelSheet = excelSheet;
}
