Excel Report create customise excel with databse

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.IO;
using System.Data.SqlClient;
public partial class AmbitAdmin_AluminiReports : System.Web.UI.Page
{
    #region Private variables

    DataSet _ds = new DataSet();
    string StrDatefrom = "";
    string StrDateto = "";
    #endregion

   
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (!IsPostBack)
            {
                //getting citylist
                if (Session["Mess"] != null)
                    Session.Contents.Remove("Mess");
            }
        }
        catch (Exception ex)
        {
            throw (ex);
        }
    }

    protected void btnSave_Click(object sender, EventArgs e)
    {
        string strErrMsg = "";
        try
        {
            strErrMsg = checkValues();
            if (strErrMsg != "")
            {
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "Alert", "<script language='javascript' type='text/javascript'>alert('" + strErrMsg + "');</script>");
            }
            else
            {
                if (Page.IsValid)
                {
                    cmdDisplayReports();
                }
            }
        }
        catch (Exception ex)
        {
            throw (ex);
        }
    }

    /// <summary>
    /// Publish in excel
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    ///
    protected string Reports(DataTable dt)
    {

        DataTable toExcel = dt.Copy();
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        sb.Append("<table border='1' bordercolor='black' rules='all' " + "style='BORDER: 1px double; BORDER-COLLAPSE: collapse;' cellpading='2' cellspacing='2'>\r\n");
        sb.Append("<tr height=17 style='height:12.75pt'>\r\n");
            sb.Append("<td align='left' style='height:{1}pt;mso-number-format:\\@;' colspan=" + (toExcel.Columns.Count + 1).ToString() + "></td>\r\n");
        sb.Append("</tr>\r\n");
        sb.Append("<tr height=17 style='height:12.75pt'>\r\n");
            sb.Append("<td align='center' style='height:{1}pt;mso-number-format:\\@;' colspan=" + (toExcel.Columns.Count + 1).ToString() + "><strong>" + toExcel.Rows.Count.ToString() + " Records Found.</strong></td>\r\n");
        sb.Append("</tr>\r\n");
        sb.Append("<tr height=17 style='height:12.75pt'>\r\n");
            sb.Append("<td align='left' style='height:{1}pt;mso-number-format:\\@;' colspan=" + (toExcel.Columns.Count + 1).ToString() + "></td>\r\n");
        sb.Append("</tr>\r\n");
       
        sb.Append("<tr height=17 style='height:12.75pt'>\r\n");
        sb.Append("<td align='left' style='height:{1}pt;mso-number-format:\\@;'><strong>Sr. No</strong></td>\r\n"); //For Serial Number
        foreach (DataColumn column in toExcel.Columns)
        {
            sb.Append("<td align='left' style='height:{1}pt;mso-number-format:\\@;'><strong>" + column.ColumnName + "</strong></td>\r\n");
        }
        sb.Append("</tr>\r\n");
        if (toExcel.Rows.Count == 0)
        {
            sb.Append("<tr height=17 style='height:12.75pt'>\r\n");
            sb.Append("<td align='left' colspan'" + (toExcel.Columns.Count + 1) + "'>\r\n");
            sb.Append("No Records Found</td>\r\n");
            sb.Append("</tr>\r\n");
        }
        else
        {
            Boolean Flag;
            int Counter = 1;
            foreach (DataRow row in toExcel.Rows)
            {
                Flag = true;
                sb.Append("<tr height=17 style='height:12.75pt'>\r\n");
                foreach (DataColumn column in toExcel.Columns)
                {
                    if (Flag)
                    {
                        sb.Append("<td align='left' style='height:{1}pt;mso-number-format:\\@;'>" + Counter.ToString() + "</td>\r\n"); // For Serial Number
                        //sb.Append("<td align='left' style='height:{1}pt;mso-number-format:\\@;'>" + row[column].ToString().Replace(",", string.Empty) + "</td>\r\n");
                        sb.Append("<td align='left' style='height:{1}pt;mso-number-format:\\@;'>" + row[column].ToString() + "</td>\r\n");
                        Flag = false;
                    }
                    else
                    {
                        //sb.Append("<td align='left' style='height:{1}pt;mso-number-format:\\@;'>" + row[column].ToString().Replace(",", string.Empty) + "</td>\r\n");
                        sb.Append("<td align='left' style='height:{1}pt;mso-number-format:\\@;'>" + row[column].ToString() + "</td>\r\n");
                    }
                }
                sb.Append("</tr>\r\n");
                Counter += 1;
            }
        }
        sb.Append("</table>\r\n");
        return sb.ToString();
    }

    protected void cmdDisplayReports()
    {
        string attachment = "attachment; filename=Alumini_Report.xls";
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.ClearContent();
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.AddHeader("content-disposition", attachment);
        HttpContext.Current.Response.Charset = "";
        try
        {
            getReports();

            //if (_ds.Tables[0].Rows.Count > 0)
            //{
                StringBuilder sb = new StringBuilder();
                sb.Append(Reports(_ds.Tables[0]));
                HttpContext.Current.Response.Write(sb.ToString());
            //}
            HttpContext.Current.Response.End();
        }
        catch (Exception ex)
        {

            throw (ex);
        }
    }

  
  
    #region Private methods
    /// <summary>
    /// Bind dataset with details
    /// </summary>
    private void getReports()
    {
        #region private varibales
           
        #endregion

        // generating records
        try
        {
        StrDatefrom = txtFromDate.Text;
        StrDateto = txtDateTo.Text;
        _ds = getData();
        }
        catch (Exception ex)
        {
            throw (ex);
        }
    }

    /// <summary>
    /// Server Validation
    /// </summary>
    /// <returns></returns>
    private string checkValues()
    {
        #region Private variables
         string strErrMsg="";
        #endregion

        // checking values
        /*
        if (txtDateFrom.Text == "" && txtDateTo.Text == "")
            strErrMsg = "Please select atleast one criteria for search.";
       
         */
        if (txtFromDate.Text == "" && txtDateTo.Text != "")
             strErrMsg = "Please select From Date.";
         if (txtFromDate.Text != "" && txtDateTo.Text == "")
             strErrMsg = "Please select To Date.";
         if (txtFromDate.Text != "" && txtDateTo.Text != "")
        {
            if (Convert.ToDateTime(txtDateTo.Text) < Convert.ToDateTime(txtFromDate.Text))
                strErrMsg = "To Date cannot be less than From Date.";
        }
         return strErrMsg;
    }

    protected DataSet getData()
    {
        #region private variabes
        DataSet _dsData = new DataSet();
        string _strConnString = "";
        #endregion
        SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SqlConnString"]);
        SqlCommand cmd = null;
        try
        {
            cmd = new SqlCommand("webroot.Manage_Alumini_Form", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@action", SqlDbType.VarChar).Value = "GetReport";
            cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = DBNull.Value;
            cmd.Parameters.Add("@emailaddr", SqlDbType.VarChar).Value = DBNull.Value;
            cmd.Parameters.Add("@mobile", SqlDbType.VarChar).Value = DBNull.Value;
            cmd.Parameters.Add("@currOrg", SqlDbType.VarChar).Value = DBNull.Value;
            cmd.Parameters.Add("@currDesig", SqlDbType.VarChar).Value = DBNull.Value;
            cmd.Parameters.Add("@Date1", SqlDbType.VarChar).Value = txtFromDate.Text;
            cmd.Parameters.Add("@Date2", SqlDbType.VarChar).Value = txtDateTo.Text;

            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet(cmd.CommandText);
            da.Fill(ds);
            _dsData = ds;
        }
        catch (Exception ex)
        {

            throw (ex);
        }
        return _dsData;
    }


    #endregion
    #region
    private int getObjectLength()
    {
        return 19;
    }
    #endregion



}

