Ejemplo n.º 1
0
 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();
 }
Ejemplo n.º 2
0
        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);
            }
        }