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(); } }
private void bgwValidate_DoWork(object sender, DoWorkEventArgs e) { //Do the validation here DateTime begin = DateTime.Now; try { const int MAX_SEMS = 8; int sheetCount = 0; int rowCount = 0, columnCount = 0, i; int isem = 0, tsem = 0; string[] courseCodes; isValid = true; excelApp = new Application(); Workbook courseWorkbook = excelApp.Workbooks.Open(fileName, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); Worksheet courseWorksheet = (Worksheet)courseWorkbook.Sheets.get_Item(1); Range courseWorksheetRange = courseWorksheet.UsedRange; sheetCount = courseWorkbook.Sheets.Count; tempProcessLog = ln + "Reading worksheet..." + ln + "Total sheets : " + sheetCount; tempProcessLog += ln + "Using Sheet1... "; rowCount = courseWorksheetRange.Rows.Count; columnCount = courseWorksheetRange.Columns.Count; courseCodes = new string[rowCount]; tempProcessLog += ln + "Rows " + rowCount + " | Columns " + columnCount + ln; if (rowCount <= 1) { tempProcessLog += ln + "There are no records to validate..."; App.Current.Dispatcher.Invoke(new System.Action(() => { txtOutput.AppendText(tempProcessLog); txtOutput.ScrollToEnd(); progress.Value = 100; })); isValid = false; return; } if (columnCount > 4 || columnCount < 4) { tempProcessLog += ln + "Expecting columns to be 4, " + columnCount + " is present." + ln + "This excelsheet is not meeting the desired format."; App.Current.Dispatcher.Invoke(new System.Action(() => { txtOutput.AppendText(tempProcessLog); txtOutput.ScrollToEnd(); progress.Value = 100; })); isValid = false; return; } tempProcessLog += ln + "Expecting first row to be column names. " + ln + "reading from 2nd row..."; List <String> tmpCourseCodes = new List <string>(); for (i = 2; i <= rowCount; i++) { try { tempProcessLog += ln + "> Processing Row: " + i + ln; if (courseWorksheet.Cells[i, 1].Value != null) { //Check for duplicate course_code in database if (Medatabase.isPresentLike("course_master", "course_code", (string)courseWorksheet.Cells[i, 1].Value)) { tempProcessLog += ln + "duplicate course_code '" + (string)courseWorksheet.Cells[i, 1].Value + "' already present in database..."; isValid = false; invalidRows++; } //Check for duplicate course_code in the same excel sheet if (tmpCourseCodes.IndexOf((string)courseWorksheet.Cells[i, 1].Value) != -1) { tempProcessLog += ln + "duplicate course_code '" + (string)courseWorksheet.Cells[i, 1].Value + "' already present in the same excel sheet..."; isValid = false; invalidRows++; } else //if not duplicate { tmpCourseCodes.Add((string)courseWorksheet.Cells[i, 1].Value); } } else { tempProcessLog += ln + "empty course_code given...'" + ln; isValid = false; invalidRows++; } if (courseWorksheet.Cells[i, 2].Value != null) { tsem = Convert.ToInt16(courseWorksheet.Cells[i, 2].Value); if (tsem > MAX_SEMS || tsem < 0) { tempProcessLog += ln + (string)courseWorksheet.Cells[i, 1].Value + " > total semesters must be between 0 and " + MAX_SEMS + ", " + tsem + " given"; isValid = false; invalidRows++; } } else { tempProcessLog += ln + "empty value given for total semesters...'" + ln; isValid = false; invalidRows++; } if (courseWorksheet.Cells[i, 3].Value != null) { isem = Convert.ToInt16(courseWorksheet.Cells[i, 3].Value); if (isem < 1) { tempProcessLog += ln + (string)courseWorksheet.Cells[i, 1].Value + " > in semesters must be between 0 and " + MAX_SEMS + ", " + isem + " given"; isValid = false; invalidRows++; } if (isem > tsem) { tempProcessLog += ln + (string)courseWorksheet.Cells[i, 1].Value + " > in semesters can't be > than total sems, " + isem + " given"; isValid = false; invalidRows++; } if (isem > MAX_SEMS) { tempProcessLog += ln + (string)courseWorksheet.Cells[i, 1].Value + " > max in semesters possible is " + MAX_SEMS + ", " + isem + " given"; isValid = false; invalidRows++; } } else { tempProcessLog += ln + "empty value given for in semesters...'" + ln; isValid = false; invalidRows++; } if (courseWorksheet.Cells[i, 4].Value != null) { if ((int)courseWorksheet.Cells[i, 4].Value < 1) { tempProcessLog += ln + "credits must be greater than 0"; isValid = false; invalidRows++; } } else { tempProcessLog += ln + "empty value given for total credits...'" + ln; isValid = false; invalidRows++; } } catch (Microsoft.CSharp.RuntimeBinder.RuntimeBinderException be) { invalidRows++; isValid = false; tempProcessLog += ln + "In row [" + i + "] expecting numeric value, string given!"; LogWriter.WriteError("During course excel sheet validation", 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 = ""; } } catch (System.IO.IOException ioe) { tempProcessLog += ln + "ERROR HANDLING FILE :(" + ln + "Please retry"; LogWriter.WriteError("During course excel sheet validation", 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("During course excel sheet validation", ex.Message); isValid = false; return; } finally { if (invalidRows > 0) { tempProcessLog += ln + "There are total " + invalidRows + " errors."; 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(); } }