Attempting to use the ID value from a grid view via data source to retrieve and export data from another data source to excel

Hello,

I'm attempting to grab the ID from a hyperlink and a button within a gridview so I use the ID to

retrieve data and export it as a excel spreadsheet. I have the setup correctly; however,

I am unable to get the data to export. Currently, the spreadsheet generates without data.

I'm attempting to do it with the Export hyper link or the Export button. Can you assist me

in the understanding of what I'm doing wrong

C# code:

using System; using System.Collections; using System.Data; using System.Data.SqlClient; using System.IO; using System.Web.UI; using System.Web.UI.WebControls; using System.Drawing; using iTextSharp.text; using iTextSharp.text.pdf; using iTextSharp.text.html.simpleparser; using System.Configuration; using System.Web;   public partial class SelectEventExport : System.Web.UI.Page {     private int eventid = 0;     //private string strEventName;          protected void Page_Load(object sender, EventArgs e)     {          int rigRedirect;          if (Session["UserID"] == null)         {             Session.Abandon();             Response.Redirect("EventLogin.aspx");         }          else          {             rigRedirect = (int)(Session["Rights"]);                      if (Session["UserID"] == null && rigRedirect != 6 && rigRedirect != 1)                      {                         Session.Abandon();                         Response.Redirect("EventLogin.aspx");                      }                 else if (rigRedirect != 6 && rigRedirect != 1)                      {                         Session.Abandon();                         Response.Redirect("EventLogin.aspx");                      }                 else                      {                         Label1.Text = "Welcome " + Session["Fname"] + " " + Session["Lname"];                       }         }     }      protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)     {         eventid = Convert.ToInt32(Request.QueryString["e_ID"]);                String strCon = ConfigurationManager.ConnectionStrings["CME_RFID"].ConnectionString;        DataTable dtExport = new DataTable();         String strSQL = "SELECT DISTINCT PersonID, Person_Last, Person_First, Person_Title, Life_Number, Event_Name, EventID, CME_Event_Track_ID FROM vAttendanceList WHERE EventID = " + eventid;          SqlConnection connect = new SqlConnection(strCon);         SqlCommand command = new SqlCommand(strSQL, connect);                                   SqlDataAdapter adExport = new SqlDataAdapter(command);         adExport.Fill(dtExport);           exportSpreadsheet(dtExport, "ExportReport");          }          public static void exportSpreadsheet(DataTable dtTable, String strName)     {       HttpContext context = HttpContext.Current;       context.Response.Clear();                     foreach (DataColumn column in dtTable.Columns)         {             context.Response.Write(column.ColumnName + ",");         }         context.Response.Write(Environment.NewLine);       foreach (DataRow drRow in dtTable.Rows)         {             for (int i = 0; i < dtTable.Columns.Count; i++)             {                 context.Response.Write(drRow[i].ToString().Replace(",", string.Empty) + ",");             }             context.Response.Write(Environment.NewLine);         }         context.Response.ContentType = "text/csv";         context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + strName + ".csv");         context.Response.End();     }

Aspx code:

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterBadge.master" AutoEventWireup="true" CodeFile="SelectEventExport.aspx.cs" Inherits="SelectEventExport" %>   ID="Content1" ContentPlaceHolderID="head" Runat="Server">   ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">      style="text-align: center">          alt="" src="Images/sinai.jpg" style="width: 60px; height: 59px" />          style="font-size: xx-large; font-family: Verdana;">ID Badge - RFID Test          style="text-align: center">          ID="Label1" runat="server" Text="Label" Style="font-size: large; color: #FF3300;             font-weight: 500">             style="text-align: center">      align="center">    style="text-align: center">          ID="Label2" runat="server" Text="Select Events To Export" Style="font-size: large;             color: #000000; font-weight: 500; text-decoration: underline;">             align="center">      align="center"> <%--<asp:ImageButton ID="btnPDF" runat="server" ImageUrl="~/Images/PDF.jpg" Width="32px" Height="32px" onclick="btnPDF_Click"/> <asp:ImageButton ID="btnExportExcel" runat="server" ImageUrl="~/Images/ExcelImage.jpg" onclick="btnExportExcel_Click" />--%>     align="center"> <%--<p style="text-align: center"><asp:Button ID="btnLogin" runat="server" Text="Cancel" OnClick="btnLogin_Click"/> p>--%>      ID="DropDownList1" runat="server"          DataSourceID="SqlDataSource3" DataTextField="CME_Type"          DataValueField="CME_Type_ID" AutoPostBack="True">          <%$ ConnectionStrings:CME_RFID %>"          ProviderName="System.Data.SqlClient"         SelectCommand="SELECT CME_Type,CME_Type_ID  FROM CME_RFIDReader.CME_Type">         
   ID="GridView1" runat="server" DataSourceID="SqlDataSource1" AllowPaging="True"             AllowSorting="True" AutoGenerateColumns="False" BackColor="#CCCCCC" BorderColor="White"             BorderStyle="Solid" BorderWidth="3px" CellPadding="4" DataKeyNames="EventID"             CellSpacing="2" ForeColor="Black" Width="1258px"          OnSelectedIndexChanged="GridView1_SelectedIndexChanged">              BackColor="White" />                               DataTextField="EventID" DataNavigateUrlFields="EventID"                     DataNavigateUrlFormatString="~/SelectEventExport.aspx?e_ID={0}"                      DataTextFormatString="Export" />                   ButtonType="Button" CausesValidation="False"                      DeleteText="" EditText="Add Track ID" InsertText="" NewText=""                      SelectText="" ShowEditButton="True" UpdateText="Commit" />                  ButtonType="Button"                      DeleteText="" EditText="" InsertText="" NewText=""                      SelectText="Export" UpdateText="" InsertVisible="False"                      ShowCancelButton="False" ShowSelectButton="True" CancelText="" />                             DataField="CME_Event_Track_ID" HeaderText="CME Track ID" SortExpression="CME_Event_Track_ID" />                  DataField="Event_Type" HeaderText="Event Type" SortExpression="Event_Type" Visible="False" />                  DataField="Event_Name" HeaderText="Event Name" SortExpression="Event_Name" />                  DataField="Event_Desc" HeaderText="Event Desc" SortExpression="Event_Desc" />                  DataField="Event_Date" HeaderText="Event Date" SortExpression="Event_Date" />                           BackColor="#CCCCCC" />              BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />                                                          BackColor="#000099" Font-Bold="True" ForeColor="White" />              BackColor="Black" Font-Bold="True" ForeColor="White" />           /> 
           <%$ ConnectionStrings:CME_RFID %>" ProviderName="System.Data.SqlClient"         SelectCommand="SELECT DISTINCT EventID, CME_Event_Track_ID, Event_Type, Event_Name, Event_Desc, Dept_ID, Event_Date, Event_TimeStart, Event_TimeEnd FROM CME_RFIDReader.CME_Events WHERE Dept_ID =@DeptID AND Event_Type = @Event_Type ORDER BY Event_Date DESC"          UpdateCommand="UPDATE CME_RFIDReader.CME_Events SET CME_Event_Track_ID = @CME_Event_Track_ID WHERE EventID = @EventID AND Event_Type = @Event_Type">                      SessionField="DeptID" Name="DeptID" />              ControlID="DropDownList1" Name="Event_Type"  PropertyName="SelectedValue" Type="Int32" />

Hi Emmanuel,

import the gridview data in excel

Try this...

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="import_the_gridview_data_in_excel._Default" %>

Untitled Page

            Text="Import to Excel" />


using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text;
namespace import_the_gridview_data_in_excel
{
    public partial class _Default : System.Web.UI.Page
    {
        string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlDataAdapter sqlda = new SqlDataAdapter("SELECT * from employee", conn);
            DataSet ds = new DataSet();
            sqlda.Fill(ds, "employee");
            GridView1.DataSource = ds;
            GridView1.DataMember = "employee";
            GridView1.DataBind();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ExportToExcel("Report.xls", GridView1);
        }
        private void ExportToExcel(string strFileName, GridView dg)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";
            Response.Charset = "";
            this.EnableViewState = false;
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            GridView1.RenderControl(oHtmlTextWriter);
            Response.Write(oStringWriter.ToString());
            Response.End();

        }
        public override void VerifyRenderingInServerForm(Control control)
        {
        }
    }
}