xml data dum in sql Sp

 @xmlLanguage xml  

SET ARITHABORT ON

if (@Action='insertDownloadCenterDetails')
                Begin
                           insert into DownloadCenter(Title,UploadFile)values(@Title,@UploadFile)
                           set @DownloadCenterId=@@identity
                 end
                   Begin
                       DELETE FROM DownloadCenterDetails WHERE fkDownloadCenterId = @DownloadCenterId
                   end                 
      
            SELECT  
                ISNULL(cast(Colx.query('data(fkTabId)') as varchar(max)),'0') as fkTabId,
                ISNULL(cast(Colx.query('data(fkSubTabId)') as varchar(max)),'0') as fkSubTabId
                INTO #TMPLanguage1 FROM @xmlLanguage.nodes('DocumentElement/Table') AS Tabx(Colx)
                insert into DownloadCenterDetails(fkDownloadCenterId,fkTabId,fkSubTabId)
                select @DownloadCenterId,fkTabId, fkSubTabId from #TMPLanguage1
 ---------------------------------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;

public partial class Xml_DataTable_dump_SQL_SP_code : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable _dt = new DataTable();

        DataColumn col1 = new DataColumn("fkTabId");
        DataColumn col2 = new DataColumn("fkSubTabId");


        col1.DataType = System.Type.GetType("System.Int32");
        col2.DataType = System.Type.GetType("System.Int32");


        _dt.Columns.Add(col1);
        _dt.Columns.Add(col2);


        //fore each loop while for etc
        _dt.Rows.Add("", "");

        //_DownloadCenter.strxmlData = CommonFunctions.GetXMLOfDataTable(_dt);
        //_DownloadCenter.InsertDownloadCenterDetails();
        //_DownloadCenter = null;
    }
    public bool InsertDownloadCenterDetails()
    {
        bool flag = false;
        try
        {

            //obj = _accessData.GetObject();
            //obj[0] = "insertDownloadCenterDetails";
            //obj[1] = _DownloadCenterId;
            //obj[2] = _TabId;
            //obj[3] = _SubTabId;
            //obj[6] = _Title;
            //obj[7] = _UploadFile;
            //obj[12] = strxmlData;
            //flag = _accessData.executeStatement(obj);

            return flag;
        }
        catch (Exception ex)
        {
            throw (ex);
        }
    }
    public static string GetXMLOfDataTable(DataTable dtToProcess)
    {
        string strOutput = "";
        try
        {
            dtToProcess.TableName = "Table";

            StringWriter sw = new StringWriter();

            dtToProcess.WriteXml(sw);
            strOutput = sw.ToString();
        }
        catch (Exception ex)
        {
        }


        return strOutput;
    }
}

