.NET开发

本类阅读TOP10

·NHibernate快速指南(翻译)
·vs.net 2005中文版下载地址收藏
·【小技巧】一个判断session是否过期的小技巧
·VB/ASP 调用 SQL Server 的存储过程
·?dos下编译.net程序找不到csc.exe文件
·通过Web Services上传和下载文件
·学习笔记(补)《.NET框架程序设计(修订版)》--目录
·VB.NET实现DirectDraw9 (2) 动画
·VB.NET实现DirectDraw9 (1) 托管的DDraw
·建站框架规范书之——文件命名

分类导航
VC语言Delphi
VB语言ASP
PerlJava
Script数据库
其他语言游戏开发
文件格式网站制作
软件工程.NET开发
Export DataSets to Excel...

作者:未知 来源:月光软件站 加入时间:2005-2-28 月光软件站

Often we need to load the data from a dataset into an excel spreadsheet to be manipulated and/or saved off to a local file. There are several ways to accomplish this using either Crystal Reports or ActiveReports. However there is a simple, elegant way to do the same thing without the need for a reporting tool.


By: Ken Walker Date: March 14, 2003 Download the code. Article Rating: 4.5 Printer Friendly Version

This article will show how to create a class which does the export. The class contains a convert method which has three overloads so that we can pass in different kinds of information:

  1. Overload #1
    1. A Dataset
    2. The response object of the web page
  2. Overload #2
    1. A Dataset
    2. An index value of which table from the dataset
    3. The response object of the web page
  3. Overload #3
    1. A Dataset
    2. The table name of a table in the dataset
    3. The response object of the web page

The methods will be shared methods so that we don’t have to instaniate the class in order to use the method.

What makes this task so straight forward is the elegance of the .NET Framework design. It turns out that most web controls have a RenderControl method which will write an html text stream. All we need to do is set up the response object, call the RenderControl method of a datagrid and tell the response object to output the "rendering". Pretty simple!

Here’s the code for the class (DataSetToExcel.vb):

'Class to convert a dataset to an html stream which can be used to display the dataset
'in MS Excel
'The Convert method is overloaded three times as follows
' 1) Default to first table in dataset
' 2) Pass an index to tell us which table in the dataset to use
' 3) Pass a table name to tell us which table in the dataset to use


Public Class DataSetToExcel

  Public Shared Sub Convert(ByVal ds As DataSet, ByVal response As HttpResponse)
    'first let's clean up the response.object
    response.Clear()
    response.Charset = ""
    'set the response mime type for excel
    response.ContentType = "application/vnd.ms-excel"
    'create a string writer
    Dim stringWrite As New System.IO.StringWriter
    'create an htmltextwriter which uses the stringwriter
    Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
    'instantiate a datagrid
    Dim dg As New DataGrid
    'set the datagrid datasource to the dataset passed in
    dg.DataSource = ds.Tables(0)
    'bind the datagrid
    dg.DataBind()
    'tell the datagrid to render itself to our htmltextwriter
    dg.RenderControl(htmlWrite)
    'all that's left is to output the html
    response.Write(stringWrite.ToString)
    response.End()
  End Sub

  Public Shared Sub Convert(ByVal ds As DataSet, ByVal TableIndex As Integer, ByVal response As HttpResponse)
    'lets make sure a table actually exists at the passed in value
    'if it is not call the base method
    If TableIndex > ds.Tables.Count - 1 Then
      Convert(ds, response)
    End If
    'we've got a good table so
    'let's clean up the response.object
    response.Clear()
    response.Charset = ""
    'set the response mime type for excel
    response.ContentType = "application/vnd.ms-excel"
    'create a string writer
    Dim stringWrite As New System.IO.StringWriter
    'create an htmltextwriter which uses the stringwriter
    Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
    'instantiate a datagrid
    Dim dg As New DataGrid
    'set the datagrid datasource to the dataset passed in
    dg.DataSource = ds.Tables(TableIndex)
    'bind the datagrid
    dg.DataBind()
    'tell the datagrid to render itself to our htmltextwriter
    dg.RenderControl(htmlWrite)
    'all that's left is to output the html
    response.Write(stringWrite.ToString)
    response.End()
  End Sub

  Public Shared Sub Convert(ByVal ds As DataSet, ByVal TableName As String, ByVal response As HttpResponse)
    'let's make sure the table name exists
    'if it does not then call the default method
    If ds.Tables(TableName) Is Nothing Then
      Convert(ds, response)
    End If
    'we've got a good table so
    'let's clean up the response.object
    response.Clear()
    response.Charset = ""
    'set the response mime type for excel
    response.ContentType = "application/vnd.ms-excel"
    'create a string writer
    Dim stringWrite As New System.IO.StringWriter
    'create an htmltextwriter which uses the stringwriter
    Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
    'instantiate a datagrid
    Dim dg As New DataGrid
    'set the datagrid datasource to the dataset passed in
    dg.DataSource = ds.Tables(TableName)
    'bind the datagrid
    dg.DataBind()
    'tell the datagrid to render itself to our htmltextwriter
    dg.RenderControl(htmlWrite)
    'all that's left is to output the html
    response.Write(stringWrite.ToString)
    response.End()
  End Sub

End Class

Editor's Note: The class above was compiled using the following Visual Basic Compiler directive:

vbc /t:library /r:system.dll /r:system.web.dll /r:system.data.dll /r:system.xml.dll DataSetToExcel.vb

My example web page is based upon creating a dataset from SQL Server (the authors table from the pubs database), but it doesn’t matter how you get the dataset created, so modify your page according to your methodology. The example simply creates the dataset and calls the class method from the Page_Load event handler of the page.

Here’s the code for the calling page. First the .aspx page which is really just a shell to give us something to call. As usual, all the work is done in the code-behind file.

DataToExcel.aspx

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="DataToExcel.aspx.vb" Inherits="DataToExcel" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>DataSetToExcel</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<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">
 
</form>
</body>
</html>

DataToExcel.aspx.vb

Public Class DataToExcel
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

  'This call is required by the Web Form Designer.
  <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

  End Sub

  'NOTE: The following placeholder declaration is required by the Web Form Designer.
  'Do not delete or move it.
  Private designerPlaceholderDeclaration As System.Object

  Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
    'CODEGEN: This method call is required by the Web Form Designer
    'Do not modify it using the code editor.
    InitializeComponent()
  End Sub

#End Region

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim myConnection As New SqlClient.SqlConnection(ConfigurationSettings.AppSettings("PubsConnection"))
    Dim cmd As New SqlClient.SqlCommand("select * from authors", myConnection)
    Dim da As New SqlClient.SqlDataAdapter(cmd)
    'instantiate a dataset
    Dim ds As New DataSet
    Try
      'populate the dataset
      da.Fill(ds)
    Finally
      'check on connection status
      If myConnection.State = ConnectionState.Open Then
        myConnection.Close()
      End If
      'get rid of connection object
      myConnection.Dispose()
    End Try
    'call our class method
    DataSetToExcel.Convert(ds, Response)
  End Sub

End Class

This is really a simple solution to a common problem. Hope it works out for you!

You may run the program here.
You may download the code here.




相关文章

相关软件