コード例 #1
0
    protected void btngo_Click(object sender, EventArgs e)
    {
        try
        {
            string   valCollege            = string.Empty;
            int      exMonthName           = 0;
            int      colspanbranch         = 0;
            int      colspansubj           = 0;
            int      coldeg                = 0;
            int      coldegr               = 0;
            int      dsrow                 = 0;
            int      inrow                 = 0;
            string   sess                  = string.Empty;
            string   collegeCode1          = string.Empty;
            int      total                 = 0;
            bool     colspanBool           = false;
            string   BranchCodeReplication = string.Empty;
            string   degree                = string.Empty;
            string   subjcode              = string.Empty;
            DataView colSpanDV             = new DataView();
            DataView colSpansub            = new DataView();
            DataSet  dsCollege             = new DataSet();
            Fpspread.Sheets[0].Visible              = true;
            Fpspread.Sheets[0].AutoPostBack         = true;
            Fpspread.Sheets[0].RowHeader.Visible    = false;
            Fpspread.Sheets[0].ColumnHeader.Visible = true;
            MyStyle.Font.Size       = FontUnit.Medium;
            MyStyle.Font.Name       = "Book Antiqua";
            MyStyle.Font.Bold       = true;
            MyStyle.HorizontalAlign = HorizontalAlign.Center;
            MyStyle.ForeColor       = Color.White;
            MyStyle.BackColor       = ColorTranslator.FromHtml("#0CA6CA");
            Fpspread.Sheets[0].ColumnHeader.DefaultStyle = MyStyle;
            Fpspread.CommandBar.Visible    = false;
            Fpspread.Sheets[0].ColumnCount = 8;
            Fpspread.Sheets[0].RowCount    = 2;
            Fpspread.BorderWidth           = 2;
            valCollege = rs.GetSelectedItemsValueAsString(cblCollege);
            btn_directprint.Visible = true;
            if (valCollege.ToString() != "")
            {
                if (ddlDate.SelectedValue != "" && ddlSession.SelectedValue != "")
                {
                    lblerror.Visible = false;
                    Fpspread.Visible = true;
                    int.TryParse(Convert.ToString(ddlMonth.SelectedValue).Trim(), out exMonthName);
                    string   exdate      = Convert.ToString(ddlDate.SelectedValue).Trim();
                    string[] spl1        = exdate.Split('-');
                    DateTime dtl1        = Convert.ToDateTime(spl1[1] + '-' + spl1[0] + '-' + spl1[2]);
                    string   exmdate     = dtl1.ToString("dd");
                    string   exmmonth    = dtl1.ToString("MM");
                    string   exmmonthful = dtl1.ToString("MMMM");
                    string   exmyear     = dtl1.ToString("yyyy");
                    string   examdate    = exmyear + '-' + exmmonth + '-' + exmdate;
                    string   prmonthyea  = exmmonthful + '-' + exmyear;

                    // string sql ="select distinct s.subject_code, de.Dept_Name,c.Course_Name,d.Degree_Code,s.subject_name,d.Acronym,et.exam_date,et.exam_session from exmtt e,exmtt_det et,course c,Degree d,Department de,subject s  where  e.degree_code=d.Degree_Code and c.Course_Id=d.Course_Id and d.Dept_Code=de.Dept_Code  and et.exam_date='" + examdate + "' and e.exam_Month='" + exMonthName + "' and e.Exam_Year='" + ddlYear.SelectedItem.Text + "' and et.exam_session='" + ddlSession.SelectedItem.Text + "' and et.coll_code in('" + valCollege + "')and e.exam_code=et.exam_code and et.subject_no=s.subject_no group by c.Course_Name, de.Dept_Name,d.Degree_Code,s.subject_name,d.Acronym,et.exam_date,et.exam_session,s.subject_code order by de.Dept_Name";
                    //string sql = "select distinct s.subject_code,de.Dept_Name,c.Course_Name,s.subject_name,et.exam_date,et.exam_session,d.Acronym,COUNT(ea.roll_no) as stucount from Exam_Details e,exam_application ea,exam_appl_details ed,subject s,exmtt_det et, Registration r,Degree d,course c,Department de where e.exam_code=ea.exam_code and ea.appl_no=ed.appl_no and ed.subject_no=s.subject_no and s.subject_no=et.subject_no and ea.roll_no =r.Roll_No and r.degree_code=d.Degree_Code and d.Dept_Code=de.Dept_Code and d.Course_Id=c.Course_Id and e.Exam_Month='" + exMonthName + "'and et.coll_code in('" + valCollege + "') and e.Exam_year='" + ddlYear.SelectedItem.Text + "' and  et.exam_date='" + examdate + "' and et.exam_session='" + ddlSession.SelectedItem.Text + "' group by s.subject_code,de.Dept_Name,c.Course_Name,s.subject_name,et.exam_date,et.exam_session,d.Acronym,c.type order by de.Dept_Name";

                    string sql = "select  s.subject_code,(c.Course_Name+'-'+de.Dept_Name) as course,c.Edu_Level,s.subject_name,et.exam_date,et.exam_session,d.Acronym,COUNT(ea.roll_no) as stucount from Exam_Details e,exam_application ea,exam_appl_details ed,subject s,exmtt_det et, Registration r,Degree d,course c,Department de where e.exam_code=ea.exam_code and ea.appl_no=ed.appl_no and ed.subject_no=s.subject_no and s.subject_no=et.subject_no and ea.roll_no =r.Roll_No and r.degree_code=d.Degree_Code and d.Dept_Code=de.Dept_Code and d.Course_Id=c.Course_Id and e.Exam_Month='" + exMonthName + "' and et.coll_code in('" + valCollege + "') and e.Exam_year='" + ddlYear.SelectedItem.Text + "'  and  et.exam_date='" + examdate + "' and et.exam_session='" + ddlSession.SelectedItem.Text + "'  group by s.subject_code,de.Dept_Name,c.Course_Name,s.subject_name,et.exam_date,et.exam_session,d.Acronym,c.Edu_Level,c.type order by  s.subject_code";
                    ds = dt.select_method_wo_parameter(sql, "text");
                    int tabrow = ds.Tables[0].Rows.Count;
                    if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    {
                        Fpspread.Sheets[0].RowCount = 0;
                        for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                        {
                            Fpspread.Sheets[0].RowCount++;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Text            = "Date of Exam";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Bold       = true;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Name       = "Book Antiqua";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Size       = FontUnit.Medium;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
                            Fpspread.Columns[0].Width = 50;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Text            = "Session";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Bold       = true;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Name       = "Book Antiqua";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Size       = FontUnit.Medium;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].HorizontalAlign = HorizontalAlign.Center;
                            Fpspread.Columns[1].Width = 50;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Text            = "Subject Code";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Bold       = true;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Name       = "Book Antiqua";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Size       = FontUnit.Medium;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].HorizontalAlign = HorizontalAlign.Center;
                            Fpspread.Columns[1].Width = 50;
                            string subj     = Convert.ToString(ds.Tables[0].Rows[j]["subject_code"]).Trim();
                            string subjname = Convert.ToString(ds.Tables[0].Rows[j]["subject_name"]).Trim();
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Text            = "Branch";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Bold       = true;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Name       = "Book Antiqua";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Size       = FontUnit.Medium;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].HorizontalAlign = HorizontalAlign.Center;
                            Fpspread.Columns[1].Width = 50;
                            string edulevel = Convert.ToString(ds.Tables[0].Rows[j]["Edu_Level"]).Trim();
                            ds.Tables[0].DefaultView.RowFilter = " Edu_Level='" + Convert.ToString(ds.Tables[0].Rows[j]["Edu_Level"]) + "' ";
                            if (!hat.ContainsKey(edulevel))
                            {
                                hat.Add(edulevel, subjname);
                                Spdegree.InnerHtml = "DEGREE: " + edulevel + "";
                            }
                            string bran = Convert.ToString(ds.Tables[0].Rows[j]["course"]).Trim();
                            ds.Tables[0].DefaultView.RowFilter = " course='" + Convert.ToString(ds.Tables[0].Rows[j]["course"]) + "' ";
                            colSpanDV = ds.Tables[0].DefaultView;
                            if (BranchCodeReplication != Convert.ToString(ds.Tables[0].Rows[j]["course"]))
                            {
                                Fpspread.Sheets[0].Cells[j, 3].Text            = Convert.ToString(bran).Trim();
                                Fpspread.Sheets[0].Cells[j, 3].VerticalAlign   = VerticalAlign.Middle;
                                Fpspread.Sheets[0].Cells[j, 3].HorizontalAlign = HorizontalAlign.Left;
                                if (BranchCodeReplication != "")
                                {
                                    if (colSpanDV.Count > 0)
                                    {
                                        Fpspread.Sheets[0].SpanModel.Add(Fpspread.Sheets[0].RowCount - colspanbranch - 1, 3, colspanbranch, 1);
                                    }
                                }
                                colspanbranch = 0;
                            }
                            colspanbranch++;
                            BranchCodeReplication = Convert.ToString(ds.Tables[0].Rows[j]["course"]).Trim();
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Text            = "Q.P Code/Booklet code";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Bold       = true;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Name       = "Book Antiqua";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Size       = FontUnit.Medium;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].HorizontalAlign = HorizontalAlign.Center;
                            Fpspread.Columns[1].Width = 50;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 5].Text            = "Answer Paper Packet Number Alloted by College";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 5].Font.Bold       = true;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 5].Font.Name       = "Book Antiqua";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 5].Font.Size       = FontUnit.Medium;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 5].HorizontalAlign = HorizontalAlign.Center;
                            Fpspread.Columns[1].Width = 50;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 6].Text            = "Subject";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 6].Font.Bold       = true;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 6].Font.Name       = "Book Antiqua";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 6].Font.Size       = FontUnit.Medium;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 6].HorizontalAlign = HorizontalAlign.Center;
                            Fpspread.Columns[1].Width = 50;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 7].Text            = "Total Answer scripts";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 7].Font.Bold       = true;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 7].Font.Name       = "Book Antiqua";
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 7].Font.Size       = FontUnit.Medium;
                            Fpspread.Sheets[0].ColumnHeader.Cells[0, 7].HorizontalAlign = HorizontalAlign.Center;
                            Fpspread.Columns[1].Width = 50;
                            string  strquery = "select *,district+' - '+pincode  as districtpin from collinfo where college_code='" + Convert.ToString(Session["collegecode"]).Trim() + "'";
                            DataSet ds1      = new DataSet();
                            ds1.Dispose();
                            ds1.Reset();
                            ds1 = dt.select_method_wo_parameter(strquery, "Text");
                            spF1College.InnerText = ds1.Tables[0].Rows[0]["Collname"].ToString();
                            string catagor = ds1.Tables[0].Rows[0]["category"].ToString();
                            // string[] strpa = Convert.ToString(ds1.Tables[0].Rows[0]["affliatedby"]).Trim().Split(',');
                            spcategory.InnerHtml = "(" + Convert.ToString(catagor).Trim() + " & " + Convert.ToString(ds1.Tables[0].Rows[0]["university"]).Trim() + ")";
                            spF1Date.InnerText   = "Month & year of Exam: " + Convert.ToString(prmonthyea).Trim() + "";
                            spHead.InnerText     = "DESPATCH OF ANSWER PACKETS ";
                            dateoedel.InnerHtml  = "Date of Delvery:" + Convert.ToString(ddlDate.SelectedValue).Trim() + "";
                            spsign.InnerHtml     = "Signature of the Anna University Representative";
                            spsignchif.InnerHtml = "Signature of the Chief Superintendent";
                            spsig.InnerHtml      = "Authorized Signatory office of COE";
                            spnbun.InnerHtml     = "Received " + ds.Tables[0].Rows.Count + " bundles from exam cell";
                            ds.Tables[0].DefaultView.RowFilter = " subject_code='" + Convert.ToString(ds.Tables[0].Rows[j]["subject_code"]) + "' ";
                            colSpansub = ds.Tables[0].DefaultView;
                            if (subjcode != Convert.ToString(ds.Tables[0].Rows[j]["subject_code"]))
                            {
                                if (subjcode != "")
                                {
                                    if (colSpansub.Count > 0)
                                    {
                                        dsrow = j;
                                        inrow = j;
                                        dsrow = ds.Tables[0].Rows.Count - 1;
                                        Fpspread.Sheets[0].SpanModel.Add(Fpspread.Sheets[0].RowCount - colspansubj - 1, 2, colspansubj, 1);
                                        Fpspread.Sheets[0].SpanModel.Add(Fpspread.Sheets[0].RowCount - colspansubj - 1, 6, colspansubj, 1);
                                    }
                                }
                                colspansubj = 0;
                            }
                            colspansubj++;
                            if (dsrow == 0 && j == ds.Tables[0].Rows.Count - 1)
                            {
                                Fpspread.Sheets[0].SpanModel.Add(0, 2, ds.Tables[0].Rows.Count, 1);
                                Fpspread.Sheets[0].SpanModel.Add(0, 6, ds.Tables[0].Rows.Count, 1);
                            }
                            if (dsrow == j)
                            {
                                Fpspread.Sheets[0].SpanModel.Add(inrow, 2, colspansubj, 1);
                                Fpspread.Sheets[0].SpanModel.Add(inrow, 6, colspansubj, 1);
                            }
                            subjcode = Convert.ToString(ds.Tables[0].Rows[j]["subject_code"]).Trim();
                            if (!hat.ContainsKey(subj))
                            {
                                hat.Add(subj, bran);
                                Fpspread.Sheets[0].Cells[j, 2].Text            = Convert.ToString(subj).Trim();
                                Fpspread.Sheets[0].Cells[j, 2].Font.Size       = FontUnit.Medium;
                                Fpspread.Sheets[0].Cells[j, 2].VerticalAlign   = VerticalAlign.Middle;
                                Fpspread.Sheets[0].Cells[j, 2].HorizontalAlign = HorizontalAlign.Center;
                                Fpspread.Sheets[0].Cells[j, 6].Text            = Convert.ToString(subjname).Trim();
                                Fpspread.Sheets[0].Cells[j, 6].Font.Size       = FontUnit.Medium;
                                Fpspread.Sheets[0].Cells[j, 6].VerticalAlign   = VerticalAlign.Middle;
                                Fpspread.Sheets[0].Cells[j, 6].HorizontalAlign = HorizontalAlign.Left;
                            }
                            Fpspread.Sheets[0].Cells[j, 7].Text            = Convert.ToString(ds.Tables[0].Rows[j]["stucount"]).Trim();
                            Fpspread.Sheets[0].Cells[j, 7].HorizontalAlign = HorizontalAlign.Center;
                            Fpspread.Sheets[0].Cells[j, 7].Font.Size       = FontUnit.Medium;
                            total += Convert.ToInt32(ds.Tables[0].Rows[j]["stucount"].ToString());
                        }
                        Fpspread.Sheets[0].SpanModel.Add(0, 0, Fpspread.Sheets[0].RowCount, 1);
                        Fpspread.Sheets[0].Cells[0, 0].Text            = Convert.ToString(exdate).Trim();
                        Fpspread.Sheets[0].Cells[0, 0].Font.Size       = FontUnit.Medium;
                        Fpspread.Sheets[0].Cells[0, 0].VerticalAlign   = VerticalAlign.Middle;
                        Fpspread.Sheets[0].Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
                        Fpspread.Sheets[0].SpanModel.Add(0, 1, Fpspread.Sheets[0].RowCount, 1);
                        Fpspread.Sheets[0].Cells[0, 1].Text            = Convert.ToString(ddlSession.SelectedValue).Trim();
                        Fpspread.Sheets[0].Cells[0, 1].Font.Size       = FontUnit.Medium;
                        Fpspread.Sheets[0].Cells[0, 1].VerticalAlign   = VerticalAlign.Middle;
                        Fpspread.Sheets[0].Cells[0, 1].HorizontalAlign = HorizontalAlign.Center;
                        Fpspread.Sheets[0].RowCount++;
                        Fpspread.Sheets[0].SpanModel.Add(Fpspread.Sheets[0].RowCount - 1, 0, 1, 5);
                        Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 0].Text            = "Total";
                        Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 7].Text            = Convert.ToString(total).Trim();
                        Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 7].Font.Size       = FontUnit.Medium;
                        Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 7].HorizontalAlign = HorizontalAlign.Center;
                        Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Right;
                        Fpspread.Sheets[0].RowCount++;
                        Fpspread.Sheets[0].SpanModel.Add(Fpspread.Sheets[0].RowCount - 1, 0, 1, Fpspread.Sheets[0].Columns.Count);
                        if (ddlSession.SelectedValue.Trim() == "A.N")
                        {
                            sess = "Afternoon";
                        }
                        else
                        {
                            sess = "Forenoon";
                        }
                        Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 0].Text            = "1.Certificate of opening of sessionwise package of Q.P for " + sess + "";
                        Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Left;
                        Fpspread.Sheets[0].RowCount++;
                        Fpspread.Sheets[0].SpanModel.Add(Fpspread.Sheets[0].RowCount - 1, 0, 1, Fpspread.Sheets[0].Columns.Count);
                        Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 0].Text            = "2.Student Attendance Sheet original copies for " + sess + "";
                        Fpspread.Sheets[0].Cells[Fpspread.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Left;
                        Fpspread.Sheets[0].PageSize = Fpspread.Sheets[0].RowCount;
                        Fpspread.Width  = 900;
                        Fpspread.Height = 900;
                        Fpspread.SaveChanges();
                    }

                    else
                    {
                        lblerror.Text    = "No Records Found";
                        lblerror.Visible = true;
                    }
                }

                else
                {
                    lblerror.Text           = "Please select the date and session";
                    Fpspread.Visible        = false;
                    lblerror.Visible        = true;
                    btn_directprint.Visible = false;
                }
            }
            else
            {
                lblerror.Text           = "Please select all field";
                Fpspread.Visible        = false;
                lblerror.Visible        = true;
                btn_directprint.Visible = false;
            }
        }
        catch (Exception ex)
        { da.sendErrorMail(ex, collegeCode, "DespatchOfAnswerPackets"); }
    }
