How to show Attendance sheet in your Excel sheet using Asp.net ?

I saw many forum messages particularly there was a question raised that is about attendance sheet and bio metric devices . I wrote how to work with Bio-metric devices in my previous article that link was referred in many of the websites. But in this article I want to show how to export an excel attendance data which is present in a grid with detail description and code.

Many of them working in Attendance related projects , So first we have to fetch the data from the respective database . so for that I had two tables associated with it. One is Branch staff, Attend_uniform resource locator , I fetched these data in two tables the data in my Datasets are as follows.

see the below link visualizers. Here I added the searched date to current date for the requirement month. You can see the dataset visusalizer .



After fetching the data we need to keep present and absents against the dates.

See the below dataset visualizer how it will be see the below screen shots...



then I loop all the above datatable filtering the first image shown with acccode and att_date...

If such things are found and acccode and att_date I will update the dataset with P if present , A if the absent. How it will be I will show in below the snapshot. I will keep a check or condition such that if the dayofweek is sunday i will keep 's' notation or no such data available i will keep it gap i.e. null. Our datatable is prepared according to this.

The next step, is converting to Excel that is found in many websites in Google search.

 con.Open();
        System.Data.DataTable dtatturl = new System.Data.DataTable();
        System.Data.DataTable dttype = new System.Data.DataTable();
        System.Data.DataTable dtmax = new System.Data.DataTable();
        System.Data.DataTable datTab = new System.Data.DataTable("att_url");
        try
        {
            datTab.Columns.Add("SNO", typeof(int));
            datTab.Columns.Add("ACCODE", typeof(string));
            datTab.Columns.Add("EMPNAME", typeof(string));
            int noOfDays = DateTime.DaysInMonth(Convert.ToDateTime(dpTime.Text).Year, Convert.ToDateTime(dpTime.Text).Month);
// DateTime.DaysInMonth(Convert.ToDateTime(dpTime.Text).Year.ToString("yyyy"), Convert.ToDateTime(dpTime.Text).ToString("MMM"));
            for (int i = 1; i <= noOfDays; i++)
                datTab.Columns.Add("" + i + "", typeof(string));
            int sNo = 1;

            //First row_number...
            cmd = new OleDbCommand("SELECT row_number() OVER (ORDER BY ACCODE asc) as SNO, 
ACCODE,EMPNAME FROM BRSTAFF WHERE  accode not in ('3054','5050','5033','5035','5063','5067','5070') 
and  STATUS='0' AND trim(CBSBR)='" + branch + "' GROUP BY ACCODE,EMPNAME ORDER BY 
SUM(BASIC+DA+HRA+OA+MA) Asc", con);
            OleDbDataAdapter dataadapt = new OleDbDataAdapter(cmd);
            dataadapt.Fill(datTab);

            cmd = new OleDbCommand("select att_type, att_date , accode from ATTEND_URL where cbsbr='" 
+ branch + "' ", con);
            OleDbDataAdapter adaptsql = new OleDbDataAdapter(cmd);
            adaptsql.Fill(dtatturl);

            cmd = new OleDbCommand("select max(att_date)   from ATTEND_URL where cbsbr='" + branch + 
"' and TO_CHAR(att_date,'MON') ='" + Convert.ToDateTime(dpTime.Text).ToString("MMM").ToUpper() + "'", con);
            dataadapt = new OleDbDataAdapter(cmd);
            dataadapt.Fill(dtmax);

            cmd.Dispose();
            sNo = 1;
            int li = 0;
            int sun = 1;
            int s = 0;
            List count = new List();
            List ds = new List();


            for (int i = 0; i < datTab.Rows.Count; i++)
            {
                int z = 1;
                string dd = "";
                string k;
                DateTime atDt = DateTime.Now;
                if (dtmax.Rows.Count > 0)
                {
                    for (int ik = 0; ik < dtmax.Rows.Count; ik++)
                    {
                        if (dtmax.Rows[ik][0] != null)
                        {
                            atDt = Convert.ToDateTime(dtmax.Rows[ik][0].ToString());
                        }
                    }
                }
                dd = String.Format("{0:dd}", atDt);
                int date = Convert.ToInt32(dd);
                // dr.Close();
                cmd.Dispose();
                DateTime mn;
                List atType = new List();
                List atDate = new List();
                

                    for (int it = 0; it < datTab.Rows.Count;)
                    {
                        for (int j = 1; j <= date; j++)
                        {
                            if (j.ToString().Length == 1)
                                k = "0" + j.ToString();
                            else
                                k = j.ToString();
                            mn = Convert.ToDateTime(k + "-" + Convert.ToDateTime(dpTime.Text).ToString("MMM").ToUpper() + "-" + 
Convert.ToDateTime(dpTime.Text).ToString("yyyy").ToUpper());
                            string strexp = " accode ='" + datTab.Rows[it]["ACCODE"].ToString() + "' and  ATT_DATE 
='" + Convert.ToDateTime(mn).ToString("dd-MMM-yyyy") + "' ";
                            DataRow[] drt = dtatturl.Select(strexp);
                            foreach (System.Data.DataRow drs in drt)
                            {
                                if (drt != null && Convert.ToString(Convert.ToDateTime(mn).DayOfWeek) != "Sunday")
                                {
                                    datTab.Rows[it]["" + z + ""] = drs[0].ToString();
                                }
                                else
                                {
                                    
                                }
                                
                                z++;
                            }
                            if (drt.Length == 0)
                            {
                                if (Convert.ToString(Convert.ToDateTime(mn).DayOfWeek) == "Sunday")
                                {
                                    datTab.Rows[it]["" + z + ""] = "S";
                                    z++;
                                }
                                else
                                {
                                    datTab.Rows[it]["" + z + ""] = " ";
                                    z++;
                                }
                            }
                           
                        }
                        it++;
                        z = 1;
                        // dr.Close();
                        //  cmd.Dispose();                                                                    
                                           
               
                    sNo = sNo + 1;
                   
                }

            }
            datTab.AcceptChanges();
        }
        catch (Exception ex)
        {
            Message.Text = ex.Message;
        }
        finally
        {
            con.Close();
        }

        return datTab;
    }