.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



相關文章

Cloud Intelligence Leading the Digital Future

Alibaba Cloud ACtivate Online Conference, Nov. 20th & 21st, 2019 (UTC+08)

Register Now >

Starter Package

SSD Cloud server and data transfer for only $2.50 a month

Get Started >

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 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。