public object WiggleRoom(BL.AnalysisObject a, int centerRowNum, int centerColNum, int failCount)
{
object tempVal = null;
if (failCount == 3)
{
return(tempVal);
}
try
{
tempVal = a.allTheData[centerRowNum, centerColNum];
}
catch (System.IndexOutOfRangeException e)
{
if (EL.singleExcel.GetExcelColumnName(centerColNum - 1) != "A")
{
centerColNum--;
failCount++;
WiggleRoom(a, centerRowNum, centerColNum, failCount);
}
else
{
centerColNum++;
failCount++;
WiggleRoom(a, centerRowNum, centerColNum, failCount);
}
}
return(tempVal);
}
public BL.AnalysisObject basicVars(BL.AnalysisObject a, EL.singleExcel thisExcel)
{
// Get Some Generic, Untested Data About The Page From Excel
//var theTotalColumns = thisExcel.excelRange.Columns.Count;
//var theTotalRows = thisExcel.excelRange.Rows.Count;
a.colCount = thisExcel.excelRange.Columns.Count;
a.rowCount = thisExcel.excelRange.Rows.Count;
a.allTheData = (object[, ])thisExcel.excelRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault); // Everything in UsedRange
var constantCells = thisExcel.excelRange.SpecialCells(Excel.XlCellType.xlCellTypeConstants, Type.Missing); // Everything with a constant in it in UsedRange
// Do a little preprossing on that Data
a.splitUpAddresses = Regex.Split(constantCells.Address, @"(?:\,|\:)");
a.splitUpAddresses = a.splitUpAddresses.Where(s => !string.IsNullOrWhiteSpace(s)).Distinct().ToArray(); // Create an Array of the Constant Addresses in the Order The Were Found
var count = a.splitUpAddresses.Length;
string[] tempArr = new string[count];
Array.Copy(a.splitUpAddresses, tempArr, count); // Create a Second Array that is Sorted
Array.Sort(tempArr); // Create a Second Array that is Sorted
string[] firstAddress = Regex.Split(tempArr[0], @"(?:\$)");
int x = 0;
while (firstAddress[1].Length != 1)
{
firstAddress = Regex.Split(tempArr[x], @"(?:\$)");
x++;
}
a.startCol = firstAddress[1]; // Get Column of First Address, in case it isn't A
firstAddress = Regex.Split(a.splitUpAddresses[0], @"(?:\$)");
a.startRow = firstAddress[2]; // Get Row of First Address, in case it isn't 1
return(a);
}
public static List <string> getRowData(BL.AnalysisObject a, int avgColCount, int focusRow, Regex rPattern, List <string> terms)
{
if (a.colCount >= avgColCount * 1.5)
{
a.colCount = avgColCount;
}
for (int x = 1; x < a.colCount + 1; x++)
{
try
{
if (a.allTheData[focusRow, x] != null)
{
string tempVar = a.allTheData[focusRow, x].ToString().ToLower().Trim();
if (tempVar != null && !tempVar.Equals("") && !rPattern.IsMatch(tempVar))
{
terms.Add(tempVar);
}
}
}
catch (System.NullReferenceException e)
{
}
}
return(terms);
}
public int ExcelWorkSheetNullCheck(BL.AnalysisObject a, String[] splitUpAddresses, int lessThis)
{
int count = splitUpAddresses.Length;
string theVal = null;
int centerRowNum = 0;
while (theVal == null || theVal.Equals(""))
{
var thisAddress = Regex.Split(splitUpAddresses[count - lessThis], @"(?:\$)");
int centerColNum = EL.singleExcel.ExcelColumnNameToNumber(thisAddress[1], a.startCol);
centerRowNum = Convert.ToInt32(thisAddress[2]);
int failCount = 0;
var tempVal = WiggleRoom(a, centerRowNum, centerColNum, failCount);
if (tempVal != null)
{
theVal = a.allTheData[centerRowNum, centerColNum].ToString().Trim();
}
lessThis++;
}
return(centerRowNum);
}
public void runTriggers(int fileCount, BL.AnalysisObject a, int avgRowCount, int avgColCount, out int avgRowCounta, out int avgColCounta)
{
/**
* If the Row count is outsized for the data set, then it will be analyzed against two potential alternative routes to account for Excel error(s)
**/
int centerRowNum = 0;
if (a.rowCount > 65000 || a.rowCount > (avgRowCount * 1.5))
{
Console.WriteLine("Total Rows: " + a.rowCount);
if (a.rowCount > 65000) // Most common error is that UsedRange will return back over 65k rows.
{
Console.WriteLine("Option 1");
// As this is the default error response, it is assumed that number is wrong and skipped, thus the value 2 sent to ExcelWorkSheetNullCheck
centerRowNum = MF.ExcelWorkSheetNullCheck(a, a.splitUpAddresses, 2);
}
else // It is also possible that the UsedRange is technically correct, but there is bad data (there are 30 rows of data and a stray key on row 300).
{
Console.WriteLine("Option 2");
// As the row count may simply be significantly larger than the average, this version is more forgiving by sending value 1
centerRowNum = MF.ExcelWorkSheetNullCheck(a, a.splitUpAddresses, 1);
}
Console.WriteLine("Row 1 " + centerRowNum);
// ExcelRowChecker samples the object for data to determine the actual used range
centerRowNum = MF.ExcelRowChecker(a, centerRowNum, a.colCount, avgColCount);
Console.WriteLine("Row 2 " + centerRowNum);
}
else
{
// If the number is not unusually large, we only use ExcelRowChecker to sample
Console.WriteLine("Option 3");
Console.WriteLine("Total Rows: " + a.rowCount);
centerRowNum = MF.ExcelRowChecker(a, a.rowCount, a.colCount, avgColCount);
Console.WriteLine("Row 1 " + centerRowNum);
}
MF.MakeAverage(fileCount, avgRowCount, centerRowNum, avgColCount, a.colCount, out avgRowCounta, out avgColCounta);
Console.WriteLine("Avg Row " + avgRowCounta);
a.rowCount = centerRowNum;
}
public int ExcelRowChecker(BL.AnalysisObject a, int centerRowNum, int theTotalColumns, int avgColCount)
{
int falseRowCount = 0;
int altRowCount = 0;
int upperAltRowCount = 0;
int goodRow = 0;
double thePiece = .05;
double checkRowRange = centerRowNum * thePiece;
if (a.colCount >= avgColCount * 1.5)
{
a.colCount = avgColCount;
}
for (int y = centerRowNum + 10; y > centerRowNum - checkRowRange; y--)
{
Double emptyCellCount = 0;
for (int x = 1; x < a.colCount + 1; x++)
{
Double x2 = Convert.ToDouble(x);
try
{
if (a.allTheData[y, x] == null || a.allTheData[y, x].ToString().Trim().Equals(""))
{
emptyCellCount++;
}
}
catch (System.IndexOutOfRangeException e)
{
emptyCellCount++;
}
if (x >= (a.colCount * .7) && emptyCellCount / x2 > .7)
{
if (goodRow == 0)
{
falseRowCount++;
}
else
{
altRowCount++;
}
break;
}
if (x == a.colCount)
{
goodRow++;
}
}
if (falseRowCount > 1 && goodRow > 3)
{
if (falseRowCount + upperAltRowCount < 5 || (falseRowCount < 10 && upperAltRowCount < 10 && goodRow > 5))
{
centerRowNum = centerRowNum + 50;
centerRowNum = ExcelRowChecker(a, centerRowNum, a.colCount, avgColCount);
return(centerRowNum);
}
else if (falseRowCount != 10)
{
centerRowNum = centerRowNum + 10 - falseRowCount + upperAltRowCount;
return(centerRowNum);
}
break;
}
else if (y - 1 <= Math.Ceiling(centerRowNum - checkRowRange))
{
thePiece = thePiece + .05;
checkRowRange = centerRowNum * thePiece;
falseRowCount = falseRowCount + altRowCount;
if (goodRow > 0)
{
upperAltRowCount = upperAltRowCount + altRowCount;
}
altRowCount = 0;
}
if (y <= 0)
{
if (goodRow != 0)
{
break;
}
else
{
centerRowNum = 0;
return(centerRowNum);
}
}
}
if (falseRowCount < 10)
{
Console.WriteLine("Odd Result");
}
centerRowNum = centerRowNum + 10 - falseRowCount;
return(centerRowNum);
}
public static int readRowData(BL.AnalysisObject a, int avgColCount, Regex rPattern, List <string> terms, List <string> betterTerms, List <string> secondaryTerms, List <string> words, List <string> looseWords, int filesMatched)
{
if (a.colCount >= avgColCount * 1.5)
{
a.colCount = avgColCount;
}
int matchCountLimiter = Convert.ToInt32(Math.Ceiling(Math.Min(words.Count * .5, a.colCount * .7)));
for (int y = 1; y < a.rowCount + 1; y++)
{
Double emptyCellCount = 0;
bool primary = false;
int matchCount = 0;
for (int x = 1; x < a.colCount + 1; x++)
{
try
{
if (a.allTheData[y, x] == null || a.allTheData[y, x].ToString().Trim().Equals(""))
{
emptyCellCount++;
}
else
{
string tempVar = a.allTheData[y, x].ToString().ToLower().Trim();
if (rPattern.IsMatch(tempVar))
{
if (words[0].Equals(tempVar))
{
primary = true;
}
matchCount++;
}
}
}
catch (System.NullReferenceException e)
{
emptyCellCount++;
}
}
if ((matchCount >= matchCountLimiter || primary) && matchCount != 0)
{
getRowData(a, avgColCount, y, rPattern, terms);
if ((matchCount >= matchCountLimiter && primary) && words.Count != 1)
{
getRowData(a, avgColCount, y, rPattern, betterTerms);
}
if ((matchCount >= Math.Ceiling(matchCountLimiter * .7) && !primary) && words.Count != 1)
{
getRowData(a, avgColCount, y, rPattern, secondaryTerms);
}
filesMatched++;
return(filesMatched);
}
}
return(filesMatched);
}
