private void buttonExportExcel_Click(object sender, EventArgs e)
{
if (comboBoxStudents.SelectedValue != null)
{
using (SaveFileDialog sfd = new SaveFileDialog()
{
Filter = "Excel Workbook|*.xlsx|Excel 97-2003 Workbook|*.xls|Excel Workbook(.csv)|*.csv"
})
{
sfd.InitialDirectory = "C";
sfd.Title = "SAVE AS EXCEL FILE";
sfd.FileName = "Export note - " + comboBoxStudents.GetItemText(comboBoxStudents.SelectedItem);
if (sfd.ShowDialog() == DialogResult.OK)
{
String path = sfd.FileName;
BLExternals bl = new BLExternals();
int studentId = Convert.ToInt32(comboBoxStudents.SelectedValue);
int studyYear = bl.GetYear(Convert.ToInt32(comboBoxStudents.SelectedValue));
DataTable tableY1 = bl.GetCatalogInfoPerYear(studentId, 1);
DataTable tableY2 = bl.GetCatalogInfoPerYear(studentId, 2);
DataTable tableY3 = bl.GetCatalogInfoPerYear(studentId, 3);
DataTable tableY4 = bl.GetCatalogInfoPerYear(studentId, 4);
tableY1.Columns["CourseName"].ColumnName = "Materie";
tableY1.Columns["PartialExam"].ColumnName = "Examen partial";
tableY1.Columns["FinalExam"].ColumnName = "Examen final";
tableY1.Columns["Laboratory"].ColumnName = "Laborator";
tableY1.Columns["Seminary"].ColumnName = "Seminar";
tableY1.Columns["BonusPoints"].ColumnName = "Puncte bonus";
tableY1.Columns["Total"].ColumnName = "Total";
tableY1.Columns["LastUpdate"].ColumnName = "Ultima actualizare";
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
if (studyYear >= 1)
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = "Catalog";
excelSheet.Cells[1, 1] = comboBoxStudents.GetItemText(comboBoxStudents.SelectedItem) + ", " + DateTime.Now.ToShortDateString();
// loop through each row and add values to our sheet
int rowcount = 2;
for (int i = 1; i <= tableY1.Columns.Count; i++)
{
// on the first iteration we add the column headers
excelSheet.Cells[2, i] = tableY1.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount + 1, 1] = "Anul 1";
excelSheet.Range[excelSheet.Cells[rowcount + 1, 1], excelSheet.Cells[rowcount + 1, tableY1.Columns.Count]].Merge();
excelSheet.Range[excelSheet.Cells[rowcount + 1, 1], excelSheet.Cells[rowcount + 1, tableY1.Columns.Count]].Cells.Font.Size = 14;
excelSheet.Cells[rowcount + 1, 1].Font.Bold = true;
excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount + 1, tableY1.Columns.Count]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rowcount = 3;
foreach (DataRow datarow in tableY1.Rows)
{
rowcount += 1;
for (int i = 1; i <= tableY1.Columns.Count; i++)
{
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
if (i != 1)
{
excelSheet.Cells[rowcount, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
if (i == tableY1.Columns.Count - 1)
{
excelSheet.Cells[rowcount, i].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
//for alternate rows
if (rowcount > 3)
{
if (i == tableY1.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, tableY1.Columns.Count]];
FormattingExcelCells(excelCellrange, "#aec4d1", System.Drawing.Color.Black, false);
excelSheet.Cells[rowcount, i - 1].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
}
}
}
}
if (studyYear >= 2)
{
excelSheet.Cells[rowcount + 2, 1] = "Anul 2";
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY2.Columns.Count]].Merge();
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY2.Columns.Count]].Cells.Font.Size = 14;
excelSheet.Cells[rowcount + 2, 1].Font.Bold = true;
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY2.Columns.Count]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rowcount = rowcount + 2;
foreach (DataRow datarow in tableY2.Rows)
{
rowcount += 1;
for (int i = 1; i <= tableY2.Columns.Count; i++)
{
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
if (i != 1)
{
excelSheet.Cells[rowcount, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
if (i == tableY1.Columns.Count - 1)
{
excelSheet.Cells[rowcount, i].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
//for alternate rows
if (rowcount > 3)
{
if (i == tableY2.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, tableY2.Columns.Count]];
FormattingExcelCells(excelCellrange, "#aec4d1", System.Drawing.Color.Black, false);
excelSheet.Cells[rowcount, i - 1].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
}
}
}
}
}
if (studyYear >= 3)
{
excelSheet.Cells[rowcount + 2, 1] = "Anul 3";
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY3.Columns.Count]].Merge();
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY3.Columns.Count]].Cells.Font.Size = 14;
excelSheet.Cells[rowcount + 2, 1].Font.Bold = true;
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY3.Columns.Count]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rowcount = rowcount + 2;
foreach (DataRow datarow in tableY3.Rows)
{
rowcount += 1;
for (int i = 1; i <= tableY3.Columns.Count; i++)
{
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
if (i != 1)
{
excelSheet.Cells[rowcount, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
if (i == tableY1.Columns.Count - 1)
{
excelSheet.Cells[rowcount, i].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
//for alternate rows
if (rowcount > 3)
{
if (i == tableY3.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, tableY3.Columns.Count]];
FormattingExcelCells(excelCellrange, "#aec4d1", System.Drawing.Color.Black, false);
excelSheet.Cells[rowcount, i - 1].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
}
}
}
}
}
if (studyYear >= 4)
{
excelSheet.Cells[rowcount + 2, 1] = "Anul 4";
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY4.Columns.Count]].Merge();
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY4.Columns.Count]].Cells.Font.Size = 14;
excelSheet.Cells[rowcount + 2, 1].Font.Bold = true;
excelSheet.Range[excelSheet.Cells[rowcount + 2, 1], excelSheet.Cells[rowcount + 2, tableY4.Columns.Count]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rowcount = rowcount + 2;
foreach (DataRow datarow in tableY4.Rows)
{
rowcount += 1;
for (int i = 1; i <= tableY4.Columns.Count; i++)
{
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
if (i != 1)
{
excelSheet.Cells[rowcount, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
if (i == tableY1.Columns.Count - 1)
{
excelSheet.Cells[rowcount, i].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
//for alternate rows
if (rowcount > 3)
{
if (i == tableY4.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, tableY4.Columns.Count]];
FormattingExcelCells(excelCellrange, "#aec4d1", System.Drawing.Color.Black, false);
excelSheet.Cells[rowcount, i - 1].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, tableY1.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, tableY1.Columns.Count]];
FormattingExcelCells(excelCellrange, "#007099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
excelworkBook.SaveAs(path);;
excelworkBook.Close();
excel.Quit();
MessageBox.Show("Fisierul a fost exportat cu succes.", "Export", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
}
}
}
private void comboBoxStudents_SelectionChangeCommitted(object sender, EventArgs e)
{
lblSearchMsg.Visible = false;
btnExportExcel.Visible = true;
gridCatalogY1.Visible = false;
gridCatalogY2.Visible = false;
gridCatalogY3.Visible = false;
gridCatalogY4.Visible = false;
lblY1.Visible = false;
lblY2.Visible = false;
lblY3.Visible = false;
lblY4.Visible = false;
BLExternals bl = new BLExternals();
int studyYear = bl.GetYear(Convert.ToInt32(comboBoxStudents.SelectedValue));
if (studyYear >= 1)
{
if (studyYear >= 2)
{
if (studyYear >= 3)
{
if (studyYear >= 4)
{
DataTable tableY4 = bl.GetCatalogInfoPerYear(Convert.ToInt32(comboBoxStudents.SelectedValue), 4);
gridCatalogY4.DataSource = tableY4;
gridCatalogY4.Columns["CourseName"].HeaderText = "Materie";
gridCatalogY4.Columns["PartialExam"].HeaderText = "Partial";
gridCatalogY4.Columns["FinalExam"].HeaderText = "Final";
gridCatalogY4.Columns["Laboratory"].HeaderText = "Laborator";
gridCatalogY4.Columns["Seminary"].HeaderText = "Seminar";
gridCatalogY4.Columns["BonusPoints"].HeaderText = "Puncte bonus";
gridCatalogY4.Columns["Total"].HeaderText = "Total";
gridCatalogY4.Columns["LastUpdate"].HeaderText = "Ultima actualizare";
gridCatalogY4.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
gridCatalogY4.Visible = true;
lblY4.Visible = true;
}
DataTable tableY3 = bl.GetCatalogInfoPerYear(Convert.ToInt32(comboBoxStudents.SelectedValue), 3);
gridCatalogY3.DataSource = tableY3;
gridCatalogY3.Columns["CourseName"].HeaderText = "Materie";
gridCatalogY3.Columns["PartialExam"].HeaderText = "Partial";
gridCatalogY3.Columns["FinalExam"].HeaderText = "Final";
gridCatalogY3.Columns["Laboratory"].HeaderText = "Laborator";
gridCatalogY3.Columns["Seminary"].HeaderText = "Seminar";
gridCatalogY3.Columns["BonusPoints"].HeaderText = "Puncte bonus";
gridCatalogY3.Columns["Total"].HeaderText = "Total";
gridCatalogY3.Columns["LastUpdate"].HeaderText = "Ultima actualizare";
gridCatalogY3.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
gridCatalogY3.Visible = true;
lblY3.Visible = true;
}
DataTable tableY2 = bl.GetCatalogInfoPerYear(Convert.ToInt32(comboBoxStudents.SelectedValue), 2);
gridCatalogY2.DataSource = tableY2;
gridCatalogY2.Columns["CourseName"].HeaderText = "Materie";
gridCatalogY2.Columns["PartialExam"].HeaderText = "Partial";
gridCatalogY2.Columns["FinalExam"].HeaderText = "Final";
gridCatalogY2.Columns["Laboratory"].HeaderText = "Laborator";
gridCatalogY2.Columns["Seminary"].HeaderText = "Seminar";
gridCatalogY2.Columns["BonusPoints"].HeaderText = "Puncte bonus";
gridCatalogY2.Columns["Total"].HeaderText = "Total";
gridCatalogY2.Columns["LastUpdate"].HeaderText = "Ultima actualizare";
gridCatalogY2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
gridCatalogY2.Visible = true;
lblY2.Visible = true;
}
DataTable tableY1 = bl.GetCatalogInfoPerYear(Convert.ToInt32(comboBoxStudents.SelectedValue), 1);
gridCatalogY1.DataSource = tableY1;
gridCatalogY1.Columns["CourseName"].HeaderText = "Materie";
gridCatalogY1.Columns["PartialExam"].HeaderText = "Partial";
gridCatalogY1.Columns["FinalExam"].HeaderText = "Final";
gridCatalogY1.Columns["Laboratory"].HeaderText = "Laborator";
gridCatalogY1.Columns["Seminary"].HeaderText = "Seminar";
gridCatalogY1.Columns["BonusPoints"].HeaderText = "Puncte bonus";
gridCatalogY1.Columns["Total"].HeaderText = "Total";
gridCatalogY1.Columns["LastUpdate"].HeaderText = "Ultima actualizare";
gridCatalogY1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
gridCatalogY1.Visible = true;
lblY1.Visible = true;
}
}