/// <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; }