1,首先得到一个DataTable
public DataTable GetTable(string sql)
{
SqlConnnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstringname"].ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter sda
= new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
con.Close();
return
ds.Tables[0];
}
2,Excel导出功能实现的实现方法
/// <summary>
/// 将网格数据导出到Excel,
///
</summary>
/// <param
name="ctrl">网格名称(如GridView1)</param>
/// <param
name="FileType">要导出的文件类型(Excel:application/ms-excel)</param>
/// <param name="FileName">要保存的文件名</param>
//System.Web.UI.Control ctrl相当于Control ctrl 我这么写是由于我的项目中有冲突
private void
Export(System.Web.UI.Control ctrl, string FileType, string FileName)
{
HttpContext.Current.Response.Charset = "GB2312";
HttpContext.Current.Response.ContentEncoding =
System.Text.Encoding.UTF8;//注意编码
HttpContext.Current.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(FileName,
System.Text.Encoding.UTF8).ToString());
HttpContext.Current.Response.ContentType =
FileType;//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
ctrl.Page.EnableViewState = false;
StringWriter tw = new
StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
ctrl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
//这个重写是必须的，可以不让他做事，但是必须存在
public override void
VerifyRenderingInServerForm(System.Web.UI.Control control)
{
}
3，在导出按钮的事件中调用Excel的导出方法
protected void btnExport_Click(object sender, EventArgs e)
{
string classID = Request["ClassID"].ToString();
string
dt1 = txtStart.Value;
string dt2 = txtEnd.Value;
string sql =
"select
ClassCname,NewsTitle,a.CreatTime as createtime,Editor FROM jy_news a,jy_nc
b,jy_news_class c where a.NewsID=b.NewsID AND b.ClassID=c.ClassID ";
string where = "";
if
(!string.IsNullOrEmpty(classID))
{
where+="
and b.Classid=‘" +classID + "‘";
}
if (!string.IsNullOrEmpty(dt1))
{
where += " and a.CreatTime>=‘" + dt1 + "‘";
}
if (!string.IsNullOrEmpty(dt2))
{
where += " and a.CreatTime<=‘" + dt2 + "‘";
}
sql = sql + where + " order by a.CreatTime desc";
DataTable dt = GetTable(sql);
this.rptdata.DataSource = dt;
this.rptdata.DataBind();
foreach (System.Web.UI.Control c in rptdata.Controls)
{
Label lbl1 = (Label)c.FindControl("Label1");
Label lbl2 = (Label)c.FindControl("Label2");
if
(string.IsNullOrEmpty(lbl1.Text) && string.IsNullOrEmpty(lbl2.Text))
{
if (!string.IsNullOrEmpty(dt1)
&& !string.IsNullOrEmpty(dt2))
{
string dt3 = string.Format("{0:yyyy.MM.dd}", DateTime.Parse(dt1));
string dt4 = string.Format("{0:yyyy.MM.dd}",
DateTime.Parse(dt2));
lbl1.Text = dt3;
lbl2.Text
= dt4;
}
}
break;
}
string filename = "新闻列表";
if
(!string.IsNullOrEmpty(dt1))
{
filename = "_"
+ dt1;
}
if (!string.IsNullOrEmpty(dt2))
{
filename = filename + "-" + dt2;
}
filename = filename + ".xls";
string filetype = "application/ms-excel";
System.Web.UI.Control ctrl = rptdata;
Export(ctrl, filetype,
filename);
}
最后是前台页面，页面上有个日历控件My97DatePicker
<html>
<head runat="server">
<title>新闻列表导出</title>
<script src="/My97DatePicker/WdatePicker.js"
type="text/javascript"></script>
</head>
<body>
<form id="form1"
runat="server">
<div id="msg">
<p style="color:
red">
</p>
</div>
<div>
<label id="lblstart">
开始时间:</label><input type="text"
id="txtStart" onclick="WdatePicker()" runat="server"
onchange="txt();" />
<label
id="lblend">
结束时间:</label><input type="text" id="txtEnd"
onclick="WdatePicker()" runat="server" />
<asp:Button
ID="btnExport" runat="server" Text="导出" OnClick="btnExport_Click" />
</div>
<div id="repeaterView">
<asp:Repeater
ID="rptdata" runat="server">
<HeaderTemplate>
<table border="1" cellpadding="0" cellspacing="0" style="width:
1006px; border-collapse: collapse;
text-align:
center;">
<tr>
<td
colspan="5" style="text-align: center; font-size: 150%">
<strong>普陀区科协网站信息发布签发单</strong>
</td>
</tr>
<tr style="height: 30px">
<td colspan="3"
style="border: 0px">
发布时间： <asp:Label ID="Label1" runat="server"
Text=""></asp:Label>--
<asp:Label ID="Label2" runat="server"
Text=""></asp:Label>
</td>
<td colspan="2" style="border:
0px">
签发人：
</td>
</tr>
<tr style="height: 30px">
<td
style="font-weight:bold; text-align: center">
<font size="3">序号</font>
</td>
<td style="font-weight: bold; text-align:
center">
<font
size="3">标题</font>
</td>
<td style="font-weight: bold; text-align: center">
<font size="3">所在栏目</font>
</td>
<td style=" font-weight: bold;
text-align: center">
<font
size="3">发布时间</font>
</td>
<td style="font-weight: bold; text-align: center">
<font size="3">发布人</font>
</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr style="height: 30px">
<td style="text-align:
center">
<%# Container.ItemIndex + 1 %>
</td>
<td style="text-align:
center">
<%#
DataBinder.Eval(Container.DataItem, "NewsTitle")%>
</td>
<td style="text-align: center">
<%# DataBinder.Eval(Container.DataItem,
"ClassCname")%>
</td>
<td style="text-align: center">
<%#string.Format("{0:yyyy年MM月dd日}", Eval("createtime"))%>
</td>
<td style="text-align:
center">
<%#
DataBinder.Eval(Container.DataItem, "Editor")%>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
</div>
</form>
</body>
</html>