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();
}
}
}