public void WriteResultToExcelAttendance()
{
this.xlApp = new Microsoft.Office.Interop.Excel.Application();
if (this.xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
}
this.xlApp.Visible = false;
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(1);
Excel.Sheets xlWorksheet = xlWorkBook.Worksheets;
var xlNewSheet = (Excel.Worksheet)xlWorksheet.Add(xlWorksheet[1]);
int row = 1;
foreach (Attendance student in attendanceList)
{
xlNewSheet.Cells[row, 1] = student.StudentNo;
xlNewSheet.Cells[row, 2] = student.Name;
xlNewSheet.Cells[row, 3] = student.Visa;
xlNewSheet.Cells[row, 4] = student.NewWarning;
xlNewSheet.Cells[row, 5] = student.CourseCode;
xlNewSheet.Cells[row, 6] = student.StartDate;
xlNewSheet.Cells[row, 7] = student.EndDate;
xlNewSheet.Cells[row, 8] = student.CurrentAttendace;
xlNewSheet.Cells[row, 9] = student.OverallAttendace;
//foreach (Payment payment in payments)
//{
// xlNewSheet.Cells[row, 1] = student.StNo;
// if (payment.DueDate < System.DateTime.Today)
// {
// Excel.Range cell = xlNewSheet.Cells[row, 7];
// cell.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
// }
// xlNewSheet.Cells[row, 7] = payment.DueDate;
// xlNewSheet.Cells[row, 8] = payment.PaymentAmount;
// xlNewSheet.Cells[row, 9] = payment.Note;
row++;
//}
}
//Excel.Range range = xlNewSheet.UsedRange;
//range.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
String filename = xlApp.GetSaveAsFilename("result.xls", "Excel files (*.xls), *.xls");
if (string.IsNullOrEmpty(filename))
{
filename = "result.xls";
}
xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlWorkBook.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
MessageBox.Show("Done!!!!");
}
public void WriteResultToExcelSterter()
{
this.xlApp = new Microsoft.Office.Interop.Excel.Application();
if (this.xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
}
this.xlApp.Visible = false;
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(1);
Excel.Sheets xlWorksheet = xlWorkBook.Worksheets;
var xlNewSheet = (Excel.Worksheet)xlWorksheet.Add(xlWorksheet[1]);
int row = 1;
foreach (Starters student in statersList)
{
xlNewSheet.Cells[row, 1] = student.StudentNo;
xlNewSheet.Cells[row, 2] = student.Name;
xlNewSheet.Cells[row, 3] = student.Visa;
xlNewSheet.Cells[row, 4] = student.CourseCode;
xlNewSheet.Cells[row, 5] = student.StartDate;
xlNewSheet.Cells[row, 6] = student.EndDate;
xlNewSheet.Cells[row, 10] = student.Agent;
if (student.Visa == "Student")
{
xlNewSheet.Cells[row, 11] = student.CoEStartDate;
xlNewSheet.Cells[row, 12] = student.CoEEndDate;
xlNewSheet.Cells[row, 13] = student.CoEDescription;
xlNewSheet.Cells[row, 14] = student.CoEStatus;
if (student.CoEStatus != "Okay")
{
switch (student.CoEDateChecker)
{
case 1:
xlNewSheet.Cells[row, 11].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
break;
case 2:
xlNewSheet.Cells[row, 12].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
break;
case 3:
xlNewSheet.Cells[row, 11].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
xlNewSheet.Cells[row, 12].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
break;
case 4:
xlNewSheet.Cells[row, 13].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
break;
case 5:
xlNewSheet.Cells[row, 11].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
xlNewSheet.Cells[row, 13].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
break;
case 6:
xlNewSheet.Cells[row, 12].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
xlNewSheet.Cells[row, 13].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
break;
case 7:
xlNewSheet.Cells[row, 11].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
xlNewSheet.Cells[row, 12].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
xlNewSheet.Cells[row, 13].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
break;
}
}
}
else
{
xlNewSheet.Cells[row, 14] = "N/A";
}
row++;
////ArrayList payments = student.GetPayments();
//foreach (Payment payment in payments)
//{
// xlNewSheet.Cells[row, 1] = student.StNo;
// if (payment.DueDate < System.DateTime.Today)
// {
// Excel.Range cell = xlNewSheet.Cells[row, 7];
// cell.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
// }
// xlNewSheet.Cells[row, 7] = payment.DueDate;
// xlNewSheet.Cells[row, 8] = payment.PaymentAmount;
// xlNewSheet.Cells[row, 9] = payment.Note;
// row++;
//}
}
//Excel.Range range = xlNewSheet.UsedRange;
//range.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
String filename = xlApp.GetSaveAsFilename("result.xls", "Excel files (*.xls), *.xls");
xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlWorkBook.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
MessageBox.Show("Done!!!!");
}