public void readCinderellas()
{
//Values for the progress bar.
//int overallPercent = 0;
//int percentCounter = 0;
//Creates a new array of objects called particulars where the values for a row are stored for analysis.
object[] particulars_C = new object[7];
//The following object Value_C will be defined later.
object Value_C;
//Creates a new query to move data into the database.
SQL_Queries Cinderella_Add = new SQL_Queries();
//Creates an array of strings for errors.
string[] errors = { "No Good_C" };
//Creates a new open file dialogue box called FileToBeRead.
OpenFileDialog fileToBeRead_C = new OpenFileDialog();
//Opens a new instance of Excel.
ExcelReader.Application intermediary_C = new ExcelReader.Application();
//Additional filtering for FileToBeRead.
fileToBeRead_C.Filter = "Worksheets (*.xls;*.xlsx;*.xlsb;*.xlsm) | *.xls; *.xlsx; *.xlsb; *.xlsm";
fileToBeRead_C.Multiselect = false;
DialogResult selection = fileToBeRead_C.ShowDialog();
//This loop checks that the selected file is an existing Excel file.
if (selection == DialogResult.OK)
{
//Checks to make sure file exists.
if (!(System.IO.File.Exists(fileToBeRead_C.FileName)))
{
MessageBox.Show("Error: File Not Found.");
return;
}
//Places the path of the file into a string.
string Name_of_File = fileToBeRead_C.FileName;
//Places the extension of a file into a string.
string Extension_of_File_C = Path.GetExtension(Name_of_File);
//Checks to make sure that file selected is an Excel file.
if (Extension_of_File_C == ".xls")
{
}
else if (Extension_of_File_C == ".xlsb")
{
}
else if (Extension_of_File_C == ".xlsm")
{
}
else if (Extension_of_File_C == ".xlsx")
{
}
else
{
MessageBox.Show("Error: Invalid file Type.");
return;
}
//closetBook will refer to the entire workbook selected to be read.
ExcelReader.Workbook closetBook_C = intermediary_C.Workbooks.Open(fileToBeRead_C.FileName, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, true);
//closetSheet will refer to the data sheet where the Cinderellas's infomation is stored.
ExcelReader.Worksheet closetSheet_C = closetBook_C.Worksheets.get_Item(1);
//closetTuples refers to only the used range of cells in the Excel sheet.
ExcelReader.Range closetTuples_C = closetSheet_C.UsedRange;
//The integer index will refer to the number of a cell in a column that is currently being processd.
int index = 0;
//The following variables Date and Time will represent the Date and Time of an Appointment.
DateTime Appointment_DateTime = new DateTime();
string ApptD;
string ApptT;
int NumRows = closetTuples_C.Rows.Count;
//This loop begins reading values into the database.
for (int rows = 2; rows <= closetTuples_C.Rows.Count; rows++)
{
//This loop begins to read values for each specific column.
for (int columns = 1; columns <= closetTuples_C.Columns.Count; columns++)
{
//The object Value_C is now assigned to be the value of a certain cell in the closetSheet Excel sheet.
Value_C = closetSheet_C.Cells[rows, columns].Value;
//The integer correctIndex is assigned to a cell with a special character that needs to be replaced.
int correctIndex;
//Checks the first column for any cells with special characters and if it finds one, corrects it.
if (columns == 1)
{
particulars_C[index] = Value_C.ToString().Trim();
if (particulars_C[index].ToString().Contains("'"))
{
correctIndex = particulars_C[index].ToString().IndexOf("'");
particulars_C[index] = particulars_C[index].ToString().Replace("'", "''");
Console.WriteLine(particulars_C[index].ToString());
closetSheet_C.Cells[rows, 1] = particulars_C[index];
}
index++;
}
//Checks the second column for any cells with special characters and if it finds one, corrects it.
else if (columns == 2)
{
particulars_C[index] = Value_C.ToString().Trim();
if (particulars_C[index].ToString().Contains("'"))
{
correctIndex = particulars_C[index].ToString().IndexOf("'");
particulars_C[index] = particulars_C[index].ToString().Replace("'", "''");
Console.WriteLine(particulars_C[index].ToString());
closetSheet_C.Cells[rows, 2] = particulars_C[index];
}
index++;
}
//Checks the third column for any cells with special characters and if it finds one, corrects it.
else if (columns == 3)
{
particulars_C[index] = Value_C.ToString().Trim();
if (particulars_C[index].ToString().Contains("'"))
{
correctIndex = particulars_C[index].ToString().IndexOf("'");
particulars_C[index] = particulars_C[index].ToString().Replace("'", "''");
Console.WriteLine(particulars_C[index].ToString());
closetSheet_C.Cells[rows, 3] = particulars_C[index];
}
index++;
}
//Checks the fourth column for any cells with special characters and if it finds one, corrects it.
else if (columns == 4)
{
particulars_C[index] = Value_C.ToString().Trim();
if (particulars_C[index].ToString().Contains("'"))
{
correctIndex = particulars_C[index].ToString().IndexOf("'");
particulars_C[index] = particulars_C[index].ToString().Replace("'", "''");
Console.WriteLine(particulars_C[index].ToString());
closetSheet_C.Cells[rows, 4] = particulars_C[index];
}
index++;
}
//Checks the fifth column for any cells with special characters and if it finds one, corrects it.
else if (columns == 5)
{
particulars_C[index] = Value_C.ToString().Trim();
if (particulars_C[index].ToString().Contains("'"))
{
correctIndex = particulars_C[index].ToString().IndexOf("'");
particulars_C[index] = particulars_C[index].ToString().Replace("'", "''");
Console.WriteLine(particulars_C[index].ToString());
closetSheet_C.Cells[rows, 5] = particulars_C[index];
}
index++;
}
//Formats the sixth column into the Date and Time columns.
else if (columns == 6)
{
particulars_C[index] = Value_C.ToString().Trim();
Appointment_DateTime = Convert.ToDateTime(Value_C);
ApptD = Appointment_DateTime.Date.ToString();
ApptT = Appointment_DateTime.TimeOfDay.ToString();
//Potentially Obsolete Code
//The Value for particulars[index] is then split into two strings at the ' '.
//These two strings are then placed into an array of strings called DateandTime
//string[] DateandTime = particulars_C[index].ToString().Trim().Split(' ');
//Potentially Obsolete Code
//The string DateandTime[0], which contains the appointment date, is now placed in the correct spots.
//Appointment_DateTime = Convert.ToDateTime(Value_C);
//Potentially Obsolete Code
/*Console.WriteLine(particulars_C[index].ToString());
closetSheet_C.Cells[rows, 6] = particulars_C[index];
index++;*/
//Potentially Obsolete Code
//The string DateandTime[1], which contains the appointment time, is now placed in the correct spots.
//Appointment_Time = Convert.ToDateTime(DateandTime[1]);
//Potentially Obsolete Code
/*Console.WriteLine(particulars_C[index].ToString());
closetSheet_C.Cells[rows, 7] = particulars_C[index];
index++;*/
}
}
index = 0;
ApptD = Appointment_DateTime.Date.ToString();
ApptT = Appointment_DateTime.TimeOfDay.ToString();
//The query will finally begin to add the data from the Excel sheet to the database.
Cinderella_Add.addCinderellaAndReferral(closetSheet_C.Cells[rows, 3].Value.ToString() + " " + closetSheet_C.Cells[rows, 4].Value.ToString(), closetSheet_C.Cells[rows, 5].Value.ToString(), closetSheet_C.Cells[rows, 1].Value.ToString(), closetSheet_C.Cells[rows, 2].Value.ToString(), ApptD, ApptT, "");
//Code for progress bar.
}
//Closes out of Excel.
closetBook_C.Close(false, Type.Missing, Type.Missing);
intermediary_C.Quit();
Marshal.FinalReleaseComObject(intermediary_C);
Marshal.FinalReleaseComObject(closetBook_C);
Marshal.FinalReleaseComObject(closetSheet_C);
Marshal.FinalReleaseComObject(closetTuples_C);
GC.Collect();
//Code for Pop up Window which tells when the import is completed.
Import_Wait popup = new Import_Wait();
DialogResult dialogresult = popup.ShowDialog();
if (dialogresult == DialogResult.OK)
{
popup.Dispose();
}
}
}
//readGodmothers
//reads the excel file containing the godmother information into the db
//Input: the excel file containg the godmother information
//Output: godmother's have been added to the db
//precondition: the godmother excel file is in the proper format and the db is connectable and is in a working state
//postcondition: the godmother's have been correctly added to the db
//IMPORTANT NOTE: Excel rows and columns start at the value 1.
public void readGodmothers()
{
//Values for the progress bar.
//int overallPercent = 0;
//int percentCounter = 0;
//Creates a new array of objects called particulars_G where the values for a row are stored for analysis.
object[] particulars_G = new object[8];
//This string is specifically for the use of analysing the Fairy Godmother Roles for a shift.
string particulars_Shift;
//An integer that represents a shift.
int shiftID = 0;
//An integer that represents a role.
int roleID = 0;
//The following object Value_C will be defined later.
object Value_G;
//Creates a new query to move data into the database.
SQL_Queries Godmother_Add = new SQL_Queries();
//Creates an array of strings for errors.
string[] errors = { "No Good_G" };
//Creates a new open file dialogue box called FileToBeRead.
OpenFileDialog fileToBeRead_G = new OpenFileDialog();
//Opens a new instance of Excel.
ExcelReader.Application intermediary_G = new ExcelReader.Application();
//Additional filtering for FileToBeRead.
fileToBeRead_G.Filter = "Worksheets (*.xls;*.xlsx;*.xlsb;*.xlsm) | *.xls; *.xlsx; *.xlsb; *.xlsm";
fileToBeRead_G.Multiselect = false;
DialogResult selection = fileToBeRead_G.ShowDialog();
//This loop checks that the selected file is an existing Excel file.
if (selection == DialogResult.OK)
{
//Checks to make sure file exists.
if (!(System.IO.File.Exists(fileToBeRead_G.FileName)))
{
MessageBox.Show("Error: File Not Found.");
return;
}
//Places the path of the file into a string.
string Name_of_File = fileToBeRead_G.FileName;
//Places the extension of a file into a string.
string Extension_of_File_G = Path.GetExtension(Name_of_File);
//Checks to make sure that file selected is an Excel file.
if (Extension_of_File_G == ".xls")
{
}
else if (Extension_of_File_G == ".xlsb")
{
}
else if (Extension_of_File_G == ".xlsm")
{
}
else if (Extension_of_File_G == ".xlsx")
{
}
else
{
MessageBox.Show("Error: Invalid file Type.");
return;
}
//closetBook will refer to the entire workbook selected to be read.
ExcelReader.Workbook closetBook_G = intermediary_G.Workbooks.Open(fileToBeRead_G.FileName, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, true);
//closetSheet will refer to the data sheet where the Cinderellas's infomation is stored.
ExcelReader.Worksheet closetSheet_G = closetBook_G.Worksheets.get_Item(1);
//closetTuples refers to only the used range of cells in the Excel sheet.
ExcelReader.Range closetTuples_G = closetSheet_G.UsedRange;
//The integer index will refer to the number of a cell in a column that is currently being processd.
int index = 0;
//This loop begins reading values into the database.
for (int rows = 2; rows <= closetTuples_G.Rows.Count; rows++)
{
//This loop begins to read values for each specific column.
//It stops at column 8 due to columns 9 through 11 being shift related.
for (int columns = 1; columns <= 8; columns++)
{
//The object Value_C is now assigned to be the value of a certain cell in the closetSheet Excel sheet.
Value_G = closetSheet_G.Cells[rows, columns].Value;
//The integer correctIndex is assigned to a cell with a special character that needs to be replaced.
int correctIndex;
//Checks the first column for any cells with special characters and if it finds one, corrects it.
if (columns == 1)
{
particulars_G[index] = Value_G.ToString().Trim();
if (particulars_G[index].ToString().Contains("'"))
{
correctIndex = particulars_G[index].ToString().IndexOf("'");
particulars_G[index] = particulars_G[index].ToString().Replace("'", "''");
Console.WriteLine(particulars_G[index].ToString());
closetSheet_G.Cells[rows, 1] = particulars_G[index];
}
index++;
}
//Checks the second column for any cells with special characters and if it finds one, corrects it.
else if (columns == 2)
{
particulars_G[index] = Value_G.ToString().Trim();
if (particulars_G[index].ToString().Contains("'"))
{
correctIndex = particulars_G[index].ToString().IndexOf("'");
particulars_G[index] = particulars_G[index].ToString().Replace("'", "''");
Console.WriteLine(particulars_G[index].ToString());
closetSheet_G.Cells[rows, 2] = particulars_G[index];
}
index++;
}
//Places the contents of third column in a string.
else if (columns == 3)
{
if (Value_G == null)
{
}
else
particulars_G[index] = Value_G.ToString().Trim();
index++;
}
//Places the contents of fourth column in a string.
else if (columns == 4)
{
if (Value_G == null)
{
Value_G = "NULL";
Console.WriteLine(Value_G.ToString());
closetSheet_G.Cells[rows, 4] = Value_G;
}
if (Value_G.ToString().Trim().Contains('(') || Value_G.ToString().Trim().Contains(')') || Value_G.ToString().Trim().Contains('-') || Value_G.ToString().Trim().Contains(' '))
{
Value_G = "NULL";
Console.WriteLine(Value_G.ToString());
closetSheet_G.Cells[rows, 4] = Value_G;
}
particulars_G[index] = Value_G.ToString().Trim();
index++;
}
//Places the contents of fifth column in a string.
else if (columns == 5)
{
if (Value_G == null)
{
Value_G = "NULL";
Console.WriteLine(Value_G.ToString());
closetSheet_G.Cells[rows, 5] = Value_G;
}
particulars_G[index] = Value_G.ToString().Trim();
index++;
}
//Places the contents of sixth column in a string.
else if (columns == 6)
{
if (Value_G == null)
{
Value_G = "NULL";
Console.WriteLine(Value_G.ToString());
closetSheet_G.Cells[rows, 6] = Value_G;
}
particulars_G[index] = Value_G.ToString().Trim();
index++;
}
//Places the contents of seventh column in a string.
else if (columns == 7)
{
if (Value_G == null)
{
Value_G = "NULL";
Console.WriteLine(Value_G.ToString());
closetSheet_G.Cells[rows, 7] = Value_G;
}
particulars_G[index] = Value_G.ToString().Trim();
index++;
}
//Places the contents of eigth column in a string.
else if (columns == 8)
{
if (Value_G == null)
{
Value_G = "NULL";
Console.WriteLine(Value_G.ToString());
closetSheet_G.Cells[rows, 8] = Value_G;
}
particulars_G[index] = Value_G.ToString().Trim();
string Value_to_Check = particulars_G[index].ToString();
string Value_to_Compare = "0";
if (Value_to_Check == Value_to_Compare)
{
Value_to_Check = "NULL";
particulars_G[index] = Value_to_Check;
Console.WriteLine(particulars_G[index].ToString());
closetSheet_G.Cells[rows, 8] = particulars_G[index];
}
index++;
}
}
index = 0;
//The query will finally begin to add the data from the Excel sheet to the database.
Godmother_Add.NewGodMother(closetSheet_G.Cells[rows, 1].Value.ToString(), closetSheet_G.Cells[rows, 2].Value.ToString(), closetSheet_G.Cells[rows, 5].Value.ToString(), closetSheet_G.Cells[rows, 6].Value.ToString(), closetSheet_G.Cells[rows, 3].Value.ToString(), closetSheet_G.Cells[rows, 4].Value.ToString(), closetSheet_G.Cells[rows, 7].Value.ToString(), closetSheet_G.Cells[rows, 8].Value.ToString());
//Code for progress bar.
}
//This loop begins reading values into the database for the Fairy Godmother shifts.
for (int rows = 2; rows <= closetTuples_G.Rows.Count; rows++)
{
//This loop begins to read values for each of the three shift columns.
//It begins at column 9 because the Shift information begins at column 9.
for (int columns = 9; columns <= closetTuples_G.Columns.Count; columns++)
{
//The object Value_G is now assigned to be the value of a certain cell in the closetSheet Excel sheet.
Value_G = closetSheet_G.Cells[rows, columns].Value;
//The integer correctIndex is assigned to a cell with a special character that needs to be replaced.
//int correctIndex;
//Places the contents of column 9 (the shift and role IDs) into the particulars_Shift object.
if (columns == 9)
{
shiftID = 1;
particulars_Shift = Value_G.ToString().Trim();
string CompareRoles = particulars_Shift;
//The following strings represent any possible value for the role columns.
string NotVolunteering = "Not Volunteering";
string NullString = "NULL";
string EmptyString = "";
string PersonalShopper = "Personal Shopper";
string Alterations = "Alterations";
if (CompareRoles == NotVolunteering || CompareRoles == NullString || CompareRoles == EmptyString || CompareRoles == null)
{
continue;
}
else if (CompareRoles == PersonalShopper)
{
roleID = 4;
Godmother_Add.newFGShiftWorker(closetSheet_G.Cells[rows, 1].Value.ToString(), closetSheet_G.Cells[rows, 2].Value.ToString(), shiftID, roleID);
}
else if (CompareRoles == Alterations)
{
roleID = 5;
Godmother_Add.newFGShiftWorker(closetSheet_G.Cells[rows, 1].Value.ToString(), closetSheet_G.Cells[rows, 2].Value.ToString(), shiftID, roleID);
}
else
{
roleID = 6;
Godmother_Add.newFGShiftWorker(closetSheet_G.Cells[rows, 1].Value.ToString(), closetSheet_G.Cells[rows, 2].Value.ToString(), shiftID, roleID);
}
}
//Places the contents of column 10 (the shift and role IDs) into the particulars_Shift object.
else if (columns == 10)
{
shiftID = 2;
particulars_Shift = Value_G.ToString().Trim();
string CompareRoles = particulars_Shift;
//The following strings represent any possible value for the role columns.
string NotVolunteering = "Not Volunteering";
string NullString = "NULL";
string EmptyString = "";
string PersonalShopper = "Personal Shopper";
string Alterations = "Alterations";
if (CompareRoles == NotVolunteering || CompareRoles == NullString || CompareRoles == EmptyString || CompareRoles == null)
{
continue;
}
else if (CompareRoles == PersonalShopper)
{
roleID = 4;
Godmother_Add.newFGShiftWorker(closetSheet_G.Cells[rows, 1].Value.ToString(), closetSheet_G.Cells[rows, 2].Value.ToString(), shiftID, roleID);
}
else if (CompareRoles == Alterations)
{
roleID = 5;
Godmother_Add.newFGShiftWorker(closetSheet_G.Cells[rows, 1].Value.ToString(), closetSheet_G.Cells[rows, 2].Value.ToString(), shiftID, roleID);
}
else
{
roleID = 6;
Godmother_Add.newFGShiftWorker(closetSheet_G.Cells[rows, 1].Value.ToString(), closetSheet_G.Cells[rows, 2].Value.ToString(), shiftID, roleID);
}
}
//Places the contents of column 9 (the shift and role IDs) into the particulars_Shift object.
else if (columns == 11)
{
shiftID = 3;
particulars_Shift = Value_G.ToString().Trim();
string CompareRoles = particulars_Shift;
//The following strings represent any possible value for the role columns.
string NotVolunteering = "Not Volunteering";
string NullString = "NULL";
string EmptyString = "";
string PersonalShopper = "Personal Shopper";
string Alterations = "Alterations";
if (CompareRoles == NotVolunteering || CompareRoles == NullString || CompareRoles == EmptyString || CompareRoles == null)
{
continue;
}
else if (CompareRoles == PersonalShopper)
{
roleID = 4;
Godmother_Add.newFGShiftWorker(closetSheet_G.Cells[rows, 1].Value.ToString(), closetSheet_G.Cells[rows, 2].Value.ToString(), shiftID, roleID);
}
else if (CompareRoles == Alterations)
{
roleID = 5;
Godmother_Add.newFGShiftWorker(closetSheet_G.Cells[rows, 1].Value.ToString(), closetSheet_G.Cells[rows, 2].Value.ToString(), shiftID, roleID);
}
else
{
roleID = 6;
Godmother_Add.newFGShiftWorker(closetSheet_G.Cells[rows, 1].Value.ToString(), closetSheet_G.Cells[rows, 2].Value.ToString(), shiftID, roleID);
}
}
}
//Code for progress bar.
}
//Code to close out
closetBook_G.Close(false, Type.Missing, Type.Missing);
intermediary_G.Quit();
Marshal.FinalReleaseComObject(intermediary_G);
Marshal.FinalReleaseComObject(closetBook_G);
Marshal.FinalReleaseComObject(closetSheet_G);
Marshal.FinalReleaseComObject(closetTuples_G);
GC.Collect();
//Code for Pop up Window which tells when the import is completed.
Import_Wait popup = new Import_Wait();
DialogResult dialogresult = popup.ShowDialog();
if (dialogresult == DialogResult.OK)
{
popup.Dispose();
}
}
}