I encountered issues with excel export. My query page has fields which are also getting exported into the spreadsheet. I only want the gridview results exported into the spreadsheet.
Query.aspx (form fields to query database, results into Grid)
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Query.aspx.cs" Inherits="Query " %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html dir="ltr" xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Citizens With Disabilities - Ont</title>
<style type="text/css">
.auto-style1
{
font-size: medium;
}
.auto-style2
{
font-size: large;
}
.auto-style3
{
text-align: center;
border-color: #C0C0C0;
background-color: #C0C0C0;
}
.auto-style5
{
border-collapse: collapse;
border-left-style: solid;
border-left-width: 1px;
border-right: 1px solid #C0C0C0;
border-top-style: solid;
border-top-width: 1px;
border-bottom: 1px solid #C0C0C0;
border-color: #C0C0C0;
background-color: #C0C0C0;
}
.auto-style8
{
font-family: "Times New Roman" , Times, serif;
border-color: #C0C0C0;
}
.auto-style10
{
font-size: medium;
font-family: "Times New Roman" , Times, serif;
border-color: #C0C0C0;
}
.auto-style14
{
margin-top: 0px;
margin-right: 8px;
}
</style>
</head>
<body class="auto-style2" style="margin-left: 80px; background-color: #C0C0C0">
<form id="form1" runat="server" class="auto-style5">
<div class="auto-style3">
<strong><span style="color: rgb(0, 0, 0); font-family: " helvetica neue"
, helvetica, arial, sans-serif; font-style: normal; font-variant-ligatures: normal;
font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2;
text-align: start; text-indent: 0px; text-transform: none; white-space: normal;
widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; display: inline !important;
float: none;"><span style="color: rgb(0, 0, 0); font-family: " helvetica neue"
, helvetica, arial, sans-serif; font-style: normal; font-variant-ligatures: normal;
font-variant-caps: normal; letter-spacing: normal; orphans: 2; text-align: start;
text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; display: inline !important; float: none;">QUERY
FORM FOR RECORDS IN DATABASE</span></span></strong></div>
<strong>
<asp:Button ID="Button10" runat="server" PostBackUrl="Search.aspx" Text="QUERY" Font-Bold="True"
Height="57px" Width="259px" />
<asp:Button ID="btnExport" runat="server" Text="Export To Excel" OnClick="ExportToExcel"
CssClass="auto-style14" Height="56px" Width="294px" Font-Bold="True" />
<asp:Button ID="Reset_Button" runat="server" Text="Reset" Width="223px" OnClientClick="this.form.reset();return false;"
Font-Bold="True" Height="64px" />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
SortExpression="ID"></asp:BoundField>
<asp:BoundField DataField="ElectoralRiding" HeaderText="ElectoralRiding" SortExpression="ElectoralRiding">
</asp:BoundField>
<asp:BoundField DataField="Subscription" HeaderText="Subscription" SortExpression="Subscription">
</asp:BoundField>
<asp:BoundField DataField="PreferredFormat" HeaderText="PreferredFormat" SortExpression="PreferredFormat">
</asp:BoundField>
<asp:BoundField DataField="Interest" HeaderText="Interest" SortExpression="Interest">
</asp:BoundField>
<asp:BoundField DataField="DisabilityStatus" HeaderText="DisabilityStatus" SortExpression="DisabilityStatus">
</asp:BoundField>
<asp:BoundField DataField="Experience" HeaderText="Experience" SortExpression="Experience">
</asp:BoundField>
<asp:BoundField DataField="MembershipType" HeaderText="MembershipType" SortExpression="MembershipType">
</asp:BoundField>
<asp:BoundField DataField="Skillsets" HeaderText="Skillsets" SortExpression="Skillsets">
</asp:BoundField>
<asp:BoundField DataField="Question" HeaderText="Question" SortExpression="Question">
</asp:BoundField>
<asp:BoundField DataField="MembershipNumber" HeaderText="MembershipNumber" SortExpression="MembershipNumber">
</asp:BoundField>
<asp:BoundField DataField="DateJoined" HeaderText="DateJoined" SortExpression="DateJoined">
</asp:BoundField>
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name"></asp:BoundField>
<asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address">
</asp:BoundField>
<asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone"></asp:BoundField>
<asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email"></asp:BoundField>
<asp:BoundField DataField="Expiration" HeaderText="Expiration" SortExpression="Expiration">
</asp:BoundField>
<asp:BoundField DataField="Goals" HeaderText="Goals" SortExpression="Goals"></asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:db_44057472_29d3_489a_acb6_29456319f8f2ConnectionString %>"
SelectCommand="SELECT * FROM [Table1] WHERE ((([ElectoralRiding] Like '%' + @ElectoralRiding + '%') OR (@ElectoralRiding IS NULL)) AND ([Experience] Like '%' + @Experience + '%') OR (@Experience IS NULL))">
<SelectParameters>
<asp:ControlParameter ControlID="ListBox1" Name="ElectoralRiding" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="ListBox6" Name="Experience" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</strong>
<br />
<p>
<br class="auto-style10" />
</p>
<p>
<span class="auto-style1" onload="theForm.reset();">
<br class="auto-style8" />
<strong>Members' Electoral Ridings</strong><br class="auto-style8" />
</p>
<asp:CheckBoxList ID="ListBox1" runat="server" Height="150px" Width="509px">
<asp:ListItem>Municipal riding </asp:ListItem>
<asp:ListItem>Provincial riding </asp:ListItem>
<asp:ListItem>Federal riding </asp:ListItem>
</asp:CheckBoxList>
<p>
</p>
<p>
<br class="auto-style8" />
<strong>Members' Experience of Disability </strong>
</p>
<asp:CheckBoxList ID="ListBox6" runat="server" Height="127px" Width="665px" CssClass="auto-style1">
<asp:ListItem>ADD/ADHD </asp:ListItem>
<asp:ListItem>Agility </asp:ListItem>
<asp:ListItem>Brain Injury </asp:ListItem>
<asp:ListItem>Chronic Pain </asp:ListItem>
<asp:ListItem>Deaf and Use Sign Language </asp:ListItem>
<asp:ListItem>Deaf, deafened or hard of Hearing </asp:ListItem>
<asp:ListItem>Intellectual Disability </asp:ListItem>
<asp:ListItem>Learning Disability </asp:ListItem>
<asp:ListItem>Mental Health </asp:ListItem>
<asp:ListItem>Mobility </asp:ListItem>
<asp:ListItem>Speech </asp:ListItem>
<asp:ListItem>Stamina </asp:ListItem>
<asp:ListItem>Blind or partially sighted </asp:ListItem>
<asp:ListItem>Other (please specify): </asp:ListItem>
</asp:CheckBoxList>
</span><strong>
<br />
</form>
</body>
</html>
Query.aspx.cs (highlighted the excel part)
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.Collections.Specialized;
using System.Text;
using System.Collections.Generic;
using System.IO;
public partial class Query : System.Web.UI.Page
{
private string GetConnectionString()
{
//Where DBConnection is the connetion string that was set up in the web config file
return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
}
protected void Button1_Click(object sender, EventArgs e)
{
string lb1 = string.Empty;
string lb2 = string.Empty;
string lb3 = string.Empty;
string lb4 = string.Empty;
string lb5 = string.Empty;
string lb6 = string.Empty;
string lb7 = string.Empty;
string lb8 = string.Empty;
string lb9 = string.Empty;
foreach (ListItem item in ListBox1.Items)
if (item.Selected)
{
lb1 += item.Text + ",";
}
foreach (ListItem item in ListBox2.Items)
if (item.Selected)
{
lb2 += item.Text + ",";
}
foreach (ListItem item in ListBox3.Items)
if (item.Selected)
{
lb3 += item.Text + ",";
}
foreach (ListItem item in ListBox4.Items)
if (item.Selected)
{
lb4 += item.Text + ",";
}
foreach (ListItem item in ListBox5.Items)
if (item.Selected)
{
lb5 += item.Text + ",";
}
foreach (ListItem item in ListBox6.Items)
if (item.Selected)
{
lb6 += item.Text + ",";
}
foreach (ListItem item in ListBox7.Items)
if (item.Selected)
{
lb7 += item.Text + ",";
}
foreach (ListItem item in ListBox8.Items)
if (item.Selected)
{
lb8 += item.Text + ",";
}
foreach (ListItem item in ListBox9.Items)
if (item.Selected)
{
lb9 += item.Text + ",";
}
InsertRecords(lb1, lb2, lb3, lb4, lb5, lb6, lb7, lb8, lb9);
}
private void InsertRecords(String lb1, String lb2, String lb3, String lb4, String lb5, String lb6, String lb7, String lb8, String lb9)
{
SqlConnection conn = new SqlConnection(GetConnectionString());
SqlCommand cmd = new SqlCommand("INSERT INTO Search (ElectoralRiding, Subscription, PreferredFormat, Interest, DisabilityStatus, Experience, MembershipType, Skillsets, Question, MembershipNumber, DateJoined, Name, Address, Phone, Email, Expiration, Goals) Values(@lb1, @lb2, @lb3, @lb4, @lb5, @lb6, @lb7, @lb8, @lb9, @tb1, @tb2, @tb3, @tb4, @tb5, @tb6, @tb7, @tb8)", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@lb1", lb1);
cmd.Parameters.AddWithValue("@lb2", lb2);
cmd.Parameters.AddWithValue("@lb3", lb3);
cmd.Parameters.AddWithValue("@lb4", lb4);
cmd.Parameters.AddWithValue("@lb5", lb5);
cmd.Parameters.AddWithValue("@lb6", lb6);
cmd.Parameters.AddWithValue("@lb7", lb7);
cmd.Parameters.AddWithValue("@lb8", lb8);
cmd.Parameters.AddWithValue("@lb9", lb9);
cmd.Parameters.AddWithValue("@tb1", TextBox1.Text.Trim());
cmd.Parameters.AddWithValue("@tb2", TextBox2.Text.Trim());
cmd.Parameters.AddWithValue("@tb3", TextBox3.Text.Trim());
cmd.Parameters.AddWithValue("@tb4", TextBox4.Text.Trim());
cmd.Parameters.AddWithValue("@tb5", TextBox5.Text.Trim());
cmd.Parameters.AddWithValue("@tb6", TextBox6.Text.Trim());
cmd.Parameters.AddWithValue("@tb7", TextBox7.Text.Trim());
cmd.Parameters.AddWithValue("@tb8", TextBox8.Text.Trim());
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
string message = "Your details have been saved successfully.";
string script = "window.onload = function(){ alert('";
script += message;
script += "');";
script += "window.location = '";
script += Request.Url.AbsoluteUri;
script += "'; }";
ClientScript.RegisterStartupScript(this.GetType(), "SuccessMessage", script, true);
}
protected void ExportToExcel(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=gridviewdata.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
}
}