asp.net 查询，导出
using System;
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.Globalization;
using System.IO;
public partial class _PIE_Project : System.Web.UI.Page
{
protected System.Data.SqlClient.SqlConnection SqlConnection1;
protected System.Data.SqlClient.SqlDataAdapter SqlDataAdapter1;
protected System.Data.DataSet DataSet1;
protected System.Data.DataSet DataSet2;
string Str_sql = "";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
P_ht.H_data P_data = new P_ht.H_data();
P_data.sql_text1 = " SELECT [projectName], [WorkName], [PlanEndDate], [PlanStartDate] FROM [PJ_WorkPiece] ";
Str_sql = P_data.sql_text1;
P_data.select_tables();
DataSet1 = P_data.DataSet8;
Session["r1_PJ_WorkPiece"] = this.DataSet1;
this.GridView1.DataSource = DataSet1;
this.GridView1.DataBind();
}
}
protected void Btn_search_Click(object sender, EventArgs e)
{
string m_Where = "";
//
//// 正则表示式验证
//
//
//// check
//
//// Response.Write("非标准日期");
DateTime dt1,dt2;
//
if ((this.TextBox1.Text.Trim() != "") && (this.TextBox2.Text.Trim() != ""))
{
if (DateTime.TryParseExact(this.TextBox1.Text.Trim(), "yyyy-MM-dd", null, DateTimeStyles.None, out dt1))
{
//验证通过
}
else
{
ClientScriptManager scriptManager = ((Page)System.Web.HttpContext.Current.Handler).ClientScript;
scriptManager.RegisterStartupScript(typeof(string), "", "alert('提示: 项目开始日期不是日期格式! ');", true);
return;
}
if (DateTime.TryParseExact(this.TextBox2.Text.Trim(), "yyyy-MM-dd", null, DateTimeStyles.None, out dt2))
{
//验证通过
}
else
{
ClientScriptManager scriptManager = ((Page)System.Web.HttpContext.Current.Handler).ClientScript;
scriptManager.RegisterStartupScript(typeof(string), "", "alert('提示: 项目截止日期不是日期格式! ');", true);
return;
}
////
if ((dt2 < dt1))
{
ClientScriptManager scriptManager = ((Page)System.Web.HttpContext.Current.Handler).ClientScript;
scriptManager.RegisterStartupScript(typeof(string), "", "alert('提示: 项目开始日期不能大于项目截止日期! ');", true);
return;
}
else
{
m_Where += (m_Where == "" ? "" : " and ") + " PlanStartDate Between '" + this.TextBox1.Text.Trim() + "' and '" + this.TextBox2.Text.Trim() + "'";
}
}
else
{
if ((this.TextBox1.Text.Trim() != "") && (this.TextBox2.Text.Trim() == ""))
{
if (DateTime.TryParseExact(this.TextBox1.Text.Trim(), "yyyy-MM-dd", null, DateTimeStyles.None, out dt1))
{
//验证通过
m_Where += (m_Where == "" ? "" : " and ") + " PlanStartDate > '" + this.TextBox1.Text.Trim() + "' ";
}
else
{
ClientScriptManager scriptManager = ((Page)System.Web.HttpContext.Current.Handler).ClientScript;
scriptManager.RegisterStartupScript(typeof(string), "", "alert('提示: 项目开始日期不是日期格式! ');", true);
return;
}
}
else
{
if ((this.TextBox1.Text.Trim() == "") && (this.TextBox2.Text.Trim() != ""))
{
if (DateTime.TryParseExact(this.TextBox2.Text.Trim(), "yyyy-MM-dd", null, DateTimeStyles.None, out dt1))
{
//验证通过
m_Where += (m_Where == "" ? "" : " and ") + " PlanStartDate < '" + this.TextBox2.Text.Trim() + "' ";
}
else
{
ClientScriptManager scriptManager = ((Page)System.Web.HttpContext.Current.Handler).ClientScript;
scriptManager.RegisterStartupScript(typeof(string), "", "alert('提示: 项目开始日期不是日期格式! ');", true);
return;
}
}
}
}
// project
if (this.TextBox3.Text.Trim() != "")
{
m_Where += (m_Where == "" ? "" : " and ") + " projectName like '%" + this.TextBox3.Text.Trim() + "%' ";
}
//
//// Select
//
P_ht.H_data P_data = new P_ht.H_data();
P_data.sql_text1 = " SELECT [projectName], [WorkName], [PlanEndDate], [PlanStartDate] FROM [PJ_WorkPiece] where " + m_Where + " ";
Str_sql = P_data.sql_text1;
P_data.select_tables();
DataSet1 = P_data.DataSet8;
Session["r1_PJ_WorkPiece"] = this.DataSet1;
this.GridView1.DataSource = DataSet1;
this.GridView1.DataBind();
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
this.DataSet2 = new System.Data.DataSet();
this.DataSet2 = (DataSet)Session["r1_PJ_WorkPiece"];
this.GridView1.DataSource = this.DataSet2;
this.GridView1.DataBind();
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
int i;
//执行循环，保证每条数据都可以更新
for (i = -1; i < GridView1.Rows.Count; i++)
{
//首先判断是否是数据行
if (e.Row.RowType == DataControlRowType.DataRow)
{
//当鼠标停留时更改背景色
e.Row.Attributes.Add("onmouseover", "c=this.style.backgroundColor;this.style.backgroundColor='#bcbf56'");
//当鼠标移开时还原背景色
e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=c");
}
}
}
protected void btn_export_Click(object sender, EventArgs e)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/vnd.ms-xls";
StringWriter stringWrite = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
DataGrid dg = new DataGrid();
//dg.DataSource = ds;
this.DataSet2 = new System.Data.DataSet();
this.DataSet2 = (DataSet)Session["r1_PJ_WorkPiece"];
dg.DataSource = DataSet2;
dg.DataBind();
dg.RenderControl(htmlWrite);
//HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename="+HttpUtility.UrlEncode(excelName))
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename="+HttpUtility.UrlEncode("2015.xls"));
HttpContext.Current.Response.Write(stringWrite.ToString());
HttpContext.Current.Response.End();
}
}
