EXCEL SHEET IMPORT

protected void btnImport_Click(object sender, EventArgs e)
{
try
{
if (ddlCourseName.SelectedValue == "--Select--")
{
lblErrorMsg.Text = "Please select course name.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
}
else if (ddlCourseModule.SelectedValue == "--Select--")
{
lblErrorMsg.Text = "Please select module name.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
}
else if (!(UploadQuestions.HasFile))
{
lblErrorMsg.Text = "Please select file to import.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
}
else if ((UploadQuestions.PostedFile.FileName.Substring(UploadQuestions.PostedFile.FileName.IndexOf(".")).ToLower()) != ".xls")
{
lblErrorMsg.Text = "Please select excel file to import.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
}
else if (UploadQuestions.HasFile)
{
string url = "CreateAssessment.aspx?CourseID=" + Convert.ToInt16(ddlCourseName.SelectedValue) + "&ModuleID=" + Convert.ToInt16(ddlCourseModule.SelectedValue);
string path = Server.MapPath("Questions");
strFile = "QuestionFormat" + System.DateTime.Now.Ticks.ToString() + ".xls";
UploadQuestions.SaveAs(path + "\\" + strFile);
ValidateQuestion(path + "\\" + strFile);
// ValidateQuestion(path + "\\QuestionFormat633952775374218750.xls");
if (flag != 0)
{
Response.Redirect(url, false);
}
}
}
catch (Exception ex)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, ex.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, ex.Source);
LCE.Configuration.ErrorLog("Assessment/ImportQuestions.cs", errorMessage);
}
}