Menu handle by Treeview

<asp:TreeView ID="treeviwExample"  runat="server" ImageSet="Arrows">
            <HoverNodeStyle Font-Underline="True" ForeColor="#5555DD" />
            <NodeStyle Font-Names="Verdana" Font-Size="8pt" ForeColor="Black" HorizontalPadding="5px" NodeSpacing="0px" VerticalPadding="0px" />
            <ParentNodeStyle Font-Bold="False" />
            <SelectedNodeStyle Font-Underline="True" ForeColor="#5555DD" HorizontalPadding="0px" VerticalPadding="0px" />           
        </asp:TreeView>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class Treeview_checkchanges : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindTreeViewControl();
        }
    }
    private void BindTreeViewControl()
    {
        try
        {
            DataSet ds = GetDataSet("Select MenuId,MenuName,ParentId from tblMenuSite where deleted=0 order by groupid desc");
            DataRow[] Rows = ds.Tables[0].Select("ParentId IS NULL"); // Get all parents nodes
            for (int i = 0; i < Rows.Length; i++)
            {
                TreeNode root = new TreeNode(Rows[i]["MenuName"].ToString(), Rows[i]["MenuId"].ToString());
                root.SelectAction = TreeNodeSelectAction.Expand;
                CreateNode(root, ds.Tables[0]);
                treeviwExample.Nodes.Add(root);
            }
        }
        catch (Exception Ex) { throw Ex; }
    }

    public void CreateNode(TreeNode node, DataTable Dt)
    {
        DataRow[] Rows = Dt.Select("ParentId =" + node.Value);
        if (Rows.Length == 0) { return; }
        for (int i = 0; i < Rows.Length; i++)
        {
            TreeNode Childnode = new TreeNode(Rows[i]["MenuName"].ToString(), Rows[i]["MenuId"].ToString());
            Childnode.SelectAction = TreeNodeSelectAction.Expand;
            node.ChildNodes.Add(Childnode);
            CreateNode(Childnode, Dt);
        }
    }
    private DataSet GetDataSet(string Query)
    {
        DataSet Ds = new DataSet();
        try
        {
            string strCon = @"Data Source=;Initial Catalog=;uid=sa;pwd=;";
            SqlConnection Con = new SqlConnection(strCon);
            SqlDataAdapter Da = new SqlDataAdapter(Query, Con);
            Da.Fill(Ds);
        }
        catch (Exception) { }
        return Ds;
    }
}

