在ASP.NET中將資料直接輸出成Excel格式

來源:互聯網
上載者:User
   本文實現了將資料庫中的資料直接輸出到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 Form設計器產生的程式碼 "
'該調用是 Web Form設計器所必需的。
<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 Form設計器所必需的
 '不要使用代碼編輯器修改它。
 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
 

 



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。