Thanks
If this post helps you mark it as answer

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

emmanuel constant14y ago
Hello Satyapriya Nayak,

This solution is good; however, it is only using one data source. I'm using one data source that populates the gridview. Once the gridview is populated the user clicks on the hyperlink labeled Export. After the hyperlink is clicked the ID of the record clicked (via hyperlink) retrieved as a query string and then used as a where clause that for a second data source that is used to fill up a dataset that will be exported as a excel spreadsheet.

My problem with the code is that I'm not sure that I'm writing the logic to obtain the ID from the grid view to use as the where clause for the second data source correctly.
0
Satyapriya Nayak14y ago
Hi Emmanuel,

import the gridview data in excel

Try this...

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="import_the_gridview_data_in_excel._Default" %>

    Untitled Page










            Text="Import to Excel" />


using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text;
namespace import_the_gridview_data_in_excel
{
    public partial class _Default : System.Web.UI.Page
    {
        string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlDataAdapter sqlda = new SqlDataAdapter("SELECT * from employee", conn);
            DataSet ds = new DataSet();
            sqlda.Fill(ds, "employee");
            GridView1.DataSource = ds;
            GridView1.DataMember = "employee";
            GridView1.DataBind();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ExportToExcel("Report.xls", GridView1);
        }
        private void ExportToExcel(string strFileName, GridView dg)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";
            Response.Charset = "";
            this.EnableViewState = false;
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            GridView1.RenderControl(oHtmlTextWriter);
            Response.Write(oStringWriter.ToString());
            Response.End();

        }
        public override void VerifyRenderingInServerForm(Control control)
        {
        }
    }
}

Thanks
If this post helps you mark it as answer
0