Export Datatable value to Excel in asp.net c#

Hi Everyone in this article  have shown how to export data table value in excel .
basically I don't have ms office installed so i  have opened with browser.
basically that is html table i  am generating but because i have given .xls extension so we can open it in
excel also.

so whatever e column u have included in stored procedure will come in report.
just pass data table value or rows and any file name so it will take all rows from table and convert them into
html format so we can open in excel also.
lets have a look.

 On buttonclick it will export
protected void btnExport_Click(Object sender, EventArgs e)
    {
     
        ExportDataToExcelUsingDataTable((DataTable)ViewState["table"], "Report For User" + DateTime.Now.ToShortDateString() + ".xls");

    }


function to convert datatable record or data to excel format like 
public void ExportDataToExcelUsingDataTable(DataTable DtRecordOfUser, string FlNm)
    {
        try
        {
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + FlNm);
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            string tab = string.Empty;

            HttpContext.Current.Response.Write("<table width='100%' border='1' " + "cellSpacing='0' cellPadding='0' " +

"style='font-size:11.0pt;'> <tr style='font-size:12pt;'>");

            for (int j = 0; j < DtRecordOfUser.Columns.Count; j++)
            {

                HttpContext.Current.Response.Write("<td  style='background-color:#339999;color:#FFF878;'>");
                HttpContext.Current.Response.Write("<B>");
                HttpContext.Current.Response.Write(DtRecordOfUser.Columns[j].ColumnName.ToString());
                HttpContext.Current.Response.Write("</B>");
                HttpContext.Current.Response.Write("</td>");
            }
            HttpContext.Current.Response.Write("</tr>");
            foreach (DataRow row in DtRecordOfUser.Rows)
            {

                HttpContext.Current.Response.Write("<tr>");
                for (int i = 0; i < DtRecordOfUser.Columns.Count; i++)
                {
                    HttpContext.Current.Response.Write("<td  style='background-color:#FFFBD6;color:#333333;'>");
                    HttpContext.Current.Response.Write(row[i].ToString());
                    HttpContext.Current.Response.Write("</td>");
                }

                HttpContext.Current.Response.Write("</tr>");
            }
            

            HttpContext.Current.Response.Write("</table>");
            HttpContext.Current.Response.Write("</font>");
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
        }
        catch (Exception ex)
        {
            
        }

    }

Generate Dynamic table in html format in .cs page | Table Report | Design Report in asp.net with c#

Hi Everyone  in this article i am going to show how to generate dynamic table or we can say clone of grid view
with code behind .
we dont need to add any new control just apply css on code behind page and generate table with data.
i have shown both example with grid view and aslo with dynamic generated table with examples code.


0-> declare public string strDisplayDynamicTableReport = ""; before page load
1-> to take public string so we can assign string and then append on .aspx page like server control.
2-> We can bind our declare css also.
strDisplayDynamicTableReport += "<table cellpadding='0' cellspacing='0' border='1' bordercolor='red' >";
3->count table rows

for (int cnt = 0; cnt < DtGetAllEmoRecord.Rows.Count; cnt++)

4-> while  there is record in table add record in string and then print in html format.
by adding .aspx page

             <div>

                        <%=strDisplayDynamicTableReport%>

                  </div>


code for gridview


<asp:GridView runat="server" ID="grddata" AllowPaging="True" AutoGenerateColumns="false" DataKeyNames="NAME">
      <Columns>
          <asp:BoundField DataField="NAME" HeaderText="Name" ReadOnly="True"
              SortExpression="NAME" />
               <asp:BoundField DataField="NUMBER" HeaderText="Number" ReadOnly="True"
              SortExpression="NUMBER" />
               <asp:BoundField DataField="MAIL" HeaderText="Email Id" ReadOnly="True"
              SortExpression="MAIL" />
              <asp:TemplateField HeaderText="Delete">
              <ItemTemplate>
              <asp:LinkButton runat="server" ID="lnkdele" Text="delete" OnClick="lnkdele_click"></asp:LinkButton>
              </ItemTemplate>
              </asp:TemplateField>
                <asp:TemplateField HeaderText="Edit">
              <ItemTemplate>
              <asp:LinkButton runat="server" ID="lnkedit" Text="Edit" OnClick="lnkedit_click"></asp:LinkButton>
              </ItemTemplate>
              </asp:TemplateField>
      </Columns>
      <EmptyDataRowStyle BackColor="#FF9999" BorderStyle="Dashed" />
     
    </asp:GridView>



