1) new Excel Sheet

DataBase




create procedure USPAttendanceReport
(
  @dateFrom datetime,
  @dateTo datetime
 )
as
begin
      select srno, name into #temp from tbl_login where status=1 and srno>1
  select distinct convert(varchar,Coming_Time,103) as Coming_Time 
  from tbl_Attendance  where Coming_Time>=@dateFrom and Going_Time<=@dateTo  order by Coming_Time
  select * from #temp
  
  
  select User_Id,convert(varchar,Coming_Time,103) as Coming_Time,convert(nvarchar,CAST(Coming_Time as time),100) as Coming_Times,
  convert(nvarchar,CAST(Going_Time as time),100) as Going_Time ,DATENAME(dw, Coming_Time) day_Name from tbl_Attendance  
  where Coming_Time>=@dateFrom and Going_Time<=@dateTo  order by Coming_Time
 
end





Admin/Attendance.aspx.cs


download_XL_Click es function ko find karke replace kar lena



protected void download_XL_Click(object sender, EventArgs e)
    {
        if (ddlusers.SelectedValue == "0" && txt_from.Text != "" && txt_to.Text != "")
        {
            try
            {

                string[] col1 = { "@dateFrom", "@dateTo" };
                object[] val1 = { txt_from.Text, txt_to.Text };
                DataSet ds = dal.getDataSet("USPAttendanceReport", col1, val1);
                ClosedXML.Excel.XLWorkbook wb = new ClosedXML.Excel.XLWorkbook();
                var ws = wb.Worksheets.Add("Attendance");
                int col = ds.Tables[1].Rows.Count * 2;
                ws.Cell(1, 1).Value = "Attendance from " + txt_from.Text + " to " + txt_to.Text;
                ws.Range(1, 1, 1, col + 1).Merge();
                //ws.Range(ws.Cell(row, col++), ws.Cell(row, col++)).Merge();
                ws.Range(1, 1, 1, col + 1).AddToNamed("Titles");
                // styles
                var titlesStyle = wb.Style;
                titlesStyle.Font.Bold = true;
                titlesStyle.Alignment.Horizontal = ClosedXML.Excel.XLAlignmentHorizontalValues.Left;
                titlesStyle.Fill.BackgroundColor = ClosedXML.Excel.XLColor.GreenPigment;

                // style titles row
                wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle;
                ws.Cell(2, 1).Value = "dd-mm-yyyy";
                ws.Cell(2, 1).Style.Fill.BackgroundColor = ClosedXML.Excel.XLColor.GreenPigment;
                int k = 2;
                for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
                {
                    ws.Cell(2, k).Value = ds.Tables[1].Rows[i]["name"].ToString();
                    ws.Cell(2, k).Style.Fill.BackgroundColor = ClosedXML.Excel.XLColor.GreenPigment;
                    ws.Range(2, k, 2, k + 1).Merge();
                    k = k + 2;

                }

                ws.Cell(3, 1).Value = "";
                ws.Cell(3, 1).Style.Fill.BackgroundColor = ClosedXML.Excel.XLColor.GreenPigment;
                k = 2;
                for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
                {
                    ws.Cell(3, k).Value = "In";
                    ws.Cell(3, k).Style.Fill.BackgroundColor = ClosedXML.Excel.XLColor.GreenPigment;
                    ws.Cell(3, k + 1).Value = "Out";
                    ws.Cell(3, k + 1).Style.Fill.BackgroundColor = ClosedXML.Excel.XLColor.GreenPigment;
                    k = k + 2;

                }
                //For display date start
                k = 4;
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    ws.Cell(k, 1).Value = ds.Tables[0].Rows[i]["Coming_Time"].ToString();
                    ws.Cell(k, 1).Style.NumberFormat.Format = "dd-mm-yy";
                    ws.Column(1).AdjustToContents();
                    int columnCtr = 2;
                    for (int ctrEmployee = 0; ctrEmployee < ds.Tables[1].Rows.Count; ctrEmployee++)
                    {

                        DataRow[] rowsFiltered = ds.Tables[2].Select("Coming_Time='" + ds.Tables[0].Rows[i]["Coming_Time"].ToString() + "' AND User_Id=" + Convert.ToInt32(ds.Tables[1].Rows[ctrEmployee]["srno"]));


                        foreach (DataRow rowss in rowsFiltered)
                        {
                            ws.Cell(k, columnCtr).Value = rowss.ItemArray[2].ToString();
                            ws.Cell(k, columnCtr).Style.NumberFormat.Format = "h:mm AM/PM";
                            ws.Cell(k, columnCtr + 1).Value = rowss.ItemArray[3].ToString();
                            ws.Cell(k, columnCtr + 1).Style.NumberFormat.Format = "h:mm AM/PM";
                            
                        }
                        columnCtr = columnCtr + 2;


                    }

                    if (i < ds.Tables[0].Rows.Count - 1)
                    {
                        int checkForLeave = Convert.ToInt32(ds.Tables[0].Rows[i + 1]["Coming_Time"].ToString().Split('/')[0]) - Convert.ToInt32(ds.Tables[0].Rows[i]["Coming_Time"].ToString().Split('/')[0]);
                        if (checkForLeave > 1)
                        {
                            for (int j = 0; j < checkForLeave - 1; j++)
                            {
                                k = k + 1;
                                ws.Range(k, 1, k, col + 1).Merge().Style.Fill.BackgroundColor = ClosedXML.Excel.XLColor.Red;
                            }
                        }
                        k = k + 1;
                    }

                }

                //For display date end

                //ws.Rows().AdjustToContents();
                //ws.Columns().AdjustToContents();
                // Prepare the response
                HttpResponse httpResponse = Response;
                httpResponse.Clear();
                httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                //Provide you file name here
                httpResponse.AddHeader("content-disposition", "attachment;filename=\"" + "Attendance" + ".xlsx\"");
                // Flush the workbook to the Response.OutputStream
                using (MemoryStream memoryStream = new MemoryStream())
                {
                    wb.SaveAs(memoryStream);
                    memoryStream.WriteTo(httpResponse.OutputStream);
                    memoryStream.Close();
                }

                HttpContext.Current.Response.Flush(); // Sends all currently buffered output to the client.
                HttpContext.Current.Response.SuppressContent = true;  // Gets or sets a value indicating whether to send HTTP content to the client.
                HttpContext.Current.ApplicationInstance.CompleteRequest();
            }
            catch(Exception ex)
            {

            }

        }
        else
        {
            DataSet dataset = Session["table"] as DataSet;
            ClosedXML.Excel.XLWorkbook wbook = new ClosedXML.Excel.XLWorkbook();
            for (int i = 0; i < dataset.Tables.Count; i++)
            {
                wbook.Worksheets.Add(dataset.Tables[i], dataset.Tables[i].TableName);
            }

            // Prepare the response
            HttpResponse httpResponse = Response;
            httpResponse.Clear();
            httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            //Provide you file name here
            httpResponse.AddHeader("content-disposition", "attachment;filename=\"" + "Attendance" + ".xlsx\"");
            // Flush the workbook to the Response.OutputStream
            using (MemoryStream memoryStream = new MemoryStream())
            {
                wbook.SaveAs(memoryStream);
                memoryStream.WriteTo(httpResponse.OutputStream);
                memoryStream.Close();
            }

            HttpContext.Current.Response.Flush(); // Sends all currently buffered output to the client.
            HttpContext.Current.Response.SuppressContent = true;  // Gets or sets a value indicating whether to send HTTP content to the client.
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }


    }