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


Today I want to discuss in detail how to show an attendance sheet and export to excel from a grid . I will discuss in this article with detail description with code snippets. The Front end is .Net and back-end is anything oracle or sql-server ?

I had gone through forum messages particularly there was a question raised that is about attendance sheet and bio metric devices . I had written how to work with Bio-metric devices in my previous article this 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;
}


the output as follows....
attendancexcel


Attachments

  • attndurldata (46313-1-attndurldata.bmp)
  • attendpresent1 (46313-2-attendpresent1.bmp)
  • attendancexcel (46313-3-attendancexcel.bmp)
  • Article by srirama
    A Good advice from parent to a Child , Master to a Student , Scholar to an Ignorant is like a doctor prescribed pill it is bitter to take but when they take it will do all good for them --- Bhushan

    Follow srirama or read 73 articles authored by srirama

    Comments

    No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: