asp.net 將repeater上資料匯出到excel

最後更新：2014-06-12 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

標籤：des style class code java http

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>

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More