.NET中将数据直接输出成Excel格式

作者:网络 来源:佚名 更新时间:2008-10-31 23:06:13 点击:

本文实现了将数据库中的数据直接输出到excel文件格式并在浏览器里输出。下面就是实现的例子:
查看例子

<以下为引用的内容:

excelexport.aspx
  
  <%@ page language="vb" autoeventwireup="false" codebehind="excelexport.aspx.vb"
  inherits="aspxweb.mengxianhui.com.excelexport"%>
  <!doctype html public "-//w3c//dtd html 4.0 transitional//en">
  <html>
   <head>
    <title>excelexport</title>
    <meta name="generator" content="microsoft visual studio.net 7.0">
    <meta name="code_language" content="visual basic 7.0">
    <meta name="vs_defaultclientscript" content="javascript">
    <meta name="vs_targetschema" content="http://schemas.microsoft.com/intellisense/ie5">
   </head>
  <body ms_positioning="gridlayout">
   <form id="form1" method="post" runat="server">
    <asp:datagrid id="datagrid1" runat="server" cellpadding="4" backcolor="white"
  bordercolor="#cc9966" borderwidth="1px" borderstyle="none" width="100%" height="100%"
  font-size="9pt" font-names="宋体">
    <selecteditemstyle font-bold="true" forecolor="#663399" backcolor="#ffcc66"></selecteditemstyle>
    <alternatingitemstyle backcolor="#ffcc99"></alternatingitemstyle>
    <itemstyle borderwidth="2px" forecolor="#330099" borderstyle="solid"
  bordercolor="black" backcolor="white"></itemstyle>
    <headerstyle font-bold="true" horizontalalign="center" borderwidth="2px"
  forecolor="#ffffcc" borderstyle="solid" bordercolor="black" backcolor="#990000"></headerstyle>
    </asp:datagrid>
   </form>
  </body>
  </html>
  
  
    excelexport.aspx.vb
  
  public class excelexport
  inherits system.web.ui.page
  protected withevents datagrid1 as system.web.ui.webcontrols.datagrid
  #region " web 窗体设计器生成的代码 "
  '该调用是 web 窗体设计器所必需的。
  <system.diagnostics.debuggerstepthrough()> private sub initializecomponent()
  end sub
  
  private sub page_init(byval sender as system.object, byval e as system.eventargs) _
  handles mybase.init
   'codegen: 此方法调用是 web 窗体设计器所必需的
   '不要使用代码编辑器修改它。
   initializecomponent()
  end sub
  
  #end region
  private sub page_load(byval sender as system.object, byval e as system.eventargs) _
  handles mybase.load
   '在此处放置初始化页的用户代码
   ' 定义是否是 sql server 数据库,这里为false
   dim blnissqlserver as system.boolean = false
   dim strsql as string
   dim objdataset as new dataset()
   dim objconn as object
   dim strcnn as string
   if blnissqlserver then
    strcnn = "user id=sa;initial catalog=northwind;data source=.\netsdk;"
    objconn = new system.data.sqlclient.sqlconnection(strcnn)
    objconn.open()
    dim objadapter as new system.data.sqlclient.sqldataadapter()
    strsql = "select * from customers where country='usa'"
    objadapter.selectcommand = new system.data.sqlclient.sqlcommand(strsql, objconn)
    objadapter.fill(objdataset)
   else
    strcnn = "provider=microsoft.jet.oledb.4.0;data source=" + server.mappath("test.mdb")
    objconn = new system.data.oledb.oledbconnection(strcnn)
    objconn.open()
    dim objadapter as new system.data.oledb.oledbdataadapter()
    strsql = "select top 10 title from document"
    objadapter.selectcommand = new system.data.oledb.oledbcommand(strsql, objconn)
    objadapter.fill(objdataset)
   end if
   dim oview as new dataview(objdataset.tables(0))
   datagrid1.datasource = oview
   datagrid1.databind()
   objconn.close()
   objconn.dispose()
   objconn = nothing
   if request.querystring("bexcel") = "1" then
    response.contenttype = "application/vnd.ms-excel"
    ' 从content-type header中去除charset设置
    response.charset = ""
    ' 关闭 viewstate
    me.enableviewstate = false
    dim tw as new system.io.stringwriter()
    dim hw as new system.web.ui.htmltextwriter(tw)
    ' 获取control的html
    datagrid1.rendercontrol(hw)
    ' 把html写回浏览器
    response.write(tw.tostring())
    response.end()
   end if
  end sub
  end class