I am working on a function "Export data from sql server database to Excel in MVC3", I want to select data based on certain conditions and then export the selected data to excel. Now I have achieved the following function, say, in the screenshot, if I select "Will expire this month" and then submit, it will only display members who will expire this month in the excel, not all the Members. If I select two dates in the calendar, it only displays members who expires between the two selected dates. Also, I can export data from database to excel based on the sql statement. The problem is I can write sql statement for exporting all the data from database to excel, not based on the conditions (expire this week, expiry this month...). I post my codes. Any ideas? Many thanks. 
RenewalDate Controller
public ActionResult Index()
{
ViewData["BreadCrumbs"] = new List<BreadCrumb> {
new BreadCrumb { Url = UrlMaker.ToDefault(), Title = "Home" },
new BreadCrumb {Url = UrlMaker.ToReportsArea(), Title = "Reports Area"},
new BreadCrumb { Title = "Renewal" } };
List<string> expiryOptions = new List<string>();
expiryOptions.Add("Will expire this month");
expiryOptions.Add("Expire next month");
expiryOptions.Add("Will expire this week");
expiryOptions.Add("Expire next week");
expiryOptions.Add("Have expired");
ViewBag.ExpiryOptions = new SelectList(expiryOptions);
ViewBag.a = 0;
ViewBag.ExpiryDate = string.Empty;
return View();
}
[HttpPost]
public ActionResult Index(string option, DateTime? expireFrom, DateTime? expireTo)
{
ViewData["BreadCrumbs"] = new List<BreadCrumb> {
new BreadCrumb { Url = UrlMaker.ToDefault(), Title = "Home" },
new BreadCrumb {Url = UrlMaker.ToReportsArea(), Title = "Reports Area"},
new BreadCrumb { Title = "Renewal" } };
Session["option"] = option;
List<string> expiryOptions = new List<string>();
expiryOptions.Add("will expire this month");
expiryOptions.Add("expire next month");
expiryOptions.Add("will expire this week");
expiryOptions.Add("expire next week");
expiryOptions.Add("have expired");
ViewBag.ExpiryOptions = new SelectList(expiryOptions);
ViewBag.a = 1;
if (expireFrom != null && expireTo != null)
{
ViewBag.ExpiryDate = (memberRepository.renewalDateQuery((DateTime)expireFrom, (DateTime)expireTo));
}
else
{
ViewBag.ExpiryDate = memberRepository.RenewalQuery(option);
}
return View();
}
private DataTable GetData(SqlCommand cmd)
{
DataTable dt = new DataTable();
String strConnString = System.Configuration.ConfigurationManager.
ConnectionStrings["ProActiveMembershipDB"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
sda.Dispose();
con.Dispose();
}
}
public ActionResult ExportToExcel()
{
//Get the data from database into datatable
string strQuery = "select MemberType,OrganisationName, Forename, Surname, Email, ExpiryDateofScheme from Members ";
SqlCommand cmd = new SqlCommand(strQuery);
DataTable dt = GetData(cmd);
//Create a dummy GridView
System.Web.UI.WebControls.GridView GridView1 = new System.Web.UI.WebControls.GridView();
GridView1.AllowPaging = false;
GridView1.DataSource = dt;
GridView1.DataBind();
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=RenewalMemberList.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
//Change the Header Row back to white color
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Apply style to Individual Cells
GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[4].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[5].Style.Add("background-color", "green");
for (int i = 0; i < GridView1.Rows.Count; i++)
{
GridViewRow row = GridView1.Rows[i];
//Change Color back to white
row.BackColor = System.Drawing.Color.White;
//Apply text style to each Row
GridView1.Rows[i].Attributes.Add("class", "textmode");
//Apply style to Individual Cells of Alternating Row
if (i % 2 != 0)
{
row.Cells[0].Style.Add("background-color", "#AAE92A");
row.Cells[1].Style.Add("background-color", "#AAE92A");
row.Cells[2].Style.Add("background-color", "#AAE92A");
row.Cells[3].Style.Add("background-color", "#AAE92A");
row.Cells[4].Style.Add("background-color", "#AAE92A");
row.Cells[5].Style.Add("background-color", "#AAE92A");
}
}
GridView1.RenderControl(hw);
//style to format numbers to string
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
return View();
}
}
Index View
@model IEnumerable<ProActiveMembership.Areas.Members.Models.Member>
@{
ViewBag.Title = "Report: Renewal Reports";
Layout = Layout = "~/Views/Shared/ProActive.cshtml";;
}
<h2 class="PageTitle">
<u>Renewal Report</u></h2>
@using (Html.BeginForm("Index", "RenewalDate", FormMethod.Post, new { id = "form" }))
{
<br />
<p>choose from one of the following options:</p>
<p>
I want to see members that: @Html.DropDownList("option", (SelectList)ViewBag.ExpiryOptions, "choose...", new { id = "ddl" })
</p>
}
@using (Html.BeginForm("Index", "RenewalDate", FormMethod.Post))
{
// below date1 is expirefrom date and date2 is the expireto date
<p>
expire between @Html.TextBox("expireFrom", "", new { id = "date1" }) and @Html.TextBox("expireTo", "", new { id = "date2" })
</p>
<input type="submit" value="submit" />
<br />
}
<div class="scrollable">
<table class="RenewalDateReports">
<tr class="RenewalDateReports">
<th class="RenewalDateReports">
Name
</th>
<th class="RenewalDateReports">
Expiry Date
</th>
<th class="RenewalDateReports">
Membership with
</th>
<th class="RenewalDateReports">
Grade
</th>
<th class="RenewalDateReports">
Action
</th>
</tr>
@foreach (var item in ViewBag.ExpiryDate)
{
<tr>
<td class="RenewalDateReports">
@item.Forename @item.Surname
@item.OrganisationName
</td>
<td class="RenewalDateReports">
@item.ExpiryDateofScheme.ToShortDateString()
</td>
<td class="RenewalDateReports">
@item.MembershipGrade.Organisation.OrganisationName
</td>
<td class="RenewalDateReports">
@item.MembershipGrade.GradeName
</td>
<td class="RenewalDateReports">
@* @Html.ActionLink("Notify", "###", new { id = item.MemberID })*@
@Html.ActionLink("Notify", "Communication", "Member", new { Area = "Members" }, null)
</td>
</tr>
@* @Html.ActionLink("Export to Excel", "ExportToExcel", "RenewalDate")*@
}
</table>
</div>
<br />
@Html.ActionLink("Export to Excel", "ExportToExcel", "RenewalDate")
<br />
<script type="text/javascript">
$(document).ready(function () {
$("#ddl").change(function () {
var form = document.getElementById("form");
form.submit();
});
$(function () {
$("#date1").datepicker({
dateFormat: 'dd/mm/yy',
firstDay: 1,
showOn: "button",
buttonImage: "../../assets/images/calendar.gif",
buttonImageOnly: true
});
});
$(function () {
$("#date2").datepicker({
dateFormat: 'dd/mm/yy',
firstDay: 1,
showOn: "button",
buttonImage: "../../assets/images/calendar.gif",
buttonImageOnly: true
});
});
});
</script>
ExportToExcel View
@{
ViewBag.Title = "ExportToExcel";
}
<h2>ExportToExcel</h2>