コード例 #2
0
    protected void btngo_Click(object sender, EventArgs e)
    {
        try
        {
            //divfpspread.Visible = true;
            btnxl.Visible              = true;
            lblrptname.Visible         = true;
            txtexcelname.Visible       = true;
            Printcontrol.Visible       = false;
            btnprintmaster.Visible     = true;
            pnlContent1.Visible        = true;
            Fpspread.Sheets[0].Visible = true;
            string valCollege = string.Empty;
            lblnorec.Visible = false;
            string sumpart = string.Empty;
            int    value   = 0;
            int    sno     = 0;
            valCollege = rs.GetSelectedItemsValueAsString(cblCollege);
            if (valCollege != "")
            {
                if (txtfromDate.Text != "" && txttoDate.Text != "")
                {
                    frdate = txtfromDate.Text;
                    todate = txttoDate.Text;
                    string   dt     = frdate;
                    string[] dsplit = dt.Split(new Char[] { '-' });
                    frdate               = dsplit[2].ToString() + "-" + dsplit[1].ToString() + "-" + dsplit[0].ToString();
                    demfcal              = int.Parse(dsplit[2].ToString());
                    demfcal              = demfcal * 12;
                    cal_from_date        = demfcal + int.Parse(dsplit[1].ToString());
                    cal_from_attdate_tmp = demfcal + int.Parse(dsplit[1].ToString());

                    monthcal           = cal_from_date.ToString();
                    dt                 = todate;
                    dsplit             = dt.Split(new Char[] { '-' });
                    spltodate          = dsplit[1].ToString() + "/" + dsplit[0].ToString() + "/" + dsplit[2].ToString();
                    todate             = dsplit[2].ToString() + "-" + dsplit[1].ToString() + "-" + dsplit[0].ToString();
                    demtcal            = int.Parse(dsplit[2].ToString());
                    demtcal            = demtcal * 12;
                    cal_to_date        = demtcal + int.Parse(dsplit[1].ToString());
                    cal_to_attdate_tmp = demtcal + int.Parse(dsplit[1].ToString());
                    per_from_attdate   = Convert.ToDateTime(frdate);
                    per_to_attdate     = Convert.ToDateTime(todate);
                    if (per_from_attdate <= per_to_attdate)
                    {
                        #region colbinding
                        Fpspread.Sheets[0].AutoPostBack         = true;
                        Fpspread.Sheets[0].RowHeader.Visible    = false;
                        Fpspread.Sheets[0].ColumnHeader.Visible = true;
                        MyStyle.Font.Size       = FontUnit.Medium;
                        MyStyle.Font.Name       = "Book Antiqua";
                        MyStyle.Font.Bold       = true;
                        MyStyle.HorizontalAlign = HorizontalAlign.Center;
                        MyStyle.ForeColor       = Color.White;
                        MyStyle.BackColor       = ColorTranslator.FromHtml("#0CA6CA");
                        Fpspread.Sheets[0].ColumnHeader.DefaultStyle = MyStyle;
                        Fpspread.CommandBar.Visible    = false;
                        Fpspread.Sheets[0].ColumnCount = 5;
                        Fpspread.Sheets[0].RowCount    = 0;
                        Fpspread.BorderWidth           = 2;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Text            = "S.No";
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Bold       = true;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Name       = "Book Antiqua";
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].Font.Size       = FontUnit.Medium;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 0].HorizontalAlign = HorizontalAlign.Center;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Text            = "College Name";
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Bold       = true;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Name       = "Book Antiqua";
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].Font.Size       = FontUnit.Medium;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 1].HorizontalAlign = HorizontalAlign.Center;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Text            = "Total Strenth";
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Bold       = true;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Name       = "Book Antiqua";
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].Font.Size       = FontUnit.Medium;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 2].HorizontalAlign = HorizontalAlign.Center;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Text            = "Present %";
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Bold       = true;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Name       = "Book Antiqua";
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].Font.Size       = FontUnit.Medium;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 3].HorizontalAlign = HorizontalAlign.Center;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Text            = "Absent %";
                        Fpspread.Columns[4].Width = 25;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Bold       = true;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Name       = "Book Antiqua";
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].Font.Size       = FontUnit.Medium;
                        Fpspread.Sheets[0].ColumnHeader.Cells[0, 4].HorizontalAlign = HorizontalAlign.Center;
                        #endregion
                        //  // string sql = "select * from Registration r,attendance a where r.Roll_No=a.roll_no and r.App_No=a.Att_App_no and a.Att_CollegeCode= r.college_code and r.CC='0' and r.DelFlag='0' and r.Exam_Flag <>'debar' and a.month_year between '" + cal_from_attdate_tmp + "' and '" + cal_to_attdate_tmp + "' and r.college_code in('" + valCollege + "')";
                        string sql = "select * from Registration r,attendance a where r.Roll_No=a.roll_no and r.App_No=a.Att_App_no and a.Att_CollegeCode= r.college_code and r.CC='0' and r.DelFlag='0' and r.Exam_Flag <>'debar' and a.month_year between '" + cal_from_attdate_tmp + "' and '" + cal_to_attdate_tmp + "' and r.college_code in('" + valCollege + "') order by r.degree_code,r.Roll_No";

                        ds = da.select_method_wo_parameter(sql, "text");
                        int fprow = 0;
                        for (int clg = 0; clg < cblCollege.Items.Count; clg++)
                        {
                            if (cblCollege.Items[clg].Selected == true)
                            {
                                double present        = 0;
                                double absent         = 0;
                                int    hour           = 0;
                                double tolhour_perclg = 0;
                                int    cunstu         = 0;
                                //int tolhour_perclg = 0;
                                Fpspread.Sheets[0].RowCount++;
                                sno++;
                                string stucon = da.GetFunction("select count(distinct r.app_no) from Registration r where r.CC='0' and r.DelFlag='0' and r.Exam_Flag <>'debar' and r.college_code='" + cblCollege.Items[clg].Value + "' and Adm_Date<='" + spltodate + "'");


                                //string stucon = da.GetFunction("select count(distinct r.roll_no) from Registration r,attendance a where r.Roll_No=a.roll_no and r.App_No=a.Att_App_no and a.Att_CollegeCode= r.college_code and r.CC='0' and r.DelFlag='0' and r.Exam_Flag <>'debar' and a.month_year between '" + cal_from_attdate_tmp + "' and '" + cal_to_attdate_tmp + "' and r.college_code in('" + cblCollege.Items[clg].Value + "')");
                                //ds = da.select_method("bind_branch", has, "sp");
                                //string gendegree = "select distinct r.degree_code from Registration r,attendance a where r.Roll_No=a.roll_no and r.App_No=a.Att_App_no and a.Att_CollegeCode= r.college_code  and r.CC='0' and r.DelFlag='0' and r.Exam_Flag <>'debar' and a.month_year between '" + cal_from_attdate_tmp + "'  and '" + cal_to_attdate_tmp + "' and  r.college_code='" + cblCollege.Items[clg].Value +"' order by r.degree_code";
                                string valcolle_code = Convert.ToString(cblCollege.Items[clg].Value).Trim();
                                int    valcoll_code;
                                int.TryParse(valcolle_code, out valcoll_code);
                                has.Clear();
                                has.Add("valcoll_code", valcoll_code);
                                has.Add("cal_from_attdate_tmp", cal_from_attdate_tmp);
                                has.Add("cal_to_attdate_tmp", cal_to_attdate_tmp);
                                DataSet degreegener = da.select_method("attoverallclg_coll", has, "sp");
                                //DataSet degreegener = da.select_method_wo_parameter(gendegree, "text");
                                for (int degrow = 0; degrow < degreegener.Tables[0].Rows.Count; degrow++)
                                {
                                    int    tolhour_perdeg = 0;
                                    string hrs            = da.GetFunction("select No_of_hrs_per_day from PeriodAttndSchedule where degree_code='" + degreegener.Tables[0].Rows[degrow]["degree_code"] + "'");
                                    int.TryParse(hrs, out hour);
                                    //string stud =da.GetFunction(" select count(distinct r.roll_no) from Registration r,attendance a where r.Roll_No=a.roll_no and r.App_No=a.Att_App_no and a.Att_CollegeCode= r.college_code  and r.CC='0' and r.DelFlag='0' and r.Exam_Flag <>'debar' and a.month_year between '" + cal_from_attdate_tmp + "'  and '" + cal_to_attdate_tmp + "'and r.degree_code='" + degreegener.Tables[0].Rows[degrow]["degree_code"] + "' and  r.college_code='" + cblCollege.Items[clg].Value + "'");

                                    ds.Tables[0].DefaultView.RowFilter = "college_code='" + cblCollege.Items[clg].Value + "' and degree_code='" + degreegener.Tables[0].Rows[degrow]["degree_code"] + "'";
                                    DataView clgfilter = ds.Tables[0].DefaultView;
                                    for (DateTime dtt = per_from_attdate; dtt <= per_to_attdate; dtt = dtt.AddDays(1))
                                    {
                                        //for (int degrow = 0; degrow < clgfilter.Count; degrow++)
                                        // {

                                        //ds.Tables[0].DefaultView.RowFilter = "month_year='" + cal_from_date + "'";

                                        for (int i = 1; i <= hour; i++)
                                        {
                                            for (int sturow = 0; sturow < clgfilter.Count; sturow++)
                                            {
                                                date = "d" + dtt.Day.ToString("") + "d" + i.ToString();
                                                string maxval = Convert.ToString(clgfilter[sturow][date]).Trim();
                                                if (maxval != "" && maxval != "0" && maxval != "NULL" && maxval != "Null" && maxval != "null" && maxval != "H" && maxval != "NJ" && maxval != "Null")
                                                {
                                                    int.TryParse(maxval, out value);

                                                    string valqu = da.GetFunction("select CalcFlag  from AttMasterSetting where collegecode=13 and LeaveCode='" + value + "'  group by CalcFlag");
                                                    int.TryParse(valqu, out value);

                                                    if (value == 0)
                                                    {
                                                        present = present + 1;
                                                    }
                                                    else
                                                    {
                                                        absent = absent + 1;
                                                    }
                                                }
                                                else
                                                {
                                                }
                                            }
                                        }
                                        tolhour_perdeg += hour;
                                    }
                                    //int.TryParse(stud, out cunstu);
                                    //tolhour_perdeg *= cunstu;
                                    //tolhour_perclg += tolhour_perdeg;
                                    tolhour_perclg = present + absent;
                                }

                                if (present != 0)
                                {
                                    present = (present / tolhour_perclg);
                                    present = Math.Round(present, 3);
                                    present = present * 100;
                                    sumpart = String.Format("{0:0.00}", present);
                                }
                                if (absent != 0)
                                {
                                    absent  = (absent / tolhour_perclg);
                                    absent  = Math.Round(absent, 3);
                                    absent  = absent * 100;
                                    sumpart = String.Format("{0:0.00}", absent);
                                }
                                Fpspread.Sheets[0].Cells[fprow, 0].Text = sno.ToString();
                                Fpspread.Columns[0].Width = 10;
                                Fpspread.Sheets[0].Cells[fprow, 0].Font.Bold       = true;
                                Fpspread.Sheets[0].Cells[fprow, 0].Font.Name       = "Book Antiqua";
                                Fpspread.Sheets[0].Cells[fprow, 0].Font.Size       = FontUnit.Medium;
                                Fpspread.Sheets[0].Cells[fprow, 0].HorizontalAlign = HorizontalAlign.Center;
                                Fpspread.Sheets[0].Cells[fprow, 1].Text            = cblCollege.Items[clg].ToString();
                                Fpspread.Columns[1].Width = 100;
                                Fpspread.Sheets[0].Cells[fprow, 1].Font.Bold       = true;
                                Fpspread.Sheets[0].Cells[fprow, 1].Font.Name       = "Book Antiqua";
                                Fpspread.Sheets[0].Cells[fprow, 1].Font.Size       = FontUnit.Medium;
                                Fpspread.Sheets[0].Cells[fprow, 1].HorizontalAlign = HorizontalAlign.Left;
                                Fpspread.Sheets[0].Cells[fprow, 2].Text            = stucon.ToString();
                                Fpspread.Columns[2].Width = 25;
                                Fpspread.Sheets[0].Cells[fprow, 2].Font.Bold       = true;
                                Fpspread.Sheets[0].Cells[fprow, 2].Font.Name       = "Book Antiqua";
                                Fpspread.Sheets[0].Cells[fprow, 2].Font.Size       = FontUnit.Medium;
                                Fpspread.Sheets[0].Cells[fprow, 2].HorizontalAlign = HorizontalAlign.Center;
                                Fpspread.Sheets[0].Cells[fprow, 3].Text            = present.ToString();
                                Fpspread.Columns[3].Width = 25;
                                Fpspread.Sheets[0].Cells[fprow, 3].Font.Bold       = true;
                                Fpspread.Sheets[0].Cells[fprow, 3].Font.Name       = "Book Antiqua";
                                Fpspread.Sheets[0].Cells[fprow, 3].Font.Size       = FontUnit.Medium;
                                Fpspread.Sheets[0].Cells[fprow, 3].HorizontalAlign = HorizontalAlign.Center;
                                Fpspread.Sheets[0].Cells[fprow, 4].Text            = absent.ToString();
                                Fpspread.Sheets[0].Cells[fprow, 4].Font.Bold       = true;
                                Fpspread.Sheets[0].Cells[fprow, 4].Font.Name       = "Book Antiqua";
                                Fpspread.Sheets[0].Cells[fprow, 4].Font.Size       = FontUnit.Medium;
                                Fpspread.Sheets[0].Cells[fprow, 4].HorizontalAlign = HorizontalAlign.Center;
                                Fpspread.Sheets[0].PageSize = Fpspread.Sheets[0].RowCount;
                                Fpspread.Width  = 900;
                                Fpspread.Height = 700;
                                Fpspread.SaveChanges();
                                fprow++;
                            }
                        }
                    }
                    else
                    {
                        Fpspread.Visible       = false;
                        btnxl.Visible          = false;
                        lblrptname.Visible     = false;
                        txtexcelname.Visible   = false;
                        Printcontrol.Visible   = false;
                        btnprintmaster.Visible = false;
                        lblnorec.Visible       = true;
                        lblnorec.Text          = "Please select the To date  is greater then From date";
                    }
                }
                else
                {
                    Fpspread.Visible       = false;
                    btnxl.Visible          = false;
                    lblrptname.Visible     = false;
                    txtexcelname.Visible   = false;
                    Printcontrol.Visible   = false;
                    btnprintmaster.Visible = false;
                    lblnorec.Visible       = true;
                    lblnorec.Text          = "Please select the From date and To date";
                }
            }
            else
            {
                btnxl.Visible          = false;
                lblrptname.Visible     = false;
                txtexcelname.Visible   = false;
                Printcontrol.Visible   = false;
                btnprintmaster.Visible = false;
                Printcontrol.Visible   = false;
                Fpspread.Visible       = false;
                lblnorec.Visible       = true;
                lblnorec.Text          = "Please select the College";
            }
        }
        catch (Exception ex)
        {
            da.sendErrorMail(ex, collegeCode, "OverallcollegeAttendancepercentage");
        }
    }