private static void ExportToExcel(int studentIndex, string excelTemplateFilePath, string outputFilePath,
SubjectInfo[] subjects, StateSubjectInfo[] stateSubjects, StudentInfo[] students)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(excelTemplateFilePath, 0, false);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets[1];
Microsoft.Office.Interop.Excel.Font excelTextFont = xlWorkSheet1.Cells[STATE_SUBJECT_ROW, STATE_SUBJECT_COL].Font;
System.Drawing.Font textFont = new System.Drawing.Font(excelTextFont.Name, 8/*excelTextFont.Size*/);
double cellWidth = 312;// xlWorkSheet2.Cells[STATE_SUBJECT_ROW, STATE_SUBJECT_COL].EntireColumn.ColumnWidth;
int stateSubjectRowIndex = STATE_SUBJECT_ROW;
for (int index = 0; index < stateSubjects.Length; ++index)
{
if (stateSubjects[index].Name.Length > 0)
{
ArrayList splittedSubjectName = SplitSubjectName(stateSubjects[index].Name, textFont, cellWidth);
foreach (string subjectName in splittedSubjectName)
{
xlWorkSheet1.Cells[stateSubjectRowIndex, STATE_SUBJECT_COL] = subjectName;
++stateSubjectRowIndex;
}
}
}
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet3 = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets[1];
StudentInfo studentInfo = students[studentIndex];
string[] parsedStudentFullName = studentInfo.FullName.Split(' ');
if (parsedStudentFullName.Length > 0)
xlWorkSheet3.Cells[LAST_NAME_ROW, LAST_NAME_COL] = parsedStudentFullName[0];
string firstName = "";
if (parsedStudentFullName.Length > 2)
firstName = parsedStudentFullName[1] + " " + parsedStudentFullName[2];
else if (parsedStudentFullName.Length > 1)
firstName = parsedStudentFullName[1];
xlWorkSheet3.Cells[FIRST_NAME_ROW, FIRST_NAME_COL] = firstName;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets[2];
float totalGradeSum = 0.0f;
int gradeCount = 0;
int totalHourCount = 0;
int part = 0;
int partOffset = 0;
excelTextFont = xlWorkSheet2.Cells[SUBJECT_ROW_START[part], SUBJECT_COL[part]].Font;
textFont = new System.Drawing.Font(excelTextFont.Name, 8);
cellWidth = xlWorkSheet2.Cells[SUBJECT_ROW_START[part], SUBJECT_COL[part]].EntireColumn.ColumnWidth;
for (int index = 0; index < subjects.Length; ++index)
{
if (subjects[index].Name.Length > 0)
{
ArrayList splittedSubjectName = SplitSubjectName(subjects[index].Name, textFont, cellWidth);
if ((SUBJECT_ROW_START[part] + partOffset + splittedSubjectName.Count) > SUBJECT_ROW_FINISH[part])
{
partOffset = 0;
++part;
}
foreach (string subjectName in splittedSubjectName)
{
xlWorkSheet2.Cells[SUBJECT_ROW_START[part] + partOffset, SUBJECT_COL[part]] = subjectName;
++partOffset;
}
string hourCount = "";
if (subjects[index].HourCount != -1)
{
totalHourCount += subjects[index].HourCount;
hourCount = Convert.ToString(subjects[index].HourCount);
}
xlWorkSheet2.Cells[SUBJECT_ROW_START[part] + partOffset - 1, HOUR_COUNT_COL[part]] = hourCount;
string grade = "";
if (studentInfo.Grades[index] != 0)
{
totalGradeSum += studentInfo.Grades[index];
++gradeCount;
grade = GetGradeName(studentInfo.Grades[index], subjects[index].Type);
}
xlWorkSheet2.Cells[SUBJECT_ROW_START[part] + partOffset - 1, GRADE_COL[part]] = grade;
}
}
xlWorkSheet2.Cells[AVERAGE_GRADE_ROW, AVERAGE_GRADE_COL] = Convert.ToString(totalGradeSum / (float)gradeCount);
xlWorkSheet2.Cells[TOTAL_HOUR_COUNT_ROW, TOTAL_HOUR_COUNT_COL] = Convert.ToString(totalHourCount);
if (System.IO.File.Exists(outputFilePath))
System.IO.File.Delete(outputFilePath);
xlWorkBook.SaveAs(outputFilePath);
xlWorkBook.Close(true);
xlApp.Quit();
ReleaseCOMObject(xlApp);
ReleaseCOMObject(xlWorkBook);
ReleaseCOMObject(xlWorkSheet1);
}
private static StudentInfo[] ParseStudentInfo(string content)
{
string[] allStudentInfo = content.Split(',');
StudentInfo[] students = new StudentInfo[allStudentInfo.Length - 1];
for (int studentIndex = 0; studentIndex < students.Length; ++studentIndex)
{
String[] studentInfo = allStudentInfo[studentIndex].Split('>');
students[studentIndex] = new StudentInfo(studentInfo[0]);
for (int gradeIndex = 1; gradeIndex < (studentInfo.Length - 1); ++gradeIndex)
students[studentIndex].Grades[gradeIndex - 1] = Convert.ToInt32(studentInfo[gradeIndex]);
}
return students;
}