dataset clone and copy and select query and distinct use dataset table filter and date filter

 -----------------------Short code------------------------
DataTable table6 = ds.Tables[0];
                    if (table6.Rows.Count > 0)
                    {
                        table6.DefaultView.RowFilter = string.Format(CultureInfo.InvariantCulture, "EZone ='" + ds.Tables[3].Rows[i]["Zone"].ToString().Trim() + "' and eemailid='" + ds.Tables[3].Rows[i]["eemailid"].ToString().Trim() + "' and Inv_Dist_Awareness ='2' and  instdate >= #" + moreThan + "# and instdate <= #" + lessThan + "#");
                        table6 = table5.DefaultView.ToTable();
                    }
                  DataRow[] rows;
   rows = table6.Select();

-----------------------------------------------------
 DataSet tempTaskDS = new DataSet();
                DataTable tempTable = ds.Tables[0].Clone();
                foreach (DataRow row in ds.Tables[0].Select("instdate >= #" + Currentmonth_fisrt + "# and instdate <= #" + Currentmonth_last + "#"))
                {
                    tempTable.Rows.Add(row.ItemArray);
                    i++;
                }

                tempTaskDS.Tables.Add(tempTable);
--------------------------------------------------


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;


public partial class Datasetclone_datasetclone : System.Web.UI.Page
{

    DataTable GetTable()
    {
        //
        // Here we create a DataTable with four columns.
        //
        DataTable table = new DataTable();
        table.Columns.Add("Dosage", typeof(int));
        table.Columns.Add("Drug", typeof(string));
        table.Columns.Add("Patient", typeof(string));
        table.Columns.Add("Date", typeof(DateTime));

        //
        // Here we add five DataRows.
        //
        table.Rows.Add(25, "Indocin", "David", DateTime.Now);
        table.Rows.Add(25, "ajit", "David", DateTime.Now);
        table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
        table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
        table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
        table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
        return table;
    }
    protected void Page_Load(object sender, EventArgs e)
    {

        DataTable ds = new DataTable();
        ds = GetTable();
        DataSet ds1 = new DataSet();
        ds1.Tables.Add(ds);
        _FilterAndInsert(ds1);
        //ds.Tables.Add(employees);
        //ds.Tables.Add(payCheckes);
    }
    public DataSet  _FilterAndInsert(DataSet sourceSet)
    {
         sourceSet.Tables[0].Columns.Add("ppID", typeof(string));
        ///Apply your filter clauses in Select() function parameter
        DataRow[] rows = sourceSet.Tables[0].Select("Dosage >="+25+"");
        ///Get the structure of source table
        DataTable tempTable = sourceSet.Tables[0].Clone();
        ///Add the filtered rows in temp table
        int i = 0;
        foreach (DataRow row in rows)
        {
            tempTable.Rows.Add(row.ItemArray[0], row.ItemArray[1], row.ItemArray[2], row.ItemArray[3], row.ItemArray[4] = (i+1).ToString());
            i++;
        }
        tempTable.Columns.Remove("Date"); 
        tempTable.AcceptChanges();
        ///Create new dataset
        DataSet resultSet = new DataSet();
        ///Add temp table at first index or modify this code sequence as you require
        resultSet.Tables.Add(tempTable);
        for (int index = 1; index < sourceSet.Tables.Count; index++)
        {
            ///Set the copy of source table in local instance
            DataTable tableToAdd = sourceSet.Tables[index].Copy();
            ///Remove from source to avoid any exceptions
            sourceSet.Tables.RemoveAt(index);
            ///Add the copy to result set
            resultSet.Tables.Add(tableToAdd);
        }
        ///Set the copy to source table from result set
        sourceSet = resultSet.Copy();

        //DataTable tbl;
        //DataTable tbl2 = new DataTable();
        //DataSet ds = new DataSet();
        //ds.Tables.Add(new DataTable());
        //ds.Tables[0].Columns.Add("ID", typeof(int));
        //ds.Tables[0].Columns.Add("Tour_Code", typeof(int));
        //ds.Tables[0].Columns.Add("Tour_Date", typeof(string));
        ////ds.Tables[0].Columns.Add("No_Of_Vehicle", typeof(int));
        //ds.Tables[0].Columns.Add("Vehicle", typeof(string));
        //tbl2 = ds.Tables[0].Clone();

        DataTable dt = new DataTable("pramod");
        dt.Columns.Add("Dosage", tempTable.Columns["Dosage"].DataType);

        object LastValue = null;
        foreach (DataRow dr in tempTable.Select("", "Dosage"))
        {
            if (LastValue == null || !(ColumnEqual(LastValue, dr["Dosage"])))
            {
                LastValue = dr["Dosage"];
                dt.Rows.Add(new object[] { LastValue });
            }
        }
       
        return sourceSet;
    }

