ASP.NET導出數(shù)據(jù)到Excel
2024-07-10 12:56:25
供稿：網(wǎng)友
該方法只是把asp.net頁面保存成html頁面只是把后綴改為xlc不過excel可以讀取，接下連我看看還有別的方式能導出數(shù)據(jù)，并利用模版生成。
下面是代碼
縣新建一個asp.ne的tweb應用程序把代碼粘貼進去就好了
html頁面代碼
<%@ page language="c#" codebehind="outexcel.aspx.cs" autoeventwireup="false" inherits="emeng.exam.outputexcel" %>
<!doctype html public "-//w3c//dtd html 4.0 transitional//en" >
<html>
<head>
<title>outputexcel</title>
</head>
<body>
<form id="form1" method="post" runat="server">
<asp:datagrid id="datagrid1" runat="server">
<columns>
<asp:boundcolumn></asp:boundcolumn>
</columns>
</asp:datagrid>
<p>
<asp:label id="label1" runat="server">文件名：</asp:label>
<asp:textbox id="textbox1" runat="server"></asp:textbox>
<asp:button id="button1" runat="server" text="輸出到excel"></asp:button></p>
</form>
</body>
</html>
接下來是cs頁面里的代碼
using system;
using system.collections;
using system.componentmodel;
using system.data;
using system.drawing;
using system.web;
using system.web.sessionstate;
using system.web.ui;
using system.web.ui.webcontrols;
using system.web.ui.htmlcontrols;
using system.data.sqlclient;
namespace emeng.exam
{
/// <summary>
/// outputexcel 的摘要說明。
/// </summary>
public class outputexcel : system.web.ui.page
{
protected system.web.ui.webcontrols.button button1;
protected system.web.ui.webcontrols.datagrid datagrid1;
protected system.web.ui.webcontrols.textbox textbox1;
protected system.web.ui.webcontrols.label label1;
private dataset myds =new dataset();
private void page_load(object sender, system.eventargs e)
{
// 在此處放置用戶代碼以初始化頁面
if(!page.ispostback)
{
data_load();//調用方法填充表格
}
}
/// <summary>
/// 創(chuàng)建數(shù)據(jù)源
/// </summary>
/// <returns>dataview</returns>
private void data_load()
{
//數(shù)據(jù)庫連接字符串catalog為指定的數(shù)據(jù)庫名稱，datasource為要連接的sql服務器名稱
string myconn ="user id=sa;password=sa;initial catalog=test;data source=zxb;connect timeout=20";
//查詢字符串
string mysqlstr="select * from fy";
//連接數(shù)據(jù)庫操作
sqlconnection myconnection = new sqlconnection(myconn);
//執(zhí)行sql語句操作
sqldataadapter mydataadapter = new sqldataadapter(mysqlstr,myconnection);
//打開數(shù)據(jù)庫
myconnection.open();
//向dataset填充數(shù)據(jù)，填充數(shù)據(jù)庫服務器中test庫中的fy表
mydataadapter.fill(myds,"fy");
//向dastagrid填充數(shù)據(jù)
datagrid1.datasource=myds;
datagrid1.databind();
}
/// <summary>
/// 輸出到excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_click(object sender, system.eventargs e)
{
if(textbox1.text=="")
{
response.write("<script language=javascript>");
response.write("window.alert('請輸入文件名');");
response.write("</script>");
}
else
{
response.clear();
response.buffer= true;
response.charset="gb2312"; //設置了類型為中文防止亂碼的出現(xiàn)
response.appendheader("content-disposition","attachment;filename="+textbox1.text+".xls"); //定義輸出文件和文件名
response.contentencoding=system.text.encoding.getencoding("gb2312");//設置輸出流為簡體中文
response.contenttype = "application/ms-excel";//設置輸出文件類型為excel文件。
this.enableviewstate = false;
system.globalization.cultureinfo mycitrad = new system.globalization.cultureinfo("zh-cn",true);
system.io.stringwriter ostringwriter = new system.io.stringwriter(mycitrad);
system.web.ui.htmltextwriter ohtmltextwriter = new system.web.ui.htmltextwriter(ostringwriter);
this.datagrid1.rendercontrol(ohtmltextwriter);
response.write(ostringwriter.tostring());
}
}
#region web 窗體設計器生成的代碼
override protected void oninit(eventargs e)
{
//
// codegen: 該調用是 asp.net web 窗體設計器所必需的。
//
initializecomponent();
base.oninit(e);
}
/// <summary>
/// 設計器支持所需的方法 - 不要使用代碼編輯器修改
/// 此方法的內容。
/// </summary>
private void initializecomponent()
{
this.datagrid1.itemdatabound += new system.web.ui.webcontrols.datagriditemeventhandler(this.datagrid1_itemdatabound);
this.button1.click += new system.eventhandler(this.button1_click);
this.load += new system.eventhandler(this.page_load);
}
#endregion
private void datagrid1_itemdatabound(object sender, system.web.ui.webcontrols.datagriditemeventargs e)
{
if(e.item.itemtype == listitemtype.item || e.item.itemtype == listitemtype.alternatingitem)
{
e.item.cells[0].attributes.add("style","vnd.ms-excel.numberformat:@");
e.item.cells[3].attributes.add("style","vnd.ms-excel.numberformat:￥#,###.00");
}
}
}
}
還在繼續(xù)研究別的方式
<%@ page language="c#" codebehind="outexcel.aspx.cs" autoeventwireup="false" inherits="emeng.exam.outputexcel" %>
<!doctype html public "-//w3c//dtd html 4.0 transitional//en" >
<html>
<head>
<title>outputexcel</title>
</head>
<body>
<form id="form1" method="post" runat="server">
<asp:datagrid id="datagrid1" runat="server">
<columns>
<asp:boundcolumn></asp:boundcolumn>
</columns>
</asp:datagrid>
<p>
<asp:label id="label1" runat="server">文件名：</asp:label>
<asp:textbox id="textbox1" runat="server"></asp:textbox>
<asp:button id="button1" runat="server" text="輸出到excel"></asp:button></p>
</form>
</body>
</html>
接下來是cs頁面里的代碼
using system;
using system.collections;
using system.componentmodel;
using system.data;
using system.drawing;
using system.web;
using system.web.sessionstate;
using system.web.ui;
using system.web.ui.webcontrols;
using system.web.ui.htmlcontrols;
using system.data.sqlclient;
namespace emeng.exam
{
/// <summary>
/// outputexcel 的摘要說明。
/// </summary>
public class outputexcel : system.web.ui.page
{
protected system.web.ui.webcontrols.button button1;
protected system.web.ui.webcontrols.datagrid datagrid1;
protected system.web.ui.webcontrols.textbox textbox1;
protected system.web.ui.webcontrols.label label1;
private dataset myds =new dataset();
private void page_load(object sender, system.eventargs e)
{
// 在此處放置用戶代碼以初始化頁面
if(!page.ispostback)
{
data_load();//調用方法填充表格
}
}
/// <summary>
/// 創(chuàng)建數(shù)據(jù)源
/// </summary>
/// <returns>dataview</returns>
private void data_load()
{
//數(shù)據(jù)庫連接字符串catalog為指定的數(shù)據(jù)庫名稱，datasource為要連接的sql服務器名稱
string myconn ="user id=sa;password=sa;initial catalog=test;data source=zxb;connect timeout=20";
//查詢字符串
string mysqlstr="select * from fy";
//連接數(shù)據(jù)庫操作
sqlconnection myconnection = new sqlconnection(myconn);
//執(zhí)行sql語句操作
sqldataadapter mydataadapter = new sqldataadapter(mysqlstr,myconnection);
//打開數(shù)據(jù)庫
myconnection.open();
//向dataset填充數(shù)據(jù)，填充數(shù)據(jù)庫服務器中test庫中的fy表
mydataadapter.fill(myds,"fy");
//向dastagrid填充數(shù)據(jù)
datagrid1.datasource=myds;
datagrid1.databind();
}
/// <summary>
/// 輸出到excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_click(object sender, system.eventargs e)
{
if(textbox1.text=="")
{
response.write("<script language=javascript>");
response.write("window.alert('請輸入文件名');");
response.write("</script>");
}
else
{
response.clear();
response.buffer= true;
response.charset="gb2312"; //設置了類型為中文防止亂碼的出現(xiàn)
response.appendheader("content-disposition","attachment;filename="+textbox1.text+".xls"); //定義輸出文件和文件名
response.contentencoding=system.text.encoding.getencoding("gb2312");//設置輸出流為簡體中文
response.contenttype = "application/ms-excel";//設置輸出文件類型為excel文件。
this.enableviewstate = false;
system.globalization.cultureinfo mycitrad = new system.globalization.cultureinfo("zh-cn",true);
system.io.stringwriter ostringwriter = new system.io.stringwriter(mycitrad);
system.web.ui.htmltextwriter ohtmltextwriter = new system.web.ui.htmltextwriter(ostringwriter);
this.datagrid1.rendercontrol(ohtmltextwriter);
response.write(ostringwriter.tostring());
}
}
#region web 窗體設計器生成的代碼
override protected void oninit(eventargs e)
{
//
// codegen: 該調用是 asp.net web 窗體設計器所必需的。
//
initializecomponent();
base.oninit(e);
}
/// <summary>
/// 設計器支持所需的方法 - 不要使用代碼編輯器修改
/// 此方法的內容。
/// </summary>
private void initializecomponent()
{
this.datagrid1.itemdatabound += new system.web.ui.webcontrols.datagriditemeventhandler(this.datagrid1_itemdatabound);
this.button1.click += new system.eventhandler(this.button1_click);
this.load += new system.eventhandler(this.page_load);
}
#endregion
private void datagrid1_itemdatabound(object sender, system.web.ui.webcontrols.datagriditemeventargs e)
{
if(e.item.itemtype == listitemtype.item || e.item.itemtype == listitemtype.alternatingitem)
{
e.item.cells[0].attributes.add("style","vnd.ms-excel.numberformat:@");
e.item.cells[3].attributes.add("style","vnd.ms-excel.numberformat:￥#,###.00");
}
}
}
}
還在繼續(xù)研究別的方式
