在web from上输出数据到excel有两种方法，一个是有数据库直接导出；另外一个方法是由datagrid直接输出到excel文件。下面得代码实现了这两个功能。注意：在使用时要引用microsoft office web components 9.0 com组件，另外注意设置要保存文件得目录具有匿名可修改的权限。
datagridtoexcel.aspx
<![CDATA[<%@ page language="vb" enableviewstate="false" autoeventwireup="false" codebehind="datagridtoexcel.<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank" rel="nofollow" >asp</a>x.vb”<br />
inherits=”<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank" rel="nofollow" >asp</a>xweb.mengxianhui.com.datagridtoexcel”%><br />
<!doctype html public "-//w3c//dtd html 4.0 transitional//en"><br />
<html><br />
<head><br />
<title id="mengxianhui" runat="server"></title><br />
<meta name="generator" content="microsoft visual studio.net 7.0"><br />
<meta name="code_language" content="visual basic 7.0"><br />
<meta name="vs_defaultclientscript" content="javascript"><br />
<meta name="vs_targetschema" content="http://schemas.microsoft.com/intellisense/ie5"><br />
</head><br />
<body ms_positioning="gridlayout" style="font-size:9pt"></p>
<form id="form1" method="post" runat="server">
<<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank" rel="nofollow" >asp</a>:label id=”label1″ runat=”server”></<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank" rel="nofollow" >asp</a>:label><br />
<<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank" rel="nofollow" >asp</a>:textbox id=”xlfile” runat=”server”></<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank" rel="nofollow" >asp</a>:textbox></p>
<p> <<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank" rel="nofollow" >asp</a>:button id=”exportdatabase2excel” runat=”server” /><br />
<<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank" rel="nofollow" >asp</a>:button id=”exportdatagrid2excel” runat=”server” /><br />
<br />
<<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank" rel="nofollow" >asp</a>:datagrid id=”datagrid1″ runat=”server” autogeneratecolumns=”false” bordercolor=”#cc9966″<br />
borderstyle=”none” borderwidth=”1px” backcolor=”white” cellpadding=”4″><br />
<itemstyle forecolor="#330099" backcolor="white"></itemstyle><br />
<headerstyle font-bold="true" forecolor="#ffffcc" backcolor="#990000"></headerstyle>
<columns>
<<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank" rel="nofollow" >asp</a>:boundcolumn datafield=”title”></<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank" rel="nofollow" >asp</a>:boundcolumn><br />
<<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank" rel="nofollow" >asp</a>:boundcolumn datafield=”author”></<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank" rel="nofollow" >asp</a>:boundcolumn><br />
</columns>
</<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/web/asp/index.html" target="_blank" rel="nofollow" >asp</a>:datagrid><br />
</form>
<p> </body><br />
</html><br />
]]>
datagridtoexcel.aspx.vb
<![CDATA[imports system<br />
imports system.data<br />
imports system.data.oledb<br />
imports owc</p>
<p>public class datagridtoexcel<br />
inherits system.web.ui.page<br />
protected withevents xlfile as system.web.ui.webcontrols.textbox<br />
protected withevents datagrid1 as system.web.ui.webcontrols.datagrid<br />
protected withevents exportdatagrid2excel as system.web.ui.webcontrols.button<br />
protected withevents exportdatabase2excel as system.web.ui.webcontrols.button<br />
protected withevents label1 as system.web.ui.webcontrols.label<br />
protected mengxianhui as new htmlgenericcontrol()</p>
<p> private cnn as oledbconnection = new oledbconnection(“provider=microsoft.jet.oledb.4.0;data source=”_<br />
+ server.mappath(“test.mdb”))<br />
private <a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/database/mssql/index.html" target="_blank" rel="nofollow" >sql</a> as oledbcommand = new oledbcommand(“select top 50 title,author from document”, cnn)</p>
<p>#region ” web form designer generated code ”</p>
<p> this call is required by the web form designer.<br />
<system.diagnostics.debuggerstepthrough()> private sub initializecomponent()</p>
<p> end sub</p>
<p> private sub page_init(byval sender as system.object, byval e as system.eventargs)_<br />
handles mybase.init<br />
codegen: this method call is required by the web form designer<br />
do not modify it using the code editor.<br />
initializecomponent()<br />
end sub</p>
<p>#end region</p>
<p> private sub page_load(byval sender as system.object, byval e as system.eventargs)_<br />
handles mybase.load<br />
label1.text = “请输入要保存得<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/soft/tools/file/index.html" target="_blank" rel="nofollow" >文件</a>名字：”<br />
exportdatagrid2excel.text = “由datagrid生成excel<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/soft/tools/file/index.html" target="_blank" rel="nofollow" >文件</a>”<br />
exportdatabase2excel.text = “<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/database/index.html" target="_blank" rel="nofollow" >数据库</a>直接生成excel<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/soft/tools/file/index.html" target="_blank" rel="nofollow" >文件</a>”<br />
datagrid1.columns(0).headerstyle.horizontalalign = horizontalalign.center<br />
datagrid1.columns(0).headertext = “文章名称”<br />
datagrid1.columns(1).headertext = “作者”<br />
datagrid1.columns(0).headerstyle.font.bold = true<br />
datagrid1.style.add(“font-size”, “9pt”)<br />
mengxianhui.innertext = “【孟宪会之精彩世界】－ 将datagrid输出到excel<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/soft/tools/file/index.html" target="_blank" rel="nofollow" >文件</a>”<br />
me.binddatagrid()<br />
end sub</p>
<p> private sub binddatagrid()<br />
cnn.open()<br />
dim reader as oledbdatareader = <a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/database/mssql/index.html" target="_blank" rel="nofollow" >sql</a>.executereader()<br />
me.datagrid1.datasource = reader<br />
me.datagrid1.databind()<br />
reader.close()<br />
cnn.close()<br />
end sub</p>
<p> private sub writedatagrid2excel()<br />
dim xlsheet as new spreadsheetclass()<br />
cnn.open()<br />
dim reader as oledbdatareader = me.<a href="http://www.west999.com/www/go/?url=http://www.chinaitpower.com/dev/database/mssql/index.html" target="_blank" rel="nofollow" >sql</a>.executereader()<br />
dim numbercols as integer = reader.fieldcount<br />
dim row as integer = 2<br />
dim i as integer = 0<br />
输出标题<br />
for i = 0 to numbercols – 1<br />
xlsheet.activesheet.cells(1, i + 1) = reader.getname(i).tostring()<br />
next</p>
<p> 输出字段内容<br />
while (reader.read())<br />
for i = 0 to numbercols – 1<br />
xlsheet.activesheet.cells(row, i + 1) = reader.getvalue(i).tostring()<br />
next<br />
row = row + 1<br />
end while<br />
reader.close()<br />
cnn.close()<br />
try<br />
xlsheet.activesheet.export(server.mappath(“.”) + “\images\” + me.xlfile.text,_<br />
owc.sheetexportactionenum.ssexportactionnone)<br />
catch e as system.runtime.interopservices.comexception<br />
response.write(“错误：” + e.message)<br />
end try<br />
end sub</p>
<p> private sub writedatagrid2excel2()<br />
dim xlsheet as new spreadsheetclass()<br />
dim i as integer = 0<br />
dim j as integer = 0<br />
response.end()<br />
输出标题<br />
dim oitem as datagridcolumn<br />
for each oitem in datagrid1.columns<br />
xlsheet.activesheet.cells(1, i + 1) = oitem.headertext<br />
xlsheet.activesheet.range(xlsheet.activesheet.cells(1, 1),_<br />
xlsheet.activesheet.cells(1, i + 1)).font.bold = true<br />
设置格式<br />
xlsheet.range(xlsheet.cells(1, 1), xlsheet.cells(1, i + 1)).font.bold = true<br />
xlsheet.range(xlsheet.cells(1, 1), xlsheet.cells(1, i + 1)).font.color = “red”<br />
i = i + 1<br />
next</p>
<p> dim numbercols as integer = datagrid1.items.item(0).cells.count<br />
输出字段内容<br />
for j = 0 to datagrid1.items.count – 1<br />
for i = 0 to numbercols – 1<br />
xlsheet.range(xlsheet.cells(2, 2), xlsheet.cells(j + 2, i + 1)).font.color = “blue”<br />
xlsheet.range(“a2:b14″).wraptext = true<br />
xlsheet.range(xlsheet.cells(2, 1), xlsheet.cells(j + 2, i + 1)).autofitcolumns()<br />
xlsheet.activesheet.cells(j + 2, i + 1) = datagrid1.items.item(j).cells(i).text.replace(” “, ” “)<br />
next<br />
next<br />
try<br />
xlsheet.activesheet.export(server.mappath(“.”) + “\images\” + me.xlfile.text,_<br />
owc.sheetexportactionenum.ssexportactionnone)<br />
catch e as system.runtime.interopservices.comexception<br />
response.write(“错误：” + e.message)<br />
end try<br />
end sub</p>
<p> private sub exportdatagrid2excel_click(byval sender as object,_<br />
byval e as system.eventargs) handles exportdatagrid2excel.click<br />
if (me.xlfile.text.trim() <> “”) then<br />
me.writedatagrid2excel2()<br />
end if<br />
end sub</p>
<p> private sub exportdatabase2excel_click(byval sender as object, _<br />
byval e as system.eventargs) handles exportdatabase2excel.click<br />
if (me.xlfile.text.trim() <> “”) then<br />
me.writedatagrid2excel()<br />
end if<br />
end sub</p>
<p>end class<br />
]]>
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com 特别注意：本站所有转载文章言论不代表本站观点！ 本站所提供的图片等素材，版权归原作者所有，如需使用，请与原作者联系。未经允许不得转载：
IDC资讯中心 »
将DataGrid输出到Excel文件-.NET教程,数据库应用
IDC资讯中心
