private void Absent_Statement_Load(object sender, EventArgs e)
{
Session_combobox.SelectedIndex = 0;
DateComboboxFill();
BranchComboboxFill();
Exam_CodeComboboxFill();
//SubjectComboboxFill();
No_of_candidates_ViewText.Clear();
No_of_Present_ViewText.Clear();
No_of_Absent_ViewText.Clear();
GetFileSavepath();
}
private void Prepare_Statement_btn_Click(object sender, EventArgs e)
{
if (Filepath_textbox.Text != "PLEASE_ADD_PATH")
{
if (table.Rows.Count != 0)
{
try
{
string createStatePath = Filepath_textbox.Text + @"\Attendance Sheets";
Directory.CreateDirectory(createStatePath);
using (var package = new ExcelPackage())
{
//Add a new worksheet to the empty workbook
var worksheet = package.Workbook.Worksheets.Add(Branch_combobox.Text);
SQLiteCommand command2 = new SQLiteCommand("select Year_Of_Admission from Students where Reg_no=@Reg_no", con.ActiveCon());
command2.Parameters.AddWithValue("@Reg_no", table.Rows[0]["Reg_no"].ToString());
string semester = (string)command2.ExecuteScalar();
//Insert Items to ExcelSheet
worksheet.Cells["A1"].Value = "KMEA ENGINEERING COLLEGE";
worksheet.Cells["A2"].Value = Examination_Textbox.Text;
worksheet.Cells["A3"].Value = "ATTENDANCE STATEMENT";
worksheet.Cells["A4"].Value = Date_combobox.Text;
worksheet.Cells["D4"].Value = Session_combobox.Text;
worksheet.Cells["A5"].Value = Branch_combobox.Text;
worksheet.Cells["C5"].Value = "Year: " + semester;
worksheet.Cells["D5"].Value = Dgv.Rows[0].Cells["Course"].Value.ToString() + " " + ExamCode_combobox.Text;
using (var range = worksheet.Cells["A1:D1"])
{
range.Merge = true;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.Font.Name = "Arial";
range.Style.Font.Size = 16;
range.Style.Font.Bold = true;
}
using (var range = worksheet.Cells["A2:D2"])
{
range.Merge = true;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.Font.Name = "Arial";
range.Style.Font.Size = 14;
range.Style.Font.Bold = true;
}
using (var range = worksheet.Cells["A3:D3"])
{
range.Merge = true;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.Font.Name = "Arial";
range.Style.Font.Size = 12;
range.Style.Font.Bold = true;
}
using (var range = worksheet.Cells["A4:D4"])
{
range.Style.Font.Name = "Arial";
range.Style.Font.Size = 11;
range.Style.Font.Bold = true;
}
using (var range = worksheet.Cells["A5:D5"])
{
range.Style.Font.Name = "Arial";
range.Style.Font.Size = 11;
range.Style.Font.Bold = true;
}
// column headings
worksheet.Cells[6, 1].Value = "Sl.No";
worksheet.Cells[6, 1].Style.Font.Name = "Arial";
worksheet.Cells[6, 1].Style.Font.Size = 12;
worksheet.Cells[6, 1].Style.Font.Bold = true;
for (var i = 0; i < 3; i++)
{
worksheet.Cells[6, i + 2].Value = table.Columns[i].ColumnName;
worksheet.Cells[6, i + 2].Style.Font.Name = "Arial";
worksheet.Cells[6, i + 2].Style.Font.Size = 12;
worksheet.Cells[6, i + 2].Style.Font.Bold = true;
}
//rows filling
int count = 0;
for (int i = 0; i < table.Rows.Count; i++)
{
worksheet.Cells[i + 7, 1].Value = i + 1; //Sl.No Filling
for (var j = 0; j < 3; j++)
{
worksheet.Cells[i + 7, j + 2].Value = table.Rows[i][j];
if (table.Rows[i][j].ToString() == "Absent")
{
worksheet.Cells[i + 7, j + 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[i + 7, j + 2].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
worksheet.Cells[i + 7, j + 2].Style.Font.Color.SetColor(Color.Red);
}
}
count = i + 9;
}
using (var range = worksheet.Cells[7, 1, table.Rows.Count + 6, 4])
{
range.AutoFitColumns();
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
worksheet.Cells[count, 3].Value = "No of Present = " + No_of_Present_ViewText.Text;
worksheet.Cells[count + 1, 3].Value = "No of Absent = " + No_of_Absent_ViewText.Text;
//Save Excel File
string path = createStatePath + @"\AttndncStatmnt " + Session_combobox.Text + " " + ExamCode_combobox.Text + " " + Branch_combobox.Text + ".xlsx";
Stream stream = File.Create(path);
package.SaveAs(stream);
stream.Close();
Branch_combobox.SelectedIndex = 0;
ExamCode_combobox.SelectedIndex = 0;
//SubjectName_Combobox.SelectedIndex = 0;
No_of_candidates_ViewText.Clear();
No_of_Present_ViewText.Clear();
No_of_Absent_ViewText.Clear();
table.Clear();
Dgv.DataSource = null;
msgbox.show("Excel file created ", "Success", CustomMessageBox.MessageBoxButtons.OK, CustomMessageBox.MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
con.CloseCon();
}
}
else
{
msgbox.show("Search Students First ", "Error", CustomMessageBox.MessageBoxButtons.OK, CustomMessageBox.MessageBoxIcon.Error);
}
}
else
{
msgbox.show("Filepath is not given ", "Error", CustomMessageBox.MessageBoxButtons.OK, CustomMessageBox.MessageBoxIcon.Error);
}
}