just call below function on page load or any control event..




private void DiplayAllREcord()
    {
       
        SqlDataAdapter sda = new SqlDataAdapter("Sp_InsertUpadetData 'SELECT','','','',''", sqcon);
        DataTable DtGetAllEmoRecord = new DataTable();
        sda.Fill(DtGetAllEmoRecord);
      
       if (DtGetAllEmoRecord != null && DtGetAllEmoRecord.Rows.Count > 0)
        {
            strDisplayDynamicTableReport += "<div id='UserDetails'>";
            strDisplayDynamicTableReport += "<table cellpadding='0' cellspacing='0' border='1' bordercolor='red' >";
            strDisplayDynamicTableReport += "<tr>";
            strDisplayDynamicTableReport += "<td width='150'><b>Employee Name:</b> </td>";
            strDisplayDynamicTableReport += "<td width='150'><b>Employee Number:</b> </td>";
            strDisplayDynamicTableReport += "<td width='150'><b>Employee Email:</b> </td>";
            strDisplayDynamicTableReport += "</tr>";
            for (int cnt = 0; cnt < DtGetAllEmoRecord.Rows.Count; cnt++)
            {
              
                strDisplayDynamicTableReport += "<tr>";
                strDisplayDynamicTableReport += "<td width='200'>" + DtGetAllEmoRecord.Rows[cnt]["NAME"].ToString() + "</td>";
                strDisplayDynamicTableReport += "<td width='200'>" + DtGetAllEmoRecord.Rows[cnt]["NUMBER"].ToString() + "</td>";
                strDisplayDynamicTableReport += "<td width='200'>" + DtGetAllEmoRecord.Rows[cnt]["MAIL"].ToString() + "</td>";
                strDisplayDynamicTableReport += "</tr>";
               
            }
            strDisplayDynamicTableReport += "</table>";
            strDisplayDynamicTableReport += "</div>";
        }
        grddata.DataSource = DtGetAllEmoRecord;
        grddata.DataBind();
       
 
    }

how to merge row in gridview when value is same

hi guys in this article i am  going to show you how to merge rows in grid view it two rows has same value.
idea is very simple to check if next and previous rows has some equal data then merge that number of rows.using col span.

lets have a look.

Now here there is no mathing value now m entering one matching value.

now here is name and number is same for dev so after click on merge button we will merge both row in same row and other will in different column.

here is code

protected void btnExport_Click(Object sender, EventArgs e)
{

    MergeRows(grddata);

}

public void MergeRows(GridView gridView)
{
    for (int rowIndex = gridView.Rows.Count - 2; rowIndex >= 0; rowIndex--)
    {
        GridViewRow row = gridView.Rows[rowIndex];
        GridViewRow previousRow = gridView.Rows[rowIndex + 1];

        if (row.Cells[0].Text == previousRow.Cells[0].Text && row.Cells[1].Text == previousRow.Cells[1].Text)
        {
            row.Cells[0].RowSpan = (previousRow.Cells[0].RowSpan < 2) ? 2 : previousRow.Cells[0].RowSpan + 1;
            previousRow.Cells[0].Visible = false;
            row.Cells[1].RowSpan = (previousRow.Cells[1].RowSpan < 2) ? 2 : previousRow.Cells[1].RowSpan + 1;
            previousRow.Cells[1].Visible = false;
        }
    }
}

How to Export Gridview data in excel format in c# asp.net with example

Hi everyone in this article i am going to show you how to Export gridview  data into excel.

first thing is that you should have  Ms offices installed i don't have installed but i will show you in browser.


