public void RunPreview()
{
var _excelApp = new Excel.Application();
Excel.Workbook _excelWorkbook;
Excel.Worksheet _excelWorksheet;
Excel.Range range;
// Open Excel spreadsheet.
_excelWorkbook = _excelApp.Workbooks.Open(@"C:\ExcelTestFiles\ZacRya140814.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
string fName, lName,testDate;
// int rCnt = 0;
// int cCnt = 0;
_excelWorksheet = (Excel.Worksheet)_excelWorkbook.Worksheets.get_Item(1);
range = _excelWorksheet.UsedRange;
fName = Convert.ToString((range.Cells[7, 7] as Excel.Range).Value2);
lName = Convert.ToString((range.Cells[8, 7] as Excel.Range).Value2);
testDate = Convert.ToString((range.Cells[4, 4] as Excel.Range).Value2);
//Check to see if name exist in the student profile table
ReadingDataEntities db = new ReadingDataEntities();
/////////////////////////////////////////////////////////
// Check to see if student exist //
// Otherwise and the student to the database //
/////////////////////////////////////////////////////////
int studentNo;
var queryStudent = (from p in db.StudentProfiles.ToList()
where p.First_Name==fName.Trim() && p.Last_Name==lName.Trim()
select p).Count();
//Insert student into the student profile table
if(queryStudent<1)
{
StudentProfile _student = new StudentProfile();
_student.First_Name=fName;
_student.Last_Name=lName;
}
var studentAttributes = (from p in db.StudentProfiles
where p.First_Name == fName && p.Last_Name == lName
select p).FirstOrDefault();
////////////////////////////////////////////////////////////
// Generate Excel Header //
////////////////////////////////////////////////////////////
var _excelAppGenerate = new Excel.Application();
Excel.Workbook _excelWorkbookGenerate;
Excel.Worksheet _excelWorksheetGenerate = null;
// creating new WorkBook within Excel application
_excelWorkbookGenerate = _excelAppGenerate.Workbooks.Add(Type.Missing);
// see the excel sheet behind the program
_excelAppGenerate.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
_excelWorksheetGenerate = _excelWorkbookGenerate.Sheets["Sheet1"];
_excelWorksheetGenerate = _excelWorkbookGenerate.ActiveSheet;
_excelWorksheetGenerate.Name = "Student Reading Assessment";
// changing the name of active sheet
_excelWorksheet.Name = "Student Reading Assessment";
List<ReadingTestScores> readingScores = new List<ReadingTestScores>();
ExcelSections.Header(1, 10, range,_excelWorksheetGenerate, readingScores);
///////////////////////////////////////////////////////////////////////////////////////////////////
//// Evaluate each Data Point Standard Score Raw Score t Scor and (ss) for a given student ////
///////////////////////////////////////////////////////////////////////////////////////////////////
studentNo=studentAttributes.StudentID;
int excelIndexNo;
int bookmark = 9;
for (int rowCnt = 11 ; rowCnt <= 25132; rowCnt++)
{
excelIndexNo = Convert.ToInt32((range.Cells[rowCnt, 1] as Excel.Range).Value2);
readingScores=ExcelDataMultiplexer.Multiplexer(studentNo, range, rowCnt, excelIndexNo);
var _currentRow = (from p in readingScores.ToList()
select p).LastOrDefault();
if (_currentRow != null )
{
if (_currentRow.ExcelLastRowIndex != 0)
{
rowCnt = _currentRow.ExcelLastRowIndex;
//Add to Reading Scores to Database
ExcelSections.Body(bookmark, readingScores.Count, range, _excelWorksheetGenerate, readingScores);
// ReadingDataEntities dbTestScores= new ReadingDataEntities();
bookmark = bookmark + readingScores.Count;
_currentRow.ExcelLastRowIndex = 0;
}
}
}
}
public override List<EntireTestCollection> Open(string studentFile, List<EntireTestCollection> indicies, BackgroundWorker bgWorker)
{
if (indicies == null) throw new ArgumentNullException("indicies");
var excelApp = new Excel.Application();
var tmp = excelApp.Workbooks;
// Open Excel spreadsheet.
var excelWorkbook = tmp.Open(@studentFile, Type.Missing, true, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
excelApp.Visible = true;
var activeworksheet = "Entry";
var excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[activeworksheet];
excelWorksheet.Select(true);
excelWorksheet.Unprotect();
excelApp.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable;
excelApp.DisplayAlerts = false;
var range = excelWorksheet.UsedRange;
excelWorksheet.Cells[1, 1].EntireColumn.ColumnWidth = 15;
excelWorksheet.Cells[1, 3].EntireColumn.ColumnWidth = 20;
string fName = Convert.ToString(((Excel.Range) range.Cells[7, 7]).Value2);
string lName = Convert.ToString(((Excel.Range) range.Cells[8, 7]).Value2);
string testDate = Convert.ToString(((Excel.Range) range.Cells[4, 4]).Value2);
var fullname = fName + " " + lName;
//Check to see if name exist in the student profile table
var db = new DAL.ReadingDataEntities();
/////////////////////////////////////////////////////////
// Check to see if student exist //
// Otherwise and the student to the database //
/////////////////////////////////////////////////////////
var queryStudent = (from p in db.StudentProfiles
where p.First_Name == fName.Trim() && p.Last_Name == lName.Trim()
select p).Count();
//Insert student into the student profile table
if (queryStudent < 1)
{
var student = new DAL.StudentProfile
{
First_Name = fName,
Last_Name = lName
};
}
var studentAttributes = (from p in db.StudentProfiles
where p.First_Name == fName && p.Last_Name == lName
select p).FirstOrDefault();
////////////////////////////////////////////////////////////
// Generate Excel Header /
////////////////////////////////////////////////////////////
var excelAppGenerate = new Excel.Application();
Excel.Workbook excelWorkbookGenerate=null;
Excel.Worksheet excelWorksheetGenerate = null;
// creating new WorkBook within Excel application
var tmp2 = excelAppGenerate.Workbooks;
// creating new WorkBook within Excel application
excelWorkbookGenerate = tmp2.Add(Type.Missing);
// see the excel sheet behind the program
excelAppGenerate.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
excelWorksheetGenerate = excelWorkbookGenerate.Sheets["Sheet1"];
excelWorksheetGenerate = excelWorkbookGenerate.ActiveSheet;
excelWorksheetGenerate.Name = "Accepted Test Indicies";
excelAppGenerate.DisplayAlerts = false;
List<DAL.ReadingTestScores> headerInformation = new List<DAL.ReadingTestScores>();
IHeader buildHeader = new GenerateExcelHeader();
buildHeader.Create(1, 10, range, excelWorksheetGenerate);
///////////////////////////////////////////////////////////////////////////
//////// Check to see if test matches template ////
///////////////////////////////////////////////////////////////////////////
bgWorker.ReportProgress(1, "Processing Useable Data");
ICollectIndices captureIndicies = new CollectAllIndicies();
indicies = captureIndicies.Collect(range,bgWorker);
// _excelApp.Visible = false;
bgWorker.ReportProgress(1, "Processing Generating File of Good Data");
GenerateExcelDocParseIndicies generateExcelDoc = new GenerateExcelBodyRetain();
generateExcelDoc.Create(11, indicies.Count, range, excelWorksheetGenerate, indicies,bgWorker);
bgWorker.ReportProgress(100, "Processed");
var fileName = fullname + "_Keep.xls";
bgWorker.ReportProgress(99, "Saving File of Good Data");
var fi = new FileInfo(@"C:\AssessmentXLSfiles\AssesmentAudit\SavedAssessments\" + fileName);
if (fi.Exists)
{
File.Delete(@"C:\AssessmentXLSfiles\AssesmentAudit\SavedAssessments\" + fileName);
}
excelWorkbookGenerate.SaveAs(@"C:\AssessmentXLSfiles\AssesmentAudit\SavedAssessments\" + fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
bgWorker.ReportProgress(100, "Saved");
excelWorkbook.Close(0);
excelApp.Application.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(tmp);
Marshal.ReleaseComObject(excelWorksheet);
Marshal.ReleaseComObject(excelWorkbook);
Marshal.ReleaseComObject(excelApp);
excelWorkbookGenerate.Close(0);
excelAppGenerate.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(tmp2);
Marshal.ReleaseComObject(excelWorksheetGenerate);
Marshal.ReleaseComObject(excelWorkbookGenerate);
Marshal.ReleaseComObject(excelAppGenerate);
System.Threading.Thread.Sleep(500);
return indicies;
}