private void bgwValidate_DoWork(object sender, DoWorkEventArgs e) { //Do the validation here DateTime begin = DateTime.Now; try { excelApp = new Application(); List <String> scodes = new List <string>(); int rowCount = 0, columnCount = 0, i, j = 1; Workbook courseWorkbook = excelApp.Workbooks.Open(fileName, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); Worksheet courseWorksheet; Range courseWorksheetRange; isValid = true; tempProcessLog = ln + "Reading worksheet..." + ln + "Total sheets : " + courseWorkbook.Sheets.Count; foreach (Worksheet cws in courseWorkbook.Sheets) { if (!Medatabase.isPresentLike("course_master", "course_code", cws.Name)) { tempProcessLog += ln + "sheet with name \"" + cws.Name + "\" course code is not present in database"; isValid = false; invalidRows++; } else { tempProcessLog += ln + "======== reading sheet " + cws.Name + "========" + ln; courseWorksheet = cws; courseWorksheetRange = courseWorksheet.UsedRange; rowCount = courseWorksheetRange.Rows.Count; columnCount = courseWorksheetRange.Columns.Count; tempProcessLog += ln + "Rows " + rowCount + " | Columns " + columnCount + ln; if (rowCount <= 1) { tempProcessLog += ln + "in sheet \"" + cws.Name + "\" There are no records to validate..."; isValid = false; continue; //Move to next sheet } if (columnCount > 5 || columnCount < 5) { tempProcessLog += ln + "in sheet \"" + cws.Name + "\" Expecting columns to be 5, " + columnCount + " is present." + ln + "This excelsheet is not meeting the desired format."; isValid = false; continue; //Move to next sheet } tempProcessLog += ln + "Expecting first row to be column names. " + ln + "reading from 2nd row..."; for (i = 2; i <= rowCount; i++) { try { tempProcessLog += ln + ">> Sheet [" + cws.Name + "] > Processing Row: " + i + ln; /** CANT CHECK FOR DUPLICATE AS ELECTIVE LAB CAN HAVE DUPLICATE SUBJECT CODE :( **/ //Check for duplicate subject_code in database if (Medatabase.isPresent("subject_master", "sub_code", (string)courseWorksheet.Cells[i, 1].Value)) { tempProcessLog += ln + "duplicate subject_code '" + (string)courseWorksheet.Cells[i, 1].Value + "' already present in database in row " + i; isValid = false; invalidRows++; } //if not a lab subject if (String.Compare((string)courseWorksheet.Cells[i, 5].Value, "Lab", true) != 0) { //Check for duplicate subject_code in the same excel sheet if (scodes.IndexOf((string)courseWorksheet.Cells[i, 1].Value) != -1) { tempProcessLog += ln + "duplicate subject_code '" + (string)courseWorksheet.Cells[i, 1].Value + "' already present in the same excel sheet in row " + i; isValid = false; invalidRows++; } scodes.Add((string)courseWorksheet.Cells[i, 1].Value); } if (courseWorksheet.Cells[i, 1].Value == null) { tempProcessLog += ln + "Empty value given for subject_code in row " + i; isValid = false; invalidRows++; } if (courseWorksheet.Cells[i, 2].Value == null) { tempProcessLog += ln + "Empty value given for subject_name in row " + i; isValid = false; invalidRows++; } if ((int)courseWorksheet.Cells[i, 3].Value <= 0 || (int)courseWorksheet.Cells[i, 3].Value > Courses.getSemCount(cws.Name)) { tempProcessLog += ln + "Invalid semester number \"" + (int)courseWorksheet.Cells[i, 3].Value + "\" in row " + i; isValid = false; invalidRows++; } if ((int)courseWorksheet.Cells[i, 4].Value <= 0) { tempProcessLog += ln + "Subject credits cannot be less than or equal to 0 in row " + i; isValid = false; invalidRows++; } if (courseWorksheet.Cells[i, 5].Value == null) { tempProcessLog += ln + "Empty value given for subject type in row " + i; isValid = false; invalidRows++; } } catch (Microsoft.CSharp.RuntimeBinder.RuntimeBinderException be) { invalidRows++; isValid = false; tempProcessLog += ln + "In row [" + i + "] expecting int value, string given!"; LogWriter.WriteError("While validating subject details worksheet", be.Message); } //Update output textbox App.Current.Dispatcher.Invoke(new System.Action(() => { txtOutput.AppendText(tempProcessLog); txtOutput.ScrollToEnd(); })); bgwValidate.ReportProgress(Convert.ToInt16(((i * 100) / rowCount))); tempProcessLog = ""; } } bgwValidate.ReportProgress(Convert.ToInt16(((j * 100) / courseWorkbook.Sheets.Count))); j++; } } catch (System.IO.IOException ioe) { tempProcessLog += ln + "ERROR HANDLING FILE :(" + ln + "Please retry"; LogWriter.WriteError("While validating subject details worksheet", ioe.Message); isValid = false; return; } catch (databaseException) { tempProcessLog += ln + "ERROR OCCURED IN DATABASE OPERATION :(" + ln + "Please retry."; isValid = false; return; } catch (Exception ex) { tempProcessLog += ln + "UNEXPECTED ERROR OCCURED :(" + ln + "Please retry."; LogWriter.WriteError("While validating subject details worksheet", ex.Message); isValid = false; return; } finally { if (invalidRows > 0) { tempProcessLog += ln + ln + "There are total " + invalidRows + " errors."; } //Update output textbox tempProcessLog += ln + "Took " + String.Format("{0:0.00}", (DateTime.Now - begin).TotalSeconds) + " seconds"; App.Current.Dispatcher.Invoke(new System.Action(() => { txtOutput.AppendText(tempProcessLog); txtOutput.ScrollToEnd(); progress.Value = 100; })); excelApp.Workbooks.Close(); } }