    private bool ColumnEqual(object A, object B)
    {

        // Compares two values to see if they are equal. Also compares DBNULL.Value.
        // Note: If your DataTable contains object fields, then you must extend this
        // function to handle them in a meaningful way if you intend to group on them.

        if (A == DBNull.Value && B == DBNull.Value) //  both are DBNull.Value
            return true;
        if (A == DBNull.Value || B == DBNull.Value) //  only one is DBNull.Value
            return false;
        return (A.Equals(B));  // value type standard comparison
    }
}

public string _FilterAndInsert(DataSet sourceSet)
    {
        // sourceSet.Tables[0].Columns.Add("ppID", typeof(string));
        ///Apply your filter clauses in Select() function parameter
        string lessThan = DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString() + "-" + DateTime.Now.Year.ToString();
        string moreThan = DateTime.Now.Month.ToString() + "-" + DateTime.Now.AddDays(-7).Day.ToString() + "-" + DateTime.Now.Year.ToString();

        DataRow[] rows = sourceSet.Tables[0].Select("instdate <= #" + lessThan + "# and instdate > #" + moreThan + "#");//+ "AND instdate >= #" + moreThan + "#");
        ///Get the structure of source table
        DataTable tempTable = sourceSet.Tables[0].Clone();
        ///Add the filtered rows in temp table
        int i = 0;
        string weeklycount = rows.Length.ToString(); ;

        return weeklycount;
    }

select query by month and year

 datepart(mm,UpldDate) =@month and datepart(yyyy,UpldDate) =@year

Add favriate

<div class="rightFavourite" id="addbtnDiv" runat="server">
    <a href="javascript:;" class="addFavourite" onclick="javascript:addToCompare();">Add to Favourite</a></div>
<asp:HiddenField ID="hdnPageUrl" runat="server" />
<asp:HiddenField ID="hdnPageTitle" runat="server" />
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class UserControls_UCaddFvrtBtn : System.Web.UI.UserControl
{
    FavouritePages.clFvrtPg objClFvrtPg = new FavouritePages.clFvrtPg();

    protected void Page_Load(object sender, EventArgs e)
    {
        hdnPageUrl.Value = HttpContext.Current.Request.Url.ToString();
        hdnPageTitle.Value = this.Page.Title.ToString();
        checkPageExits();
    }
    public void checkPageExits()
    {
        string pageUrl = HttpContext.Current.Request.Url.ToString();
        string pageTitle = this.Page.Title.ToString();

        DataTable _dtFvrtPg = objClFvrtPg.dtFavouritePages;
        if (_dtFvrtPg != null)
        {
            if (_dtFvrtPg.Rows.Count > 0)
            {
                foreach (DataRow dr in _dtFvrtPg.Rows)
                {
                    if (pageUrl == dr["PageUrl"].ToString() && pageTitle == dr["PageTitle"].ToString())
                    {
                        addbtnDiv.Attributes.Add("style", "display:none");
                    }                 
                }
            }
        }
    }
}

Request UrlReferrer

 if (Request.UrlReferrer == null)
            {
}


<script language="JavaScript" type="text/javascript">
    window.history.forward();
    function noBack() { window.history.forward(); }

    </script>

<body onload="noBack();" onpageshow="if (event.persisted) noBack();" onunload="">