public Boolean DuplicateColumnNames() { using (ReadAndStreamFile()) { reader.Read(); { var ColumnsNames = Enumerable.Range(0, reader.FieldCount).Select(i => reader.GetValue(i)).ToList(); var excel = new ExcelDataReaderFile(); errorDetected = false; //looping through column names for (int columnIndexNumber = 0; columnIndexNumber < ColumnsNames.Count; columnIndexNumber++) { //looping through rows for (int columnIndexNum = 0; columnIndexNum < ColumnsNames.Count; columnIndexNum++) { var cellAddressA = excel.GetAddress(columnIndexNumber, 0); var cellAddressB = excel.GetAddress(columnIndexNum, 0); //so lets say we are talking about column A and Column C, if ColumnA and Column C are not null if (ColumnsNames[columnIndexNumber] != null && ColumnsNames[columnIndexNum] != null) { //so if column A were never put against column C to check if they match if (ColumnsNames[columnIndexNumber] != ColumnAlreadyMatchedA && ColumnsNames[columnIndexNum] != ColumnAlreadyMatchedB) { //check every column against eachother apart from checking it with itself ( say if columnIndexNumber and columnIndexNum are the same then it emans we are trying to match it with itself) // any column say ColumnsNames[columnIndexNumber] is column A already matched with B, C and D so going backwards doesnt check column B,C,D against column A if (ColumnsNames[columnIndexNumber] == ColumnsNames[columnIndexNum] && columnIndexNumber != columnIndexNum) { // and so Column a is given "ColumnAlreadyMatchedA"name ColumnAlreadyMatchedA = ColumnsNames[columnIndexNumber]; ColumnAlreadyMatchedB = ColumnsNames[columnIndexNum]; Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddressA} with column name {ColumnsNames[columnIndexNumber]} matches {cellAddressB} with column name {ColumnsNames[columnIndexNum]}"); errorDetected = true; } else { // errorDetected = false; } } } } } } reader.Dispose(); reader.Close(); return(errorDetected); } }
public bool InvalidCellData() { using (ReadAndStreamFile()) { var conf = new ExcelDataSetConfiguration { ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = true } }; var dataSet = reader.AsDataSet(conf); var dataTable = dataSet.Tables[0]; var rows = Enumerable.Range(0, reader.FieldCount).Select(i => reader.Read()).ToArray(); errorDetected = false; for (var ColumnIndexNumber = 0; ColumnIndexNumber < dataTable.Columns.Count; ColumnIndexNumber++) { for (var RowIndexNumber = 0; RowIndexNumber < dataTable.Rows.Count; RowIndexNumber++) { //RowIndexNumber is row number starts from index number 0 //ColumnIndexNumber is column number starts from index number 0 var data = dataTable.Rows[RowIndexNumber][ColumnIndexNumber]; var excel = new ExcelDataReaderFile(); var cellAddress = excel.GetAddress(ColumnIndexNumber, RowIndexNumber + 1); if (data.ToString().Length != 0) { if (data.GetType() == reader.GetFieldType(ColumnIndexNumber) && data.ToString().Length > rowDataSizeLimit) { Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} is {data.ToString().Length} characters long and exceeds {rowDataSizeLimit} character cell contents limit. Supply valid cell contents."); errorDetected = true; } else if (data.ToString().Length <= rowDataSizeLimit & data.GetType() != reader.GetFieldType(ColumnIndexNumber)) { Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} data {data} data type {data.GetType()} does not match data type of column data {reader.GetFieldType(ColumnIndexNumber)}. Supply data with a consistent data type."); errorDetected = true; } else if (data.ToString().Length > rowDataSizeLimit & data.GetType() != reader.GetFieldType(ColumnIndexNumber)) { Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} is {data.ToString().Length} characters long and exceeds {rowDataSizeLimit} character cell contents limit. Supply valid cell contents. Data type {data.GetType()} does not match data type of column data {reader.GetFieldType(ColumnIndexNumber)}. Supply data with a consistent data type."); errorDetected = true; } else { } } //else //{ // Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} is empty. Supply valid cell data."); // errorDetected = false; //} } } reader.Dispose(); reader.Close(); return(errorDetected); } }
public Boolean InvalidColumnNames() { using (ReadAndStreamFile()) { reader.Read(); { int counter = 0; var ColumnsNames = Enumerable.Range(0, reader.FieldCount).Select(i => reader.GetValue(i)).ToList(); if (ColumnsNames.Count != 0 && reader.Read() == true) { errorDetected = false; for (int columnNumber = 0; columnNumber < ColumnsNames.Count; columnNumber++) { var excel = new ExcelDataReaderFile(); var cellAddress = excel.GetAddress(counter, 0); counter += 1; if (ColumnsNames[columnNumber] != null && ColumnsNames[columnNumber].ToString().Length > columnNameSizeLimit) { Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} is {columnNumber.ToString().Length} characters long and exceeds {columnNameSizeLimit} character column name limit. Supply a valid column name."); errorDetected = true; } else if (ColumnsNames[columnNumber] == null) { Logger.Error($"[{GetFileName(file)}]{reader.Name}!{cellAddress} is empty. Supply a valid column name."); errorDetected = true; } else { } continue; } } else { Logger.Error($"[{GetFileName(file)}]{reader.Name} is empty and cannot be validated. Supply a non-empty file."); errorDetected = true; }; } reader.Dispose(); reader.Close(); return(errorDetected); } }