private void ValidateQuestion(string fileName)
{
string QFormat = "OK";
int rows;
try
{
GC.Collect();
// creat a Application object
objXL = new Application();
// get WorkBook object
objWB = objXL.Workbooks.Open(fileName, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);

// get WorkSheet object
objSheet = (Microsoft.Office.Interop.Excel.Worksheet)objWB.Sheets[1];
rows = objSheet.UsedRange.Cells.Rows.Count;
if (rows < 15)
{
lblErrorMsg.Text = "Please add question in the excel sheet";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
}
else
{
//****************Check for proper data*******************//

for (int i = 16; i <= (rows + 1); i++)
{
string qt = objSheet.get_Range(objSheet.Cells[i, 2], objSheet.Cells[i, 2]).Text.ToString();
if (qt != "")
{
if (Convert.ToInt16(qt) == 1)//for single select
{
if (objSheet.get_Range(objSheet.Cells[i, 3], objSheet.Cells[i, 3]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if (objSheet.get_Range(objSheet.Cells[i, 4], objSheet.Cells[i, 4]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;

}
else if (objSheet.get_Range(objSheet.Cells[i, 5], objSheet.Cells[i, 5]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if (objSheet.get_Range(objSheet.Cells[i, 6], objSheet.Cells[i, 6]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if (objSheet.get_Range(objSheet.Cells[i, 7], objSheet.Cells[i, 7]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if (objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if ((Convert.ToInt16(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString()) < 1) || (Convert.ToInt16(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString()) > 4))//check selected options
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if (objSheet.get_Range(objSheet.Cells[i, 9], objSheet.Cells[i, 9]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
}
else if (Convert.ToInt16(qt) == 2)//for multiple select
{
if (objSheet.get_Range(objSheet.Cells[i, 3], objSheet.Cells[i, 3]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if (objSheet.get_Range(objSheet.Cells[i, 4], objSheet.Cells[i, 4]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if (objSheet.get_Range(objSheet.Cells[i, 5], objSheet.Cells[i, 5]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if (objSheet.get_Range(objSheet.Cells[i, 6], objSheet.Cells[i, 6]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if (objSheet.get_Range(objSheet.Cells[i, 7], objSheet.Cells[i, 7]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if (objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if ((objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "1") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "1,2") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "1,2,3") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "1,3,4") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "2") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "2,3") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "2,3,4") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "3") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "3,4") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "4") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "1,3") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "1,2,4") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "1,4") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "2,4") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "1,2,3,4") &&
(objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "3,4")

)//check selected options
{
lblErrorMsg.Text = "Please check multiple choice answer,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if (objSheet.get_Range(objSheet.Cells[i, 9], objSheet.Cells[i, 9]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
}
else if (Convert.ToInt16(qt) == 3)//for true false
{
if (objSheet.get_Range(objSheet.Cells[i, 3], objSheet.Cells[i, 3]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if (objSheet.get_Range(objSheet.Cells[i, 4], objSheet.Cells[i, 4]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if (objSheet.get_Range(objSheet.Cells[i, 5], objSheet.Cells[i, 5]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if (objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
else if ((objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "1") && (objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString() != "2"))
{
lblErrorMsg.Text = "Please check true/false answer format,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;

}
else if (objSheet.get_Range(objSheet.Cells[i, 9], objSheet.Cells[i, 9]).Text.ToString() == "")
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
}
}
else
{
lblErrorMsg.Text = "Excel sheet format is incorrect,Please download template format of excelsheet.";
lblErrorMsg.ForeColor = System.Drawing.Color.Red;
QFormat = "Incorrect";
flag = 0;
break;
}
}
}
//********************************************************//
//if format is correct then add to ds
//---------------------------------//
if (QFormat == "OK")
{
for (int i = 16; i <= (rows+1); i++)
{
int qt = Convert.ToInt16(objSheet.get_Range(objSheet.Cells[i, 2], objSheet.Cells[i, 2]).Text.ToString());
if (qt == 1)
{
BLL_Assessment AssObj = new BLL_Assessment();
AssObj.QuestionType = qt;
AssObj.CourseID = Convert.ToInt16(ddlCourseName.SelectedValue);
AssObj.ModuleID = Convert.ToInt16(ddlCourseModule.SelectedValue);
AssObj.QuestionText = objSheet.get_Range(objSheet.Cells[i, 3], objSheet.Cells[i, 3]).Text.ToString();
AssObj.OptionA = objSheet.get_Range(objSheet.Cells[i, 4], objSheet.Cells[i, 4]).Text.ToString();
AssObj.OptionB = objSheet.get_Range(objSheet.Cells[i, 5], objSheet.Cells[i, 5]).Text.ToString();
AssObj.OptionC = objSheet.get_Range(objSheet.Cells[i, 6], objSheet.Cells[i, 6]).Text.ToString();
AssObj.OptionD = objSheet.get_Range(objSheet.Cells[i, 7], objSheet.Cells[i, 7]).Text.ToString();
AssObj.CorrectOptions = objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString();
AssObj.AnsExplaination = objSheet.get_Range(objSheet.Cells[i, 9], objSheet.Cells[i, 9]).Text.ToString();
AssObj.AddQuestion();
flag = 1;
}
else if (qt == 2)
{
BLL_Assessment AssObj = new BLL_Assessment();
AssObj.QuestionType = qt;
AssObj.CourseID = Convert.ToInt16(ddlCourseName.SelectedValue);
AssObj.ModuleID = Convert.ToInt16(ddlCourseModule.SelectedValue);
AssObj.QuestionText = objSheet.get_Range(objSheet.Cells[i, 3], objSheet.Cells[i, 3]).Text.ToString();
AssObj.OptionA = objSheet.get_Range(objSheet.Cells[i, 4], objSheet.Cells[i, 4]).Text.ToString();
AssObj.OptionB = objSheet.get_Range(objSheet.Cells[i, 5], objSheet.Cells[i, 5]).Text.ToString();
AssObj.OptionC = objSheet.get_Range(objSheet.Cells[i, 6], objSheet.Cells[i, 6]).Text.ToString();
AssObj.OptionD = objSheet.get_Range(objSheet.Cells[i, 7], objSheet.Cells[i, 7]).Text.ToString();
AssObj.CorrectOptions = objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString();
AssObj.AnsExplaination = objSheet.get_Range(objSheet.Cells[i, 9], objSheet.Cells[i, 9]).Text.ToString();
AssObj.AddQuestion();
flag = 1;
}
else if (qt == 3)
{
BLL_Assessment AssObj = new BLL_Assessment();
AssObj.QuestionType = qt;
AssObj.CourseID = Convert.ToInt16(ddlCourseName.SelectedValue);
AssObj.ModuleID = Convert.ToInt16(ddlCourseModule.SelectedValue);
AssObj.QuestionText = objSheet.get_Range(objSheet.Cells[i, 3], objSheet.Cells[i, 3]).Text.ToString();
AssObj.OptionA = "";
AssObj.OptionB = "";
AssObj.OptionC = "";
AssObj.OptionD = "";
AssObj.CorrectOptions = objSheet.get_Range(objSheet.Cells[i, 8], objSheet.Cells[i, 8]).Text.ToString();
AssObj.AnsExplaination = objSheet.get_Range(objSheet.Cells[i, 9], objSheet.Cells[i, 9]).Text.ToString();
AssObj.AddQuestion();
flag = 1;
}
}
Session["msg"] = "Questions Imported Successfully..";
}
//---------------------------------//
//CLEAN OBJECT-------
objWB.Close(null, null, null);
objXL.Workbooks.Close();
objXL.Quit();
//System.Runtime.InteropServices.Marshal.ReleaseComObject(objRng);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objXL);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objWB);
objSheet = null;
objWB = null;
objXL = null;
GC.Collect();
//-----------
}
catch (Exception ex)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, ex.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, ex.Source);
LCE.Configuration.ErrorLog("Assessment/ImportQuestions.cs", errorMessage);
}
finally
{
Dispose();

System.Diagnostics.Process[] prs = System.Diagnostics.Process.GetProcesses();

foreach (System.Diagnostics.Process proces in prs)
{

if (proces.ProcessName.ToUpper() == "EXCEL")
{
proces.Refresh();
if (!proces.HasExited)
proces.Kill();
}
}
}
}