lets get the code which will fill the grid .i am using visual studio 2010 and database is Sql server 2008.


private void DiplayAllREcord()
    {
       
        SqlDataAdapter sda = new SqlDataAdapter("Sp_InsertUpadetData 'SELECT','','','',''", sqcon);
        DataTable DtGetAllEmoRecord = new DataTable();
        sda.Fill(DtGetAllEmoRecord);
        grddata.DataSource = DtGetAllEmoRecord;
        grddata.DataBind();
       
       }


this is my code to fill grid view before post back .
now my button to export code behind code

protected void btnExport_Click(Object sender, EventArgs e)
{
    ExportExcelusingGridview(grddata, "Report For User" + DateTime.Now.ToShortDateString() + ".xls");
}

now i have given code to export gridview data to excel  as i dont have  MS - office installed so i m going to show in browser.

public void ExportExcelusingGridview(GridView grd, string fileName)
    {
        StringBuilder sbDocBody = new StringBuilder(); ;
        try
        {


            if (grd.Rows.Count > 0)
            {


                sbDocBody.Append("<table width='100%' border='1' " + "cellSpacing='0' cellPadding='0' " +

"style='font-size:11.0pt;'> <tr style='font-size:12pt;'>");
                // Add Data Rows
                for (int j = 0; j < grd.HeaderRow.Cells.Count; j++)
                    sbDocBody.Append("<td style='background-color:#ECE9D8;color:#000000;' width=\"120\"><B>" +

grd.HeaderRow.Cells[j].Text + "</B></td>");
                sbDocBody.Append("</tr>");
                for (int i = 0; i < grd.Rows.Count; i++)
                {
                    sbDocBody.Append("<tr>");
                    for (int j = 0; j < grd.Rows[i].Cells.Count; j++)
                    {
                        sbDocBody.Append("<td style='background-color:#FFFFE8;color:#333333;'>" + grd.Rows[i].Cells[j].Text +

"</td>");
                    }
                    sbDocBody.Append("</tr>");
                }
                sbDocBody.Append("<tr>");
                for (int j = 0; j < grd.FooterRow.Cells.Count; j++)
                {
                    sbDocBody.Append("<td style='background-color:#ECE9D8;color:#fff;' width=\"120\"

style='text-align:left'>" + grd.FooterRow.Cells[j].Text + "</td>");
                }
                sbDocBody.Append("</tr>");
                sbDocBody.Append("</table>");

            }
            else
                sbDocBody.Append("No records to export !!");

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.AppendHeader("Content-Type", "application/ms-excel");
            HttpContext.Current.Response.AppendHeader("Content-disposition", "attachment; filename=" + fileName);
            HttpContext.Current.Response.Write(sbDocBody.ToString());
            HttpContext.Current.Response.End();
        }
        catch (Exception ex)
        {
            //
        }
    }



so after click on excel it will appear like this..in browser but u can save it and if you need open with excel also.just copy code  ans paste it will work fine .just remember two things to include.
using System.Drawing;
using System.Text; //for string builder
using System.Web; // for httpcontext

Fetch all text box value into array

hi everyone in this article i have shown how to fetch text box value or words or characters and use it like array
means to fetch all value which has entered in text  box and then restore each single value into array.


what i did i just took text box value into string and  had count length of string will be max array length so there be
no index-out of range  error will be come.

then using for loop count length and store each single value into array.
here is code



string  stringarrayvalue=Txtmytextboxvalue.text
        int[] aarr=new int [Txtmytextboxvalue.Text.Length];

//or u can take         int[] aarr=new int [stringarrayvalue.Length]; both will work




        for (int i = 0; i < stringarrayvalue.Length; i++)
        {


            aarr[i] =Convert.ToInt32(stringarrayvalue[i].ToString());

            Label lbli = new Label();

            lbli.Text = aarr[i].ToString();

            dvlabl.Controls.Add(lbli);

// took dynamic lable to display value
         



        }

this code is working fine.just copy and  paste  it .but label is dynamic u can take asp label.