private void BtnGetSheets_Click(object sender, EventArgs e)
{
if (ValidationLogic.InvalidFile(TbxFilePath))
{
ErrorActions.InvalidFile();
}
else
{
try
{
/* using form inputs open the file and loop through all sheets within
* the workbook, adding the name of each sheet encountered into a list
* box that the user can select from. */
string file = TbxFilePath.Text.ToString();
ExcelThread.OpenWorkbook(file);
ExcelThread.GetSheets();
LbxSheetNames.Items.Clear();
for (int i = 1; i <= ExcelThread.Worksheets.Count; i++)
{
Microsoft.Office.Interop.Excel.Worksheet sheet = ExcelThread.Worksheets[i];
LbxSheetNames.Items.Add(sheet.Name.ToString());
}
// make the next stage of the process/application visible.
GrpSheetAndHeader.Visible = true;
}
catch
{
ErrorActions.FailOpenFile();
}
}
}
/* this method is designed to allow the user to perform a soft reset and reset
* the application to the state direcly after pressin the 'get headers or
* columns' button. The intent is to allow mutiple agings of the same data
* making use of different headers on subsquent agings. */
private void ReAgeCurrentFileToolStripMenuItem_Click(object sender, EventArgs e)
{
// subset of get sheets button event.
ExcelThread.Reset();
string file = TbxFilePath.Text.ToString();
ExcelThread.OpenWorkbook(file);
ExcelThread.GetSheets();
// subset of the get headers button event.
if (ValidationLogic.NoItemSelected(LbxSheetNames))
{
ErrorActions.NoSheetSelected();
}
else
{
string sheetWithData = LbxSheetNames.SelectedItem.ToString();
ExcelThread.Worksheet = ExcelThread.Worksheets[sheetWithData];
if (ValidationLogic.NoColumnsFound(ExcelThread.Worksheet))
{
ErrorActions.NoColumnsFound();
}
else
{
BuildHeaders();
PrepForAging();
}
}
}
private void BtnRunAging_Click(object sender, EventArgs e)
{
if (ValidationLogic.DateInvalid(DtpAnchorDate.Value))
{
ErrorActions.DateInFuture();
}
else if (ValidationLogic.ColumnsNotSelected(LbxDataToAge, LbxDatesToAgeBy))
{
ErrorActions.ColumnsNotSelected();
}
else
{
// simple visibility changes are how the processing status is communicated.
BtnRunAging.Visible = false;
LblProcessing.Visible = true;
// ensure our accumilator is ready to receive data.
AgedData.Zero();
int dateColumn = LbxDatesToAgeBy.SelectedIndex + 1; // Excel arrays are not 0 based
int valueColumn = LbxDataToAge.SelectedIndex + 1; // Excel arrays are not 0 based
// since our data included a header we skip over the first row (i=2).
for (int i = 2; i <= ExcelThread.DataSet.Rows.Count; i++)
{
// value2 property doesn’t use the Currency and Date data types returns as Double
var rawDate = ExcelThread.DataSet.Cells[i, dateColumn].Value2;
var rawVal = ExcelThread.DataSet.Cells[i, valueColumn].Value2;
/* since we are not validating the existance of data within each cell in our dataset
* we need to check that we sucessfully returned non-nulls. If either the value
* within the 'to age' or the 'to age by' column for a given row are empty the
* entire row will be skipped. This application is not intended to clean data only
* process an existing proper dataset. */
if (rawDate is null || rawVal is null)
{
continue;
}
// getting the types of the data returned and also setting flags used in type checking.
Type dateType = rawDate.GetType();
Type valType = rawVal.GetType();
int bucket = -1;
float value = (float)-1.0;
/* by using the Value2 property we ensure that dates will be read in at Doubles. Given
* the stagering diversity in the way date strings are represented the decision was
* made to reject any dates not formated as a date, datetime, or number within excel. */
if (dateType.Name != "Double")
{
ErrorActions.InvalidDataType();
}
else
{
int daysPast = (DtpAnchorDate.Value - DateTime.FromOADate(rawDate)).Days;
bucket = daysPast / (int)30; // explicitly casting denominator at int because I am paranoid.
}
/* if the 'to age' value read in was a double (because of using Value2) we cast it
* to a float and we parse any string received into a float as well. */
if (valType.Name == "Double")
{
value = (float)rawVal;
}
else if (valType.Name == "String")
{
try
{
value = float.Parse(rawVal);
}
catch
{
ErrorActions.InvalidDataType();
}
}
else
{
ErrorActions.InvalidDataType();
}
// finally we validate our type check flags and update our accumlator
if (bucket == -1 || value == (float)-1.0)
{
ErrorActions.InvalidValue();
}
else
{
AgedData.AddAgedData(bucket, value);
}
}
// simple visibility and text changes are how the processing status is communicated.
LblProcessing.Text = "Complete"; // text was changed to eliminate a control from the form.
BtnView.Visible = true;
BtnSave.Visible = true;
}
}
private void BuildHeaders()
{
if (ValidationLogic.NoItemSelected(LbxSheetNames))
{
ErrorActions.NoSheetSelected();
}
else
{
// set the specific sheet by using the selected sheet name from the list box control.
string sheetWithData = LbxSheetNames.SelectedItem.ToString();
ExcelThread.Worksheet = ExcelThread.Worksheets[sheetWithData];
if (ValidationLogic.NoColumnsFound(ExcelThread.Worksheet))
{
ErrorActions.NoColumnsFound();
}
else
{
// Determine the number of columns within the sheet that are actually holding values
ExcelThread.DataSet = ExcelThread.Worksheet.UsedRange;
int colCount = ExcelThread.DataSet.Columns.Count;
/* the following two lines are required to ensure if the user changes the value
* of their answer for if headers are included and re-clicks the button the
* list box control does not 'grow' with the new selection. In addition columns
* to be used in the Aging process are selected by index, therefore the order of
* the list box controls must not be sorted. ORDER MATTERS!!!! */
LbxDataToAge.Items.Clear();
LbxDatesToAgeBy.Items.Clear();
/* if headers exist loop through all columns and add the value of the first
* row into two list box controls, what to age and what to age by. */
if (RbtHeadYes.Checked)
{
for (int i = 1; i <= colCount; i++)
{
Microsoft.Office.Interop.Excel.Range cellValue = ExcelThread.DataSet.Cells[1, i];
LbxDatesToAgeBy.Items.Add(cellValue.Value.ToString());
LbxDataToAge.Items.Add(cellValue.Value.ToString());
}
// make the next stage of the process/application visible.
GrpColumnAndAging.Visible = true;
}
/* if no headers exist add 'Column X' where X represents the column number,
* into two list box controls, what to age and what to age by. */
else if (RbtHeadNo.Checked)
{
for (int i = 1; i <= colCount; i++)
{
string columnNumber = "Column " + i.ToString();
LbxDatesToAgeBy.Items.Add(columnNumber);
LbxDataToAge.Items.Add(columnNumber);
}
// make the next stage of the process/application visible.
GrpColumnAndAging.Visible = true;
}
else
{
ErrorActions.HeaderNotAnswered();
}
}
}
}