ASP.NET导出数据到Excel
2024-07-10 12:56:25
供稿：网友
该方法只是把asp.net页面保存成html页面只是把后缀改为xlc不过excel可以读取，接下连我看看还有别的方式能导出数据，并利用模版生成。
下面是代码
县新建一个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>
/// 创建数据源
/// </summary>
/// <returns>dataview</returns>
private void data_load()
{
//数据库连接字符串catalog为指定的数据库名称，datasource为要连接的sql服务器名称
string myconn ="user id=sa;password=sa;initial catalog=test;data source=zxb;connect timeout=20";
//查询字符串
string mysqlstr="select * from fy";
//连接数据库操作
sqlconnection myconnection = new sqlconnection(myconn);
//执行sql语句操作
sqldataadapter mydataadapter = new sqldataadapter(mysqlstr,myconnection);
//打开数据库
myconnection.open();
//向dataset填充数据，填充数据库服务器中test库中的fy表
mydataadapter.fill(myds,"fy");
//向dastagrid填充数据
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"; //设置了类型为中文防止乱码的出现
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");
}
}
}
}
还在继续研究别的方式
<%@ 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>
/// 创建数据源
/// </summary>
/// <returns>dataview</returns>
private void data_load()
{
//数据库连接字符串catalog为指定的数据库名称，datasource为要连接的sql服务器名称
string myconn ="user id=sa;password=sa;initial catalog=test;data source=zxb;connect timeout=20";
//查询字符串
string mysqlstr="select * from fy";
//连接数据库操作
sqlconnection myconnection = new sqlconnection(myconn);
//执行sql语句操作
sqldataadapter mydataadapter = new sqldataadapter(mysqlstr,myconnection);
//打开数据库
myconnection.open();
//向dataset填充数据，填充数据库服务器中test库中的fy表
mydataadapter.fill(myds,"fy");
//向dastagrid填充数据
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"; //设置了类型为中文防止乱码的出现
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");
}
}
}
}
还在继续研究别的方式
