public void run()
{
try
{
// Initial testing
tf = new TableFactory(filePath, startRow, startCol, tasksCompleted);
tfRunning = true;
tf.makeTable();
/*
* Console.WriteLine("Application Finished Running");
* exitCode = 0;
* appFinishedRunning = true;
* }
*
* public void test()
* { */
tfRunning = false;
tasksCompleted = tf.getTasksCompleted();
sheetName = tf.getNewSheetName();
lowerRange = tf.getStartRange();
upperRange = tf.getEndRange();
currentSheet = tf.getNewSheet();
noSystems = tf.getNumberOfSystems();
Console.WriteLine("Sheet Name = " + tf.getNewSheetName() + " Table range = " + tf.getStartRange() + ":" + tf.getEndRange());
//return 8;
tasksCompleted++;
//this.noScorers = findNoScorers();
if (noSystems < 1)
{
Console.WriteLine("##############################\n");
Console.WriteLine("Error: No/Invalid Number of Systems Assigned");
Console.WriteLine("Current Number of Systems Assigned: " + this.noSystems);
Console.WriteLine("\n##############################\n");
appFinishedRunning = true;
exitCode = -2;
}
else if (noScorers < 1)
{
Console.WriteLine("##############################\n");
Console.WriteLine("Error: No/Invalid Number of Scorers Assigned");
Console.WriteLine("Current Number of Scorers Assigned: " + this.noScorers);
Console.WriteLine("\n##############################\n");
appFinishedRunning = true;
exitCode = -3;
}
else if (!validateExcelFile())
{
Console.WriteLine("##############################\n");
Console.WriteLine("Error: Problem Opening file at the path assigned");
Console.WriteLine("Current File Path: " + this.filePath);
Console.WriteLine("\n##############################\n");
appFinishedRunning = true;
exitCode = -4;
}
else if (!validateRangeSelected())
{
Console.WriteLine("##############################\n");
Console.WriteLine("Error: No/Invalid range assigned");
Console.WriteLine("Current Range: (" + lowerRange + ":" + upperRange + ")");
Console.WriteLine("\n##############################\n");
appFinishedRunning = true;
exitCode = -5;
}
else
{
Console.WriteLine("All tests passed...\n");
tasksCompleted++;
//return 0;
int currRow = 2; //startRow + 1;
numberingColumn = 1; // startCol;
titleColumn = 2; // numberingColumn + 1;
Boolean endOfDocumentFound = false;
try
{
while (!endOfDocumentFound)
{
if ((MyValues.GetValue(currRow, numberingColumn) == null) && (MyValues.GetValue(currRow, titleColumn) == null))
{
endOfDocumentFound = true;
}
else
{
currRow++;
}
}
}
catch (System.IndexOutOfRangeException ex)
{
string err = ex.StackTrace;
}
endRow = currRow - 1;
currRow = 2;// startRow + 1;
Console.WriteLine("starting row = " + startRow);
Console.WriteLine("End row = " + endRow);
Console.WriteLine("curr row = " + currRow);
tasksCompleted++;
//return 4;
List <Heading> headings = new List <Heading>();
Heading currentHeading = null;
SubHeading currentSubHeading = null;
SubSubHeading currentSubSubHeading = null;
Requirement currentRequirement = null;
bool headingInUse = false;
bool subHeadingInUse = false;
bool subSubHeadingInUse = false;
Console.WriteLine("Finding all headings in sheet...");
// get headings in the sheet
while (currRow != endRow)
{
Location currentLocation = new Location(currRow + startRow - 1, numberingColumn + startCol - 1);
String title = "";
if (MyValues.GetValue(currRow, titleColumn) != null)
{
title = MyValues.GetValue(currRow, titleColumn).ToString();
}
if (MyValues.GetValue(currRow, numberingColumn) != null)
{
string cellData = MyValues.GetValue(currRow, numberingColumn).ToString();
int count = decimalPlacesCounter(cellData);
if (count == 0)
{
//Console.WriteLine("heading found '" + cellData + "'");
currentHeading = new Heading(cellData, currentLocation, title); // create new heading object from the current location
headings.Add(currentHeading);
headingInUse = true;
subHeadingInUse = false;
subSubHeadingInUse = false;
}
else if (count == 1)
{
//Console.WriteLine("Subheading found '" + cellData + "'");
currentSubHeading = new SubHeading(cellData, currentLocation, title); // create new heading object from the current location
currentHeading.addSubHeadingToList(currentSubHeading);
headingInUse = false;
subHeadingInUse = true;
subSubHeadingInUse = false;
}
else if (count == 2)
{
//Console.WriteLine("SubSubHeading found '" + cellData + "'");
currentSubSubHeading = new SubSubHeading(cellData, currentLocation, title); // create new heading object from the current location
currentSubHeading.addSubSubHeadingToList(currentSubSubHeading);
headingInUse = false;
subHeadingInUse = false;
subSubHeadingInUse = true;
}
else if (count == 3)
{
//Console.WriteLine("Requirement found '" + cellData + "'");
//currentSubSubHeading = new SubSubHeading2(cellData, currentLocation, title); // create new heading object from the current location
//currentSubHeading.addSubSubHeadingToList(currentSubSubHeading);
}
}
else if (MyValues.GetValue(currRow, titleColumn) != null)
{
string cellData = MyValues.GetValue(currRow, titleColumn).ToString();
Console.WriteLine("Cell on row " + currRow + " col " + numberingColumn + " = null" + " : Celldata in title colloim is " + cellData + " col " + titleColumn);
if (isRequirement(cellData))
{
currentRequirement = new Requirement(cellData, currentLocation, title);
int count = decimalPlacesCounter(cellData);
if (subSubHeadingInUse)
{
Console.WriteLine("subsub requirement found '" + cellData + "'");
currentSubSubHeading.addRequirementToList(currentRequirement);
}
else if (subHeadingInUse)
{
Console.WriteLine("sub requirement found '" + cellData + "'");
currentSubHeading.addRequirementToList(currentRequirement);
}
else if (headingInUse)
{
Console.WriteLine("heading requirement found '" + cellData + "'");
currentHeading.addRequirementToList(currentRequirement);
}
}
else if (cellData == "Average")
{
if (subSubHeadingInUse)
{
subSubHeadingInUse = false;
subHeadingInUse = true;
}
else if (subHeadingInUse)
{
subHeadingInUse = false;
headingInUse = true;
}
}
}
currRow++;
}
tasksCompleted++;
Console.Write("Calculating averages...");
// Calculate Heading Averages
for (int systemNumber = 1; systemNumber <= noSystems; systemNumber++)
{
for (int i = 0; i < headings.Count; i++)
{
currentHeading = headings[i];
List <SubHeading> subHeadings = currentHeading.getSubHeadings();
String currentHeadingAverage = "";
for (int j = 0; j < subHeadings.Count; j++)
{
currentSubHeading = subHeadings[j];
Location currentSubHeadingLocation = new Location(currentSubHeading.getLocation().getRow(), (currentSubHeading.getLocation().getColumn() + 1 + systemNumber));
String currentSubHeadingAverage_ssh = ""; // to accumlate average for number of subsubheadings
String currentSubHeadingAverage_reqs = ""; // to accumlate average for number of requirements
Boolean averageAlreadyAssigned = false;
if (currentSubHeading.hasSubSubHeadings() && currentSubHeading.hasRequirements())
{
List <JointHeading> j_list = tf.buildJointHeadingList(currentSubHeading);
List <Location> locations = new List <Location>();
int ssh_count = 0;
int req_count = 0;
for (int k = 0; k < j_list.Count; k++)
{
JointHeading jh = j_list[k];
if (jh.isSubSubHeading())
{
currentSubSubHeading = jh.getSubSubHeading();
int ssh_row = currentSubSubHeading.getLocation().getRow() + currentSubSubHeading.getRequirements().Count + 1;
int ssh_col = currentSubSubHeading.getLocation().getColumn() + 1 + systemNumber;
Location ssh_l = new Location(ssh_row, ssh_col);
locations.Add(ssh_l);
String ssh_data = currentSubSubHeading.assignAverageForRequirements(systemNumber);
writeToSingleCell(ssh_l, ssh_data, 0);
ssh_count++;
}
if (jh.isRequirement())
{
int ssh_row = jh.getLocation().getRow();
int ssh_col = jh.getLocation().getColumn() + 1 + systemNumber;
Location ssh_l = new Location(ssh_row, ssh_col);
locations.Add(ssh_l);
req_count++;
}
}
String averageOfRequirements = "";
for (int k = 0; k < locations.Count; k++)
{
Location newLoc = locations[k];
if (k == (locations.Count - 1))
{
averageOfRequirements = "AVERAGE(" + averageOfRequirements + newLoc.getExcelAddress() + ") ";
}
else
{
averageOfRequirements = averageOfRequirements + newLoc.getExcelAddress() + ", ";
}
}
averageOfRequirements = "=IFERROR(" + averageOfRequirements + "/10,\"\")";
int row = currentSubHeading.getLocation().getRow();// + j_list.Count + 1 + ssh_count;
int col = currentSubHeading.getLocation().getColumn() + 1 + systemNumber;
Location l = new Location(row, col);
String data = averageOfRequirements;
writeToSingleCell(l, data, 2);
averageAlreadyAssigned = true;
}
else if (currentSubHeading.hasSubSubHeadings())
{
List <SubSubHeading> subSubHeadings = currentSubHeading.getSubSubHeadings();
for (int k = 0; k < subSubHeadings.Count; k++)
{
currentSubSubHeading = subSubHeadings[k];
int row = currentSubSubHeading.getLocation().getRow() + currentSubSubHeading.getRequirements().Count + 1;
int col = currentSubSubHeading.getLocation().getColumn() + 1 + systemNumber;
Location l = new Location(row, col);
String data = currentSubSubHeading.assignAverageForRequirements(systemNumber);
writeToSingleCell(l, data, 0);
if (isValidRow(row) && currentSubSubHeading.hasRequirements())
{
if (k == (subSubHeadings.Count - 1))
{
currentSubHeadingAverage_ssh = currentSubHeadingAverage_ssh + l.getExcelAddress();
}
else
{
currentSubHeadingAverage_ssh = currentSubHeadingAverage_ssh + l.getExcelAddress() + " , ";
}
}
}
if (currentSubHeadingAverage_ssh != "")
{
currentSubHeadingAverage_ssh = "AVERAGE(" + currentSubHeadingAverage_ssh + ")";
currentSubHeadingAverage_ssh = "=IFERROR(" + currentSubHeadingAverage_ssh + "/10,\"\")";
}
}
else if (currentSubHeading.hasRequirements())
{
int row = currentSubHeading.getLocation().getRow() + currentSubHeading.getRequirements().Count + 1;
int col = currentSubHeading.getLocation().getColumn() + 1 + systemNumber;
Location l = new Location(row, col);
String data = currentSubHeading.assignAverageForRequirements(systemNumber);
writeToSingleCell(l, data, 0);
if (currentSubHeading.hasSubSubHeadings())
{
currentSubHeadingAverage_reqs = data; //"=IFERROR(" + l.getExcelAddress() + "/10,0)";
}
else
{
currentSubHeadingAverage_reqs = "=IFERROR(" + l.getExcelAddress() + "/10,0)";
}
}
String currentSubHeadingAverage = "";
if (currentSubHeading.hasSubSubHeadings())
{
currentSubHeadingAverage = currentSubHeadingAverage_ssh;
}
if (currentSubHeading.hasRequirements())
{
currentSubHeadingAverage = currentSubHeadingAverage_reqs;
}
if (currentSubHeading.hasSubSubHeadings() && currentSubHeading.hasRequirements())
{
//Console.WriteLine("************************************");
//Console.WriteLine("Special Case. It has both requirements and subsubheadings");
//Console.WriteLine("String is now: " + currentSubHeadingAverage_1 + " && " + currentSubHeadingAverage_2);
//Console.WriteLine("************************************");
currentSubHeadingAverage = formatNewAverageString(currentSubHeadingAverage_ssh, currentSubHeadingAverage_reqs);
//Console.WriteLine("String is now: " + currentSubHeadingAverage);
//Console.WriteLine("************************************");
}
//else
// Console.WriteLine("Sub heading " + currentSubHeading.getValue() + " has no reqs");
if (!averageAlreadyAssigned)
{
Location subHeadingAverageLocation = new Location(currentSubHeading.getLocation().getRow(), (currentSubHeading.getLocation().getColumn() + 1 + systemNumber));
writeToSingleCell(subHeadingAverageLocation, currentSubHeadingAverage, 0);
}
// && (currentSubHeading.hasRequirements() || currentSubHeading.hasSubSubHeadings()))
if (j == (subHeadings.Count - 1))
{
if (currentSubHeading.hasRequirements() || currentSubHeading.hasSubSubHeadings())
{
currentHeadingAverage = currentHeadingAverage + currentSubHeadingLocation.getExcelAddress();
}
else // if we are at the last sub heading in a heading, and that sub heading contains no reqs or subsubheadings, remomve the comma ',' from the end of the string
{
string[] arr = currentHeadingAverage.Split();
int length = arr.Length;
if (arr[length - 1] == ",")
{
arr[length - 1] = "";
}
currentHeadingAverage = "";
for (int idx = 0; idx < arr.Length; idx++)
{
String s = arr[idx];
currentHeadingAverage += s;
}
}
}
else
if (currentSubHeading.hasRequirements() || currentSubHeading.hasSubSubHeadings())
{
currentHeadingAverage = currentHeadingAverage + currentSubHeadingLocation.getExcelAddress() + " , ";
}
}
if (currentHeading.hasRequirements())
{
int row = currentHeading.getLocation().getRow() + currentHeading.getRequirements().Count + 1;
int col = currentHeading.getLocation().getColumn() + 1 + systemNumber;
Location l = new Location(row, col);
String data = currentHeading.assignAverageForRequirements(systemNumber);
Console.WriteLine("Writing " + data + " to location " + l.getAddress());
writeToSingleCell(l, data, 0);
currentHeadingAverage += l.getExcelAddress() + "/10"; // divide average of requirements by 10 so we don't get X00%
}
if (currentHeadingAverage != "")
{
currentHeadingAverage = "AVERAGE(" + currentHeadingAverage + ")";
}
currentHeadingAverage = "=IFERROR(" + currentHeadingAverage + ", 0)";
Location currentHeadingAverageLocation = new Location(currentHeading.getLocation().getRow(), (currentHeading.getLocation().getColumn() + 1 + systemNumber));
//Console.WriteLine("Heading location " + currentHeadingAverageLocation.getExcelAddress());
writeToSingleCell(currentHeadingAverageLocation, currentHeadingAverage, 0);
}
}
tasksCompleted++;
Console.WriteLine("DONE");
Console.WriteLine("Setting up scorer sheets...");
setUpScorerSheets(headings, 2);
Console.WriteLine("Scorer sheets are set up");
tasksCompleted++;
Console.Write("Applying Conditional Formatting...");
tf.conditionalFormat();
Console.WriteLine("Done");
tasksCompleted++;
Console.WriteLine("Application Finished Running");
exitCode = 0;
appFinishedRunning = true;
}
}
catch (Exception e) {
Console.WriteLine("Exception Caught");
DateTime today = DateTime.Now;
String time = today.Day.ToString() + today.Month.ToString() + today.Year.ToString() + "_" + today.Hour.ToString() + today.Minute.ToString() + today.Second.ToString();
String filename = "error_log_funct_gen" + time + ".txt";
String fileContent = e.Message.ToString() + Environment.NewLine;
System.IO.File.WriteAllText(filename, fileContent);
}
}
public void setUpScorerSheets(List <Heading> headings, int space)
{
// first paste the data
Console.WriteLine("Pasting Data...");
int columnLength = noSystems + 3 + space;
int col = startCol;
int row = 1;// startRow;
String lowerRange = getExcelColumnName(currentSheet.Range[this.lowerRange].Column);
String upperRange = getExcelColumnName(currentSheet.Range[this.upperRange].Column);
for (int scorer = 1; scorer <= noScorers; scorer++)
{
col = col + columnLength;
Console.WriteLine("Pasting to Column: " + col + "\n Home Range: (" + lowerRange + ":" + upperRange + ")");
Excel.Range r1 = currentSheet.get_Range(lowerRange + ":" + upperRange);
Excel.Range r2 = currentSheet.get_Range(getExcelColumnName(col) + ":" + getExcelColumnName(col));
try
{
r1.Copy(r2);
}
catch (System.Runtime.InteropServices.COMException e)
{
Console.WriteLine(e.StackTrace);
}
Location startRange = new Location(row, col);
Location endRange = new Location(row + 3, col + noSystems + 2);
writeToMultipleCells(startRange, endRange, "", 0, 0, 0, 0, true, false, true, System.Drawing.Color.LightGray, System.Drawing.Color.Black, Excel.XlHAlign.xlHAlignCenter, Excel.XlVAlign.xlVAlignCenter, "Calibri", 16);
endRange = new Location(row, col);
writeToSingleCell(endRange, scorer.ToString(), 0, 0, 0, 0, true, false, true, System.Drawing.Color.DarkSlateGray, System.Drawing.Color.White, Excel.XlHAlign.xlHAlignCenter, Excel.XlVAlign.xlVAlignCenter, "Calibri", 16); // write scorer number to make it easier to read on spreadsheet
endRange = new Location(row, col + 1);
writeToMultipleCells(startRange, endRange, "", 0, 0, 0, 0, true, false, true, System.Drawing.Color.DarkSlateGray, System.Drawing.Color.White, Excel.XlHAlign.xlHAlignCenter, Excel.XlVAlign.xlVAlignCenter, "Calibri", 16);
writeToSingleCell(endRange, "FUNCTIONALITY REQUIREMENTS SCORESHEET", 0, 0, 0, 0, true, false, true, System.Drawing.Color.DarkSlateGray, System.Drawing.Color.White, Excel.XlHAlign.xlHAlignCenter, Excel.XlVAlign.xlVAlignCenter, "Calibri", 16);
startRange = new Location(row, col + 2);
writeToSingleCell(startRange, "<Enter name>", 0, 0, 0, 0, false, false, false, System.Drawing.Color.LightGray, System.Drawing.Color.Black, Excel.XlHAlign.xlHAlignLeft, Excel.XlVAlign.xlVAlignCenter, "Calibri", 16);
//See worksheet "INSTRUCTIONS (REQTS & ITT)" for full instructions on how to use this scoresheet
endRange = new Location(row + 2, col + 1);
writeToSingleCell(endRange, "See worksheet \"INSTRUCTIONS (REQTS & ITT)\" for full instructions on how to use this scoresheet", 0, 0, 0, 0, true, false, true, System.Drawing.Color.LightGray, System.Drawing.Color.Black, Excel.XlHAlign.xlHAlignLeft, Excel.XlVAlign.xlVAlignBottom, "Calibri", 12);
}
Console.WriteLine("Data Pasted");
Console.WriteLine("Assigning scorer table values to main...");
for (int i = 0; i < headings.Count; i++)
{
Heading heading = headings[i];
// handle case where a heading has a direct set of requirements
if (heading.hasRequirements())
{
List <Requirement> reqs = heading.getRequirements();
for (int k = 0; k < reqs.Count; k++)
{
Requirement inReq = reqs[k];
int homeRow = inReq.getLocation().getRow();
int homeCol = inReq.getLocation().getColumn();
homeCol = homeCol + 2;
for (int l = 1; l <= noSystems; l++)
{
String average = "";
int indexCol = homeCol + columnLength;
for (int m = 1; m <= noScorers; m++)
{
Location loc = new Location(homeRow, indexCol);
if (m != noScorers)
{
average = average + loc.getExcelAddress() + ", ";
}
else
{
average = average + loc.getExcelAddress() + ")";
}
indexCol += columnLength;
}
average = "=IFERROR(AVERAGE(" + average + ", \"\")";
Location cell = new Location(homeRow, homeCol);
writeToSingleCell(cell, average, 0);
homeCol = homeCol + 1;
// make string
}
}
}
List <SubHeading> subHeadings = heading.getSubHeadings();
for (int j = 0; j < subHeadings.Count; j++)
{
SubHeading subHeading = subHeadings[j];
if (subHeading.hasRequirements())
{
List <Requirement> reqs = subHeading.getRequirements();
for (int k = 0; k < reqs.Count; k++)
{
Requirement inReq = reqs[k];
int homeRow = inReq.getLocation().getRow();
int homeCol = inReq.getLocation().getColumn();
homeCol = homeCol + 2;
for (int l = 1; l <= noSystems; l++)
{
String average = "";
int indexCol = homeCol + columnLength;
for (int m = 1; m <= noScorers; m++)
{
Location loc = new Location(homeRow, indexCol);
if (m != noScorers)
{
average = average + loc.getExcelAddress() + ", ";
}
else
{
average = average + loc.getExcelAddress() + ")";
}
indexCol += columnLength;
}
average = "=IFERROR(AVERAGE(" + average + ", \"\")";
Location cell = new Location(homeRow, homeCol);
writeToSingleCell(cell, average, 0);
homeCol = homeCol + 1;
// make string
}
}
}
if (subHeading.hasSubSubHeadings())
{
List <SubSubHeading> subSubHeadings = subHeading.getSubSubHeadings();
for (int w = 0; w < subSubHeadings.Count; w++)
{
SubSubHeading req = subSubHeadings[w];
if (req.hasRequirements())
{
List <Requirement> reqs = req.getRequirements();
for (int k = 0; k < reqs.Count; k++)
{
Requirement inReq = reqs[k];
int homeRow = inReq.getLocation().getRow();
int homeCol = inReq.getLocation().getColumn();
homeCol = homeCol + 2;
for (int l = 1; l <= noSystems; l++)
{
String average = "";
int indexCol = homeCol + columnLength;
for (int m = 1; m <= noScorers; m++)
{
Location loc = new Location(homeRow, indexCol);
if (m != noScorers)
{
average = average + loc.getExcelAddress() + ", ";
}
else
{
average = average + loc.getExcelAddress() + ")";
}
indexCol += columnLength;
}
average = "=IFERROR(AVERAGE(" + average + ", \"\")";
Location cell = new Location(homeRow, homeCol);
writeToSingleCell(cell, average, 0);
homeCol = homeCol + 1;
// make string
}
}
}
}
}
}
}
Console.WriteLine("Scorer table values assigned");
}
public void addSubHeadingToList(SubHeading s)
{
this.subHeadings.Add(s);
}