using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Data;
using System.IO;
public partial class ExportReportData : System.Web.UI.Page
{
    GetReports rptEmp = new GetReports();
    string saveAsFileName;
    cls_Common objCommon = new cls_Common();
    protected void Page_Init(object sender, EventArgs e)
    {
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            ExportExcel((DataSet)Session["ExportReportData"], Convert.ToString(Request.QueryString["FromDate"]), Convert.ToString(Request.QueryString["Todate"]), Convert.ToString(Request.QueryString["ReportName"]));
    }
    protected void ExportExcel(DataSet dsEmp, string frmDate, string toDate, string reportName)
    {
        DataTable dtLevelName = new DataTable();
        int rowIndex = 0;
        int rowIndex1 = 0;
        if (Convert.ToInt16(Session["parentLevelId"]) != 0)
        {
            dtLevelName = rptEmp.getHeader(Convert.ToInt16(Session["parentLevelId"]));
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        ISheet sheet;
        NPOI.SS.UserModel.IRow row;
        HSSFFont hFont;
        HSSFCellStyle hStyle;
        HSSFCellStyle IStyle;
        ISheet sheet1;
        string strPre = string.Empty;
        string NewTimeFormat = string.Empty;//
        Int32 SlNo = 0;
        int EmployeeDatacount = 1;
        NPOI.SS.UserModel.IRow row1;
        TimeSpan result;
        string fromTimeString;
        string TimeFormat = Convert.ToString(Session["ITimeFormat"]);
        double ValCount = 0.0;
        int CountDays = 0;
        if (TimeFormat == "HH:mm")//
        {
            NewTimeFormat = "HH:mm";//
        }
        else
        {
            NewTimeFormat = "hh:mm tt";//
        }
        switch (reportName)
        {
            case "Absentees":
                #region AbsentReport
                saveAsFileName = string.Format("Absentees.xls");
                sheet = workbook.CreateSheet("Absent Report");
                row = sheet.CreateRow(rowIndex);
                hFont = (HSSFFont)workbook.CreateFont();
                hFont.Boldweight = (short)FontBoldWeight.Bold;
                hFont.Color = HSSFColor.Maroon.Index;
                hFont.FontHeightInPoints = 10;//
                hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle.SetFont(hFont);
                hStyle.FillForegroundColor = IndexedColors.White.Index;//
                hStyle.FillPattern = FillPattern.SolidForeground;//
                hStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                HSSFCellStyle MainHStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                MainHStyle.SetFont(hFont);
                MainHStyle.Alignment = HorizontalAlignment.Center;
                IStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                IStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                if (dtLevelName.Rows.Count > 0)
                {
                    row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 9));
                    row.GetCell(0).CellStyle = MainHStyle;
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                }
                row.CreateCell(0).SetCellValue("Absent Report From - " + Convert.ToString(frmDate) + " - " + " To - " + Convert.ToString(frmDate));
                row.GetCell(0).CellStyle = MainHStyle;
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 9));
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue("Sl.No");
                row.CreateCell(1).SetCellValue("Employee ID");
                row.CreateCell(2).SetCellValue("Employee Name");
                row.CreateCell(3).SetCellValue("Designation");
                row.CreateCell(4).SetCellValue("Level Name");
                row.CreateCell(5).SetCellValue("Gender");
                row.CreateCell(6).SetCellValue("Shift Name");
                row.CreateCell(7).SetCellValue("Date");
                row.CreateCell(8).SetCellValue("First Session");
                row.CreateCell(9).SetCellValue("Second Session");
                //row.RowStyle = hStyle;
                for (int i = 0; i <= 9; i++)
                {
                    row.GetCell(i).CellStyle = hStyle;
                    sheet.AutoSizeColumn(i);
                }
                // row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                //row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                //row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = hStyle;
                rowIndex++;
                foreach (DataRow DR in dsEmp.Tables[0].Rows)
                {
                    row = sheet.CreateRow(rowIndex);
                    if (strPre == string.Empty || strPre != Convert.ToString(DR["Employee Id"]))
                    {
                        SlNo++;
                        row.CreateCell(0).SetCellValue(SlNo);
                    }
                    else
                    {
                        row.CreateCell(0).SetCellValue("");
                    }
                    row.CreateCell(1).SetCellValue(Convert.ToString(DR["Employee Id"]));
                    row.CreateCell(2).SetCellValue(Convert.ToString(DR["Employee Name"]));
                    row.CreateCell(3).SetCellValue(Convert.ToString(DR["Designation"]));
                    row.CreateCell(4).SetCellValue(Convert.ToString(DR["Level Name"]));
                    row.CreateCell(5).SetCellValue(Convert.ToString(DR["Gender"]));
                    row.CreateCell(6).SetCellValue(Convert.ToString(DR["Shift_ShortName"]));
                    row.CreateCell(7).SetCellValue(Convert.ToString(DR["Date"]));
                    row.CreateCell(8).SetCellValue(Convert.ToString(DR["First Session"]));
                    row.CreateCell(9).SetCellValue(Convert.ToString(DR["Second Session"]));
                    strPre = Convert.ToString(DR["Employee Id"]);
                    //Style Added the created Headers.
                    row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
               row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
               row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = IStyle;
                    //for (int i = 0; i <= 9; i++)
                    //{
                    //    row.GetCell(i).CellStyle = IStyle;
                    //    sheet.AutoSizeColumn(i);
                    //}                    
                    rowIndex++;
                }
                break;
            #endregion
            case "MonthlyAttendance":
            case "AttendanceSummary":
                #region MonthlyAttendance
                TimeSpan TS_TBH, TS_TLH, TS_HW, TS_LateEntryHrs, TS_EarlyExitHrs, TS_WDOTHours, TS_WOOTHours, TS_HOTHours, TS_SWH, TS_DWH;
                double VC_TBH = 00.00, VC_TLH = 0.0, VC_HW = 0.0, VC_LateEntryHrs = 0.0, VC_EarlyExitHrs = 0.0,
                    VC_WDOTHours = 0.0, VC_WOOTHours = 0.0, VC_HOTHours = 0.0, VC_SWH = 0.0, VC_DWH = 0.0;
                saveAsFileName = string.Format("MonthlyAttendance.xls");
                //HSSFWorkbook workbook = new HSSFWorkbook();
                sheet = workbook.CreateSheet("Monthly Attendance Report");
                sheet.IsPrintGridlines = true;//
                sheet.VerticallyCenter = true;//
                row = sheet.CreateRow(rowIndex);
                hFont = (HSSFFont)workbook.CreateFont();
                hFont.Boldweight = (short)FontBoldWeight.Bold;
                hFont.Color = HSSFColor.Maroon.Index;
                hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.SetFont(hFont);
                HSSFCellStyle hStyle2 = (HSSFCellStyle)workbook.CreateCellStyle();//
                hStyle2.SetFont(hFont);//
                HSSFCellStyle MainStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                MainStyle.SetFont(hFont);
                MainStyle.Alignment = HorizontalAlignment.Center;
                HSSFFont hFont1 = (HSSFFont)workbook.CreateFont();//
                hFont1.Boldweight = (short)FontBoldWeight.Bold;//
                hFont1.Color = HSSFColor.Black.Index;//
                hFont1.FontHeightInPoints = 10;//
                IStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                IStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                HSSFCellStyle hStyle1 = (HSSFCellStyle)workbook.CreateCellStyle();//
                hStyle1.SetFont(hFont1);//
                hStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;//
                hStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//
                hStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//
                hStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//
                if (dtLevelName.Rows.Count > 0)
                {
                    row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 21));
                    row.GetCell(0).CellStyle = MainStyle;
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                }
                row.CreateCell(0).SetCellValue("Monthly Attendance report From - " + Convert.ToString(frmDate) + " To - " + Convert.ToString(frmDate));
                row.GetCell(0).CellStyle = MainStyle;
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 21));
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue("Sl.No");
                row.CreateCell(1).SetCellValue("Level");
                row.CreateCell(2).SetCellValue("Employee Id");
                row.CreateCell(3).SetCellValue("Employee Name");
                row.CreateCell(4).SetCellValue("Designation");
                row.CreateCell(5).SetCellValue("G");
                row.CreateCell(6).SetCellValue("Date");
                row.CreateCell(7).SetCellValue("SSN");
                row.CreateCell(8).SetCellValue("In Time");
                row.CreateCell(9).SetCellValue("Out Time");
                row.CreateCell(10).SetCellValue("TBH");
                row.CreateCell(11).SetCellValue("TLH");
                row.CreateCell(12).SetCellValue("HW");
                row.CreateCell(13).SetCellValue("Late Entry Hrs");
                row.CreateCell(14).SetCellValue("Early Exit Hrs");
                row.CreateCell(15).SetCellValue("WD-OT Hours");
                row.CreateCell(16).SetCellValue("WO-OT Hours");
                row.CreateCell(17).SetCellValue("H-OT Hours");
                row.CreateCell(18).SetCellValue("SWH");
                row.CreateCell(19).SetCellValue("DWH");
                row.CreateCell(20).SetCellValue("First Session");
                row.CreateCell(21).SetCellValue("Second Session");
                //row.RowStyle = hStyle;
                for (int i = 0; i <= 21; i++)
                {
                    row.GetCell(i).CellStyle = hStyle;
                    sheet.AutoSizeColumn(i);
                }
                //row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                //row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                //row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = row.GetCell(10).CellStyle = row.GetCell(11).CellStyle =
                //row.GetCell(12).CellStyle = row.GetCell(13).CellStyle = row.GetCell(14).CellStyle = row.GetCell(15).CellStyle =
                //row.GetCell(16).CellStyle = row.GetCell(17).CellStyle = row.GetCell(18).CellStyle = row.GetCell(19).CellStyle =
                //row.GetCell(20).CellStyle = row.GetCell(21).CellStyle = hStyle;
                rowIndex++;
                try
                {
                    Int64 Totallateentry, TotalEarlyexit, TotalSWH, TotalDWH = 0;
                    string LateEntryString, SWHString, EarlyexitString, DWHString;
                    foreach (DataRow DR in dsEmp.Tables[0].Rows)
                    {
                        // row = sheet.CreateRow(rowIndex);
                        if (strPre == string.Empty || strPre != Convert.ToString(DR["EMPLOYEE_ID"]))
                        {
                            if (EmployeeDatacount > 1)
                            {
                                row = sheet.CreateRow(rowIndex);
                                row.CreateCell(9).SetCellValue("Total Hours:");
                                row.GetCell(9).CellStyle = hStyle1;
                                TS_TBH = TimeSpan.FromMinutes(VC_TBH);
                                //row.CreateCell(10).SetCellValue(TS_TBH.ToString());
                                row.CreateCell(10).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_TBH.TotalHours, TS_TBH.Minutes));
                                row.GetCell(10).CellStyle = hStyle1;
                                VC_TBH = 0.0;
                                TS_TLH = TimeSpan.FromMinutes(VC_TLH);
                                //row.CreateCell(11).SetCellValue(Convert.ToString(test1));
                                row.CreateCell(11).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_TLH.TotalHours, TS_TLH.Minutes));
                                row.GetCell(11).CellStyle = hStyle1;
                                VC_TLH = 0.0;
                                TS_HW = TimeSpan.FromMinutes(VC_HW);
                                //  row.CreateCell(12).SetCellValue(TS_HW.ToString());
                                row.CreateCell(12).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_HW.TotalHours, TS_HW.Minutes));
                                row.GetCell(12).CellStyle = hStyle1;
                                VC_HW = 0.0;
                                TS_LateEntryHrs = TimeSpan.FromMinutes(VC_LateEntryHrs);
                                row.CreateCell(13).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_LateEntryHrs.TotalHours, TS_LateEntryHrs.Minutes));
                                row.GetCell(13).CellStyle = hStyle1;
                                VC_LateEntryHrs = 0.0;
                                TS_EarlyExitHrs = TimeSpan.FromMinutes(VC_EarlyExitHrs);
                                row.CreateCell(14).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_EarlyExitHrs.TotalHours, TS_EarlyExitHrs.Minutes));
                                row.GetCell(14).CellStyle = hStyle1;
                                VC_EarlyExitHrs = 0.0;
                                TS_WDOTHours = TimeSpan.FromMinutes(VC_WDOTHours);
                                row.CreateCell(15).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_WDOTHours.TotalHours, TS_WDOTHours.Minutes));
                                row.GetCell(15).CellStyle = hStyle1;
                                VC_WDOTHours = 0.0;
                                TS_WOOTHours = TimeSpan.FromMinutes(VC_WOOTHours);
                                row.CreateCell(16).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_WOOTHours.TotalHours, TS_WOOTHours.Minutes));
                                row.GetCell(16).CellStyle = hStyle1;
                                VC_WOOTHours = 0.0;
                                TS_HOTHours = TimeSpan.FromMinutes(VC_HOTHours);
                                row.CreateCell(17).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_HOTHours.TotalHours, TS_HOTHours.Minutes));
                                row.GetCell(17).CellStyle = hStyle1;
                                VC_HOTHours = 0.0;
                                TS_SWH = TimeSpan.FromMinutes(VC_SWH);
                                row.CreateCell(18).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_SWH.TotalHours, TS_SWH.Minutes));
                                row.GetCell(18).CellStyle = hStyle1;
                                VC_SWH = 0.0;
                                TS_DWH = TimeSpan.FromMinutes(VC_DWH);
                                row.CreateCell(19).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_DWH.TotalHours, TS_DWH.Minutes));
                                row.GetCell(19).CellStyle = hStyle1;
                                VC_DWH = 0.0;
                                rowIndex++;
                            }
                            row = sheet.CreateRow(rowIndex);
                            row.CreateCell(0).SetCellValue(Convert.ToString(EmployeeDatacount));
                            EmployeeDatacount++;
                        }
                        else
                        {
                            row = sheet.CreateRow(rowIndex);
                            row.CreateCell(0).SetCellValue(string.Empty);
                        }
                        row.CreateCell(1).SetCellValue(Convert.ToString(DR["BRANCHNAME"]));
                        row.CreateCell(2).SetCellValue(Convert.ToString(DR["EMPLOYEE_ID"]));
                        row.CreateCell(3).SetCellValue(Convert.ToString(DR["EMPNAME"]));
                        row.CreateCell(4).SetCellValue(Convert.ToString(DR["DESIGNATION"]));
                        row.CreateCell(5).SetCellValue(Convert.ToString(DR["GENDER"]));
                        row.CreateCell(6).SetCellValue(Convert.ToString(DR["DATE"]));
                        row.CreateCell(7).SetCellValue(Convert.ToString(DR["SHIFT_SHORTNAME"]));
                        if (Convert.ToString(DR["INTIME"]) == String.Empty || Convert.ToString(DR["INTIME"]) == "00:00")
                            row.CreateCell(8).SetCellValue(Convert.ToString(""));
                        else
                        {
                            row.CreateCell(8).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["INTIME"]))).ToString(NewTimeFormat));
                        }
                        if (Convert.ToString(DR["OUTTIME"]) == String.Empty || Convert.ToString(DR["OUTTIME"]) == "00:00")
                            row.CreateCell(9).SetCellValue(Convert.ToString(""));
                        else
                        {
                            row.CreateCell(9).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["OUTTIME"]))).ToString(NewTimeFormat));
                        }
                        //    row.CreateCell(8).SetCellValue(Convert.ToString(DR["INTIME"]));
                        //    row.CreateCell(9).SetCellValue(Convert.ToString(DR["OUTTIME"]));
                        if (Convert.ToString(DR["BREAKHOURS"]) == String.Empty || Convert.ToString(DR["BREAKHOURS"]) == "00:00")
                            row.CreateCell(10).SetCellValue(Convert.ToString(""));
                        else
                        {
                            row.CreateCell(10).SetCellValue(Convert.ToString(DR["BREAKHOURS"]));
                        }
                        if (DR["BREAKHOURSMINS"] != DBNull.Value)
                        {
                            VC_TBH = VC_TBH + Convert.ToDouble(DR["BREAKHOURSMINS"]);
                        }
                        if (Convert.ToString(DR["LUNCHHOURS"]) == String.Empty || Convert.ToString(DR["LUNCHHOURS"]) == "00:00")
                            row.CreateCell(11).SetCellValue(Convert.ToString(""));
                        else
                        {
                            row.CreateCell(11).SetCellValue(Convert.ToString(DR["LUNCHHOURS"]));
                        }
                        if (DR["LUNCHHOURSMINS"] != DBNull.Value)
                        {
                            VC_TLH = VC_TLH + Convert.ToDouble(DR["LUNCHHOURSMINS"]);
                        }
                        if (Convert.ToString(DR["HRSWORK"]) == String.Empty || Convert.ToString(DR["HRSWORK"]) == "00:00")
                            row.CreateCell(12).SetCellValue(Convert.ToString(""));
                        else
                        {
                            row.CreateCell(12).SetCellValue(Convert.ToString(DR["HRSWORK"]));
                        }
                        if (DR["TOTALMINSWORKED"] != DBNull.Value)
                        {
                            VC_HW = VC_HW + Convert.ToDouble(DR["TOTALMINSWORKED"]);
                        }
                        Totallateentry = Convert.ToInt64(DR["LATEINMIN"]);
                        VC_LateEntryHrs = VC_LateEntryHrs + Convert.ToDouble(DR["LATEINMIN"]);
                        LateEntryString = (1 == Convert.ToString(Totallateentry / 60).Length ? "0" + Convert.ToString(Totallateentry / 60) :
                           Convert.ToString(Totallateentry / 60)) + ":" + (1 == Convert.ToString(Totallateentry % 60).Length ? "0" +
                           Convert.ToString(Totallateentry % 60) : Convert.ToString(Totallateentry % 60));
                        Totallateentry = 0;
                        if (LateEntryString == "00:00")
                            row.CreateCell(13).SetCellValue(Convert.ToString(""));
                        else
                        { row.CreateCell(13).SetCellValue(Convert.ToString(LateEntryString)); }
                        TotalEarlyexit = Convert.ToInt64(DR["EARLYOUTMIN"]);
                        VC_EarlyExitHrs = VC_EarlyExitHrs + Convert.ToDouble(DR["EARLYOUTMIN"]);
                        EarlyexitString = (1 == Convert.ToString(TotalEarlyexit / 60).Length ? "0" + Convert.ToString(TotalEarlyexit / 60) :
                           Convert.ToString(TotalEarlyexit / 60)) + ":" + (1 == Convert.ToString(TotalEarlyexit % 60).Length ? "0" +
                           Convert.ToString(TotalEarlyexit % 60) : Convert.ToString(TotalEarlyexit % 60));
                        TotalEarlyexit = 0;
                        if (EarlyexitString == "00:00")
                            row.CreateCell(14).SetCellValue(Convert.ToString(""));
                        else
                        { row.CreateCell(14).SetCellValue(Convert.ToString(EarlyexitString)); }
                        if (Convert.ToString(DR["NORMOT"]) == "00:00") { row.CreateCell(15).SetCellValue(Convert.ToString("")); }
                        else { row.CreateCell(15).SetCellValue(Convert.ToString(DR["NORMOT"])); }
                        VC_WDOTHours = VC_WDOTHours + Convert.ToDouble(DR["NORMOTMIN"]);
                        if (Convert.ToString(DR["OFFOT"]) == "00:00") { row.CreateCell(16).SetCellValue(Convert.ToString("")); }
                        else
                        { row.CreateCell(16).SetCellValue(Convert.ToString(DR["OFFOT"])); }
                        VC_WOOTHours = VC_WOOTHours + Convert.ToDouble(DR["OFFOTMIN"]);
                        if (Convert.ToString(DR["HOLOT"]) == "00:00") { row.CreateCell(17).SetCellValue(Convert.ToString("")); }
                        else
                        { row.CreateCell(17).SetCellValue(Convert.ToString(DR["HOLOT"])); }
                        VC_HOTHours = VC_HOTHours + Convert.ToDouble(DR["HOLOTMIN"]);
                        if (DR["TOTSUPWORKHRS"] != DBNull.Value)
                        {
                            VC_SWH = VC_SWH + Convert.ToDouble(DR["TOTSUPWORKHRS"]);
                            TotalSWH = Convert.ToInt64(DR["TOTSUPWORKHRS"]);
                            SWHString = (1 == Convert.ToString(TotalSWH / 60).Length ? "0" + Convert.ToString(TotalSWH / 60) :
                               Convert.ToString(TotalSWH / 60)) + ":" + (1 == Convert.ToString(TotalSWH % 60).Length ? "0" +
                               Convert.ToString(TotalSWH % 60) : Convert.ToString(TotalSWH % 60));
                            TotalSWH = 0;
                        }
                        else
                        { SWHString = "00:00"; }
                        row.CreateCell(18).SetCellValue(Convert.ToString(SWHString));
                        if (DR["TOTDEFWORKHRS"] != DBNull.Value)
                        {
                            VC_DWH = VC_DWH + Convert.ToDouble(DR["TOTDEFWORKHRS"]);
                            TotalDWH = Convert.ToInt64(DR["TOTDEFWORKHRS"]);
                            DWHString = (1 == Convert.ToString(TotalDWH / 60).Length ? "0" + Convert.ToString(TotalDWH / 60) :
                               Convert.ToString(TotalDWH / 60)) + ":" + (1 == Convert.ToString(TotalDWH % 60).Length ? "0" +
                               Convert.ToString(TotalDWH % 60) : Convert.ToString(TotalDWH % 60));
                            TotalDWH = 0;
                        }
                        else
                        { DWHString = "00:00"; }
                        row.CreateCell(19).SetCellValue(Convert.ToString(DWHString));
                        row.CreateCell(20).SetCellValue(Convert.ToString(DR["FIRSTSESSION"]));
                        row.CreateCell(21).SetCellValue(Convert.ToString(DR["SECONDSESSION"]));
                        strPre = Convert.ToString(DR["EMPLOYEE_ID"]);

                        row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = row.GetCell(10).CellStyle = row.GetCell(11).CellStyle =
                row.GetCell(12).CellStyle = row.GetCell(13).CellStyle = row.GetCell(14).CellStyle = row.GetCell(15).CellStyle =
                row.GetCell(16).CellStyle = row.GetCell(17).CellStyle = row.GetCell(18).CellStyle = row.GetCell(19).CellStyle =
                row.GetCell(20).CellStyle = row.GetCell(21).CellStyle = IStyle;

                        //for (int i = 0; i <= 21; i++)
                        //{
                        //    row.GetCell(i).CellStyle = IStyle;
                        //    sheet.AutoSizeColumn(i);
                        //}
                        rowIndex++;
                    }
                    row = sheet.CreateRow(rowIndex);
                    row.CreateCell(9).SetCellValue("Total Hours:");
                    row.GetCell(9).CellStyle = hStyle1;
                    TS_TBH = TimeSpan.FromMinutes(VC_TBH);
                    //row.CreateCell(10).SetCellValue(TS_TBH.ToString());
                    row.CreateCell(10).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_TBH.TotalHours, TS_TBH.Minutes));
                    row.GetCell(10).CellStyle = hStyle1;
                    VC_TBH = 0.0;
                    TS_TLH = TimeSpan.FromMinutes(VC_TLH);
                    //row.CreateCell(11).SetCellValue(Convert.ToString(test1));
                    row.CreateCell(11).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_TLH.TotalHours, TS_TLH.Minutes));
                    row.GetCell(11).CellStyle = hStyle1;
                    VC_TLH = 0.0;
                    TS_HW = TimeSpan.FromMinutes(VC_HW);
                    //  row.CreateCell(12).SetCellValue(TS_HW.ToString());
                    row.CreateCell(12).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_HW.TotalHours, TS_HW.Minutes));
                    row.GetCell(12).CellStyle = hStyle1;
                    VC_HW = 0.0;
                    TS_LateEntryHrs = TimeSpan.FromMinutes(VC_LateEntryHrs);
                    row.CreateCell(13).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_LateEntryHrs.TotalHours, TS_LateEntryHrs.Minutes));
                    row.GetCell(13).CellStyle = hStyle1;
                    VC_LateEntryHrs = 0.0;
                    TS_EarlyExitHrs = TimeSpan.FromMinutes(VC_EarlyExitHrs);
                    row.CreateCell(14).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_EarlyExitHrs.TotalHours, TS_EarlyExitHrs.Minutes));
                    row.GetCell(14).CellStyle = hStyle1;
                    VC_EarlyExitHrs = 0.0;
                    TS_WDOTHours = TimeSpan.FromMinutes(VC_WDOTHours);
                    row.CreateCell(15).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_WDOTHours.TotalHours, TS_WDOTHours.Minutes));
                    row.GetCell(15).CellStyle = hStyle1;
                    VC_WDOTHours = 0.0;
                    TS_WOOTHours = TimeSpan.FromMinutes(VC_WOOTHours);
                    row.CreateCell(16).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_WOOTHours.TotalHours, TS_WOOTHours.Minutes));
                    row.GetCell(16).CellStyle = hStyle1;
                    VC_WOOTHours = 0.0;
                    TS_HOTHours = TimeSpan.FromMinutes(VC_HOTHours);
                    row.CreateCell(17).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_HOTHours.TotalHours, TS_HOTHours.Minutes));
                    row.GetCell(17).CellStyle = hStyle1;
                    VC_HOTHours = 0.0;
                    TS_SWH = TimeSpan.FromMinutes(VC_SWH);
                    row.CreateCell(18).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_SWH.TotalHours, TS_SWH.Minutes));
                    row.GetCell(18).CellStyle = hStyle1;
                    VC_SWH = 0.0;
                    TS_DWH = TimeSpan.FromMinutes(VC_DWH);
                    row.CreateCell(19).SetCellValue(string.Format("{0:00}:{1:00}", (int)TS_DWH.TotalHours, TS_DWH.Minutes));
                    row.GetCell(19).CellStyle = hStyle1;
                    VC_DWH = 0.0;
                    rowIndex++;
                    sheet1 = workbook.CreateSheet("Legends");
                    sheet1.IsPrintGridlines = true;
                    sheet1.VerticallyCenter = true;
                    row1 = sheet1.CreateRow(rowIndex1);
                    row1.CreateCell(0).SetCellValue("");
                    row1.CreateCell(1).SetCellValue("G : Gender");
                    sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                    row1.GetCell(1).CellStyle = hStyle;
                    rowIndex1++;
                    row1 = sheet1.CreateRow(rowIndex1);
                    row1.CreateCell(0).SetCellValue("");
                    row1.CreateCell(1).SetCellValue("SSN : Shift Short Name");
                    sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                    row1.GetCell(1).CellStyle = hStyle;
                    rowIndex1++;
                    row1 = sheet1.CreateRow(rowIndex1);
                    row1.CreateCell(0).SetCellValue("");
                    row1.CreateCell(1).SetCellValue("TBH : Total Break Hours");
                    sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                    row1.GetCell(1).CellStyle = hStyle;
                    rowIndex1++;
                    row1 = sheet1.CreateRow(rowIndex1);
                    row1.CreateCell(0).SetCellValue("");
                    row1.CreateCell(1).SetCellValue("TLH : Total Lunch Hours");
                    sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                    row1.GetCell(1).CellStyle = hStyle;
                    rowIndex1++;
                    row1 = sheet1.CreateRow(rowIndex1);
                    row1.CreateCell(0).SetCellValue("");
                    row1.CreateCell(1).SetCellValue("HW : Hours Worked");
                    sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                    row1.GetCell(1).CellStyle = hStyle;
                    rowIndex1++;
                    row1 = sheet1.CreateRow(rowIndex1);
                    row1.CreateCell(0).SetCellValue("");
                    row1.CreateCell(1).SetCellValue("HW : (Out Time-In Time)-(TBH+TLH)");
                    sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                    row1.GetCell(1).CellStyle = hStyle;
                    rowIndex1++;
                    row1 = sheet1.CreateRow(rowIndex1);
                    row1.CreateCell(0).SetCellValue("");
                    row1.CreateCell(1).SetCellValue("WD-OT Hours : Weekday OT Hours");
                    sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                    row1.GetCell(1).CellStyle = hStyle;
                    rowIndex1++;
                    row1 = sheet1.CreateRow(rowIndex1);
                    row1.CreateCell(0).SetCellValue("");
                    row1.CreateCell(1).SetCellValue("WO-OT Hours : Weekly Off OT Hours");
                    sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                    row1.GetCell(1).CellStyle = hStyle;
                    rowIndex1++;
                    row1 = sheet1.CreateRow(rowIndex1);
                    row1.CreateCell(0).SetCellValue("");
                    row1.CreateCell(1).SetCellValue("H-OT Hours : Holiday OT Hours");
                    sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                    row1.GetCell(1).CellStyle = hStyle;
                    rowIndex1++;
                    row1 = sheet1.CreateRow(rowIndex1);
                    row1.CreateCell(0).SetCellValue("");
                    row1.CreateCell(1).SetCellValue("SWH : Supposed Work Hours");
                    sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                    row1.GetCell(1).CellStyle = hStyle;
                    rowIndex1++;
                    row1 = sheet1.CreateRow(rowIndex1);
                    row1.CreateCell(0).SetCellValue("");
                    row1.CreateCell(1).SetCellValue("DWH : Defined Work Hours");
                    sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                    row1.GetCell(1).CellStyle = hStyle;
                    rowIndex1++;
                    row1 = sheet1.CreateRow(rowIndex1);
                    row1.CreateCell(0).SetCellValue("");
                    row1.CreateCell(1).SetCellValue("# : Manual Entry");
                    sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                    row1.GetCell(1).CellStyle = hStyle;
                    rowIndex1++;
                }
                catch (Exception ex)
                {
                    Response.Write(ex);
                }
                break;
            #endregion
            case "LateEntry":
                #region LateEntry
                saveAsFileName = string.Format("LateEntry.xls");
                sheet = workbook.CreateSheet("Late Entry Report");
                row = sheet.CreateRow(rowIndex);
                hFont = (HSSFFont)workbook.CreateFont();
                hFont.Boldweight = (short)FontBoldWeight.Bold;
                hFont.Color = HSSFColor.Maroon.Index;
                hFont.FontHeightInPoints = 10;
                hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle.SetFont(hFont);
                hStyle.FillForegroundColor = IndexedColors.White.Index;
                hStyle.FillPattern = FillPattern.SolidForeground;
                hStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                MainHStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                MainHStyle.SetFont(hFont);
                MainHStyle.Alignment = HorizontalAlignment.Center;
                IStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                IStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

                if (dtLevelName.Rows.Count > 0)
                {
                    row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 11));
                    row.GetCell(0).CellStyle = MainHStyle;
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                }

                row.CreateCell(0).SetCellValue("Late Entry Report" + " From - " + Convert.ToString(frmDate) + " - " + " To - " + Convert.ToString(frmDate));
                row.GetCell(0).CellStyle = MainHStyle;
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 11));
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue("Sl.No");
                row.CreateCell(1).SetCellValue("Employee ID");
                row.CreateCell(2).SetCellValue("Employee Name");
                row.CreateCell(3).SetCellValue("Level");
                row.CreateCell(4).SetCellValue("Designation");
                row.CreateCell(5).SetCellValue("Gender");
                row.CreateCell(6).SetCellValue("Date");
                row.CreateCell(7).SetCellValue("Shift Short Name");
                row.CreateCell(8).SetCellValue("Shift Start Time");
                row.CreateCell(9).SetCellValue("Actual In Time");
                row.CreateCell(10).SetCellValue("Late by Hrs");
                row.CreateCell(11).SetCellValue("Reason");
                for (int i = 0; i <= 11; i++)
                {
                    row.GetCell(i).CellStyle = hStyle;
                    sheet.AutoSizeColumn(i);
                }
                rowIndex++;
                foreach (DataRow DR in dsEmp.Tables[0].Rows)
                {
                    if (strPre != string.Empty)
                    {
                        if (strPre != Convert.ToString(DR["CardNo"]))
                        {
                            row = sheet.CreateRow(rowIndex);
                            row.CreateCell(9).SetCellValue("Sub Total");
                            result = TimeSpan.FromMinutes(ValCount);
                            fromTimeString = result.ToString();
                            row.CreateCell(10).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes) + " ( " + CountDays + " Days )");
                            row.CreateCell(11).SetCellValue("");
                            ValCount = 0.0;
                            CountDays = 0;
                            rowIndex++;
                        }

                    }
                    row = sheet.CreateRow(rowIndex);
                    if (strPre == string.Empty || strPre != Convert.ToString(DR["CardNo"]))
                    {
                        SlNo++;
                        row.CreateCell(0).SetCellValue(SlNo);
                    }
                    else
                    {
                        row.CreateCell(0).SetCellValue("");
                    }
                    row.CreateCell(1).SetCellValue(Convert.ToString(DR["CardNo"]));
                    row.CreateCell(2).SetCellValue(Convert.ToString(DR["Employee Name"]));
                    row.CreateCell(3).SetCellValue(Convert.ToString(DR["Level Name"]));
                    row.CreateCell(4).SetCellValue(Convert.ToString(DR["Designation"]));
                    row.CreateCell(5).SetCellValue(Convert.ToString(DR["Gender"]));
                    row.CreateCell(6).SetCellValue(Convert.ToString(DR["Date"]));
                    row.CreateCell(7).SetCellValue(Convert.ToString(DR["shift_shortName"]));
                    if (Convert.ToString(DR["Shift_StartTime"]) == String.Empty)
                        row.CreateCell(8).SetCellValue(Convert.ToString(DR["Shift_StartTime"]));
                    else
                    {
                        row.CreateCell(8).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["Shift_StartTime"]))).ToString(NewTimeFormat));
                    }
                    if (Convert.ToString(DR["InTime"]) == String.Empty)
                        row.CreateCell(9).SetCellValue(Convert.ToString(DR["InTime"]));
                    else
                    {
                        row.CreateCell(9).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["InTime"]))).ToString(NewTimeFormat));
                    }
                    row.CreateCell(10).SetCellValue(Convert.ToString(DR["LateBy"]));
                    row.CreateCell(11).SetCellValue(Convert.ToString(DR["Reason"]));
                    ValCount = ValCount + Convert.ToDouble(DR["LateInMins"]);
                    CountDays++;
                    strPre = Convert.ToString(DR["CardNo"]);
                    row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                    row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                    row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = row.GetCell(10).CellStyle = row.GetCell(11).CellStyle =
                    IStyle;
                    rowIndex++;
                }
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(9).SetCellValue("Sub Total");
                row.GetCell(9).CellStyle = IStyle;
                result = TimeSpan.FromMinutes(ValCount);
                row.CreateCell(10).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes) + " ( " + CountDays + " Days )");
                row.GetCell(10).CellStyle = IStyle;
                row.CreateCell(11).SetCellValue("");
                row.GetCell(11).CellStyle = IStyle;
                ValCount = 0.0;
                CountDays = 0;
                rowIndex++;
                #endregion
                break;
            case "Leave":
            case "Leave_Angler":
                #region Leave
                sheet = workbook.CreateSheet("Leave Report Detailed");
                row = sheet.CreateRow(rowIndex);
                hFont = (HSSFFont)workbook.CreateFont();
                hFont.Boldweight = (short)FontBoldWeight.Bold;
                hFont.Color = HSSFColor.Maroon.Index;
                hFont.FontHeightInPoints = 10;
                hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle.SetFont(hFont);
                hStyle.FillForegroundColor = IndexedColors.White.Index;
                hStyle.FillPattern = FillPattern.SolidForeground;
                hStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                MainStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                MainStyle.SetFont(hFont);
                MainStyle.Alignment = HorizontalAlignment.Center;
                hFont1 = (HSSFFont)workbook.CreateFont();
                hFont1.Boldweight = (short)FontBoldWeight.Bold;
                hFont1.Color = HSSFColor.Black.Index;
                hFont1.FontHeightInPoints = 10;
                hStyle1 = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle1.SetFont(hFont1);
                hStyle2 = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle2.SetFont(hFont);
                IStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                IStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                if (dtLevelName.Rows.Count > 0)
                {
                    row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 12));
                    row.GetCell(0).CellStyle = MainStyle;
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                }
                row.CreateCell(0).SetCellValue("Leave Report From - " + Convert.ToString(frmDate) + " To - " + Convert.ToString(frmDate));
                row.GetCell(0).CellStyle = MainStyle;
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 12));
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue("Sl.No");
                row.CreateCell(1).SetCellValue("Employee Id");
                row.CreateCell(2).SetCellValue("Employee Name");
                row.CreateCell(3).SetCellValue("Designation");
                row.CreateCell(4).SetCellValue("Gender");
                row.CreateCell(5).SetCellValue("From Date");
                row.CreateCell(6).SetCellValue("To Date");
                row.CreateCell(7).SetCellValue("From Session");
                row.CreateCell(8).SetCellValue("To Session");
                row.CreateCell(9).SetCellValue("No. Of Days");
                row.CreateCell(10).SetCellValue("Leave Type");
                row.CreateCell(11).SetCellValue("Status");
                row.CreateCell(12).SetCellValue("Reason");
                //row.RowStyle = hStyle;
                for (int i = 0; i <= 12; i++)
                {
                    row.GetCell(i).CellStyle = hStyle;
                    sheet.AutoSizeColumn(i);
                }
                //row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                //row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                //row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = row.GetCell(10).CellStyle = row.GetCell(11).CellStyle =
                //row.GetCell(12).CellStyle = hStyle;
                rowIndex++;
                foreach (DataRow DR in dsEmp.Tables[0].Rows)
                {
                    row = sheet.CreateRow(rowIndex);
                    if (strPre == string.Empty || strPre != Convert.ToString(DR["Employee_Id"]))
                    {
                        if (EmployeeDatacount > 1)
                        {
                            row = sheet.CreateRow(rowIndex);
                            row.CreateCell(8).SetCellValue("Sub Total :");
                            row.GetCell(8).CellStyle = hStyle;
                            // TS_Subtotal = TimeSpan.FromMinutes(ValCount);
                            row.CreateCell(9).SetCellValue(Convert.ToString(ValCount));
                            row.GetCell(9).CellStyle = hStyle;
                            ValCount = 0.0;
                            rowIndex++;
                        }
                        row = sheet.CreateRow(rowIndex);
                        row.CreateCell(0).SetCellValue(Convert.ToString(EmployeeDatacount));
                        EmployeeDatacount++;
                    }
                    else
                    {
                        row.CreateCell(0).SetCellValue(string.Empty);
                    }
                    row.CreateCell(1).SetCellValue(Convert.ToString(DR["Employee_Id"]));
                    row.CreateCell(2).SetCellValue(Convert.ToString(DR["empname"]));
                    row.CreateCell(3).SetCellValue(Convert.ToString(DR["Titlename"]));
                    row.CreateCell(4).SetCellValue(Convert.ToString(DR["Gender"]));
                    row.CreateCell(5).SetCellValue(Convert.ToString(DR["fromDate"]));
                    row.CreateCell(6).SetCellValue(Convert.ToString(DR["toDate"]));
                    row.CreateCell(7).SetCellValue(Convert.ToString(DR["Leave_SessionFrom"]));
                    row.CreateCell(8).SetCellValue(Convert.ToString(DR["Leave_SessionTo"]));
                    row.CreateCell(9).SetCellValue(Convert.ToString(DR["display"]));
                    row.CreateCell(10).SetCellValue(Convert.ToString(DR["leavetype"]));
                    row.CreateCell(11).SetCellValue(Convert.ToString(DR["Status"]));
                    row.CreateCell(12).SetCellValue(Convert.ToString(DR["Reason"]));
                    ValCount = ValCount + Convert.ToDouble(DR["NumberOfDays"]);
                    strPre = Convert.ToString(DR["Employee_Id"]);
                    //for (int i = 0; i <= 12; i++)
                    //{
                    //    row.GetCell(i).CellStyle = IStyle;
                    //    sheet.AutoSizeColumn(i);
                    //}
                    row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                    row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                    row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = row.GetCell(10).CellStyle = row.GetCell(11).CellStyle =
                    row.GetCell(12).CellStyle = IStyle;
                    rowIndex++;
                }
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(8).SetCellValue("Sub Total :");
                row.GetCell(8).CellStyle = IStyle;
                // TS_Subtotal = TimeSpan.FromMinutes(ValCount);
                row.CreateCell(9).SetCellValue(Convert.ToString(ValCount));
                row.GetCell(9).CellStyle = IStyle;
                ValCount = 0.0;
                sheet1 = workbook.CreateSheet("Legends");
                sheet1.IsPrintGridlines = true;
                sheet1.VerticallyCenter = true;
                row1 = sheet1.CreateRow(rowIndex1);
                row1.CreateCell(0).SetCellValue("");
                row1.CreateCell(1).SetCellValue("B- Before Weekly off \\ Holiday");
                sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                row1.GetCell(1).CellStyle = IStyle;
                rowIndex1++;
                row1 = sheet1.CreateRow(rowIndex1);
                row1.CreateCell(0).SetCellValue("");
                row1.CreateCell(1).SetCellValue("A- After Weekly off \\ Holiday");
                sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                row1.GetCell(1).CellStyle = IStyle;
                rowIndex1++;
                row1 = sheet1.CreateRow(rowIndex1);
                row1.CreateCell(0).SetCellValue("");
                row1.CreateCell(1).SetCellValue("I-Intervening Weekly off \\ Holiday");
                sheet1.AddMergedRegion(new CellRangeAddress(rowIndex1, rowIndex1, 1, 5));
                row1.GetCell(1).CellStyle = IStyle;
                rowIndex1++;
                saveAsFileName = string.Format("Leave.xls");
                break;
            #endregion
            case "Punctuality":
                #region Punctuality
                sheet = workbook.CreateSheet("Punctuality report");
                sheet.IsPrintGridlines = true;
                sheet.VerticallyCenter = true;
                row = sheet.CreateRow(rowIndex);
                hFont = (HSSFFont)workbook.CreateFont();
                hFont.Boldweight = (short)FontBoldWeight.Bold;
                hFont.Color = HSSFColor.Maroon.Index;
                hFont.FontHeightInPoints = 10;
                hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle.SetFont(hFont);
                hStyle.FillForegroundColor = IndexedColors.White.Index;
                hStyle.FillPattern = FillPattern.SolidForeground;
                hStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                MainHStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                MainHStyle.SetFont(hFont);
                MainHStyle.Alignment = HorizontalAlignment.Center;
                hStyle2 = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle2.SetFont(hFont);
                IStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                IStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                if (dtLevelName.Rows.Count > 0)
                {
                    row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 8));
                    row.GetCell(0).CellStyle = MainHStyle;
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                }
                row.CreateCell(0).SetCellValue("Punctuality report From - " + Convert.ToString(frmDate) + " - " + "To - " + Convert.ToString(frmDate));
                row.GetCell(0).CellStyle = MainHStyle;
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 8));
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue("Sl.No");
                row.CreateCell(1).SetCellValue("Employee Id");
                row.CreateCell(2).SetCellValue("Employee Name");
                row.CreateCell(3).SetCellValue("Level Name");
                row.CreateCell(4).SetCellValue("Gender");
                row.CreateCell(5).SetCellValue("Shift Shortname");
                row.CreateCell(6).SetCellValue("Date");
                row.CreateCell(7).SetCellValue("Actual InTime");
                row.CreateCell(8).SetCellValue("Allocated Shift InTime");
                //row.RowStyle = hStyle;
                for (int i = 0; i <= 8; i++)
                {
                    sheet.AutoSizeColumn(i);
                    row.GetCell(i).CellStyle = hStyle;
                }
                //row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                //row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                //row.GetCell(8).CellStyle = hStyle;
                rowIndex++;
                foreach (DataRow DR in dsEmp.Tables[0].Rows)
                {
                    row = sheet.CreateRow(rowIndex);
                    row.Height = 300;
                    if (strPre == string.Empty || strPre != Convert.ToString(DR["Employee_Code_Id"]))
                    {
                        SlNo++;
                        row.CreateCell(0).SetCellValue(SlNo);
                    }
                    else
                    {
                        row.CreateCell(0).SetCellValue("");
                    }
                    row.CreateCell(1).SetCellValue(Convert.ToString(DR["Employee_Code_Id"]));
                    row.CreateCell(2).SetCellValue(Convert.ToString(DR["Employee_FirstName"]));
                    row.CreateCell(3).SetCellValue(Convert.ToString(DR["Level Name"]));
                    row.CreateCell(4).SetCellValue(Convert.ToString(DR["Gender"]));
                    row.CreateCell(5).SetCellValue(Convert.ToString(DR["Shift_Shortname"]));
                    row.CreateCell(6).SetCellValue(Convert.ToString(DR["Date"]));
                    if (Convert.ToString(DR["InTime"]) == String.Empty)
                        row.CreateCell(7).SetCellValue(Convert.ToString(DR["InTime"]));
                    else
                    {
                        row.CreateCell(7).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["InTime"]))).ToString(NewTimeFormat));
                    }
                    //row.CreateCell(7).SetCellValue("Actual InTime");
                    if (Convert.ToString(DR["ShiftIn"]) == String.Empty)
                        row.CreateCell(8).SetCellValue(Convert.ToString(DR["ShiftIn"]));
                    else
                    {
                        row.CreateCell(8).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["ShiftIn"]))).ToString(NewTimeFormat));
                    }
                    strPre = Convert.ToString(DR["Employee_Code_Id"]);
                    //for (int i = 0; i <= 8; i++)
                    //{
                    //    row.GetCell(i).CellStyle = IStyle;
                    //    sheet.AutoSizeColumn(i);
                    //}
                    row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
             row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
             row.GetCell(8).CellStyle = IStyle;
                    rowIndex++;
                }
                saveAsFileName = string.Format("Punctuality.xls");
                #endregion
                break;
            case "EarlyExit":
                #region EarlyExit
                sheet = workbook.CreateSheet("Early Exit Report");
                row = sheet.CreateRow(rowIndex);
                hFont = (HSSFFont)workbook.CreateFont();
                hFont.Boldweight = (short)FontBoldWeight.Bold;
                hFont.Color = HSSFColor.Maroon.Index;
                hFont.FontHeightInPoints = 10;
                hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle.SetFont(hFont);
                hStyle.FillForegroundColor = IndexedColors.White.Index;
                hStyle.FillPattern = FillPattern.SolidForeground;
                hStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                MainHStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                MainHStyle.SetFont(hFont);
                MainHStyle.Alignment = HorizontalAlignment.Center;
                IStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                IStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                if (dtLevelName.Rows.Count > 0)
                {
                    row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 11));
                    row.GetCell(0).CellStyle = MainHStyle;
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                }
                row.CreateCell(0).SetCellValue("Early Exit Report" + " From - " + Convert.ToString(frmDate) + " - " + " To - " + Convert.ToString(frmDate));
                row.GetCell(0).CellStyle = MainHStyle;
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 11));
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue("Sl.No");
                row.CreateCell(1).SetCellValue("Employee ID");
                row.CreateCell(2).SetCellValue("Employee Name");
                row.CreateCell(3).SetCellValue("Level");
                row.CreateCell(4).SetCellValue("Designation");
                row.CreateCell(5).SetCellValue("Gender");
                row.CreateCell(6).SetCellValue("Date");
                row.CreateCell(7).SetCellValue("Shift Short Name");
                row.CreateCell(8).SetCellValue("Shift Out Time");
                row.CreateCell(9).SetCellValue("Actual Shift Out Time");
                row.CreateCell(10).SetCellValue("Early by");
                row.CreateCell(11).SetCellValue("Reason");
                //row.RowStyle = hStyle;
                for (int i = 0; i <= 11; i++)
                {
                    row.GetCell(i).CellStyle = hStyle;
                    sheet.AutoSizeColumn(i);
                }
                rowIndex++;
                foreach (DataRow DR in dsEmp.Tables[0].Rows)
                {
                    if (strPre != string.Empty)
                    {
                        if (strPre != Convert.ToString(DR["Employee_Code_Id"]))
                        {
                            row = sheet.CreateRow(rowIndex);
                            row.CreateCell(9).SetCellValue("Sub Total");
                            result = TimeSpan.FromMinutes(ValCount);
                            row.CreateCell(10).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes) + " ( " + CountDays + " Days )");
                            row.CreateCell(11).SetCellValue("");
                            ValCount = 0.0;
                            CountDays = 0;
                            rowIndex++;
                        }

                    }
                    row = sheet.CreateRow(rowIndex);
                    //row.CreateCell(0).SetCellValue(Convert.ToString(DR["Sl.No"]));
                    if (strPre == string.Empty || strPre != Convert.ToString(DR["Employee_Code_Id"]))
                    {
                        SlNo++;
                        row.CreateCell(0).SetCellValue(SlNo);
                    }
                    else
                    {
                        row.CreateCell(0).SetCellValue("");
                    }
                    row.CreateCell(1).SetCellValue(Convert.ToString(DR["Employee_Code_Id"]));
                    row.CreateCell(2).SetCellValue(Convert.ToString(DR["Employee_FirstName"]));
                    row.CreateCell(3).SetCellValue(Convert.ToString(DR["LevelName"]));
                    row.CreateCell(4).SetCellValue(Convert.ToString(DR["TitleName"]));
                    row.CreateCell(5).SetCellValue(Convert.ToString(DR["Gender"]));
                    row.CreateCell(6).SetCellValue(Convert.ToString(DR["Date"]));
                    row.CreateCell(7).SetCellValue(Convert.ToString(DR["shift_shortName"]));
                    if (Convert.ToString(DR["shiftOut"]) == String.Empty)
                        row.CreateCell(8).SetCellValue(Convert.ToString(DR["shiftOut"]));
                    else
                    {
                        row.CreateCell(8).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["shiftOut"]))).ToString(NewTimeFormat));
                    }
                    if (Convert.ToString(DR["OutTime"]) == String.Empty)
                        row.CreateCell(9).SetCellValue(Convert.ToString(DR["OutTime"]));
                    else
                    {
                        row.CreateCell(9).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["OutTime"]))).ToString(NewTimeFormat));
                    }
                    row.CreateCell(10).SetCellValue(Convert.ToString(DR["EarlyOut"]));
                    row.CreateCell(11).SetCellValue(Convert.ToString(DR["Reason"]));
                    ValCount = ValCount + Convert.ToDouble(DR["EarlyOutMins"]);
                    CountDays++;
                    strPre = Convert.ToString(DR["Employee_Code_Id"]);
                    row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                   row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                   row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = row.GetCell(10).CellStyle = row.GetCell(11).CellStyle =
                   IStyle;
                    rowIndex++;
                }
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(9).SetCellValue("Sub Total");
                row.GetCell(9).CellStyle = IStyle;
                result = TimeSpan.FromMinutes(ValCount);
                fromTimeString = result.ToString();
                row.CreateCell(10).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes) + " ( " + CountDays + " Days )");
                row.GetCell(10).CellStyle = IStyle;
                row.CreateCell(11).SetCellValue("");
                row.GetCell(11).CellStyle = IStyle;
                ValCount = 0.0;
                CountDays = 0;
                rowIndex++;
                saveAsFileName = string.Format("EarlyExit.xls");
                #endregion
                break;
            case "OnDutyEntry":
            case "OnDuty":
                #region Onduty
                sheet = workbook.CreateSheet("OnDuty Entry Report");
                row = sheet.CreateRow(rowIndex);
                hFont = (HSSFFont)workbook.CreateFont();
                hFont.Boldweight = (short)FontBoldWeight.Bold;
                hFont.Color = HSSFColor.Maroon.Index;
                hFont.FontHeightInPoints = 10;
                hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle.SetFont(hFont);
                hStyle.FillForegroundColor = IndexedColors.White.Index;
                hStyle.FillPattern = FillPattern.SolidForeground;
                hStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                MainHStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                MainHStyle.SetFont(hFont);
                MainHStyle.Alignment = HorizontalAlignment.Center;
                IStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                IStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                if (dtLevelName.Rows.Count > 0)
                {
                    row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 10));
                    row.GetCell(0).CellStyle = MainHStyle;
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                }
                row.CreateCell(0).SetCellValue("OnDuty Entry Report From - " + Convert.ToString(frmDate) + " To - " + Convert.ToString(frmDate));
                row.GetCell(0).CellStyle = MainHStyle;
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 10));
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue("Sl.No");
                row.CreateCell(1).SetCellValue("Employee Id");
                row.CreateCell(2).SetCellValue("Employee Name");
                row.CreateCell(3).SetCellValue("Level");
                row.CreateCell(4).SetCellValue("Gender");
                row.CreateCell(5).SetCellValue("From Date");
                row.CreateCell(6).SetCellValue("From Time");
                row.CreateCell(7).SetCellValue("To Date");
                row.CreateCell(8).SetCellValue("To Time");
                row.CreateCell(9).SetCellValue("Reason");
                row.CreateCell(10).SetCellValue("Status");
                for (int i = 0; i <= 10; i++)
                {
                    row.GetCell(i).CellStyle = hStyle;
                    sheet.AutoSizeColumn(i);
                }
                //row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                //row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                //row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = row.GetCell(10).CellStyle = hStyle;
                rowIndex++;
                foreach (DataRow DR in dsEmp.Tables[0].Rows)
                {
                    row = sheet.CreateRow(rowIndex);

                    if (strPre == string.Empty || strPre != Convert.ToString(DR["Onduty_Employee_Id"]))
                    {
                        row.CreateCell(0).SetCellValue(Convert.ToString(EmployeeDatacount));
                        EmployeeDatacount++;
                    }
                    else
                    {
                        row.CreateCell(0).SetCellValue(string.Empty);
                    }
                    row.CreateCell(1).SetCellValue(Convert.ToString(DR["Onduty_Employee_Id"]));
                    row.CreateCell(2).SetCellValue(Convert.ToString(DR["Employee_Name"]));
                    row.CreateCell(3).SetCellValue(Convert.ToString(DR["name"]));
                    row.CreateCell(4).SetCellValue(Convert.ToString(DR["Gender"]));
                    row.CreateCell(5).SetCellValue(Convert.ToString(DR["Onduty_FromDate"]));
                    if (Convert.ToString(DR["Onduty_FromTime"]) == String.Empty)
                        row.CreateCell(6).SetCellValue(Convert.ToString(DR["Onduty_FromTime"]));
                    else
                    {
                        row.CreateCell(6).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["Onduty_FromTime"]))).ToString(NewTimeFormat));
                    }
                    row.CreateCell(7).SetCellValue(Convert.ToString(DR["Onduty_ToDate"]));
                    if (Convert.ToString(DR["Onduty_ToTime"]) == String.Empty)
                        row.CreateCell(8).SetCellValue(Convert.ToString(DR["Onduty_ToTime"]));
                    else
                    {
                        row.CreateCell(8).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["Onduty_ToTime"]))).ToString(NewTimeFormat));
                    }
                    row.CreateCell(9).SetCellValue(Convert.ToString(DR["Onduty_Reason"]));
                    row.CreateCell(10).SetCellValue(Convert.ToString(DR["Onduty_Status"]));
                    strPre = Convert.ToString(DR["Onduty_Employee_Id"]);
                    //for (int i = 0; i <= 10; i++)
                    //{
                    //    row.GetCell(i).CellStyle = IStyle;
                    //    sheet.AutoSizeColumn(i);
                    //}
                    row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                    row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                    row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = row.GetCell(10).CellStyle = IStyle;
                    rowIndex++;
                }
                saveAsFileName = string.Format("OnDutyEntry.xls");
                break;
            #endregion
            case "LessHoursWorked":
                #region LessHours
                string SheetName = "";
                SheetName = "Less Hours Worked Detailed";
                sheet = workbook.CreateSheet(SheetName);
                row = sheet.CreateRow(rowIndex);
                hFont = (HSSFFont)workbook.CreateFont();
                hFont.Boldweight = (short)FontBoldWeight.Bold;
                hFont.Color = HSSFColor.Maroon.Index;
                hFont.FontHeightInPoints = 10;
                hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle.SetFont(hFont);
                hStyle.FillForegroundColor = IndexedColors.White.Index;
                hStyle.FillPattern = FillPattern.SolidForeground;
                hStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                MainHStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                MainHStyle.SetFont(hFont);
                MainHStyle.Alignment = HorizontalAlignment.Center;
                hStyle2 = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle2.SetFont(hFont);
                IStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                IStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

                if (dtLevelName.Rows.Count > 0)
                {
                    row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 11));
                    row.GetCell(0).CellStyle = MainHStyle;
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                }
                row.CreateCell(0).SetCellValue("Less Hours Worked Report From - " + Convert.ToString(frmDate) + " To - " + Convert.ToString(frmDate));
                row.GetCell(0).CellStyle = MainHStyle;
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 11));
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue("Sl.No");
                row.CreateCell(1).SetCellValue("Employee Id");
                row.CreateCell(2).SetCellValue("Employee Name");
                row.CreateCell(3).SetCellValue("Level");
                row.CreateCell(4).SetCellValue("Designation");
                row.CreateCell(5).SetCellValue("Gender");
                row.CreateCell(6).SetCellValue("Date");
                row.CreateCell(7).SetCellValue("InTime");
                row.CreateCell(8).SetCellValue("OutTime");
                row.CreateCell(9).SetCellValue("Hours Worked");
                row.CreateCell(10).SetCellValue("Defined Work Hours");
                row.CreateCell(11).SetCellValue("Total LessHours");

                //row.RowStyle = hStyle;
                for (int i = 0; i <= 11; i++)
                {
                    row.GetCell(i).CellStyle = hStyle;
                    sheet.AutoSizeColumn(i);
                }
                //row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                //row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                //row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = row.GetCell(10).CellStyle = row.GetCell(11).CellStyle = hStyle;
                rowIndex++;
                foreach (DataRow DR in dsEmp.Tables[0].Rows)
                {
                    row = sheet.CreateRow(rowIndex);
                    if (strPre == string.Empty || strPre != Convert.ToString(DR["Employee_Id"]))
                    {
                        row.CreateCell(0).SetCellValue(Convert.ToString(EmployeeDatacount));
                        EmployeeDatacount++;
                    }
                    else
                    {
                        row.CreateCell(0).SetCellValue(string.Empty);
                    }
                    row.CreateCell(1).SetCellValue(Convert.ToString(DR["Employee_Id"]));
                    row.CreateCell(2).SetCellValue(Convert.ToString(DR["empName"]));
                    row.CreateCell(3).SetCellValue(Convert.ToString(DR["BranchName"]));
                    row.CreateCell(4).SetCellValue(Convert.ToString(DR["Designation"]));
                    row.CreateCell(5).SetCellValue(Convert.ToString(DR["Gender"]));
                    row.CreateCell(6).SetCellValue(Convert.ToString(DR["Date"]));
                    if (Convert.ToString(DR["InTime"]) == String.Empty)
                        row.CreateCell(7).SetCellValue(Convert.ToString(DR["InTime"]));
                    else
                    {
                        row.CreateCell(7).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["InTime"]))).ToString(NewTimeFormat));
                    }
                    if (Convert.ToString(DR["OutTime"]) == String.Empty)
                        row.CreateCell(8).SetCellValue(Convert.ToString(DR["OutTime"]));
                    else
                    {
                        row.CreateCell(8).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["OutTime"]))).ToString(NewTimeFormat));
                    }
                    row.CreateCell(9).SetCellValue(Convert.ToString(DR["HrsWork"]));
                    row.CreateCell(10).SetCellValue(Convert.ToString(DR["shift_TotalWorkingHours"]));
                    row.CreateCell(11).SetCellValue(Convert.ToString(DR["TotalLessHrs"]));

                    strPre = Convert.ToString(DR["Employee_id"]);
                    //for (int i = 0; i <= 11; i++)
                    //{
                    //    row.GetCell(i).CellStyle = IStyle;
                    //    sheet.AutoSizeColumn(i);
                    //}
                    row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                    row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                    row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = row.GetCell(10).CellStyle = row.GetCell(11).CellStyle = IStyle;

                    rowIndex++;
                }
                saveAsFileName = string.Format("LessHoursWorked.xls");
                #endregion
                break;
            case "OverTime":
                #region Overtime
                double HrsOT = 0, OffOT = 0, HolOT = 0, ActHrsOT = 0, ActOffOT = 0, ActHolOT = 0, ApprovedOT = 0, RejectOT = 0;
                hFont = (HSSFFont)workbook.CreateFont();
                hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                MainHStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                IStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                sheet = workbook.CreateSheet("Over Time report");
                row = sheet.CreateRow(rowIndex);
                hFont.Boldweight = (short)FontBoldWeight.Bold;
                hFont.Color = HSSFColor.Maroon.Index;
                hFont.FontHeightInPoints = 10;
                hStyle.SetFont(hFont);
                hStyle.FillForegroundColor = IndexedColors.White.Index;
                hStyle.FillPattern = FillPattern.SolidForeground;
                hStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                MainHStyle.SetFont(hFont);
                MainHStyle.Alignment = HorizontalAlignment.Center;
                IStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                if (dtLevelName.Rows.Count > 0)
                {
                    row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 13));
                    row.GetCell(0).CellStyle = MainHStyle;
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                }
                row.CreateCell(0).SetCellValue("Over Time report" + " From - " + Convert.ToString(frmDate) + " - " + "To - " + Convert.ToString(frmDate));
                row.GetCell(0).CellStyle = MainHStyle;
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 13));
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                rowIndex++;
                row = sheet.CreateRow(rowIndex);

                row.CreateCell(0).SetCellValue("Sl.No");
                row.CreateCell(1).SetCellValue("Employee ID");
                row.CreateCell(2).SetCellValue("Employee Name");
                row.CreateCell(3).SetCellValue("Gender");
                row.CreateCell(4).SetCellValue("Shift");
                row.CreateCell(5).SetCellValue("Date");
                row.CreateCell(6).SetCellValue("Worked Hours");
                row.CreateCell(7).SetCellValue("Actual Week day OT");
                row.CreateCell(8).SetCellValue("Modified Week Day OT");
                row.CreateCell(9).SetCellValue("Actual Week Off OT");
                row.CreateCell(10).SetCellValue("Modified Week Off Day OT");
                row.CreateCell(11).SetCellValue("Actual Holiday OT");
                row.CreateCell(12).SetCellValue("Modified Holiday OT");
                row.CreateCell(13).SetCellValue("Status");
                //row.RowStyle = hStyle;
                for (int i = 0; i <= 13; i++)
                {
                    row.GetCell(i).CellStyle = hStyle;
                    sheet.AutoSizeColumn(i);
                }
                //row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                //row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                //row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = row.GetCell(10).CellStyle = row.GetCell(11).CellStyle =
                //row.GetCell(12).CellStyle = row.GetCell(13).CellStyle = hStyle;

                rowIndex++;
                foreach (DataRow DR in dsEmp.Tables[0].Rows)
                {
                    if (strPre != string.Empty)
                    {
                        if (strPre != Convert.ToString(DR["Employee_Code_Id"]))
                        {
                            row = sheet.CreateRow(rowIndex);
                            row.CreateCell(6).SetCellValue("Sub Total");
                            row.GetCell(6).CellStyle = hStyle;
                            result = TimeSpan.FromMinutes(ActHrsOT);
                            row.CreateCell(7).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                            row.GetCell(7).CellStyle = hStyle;
                            result = TimeSpan.FromMinutes(HrsOT);
                            row.CreateCell(8).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                            row.GetCell(8).CellStyle = hStyle;
                            result = TimeSpan.FromMinutes(ActOffOT);
                            row.CreateCell(9).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                            row.GetCell(9).CellStyle = hStyle;
                            result = TimeSpan.FromMinutes(OffOT);
                            row.CreateCell(10).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                            row.GetCell(10).CellStyle = hStyle;
                            result = TimeSpan.FromMinutes(ActHolOT);
                            row.CreateCell(11).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                            row.GetCell(11).CellStyle = hStyle;
                            result = TimeSpan.FromMinutes(HolOT);
                            row.CreateCell(12).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                            row.GetCell(12).CellStyle = hStyle;
                            row.CreateCell(13).SetCellValue("");
                            row.GetCell(13).CellStyle = hStyle;
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            row.CreateCell(6).SetCellValue("Total OT Hours - Worked : ");
                            row.GetCell(6).CellStyle = hStyle;
                            sheet.AutoSizeColumn(6);
                            result = TimeSpan.FromMinutes(ActHrsOT + ActOffOT + ActHolOT);
                            row.CreateCell(7).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                            row.GetCell(7).CellStyle = hStyle;
                            row.CreateCell(8).SetCellValue("Total Approved OT Hours : ");
                            row.GetCell(8).CellStyle = hStyle;
                            sheet.AutoSizeColumn(8);
                            result = TimeSpan.FromMinutes(ApprovedOT);
                            row.CreateCell(9).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                            row.GetCell(9).CellStyle = hStyle;
                            row.CreateCell(10).SetCellValue("Total OT Hrs - Deducted : ");
                            row.GetCell(10).CellStyle = hStyle;
                            sheet.AutoSizeColumn(10);
                            result = TimeSpan.FromMinutes(RejectOT);
                            row.CreateCell(11).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                            row.GetCell(11).CellStyle = hStyle;
                            row.CreateCell(12).SetCellValue("");
                            row.GetCell(12).CellStyle = hStyle;
                            row.CreateCell(13).SetCellValue("");
                            row.GetCell(13).CellStyle = hStyle;
                            HrsOT = 0.0;
                            OffOT = 0.0;
                            HolOT = 0.0;
                            ActHrsOT = 0.0;
                            ActOffOT = 0.0;
                            ActHolOT = 0.0;
                            ApprovedOT = 0.0;
                            RejectOT = 0.0;
                        }
                    }
                    row = sheet.CreateRow(rowIndex);
                    if (strPre == string.Empty || strPre != Convert.ToString(DR["Employee_Code_Id"]))
                    {
                        SlNo++;
                        row.CreateCell(0).SetCellValue(SlNo);
                    }
                    else
                    {
                        row.CreateCell(0).SetCellValue("");
                    }
                    row.CreateCell(1).SetCellValue(Convert.ToString(DR["Employee_Code_Id"]));
                    row.CreateCell(2).SetCellValue(Convert.ToString(DR["Employee_FirstName"]));
                    row.CreateCell(3).SetCellValue(Convert.ToString(DR["Gender"]));
                    row.CreateCell(4).SetCellValue(Convert.ToString(DR["shiftName"]));
                    row.CreateCell(5).SetCellValue(Convert.ToString(DR["Date"]));
                    row.CreateCell(6).SetCellValue(Convert.ToString(DR["HrsWork"]));
                    row.CreateCell(7).SetCellValue(Convert.ToString(DR["ActHrsOT"]));
                    row.CreateCell(8).SetCellValue(Convert.ToString(DR["HrsOT"]));
                    row.CreateCell(9).SetCellValue(Convert.ToString(DR["ActOffOT"]));
                    row.CreateCell(10).SetCellValue(Convert.ToString(DR["OffOT"]));
                    row.CreateCell(11).SetCellValue(Convert.ToString(DR["ActHolOT"]));
                    row.CreateCell(12).SetCellValue(Convert.ToString(DR["HolOT"]));
                    row.CreateCell(13).SetCellValue(Convert.ToString(DR["OverTimeStatus"]));
                    strPre = Convert.ToString(DR["Employee_Code_Id"]);
                    HrsOT = HrsOT + Convert.ToDouble(DR["NormOTMins"]);
                    OffOT = OffOT + Convert.ToDouble(DR["WeekOffOTMins"]);
                    HolOT = HolOT + Convert.ToDouble(DR["HolidayOTMins"]);
                    ActHrsOT = ActHrsOT + Convert.ToDouble(DR["ActNormOTMins"]);
                    ActOffOT = ActOffOT + Convert.ToDouble(DR["ActWeekOffOTMins"]);
                    ActHolOT = ActHolOT + Convert.ToDouble(DR["ActHolidayOTMins"]);
                    if (Convert.ToString(DR["OverTimeStatus"]) == "Approved")
                    {
                        if (Convert.ToDouble(DR["NormOTMins"]) != 0)
                            ApprovedOT = ApprovedOT + Convert.ToDouble(DR["NormOTMins"]);
                        else if (Convert.ToDouble(DR["WeekOffOTMins"]) != 0)
                            ApprovedOT = ApprovedOT + Convert.ToDouble(DR["WeekOffOTMins"]);
                        else
                            ApprovedOT = ApprovedOT + Convert.ToDouble(DR["HolidayOTMins"]);
                    }
                    RejectOT = RejectOT + Convert.ToDouble(DR["RejectOTHours"]);
                    //Style Added the created Headers.
                    //for (int i = 0; i <= 13; i++)
                    //{
                    //    row.GetCell(i).CellStyle = IStyle;
                    //    sheet.AutoSizeColumn(i);
                    //}
                    row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
               row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
               row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = row.GetCell(10).CellStyle = row.GetCell(11).CellStyle =
               row.GetCell(12).CellStyle = row.GetCell(13).CellStyle = IStyle;
                    rowIndex++;
                }
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(6).SetCellValue("Sub Total");
                row.GetCell(6).CellStyle = IStyle;
                result = TimeSpan.FromMinutes(ActHrsOT);
                row.CreateCell(7).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                row.GetCell(7).CellStyle = IStyle;
                result = TimeSpan.FromMinutes(HrsOT);
                row.CreateCell(8).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                row.GetCell(8).CellStyle = IStyle;
                result = TimeSpan.FromMinutes(ActOffOT);
                row.CreateCell(9).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                row.GetCell(9).CellStyle = IStyle;
                result = TimeSpan.FromMinutes(OffOT);
                row.CreateCell(10).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                row.GetCell(10).CellStyle = IStyle;
                result = TimeSpan.FromMinutes(ActHolOT);
                row.CreateCell(11).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                row.GetCell(11).CellStyle = IStyle;
                result = TimeSpan.FromMinutes(HolOT);
                row.CreateCell(12).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                row.GetCell(12).CellStyle = IStyle;
                row.CreateCell(13).SetCellValue("");
                row.GetCell(13).CellStyle = IStyle;
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(6).SetCellValue("Total OT Hours - Worked : ");
                row.GetCell(6).CellStyle = IStyle;
                sheet.AutoSizeColumn(6);
                result = TimeSpan.FromMinutes(ActHrsOT + ActOffOT + ActHolOT);
                row.CreateCell(7).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                row.GetCell(7).CellStyle = IStyle;
                row.CreateCell(8).SetCellValue("Total Approved OT Hours : ");
                row.GetCell(8).CellStyle = IStyle;
                sheet.AutoSizeColumn(8);
                result = TimeSpan.FromMinutes(ApprovedOT);
                row.CreateCell(9).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                row.GetCell(9).CellStyle = IStyle;
                row.CreateCell(10).SetCellValue("Total OT Hrs - Deducted : ");
                row.GetCell(10).CellStyle = IStyle;
                sheet.AutoSizeColumn(10);
                result = TimeSpan.FromMinutes(RejectOT);
                row.CreateCell(11).SetCellValue(string.Format("{0:00}:{1:00}", (int)result.TotalHours, result.Minutes));
                row.GetCell(11).CellStyle = IStyle;
                row.CreateCell(12).SetCellValue("");
                row.GetCell(12).CellStyle = IStyle;
                row.CreateCell(13).SetCellValue("");
                row.GetCell(13).CellStyle = IStyle;
                HrsOT = 0.0;
                OffOT = 0.0;
                HolOT = 0.0;
                ActHrsOT = 0.0;
                ActOffOT = 0.0;
                ActHolOT = 0.0;
                ApprovedOT = 0.0;
                RejectOT = 0.0;
                saveAsFileName = string.Format("OverTime.xls");
                break;
            #endregion
            case "Discrepancy":
                #region Discrepancy
                sheet = workbook.CreateSheet("Discrepancy report");
                row = sheet.CreateRow(rowIndex);
                hFont = (HSSFFont)workbook.CreateFont();
                hFont.Boldweight = (short)FontBoldWeight.Bold;
                hFont.Color = HSSFColor.Maroon.Index;
                hFont.FontHeightInPoints = 10;
                hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle.SetFont(hFont);
                hStyle.FillForegroundColor = IndexedColors.White.Index;
                hStyle.FillPattern = FillPattern.SolidForeground;
                hStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                MainHStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                MainHStyle.SetFont(hFont);
                MainHStyle.Alignment = HorizontalAlignment.Center;
                IStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                IStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                if (dtLevelName.Rows.Count > 0)
                {
                    row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 10));
                    row.GetCell(0).CellStyle = MainHStyle;
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                }
                row.CreateCell(0).SetCellValue("Discrepancy report From - " + Convert.ToString(frmDate) + " - " + "To - " + Convert.ToString(frmDate));
                row.GetCell(0).CellStyle = MainHStyle;
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 10));
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue("Sl.No");
                row.CreateCell(1).SetCellValue("Employee ID");
                row.CreateCell(2).SetCellValue("Employee Name");
                row.CreateCell(3).SetCellValue("Level");
                row.CreateCell(4).SetCellValue("Designation");
                row.CreateCell(5).SetCellValue("Gender");
                row.CreateCell(6).SetCellValue("Shift Date");
                row.CreateCell(7).SetCellValue("In Date");
                row.CreateCell(8).SetCellValue("In Time");
                row.CreateCell(9).SetCellValue("Out Date");
                row.CreateCell(10).SetCellValue("Out Time");
                //row.RowStyle = hStyle;
                for (int i = 0; i <= 10; i++)
                {
                    row.GetCell(i).CellStyle = hStyle;
                    sheet.AutoSizeColumn(i);
                }
                //row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                //row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                //row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = row.GetCell(10).CellStyle = hStyle;
                rowIndex++;
                foreach (DataRow DR in dsEmp.Tables[0].Rows)
                {
                    row = sheet.CreateRow(rowIndex);
                    if (strPre == string.Empty || strPre != Convert.ToString(DR["clk_EmpId"]))
                    {
                        SlNo++;
                        row.CreateCell(0).SetCellValue(SlNo);
                    }
                    else
                    {
                        row.CreateCell(0).SetCellValue("");
                    }
                    row.CreateCell(1).SetCellValue(Convert.ToString(DR["clk_EmpId"]));
                    row.CreateCell(2).SetCellValue(Convert.ToString(DR["empName"]));
                    row.CreateCell(3).SetCellValue(Convert.ToString(DR["Level Name"]));
                    row.CreateCell(4).SetCellValue(Convert.ToString(DR["Designation"]));
                    row.CreateCell(5).SetCellValue(Convert.ToString(DR["Gender"]));
                    row.CreateCell(6).SetCellValue(Convert.ToString(DR["ClockDate"]));
                    row.CreateCell(7).SetCellValue(Convert.ToString(DR["inDate"]));
                    if (Convert.ToString(DR["clk_InTime"]) == String.Empty)
                        row.CreateCell(8).SetCellValue(Convert.ToString(DR["clk_InTime"]));
                    else
                    {

                        row.CreateCell(8).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["clk_InTime"]))).ToString(NewTimeFormat));
                    }
                    row.CreateCell(9).SetCellValue(Convert.ToString(DR["outDate"]));
                    if (Convert.ToString(DR["clk_OutTime"]) == String.Empty)
                        row.CreateCell(10).SetCellValue(Convert.ToString(DR["clk_OutTime"]));
                    else
                    {

                        row.CreateCell(10).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["clk_OutTime"]))).ToString(NewTimeFormat));
                    }
                    strPre = Convert.ToString(DR["clk_EmpId"]);
                    //Style Added the created Headers.
                    //for (int i = 0; i <= 10; i++)
                    //{
                    //    row.GetCell(i).CellStyle = IStyle;
                    //    sheet.AutoSizeColumn(i);
                    //}
                    row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                    row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                    row.GetCell(8).CellStyle = row.GetCell(9).CellStyle = row.GetCell(10).CellStyle = IStyle;
                    rowIndex++;
                }
                saveAsFileName = string.Format("Discrepancy.xls");
                #endregion
                break;
            case "RawData":
                #region RawData
                sheet = workbook.CreateSheet("Raw Data Report Detailed Punches");
                row = sheet.CreateRow(rowIndex);
                hFont = (HSSFFont)workbook.CreateFont();
                hFont.Boldweight = (short)FontBoldWeight.Bold;
                hFont.Color = HSSFColor.Maroon.Index;
                hFont.FontHeightInPoints = 10;
                hStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle.SetFont(hFont);
                hStyle.FillForegroundColor = IndexedColors.White.Index;
                hStyle.FillPattern = FillPattern.SolidForeground;
                hStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                hStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                MainStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                MainStyle.SetFont(hFont);
                MainStyle.Alignment = HorizontalAlignment.Center;
                hStyle2 = (HSSFCellStyle)workbook.CreateCellStyle();
                hStyle2.SetFont(hFont);
                IStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                IStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                IStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                if (dtLevelName.Rows.Count > 0)
                {
                    row.CreateCell(0).SetCellValue(Convert.ToString(dtLevelName.Rows[0][0]));
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 7));
                    row.GetCell(0).CellStyle = MainStyle;
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                }
                row.CreateCell(0).SetCellValue("Raw Data Detailed Report From - " + Convert.ToString(frmDate) + " To - " + Convert.ToString(frmDate));
                row.GetCell(0).CellStyle = MainStyle;
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 7));
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue("Sl.No");
                row.CreateCell(1).SetCellValue("Card Id");
                row.CreateCell(2).SetCellValue("Employee Name");
                row.CreateCell(3).SetCellValue("Punch Date");
                row.CreateCell(4).SetCellValue("Terminal ID");
                row.CreateCell(5).SetCellValue("Punch Time");
                row.CreateCell(6).SetCellValue("Punch Type (Value)");
                row.CreateCell(7).SetCellValue("Status");
                row.CreateCell(8).SetCellValue("Remedy");
                for (int i = 0; i <= 8; i++)
                {
                    row.GetCell(i).CellStyle = hStyle;
                    sheet.AutoSizeColumn(i);
                }
                //row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                //row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                //row.GetCell(8).CellStyle = hStyle;
                rowIndex++;
                foreach (DataRow DR in dsEmp.Tables[0].Rows)
                {
                    row = sheet.CreateRow(rowIndex);

                    if (strPre == string.Empty || strPre != Convert.ToString(DR["Employee_Code_Id"]))
                    {
                        row.CreateCell(0).SetCellValue(Convert.ToString(EmployeeDatacount));
                        EmployeeDatacount++;
                    }
                    else
                    {
                        row.CreateCell(0).SetCellValue(string.Empty);
                    }
                    row.CreateCell(1).SetCellValue(Convert.ToString(DR["Employee_Code_Id"]));
                    row.CreateCell(2).SetCellValue(Convert.ToString(DR["EmployeeName"]));
                    row.CreateCell(3).SetCellValue(Convert.ToString(DR["PunchDate"]));
                    row.CreateCell(4).SetCellValue(Convert.ToString(DR["Terminal_Name"]));
                    if (Convert.ToString(DR["PunchTime"]) == String.Empty)
                        row.CreateCell(5).SetCellValue(Convert.ToString(DR["PunchTime"]));
                    else
                    {
                        row.CreateCell(5).SetCellValue((Convert.ToDateTime(Convert.ToString(DR["PunchTime"]))).ToString(NewTimeFormat));
                    }
                    row.CreateCell(6).SetCellValue(Convert.ToString(DR["PunchType"]));
                    row.CreateCell(7).SetCellValue(Convert.ToString(DR["Process"]));
                    if (Convert.ToString(DR["Resolution"]) == "Processed")
                        row.CreateCell(8).SetCellValue(Convert.ToString(""));
                    else
                    { row.CreateCell(8).SetCellValue(Convert.ToString(DR["Resolution"])); }

                    strPre = Convert.ToString(DR["Employee_Code_Id"]);
                    //for (int i = 0; i <= 8; i++)
                    //{
                    //    row.GetCell(i).CellStyle = IStyle;
                    //    sheet.AutoSizeColumn(i);
                    //}
                    row.GetCell(0).CellStyle = row.GetCell(1).CellStyle = row.GetCell(2).CellStyle = row.GetCell(3).CellStyle =
                    row.GetCell(4).CellStyle = row.GetCell(5).CellStyle = row.GetCell(6).CellStyle = row.GetCell(7).CellStyle =
                    row.GetCell(8).CellStyle = IStyle;
                    rowIndex++;
                }
                saveAsFileName = string.Format("RawData.xls");
                #endregion
                break;
        }
        using (MemoryStream exportData = new MemoryStream())
        {
            workbook.Write(exportData);
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
            Response.Clear();
            Response.BinaryWrite(exportData.GetBuffer());
            Response.End();
        }
    }
}